Without office in windows 2016 server, how to open excel file on web form?

Question

Hi all,
I want to import excel data in to web form gridview . I can run it on my PC ( vs 2019 + win 10 + office 2010) . But can't run at windows 2016 server (no office).
Is it possible not to install office and run these codes ?
Thank you so much.

    Protected Sub Excel_To_GridView2()

        Try
            Dim fileName As String = Server.HtmlEncode(ExcelUpload.FileName)
            Dim strConn As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\Temp inport files\" & fileName & ";Extended Properties='Excel 12.0;HDR=YES'"

            'MsgBox(strConn)

            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)

            Dim ds As DataSet = New DataSet

            da.Fill(ds, "dw")

            Me.GridView2.DataSource = ds
            Me.GridView2.DataMember = "dw"
            Me.GridView2.DataBind()


        Catch ex As Exception
            WebMessageBox(Me.Page, ex.Message)
            'MsgBox(ex.Message)
        End Try

    End Sub

Answer 1

Your options if the above is not working is to use OpenXML (which take sometime to learn), a free library such as EPPlus or a third party paid for library.

EPPlus example taken from here.

Option Infer On

Public Shared Function getDataTableFromExcel(ByVal path As String) As DataTable
    Using pck = New OfficeOpenXml.ExcelPackage()
        Using stream = File.OpenRead(path)
            pck.Load(stream)
        End Using
        Dim ws = pck.Workbook.Worksheets.First()
        Dim tbl As New DataTable()
        Dim hasHeader As Boolean = True ' adjust it accordingly( i've mentioned that this is a simple approach)
        For Each firstRowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column)
            tbl.Columns.Add(If(hasHeader, firstRowCell.Text, String.Format("Column {0}", firstRowCell.Start.Column)))
        Next firstRowCell
        Dim startRow = If(hasHeader, 2, 1)
        For rowNum = startRow To ws.Dimension.End.Row
            Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column)
            Dim row = tbl.NewRow()
            For Each cell In wsRow
                row(cell.Start.Column - 1) = cell.Text
            Next cell
            tbl.Rows.Add(row)
        Next rowNum
        Return tbl
    End Using
End Function