This product is fantastic. I was using Excel automation (Excel interop) in Visual Studio 2008, .Net 3.5 for creating and downloading Excel files out of SQL data. But of course that won't work unless Excel is installed on the server. So I switched to Microsoft
Open XML SDK which proved to be unbelievably complicated and cumbersome even compared to the Excel automation. Even something as simple as making a cell BOLD could take pages of code! There had to be a better way ... and this is it!
By combining the power of Linq2SQL, MVC and ClosedXML, it only took a dozen lines of code to retrieve query results by running an SQL stored procedure, putting those query results into an Excel spreadsheet with column headings and freeze panes, and sending
it back to the browser!
Here is the (somewhat simplified) code. The code is only simplified because I have it structured in a multi-tier separation of concerns with exception checking, etc. But the entire functional part of it was only these 13 lines of code!
// create data context
SqlFilesDataContext dc = new SqlFilesDataContext(connectionString);
// get query results from the stored procedure
var results = dc.QueryStoredProcedure();
// Create an Excel Workbook
XLWorkbook workbook = new XLWorkbook();
// Add the "Query Results" worksheet
IXLWorksheet sheet = workbook.Worksheets.Add("Query Results");
// Add the table to the Excel sheet
// run autofit on all the columns
// Freeze the top row and the first five columns
// Mark the first row as BOLD
sheet.FirstRow().Style.Font.Bold = true;
// All done
MemoryStream ms = new MemoryStream();
// return the filestream
// Rewind the memory stream to the beginning
string filename = "Excel.xlsx";
// Return the Excel file to the user
return File(ms, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", filename);
The most amazing part is that your InsertTable() method set all the right column names and data types and even added color to the header row! If I later change the query in the stored procedure to add or delete a column,
I won't even have to change any code because InsertTable() will automatically determine the correct column heading!
Wow! ClosedXML rocks.
Sincerely, Robert Tanenbaum