none
copier le contenu d'un Excel vers un autre RRS feed

  • Discussion générale

  • Bonjour,

    j'ai essaye de copier le contenu d'un fichier (input) vers un autre fichier Excel(output) créé en utilisant du Open XML.

    On exécutant mon code, ça tourne au cun probléme,

    mais lorsque j'essaye d'ouvrir mon fichir excel (output), j'ai ce message d'erreur " Excel found an unreadable content in ..... Do you want to recover the content of this workbook? if you trust the source of this workbook, click yes"

            public void Main()
            {
                  try
                {
                    Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                    String pathI = (string)Dts.Variables["User::ExcelFilePath"].Value;
                    String nameI = (string)Dts.Variables["User::ExcelFileName"].Value;
                    String fileI = pathI + "\\" + nameI;
                    String fileO = pathI + "\\temp_" + nameI;
                    List<string> excelTables = new List<string>();
                    DataSet ds = new DataSet();
                    //MessageBox.Show(fileI);
                    DataTable sheet1 = new DataTable();
                    OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
                    csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
                    csbuilder.DataSource = fileI;
                    csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=1");
                    GeneratedClass c = new GeneratedClass();
                    if (File.Exists(fileO))
                        File.Delete(fileO);
                    c.CreatePackage(fileO);
                    DataTable sheets = GetSchemaTable(csbuilder.ConnectionString);
                    List<string> queryInsertResult = new List<string>();
                    List<string> queryInsertResume = new List<string>();
                    using (var connection = new OleDbConnection(csbuilder.ConnectionString))
                    {
                        connection.Open();
                        foreach (DataRow r in sheets.Rows)
                        {
                            string query = "SELECT * FROM [" + r["TABLE_NAME"].ToString() + "]";
                            ds.Clear();
                            OleDbDataAdapter data = new OleDbDataAdapter(query, connection);
                            data.Fill(ds);
                            //string TABLE_NAME = r["TABLE_NAME"].ToString().Substring(0,r["TABLE_NAME"].ToString().Length - 2)+"'";
                            string TABLE_NAME = r["TABLE_NAME"].ToString().Trim();
                            int dollareindice = TABLE_NAME.IndexOf("$");
                            if (dollareindice > 0) TABLE_NAME = TABLE_NAME.Substring(0, dollareindice);
                            if (!TABLE_NAME.StartsWith("'")) TABLE_NAME = "'" + TABLE_NAME;
                            if (!TABLE_NAME.EndsWith("'")) TABLE_NAME = TABLE_NAME + "'";
                            //if (r["TABLE_NAME"].ToString().Contains("Analysis Step"))
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                string myvalue = row[row.ItemArray.Length - 1].ToString().Replace("'", "''");
                                if (TABLE_NAME.Contains("Onglet1"))
                                    queryInsertResume.Add("Insert into [Res$] (column1,[column 2]) values ('value','" + myvalue + "')");
                                else
                                    queryInsertResult.Add("Insert into [ULT$] ([Tab],[valeur],[Nom]) values (" + TABLE_NAME + ",'" + myvalue + "'," + TABLE_NAME + ")");
                            }
                            data.Dispose();
                        }
                        connection.Close();
                        if (connection != null)
                            connection.Dispose();
                        ds.Dispose();
                    }
                    csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
                    csbuilder.DataSource = fileO;
                    csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;");
                    using (var connection = new OleDbConnection(csbuilder.ConnectionString))
                    {
                        connection.Open();
                        foreach (string queryResume in queryInsertResume)
                        {
                            var cmd = new OleDbCommand(queryResume, connection);
                            cmd.ExecuteNonQuery();
                        }
                        foreach (string queryResult in queryInsertResult)
                        {
                            var cmd = new OleDbCommand(queryResult, connection);
                            cmd.ExecuteNonQuery();
                        }
                        connection.Close();
                    }
                }
                catch(Exception ex)
                {
                    File.WriteAllText(@"E:\log.txt", ex.Message);
                }
            }
     des idées???
    • Type modifié Aurel Bera mercredi 4 décembre 2013 07:42 Discussion
    mercredi 27 novembre 2013 08:18

Toutes les réponses