如何使用ASP.NET读取Excel文件并在网格中显示 [英] How to read excel file and display in grid using asp.net

问题描述

大家好

我试图在Web应用程序中读取excel文件，我通过从Internet搜索使用了许多代码集，但在每种情况下都会出现错误.
1.找不到工作表1
2.无法更新数据.其只读信息
3.外部表的格式不正确.
任何人都可以建议对相同的代码进行任何成功的编码

在此先感谢
Amrutha

解决方案

ASP:

请上传Excel文档:<   asp:FileUpload     ID   ="     runat   =" 服务器" / > 
        <   asp:Button     ID   ="   runat   服务器" 文本  保存" / <   br    > 
    <   asp:GridView     ID   ="   runat   服务器" <  /asp:GridView  >  

隐藏代码(VB.Net)

 受保护的  Sub  btnSave_Click( ByVal 发​​件人 As  对象， ByVal  e  As  System.EventArgs) Handles  btnSave.Click
        如果 FileUpload1.HasFile 然后
             Dim 文件名 As  字符串 = Path.GetFileName (FileUpload1.PostedFile.FileName)
             Dim 扩展名 As  字符串 = Path.GetExtension (FileUpload1.PostedFile.FileName)
             Dim  FolderPath  As  字符串 = ConfigurationManager.AppSettings (" )

             Dim  FilePath  As   String  = Server.MapPath (FolderPath& " & FileName)
            FileUpload1.SaveAs(FilePath)
            Import_To_Grid(文件路径，扩展名)
        结束 如果
    结束 子 

 私有 子 Import_To_Grid( ByVal  FilePath  As  字符串， ByVal 扩展名 As  字符串)
         Dim  conStr  As  字符串 = " 
        选择 案例扩展
            案例 " 
                '  Excel 97-03 
                conStr = ConfigurationManager.ConnectionStrings(" ).ConnectionString()
                退出 选择
            案例 " 
                '  Excel 07 
                conStr = ConfigurationManager.ConnectionStrings(" ).ConnectionString()
                退出 选择
        结束 选择

        conStr = 字符串 .Format(conStr，FilePath)

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

        cmdExcel.Connection = connExcel

        ' 获取第一张纸的名称
        connExcel.Open()
         Dim  dtExcelSchema  As  DataTable
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables，没什么)
         Dim  SheetName  As  字符串 = dtExcelSchema.Rows ( 0 )(" ) .ToString()
        connExcel.Close()

        ' 从第一张纸中读取数据
        connExcel.Open()
        cmdExcel.CommandText = " &工作表名称和" 
        oda.SelectCommand = cmdExcel
        oda.Fill(dt)
        connExcel.Close()

        ' 将数据绑定到GridView 

        GridView1.Caption = Path.GetFileName(FilePath)
        GridView1.DataSource = dt
        GridView1.DataBind()
        CreateTextboxGridview()
    结束 子 

进口:

 导入 System.Data.Sql
导入 System.Data.SqlClient
导入 System.Data
导入 System.Data.OleDb
导入 System.IO 

http://www. dotnetfunda.com/forums/thread9964-how-to-read-excel-excel-file-and-display-in-grid-using-aspnet.aspx [ http://www.dotnetcurry.com/ShowArticle.aspx?ID=138 [ ^ ]

http://forums.asp.net/t/1039391.aspx/1 [ ^ ]

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

解决方案

ASP:

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)

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

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:

Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Upload Excel Spreadsheet File to Server, then Display Record(s) in Gridview[^]

http://www.dotnetfunda.com/forums/thread9964-how-to-read-excel-file-and-display-in-grid-using-aspnet.aspx[^]

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[^]

这篇关于如何使用ASP.NET读取Excel文件并在网格中显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！