Click here to Skip to main content
14,601,157 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi all

Im trying to read excel file in web application i used many set of codes by searching from internet but in each case error arising.
1.Cannot find sheet1
2.cannot update data.its readonly
3.External table is not in the expected format.
Can anyone suggest any successful coding for the same

Thanks in Advance
Updated 5-Jun-12 1:39am
Rate this:
Please Sign up or sign in to vote.

Solution 1


Please upload the Excel document:<asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="btnSave" runat="server" Text="Save" />
    <br />
    <asp:GridView ID="GridView1" runat="server">

Code Behind (VB.Net)

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If FileUpload1.HasFile Then
            Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
            Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")

            Dim FilePath As String = Server.MapPath(FolderPath & "\" & FileName)
            Import_To_Grid(FilePath, Extension)
        End If
    End Sub

Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String)
        Dim conStr As String = ""
        Select Case Extension
            Case ".xls"
                'Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString()
                Exit Select
            Case ".xlsx"
                'Excel 07
                conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString()
                Exit Select
        End Select

        conStr = String.Format(conStr, FilePath)

        Dim connExcel As New OleDbConnection(conStr)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        Dim dt As New DataTable()

        cmdExcel.Connection = connExcel

        'Get the name of First Sheet
        Dim dtExcelSchema As DataTable
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()

        'Read Data from First Sheet
        cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
        oda.SelectCommand = cmdExcel

        'Bind Data to GridView

        GridView1.Caption = Path.GetFileName(FilePath)
        GridView1.DataSource = dt
    End Sub

Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Rate this:
Please Sign up or sign in to vote.

Solution 2

Refer the following links, it will helpful for u.

1. Read the single sheet (Sheet1 ) details,
2. Read all the sheet from an excel workbook[^][^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100