How to create an excel spreadsheet with C#?
I needed to create an Excel spread sheet in C# and I didn’t want to reinvent the wheel.
I found this awesome project: https://closedxml.codeplex.com
I already had a DataTable that I wanted to export in ASP.NET to an Excel file and download. I created an extension method for the DataTable.
public static class CloseXMLHelper
{
public static XLWorkbook ToExcel(this DataTable inDataTable)
{
var wb = new XLWorkbook();
var dataTable = inDataTable;
// Add a DataTable as a worksheet
wb.Worksheets.Add(dataTable);
return wb;
}
}
Then I just followed the documentation on the project for allowing a user to download the excel file. I already had the myDataTable variable in the below snippet. If you are wondering how to create a DataTable from a database, see this post: How to query a SQL database in C#?
var httpResponse = Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename=\"HelloWorld.xlsx\"");
// Flush the workbook to the Response.OutputStream
using (var memoryStream = new MemoryStream())
{
dataset.Tables[0].ToExcel().SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
It doesn’t get much more simple than this.
