1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
| Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
If FileUpload2.HasFile Then
Dim FileName As String = Path.GetFileName(FileUpload2.PostedFile.FileName)
Dim Extension As String = Path.GetExtension(FileUpload2.PostedFile.FileName)
Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
Dim FilePath As String = Server.MapPath(FolderPath + FileName)
FileUpload2.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
GridViewXX.Caption = Path.GetFileName(FilePath)
GridViewXX.DataSource = dt
GridViewXX.DataBind()
End Sub
Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridViewXX.PageIndexChanging
Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
Dim FileName As String = GridViewXX.Caption
Dim Extension As String = Path.GetExtension(FileName)
Dim FilePath As String = Server.MapPath(FolderPath + FileName)
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)
GridViewXX.PageIndex = e.NewPageIndex
GridViewXX.DataBind()
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridViewXX.RowDataBound
If e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(0).BackColor = Drawing.Color.White
End If
If e.Row.RowType = DataControlRowType.DataRow Then
If Not IsDate(e.Row.Cells(2).Text) Then
e.Row.Cells(2).BackColor = Drawing.Color.Red
End If
'asp/gridview convert Nulls to
If (e.Row.Cells(0).Text.Replace(" ", "99")) = "99" Then
e.Row.Cells(0).BackColor = Drawing.Color.Red
End If
If (e.Row.Cells(1).Text.Replace(" ", "99")) = "99" Then
e.Row.Cells(1).BackColor = Drawing.Color.Red
End If
If (e.Row.Cells(2).Text.Replace(" ", "99")) = "99" Then
e.Row.Cells(2).BackColor = Drawing.Color.Red
End If
End If
End Sub |
Partager