Introduction
A very common need of any reporting solution is to give user ability to export the report content as Excel or PDF once he/she has viewed the report in the browser as HTML. There are few Nuget packages available which do a very fine PDF job (like wkhtmltopdf) but I could never find a decent Excel export tool. There are a few jQuery libraries out there which claim to this job but my biggest issue with any client side tool such as this is the "File Format" warning which the user gets when he/she tries to open the downloaded Excel file.
Background
I decided to write some C# code which would accept a string containing the HTML code of a table which will eventually be rendered as Excel file for the user to download. User will simply click an export button in his/her browser & the already rendered HTML report will be downloaded as a genuine xlsx file in user's browser window.
We'll be using NPOI v2.3.0 which is available as a nuget package from within Visual Studio. The URL is http://npoi.codeplex.com/. This is the .NET implementation of the famous Apache POI library for reading & writing MS Office documents.
We'll be using System.Xml.Linq (XElement & other related classes) to parse the HTML table structure on the server side (C#).
Every Excel will be represented by a POCO of class named ExcelCellMeta which has 5 public properties.
public class ExcelCellMeta
{
public string Content { get; set; }
public string StyleName { get; set; }
public int ColumnIndex { get; set; }
public int ColSpan { get; set; }
public int RowSpan { get; set; }
}
- Content represents an Excel cell's content.
StyleName is an Excel style object's name defined in the code. I've only a finite number of Excel styles defined at the moment. If not specified, our engine will apply a default style, I've named that style as "Content" in the code.ColumnIndex is the starting index of an Excel cell. If an Excel cell spans over two Excel columns C & D, its index will be 2 (which corresponds with C, the starting column of the cell).ColSpan is the number of columns a cell spans over.RowSpan is the number of rows a cell spans over.
The whole Excel worksheet is represented by a POCO of class names ExcelMeta which has two public properties.
public class ExcelMeta
{
public List<List<ExcelCellMeta>> Meta { get; set; }
public double[] ColumnWidths { get; set; }
}
- Property named Meta is a list of
ExcelCellMeta. Internal list represents an Excel row (a collection of ExcelCellMeta objects); external list represents a set of row or an Excel sheet. ColumnWidths is an array to set each excel column width. It should contain exactly as many elements as the number of Excel columns you need. Mostly, you would like to keep these widths between 1.0 & 2.0 as in the code I'd be multiplying these widths with 5120. This is just my preference.
In order to use this tool, of course, you can setup ExcelMeta object in the code, there is absolutely no need to set up an HTML table structure & parse it into an ExcelMeta object but that's what exactly I set out to do.
There is one more class in the mix, named ExcelHelper. This is the class responsible for parsing HTML table structure into ExcelMeta & also generation of Excel file. It has 5 style objects:
public ICellStyle heading1 { get; set; }
public ICellStyle heading2 { get; set; }
public ICellStyle rowHead { get; set; }
public ICellStyle columnHead { get; set; }
public ICellStyle content { get; set; }
and two methods:
public ExcelMeta GetExcelMeta(string theTableHtml);
public byte[] GetExcelDocument(ExcelMeta excelMeta);
You'd call the first method to convert HTML table structure into an ExcelMeta object & then pass it on to the second method to get an Excel document as byte array.
Using the Code
There are a few custom HTML attributes we have to use to adorn HTML for the table.
"table" tag has to "attributed" with 'data-xls-columns' & 'data-xls-column-widths' like this:
<table class="table table-bordered" data-xls-columns="5"
data-xls-column-widths="1,1.5,1.5,1,1">
Here, we are telling our ExcelMeta parser that we want 5 columns in our Excel sheet & 2nd & 3rd column should be 1.5 times wider compared to other columns.
"tr" can be attributed with 'data-xls-exclude="True"' if you want to exclude a certain table row from the Excel sheet.
<tr data-xls-exclude="True">
"td" can be attributed with rowspan, colspan, data-xls-col-index & data-xls-class. rowspan & colspan serve the same purpose as they do in the HTML world and are the only two standard HTML attributes we use in the Excel parser. data-xls-col-index is to signify the starting column index of the Excel cell. Column A corresponds to 0, B corresponds to 1, C to 2 & so on. data-xls-class has to be valued at one of the 5 ICellStyle objects we have defined in ExcelHelper class, i.e., heading1, heading2, rowHead, columnHead or content.
<tr>
<td colspan=2 rowspan=2 data-xls-col-index="0"
data-xls-class="rowHead" class="rowHead"> A 2x2 cell </td>
<td colspan=3 data-xls-col-index="2" > A 1X3 cell </td>
</tr>
<tr>
<td colspan=3 data-xls-col-index="2" >A 1X3 cell </td>
</tr>
Did you notice how the first column in second row is "data-xls-col-index"ed at 2 (column C). This is because the 2X2 cell from the first row is occupying "data-xls-col-index" 0 & 1 (A & B) in both first & second rows.
A sample HTML table may look like this:
<table class="table table-bordered" data-xls-columns="5"
data-xls-column-widths="1,1.5,1.5,1,1">
<tbody><tr data-xls-exclude="True">
<td colspan="5" style="text-align:right;">
<a id="exportLink" href="#" class="btn btn-default">
<span class="glyphicon glyphicon-th"></span> Export to Excel</a></td>
</tr>
<tr>
<td colspan="5" data-xls-col-index="0"
data-xls-class="heading1" class="heading1">Searching criteria</td>
</tr>
<tr>
<td colspan="2" data-xls-col-index="0" data-xls-class="rowHead"
class="rowHead">Search Param 1</td>
<td colspan="3" data-xls-col-index="2">8821650318629</td>
</tr>
<tr>
<td colspan="2" data-xls-col-index="0" data-xls-class="rowHead"
class="rowHead">Period</td>
<td colspan="3" data-xls-col-index="2">04/04/2017 - 03/05/2017</td>
</tr>
<tr>
<td colspan="2" rowspan="2" data-xls-col-index="0"
data-xls-class="rowHead" class="rowHead">Search Param 2</td>
<td colspan="3" data-xls-col-index="2">Call Details</td>
</tr>
<tr>
<td colspan="3" data-xls-col-index="2">GPS Location Information</td>
</tr>
<tr>
<td colspan="2" rowspan="2" data-xls-col-index="0"
data-xls-class="rowHead" class="rowHead">Search Param 3</td>
<td colspan="3" data-xls-col-index="2">MS - ORIGINATING</td>
</tr>
<tr>
<td colspan="3" data-xls-col-index="2">MS - TERMINATING</td>
</tr>
<tr>
<td colspan="5" data-xls-col-index="0"
data-xls-class="heading1" class="heading1">Summary Report</td>
</tr>
<tr>
<td colspan="5" data-xls-col-index="0"
data-xls-class="heading2" class="heading2">Originating Call Details</td>
</tr>
<tr>
<td data-xls-col-index="0" data-xls-class="columnHead"
class="columnHead">Number calling </td>
<td data-xls-col-index="1" data-xls-class="columnHead"
class="columnHead">Number called</td>
<td data-xls-col-index="2" data-xls-class="columnHead"
class="columnHead">Date(YYYY/MM/DD HH:MM:SS) </td>
<td data-xls-col-index="3" data-xls-class="columnHead"
class="columnHead">Call Duration(sec) </td>
<td data-xls-col-index="4" data-xls-class="columnHead"
class="columnHead">Country </td>
</tr>
<tr>
<td data-xls-col-index="0">8821650318629 </td>
<td data-xls-col-index="1">393662555630 </td>
<td data-xls-col-index="2">2017/05/02 16:31:17 </td>
<td data-xls-col-index="3">0 </td>
<td data-xls-col-index="4">Mediterranean Sea </td>
</tr>
<tr>
<td data-xls-col-index="0">8821650318629 </td>
<td data-xls-col-index="1">393662555630 </td>
<td data-xls-col-index="2">2017/05/02 03:40:30 </td>
<td data-xls-col-index="3">0 </td>
<td data-xls-col-index="4">Italy </td>
</tr>
<tr>
<td colspan="5" data-xls-col-index="0"
data-xls-class="heading2" class="heading2">Terminating Call Details</td>
</tr>
<tr>
<td data-xls-col-index="0" data-xls-class="columnHead"
class="columnHead">Number calling </td>
<td data-xls-col-index="1" data-xls-class="columnHead"
class="columnHead">Number called </td>
<td data-xls-col-index="2" data-xls-class="columnHead"
class="columnHead">Date(YYYY/MM/DD HH:MM:SS) </td>
<td data-xls-col-index="3" data-xls-class="columnHead"
class="columnHead">Call Duration(sec) </td>
<td data-xls-col-index="4" data-xls-class="columnHead"
class="columnHead">Country </td>
</tr>
<tr>
<td data-xls-col-index="0" colspan="5" class="NoRecord">No Record found.</td>
</tr>
<tr>
<td data-xls-col-index="0" colspan="5"
data-xls-class="heading2" class="heading2">GPS Location Information </td>
</tr>
<tr>
<td data-xls-col-index="0" data-xls-class="columnHead"
class="columnHead">IMEI </td>
<td data-xls-col-index="1" data-xls-class="columnHead"
class="columnHead">Date(YYYY/MM/DD HH:MM:SS) </td>
<td data-xls-col-index="2" data-xls-class="columnHead"
class="columnHead">Country </td>
<td data-xls-col-index="3" data-xls-class="columnHead"
class="columnHead">Latitude </td>
<td data-xls-col-index="4" data-xls-class="columnHead"
class="columnHead">Longitude </td>
</tr>
<tr>
<td data-xls-col-index="0">3560130010789500 </td>
<td data-xls-col-index="1">2017/05/03 16:02:51 </td>
<td data-xls-col-index="2">ITALY </td>
<td data-xls-col-index="3">45.220586 </td>
<td data-xls-col-index="4">12.282395 </td>
</tr>
</tbody></table>
In my MVC application, I am using class to facilitate HTML code submission from View to Controller.
public class ExcelModel
{
[AllowHtml]
public string Data { get; set; }
}
/
You can use this jQuery to submit HTML:
function exportToExcel(id)
{
$('#Data').remove();
$(id).attr("action","@Url.Action("ExportExcel")");
tableHtml = $(id).html();
$(id).append("<input id="Data" name="Data" type="hidden" />");
$('#Data').val(tableHtml);
$(id).submit();
}
$(document).ready(function () {
$("#exportLink").click(function () { exportToExcel("#exportTable");});
});
MVC controller method which serves Excel file to the view, looks like this:
public ActionResult ExportExcel(ExcelModel excelModel)
{
ExcelHelper excelHelper= new ExcelHelper();
ExcelMeta meta = excelHelper.GetExcelMeta(excelModel.Data);
byte[] content = excelHelper.GetExcelDocument(meta);
FileContentResult result = new FileContentResult
(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = "CARS.xlsx"
};
return result;
}
You can download the VS2017 solution here.
History
- 9th May, 2017: Initial version
