Read Excel create HTML Table using StringBuilder and Print in ASP.Net using C# and VB.Net

Hi BugHunter,

Refer below sample, Accordingly to your requirement change sample code .

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />

Namespaces

C#

using System.IO;
using System.Data;
using System.Collections.Generic;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Code

C#

protected void ImportExcel(object sender, EventArgs e)
{
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
        Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
        DataTable dt = new DataTable();
        foreach (Row row in rows)
        {
            if (row.RowIndex.Value == 1)
            {
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Columns.Add(GetValue(doc, cell));
                }
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
                    i++;
                }
            }
        }

        StringBuilder sb = new StringBuilder();
        sb.Append("<table border = '1'>");
        sb.Append("<tr>");
        foreach (DataColumn column in dt.Columns)
        {
            sb.Append("<th>");
            sb.Append(column.ColumnName);
            sb.Append("</th>");
        }
        sb.Append("</tr>");
        foreach (DataRow row in dt.Rows)
        {
            sb.Append("<tr>");
            foreach (DataColumn column in dt.Columns)
            {
                sb.Append("<td>");
                sb.Append(row[column]);
                sb.Append("</td>");
            }
            sb.Append("</tr>");
        }
        sb.Append("</tr></table>");
        StringReader sr = new StringReader(sb.ToString());
        string gridHTML = sb.ToString().Replace("\"", "'").Replace(System.Environment.NewLine, "");
        StringBuilder spPrint = new StringBuilder();
        spPrint.Append("<script type = 'text/javascript'>");
        spPrint.Append("window.onload = new function(){");
        spPrint.Append("var printWin = window.open('', '', 'left=0");
        spPrint.Append(",top=0,width=1000,height=600,status=0');");
        spPrint.Append("printWin.document.write(\"");
        spPrint.Append(gridHTML);
        spPrint.Append("\");");
        spPrint.Append("printWin.document.close();");
        spPrint.Append("printWin.focus();");
        spPrint.Append("printWin.print();");
        spPrint.Append("printWin.close();};");
        spPrint.Append("</script>");
        ClientScript.RegisterStartupScript(this.GetType(), "PrintExcel", spPrint.ToString());
    }
}

private string GetValue(SpreadsheetDocument doc, Cell cell)
{
    string value = cell.CellValue.InnerText;
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
    }
    return value;
}

VB.Net

Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(filePath)

    Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filePath, False)
        Dim sheet As Sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild(Of Sheet)()
        Dim worksheet As Worksheet = (TryCast(doc.WorkbookPart.GetPartById(sheet.Id.Value), WorksheetPart)).Worksheet
        Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Descendants(Of Row)()
        Dim dt As DataTable = New DataTable()

        For Each row As Row In rows
            If row.RowIndex.Value = 1 Then
                For Each cell As Cell In row.Descendants(Of Cell)()
                    dt.Columns.Add(GetValue(doc, cell))
                Next
            Else
                dt.Rows.Add()
                Dim i As Integer = 0

                For Each cell As Cell In row.Descendants(Of Cell)()
                    dt.Rows(dt.Rows.Count - 1)(i) = GetValue(doc, cell)
                    i += 1
                Next
            End If
        Next

        Dim sb As StringBuilder = New StringBuilder()
        sb.Append("<table border = '1'>")
        sb.Append("<tr>")
        For Each column As DataColumn In dt.Columns
            sb.Append("<th>")
            sb.Append(column.ColumnName)
            sb.Append("</th>")
        Next
        sb.Append("</tr>")

        For Each row As DataRow In dt.Rows
            sb.Append("<tr>")
            For Each column As DataColumn In dt.Columns
                sb.Append("<td>")
                sb.Append(row(column))
                sb.Append("</td>")
            Next
            sb.Append("</tr>")
        Next

        sb.Append("</tr></table>")
        Dim sr As StringReader = New StringReader(sb.ToString())
        Dim gridHTML As String = sb.ToString().Replace("""", "'").Replace(System.Environment.NewLine, "")
        Dim spPrint As StringBuilder = New StringBuilder()
        spPrint.Append("<script type = 'text/javascript'>")
        spPrint.Append("window.onload = new function(){")
        spPrint.Append("var printWin = window.open('', '', 'left=0")
        spPrint.Append(",top=0,width=1000,height=600,status=0');")
        spPrint.Append("printWin.document.write(""")
        spPrint.Append(gridHTML)
        spPrint.Append(""");")
        spPrint.Append("printWin.document.close();")
        spPrint.Append("printWin.focus();")
        spPrint.Append("printWin.print();")
        spPrint.Append("printWin.close();};")
        spPrint.Append("</script>")
        ClientScript.RegisterStartupScript(Me.[GetType](), "PrintExcel", spPrint.ToString())
    End Using
End Sub

Private Function GetValue(doc As SpreadsheetDocument, cell As Cell) As String
    Dim value As String = cell.CellValue.InnerText
    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
        Return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(Integer.Parse(value)).InnerText
    End If
    Return value
End Function

Screenshot