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

#crud #csharp #excel #operations

In the dynamic world of modern application development, data reigns supreme. Whether it's financial reports, inventory lists, or analytical summaries, Excel remains a ubiquitous format for data storage and exchange across various industries. As C# developers, we often face the critical task of programmatically interacting with these Excel documents – be it generating new reports, extracting crucial information, or updating existing records.

However, this seemingly straightforward requirement often comes with its own set of challenges. Traditional approaches, such as relying on COM Interop, can be a developer's nightmare. They often lead to complex deployment scenarios, performance bottlenecks, and compatibility issues, especially when dealing with different versions of Microsoft Office. This is where efficient, robust, and purely .NET-based solutions become indispensable.

This article aims to cut through that complexity. We'll explore how to leverage a powerful third-party library, Spire.XLS for .NET, to seamlessly perform common Excel operations: creating, reading, and updating documents directly within your C# applications. Spire.XLS for .NET offers a comprehensive API that simplifies these tasks, providing a high-performance and reliable alternative to traditional methods.

Getting Started with Spire.XLS for .NET

Before we dive into the code, let's set up our development environment. Spire.XLS for .NET is easily integrated into your C# project via NuGet.

Install via NuGet:
Open your project in Visual Studio, right-click on your project in Solution Explorer, and select "Manage NuGet Packages...". Search for Spire.XLS and install the package.

Basic Initialization:
Once installed, you'll typically start by creating a Workbook object.

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

Creating a new Excel file from scratch is a common requirement. Spire.XLS for .NET makes this process intuitive, allowing you to add sheets, populate data, and even 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. Add a new worksheet or get the first default sheet
        Worksheet sheet = workbook.Worksheets[0];
        sheet.Name = "Product Sales"; // Rename the sheet

        // 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"; // Add a formula

        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. Set column widths for better readability
        sheet.AutoFitColumn(1);
        sheet.AutoFitColumn(2);
        sheet.AutoFitColumn(3);
        sheet.AutoFitColumn(4);
        sheet.AutoFitColumn(5);

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

        // 7. Dispose of the workbook
        workbook.Dispose();
    }
}

This example demonstrates how to create a new workbook, add a worksheet, write string and numeric data, apply basic styling (bold text, background color, alignment), add a formula, and save the file. The Workbook.SaveToFile() method is crucial for persisting your changes.

Reading Data from Excel Documents

Extracting information from existing Excel files is equally important. Spire.XLS for .NET allows you to load files and access cell data, formulas, and comments with ease.

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 an existing 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 data from 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) - Calculated Value: {sheet.Range["E2"].NumberValue}");
        Console.WriteLine($"Total (E2) - Formula: {sheet.Range["E2"].Formula}");

        // 4. Iterate through rows and columns to read a range of data
        Console.WriteLine("\n--- All Data ---");
        for (int row = 1; row <= sheet.LastRow; row++)
        {
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                // Access cell by index (row, col)
                Console.Write($"{sheet.Range[row, col].DisplayText}\t"); 
            }
            Console.WriteLine();
        }

        // 5. Dispose of the workbook
        workbook.Dispose();
    }
}

This code snippet demonstrates loading an Excel file, accessing individual cells by their names, and iterating through all used cells to display their content. Note the use of DisplayText to get the formatted text representation of a cell's value.

Updating Existing Excel Documents

Modifying an existing Excel file is a frequent task, such as updating prices, quantities, or adding new entries. Spire.XLS for .NET allows you to load a file, make changes, and then save the updated version.

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 existing 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's value
        // Let's update the quantity of "Laptop" (P001) from 10 to 15
        sheet.Range["C2"].NumberValue = 15;
        Console.WriteLine($"Updated Quantity for Laptop (C2) to: {sheet.Range["C2"].NumberValue}");

        // 4. Add a new row of data
        int newRowIndex = sheet.LastRow + 1;
        sheet.Range[newRowIndex, 1].Value = "P003";
        sheet.Range[newRowIndex, 2].Text = "Keyboard";
        sheet.Range[newRowIndex, 3].NumberValue = 20;
        sheet.Range[newRowIndex, 4].NumberValue = 75.50;
        sheet.Range[newRowIndex, 5].Formula = $"=C{newRowIndex}*D{newRowIndex}"; // Formula for the new row
        Console.WriteLine($"Added new product: Keyboard at row {newRowIndex}");

        // 5. Recalculate formulas if needed (Spire.XLS often recalculates automatically on save)
        workbook.CalculateAllValue(); 

        // 6. Save the modified workbook (can overwrite the original or save as a new file)
        string updatedFilePath = "ProductSales_Updated.xlsx";
        workbook.SaveToFile(updatedFilePath, ExcelVersion.Version2016);
        Console.WriteLine($"Excel file updated and saved to: {Path.GetFullPath(updatedFilePath)}");

        // 7. Dispose of the workbook
        workbook.Dispose();
    }
}

In this update example, we load our previously created ProductSales.xlsx file, modify the quantity of an existing product, and then add an entirely new row of data. We then save the changes to a new file, ProductSales_Updated.xlsx. The workbook.CalculateAllValue() method ensures all formulas are re-evaluated, though Spire.XLS often handles this automatically upon saving.

Conclusion

Interacting with Excel documents programmatically in C# doesn't have to be a daunting task. As demonstrated, Spire.XLS for .NET provides an elegant, efficient, and purely .NET solution for performing essential Excel operations like creating, reading, and updating. Its rich API abstracts away the complexities, allowing developers to focus on business logic rather than low-level file format details.

By adopting a robust third-party library like Spire.XLS, you can significantly enhance the stability, performance, and maintainability of your applications that rely on Excel automation. We've only scratched the surface here; Spire.XLS for .NET also offers extensive support for advanced features such as charts, pivot tables, data validation, conditional formatting, and much more. I encourage you to experiment with these examples and explore the comprehensive documentation to unlock its full potential in your C# projects. Happy coding!