Generating a HTML table from an Excel file using EPPlus?

Question

I would like to generate a HTML table from an excel file. The EPPlus package provides a .net API for manipulating excel file. I would be happy to know whether it is possible to generate a HTML table code from an Excel file using EPPlus? I couldn't find anything on the documentation, but intuition tells me that there should be a way to do it

Thank you!

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

If you are looking for something built into EPPlus, I havent seen anything that will export directly HTML.

Best thing would be to bring in the Excel file to EPPlus and extract the data to a collection or DataTable. That should be pretty straight forward.

From there, there is plenty of documentation on how to get that to an html table. Quick search turned up this as the first hit:

Datatable to html Table

edited May 23 '17 at 11:54

Community

1
1

answered May 14 '15 at 14:56

Ernie S

13,902
4
52
79

3

Too bad.. I was hoping there was some way to keep the formatting – Yuval Atzmon May 14 '15 at 17:36

score 0 · Answer 2 · answered Jul 12 '18 at 02:49

I wrote some code, you can try this.

static void Main(string[] args)
    {
        ExcelPackage p = new ExcelPackage(new System.IO.FileInfo("X.XLSX"));
        var sheet = p.Workbook.Worksheets["HMTD"];
        var noOfCol = sheet.Dimension.End.Column;
        var noOfRow = sheet.Dimension.End.Row;
        StringBuilder s = new StringBuilder();
        s.Append("<table>");
        for (int i = 1; i < noOfRow; i++)
        {
            s.Append("<tr>");
            for (int j = 1; j < noOfCol; j++)
            {
                int colspan = 1;
                int rowspan = 1;
                if (!sheet.Cells[i, j].Merge || (sheet.Cells[i, j].Merge && isFirstMergeRange(sheet, sheet.Cells[i, j].Address, ref colspan, ref rowspan)))
                {
                    s.Append("<td rowspan='" + rowspan + "' colspan='" + colspan + "'>");
                    if(sheet.Cells[i,j] != null && sheet.Cells[i,j].Value != null)
                        s.Append(sheet.Cells[i,j].Value.ToString());
                    s.Append("</td>");
                }
            }
            s.Append("</tr>");
        }
        s.Append("</table>");
        System.IO.File.WriteAllText("duc.html",s.ToString());
        Console.ReadKey();
    }
    private static bool isFirstMergeRange(ExcelWorksheet sheet, string address, ref int colspan, ref int rowspan)
    {
        colspan = 1;
        rowspan = 1;
        foreach (var item in sheet.MergedCells)
        {
            var s = item.Split(':');
            if (s.Length > 0 && s[0].Equals(address)){

                ExcelRange range = sheet.Cells[item];
                colspan = range.End.Column - range.Start.Column;
                rowspan = range.End.Row - range.Start.Row;
                if(colspan == 0) colspan = 1;
                if(rowspan == 0) rowspan = 1;
                return true;
            }
        }
        return false;
    }

score 0 · Answer 3 · edited Dec 13 '22 at 10:34

Based on the answer from Duc Tran Minh, it works fine for me after I modified the code like this:

static void Main(string[] args)
{
        ExcelPackage p = new ExcelPackage(new System.IO.FileInfo("X.XLSX"));
        var sheet = p.Workbook.Worksheets["HMTD"];
        var noOfCol = sheet.Dimension.End.Column;
        var noOfRow = sheet.Dimension.End.Row;
        StringBuilder s = new StringBuilder();
        s.Append("<table>");
        for (int i = 1; i <= noOfRow; i++)
        {
            s.Append("<tr>");
            for (int j = 1; j <= noOfCol; j++)
            {
                int colspan = 1;
                int rowspan = 1;
                if (!sheet.Cells[i, j].Merge || (sheet.Cells[i, j].Merge && isFirstMergeRange(sheet, sheet.Cells[i, j].Address, ref colspan, ref rowspan)))
                {
                    s.Append("<td rowspan='" + rowspan + "' colspan='" + colspan + "'>");
                    if(sheet.Cells[i,j] != null && sheet.Cells[i,j].Value != null)
                        s.Append(sheet.Cells[i,j].Value.ToString());
                    s.Append("</td>");
                }
            }
            s.Append("</tr>");
        }
        s.Append("</table>");
        System.IO.File.WriteAllText("duc.html",s.ToString());
        Console.ReadKey();
}

bool isFirstMergeRange(ExcelWorksheet sheet, string address, ref int colspan, ref int rowspan)
{
    colspan = 1;
    rowspan = 1;
    foreach (var item in sheet.MergedCells)
    {
        var s = item.Split(':');
        if (s.Length > 0 && s[0].Equals(address))
        {

            ExcelRange range = sheet.Cells[item];
            colspan = range.End.Column - range.Start.Column + 1;
            rowspan = range.End.Row - range.Start.Row + 1;
            return true;
        }
    }
    return false;
}

Generating a HTML table from an Excel file using EPPlus?

3 Answers3

Linked