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"
conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString()
Exit Select
Case ".xlsx"
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
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
connExcel.Close()
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