Reading and Writing Excel Files with C#: Using the EPPlus Library

Kuzey Sarp Demir

Moderator

7 min

12.12.2023

0
0
0

Excel file manipulation is a common requirement for many developers. The EPPlus library provides an easy way to perform Excel operations in C#. This comprehensive guide will walk you through everything you need to know about using EPPlus for Excel automation.

What is EPPlus?

EPPlus is an open-source library for creating, reading, modifying, and processing Excel files (.xlsx) on the .NET platform. It uses the Office Open XML format and doesn't require Microsoft Excel to be installed.

Key features:

  • Supports Excel 2007/2010/2013/2016/2019 formats
  • Manages cell styling, formulas, tables, charts, and more
  • Offers LINQ support
  • Delivers high performance

Installation

To add EPPlus to your project using NuGet Package Manager:

  • Right-click your project in Visual Studio
  • Select "Manage NuGet Packages"
  • Search for "EPPlus" in the "Browse" tab
  • Select the EPPlus package and click "Install"

Alternatively, you can run this command in the Package Manager Console:

Install-Package EPPlus

Reading Excel Files

Here's how to read data from an Excel file with EPPlus:

using OfficeOpenXml;
using System;
using System.IO;

class ExcelReader
{
    public void ReadExcelFile(string filePath)
    {
        // EPPlus license setting (for Community version)
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        
        FileInfo fileInfo = new FileInfo(filePath);
        
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            if (package.Workbook.Worksheets.Count == 0)
            {
                Console.WriteLine("No worksheets found in the Excel file!");
                return;
            }
            
            // Select the first worksheet
            ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
            
            // Get row and column counts
            int rowCount = worksheet.Dimension.Rows;
            int colCount = worksheet.Dimension.Columns;
            
            Console.WriteLine($"Reading Excel file: {filePath}");
            Console.WriteLine($"Found {rowCount} rows and {colCount} columns.");
            
            // Read all cells and print to console
            for (int row = 1; row <= rowCount; row++)
            {
                for (int col = 1; col <= colCount; col++)
                {
                    Console.Write($"{worksheet.Cells[row, col].Text}\t");
                }
                Console.WriteLine();
            }
        }
    }
}

Writing to Excel Files

Creating a new Excel file and writing data with EPPlus:

using OfficeOpenXml;
using System;
using System.IO;

class ExcelWriter
{
    public void CreateExcelFile(string filePath)
    {
        // EPPlus license setting
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        
        FileInfo fileInfo = new FileInfo(filePath);
        
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            // Add a new worksheet
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("MyData");
            
            // Write header row
            worksheet.Cells[1, 1].Value = "ID";
            worksheet.Cells[1, 2].Value = "First Name";
            worksheet.Cells[1, 3].Value = "Last Name";
            worksheet.Cells[1, 4].Value = "Email";
            
            // Format header row
            using (var range = worksheet.Cells[1, 1, 1, 4])
            {
                range.Style.Font.Bold = true;
                range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
            }
            
            // Add sample data
            worksheet.Cells[2, 1].Value = 1;
            worksheet.Cells[2, 2].Value = "John";
            worksheet.Cells[2, 3].Value = "Doe";
            worksheet.Cells[2, 4].Value = "john@example.com";
            
            worksheet.Cells[3, 1].Value = 2;
            worksheet.Cells[3, 2].Value = "Jane";
            worksheet.Cells[3, 3].Value = "Smith";
            worksheet.Cells[3, 4].Value = "jane@example.com";
            
            // Auto-fit columns
            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
            
            // Save file
            package.Save();
            
            Console.WriteLine($"Excel file created: {filePath}");
        }
    }
}

Sample Project: Exporting a Data List to Excel

Let's create a more practical example - an application that exports a product list to Excel:

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;

namespace ExcelExportExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create sample data
            List<Product> products = new List<Product>
            {
                new Product { Id = 1, Name = "Laptop", Category = "Electronics", Price = 1200.50m, Stock = 45 },
                new Product { Id = 2, Name = "Smartphone", Category = "Electronics", Price = 850.00m, Stock = 120 },
                new Product { Id = 3, Name = "Book", Category = "Stationery", Price = 45.75m, Stock = 500 },
                new Product { Id = 4, Name = "Charger", Category = "Electronics", Price = 19.90m, Stock = 200 },
                new Product { Id = 5, Name = "Notebook", Category = "Stationery", Price = 25.00m, Stock = 350 }
            };
            
            string filePath = "ProductList.xlsx";
            
            // Write to Excel
            ExportToExcel(products, filePath);
            
            Console.WriteLine("Product list exported to Excel file.");
            Console.WriteLine($"File path: {Path.GetFullPath(filePath)}");
        }
        
        static void ExportToExcel(List<Product> products, string filePath)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            
            FileInfo fileInfo = new FileInfo(filePath);
            
            using (ExcelPackage package = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Products");
                
                // Header row
                worksheet.Cells[1, 1].Value = "ID";
                worksheet.Cells[1, 2].Value = "Product Name";
                worksheet.Cells[1, 3].Value = "Category";
                worksheet.Cells[1, 4].Value = "Price";
                worksheet.Cells[1, 5].Value = "Stock";
                
                // Header formatting
                using (var range = worksheet.Cells[1, 1, 1, 5])
                {
                    range.Style.Font.Bold = true;
                    range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
                    range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }
                
                // Write data
                int row = 2;
                foreach (var product in products)
                {
                    worksheet.Cells[row, 1].Value = product.Id;
                    worksheet.Cells[row, 2].Value = product.Name;
                    worksheet.Cells[row, 3].Value = product.Category;
                    worksheet.Cells[row, 4].Value = product.Price;
                    worksheet.Cells[row, 5].Value = product.Stock;
                    
                    // Format price column as currency
                    worksheet.Cells[row, 4].Style.Numberformat.Format = "#,##0.00 $";
                    
                    row++;
                }
                
                // Auto-fit columns
                worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
                
                // Add filter
                worksheet.Cells[1, 1, 1, 5].AutoFilter = true;
                
                // Save file
                package.Save();
            }
        }
    }
    
    class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Category { get; set; }
        public decimal Price { get; set; }
        public int Stock { get; set; }
    }
}

Advanced Features

Additional operations you can perform with EPPlus:

  • Conditional Formatting:
    • // Highlight stock below 50 in red
var stockColumn = worksheet.Cells[2, 5, products.Count + 1, 5];
var cond = stockColumn.ConditionalFormatting.AddLessThan(50);
cond.Style.Font.Color.Color = System.Drawing.Color.Red;
  • Adding Charts:
    • var chart = worksheet.Drawings.AddChart("PieChart", OfficeOpenXml.Drawing.Chart.eChartType.Pie3D);
chart.SetPosition(1, 0, 5, 0);
chart.SetSize(600, 400);
var serie = chart.Series.Add(worksheet.Cells[2, 4, products.Count + 1, 4], 
                           worksheet.Cells[2, 2, products.Count + 1, 2]);
  • Using Formulas:
    • // Calculate total price
worksheet.Cells[products.Count + 2, 3].Value = "Total:";
worksheet.Cells[products.Count + 2, 4].Formula = $"SUM(D2:D{products.Count + 1})";
worksheet.Cells[products.Count + 2, 4].Style.Font.Bold = true;

Performance Optimization

For working with large datasets, consider these techniques:

  • Process cells in bulk rather than individually:
    • var data = new object[products.Count, 5];
for (int i = 0; i < products.Count; i++)
{
    data[i, 0] = products[i].Id;
    data[i, 1] = products[i].Name;
    data[i, 2] = products[i].Category;
    data[i, 3] = products[i].Price;
    data[i, 4] = products[i].Stock;
}
worksheet.Cells[2, 1].LoadFromArrays(data);
  • Set CalculationMode to Manual:
    • package.Workbook.CalcMode = ExcelCalcMode.Manual;
// Perform operations...
package.Workbook.CalcMode = ExcelCalcMode.Automatic;

Conclusion

The EPPlus library provides a powerful and flexible solution for working with Excel files in C#. This guide has covered everything from basic read-write operations to advanced features like formatting, chart creation, and performance optimization. You're now equipped to implement Excel integration in your own applications using EPPlus.

Other Posts by Fib

Comments