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
1. Create Simple excel file
2. Export DataTable to excel
Happy Coding :)
See the following options
1. Microsoft Excel Interop
2. Microsoft Open XML SDK
3. ClosedXML
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:
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
workbook.SaveAs("HelloWorld.xlsx");
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 :)
