locked
how to convert excel to html table RRS feed

  • Question

  • User-1634604574 posted

    how to export excel to html table

    Saturday, February 2, 2019 11:12 AM

All replies

  • Saturday, February 2, 2019 1:05 PM
  • User-893317190 posted

    Hi zhyanadil.it@gmail.com,

    Do you mean  showing the conent of excel in html table?

    If so , you could try EPPlus.

    Below is small sample code.

    protected void Page_Load(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                FileInfo info = new FileInfo(Server.MapPath("/my.xlsx"));
                ExcelPackage package = new ExcelPackage(info);
                //get the first sheet of excel, start with 1 not 0
                ExcelWorksheet sheet = package.Workbook.Worksheets[1];
                // sheet.Dimension.End.Column gets the column count of the excel sheet
                // Cells[1,1,1,sheet.Dimension.End.Column] means getting cells from 
                // 1,1 to 1,sheet.Dimension.End.Column, which is all the cells in the first row
                foreach (var item in sheet.Cells[1,1,1,sheet.Dimension.End.Column])
                {
                    // add column to the datatable using the data of the first row in the excel sheet
                    table.Columns.Add(new DataColumn(item.Text.Trim()));
                }
                //sheet.Dimension.End.Row get the row count of the sheet
                // loop through from the second row of the sheet, which contain data of the excel sheet
                for (int i = 2; i <= sheet.Dimension.End.Row ; i++)
                {
                    //create a new row
                    DataRow row = table.NewRow();
                    // loop through all the cells in the current row
                    for (int j = 1; j <=sheet.Dimension.End.Column; j++)
                    {
                        // add data of every cell to the new datatable row
                        row[sheet.Cells[1, j].Text.Trim()] = sheet.Cells[i, j].Text;
                    }
                    // add the row to datatable
                    table.Rows.Add(row);
                }
                //new datatable has all the data from excel sheet
                // bind it to gridview
                GridView1.DataSource = table;
                GridView1.DataBind();
            }

    aspx

     <form id="form1" runat="server">
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        </form>

    My excel.

    id	name	age
    1	mike	12
    2	jerry	10
    3	kitty	14
    4	nancy	12
    5	david	15
    

    The result.

    For more information about EPPlus, you could refer to https://riptutorial.com/epplus/example/27956/create-a-datatable-from-excel-file

    Best regards,

    Ackerly Xu

    Monday, February 4, 2019 3:49 AM