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.

Categories C#

Related Posts

How to Post an Empty Body to REST API via HttpClient in C When working with REST APIs in C#, you may come across scenarios where you need to send a POST request with an empty body. This can be useful in situations where the API endpoint expects a POST request but doesn’t require any ...

Read more

Does C# have IsNullOrEmpty for List/IEnumerable?

If you’ve been working with C# for a while, you may have come across situations where you need to check if a List or IEnumerable is null or empty. In some other languages, like JavaScript, you have the convenience of using the Array.isArray() method or the length property to check if an array is empty ...

Read more

List Queries: 20 Times Faster than IQueryable?

Have you ever wondered why list queries are often faster than IQueryable queries in C#? In this article, we will explore the reasons behind this performance difference and discuss when and how to use each type of query. What is IQueryable? Before we dive into the performance comparison, let’s first understand what IQueryable is. IQueryable ...

Read more

Passing Different Number of Arguments to a Params Method at Runtime

How to Pass a Different Number of Arguments Using a List and Converting to an Array One way to pass a different number of arguments to a params method at runtime is by using a List<object> to store the arguments and then converting it to an array using the ToArray() method. Here’s an example: List<object> ...

Read more

Leave a Comment