Copy worksheet from workbook 1 to new worksheet in workbook2

Jun 29, 2011 at 3:21 PM

Hello, i'm amazed by the work of EPP and i want to thank all contributors to this project.

Now, i'm posting a new discussion because i'm facing a problem of copying worksheet from workbook1 to workbook 2.

Here is my Code below: 

---------------------------------------------------------------------------------------------

DirectoryInfo cheminDossierEnfant = new DirectoryInfo(subDirectory);           

string[] excelFiles = Directory.GetFiles(subDirectory, "*.xlsx");

        string dossierNom = cheminDossierEnfant.Name;           

int intLastSlash = subDirectory.LastIndexOf("\\");           

string stringCheminFolderFinal = subDirectory.Remove(intLastSlash);

 FileInfo fichierFinal = new FileInfo(stringCheminFolderFinal + @"\" + dossierNom + ".xlsx");
         FileInfo template = new FileInfo(@"C:\Template.xlsx");
    ExcelPackage xlpackage = new ExcelPackage(fichierFinal, template);   

        ExcelWorkbook destiBook = xlpackage.Workbook;     

      ExcelWorksheet sheetFeuilUn = destiBook.Worksheets["Feuil1"];     

      foreach (string item in excelFiles)       

    {               

FileInfo fichierExcelEnCours = null;
                ExcelPackage packageFichierSource = null;

 ExcelWorkbook sourceBook = null;               

ExcelWorksheet sourceSheet = null;               

ExcelWorksheet fichierFinalWorksheet = null;           

fichierExcelEnCours = new FileInfo(item.ToString());

                packageFichierSource = new ExcelPackage(fichierExcelEnCours);

                sourceBook = packageFichierSource.Workbook;

                sourceSheet = sourceBook.Worksheets["Fiche projet"];

                fichierFinalWorksheet = destiBook.Worksheets.Add(fichierExcelEnCours.Name);

                destiBook.StylesXml.InnerXml = sourceBook.StylesXml.InnerXml;

                for (int i = 1; i < 60; i++)               

{                    for (int y = 1; y < 19; y++)                   

{                        if (sourceSheet.Cells[i, y].Value != null)                       

{                            fichierFinalWorksheet.Cells[i, y].Value = sourceSheet.Cells[i, y].Value;     

                      fichierFinalWorksheet.Cells[i, y].Formula = sourceSheet.Cells[i, y].Formula;           

                fichierFinalWorksheet.Cells[i, y].Style.Border = sourceSheet.Cells[i, y].Style.Border;       

                    fichierFinalWorksheet.Cells[i, y].Style.Font.Family = sourceSheet.Cells[i, y].Style.Font.Family;       

                    fichierFinalWorksheet.Cells[i, y].Style.Font.Bold = sourceSheet.Cells[i, y].Style.Font.Bold;   

                        fichierFinalWorksheet.Cells[i, y].Style.Numberformat = sourceSheet.Cells[i, y].Style.Numberformat;   

                        fichierFinalWorksheet.Cells[i, y].StyleID = sourceSheet.Cells[i, y].StyleID;               

        }                   

    else                     

  {                           

fichierFinalWorksheet.Cells[i, y].StyleID = sourceSheet.Cells[i, y].StyleID;           

            }         

          }           

    }

    packageFichierSource.Dispose();         

      xlpackage.Workbook.Worksheets.MoveBefore(fichierFinalWorksheet.Name, sheetFeuilUn.Name);         

  }         

  xlpackage.Save();         

  xlpackage.Dispose();

-------------------------------------------------------------------------------------------------------------

Let me explain my goal : 

I've got a directory where his subdirectories contains a lot of Excel files (.xlsx).

I need to copy every worksheets named "Fiche Projet" in a final Excel File with the name of my subdirectory.

The problem is "style" and some "value" and "formula" are well copied. But from every "Fiche projet" worksheet, there aren't rows 35 to 39

and i don't understand why my condition doesn't work when i open my final Excel file. 

By the way, merged cells doesn't work very well, cause all the value are staying in the specified cell and it isn't extended like my "Fiche projet" worksheet. (I verify with Excel 2007).

I hope i can get some answer from every user of EPP.

Thank you for reading my discussion.