Click here to Skip to main content
15,891,905 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Amrutha
Posted
Updated 5-Jun-12 1:39am
v2

ASP:

XML
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">
    </asp:GridView>



Code Behind (VB.Net)

VB
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)
            FileUpload1.SaveAs(FilePath)
            Import_To_Grid(FilePath, Extension)
        End If
    End Sub


VB
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
        connExcel.Open()
        Dim dtExcelSchema As DataTable
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
        connExcel.Close()

        'Read Data from First Sheet
        connExcel.Open()
        cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
        oda.SelectCommand = cmdExcel
        oda.Fill(dt)
        connExcel.Close()

        'Bind Data to GridView

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




Imports:
VB
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
 
Share this answer
 
v5
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

http://www.dotnetcurry.com/ShowArticle.aspx?ID=138[^]

http://forums.asp.net/t/1039391.aspx/1[^]
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900