Excel automation using C#

Question

Hi,

I have a folder with close to 400 excel files. I need to copy the worksheets in all these excel files to a single excel file.

using Interop and Reflection namespaces heres is what I have accomplished so far.

I use folderBrowserDialog to browse to the folder and select it, this enable me to get the file names of the files within the folder and iterate through them this is as far as i got, any help would be appreciated.

if (result == DialogResult.OK)


            {
                string path = fbd1.SelectedPath; //get the path
                int pathLength = path.Length + 1;
                string[] files = Directory.GetFiles(fbd1.SelectedPath);// getting the names of files in that folder


                foreach (string i in files)
                {
                    MessageBox.Show("1 " + i);
                    myExcel.Application excelApp = new myExcel.ApplicationClass();
                    excelApp.Visible = false;
                    MessageBox.Show("2 " + i);
                    myExcel.Workbook excelWorkbook = excelApp.Workbooks.Add(excelApp.Workbooks._Open(i, 0, false, 5, "", "", false, myExcel.XlPlatform.xlWindows, "", true, false, 0, true));
                    myExcel.Sheets excelSheets = excelWorkbook.Worksheets;
                    MessageBox.Show("3 " + i);
                    excelApp.Workbooks.Close();
                    excelApp.Quit();
                }

                MessageBox.Show("Done!");
            }

How do i append the copied sheets to the destination file. Hope the question is clear?

thanks.

Answer 1

use Worksheet.Copy(Before, After) and specify after as the last worksheet of whatever main file you want. Please note you might need to create a new worksheet in the mainApp so that it has a worksheet to input after so that it doesn't throw an exception.

Try the following:

            Excel.Application mainApp = new Excel.ApplicationClass();
            mainApp.Visible = false;
            Excel.Workbook mainWorkbook = excelApp.Workbooks.Add(null);
            Excel.Sheets mainWorkSheets = mainWorkbook.Worksheets;

            foreach (string i in files)
            {
                MessageBox.Show("1 " + i);
                Excel.Application exApp = new Excel.ApplicationClass();
                exApp.Visible = false;
                MessageBox.Show("2 " + i);
                Excel.Workbook exWorkbook = exApp.Workbooks.Open(i,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                MessageBox.Show("3 " + i);
                foreach(Excel.Worksheet sheet in exWorkbook.Worksheets)
                {
                    sheet.Copy(Type.Missing, mainWorkSheets[mainWorkSheets.Count -1]);
                }
            }

            mainApp.Save("NewExcel");

Answer 2

How about something like: Merge(@"C:\ExcelFolder", @"C:\FinalDestination.xls"); works for me, directly outta working sample - trimmed for you.

Hopefully, you wont require any tweaks but in case you do, then do (0:

Please see following code:

private void Merge(string strSourceFolder, string strDestinationFile)
{
    try
    {
        //1. Validate folder,
        //2. Instantiate excel object
        //3. Loop through the files
        //4. Add sheets
        //5. Save and enjoy!

        object missing = System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        ExcelApp.Visible = false;

        //Create destination object
        Microsoft.Office.Interop.Excel.Workbook objBookDest = ExcelApp.Workbooks.Add(missing);


        foreach (string filename in Directory.GetFiles(strSourceFolder))
        {
            if (File.Exists(filename))
            {
                //create an object
                Microsoft.Office.Interop.Excel.Workbook objBookSource = ExcelApp.Workbooks._Open
              (filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
              , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


                //Browse through all files.
                foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in objBookSource.Worksheets)
                {
                    sheet.Copy(Type.Missing, objBookDest.Worksheets[objBookSource.Worksheets.Count]);
                }

                objBookSource.Close(Type.Missing, Type.Missing, Type.Missing);
                objBookSource = null; 

            }
        }
        objBookDest.SaveAs(strDestinationFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        objBookDest.Close(Type.Missing, Type.Missing, Type.Missing);

        objBookDest = null;
        ExcelApp = null;


    }
    catch (System.Exception e)
    {
        //Catch
    }
}