ClosedXML – Read/Write Excel File Using C#.NET

Many of Windows/Web applications having requirement that, get the data from database and import into Excel file. There are several ways for developers to write data into Excel using C#.NET.

See the following options
1. Microsoft Excel Interop
2. Microsoft Open XML SDK
3. ClosedXML

Microsoft Excel Interop:

1. Office Interop needs Excel driver to be installed on the machine which is generating excel file
2. Excel installed version must match with the interop assembly version
3. Interop calls are expensive in terms of performance

Microsoft Open XML SDK:

1. Open XML is developed by Microsoft
2. No need to install Excel driver on the machine
3. It is a document format consists of XML parts stored in ZIP package
4. It is the default format for MS Office 2007/2010

ClosedXML:

1. It is wrapper on Open XML specification
2. No need to install Excel driver on the machine
3. It makes easier for developers to create Excel 2007/2010/2013 files
4. It provides Object oriented way to manipulate files without dealing with the hassles of XML documents
5. Writing no.of lines of code is very less compare to Open XML
6. It supports both C#.NET and VB.NET
7. It supports wide range of excel operations like merge cells, group cells, sort cells, header, footer, tables, formulas, images, hyperlinks and etc.
8. It supports .NET framework 3.5, 4.0 , 4.5+
9. Install from nuget using PM> Install-Package ClosedXML

Check wide range of examples @ https://github.com/closedxml/closedxml/wiki

Example:

1. Create Simple excel file

var workbook = new XLWorkbook();

var worksheet = workbook.Worksheets.Add("Sample Sheet");

worksheet.Cell("A1").Value = "Hello World!";

workbook.SaveAs("HelloWorld.xlsx");

2. Export DataTable to excel

DataTable dtTable = new DataTable();

table.Columns.Add("FirstName", typeof(string));

table.Columns.Add("LastName", typeof(string));

table.Rows.Add(“Rama”,”M”);

table.Rows.Add(“Siva”,”S”);

using (XLWorkbook wb = new XLWorkbook())

            {

                wb.Worksheets.Add(dtTable);

                Response.Clear();

                Response.Buffer = true;

                Response.Charset = "";

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                Response.AddHeader("content-disposition", "attachment;filename=User.xlsx");

                using (MemoryStream MyMemoryStream = new MemoryStream())

                {

                    wb.SaveAs(MyMemoryStream);

                    MyMemoryStream.WriteTo(Response.OutputStream);

                    Response.Flush();

                    Response.End();

                }

            }

Happy Coding :)