Using ClosedXML to create Excel files from a Database

by Andy
Posted on July 1, 2015July 29, 2020

I was struggling to create an Excel file from a database table using OpenXML. OpenXML is fine if you need to build a file and save it to the hard drive. Also there was a lot more code to deal with in creating the file. It was probably 50 or so lines just to create the file and loop through the datatable to create each row. It really wasn’t that hard to create the file and the granular control wasn’t a bad thing.

The real problem was downloading the file. I could download the file alright, but every time I opened the file Excel would say it need to repair it. Then would display an error message about what was repaired. It seemed there was an issue with formating the file each time. This didn’t really happen when I saved the file to the hard drive, but saving the file wasn’t really an option as this was on a web server and I didn’t really have control of the file structure. The repair issue would probably not a big deal as the file was actually fine, but I know that the clients who would be using this particular file would have some concerns about the errors when opening the file.

I finally ran across the ClosedXML library and the following article . This was amazing! I could use ClosedXML to create Excel files from a database in as little 13 lines of actual code. In fact my final code was even less than the 13 in the original article. My final code looked something like this:

 using (MyDataContext context = new MyDataContext (ConnectionManager.GetConnectionString()))
                {
                    var dt = context.DataTable.Select(r => r);

                    using (XLWorkbook workbook = new XLWorkbook())
                    {
                        IXLWorksheet sheet = workbook.Worksheets.Add("Sheet 1");
                        sheet.Cell(1, 1).InsertTable(dt);
                        sheet.Columns().AdjustToContents();

                        MemoryStream mStream = new MemoryStream();

                        workbook.SaveAs(mStream);

                        string fileName = "MyExcelFile-" + DateTime.Now.ToShortDateString() + ".xlsx";

                        mStream.WriteTo(Response.OutputStream);
                        mStream.Close();

                        Response.AddHeader("content-disposition", "attachment;  filename=" + fileName);

                        HttpContext.Current.ApplicationInstance.CompleteRequest();
                    }
                }

using (MyDataContext context = new MyDataContext (ConnectionManager.GetConnectionString()))

{

var dt = context.DataTable.Select(r => r);

using (XLWorkbook workbook = new XLWorkbook())

{

IXLWorksheet sheet = workbook.Worksheets.Add("Sheet 1");

sheet.Cell(1, 1).InsertTable(dt);

sheet.Columns().AdjustToContents();

MemoryStream mStream = new MemoryStream();

workbook.SaveAs(mStream);

string fileName = "MyExcelFile-" + DateTime.Now.ToShortDateString() + ".xlsx";

mStream.WriteTo(Response.OutputStream);

mStream.Close();

Response.AddHeader("content-disposition", "attachment; filename=" + fileName);

HttpContext.Current.ApplicationInstance.CompleteRequest();

}

The three lines that actually do the work are highlighted. The rest is to download the file to the client from memory. No saving the file to the hard drive first, then destroying it, just simple on the fly file creation. This was the best solution I have found so far.

ClosedXML is a hand little project that is still in active development. I was a little concerned that it had gone a while without being worked on, but according to the logs it is still very active. It is a great little tool for creating Excel files from a database.

Thank you guys for creating such a useful library and even better making a Nuget package to include it in my project.