oledb ecriture dans fichier excel

bonjours,

Alors voila je suis confronté a un nouveau problème, il faut que j'ecrive dans un fichier excel. Cela se passe bien mais un fois que j'ai ecrie et que j'ai refermé la connection je n'arrive pas a ouvrir manuellement le fichier excel concerné. Voila les bout de code qui correspondent a ce que je veux faire :

la classe oledb pour lire ecrire dans excel :

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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
 
#region Imports
 
using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
 
#endregion
 
    /// <summary>
    /// A client which interfaces to excel work books
    /// </summary>
    public class MicrosoftExcelClient
    {
 
        #region Variable Declarations
 
        /// <summary>
        /// Current message from the client
        /// </summary>
        string m_CurrentMessage = "";
 
        /// <summary>
        /// The file path for the source excel book
        /// </summary>
        string m_SourceFileName;
 
        /// <summary>
        /// Connects to the source excel workbook
        /// </summary>
        OleDbConnection m_ConnectionToExcelBook;
 
        /// <summary>
        /// Reads the data from the document to a System.Data object
        /// </summary>
        OleDbDataAdapter m_AdapterForExcelBook;
 
        #endregion
 
        #region Constructor Logic
 
        /// <summary>
        /// Parameterized constructor .. specifies path
        /// </summary>
        /// <param name="iSourceFileName">The source filename</param>
        public MicrosoftExcelClient(string iSourceFileName)
        {
            this.m_SourceFileName = iSourceFileName;
        }
 
        #endregion
 
        #region Properties
 
        /// <summary>
        /// Gets the current messages from the client
        /// </summary>
        public string CurrentMessage
        {
            get { return this.m_CurrentMessage; }
        }
 
        /// <summary>
        /// Property that gets / sets the current source excel book
        /// </summary>
        public string FileName
        {
            get { return this.m_SourceFileName; }
            set { this.m_SourceFileName = value; }
        }
 
        #endregion
 
        #region query methods
 
        /// <summary>
        /// Runs a non query database command such as UPDATE , INSERT or DELETE
        /// </summary>
        /// <param name="iQuery">The required query</param>
        /// <returns></returns>
        public bool runNonQuery(string iQuery)
        {
            try
            {
                //Declares new Command to query
                OleDbCommand selectCommand = new OleDbCommand(iQuery);
 
                //Declares new connection to an Excel workbook
                selectCommand.Connection = this.m_ConnectionToExcelBook;
 
                //Data adapter
                this.m_AdapterForExcelBook = new OleDbDataAdapter();
                m_AdapterForExcelBook.UpdateCommand = selectCommand;
                selectCommand.ExecuteNonQuery();
 
                return true;
 
            }
            catch (Exception ex)
            {
                this.m_CurrentMessage = "Erreur " + ex.Message;
                return false;
            }
        }
 
        public bool runNonQuery(OleDbCommand selectCommand)
        {
            try
            {
                //Declares new Command to query
                //OleDbCommand selectCommand = new OleDbCommand(iQuery);
 
                //Declares new connection to an Excel workbook
                selectCommand.Connection = this.m_ConnectionToExcelBook;
 
                //Data adapter
                this.m_AdapterForExcelBook = new OleDbDataAdapter();
 
                m_AdapterForExcelBook.UpdateCommand = selectCommand;
                selectCommand.ExecuteNonQuery();
 
                return true;
 
            }
            catch (Exception ex)
            {
 
                this.m_CurrentMessage = "Erreur " + ex.Message;
                return false;
            }
        }
 
        /// <summary>
        /// Reads data as per the user query
        /// </summary>
        /// <param name="iQuery">The speicfic Query</param>
        /// <returns>Returns a dataTable, with results from query</returns>
        public DataTable readForSpecificQuery(string iQuery)
        {
            try
            {
                //Declares new dataTable, which will be returned filled from query
                DataTable returnDataObject = new DataTable();
 
                //Command
                OleDbCommand selectCommand = new OleDbCommand(iQuery);
                selectCommand.Connection = this.m_ConnectionToExcelBook;
 
                //DataAdapter for our excel workbook
                this.m_AdapterForExcelBook = new OleDbDataAdapter();
 
                //Applies the Select command to the oledbdataadapter and fills it
                this.m_AdapterForExcelBook.SelectCommand = selectCommand;
                this.m_AdapterForExcelBook.Fill(returnDataObject);
 
                //Sets a success message if query returned good results
                this.m_CurrentMessage = "Succès - " + returnDataObject.Rows.Count + " records importés ";
 
                return returnDataObject;
            }
            catch (Exception ex)
            {
                //Displays an error message in the statut bar.
                this.m_CurrentMessage = "ERREUR: " + ex.Message;
                return null;
            }
        }
 
        /// <summary>
        /// Reads an entire excel sheet from an opened excel workbook
        /// </summary>
        /// <param name="iSheetName">The name of the excel worksheet</param>
        /// <returns>Returns a DataTable filled</returns>
        public DataTable readEntireSheet(string iSheetName)
        {
            try
            {
                //Declares new DataTable to be returned
                DataTable returnDataObject = new DataTable();
 
                //New Command for our Select
                OleDbCommand selectCommand = new OleDbCommand("select * from [" + iSheetName + "$]");
                selectCommand.Connection = this.m_ConnectionToExcelBook;
 
                this.m_AdapterForExcelBook = new OleDbDataAdapter();
 
                this.m_AdapterForExcelBook.SelectCommand = selectCommand;
                this.m_AdapterForExcelBook.Fill(returnDataObject);
 
                this.m_CurrentMessage = "Succès - " + returnDataObject.Rows.Count + " records importés ";
 
                return returnDataObject;
 
            }
            catch (Exception ex)
            {
                //Displays error message
                this.m_CurrentMessage = "ERREUR " + ex.Message;
                return null;
            }
 
        }
 
        #endregion
 
        #region Open read connection
 
        /// <summary>
        /// Opens a READ ONLY connection to the source excel document
        /// </summary>
        /// <returns>Returns nothing</returns>
        public void openConnection()
        {
            String chaineConnexion = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.m_SourceFileName + ";Extended Properties=\"Excel 8.0;IMEX=1\"";//
 
            try
            {
                this.m_ConnectionToExcelBook = new OleDbConnection(chaineConnexion);
                this.m_ConnectionToExcelBook.Open();
                this.m_CurrentMessage = "Succès - La connexion à la source a été établie";
            }
            catch (Exception ex)
            {
                //If Excel file didn't open, return an error message
                this.m_CurrentMessage = "Erreur lors de l'ouverture du fichier";
            }
        }
 
        /// <summary>
        /// Closes the connection to the source excel document
        /// </summary>
        /// <returns></returns>
 
        #endregion
 
        #region open write connection
        /// <summary>
        /// Opens a WRITE connection to the source excel document.
        /// </summary>
        /// <returns></returns>
        public void openWriteConnection()
        {
            String chaineConnexion = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.m_SourceFileName + ";Extended Properties=\"Excel 8.0;IMEX=2\"";
 
            try
            {
                this.m_ConnectionToExcelBook = new OleDbConnection(chaineConnexion);
                this.m_ConnectionToExcelBook.Open();
                this.m_CurrentMessage = "Succès - La connexion à la source a été établie";
            }
            catch (Exception ex)
            {
                //Error message if connection didn't open
                this.m_CurrentMessage = "Erreur lors de l'ouverture du fichier";
            }
        }
        #endregion
 
        #region Close connection
 
        public bool closeConnection()
        {
            try
            {
                this.m_ConnectionToExcelBook.Close();
                this.m_CurrentMessage = "Succès - Connection fermée";
            }
            catch (Exception ex)
            {
                //Error message if closing didn't success
                this.m_CurrentMessage = "ERREUR " + ex.Message;
                return false;
            }
            return true;
        }
 
        #endregion
    }

la page ou j'utilise le cette dernière :

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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
 
public partial class ExportationExcel : System.Web.UI.Page
{
    MicrosoftExcelClient msExcelClient;
    SqlInterface db = new SqlInterface();
 
    protected void Page_Load(object sender, EventArgs e)
    {
 
 
 
    }
 
// bouton qui execute la procedure d'ecriture dans le fichier + ouverture et fermeture du fichier
    protected void Button1_Click(object sender, EventArgs e)
    {
        String TempFileName = Server.MapPath("~") + "\\temp\\";
 
        msExcelClient = new MicrosoftExcelClient(TempFileName + "matrice_import_article.xls");
        msExcelClient.openWriteConnection();
        insertValeur();
        msExcelClient.closeConnection();
 
 
    }
 
    protected void insertValeur()
    {
        string where = " Produit.id_type_produit = 2 and deleted = 0 order by Produit.id_rayon,Produit.id_gamme";
 
        DataSet dt = new DataSet();
        dt = db.select("Produit.id_produit","Produit", where);
        for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
        {
// je récupère les inforamtion a inserer dans excel
 
            Hashtable infoArticle = new Hashtable();
            infoArticle = Article.GetInfoProduit(dt.Tables[0].Rows[i]["id_produit"].ToString());
            Hashtable infoGeneral = new Hashtable();
            infoGeneral = Article.GetInfoGeneral(dt.Tables[0].Rows[i]["id_produit"].ToString());
            Hashtable infoNombre = new Hashtable();
            infoNombre = Article.GetInfoProduitNombre(dt.Tables[0].Rows[i]["id_produit"].ToString());
            Hashtable infoComplementaire = new Hashtable();
            infoComplementaire = Article.GetInfoComplementaire(dt.Tables[0].Rows[i]["id_produit"].ToString());
            Hashtable infoGamme = new Hashtable();
            infoGamme = Gamme.getGamme(infoArticle["id_gamme"].ToString());
            Hashtable infoRegion = new Hashtable();
            infoRegion = Region.getRegion(infoArticle["id_region"].ToString());
            Hashtable infoImage = new Hashtable();
            infoImage = Article.GetInfoImage(dt.Tables[0].Rows[i]["id_produit"].ToString());
 
            infoGeneral["introduction_produit_us"] = infoGeneral["introduction_produit"];
 
//préparation de ma requete parametré
 
            string champ = "(ref_principal,designation_produit,designation_produit_US,prix_vente,positionnement,est_promo,taux_remise,num_gamme,num_region,introduction_produit,introduction_produit_us"; //,desc_resume,desc_resume_us
            champ += ",poid,id_type_tva,nbr_produit,nbr_alerte,nbr_indispo,delai,image,vignette,image_zoom,prix_produit_visible,txt_prix_non_visble,txt_prix_non_visble_us,produit_visible,produit_dispo,txt_non_dispo,txt_non_dispo_us,";
            champ += "balise_ref,balise_ref_us,desc_ref,desc_ref_us,mot_cle_ref,mot_cle_ref_us,titre_ref,titre_ref_us,choix_opt,titre_opt,choix_opt_us)";//,remise_euro,mise_en_page
 
            string param = "(@ref_principal,@designation_produit,@designation_produit_US,@prix_vente,@positionnement),@promo,@taux_remise,@numero_gamme,@numero_region,@introduction_produit,@introduction_produit_us"; //,@desc_resume,@desc_resume_us
            param += ",@poid,@id_type_tva,@nbr_produit,@nbr_alerte,@nbr_indispo,@delai,@image,@vignette,@image_zoom,@prix_visible,@txt_prix_non_visble,@txt_prix_non_visble_us,@article_visible,@dispo_vente,@txt_non_dispo,@txt_non_dispo_us,";
            param += "@balise_ref,@balise_ref_us,@desc_ref,@desc_ref_us,@mot_cle_ref,@mot_cle_ref_us,@titre_ref,@titre_ref_us,@choix_opt,@titre_opt,@choix_opt_us)";//,@remise_euro,@mise_en_page
 
            OleDbCommand commande = new OleDbCommand("Insert into [matrice_import_articles$] " + champ + " VALUES " + param);
 
            champ = champ.Replace("(", "");
            champ = champ.Replace(")", "");
 
 
            if ((bool)infoNombre["est_promo"])
                infoNombre["est_promo"] = "on";
            else
                infoNombre["est_promo"] = "off";
 
            if ((bool)infoComplementaire["prix_produit_visible"])
                infoComplementaire["prix_produit_visible"] = "on";
            else
                infoComplementaire["prix_produit_visible"] = "off";
 
            if ((bool)infoComplementaire["produit_visible"])
                infoComplementaire["produit_visible"] = "on";
            else
                infoComplementaire["produit_visible"] = "off";
 
            if ((bool)infoComplementaire["produit_dispo"])
                infoComplementaire["produit_dispo"] = "on";
            else
                infoComplementaire["produit_dispo"] = "off";
 
 
            string[] parametres = champ.Split(',');
//je valorise mes paramètres
            for (int j = 0; j < parametres.Length; j++)
            {
                parametres[j] = parametres[j].Trim();
                if (infoGeneral.ContainsKey(parametres[j]))
                    commande.Parameters.Add(new OleDbParameter("@" + parametres[j], infoGeneral[parametres[j]]));
                if (infoArticle.ContainsKey(parametres[j]))
                    commande.Parameters.Add(new OleDbParameter("@" + parametres[j], infoArticle[parametres[j]]));
                if (infoNombre.ContainsKey(parametres[j]))
                    commande.Parameters.Add(new OleDbParameter("@" + parametres[j], infoNombre[parametres[j]]));
                if (infoComplementaire.ContainsKey(parametres[j]))
                    commande.Parameters.Add(new OleDbParameter("@" + parametres[j], infoComplementaire[parametres[j]]));
                if (infoGamme.ContainsKey(parametres[j]))
                    commande.Parameters.Add(new OleDbParameter("@" + parametres[j], infoGamme[parametres[j]]));
                if (infoRegion.ContainsKey(parametres[j]))
                    commande.Parameters.Add(new OleDbParameter("@" + parametres[j], infoRegion[parametres[j]]));
                if (infoImage.ContainsKey(parametres[j]))
                    commande.Parameters.Add(new OleDbParameter("@" + parametres[j], infoImage[parametres[j]]));
            }
            //ecriture dans le fichier.
 
            msExcelClient.runNonQuery(commande);
 
        }
 
 
    }
}