Click here to Skip to main content
13,254,373 members (55,838 online)
Click here to Skip to main content
Add your own
alternative version

Stats

6.8K views
6 bookmarked
Posted 9 May 2017

Export html table content to Excel - Server Side.

, 9 May 2017
Rate this:
Please Sign up or sign in to vote.
Take Html structure of a table & export to a genuine Excel file - Sever side processing.

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 avaiable 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 such tool is the "File Format" warning which user gets when he/she tries to open the downloaded Excel file.

Excel_format_warning.JPG

Background

I decided to write some C# code which would accept an string containing the html code of a table which will eventually be rendered as Excel file for user to download. User will simply click an export button in his/her browser & already rendered html report will be download as a genuine xlsx file in user's browser window. 

We'll be using NPOI v2.3.0 whcih is available as nuget package from within Visual studio. Url is http://npoi.codeplex.com/ . This is .Net implementation of famous Apache POI library for reading & wiriting 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; }
}
  1. Content represents an Excel cell's content.
  2. StyleName is an excel style object's name defiend 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.
  3. ColumnIndex is the statrting 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).
  4. ColSpan is the number of columns a cell spans over.
  5. RowSpan is the number of rows a cell spans over.

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; }
}
  1. Property named Meta is a list of 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.
  2. ColumnWidths is an array to set each excel column width. It should contain exaclty 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, ofcourse you can setup ExcelMeta object in the code, there is absolutely no need to setup 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 to parse 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 first method to convert html table structure into an ExcleMeta object & then pass it on to 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 thay 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. rowsoan & colspan server the same purpose as they do in html world & are the only two standard html attribute we use  in the Excel parser. data-xls-col-index is to signify the starting column index of the excel cell. column A correponds 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 defiend 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 first column in second row is "data-xls-col-index"ed at 2 (column C). This is because the 2X2 cell from first row is occupying  "data-xls-col-index" 0 & 1  (A & B) in both first & second rows.

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 VS2017 solution here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 9 May 2017
Article Copyright 2017 by Muhammad_Faraz
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid