How to Create, Read, or Update Excel Documents in C#

Source: Dev.to

Getting Started with Spire.XLS for .NET

Before we dive into the code, set up your development environment. Spire.XLS for .NET can be added to a C# project via NuGet.

Install via NuGet

1. Open your project in Visual Studio.
2. Right‑click the project in Solution Explorer → Manage NuGet Packages….
3. Search for Spire.XLS and install the package.

Basic Initialization

using Spire.Xls;
using System;
using System.IO;

// ... inside your method or class
Workbook workbook = new Workbook();
// Your Excel operations will go here
workbook.Dispose(); // Always dispose of the workbook object

Creating Excel Documents in C#

Spire.XLS makes it straightforward to create a new workbook, add sheets, populate data, and apply styling.

Example: Create a Basic Excel File with Data and Styling

using Spire.Xls;
using System;
using System.IO;
using System.Drawing; // For Color

class ExcelCreator
{
    public static void CreateExcelFile()
    {
        // 1. Create a new workbook
        Workbook workbook = new Workbook();

        // 2. Get the first worksheet and rename it
        Worksheet sheet = workbook.Worksheets[0];
        sheet.Name = "Product Sales";

        // 3. Write header row
        sheet.Range["A1"].Text = "Product ID";
        sheet.Range["B1"].Text = "Product Name";
        sheet.Range["C1"].Text = "Quantity";
        sheet.Range["D1"].Text = "Unit Price";
        sheet.Range["E1"].Text = "Total";

        // Apply basic styling to header
        CellStyle headerStyle = workbook.Styles.Add("HeaderStyle");
        headerStyle.Font.IsBold = true;
        headerStyle.KnownColor = ExcelColors.LightYellow;
        headerStyle.HorizontalAlignment = HorizontalAlignType.Center;
        sheet.Range["A1:E1"].Style = headerStyle;

        // 4. Populate data rows
        sheet.Range["A2"].Value = "P001";
        sheet.Range["B2"].Text = "Laptop";
        sheet.Range["C2"].NumberValue = 10;
        sheet.Range["D2"].NumberValue = 1200.50;
        sheet.Range["E2"].Formula = "=C2*D2";

        sheet.Range["A3"].Value = "P002";
        sheet.Range["B3"].Text = "Mouse";
        sheet.Range["C3"].NumberValue = 50;
        sheet.Range["D3"].NumberValue = 25.00;
        sheet.Range["E3"].Formula = "=C3*D3";

        // 5. Auto‑fit columns for readability
        for (int i = 1; i <= 5; i++)
            sheet.AutoFitColumn(i);

        // 6. Save the workbook
        string filePath = "ProductSales.xlsx";
        workbook.SaveToFile(filePath, ExcelVersion.Version2016);
        Console.WriteLine($"Excel file created at: {Path.GetFullPath(filePath)}");

        // 7. Clean up
        workbook.Dispose();
    }
}

This example demonstrates how to:

• Create a workbook and worksheet
• Write string and numeric data
• Apply basic styling (bold text, background color, alignment)
• Add a formula
• Save the file

Reading Data from Excel Documents

Spire.XLS can load existing workbooks and give you access to cell values, formulas, and comments.

Example: Read Data from an Existing Excel File

using Spire.Xls;
using System;
using System.IO;

class ExcelReader
{
    public static void ReadExcelFile(string filePath)
    {
        if (!File.Exists(filePath))
        {
            Console.WriteLine($"Error: File not found at {filePath}");
            return;
        }

        // 1. Load the workbook
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(filePath);

        // 2. Get the first worksheet
        Worksheet sheet = workbook.Worksheets[0];
        Console.WriteLine($"--- Reading data from sheet: {sheet.Name} ---");

        // 3. Read specific cells
        Console.WriteLine($"Product ID (A2): {sheet.Range["A2"].Text}");
        Console.WriteLine($"Product Name (B2): {sheet.Range["B2"].Text}");
        Console.WriteLine($"Quantity (C2): {sheet.Range["C2"].NumberValue}");
        Console.WriteLine($"Unit Price (D2): {sheet.Range["D2"].NumberValue}");
        Console.WriteLine($"Total (E2) – Value: {sheet.Range["E2"].NumberValue}");
        Console.WriteLine($"Total (E2) – Formula: {sheet.Range["E2"].Formula}");

        // 4. Iterate through all used cells
        Console.WriteLine("\n--- All Data ---");
        for (int row = 1; row <= sheet.LastRow; row++)
        {
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                Console.Write($"{sheet.Range[row, col].DisplayText}\t");
            }
            Console.WriteLine();
        }

        // 5. Clean up
        workbook.Dispose();
    }
}

Key points:

• LoadFromFile loads an existing workbook.
• Range["A2"] (or Range[row, col]) accesses individual cells.
• DisplayText returns the formatted representation of a cell’s value.

Updating Existing Excel Documents

Modifying a workbook follows the same pattern: load, change, and save.

Example: Update an Existing Excel File

using Spire.Xls;
using System;
using System.IO;

class ExcelUpdater
{
    public static void UpdateExcelFile(string filePath)
    {
        if (!File.Exists(filePath))
        {
            Console.WriteLine($"Error: File not found at {filePath}");
            return;
        }

        // 1. Load the workbook
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(filePath);

        // 2. Get the first worksheet
        Worksheet sheet = workbook.Worksheets[0];
        Console.WriteLine($"--- Updating data in sheet: {sheet.Name} ---");

        // 3. Modify a specific cell (e.g., change quantity of Laptop from 10 to 15)
        sheet.Range["C2"].NumberValue = 15;
        Console.WriteLine($"Updated Quantity for Laptop (C2) to: {sheet.Range["C2"].NumberValue}");

        // 4. Save the changes (overwrite or save as a new file)
        string updatedPath = "ProductSales_Updated.xlsx";
        workbook.SaveToFile(updatedPath, ExcelVersion.Version2016);
        Console.WriteLine($"Updated file saved at: {Path.GetFullPath(updatedPath)}");

        // 5. Clean up
        workbook.Dispose();
    }
}

This snippet shows how to:

• Load an existing workbook
• Change cell values (or formulas, styles, etc.)
• Persist the modifications with SaveToFile