question

KerryOu-8610 avatar image
0 Votes"
KerryOu-8610 asked KerryOu-8610 commented

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

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
dotnet-visual-basicdotnet-aspnet-webforms
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered KerryOu-8610 commented

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


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your reply. I will find another way.

0 Votes 0 ·

Hi,
It doesn"t require a full Office installation. You could use https://www.microsoft.com/en-us/download/details.aspx?id=54920 though my perosnal preference would be still to use the OpenXML SDK.
Also you need to install the matching version (ie 64 bit if your app runs in 64 bit mode which should be the default and the 32 bit version if your web app is configured to run with 32 bit mode).

1 Vote 1 ·

Yes, I already installed this. It's working. Thanks for your reply.

0 Votes 0 ·
Show more comments