How to Read and Write Excel Files in C#

To read and write Excel files in C#, you can utilize the ExcelPackage class from the EPPlus library, which provides a straightforward API for handling Excel documents. Here’s a step-by-step guide:

Step 1: Install EPPlus

Open your project in Visual Studio and go to Tools > NuGet Package Manager > Manage Packages. Search for “Epplus” and install the latest stable version.

Step 2: Read an Existing Excel File

using OfficeOpenXml;

public class ExcelHandler {
    public void ReadExcel(string filePath) {
        // Load the Excel file
        var package = new ExcelPackage(new FileInfo(filePath));

        // Get the first worksheet
        var worksheet = package.Workbook.Worksheets[0];

        // Iterate over each row starting from the second row (assuming headers are in the first row)
        for (int i = 1; i < worksheet.Dimension.Rows; i++) {
            string name = worksheet.Cells[i, 1].Value.ToString();
            int age = Convert.ToInt32(worksheet.Cells[i, 2].Value);

            // Process the data as needed
            Console.WriteLine($”Name: {name}, Age: {age}”);
        }

        // Dispose of the package to ensure resources are freed.
        package.Dispose();
    }
}

using OfficeOpenXml;

public class ExcelHandler {

public void ReadExcel(string filePath) {

// Load the Excel file

var package = new ExcelPackage(new FileInfo(filePath));

// Get the first worksheet

var worksheet = package.Workbook.Worksheets[0];

// Iterate over each row starting from the second row (assuming headers are in the first row)

for (int i = 1; i < worksheet.Dimension.Rows; i++) {

string name = worksheet.Cells[i, 1].Value.ToString();

int age = Convert.ToInt32(worksheet.Cells[i, 2].Value);

// Process the data as needed

Console.WriteLine($“Name: {name}, Age: {age}”);

}

// Dispose of the package to ensure resources are freed.

package.Dispose();

}

Step 3: Write Data to an Excel File

using OfficeOpenXml;

public class ExcelHandler {
    public void WriteExcel(string filePath, List<Person> people) {
        // Create a new Excel package
        var fileInfo = new FileInfo(filePath);
        if (fileInfo.Exists)
            fileInfo.Delete(); // Delete existing file if it exists

        using (var package = new ExcelPackage(fileInfo)) {
            // Add a new worksheet
            var worksheet = package.Workbook.CreateSheet(“Employees”);

            // Set the header row
            worksheet.Cells[1, 1].Value = “Name”;
            worksheet.Cells[1, 2].Value = “Age”;

            // Start from the second row for data
            int rowIndex = 2;

            foreach (var person in people) {
                worksheet.Cells[rowIndex, 1].Value = person.Name;
                worksheet.Cells[rowIndex, 2].Value = person.Age;

                rowIndex++;
            }

            // Save and close the package
            package.Save();
        }
    }
}

using OfficeOpenXml;

public class ExcelHandler {

public void WriteExcel(string filePath, List<Person> people) {

// Create a new Excel package

var fileInfo = new FileInfo(filePath);

if (fileInfo.Exists)

fileInfo.Delete(); // Delete existing file if it exists

using (var package = new ExcelPackage(fileInfo)) {

// Add a new worksheet

var worksheet = package.Workbook.CreateSheet(“Employees”);

// Set the header row

worksheet.Cells[1, 1].Value = “Name”;

worksheet.Cells[1, 2].Value = “Age”;

// Start from the second row for data

int rowIndex = 2;

foreach (var person in people) {

worksheet.Cells[rowIndex, 1].Value = person.Name;

worksheet.Cells[rowIndex, 2].Value = person.Age;

rowIndex++;

}

// Save and close the package

package.Save();

}

Step 4: Define a Person Class (Optional)

public class Person {
    public string Name { get; set; }
    public int Age { get; set; }
}

public class Person {

public string Name { get; set; }

public int Age { get; set; }

}

Explanation:

Reading: The code reads an existing Excel file, iterates through each row, and prints the values to the console. It skips the header row (assumed to be the first row) and starts reading from the second row onwards.
Writing: The code creates a new Excel file, adds a worksheet named “Employees,” sets headers, and writes data for each person in the list. It ensures any existing file with the same name is deleted before creating a new one.

Notes:

Dispose: Always dispose of the ExcelPackage object after you’re done to free resources.
Error Handling: Consider adding error handling for cases where the file might not exist or be improperly formatted.
Performance: For large datasets, consider using batch processing or optimized methods provided by EPPlus.

This approach efficiently handles basic read and write operations using EPPlus, making it suitable for many common scenarios in C# development.