Bonjour, je développe une application pour un inventaire magasin et pour le moment tout se passe bien. On gagne un temps fou par rapport à avant où l'on faisait l'inventaire sur papier...

J'ai juste un petit soucis pour l'application je dois extraire un fichier d'articles avec les références, les gencodes, les désignations... et la seule possibilité d'obtenir ce fichier, c'est de l'exporter sur Excel depuis le logiciel principal (gestion magasin et comptabilité de la boîte où je bosse) car je n'ai pas les accès au serveur sql.

Par la suite j'importe les données du fichier Excel vers mon serveur sql. J'ai environ 60000 lignes à insérées que je traite avec Interop et C#.

En gros pour faire simple, je parcours mon fichier avec un foreach puis je construis une requête INSERT vers mon serveur. Pour traiter l'ensemble de se fichier cela prend environ 15 minutes.

Le serveur SQL est installé sur un PC Windows 7 pro, 4 Go de RAM et un I3 4150. la version de SQL est 2008 R2 j'ai fais un test sur un PC perso sous Windows 10 Familial, 16 Go de RAM et un i7 et j'obtiens sensiblement les même performance.

Donc ma question, peut on améliorer ce processus par un traitement de masse ou autre chose me permettant de réduire se temps de traitement ?

note :  la création de ce fichier depuis le logiciel principale dure environ 10min ce à quoi j'ajoute le temps d'importation dans mon application 15 min, nous sommes sur un délai de 25 à 30 min entre chaque mise à jour. Même si nous utilisons cette fonction que très peu j'aimerai améliorer ce processus qui fais défaut.

Ce que vous laissez transparaître de votre architecture me laisse penser qu'il serait quand même judicieux que les personnes du "central" mettent quand même en place un mécanisme de réplication de données plus efficace que de la génération de fichier "Excel".

Il existe bien des technologies de réplications sélectives de données qui ont des performances et de la fiabilité qui n'ont rien à voir avec le bidouillage via fichier "Excel".

Après, si vous êtes dans une structure décentralisée et que le "central" est aussi réactif qu'un brontosaure sous Xanax, faut peut-être se démerder tout seul, malheureusement.

60 milles ligne, c'est vraiment pas gros (après c'est aussi fonction du nombre de colonne et de la nature des données stockées).

10 minutes pour générer un fichier, il doit faire plusieurs giga-octets en taille, sinon, soit il y a un problème dans l'application "centrale", ou le serveur "central" est une mobylette déguisée.

C'est bête d'optimiser les 15 dernière minutes quand 10 minutes sur la partie "centrale" doit venir de boulettes dans leur code.

J'espère que ce que vous appelez un fichier "Excel" est un fichier CSV car, sinon, vous (et le programme du serveur "central") perdez un temps fou à faire de la mise en page pour rien.

Si c'est un fichier XLS ou XLSX qui est généré par le programme du serveur "central", essayez de voir s'il ne peut pas générer un format bien plus léger, comme CSV, JSON, etc...

Si vous utilisez de l'Interop COM, vous perdez énormément en performance mais aussi en fiabilité. Avec les formats "légers", vous n'aurez pas besoin d'Interop et donc un très gros gain dans ces 2 domaines.

Faire des imports massifs avec des INSERT, c'est possible mais ce n'est vraiment pas ce qu'il y a de plus performant.

Votre besoin est si courant qu'il existe toute une panoplie de logiciels dédiés à ce genre de tâche : les ETL.

Exemple que donne Google pour insérer des données dans SQL Server depuis un fichier Excel :

https://docs.microsoft.com/fr-fr/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017

Il y aurait encore plus de choix si vous utilisez des formats plus légers, et ils seraient bien plus performants.

>nous sommes sur un délai de 25 à 30 min entre chaque mise à jour.

C'est énorme.

>Même si nous utilisons cette fonction que très peu j'aimerai améliorer ce processus qui fais défaut.

Attention, rationalisez vos efforts pour de l'optimisation. L'optimisation est un processus sans fin. Donc on optimise ce qui fait perdre le plus de temps.

Mais ici, je pense que l'utilisation d'ETLs vous fera rapidement gagner du temps sans trop d'efforts.

Salut, j'ai écouté tes conseils et j'ai réussi à passer pour l'importation de mon fichier à moins d' 1 seconde !!!

Cela pourrait aider d'autre personne pour le moment je transforme mon fichier Xlxs en Csv (comme tu me l'a conseillé plus haut) en séparateur point virgule. Donc pour le moment je n'ai pas écrit le code pour convertir le fichier en C# je le fais manuellement dans Excel.

Car oui je n'ai pas le choix que de travailler avec ce fichier, ensuite j'ai récup un code sur le net que j'ai adapté à mon programme.

Method qui ouvre, lit et import dans un DataTable mes données :

private void Csv(Import form, string fileName)
        {
            this.importView = new ImportView(form);
            this.importView.SetCurrentTask("Ouverture Fichier CSV");
            try
            {
                using (TextFieldParser csvReader = new TextFieldParser(fileName))
                {
                    this.importView.SetCurrentTask("Configuration lecture du fichier");
                    csvReader.SetDelimiters(new string[] { ";" });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    this.importView.SetCurrentTask("Configuration des en-tête de la table de donnée");
                    foreach (string column in colFields)
                    {
                        System.Data.DataColumn datecolumn = new System.Data.DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        this.data.Columns.Add(datecolumn);
                    }
                    while (!csvReader.EndOfData)
                    {
                        this.importView.SetCurrentTask("Lecture fichier");
                        string[] fieldData = csvReader.ReadFields();

                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = "";
                            }
                        }
                        this.data.Rows.Add(fieldData);
                    }
                }
            }
            finally
            {
                this.importView.SetCurrentTask("Insertion base de donnée");
                this.InsertBulk();
                this.importView.SetCurrentTask("Importation Terminé");
            }

        }

puis j'utilise SqlBulkCopy :

        private void InsertBulk()
        {
            this.sqlCnn = new SqlConnection(this.connexionString);
            this.importView.SetCurrentTask("Configuration BulkCopy SQL");
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(this.sqlCnn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);
            sqlBulkCopy.DestinationTableName = "Articles";
            this.importView.SetCurrentTask("Connexion base de donnée");
            this.sqlCnn.Open();
            this.importView.SetCurrentTask("Envoie requête");
            sqlBulkCopy.WriteToServer(this.data);
            this.importView.SetCurrentTask("Fermeture connexion à la base de donnée");
            this.sqlCnn.Close();
            this.importView.SetCurrentTask("Nettoyage table de donnée");
            this.data.Rows.Clear();
        }

Pour le coup j'enregistre 66000 articles en base de donnée comportant des données texte et float (référence, gencode, correspondance, désignation, stock et casier) en moins d'une seconde c'est énorme le gain de temps. 

Il ne reste plus qu'a traiter automatiquement le fichier xlxs fournis par notre central en csv, en gros cela consiste à supprimer l'entête du fichier (les 15 premières lignes) réécrire les noms de colonnes par ceux de ma table et pour finir enregistrer le fichier en csv (séparateur point virgule).

Un grand merci pour ces conseils, je vais tenter de contacter le central pour savoir si il n'est pas possible d'intégrer la sortie fichier csv pour certain module car le gain de temps est énorme.

Merci pour ce retour.

Il est clair que vous, mais aussi tous les autres sites, gagneriez à recevoir des données plus "légères" qu'un .xlsx.

Si le format de leur fichier Excel est "propre" (1 ligne d'en-tête mais peut-être qu'il est possible de paramétré ce truc), vous pouvez vous en servir directement comme une source de données, comme si c'était des tables d'une base de données relationnelle.

XLSX => DataTables dans un DataSet juste avec l'utilisation d'un DataAdapter :

https://www.connectionstrings.com/excel/

P.S.:

L'esquive des 15 premières lignes de doit pas trop poser de problème, finalement :

https://stackoverflow.com/questions/29267154/c-sharp-reading-excel-file-where-the-header-is-not-the-first-row

Voilà terminé mon module de mise à jour du fichier Articles je le post ici cela pourrait servir à d'autre (sachant que j'ai rencontrer des problèmes de formatage de données entre les CultureInfo fr-FR et en-US, les .(point) et les ,(virgules) et les ;(point-virgule) lol

using InventaireV1.Forms;
using InventaireV1.view;
using Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.VisualBasic.FileIO;
using System.IO;

namespace InventaireV1.model
{
    class XlsModel : MainSql
    {
        private Microsoft.Office.Interop.Excel.Application application = null;
        private Workbook workbook = null;
        private System.Data.DataTable data = new System.Data.DataTable("Articles");
        private ImportView importView;

        public XlsModel(string fileName, Import form, string context)
        {
            this.connexionString = ConfigurationManager.ConnectionStrings[context].ConnectionString;
            this.importView = new ImportView(form);
            this.Xlxs(form, fileName);
        }

        private void Xlxs(Import form, string fileName)
        {
            try
            {
                this.importView.SetCurrentTask("Ouverture Excel en tâche de fond");
                application = new Microsoft.Office.Interop.Excel.Application();
                application.DisplayAlerts = false;
                this.importView.SetCurrentTask("Ouverture du fichier : " + fileName);
                workbook = application.Workbooks.Open(fileName);

                foreach (Worksheet sheet in workbook.Worksheets)
                {
                    this.importView.SetCurrentTask("Configuration du fichier");
                    Microsoft.Office.Interop.Excel.Range range = sheet.Range[sheet.Cells[1, "A"], sheet.Cells[14, "F"]];
                    range.Delete(XlDeleteShiftDirection.xlShiftUp);
                    sheet.Cells[1, "A"] = "Reference";
                    sheet.Cells[1, "B"] = "Gencode";
                    sheet.Cells[1, "C"] = "Correspondance";
                    sheet.Cells[1, "D"] = "Designation";
                    sheet.Cells[1, "E"] = "Stock";
                    sheet.Cells[1, "F"] = "Casier";
                    this.importView.SetCurrentTask("Formatage des données terminé");
                }

                fileName = fileName.Substring(0, fileName.Length - 4);
                fileName += "csv";
                workbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Local: true);
            }
            finally
            {
                this.importView.SetCurrentTask("Fermeture Excel");
                workbook.Close(false);
                application.Quit();
            }
            this.importView.SetCurrentTask("Préparation mise à jour");
            this.Csv(form, fileName);
        }

        private void Csv(Import form, string fileName)
        {
            try
            {
                using (TextFieldParser csvReader = new TextFieldParser(fileName))
                {
                    csvReader.SetDelimiters(new string[] { ";" });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        System.Data.DataColumn datecolumn = new System.Data.DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        this.data.Columns.Add(datecolumn);
                    }
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        this.data.Rows.Add(fieldData);
                    }
                }
                this.importView.SetCurrentTask("Suppression fichier temporaire");
                if (File.Exists(fileName)) File.Delete(fileName);
            }
            finally
            {
                this.InsertBulk();
                this.importView.SetCurrentTask("Mise à jour terminé");
            }

        }

        private void InsertBulk()
        {
            this.sqlCnn = new SqlConnection(this.connexionString);
            this.sqlCnn.Open();
            this.sqlQuery = "DELETE FROM Articles";
            this.SetDataNonQuery();
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(this.sqlCnn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);
            sqlBulkCopy.DestinationTableName = "Articles";
            sqlBulkCopy.WriteToServer(this.data);
            this.sqlCnn.Close();
            this.data.Rows.Clear();
 
        }

    }
}

Alors attentions c'est une classe du l'application principal donc à adapter à vos besoin, les points les plus chiant à mon gout :

dans la clause finally de la methode Xlxs j'ai du utiliser workbook.Close(false); pour la bonne et simple raison que la method SaveAs pour je ne sais quel raison m'enregistre le fichier csv avec des ,(virgule) et du coup mes valeurs float de la colonne stock devenaient : 4.35 au lieu de 4,35 et plantait le BulkCopy. Obligé changer le CultureInfo avant l'insertion en base de donnée...

Dès que j'ai changé ça, j'ai retrouver un csv avec séparateur ;(point-virgule) j'ai conservé mes float en 4,35 plus besoin de changé de CultureInfo et surtout l'encodage de caractère foireux en-US.

Résultat le plus long de l'opération consiste à ouvrir et préparer mon fichier Excel (environ 20 bonnes seconde) puis de copier le tout en Bdd (1 seconde). Je suis passé de 15 minutes de mise à jour à moins d'une minute !

Dommage de toujours passer par l'InterOp Excel et toutes les emmerdes que cela provoque (temps de chargement, gestion des caractères de séparation, formatage des nombres, etc...), en passant par ADO.NET, je pense que vous auriez esquivé toutes ces difficultés.

Merci pour cet exemple complet.

-
Edité par bacelar 16 novembre 2018 à 17:12:27

oui je suis d'accord en CSV direct plus besoin de formater les données et un traitement de données très rapide, malheureusement pas le choix pour le moment. Je vais tout de même contacter la plateforme pour demander si il possible d'avoir une sortie csv pour l'édition de ses fichiers on verra bien.

En tout cas merci une fois de plus de m'avoir mis sur la piste !

Si le fichier xlsx n'est pas trop "exotique", avec ADO.NET, vous pouvez l'utiliser directement, aussi simplement qu'un CSV.

S'il est exotique, vous pouvez utiliser Open-XML SDK pour directement accéder aux données dans le fichier Excel, sans avoir à lancer "en scred" un Excel quand vous utilisez l'interOp Office.