changing the way of coding so that MS Excel is not to be needed to installed on server in asp.net

Question

I have started to deveop a website using asp.net.

I need to fetch some data from excel to display it to the client.

I am hosting my site on somee.com so that I can freely host it.

But on the server of somee.com Excel is not installed.

I have written some code for my website to display the data from from excel.

Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
    Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(FileUploadPath & sender.text)
    Dim xlWorksheet As Microsoft.Office.Interop.Excel._Worksheet = xlWorkbook.Sheets(SheetName)
    Dim xlRange As Microsoft.Office.Interop.Excel.Range = xlWorksheet.UsedRange

    Dim rowCount As Integer = xlRange.Rows.Count
    Dim colCount As Integer = xlRange.Columns.Count

    Dim tbl As New DataTable()

    For i As Integer = 1 To rowCount
        tbl.Rows.Add()
    Next

    For i As Integer = 1 To colCount
        tbl.Columns.Add()
    Next

    If rowCount > 1 Or colCount > 1 Then
        For i As Integer = 1 To rowCount
            For j As Integer = 1 To colCount
                tbl.Rows(i - 1)(j - 1) = xlRange.Value2(i, j)
            Next j
        Next i
    End If

    gvReadFiles.AutoGenerateColumns = True
    gvReadFiles.DataSource = tbl
    gvReadFiles.DataBind()

    xlApp.ActiveWorkbook.Close(False, Session(FileUploadPath & sender.text))
    xlApp.Quit()

    xlWorkbook = Nothing
    xlApp = Nothing

Now I need to have some changes in code so it is not excel dependent.

Can you help me?

Never use Office Interop from an ASP.NET application. It is not supported, doesn't work consistently, and may even violate your license agreement with Microsoft. — John Saunders, May 04 '13 at 20:30
Check this question http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp. Hope this helps — Andrei, May 04 '13 at 21:17

score 1 · Accepted Answer · answered May 05 '13 at 18:19

I solved it using google for more than 2 hours.

Here is the code

Dim objConn As OleDbConnection = Nothing
Dim dt As System.Data.DataTable = Nothing

Try

    Dim connString As String = ""

    If Extension = "xls" Or Extension = ".xls" Then

        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileUploadPath & sender.text & ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;HDR=No;IMEX=1" + Convert.ToChar(34).ToString() + ""

    ElseIf Extension = "xlsx" Or Extension = ".xlsx" Then

        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileUploadPath & sender.text & ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 12.0;HDR=No;IMEX=1" + Convert.ToChar(34).ToString() + ""

    End If

    objConn = New OleDbConnection(connString)

    objConn.Open()

    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

    Dim firstSheetName As String = "Sheet1"

    If Not dt Is Nothing Then
        Dim excelSheets As [String]() = New [String](dt.Rows.Count - 1) {}

        ' Add the sheet name to the string array.
        For Each row As DataRow In dt.Rows
            firstSheetName = row("TABLE_NAME").ToString().Substring(0, row("TABLE_NAME").ToString.Length - 1)
            Exit For
        Next

    End If

    Return firstSheetName

Catch ex As Exception

    Return "Sheet1"

Finally

    If objConn IsNot Nothing Then
        objConn.Close()
        objConn.Dispose()
    End If
    If dt IsNot Nothing Then
        dt.Dispose()
    End If

End Try

changing the way of coding so that MS Excel is not to be needed to installed on server in asp.net

1 Answers1