contrôle des données-Import excel dans GridView en vb.net

Version imprimable

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.

Bonjour,

voilà, le contrôle RowDataBound me permet de sélectionner toutes les cellules
Code:

1 2 3 Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridViewXX.RowDataBound e.Row.BackColor = Drawing.Color.Yellow End Sub
mais je trouve une difficulté d'indexer les cellules car selement e.Row.Cells(0) qui marche mais quand je mets e.Row.Cells(1) ça me donne l'erreur suivante:

Citation:

L'argument spécifié n'était pas dans les limites de la plage des valeurs valides.
Nom du paramètre : index

Merci.

voilà,
ceci me permet de sélectionner les autres colonnes:
Code:

1 2 3 If e.Row.RowType = DataControlRowType.DataRow Then e.Row.Cells(1).BackColor = Drawing.Color.Blue End If
Merci.

09/02/2012, 17h52
pacifiquement
Bonjour,
Re :-)

j'essaie de vérifier si les champs d'une colonne sont vides, j'ai mis ce code dans mon GridView1_RowDataBound mais il ne marche pas :
Code:

1 2 3 If String.IsNullOrEmpty(Trim(CStr(e.Row.Cells(1).Text))) Then e.Row.Cells(1).BackColor = Drawing.Color.Red End If
une aide?
Merci par avance.

Bonjour,
voilà la solution pour tester si un champ d'une colonne est nul:

Code:

1
2
3
If (e.Row.Cells(1).Text.Replace("&nbsp;", "99")) = "99" Then
                e.Row.Cells(1).BackColor = Drawing.Color.Red
            End If

et Voilà mon code final de la page aspx:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<asp:FileUpload ID="FileUpload2" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Charger"
             />
<br />
<asp:Label ID="Label1" runat="server" Text="Afficher les noms des colonnes?" />
 
<asp:RadioButtonList ID="rbHDR" runat="server">
    <asp:ListItem Text ="Oui" Value ="Yes" Selected ="True" >
    </asp:ListItem>
    <asp:ListItem Text = "Non" Value = "No"></asp:ListItem>
</asp:RadioButtonList>
 
<asp:GridView ID="GridViewXX" runat="server" CssClass="grid" PageSize="15" OnPageIndexChanging ="PageIndexChanging" AllowPaging ="true">
</asp:GridView>

et du code vb:

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
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 &nbsp;
            If (e.Row.Cells(0).Text.Replace("&nbsp;", "99")) = "99" Then
                e.Row.Cells(0).BackColor = Drawing.Color.Red
            End If
            If (e.Row.Cells(1).Text.Replace("&nbsp;", "99")) = "99" Then
                e.Row.Cells(1).BackColor = Drawing.Color.Red
            End If
            If (e.Row.Cells(2).Text.Replace("&nbsp;", "99")) = "99" Then
                e.Row.Cells(2).BackColor = Drawing.Color.Red
            End If
 
        End If
 
    End Sub

et le Web.Config:

Code:

1
2
3
4
<connectionStrings>
<add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
<add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
</connectionStrings>

Code:

1
2
3
<appSettings>
  <add key ="FolderPath" value ="Files/"/>
</appSettings >

et je crée un dossier Files dans le même dossier qui rassemble ma page aspx.

Merci :-)
pacifiquement

10/02/2012, 15h18
pacifiquement
Bonjour,

une question,
comment puis je référencer les lignes pour pouvoir comparer si j'ai un champ en double?

Merci par avance.

Edit
j'ai mis ceci :
Code:

1 2 3 4 5 index = 0 While index < GridViewXX.Rows.Count GridViewXX.Rows(index).Cells(0).BackColor = Drawing.Color.Azure index = index + 1 End While
ça marche sauf que la dernière ligne n'est pas prise en compte
comment faire ?

merci

Bonjour,
Encore moi :-)
j'essaie de comparer les lignes d'une colonne, pour ceci j'ai mis:
Code:

1 2 3 4 5 6 7 8 9 Dim i As Integer = 1 While i < GridViewXX.Rows.Count If GridViewXX.Rows(0).Cells(0).Text = GridViewXX.Rows(i).Cells(0).Text Then GridViewXX.Rows(0).Cells(0).BackColor = Drawing.Color.Green GridViewXX.Rows(i).Cells(0).BackColor = Drawing.Color.Green End If i = i + 1 'index = index + 1 End While
mais ceci n'est pas pris en comptes ni pour la dernière ligne ni pour les autres pages de mon gridview.
c'est comme si la vérification se fait seulement sur la première page.
sachant que tout cela je le mets à l'intérieur d'un :
Code:

1 2 Protected Sub GridViewXX_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridViewXX.RowDataBound End Sub
une aide ? et merci