How to Create an Excel (.XLS and .XLSX) File in C# without Installing Microsoft Office

If you’re a C# developer looking to create Excel files without the need to install Microsoft Office, you’re in luck! There are several libraries available that can help you achieve this task easily and efficiently. In this article, we will explore two popular libraries, ExcelLibrary and EPPlus, and discuss how to use them to create both .XLS and .XLSX files.

ExcelLibrary

ExcelLibrary is a free and open-source library that provides a simple and easy-to-use solution for creating Excel files in C#. It supports the older .XLS format and offers a DataSetHelper that allows you to work with Excel data using DataSets and DataTables.

To get started with ExcelLibrary, you can download the library from its Google Code repository. Once you have the library added to your project, you can use the following code snippet to create an Excel file:

// Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

// Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

// Open a DB connection (in this example with OleDb)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

// Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

// Add the table to the data set
ds.Tables.Add(dt);

// Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

This code snippet demonstrates how to create an Excel file by retrieving data from a database and populating a DataTable. The DataSetHelper class provided by ExcelLibrary makes it easy to convert the DataTable into an Excel worksheet.

EPPlus

EPPlus is another popular library for creating Excel files in C#. Unlike ExcelLibrary, EPPlus supports both the older .XLS format and the newer .XLSX format used by Excel 2007 and later versions. It offers a wide range of features, including support for formatting, formulas, and charts.

To use EPPlus, you can download the library from its official website or install it via NuGet. Here’s an example of how to create an Excel file using EPPlus:

using OfficeOpenXml;

// Create a new Excel package
using (ExcelPackage package = new ExcelPackage())
{
    // Create a new worksheet
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

    // Set cell values
    worksheet.Cells["A1"].Value = "Hello";
    worksheet.Cells["B1"].Value = "World";

    // Save the Excel file
    package.SaveAs(new FileInfo("MyExcelFile.xlsx"));
}

In this code snippet, we first create a new Excel package using the ExcelPackage class. We then create a new worksheet and set values for specific cells. Finally, we save the Excel file using the SaveAs method.

EPPlus provides a more modern and feature-rich solution for creating Excel files in C#. It is actively maintained and offers excellent performance even with large datasets.

If you need to work with the older .XLS format, ExcelLibrary is a suitable choice. It offers a simple API and supports working with DataSets and DataTables.

On the other hand, if you require support for the newer .XLSX format and additional features like formatting and formulas, EPPlus is the recommended library. It provides a comprehensive set of tools for creating professional Excel files.

Whether you choose ExcelLibrary or EPPlus, you can confidently create Excel files in C# without the need for Microsoft Office. These libraries offer reliable and efficient solutions for your Excel file generation needs.