contrôle des données-Import excel dans GridView en vb.net
Salut
ce bout de code me permet d'importer un fichier excel dans un GridView:
Code:
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
| 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 GridViewPageEventArgs)
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 |
Et je veux maintenant faire un contrôle des données au moment de l'import (exp:champs nuls).
j'ai essayé d'ajouter ceci , dans Import_To_Grid, seulement pour voir si la sélection de la cellule marche mais cela ne donne aucun effet:
Code:
1 2 3
| For Each gvr As GridViewRow In GridViewXX.Rows
GridViewXX.Columns(1).ControlStyle.BackColor = Drawing.Color.Yellow
Next gvr |
une idée ?
et Merci par avance.