Breaking News

Friday, March 12, 2010

.NET: How to merge multiple Excel workbooks into one?

How to merge multiple Excel workbooks into one?

Only if you want something like: Merge(@"E:\Test", @"E:\FinalDestination.xls");

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

Btw, this was in response to a post on StackOverflow.

1 comment:

  1. Try this method - it is fine sulotion based on a C# Excel component.

    Merge Excel Files into One in C#

    ReplyDelete

Designed By Published.. Blogger Templates