How do I write my html table to excel file using epplus

Question

So what im trying to do here is to write a simple html table to a xlsx (excel) file using epplus. The code ive got this far is

controller:

public void saveToExcel(string tbl)
    {
        using (ExcelPackage p = new ExcelPackage())
        {
            p.Workbook.Worksheets.Add("demo");
            ExcelWorksheet ws = p.Workbook.Worksheets[1]; 


            ws.Name = "Demo";

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
            Response.BinaryWrite(p.GetAsByteArray());
        }

    }

now this creates a empty workbook. And all I want to do right now is to write this table I have in my

View:

<Table id="tbl" name="tbl">
                <tr>
                    <td>
                        Title 1
                    </td>
                    <td >
                        Title 1
                    </td>
                    <td>
                        Title 1
                    </td>
                </tr>
                <tr>
                    <td >
                        Row 1
                    </td>
                    <td>
                        Row 1
                    </td>
                    <td>
                        Row 1
                    </td>
                </tr>
                <tr>
                    <td >
                        Row 2
                    </td>
                    <td>
                        Row 2
                    </td>
                    <td>
                        Row 2
                    </td>
                </tr>
                <tr>
                    <td >
                        Row 2
                    </td>
                    <td>
                        Row 2
                    </td>
                    <td>
                        Row 2
                    </td>
                </tr>
            </table>

        @Html.ActionLink("saveToExcel", "saveToExcel")

to the workbook. But I just dont know how and where to start.

Thankful for any pointers in the right direction.

Answer 1

I Guess: First of all you have to convert your HTML-table to a .NET Datatable This can be found here Convert Table

Next you use this code (considering your created datatable is called 'data' :

        Dim attachment As String = "attachment; filename=MyExcelPage.xlsx"

        Dim epackage As ExcelPackage = New ExcelPackage
        Dim excel As ExcelWorksheet = epackage.Workbook.Worksheets.Add("ExcelTabName")
        excel.Cells("A1").LoadFromDataTable(data, True)

        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ClearHeaders()
        HttpContext.Current.Response.ClearContent()
        HttpContext.Current.Response.AddHeader("content-disposition", attachment)
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

        HttpContext.Current.Response.BinaryWrite(epackage.GetAsByteArray())

        HttpContext.Current.Response.End()
        epackage.Dispose()