Creating Excel Files in C# Without Microsoft Office: Libraries and Techniques

Introduction

Creating Excel files programmatically is a common requirement for software applications that deal with data reporting or manipulation. While Microsoft’s primary suite of tools like Excel can handle such tasks, there are scenarios where you may need to generate these files without installing Microsoft Office on the server or client machine.

This tutorial explores various libraries and techniques in C# that enable developers to create both .xls (Excel 97-2003) and .xlsx (Excel 2007 and later) formats. We’ll cover popular options like EPPlus, NPOI, Open XML SDK, and others while discussing their features, advantages, and use cases.

Libraries for Creating Excel Files in C#

1. EPPlus

EPPlus is a .NET library that allows developers to create and manipulate .xlsx files without needing Microsoft Office installed. It provides robust support for various Excel features such as ranges, cell styling, charts, shapes, pictures, named ranges, AutoFilter, and more.

• Features:

  • Full support for Excel 2007/2010/2013 formats.
  • Rich feature set including advanced formatting options.
  • Active development with comprehensive documentation.

• Licensing:

  • EPPlus version 4 is licensed under LGPL.
  • Version 5 is available under a Polyform Noncommercial license, requiring a commercial license for business use.

• Example Usage:

  using (ExcelPackage package = new ExcelPackage())
  {
      ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
      worksheet.Cells["A1"].Value = "Hello";
      worksheet.Cells["B2"].Value = 123;
  
      // Save the file to a specified path
      FileInfo fileInfo = new FileInfo(@"C:\path\to\yourfile.xlsx");
      package.SaveAs(fileInfo);
  }
  

2. NPOI

NPOI is an open-source library that supports both .xls and .xlsx formats. It provides a comprehensive set of features similar to those found in Microsoft’s libraries.

• Features:

  • Compatible with Excel 97-2003 and Excel 2007+ file formats.
  • Supports reading, writing, and modifying Excel files.
  • Includes support for charts, formulas, and cell styles.

• Example Usage:

  using NPOI.SS.UserModel;
  using System.IO;
  
  IWorkbook workbook = new XSSFWorkbook(); // For .xlsx
  ISheet sheet1 = workbook.CreateSheet("Sample Sheet");
  
  // Create a row in the sheet
  IRow row1 = sheet1.CreateRow(0);
  ICell cell1 = row1.CreateCell(0);
  cell1.SetCellValue("Hello NPOI!");
  
  using (FileStream fileStream = new FileStream(@"C:\path\to\yourfile.xlsx", FileMode.Create, FileAccess.Write))
  {
      workbook.Write(fileStream);
  }
  

3. Open XML SDK

The Open XML SDK is a Microsoft-provided library for working with the Office Open XML formats used in Excel files starting from 2007. It allows developers to create, manipulate, and read .xlsx files without needing Excel installed.

• Features:

  • Direct support from Microsoft ensures reliable documentation and support.
  • Includes tools for validating and diffing documents.
  • Allows manipulation of large files efficiently.

• Example Usage:

  using DocumentFormat.OpenXml.Packaging;
  using DocumentFormat.OpenXml.Spreadsheet;
  
  using (SpreadsheetDocument document = SpreadsheetDocument.Create(@"C:\path\to\yourfile.xlsx", SpreadsheetDocumentType.Workbook))
  {
      WorkbookPart workbookPart = document.AddWorkbookPart();
      workbookPart.Workbook = new Workbook();
  
      WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
      worksheetPart.Worksheet = new Worksheet(new SheetData());
  
      Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
      Sheet sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
      sheets.Append(sheet);
  
      workbookPart.Workbook.Save();
  }
  

4. ExcelLibrary

Though less commonly used today due to limited support for newer formats and active development status, ExcelLibrary is an open-source library that works primarily with .xls files.

• Features:

  • Simple and straightforward API.
  • Supports data import from databases using DataSetHelper.

• Example Usage:

  DataSet ds = new DataSet();
  DataTable dt = new DataTable("Sheet1");
  ds.Tables.Add(dt);
  
  // Add some example data to the table
  dt.Columns.Add("Column1", typeof(string));
  dt.Columns.Add("Column2", typeof(int));
  
  DataRow dr = dt.NewRow();
  dr["Column1"] = "Data";
  dr["Column2"] = 100;
  dt.Rows.Add(dr);
  
  // Create an Excel file from the DataSet
  ExcelLibrary.DataSetHelper.CreateWorkbook(@"C:\path\to\yourfile.xls", ds);
  

Choosing the Right Library

When deciding which library to use, consider the following factors:

• Format Support: Determine if you need support for .xls, .xlsx, or both.
• Feature Requirements: Consider whether advanced features like charts and complex styling are needed.
• Licensing: Evaluate licensing terms, especially for commercial projects.
• Community and Documentation: Libraries with active communities and comprehensive documentation can be more reliable.

Conclusion

Creating Excel files in C# without Microsoft Office is feasible using several libraries. EPPlus and NPOI are among the most popular due to their rich feature sets and community support. Open XML SDK offers official backing from Microsoft, making it a reliable choice for those working with .xlsx formats. Each library has its strengths, so choose based on your specific requirements and constraints.

By understanding these libraries’ capabilities and limitations, you can effectively integrate Excel file generation into your C# applications without relying on Microsoft Office installations.