Better than Downloading a DataGrid: ExcelXmlWriter

One of the common ways I've seen for any kind of "export to Excel" feature on websites is to output to a hidden DataGrid or GridView, and then rendering the contents of the GridView to the response stream, naming it with a .XLS extension. This has a series of problems, not the least of which is the "This file has contents that do not match the file extension" error dialog presented when you open the file in Excel.

One of the cool tools I've found recently is called "ExcelXmlWriter" hosted and produced by CarlosAg.net. As of Excel 2003, a specific type of XML format was supported (this is not the same as the OpenXML file format included in Office 2007, which is entirely different in that a .xlsx file is really a .zip archive). By including this library, the creation of workbooks, worksheets, and specific formats - even building in formulae - is incredibly simplified.

I have had some minor difficulty utilizing this library, but by and large, it's fairly simple.

   1:          Workbook book = new Workbook();

   2:          WorksheetStyle defaultStyle = book.Styles.Add(workbookStyleID);

   3:          defaultStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;

   4:          defaultStyle.Alignment.WrapText = false;

5:

   6:          WorksheetStyle headerStyle = book.Styles.Add(headerStyleID);

   7:          headerStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;

   8:          headerStyle.Alignment.WrapText = true;

   9:          headerStyle.Interior.Color = "#284775";

  10:          headerStyle.Interior.Pattern = StyleInteriorPattern.Solid;

  11:          headerStyle.Font.Color = "white";

  12:          headerStyle.Font.Bold = true;

13:

  14:          Worksheet sheet = book.Worksheets.Add("Classic Stats");

  15:          Worksheet EvolvedSheet = book.Worksheets.Add("Evolved Stats");

I then populated these sheets with a couple subroutines; one such subroutine is listed below:

   1:          sheet.Table.DefaultColumnWidth = 110;

2:

   3:          WorksheetRow headerRow = sheet.Table.Rows.Add();

   4:          headerRow.Cells.Add("Date", DataType.String, headerStyleID);

   5:          headerRow.Cells.Add("Total # of Plays", DataType.String, headerStyleID);

   6:          headerRow.Cells.Add("Distinct # of Players", DataType.String, headerStyleID);

   7:          headerRow.Cells.Add("Average Plays per Player", DataType.String, headerStyleID);

   8:          headerRow.Cells.Add("Lowest Score", DataType.String, headerStyleID);

   9:          headerRow.Cells.Add("Highest Score", DataType.String, headerStyleID);

  10:          headerRow.Cells.Add("Average Score", DataType.String, headerStyleID);

11:

  12:          foreach (SinglePlayerStats stat in StatsList)

  13:          {

  14:              WorksheetRow statRow = sheet.Table.Rows.Add();

  15:              AddDateTimeCol(statRow, stat.Date);

  16:              statRow.Cells.Add(stat.TotalPlays.ToString(), DataType.Number, workbookStyleID);

  17:              statRow.Cells.Add(stat.DistinctPlayers.ToString(), DataType.Number, workbookStyleID);

  18:              AddDoubleCol(statRow, stat.AvgPlaysPerPlayer);

  19:              AddDoubleCol(statRow, stat.MinScore);

  20:              AddDoubleCol(statRow, stat.HighScore);

  21:              AddDoubleCol(statRow, stat.AvgScore);

  22:          }

You may notice the "AddDateTimeCol" and "AddDoubleCol" methods as well. I've noted some particular difficulties with rendering correct XML; specifically, if the file's cells' data types don't match the types of their contents, Excel will plainly refuse to load the file. However, if you don't set them to the appropriate data types (for instance, by default everything is a string), Excel will complain that they're not typed correctly.

   1:      private void AddDateTimeCol(WorksheetRow row, IFormattable value)

   2:      {

   3:          if (value is DateTime)

   4:          {

   5:              row.Cells.Add(value.ToString("MM/dd/yyyy", CultureInfo.CurrentCulture.DateTimeFormat));

   6:          }

   7:          else

   8:          {

   9:              row.Cells.Add(value.ToString(), DataType.String, workbookStyleID);

  10:          }

  11:      }

12:

  13:      private void AddDoubleCol(WorksheetRow row, double value)

  14:      {

  15:          if (double.IsNaN(value))

  16:          {

  17:              row.Cells.Add("NaN", DataType.String, workbookStyleID);

  18:          }

  19:          else

  20:          {

  21:              row.Cells.Add(value.ToString("f3"), DataType.Number, workbookStyleID);

  22:          }

  23:      }

These functions provide a worthwhile and quick wrapper to help Excel not complain quite so much. And as you can see, the result is well worth the effort:

posted on Wednesday, September 12, 2007 2:17 AM

Feedback

# re: Better than Downloading a DataGrid: ExcelXmlWriter 9/12/2007 5:59 AM Wyatt Barnett

Finally someone else noticed this gem of a library. I posted about it a while ago (http://www.sitepoint.com/blogs/2006/08/22/making-excel-the-carlosag-way/) but it seems to get little play.

Post Feedback

Title:
Name:
Email: (never displayed)
Url:
Comments:
Please add 6 and 8 and type the answer here: Remember Me?

Running with Code

News

Archives

Post Categories

All Terralever

ASP.NET

Misc

Feedback

Post Feedback