Generating A HTML Table From An Excel File Using EPPlus?

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 gen

Solution 1:

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

Solution 2:

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;
    }