Discussion :

[TomDuBouchon]

Bonjour à tous,

J'essaie de trouver une solution pour notre développeur ASP.Net, mais le développement Web n'est pas mon métier. Désolé d'avance donc pour toute future imprécision de ma part.

Donc, pour ce qui est de mon problème : Nous avons une page sur un site devant permettre à un utilisateur d'exporter vers Excel le contenu d'une table SQL Server.
Une fois le fichier récupéré, l'utilisateur doit pouvoir ajouter des lignes depuis Excel pour ensuite importer ce même fichier et mettre à jour la table SQL Server d'origine avec les nouvelles données.

Fondamentalement, pas de problème sur le code, il fonctionne bien, au travers d'une connexion oledbconnection.

Le problème que nous avons c'est qu'apparemment, lors de l'export, un "marqueur" END OF FILE semble se glisser dans le fichier Excel à la fin de la dernière ligne de données.

Ceci a pour conséquence que, lorsqu'un utilisateur ajoute des données après cette dernière ligne, et qu'il essaie d'importer son fichier, toutes les lignes qu'il a saisi ne sont pas importées, seules celles qui étaient présentes après l'export initial le sont.

De même, si on ajoute des lignes après la dernière ligne importée, et qu'on supprime la dernière ligne importée, le marqueur "END OF FILE" remonte d'un cran et ne permet toujours pas d'importer correctement l'ensemble des données.

Pour être plus clair voici un exemple :

Dans la table SQL Server :
1 | TOTO
2 | TATA
3 | TUTU

Après export, le fichier Excel :
1 | TOTO
2 | TATA
3 | TUTU

Après ajout de nouvelles lignes dans Excel :
1 | TOTO
2 | TATA
3 | TUTU
4 | TEST
5 | TEST2

Lors de l'import, seules les lignes 1, 2 et 3 sont importées.

Si on supprime la ligne 3 :
1 | TOTO
2 | TATA
4 | TEST
5 | TEST2

Lors de l'import, seules les lignes 1 et 2 sont importées.

Si on supprime toutes les lignes de l'import initial (et qu'on ne garde que les en-têtes) :
4 | TEST
5 | TEST2

Lors de l'import, aucune ligne n'est importée.

En revanche, si on insère des données entre les lignes 2 et 3 :
1 | TOTO
2 | TATA
4 | TEST
5 | TEST2
3 | TUTU

Désormais, toutes les lignes sont bien importées.

C'est ce qui me fait penser à un marqueur END OF FILE.

Ma question sera donc : comment contourner le problème ou s'en affranchir ?

Merci d'avance

[Immobilis]

Salut,

J'aurais tendance à regarder du côté de Office. Quelle version? Quelle culture: en-US, en-GB, fr-FR?

Plus sûr: CSV (+ BulkInsert ou Update), XML (avec Office 2007).

A+

[TomDuBouchon]

Bonjour et merci pour votre réponse,

Concernant Excel, il s'agit d'une version 2003 FR-fr.

Pour ce qui est de changer le format d'export, je suis tout à fait d'accord avec vous malheureusement il s'agit d'une prérogative client...

Et concernant le problème sous Office, je ne pense pas car j'ai déjà testé de modifier le fichier de sortie avec une version 2003, 2007 et 2010 et j'ai le même problème.
De plus, cela n'expliquerait pas pourquoi l'import fonctionne correctement si on insère des lignes entre les lignes existantes...

Lors de l'import, l'instruction Data.Rows.Count renvoie 3, quelquesoit le nombre de lignes qu'on ait insérées manuellement...

[Immobilis]

Peux-tu poster le code d'import et un fichier exemple?

Merci

[TomDuBouchon]

Voici le code d'export du fichier :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
        protected void ExportExcel()
        {
            Dictionary<string, string> friendlyNames;
 
            using (DataPage dataPage = new DataPage())
            {
                friendlyNames = dataPage.FriendlyNames;
            }
 
            string friendlyName = "Transco";
 
            string connectionString = ConnectionStrings.LoadConnectionString(ConnectionStringType.MainSqlServer);
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
 
                using (SqlCommand getData = new SqlCommand("select top 2000 * from Transco", sqlConnection))
                {
                    using (SqlDataReader dataReader = getData.ExecuteReader())
                    {
                        string templatesPath = Settings.ExcelTemplatesPath;
                        string emptyBookFilePath = Path.Combine(templatesPath, "book.xls");
                        string tableTemplatePath = Path.Combine(templatesPath, friendlyName + ".xls");
 
                        bool hasTemplate = File.Exists(tableTemplatePath);
 
                        string tempFileName = Path.GetTempFileName();
                        File.Delete(tempFileName);
 
                        File.Copy(hasTemplate ? tableTemplatePath : emptyBookFilePath, tempFileName);
 
                        string excelProvider = ConnectionStrings.LoadConnectionString(ConnectionStringType.ExcelProvider);
                        string excelConnectionString = string.Format(CultureInfo.InvariantCulture, excelProvider, tempFileName);
 
                        using (OleDbConnection oleConnection = new OleDbConnection(excelConnectionString))
                        {
                            oleConnection.Open();
 
                            string SafefriendlyName = friendlyName + "_SAF";
 
                            if (!hasTemplate)
                            {
                                string createTableQuery = string.Format(CultureInfo.InvariantCulture, @"create table {0} ([compte] nvarchar, [devise] nvarchar, [societe] nvarchar, [banque] nvarchar)", SafefriendlyName);
 
                                using (OleDbCommand createTable = new OleDbCommand(createTableQuery, oleConnection))
                                {
                                    createTable.ExecuteNonQuery();
                                }
                            }
 
                            string addDataQuery = string.Format(CultureInfo.InvariantCulture, "insert into {0} ([Compte], [devise], [societe], [banque]) values (@compte, @devise, @societe, @banque)", SafefriendlyName);
 
                            while (dataReader.Read())
                            {
                                using (OleDbCommand addData = new OleDbCommand(addDataQuery, oleConnection))
                                {
                                    addData.Parameters.AddWithValue("@compte", dataReader["COMPTE"]);
                                    addData.Parameters.AddWithValue("@devise", dataReader["DEVISE"]);
                                    addData.Parameters.AddWithValue("@societe", dataReader["SOCIETE"]);
                                    addData.Parameters.AddWithValue("@banque", dataReader["BANQUE"]);
 
                                    int countAffectedRows = addData.ExecuteNonQuery();
                                    Debug.Assert(countAffectedRows == 1, string.Format(CultureInfo.InvariantCulture, "Wrong number of rows affected: {0} instead of 1.", countAffectedRows));
                                }
                            }
                        }
 
                        this.Response.Clear();
                        this.Response.Buffer = true;
                        this.Response.AddHeader("content-disposition", string.Format(CultureInfo.InvariantCulture, @"attachment; filename=""{0}.xls""", friendlyName.Replace(@"""", string.Empty)));
                        this.Response.ContentType = "application/vnd.ms-excel";
                        this.Response.WriteFile(tempFileName);
                    }
                }
            }
        }

Et le code d'import :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 void ExcelFileUpload_Click(object sender, EventArgs e)
        {
            try
            {
                string tempFileFullPath = Path.GetTempFileName();
                if (this.ExcelFileUpload.HasFile)
                {
                    this.ExcelFileUpload.SaveAs(tempFileFullPath);
                }
 
                DataTable data = new DataTable();
                using (OleDbConnection oleConn = new OleDbConnection(String.Format(ConnectionStrings.LoadConnectionString(ConnectionStringType.ExcelProvider), tempFileFullPath)))
                {
                    oleConn.Open();
                    DataTable dbSchema = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dbSchema == null || dbSchema.Rows.Count < 1)
                    {
                        throw new Exception("Error: Could not determine the name of the first worksheet.");
                    }
 
                    string WorkSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
 
                    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheetName + "] ORDER BY [Compte]", oleConn);                    
                    data.TableName = WorkSheetName;
 
                    da.Fill(data);
 
                    //data.AsEnumerable().Where(row => !row.ItemArray.All(f => f is System.DBNull || String.IsNullOrEmpty(f.ToString()))).CopyToDataTable();
 
                    oleConn.Close();
                }
 
                int addedRows = 0;
                using (SqlConnection sqlConn = new SqlConnection(ConnectionStrings.LoadConnectionString(ConnectionStringType.MainSqlServer)))
                {
                    sqlConn.Open();
                    SqlCommand command = sqlConn.CreateCommand();
                    SqlTransaction transaction = sqlConn.BeginTransaction("Import");
                    command.Connection = sqlConn;
                    command.Transaction = transaction;
 
                    try
                    {
                        // Supprime toutes les données
                        command.CommandText = "TRUNCATE TABLE TRANSCO";
                        command.ExecuteNonQuery();
 
                        // Réinsère les nouvelles données
                        command.CommandText = @"
                            INSERT INTO TRANSCO (compte, devise, societe, banque) 
                            VALUES(@compte, @devise, @societe, @banque)
                        ";
 
                        foreach (DataRow row in data.Rows)
                        {
                            command.Parameters.AddWithValue("@compte", row["Compte"] ?? DBNull.Value);
                            command.Parameters.AddWithValue("@devise", row["Devise"] ?? DBNull.Value);
                            command.Parameters.AddWithValue("@societe", row["Societe"] ?? DBNull.Value);
                            command.Parameters.AddWithValue("@banque", row["Banque"] ?? DBNull.Value);
                            addedRows += command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
 
                        this.ImportResponse = String.Format("Table recréée.<br />{0} lignes insérées.", addedRows);
                        this.ImportSuccess = true;
                        transaction.Commit();
                        this.DataBind();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                this.ImportResponse = String.Format("Import échoué, vérifiez le format du fichier.<br />Exception: " + ex.Message);
                this.ImportSuccess = false;
            }
        }

Le fichier exemple en PJ.

[Immobilis]

Bizarre... J'ai repris ton code presque exactement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
string tempFileFullPath = Path.Combine(Path.GetTempPath(), this.ExcelFileUpload.FileName);
if (this.ExcelFileUpload.HasFile)
{
    this.ExcelFileUpload.SaveAs(tempFileFullPath);
}
 
DataTable data = new DataTable();
using (OleDbConnection oleConn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", tempFileFullPath)))
{
    oleConn.Open();
    DataTable dbSchema = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    if (dbSchema == null || dbSchema.Rows.Count < 1)
    {
        throw new Exception("Error: Could not determine the name of the first worksheet.");
    }
 
    string WorkSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
 
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheetName + "] ORDER BY [Compte]", oleConn);
    data.TableName = WorkSheetName;
 
    da.Fill(data);
 
    //data.AsEnumerable().Where(row => !row.ItemArray.All(f => f is System.DBNull || String.IsNullOrEmpty(f.ToString()))).CopyToDataTable();
 
    oleConn.Close();
}

J'ai 4 lignes... Il en manque une.

J'ai Excel 2007 installé sur ma machine.

Que contient ta datatable?

A+