Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpload.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, rbHDR.SelectedItem.Text)
End If
End Sub
Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR 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, isHDR)
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()
End Sub
Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
Dim FileName As String = GridView1.Caption
Dim Extension As String = Path.GetExtension(FileName)
Dim FilePath As String = Server.MapPath(FolderPath + FileName)
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub
Date :
2014-02-05 08:05:56
By :
NJ
No. 3
Guest
Code (VBScript)
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpload.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, rbHDR.SelectedItem.Text)
End If
End Sub
Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR 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, isHDR)
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()
End Sub
Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
Dim FileName As String = GridView1.Caption
Dim Extension As String = Path.GetExtension(FileName)
Dim FilePath As String = Server.MapPath(FolderPath + FileName)
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub
Private Sub insert()
Dim idhole As String = ""
Dim sum As Double = 0
Dim Sqlcrs As String = ""
Dim crsState As String = ""
Dim sqlAddCrs As String = ""
Dim sqlAddDtl As String = ""
Dim cmd As New SqlCommand
Dim dsChk As New DataSet
Dim addChk As Boolean = True
For i = 0 To GridView1.Rows.Count - 1
sqlAddCrs = "INSERT INTO Table1 VALUES ('" + GridView1.Rows(i).Cells(0).Value + "',' " + GridView1.Rows(i).Cells(1).Value + "', '" + GridView1.Rows(i).Cells(2).Value + "')"
Label2.Text = (sqlAddCrs)
Try
With cmd
.CommandType = CommandType.Text
.CommandText = sqlAddCrs
.Connection = conn
.ExecuteNonQuery()
End With
Catch ex As Exception
' Label6.Text = ex.Message
Exit Sub
End Try
Next
End Sub