Excel automation using C#

Question

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.

Michal Ciechan · Accepted Answer · 2010-03-12 11:08:44Z

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");

Kamran Khan · Accepted Answer · 2010-03-12 12:39:39Z

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
    }
}

Tom Black · Accepted Answer · 2020-09-21 10:44:36Z

0

You can try ZetExcel for Excel automation using C#. ZetExcel for .NET contains APIs to be used with .NET Framework, .NET Core & Xamarin platform.

answered Sep 21, 2020 at 10:44

Tom Black

1

1 Comment

Eduard Keilholz Over a year ago

This is not an answer to the question, you're just selling a 3rd party component here

Collectives™ on Stack Overflow

Excel automation using C#

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related