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.

5 comments:

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

    Merge Excel Files into One in C#

    ReplyDelete
  2. The genuine advantage of PDF records is that they show and print reports that appear to be identical on all PCs. https://www.altomerge.com/blog/how_to_merge_pdf_documents

    ReplyDelete
  3. eruser is the survey program to its kin, Adobe Acrobat. Adobe Acrobat is utilized for making PDF documents. https://w9.pdffiller.com/en/features.htm

    ReplyDelete
  4. Hi, Thanks for sharing this well written .Net Script to Merge Multiple Excel files into one file. Also, we can use Synkronizer Excel Add-in to Merge Multiple Excel sheets into one file.

    ReplyDelete
  5. Substance instructors should utilize technology in manners that empower understudies to direct request and take part in collective exercises. diebestenvpn

    ReplyDelete

Designed By Published.. Blogger Templates