Using ClosedXML to create Excel files from a Database

[AdSense-A]

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.

datatable_to_excel

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:

 

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.

[AdSense-A]

 

 

4 thoughts to “Using ClosedXML to create Excel files from a Database”

  1. Hi. I am still getting that stupid error when opening the Excel file… It still wants to repair it even after using your code. Inside the log file for the repairs there is no relevant information (nothing is shown related to what was fixed). Please help me get rid of whatever is wrong with this (i’m using Office 2013).

    1. I get the same issue when opening it, unfortunately I don’t have a good fix for the repair part of Excel when creating a file this way

Leave a Reply

Your email address will not be published. Required fields are marked *

Enter Captcha Here : *

Reload Image

This site uses Akismet to reduce spam. Learn how your comment data is processed.