So easy to integrate with SQL queries

Mar 24, 2011 at 8:06 PM

Manuel,

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
	sheet.Cell(1, 1).InsertTable(results);
	// run autofit on all the columns
	sheet.Columns().AdjustToContents();
	// Freeze the top row and the first five columns
	sheet.SheetView.Freeze(1, 5);
	// Mark the first row as BOLD
	sheet.FirstRow().Style.Font.Bold = true;
	// All done
	MemoryStream ms = new MemoryStream();
	workbook.SaveAs(ms);
	// return the filestream
	// Rewind the memory stream to the beginning
	ms.Seek(0, SeekOrigin.Begin);
	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

Coordinator
Mar 24, 2011 at 8:46 PM

Thank you for the kind words. This is the experience that I'm aiming for with this library (to be as easy and simple as possible).

Your comment about MS Open XML reminded me why I chose this name. When I was working with Open XML I finally got to the point where I said "Screw this, if this is open I'd rather work with a closed framework!"

-Manuel

May 19, 2011 at 9:25 PM

Hi Manuel,

 

I think that Robert's example is excellent, if you want to pull data from a database and insert it into a worksheet.  What if you already have the data on a worksheet, and you want to load it into a DataTable (especially with LINQ or LINQ to Entities) and then insert it into SQL Tables?

 

Do you have an example of how to pull in a range of data into a DataTable?  Many Thanks.

Coordinator
May 19, 2011 at 11:36 PM

I'm thinking of adding the method range.CopyTo(DataTable dt), what do you think?

May 20, 2011 at 2:10 PM

Adding that CopyTo(dt) would rock!  I admit that I was very exited at the use of LINQ and Lamdas, and thought that querying ranges and tables was already built in to ClosedXML.  I don't know how long it would take you to add this method- is there another way to query a range and bring it in, so that it can be processed?  I have kind of a tight deadline, so I may have to find another way if not.  Also, I wasn't able to get the above snippet from Robert to create the stream (

'return File(ms, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", filename);' has a compile error 'Class name is not valid at this point').

Kudos for your efforts!
Coordinator
May 20, 2011 at 3:21 PM

What do you mean with "query a range and bring it in"? Now I'm confused about your objectives.

As for saving to a stream, check out the example ASP.NET example on the FAQ.

May 20, 2011 at 5:11 PM

I want to save the range or table to my LINQ to Entities objects (i.e. to backend SQL server).  The code below is what I have so far, and it enumerates as it should, and the values are what I have in the range on the worksheet, so all is good.  So I guess I was correct when I thought the ability to query a range was built-in.  That's what I'm doing.  Is there any way to check the data type (or assumed data type) of the cell, so it will go into my strong-typed entity property without a lot of casting or parsing?  I'm not even sure if I'm reducing the range by checking for nulls (empty strings) in the where clause.  CopyToDataTable() still sounds like a good helper method, so I look forward to seeing it when you get it done.   Thanks!

 

var firstTableCell = ws.Cell("A7");
                    var lastTableCell = ws.LastCellUsed();
                    var rngData = ws.Range(firstTableCell.Address, lastTableCell.Address);
                    var query = rngData.Rows().Where(c => c.Cell(1).Value.ToString()!=String.Empty).AsQueryable();
                    var results = query.Where(x => x.Cell(1).ToString() != String.Empty);
                    var ff = results.Count();
                    foreach (var row in results)
                    {
                       var gg1= row.Cell(1).Value;
                       var gg2 = row.Cell(2).Value;
                       var gg4 =  row.Cell(4).Value;
                    }
May 20, 2011 at 5:40 PM

I knew right when I saw the name of this project this was the one I was looking for, LOL, someone who "get's it".  

 

I was wondering why every other openxml project and forum as a ghost town, tumble weeds and everything.

 

OK, thanks, will try to donate!

Coordinator
May 20, 2011 at 5:41 PM

Checking the data type:

cell.DataType

On your code there's no need to do the .AsQueryable nor to repeat the query:

            var firstTableCell = ws.Cell("A7");
            var lastTableCell = ws.LastCellUsed();
            var rngData = ws.Range(firstTableCell.Address, lastTableCell.Address);
            var results = rngData.Rows().Where(r => r.Cell(1).GetString() != String.Empty);
            var ff = results.Count();
            foreach (var row in results)
            {
                var gg1 = row.Cell(1).Value;
                var gg2 = row.Cell(2).Value;
                var gg4 = row.Cell(4).Value;
            }

May 20, 2011 at 9:41 PM

Thanks Manuel- works great.  As for the ASP.Net example in FAQ- Is there a complete sample of this in your Sample Project?  I'm not sure what to do with the snippet.  I'll play around with it over the weekend.

Coordinator
May 20, 2011 at 9:53 PM

Just create a sample web page, add a button, copy the FAQ's code into the click event, you may have to adjust where the code gets the Response object (maybe from context), and you're good to go.

May 26, 2011 at 8:56 PM

@SteveMets,

I am using MVC 2 and the code is from inside a controller so the return value is of type System.Web.FileStreamResult and the full method call is System.Web.Mvc.Controller.File() and the full method signature is

protected internal virtual FileStreamResult File(Stream fileStream, string contentType, string fileDownloadName);
So if you are not using the MVC framework inside a controller class, File would be interpreted as a class and not as a method.
@Manuel, the range.CopyTo(DataTable dt) method does seem useful. Right now, I am doing my Excel processing using OleDbConnection and OleDbDataReader ExecuteReader() 
to read the Excel files one row at a time and insert them one row at a time and that works well for me. I have also used SqlBulkCopy to good effect.
Again. thanks for a great product.