Combine multiple excel files into one with C#

Posted on Updated on


I created the code below as the application that I’m working on is using different 3rd party grid controls or there are multiple grids in the report which when exported, they are being exported into different excel files and it’s much easier if we can provide 1 single file output but have multiple worksheets for users to use. And I actually re-used the existing code below for some other manual extract stuff from CSV that we need to merge into 1 excel file, worked great ;)

Have the codes below for a while, but just got some time to post this as i think it might be handy for everyone or for ownself in future in fact ;)

I guess this is the kind of code that you only want to write once and gosh look at those Type.Missing :p

    public class ExcelEngine
    {
        /// <summary>
        /// To combine multiple workbooks into a file
        /// </summary>
        /// <remarks>
        /// The following file name convention will be used while combining the child files
        /// exportFileKey_[Description] where the description will be the tab name
        ///
        /// The ordering of the worksheets will be using the file creation time
        ///
        /// The above convention can be enhanced when necessary but need to make sure the backward compatibility for the existing codes
        ///
        /// Note:
        /// - the index starts from 1 in the excel automation array
        /// - be careful when making changes, especially moving things around in the method, e.g. prompts might come up unexpectedly
        /// - to avoid "zombie" excel instances in the task manager when referencing the COM object, please refer to the http://support.microsoft.com/default.aspx/kb/317109
        ///
        /// </remarks>
        /// <param name="exportFilePath">the destination file name choosen by the user</param>
        /// <param name="exportFileKey">the unique key file name choosen by the user, this is to avoid merging files with similar names</param>
        /// <param name="rawFilesDirectory">the folder where the files are being generated, this can be temp folder or any folder basically</param>
        /// <param name="deleteRawFiles">delete the raw files after completed?</param>
        ///
        /// <returns></returns>
        public static bool CombineWorkBooks(string exportFilePath, string exportFileKey, string rawFilesDirectory, bool deleteRawFiles)
        {
            Application xlApp = null;
            Workbooks newBooks = null;
            Workbook newBook = null;
            Sheets newBookWorksheets = null;
            Worksheet defaultWorksheet = null;
            IEnumerable<string> filesToMerge = null;
            bool areRowsTruncated = false;

            try
            {
                Console.WriteLine("Method: CombineWorkBooks - Starting excel");
                xlApp = new Application();

                if (xlApp == null)
                {
                    Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
                    return false;
                }

                Console.WriteLine("Method: CombineWorkBooks - Disabling the display alerts to prevent any prompts during workbooks close");
                // not an elegant solution? however has to do this else will prompt for save on exit, even set the Saved property didn't help
                xlApp.DisplayAlerts = false;

                Console.WriteLine("Method: CombineWorkBooks - Set Visible to false as a background process, else it will be displayed in the task bar");
                xlApp.Visible = false;

                Console.WriteLine("Method: CombineWorkBooks - Create a new workbook, comes with an empty default worksheet");
                newBooks = xlApp.Workbooks;
                newBook = newBooks.Add(XlWBATemplate.xlWBATWorksheet);
                newBookWorksheets = newBook.Worksheets;

                // get the reference for the empty default worksheet
                if (newBookWorksheets.Count > 0)
                {
                    defaultWorksheet = newBookWorksheets[1] as Worksheet;
                }

                Console.WriteLine("Method: CombineWorkBooks - Get the files sorted by creation date");
                var dirInfo = new DirectoryInfo(rawFilesDirectory);
                filesToMerge = from f in dirInfo.GetFiles(exportFileKey + "*", SearchOption.TopDirectoryOnly)
                               orderby f.CreationTimeUtc
                               select f.FullName;

                foreach (var filePath in filesToMerge)
                {
                    Workbook childBook = null;
                    Sheets childSheets = null;
                    try
                    {
                        Console.WriteLine("Method: CombineWorkBooks - Processing {0}", filePath);
                        childBook = newBooks.Open(filePath, 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);

                        childSheets = childBook.Worksheets;
                        if (childSheets != null)
                        {
                            for (int iChildSheet = 1; iChildSheet <= childSheets.Count; iChildSheet++)
                            {
                                Worksheet sheetToCopy = null;
                                try
                                {
                                    sheetToCopy = childSheets[iChildSheet] as Worksheet;
                                    if (sheetToCopy != null)
                                    {
                                        Console.WriteLine("Method: CombineWorkBooks - Assigning the worksheet name");
                                        sheetToCopy.Name = Truncate(GetReportDescription(Path.GetFileNameWithoutExtension(filePath), sheetToCopy.Name), 31); // only 31 char max

                                        Console.WriteLine("Method: CombineWorkBooks - Copy the worksheet before the default sheet");
                                        sheetToCopy.Copy(defaultWorksheet, Type.Missing);
                                    }
                                }
                                finally
                                {
                                    DisposeCOMObject(sheetToCopy);
                                }
                            }

                            Console.WriteLine("Method: CombineWorkBooks - Close the childbook");
                            // for some reason, calling close below may cause an exception -> System.Runtime.InteropServices.COMException (0x80010108): The object invoked has disconnected from its clients.
                            childBook.Close(false, Type.Missing, Type.Missing);
                        }
                    }
                    finally
                    {
                        DisposeCOMObject(childSheets);
                        DisposeCOMObject(childBook);
                    }
                }

                Console.WriteLine("Method: CombineWorkBooks - Delete the empty default worksheet");
                if (defaultWorksheet != null) defaultWorksheet.Delete();

                Console.WriteLine("Method: CombineWorkBooks - Save the new book into the export file path: {0}", exportFilePath);
                newBook.SaveAs(exportFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    , XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                newBooks.Close();
                xlApp.DisplayAlerts = true;

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Method: CombineWorkBooks - Exception: {0}", ex.ToString());
                return false;
            }
            finally
            {
                DisposeCOMObject(defaultWorksheet);
                DisposeCOMObject(newBookWorksheets);
                DisposeCOMObject(newBooks);
                DisposeCOMObject(newBook);

                Console.WriteLine("Method: CombineWorkBooks - Closing the excel app");
                if (xlApp != null)
                {
                    xlApp.Quit();
                    DisposeCOMObject(xlApp);
                }

                if (deleteRawFiles)
                {
                    Console.WriteLine("Method: CombineWorkBooks - Deleting the temporary files");
                    DeleteTemporaryFiles(filesToMerge);
                }
            }
        }

        private static void DisposeCOMObject(object o)
        {
            Console.WriteLine("Method: DisposeCOMObject - Disposing");
            if (o == null)
            {
                return;
            }
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Method: DisposeCOMObject - Exception: {0}", ex.ToString());
            }
        }

        private static void DeleteTemporaryFiles(IEnumerable<string> tempFilenames)
        {
            foreach (var tempFile in tempFilenames)
            {
                try
                {
                    File.Delete(tempFile);
                }
                catch
                    (Exception)
                {
                    Console.WriteLine("Could not delete temporary file '{0}'", tempFilenames);
                }
            }
        }

        /// <summary>
        /// the first array item will be the key
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="defaultName"></param>
        /// <returns></returns>
        protected static string GetReportDescription(string fileName, string defaultName)
        {
            var splits = fileName.Split('_');
            return splits.Length > 1 ? string.Join("-", splits, 1, splits.Length - 1) : defaultName;
        }

        /// <summary>
        /// Get a substring of the first N characters.
        /// http://dotnetperls.com/truncate-string
        /// </summary>
        public static string Truncate(string source, int length)
        {
            if (source.Length > length)
            {
                source = source.Substring(0, length);
            }
            return source;
        }

}

Advertisements

18 thoughts on “Combine multiple excel files into one with C#

    Kivios said:
    March 7, 2011 at 10:50 pm

    This is great!! How would I modify this to merge all the worksheets into one one sheet?

      Bembeng Arifin responded:
      March 8, 2011 at 3:02 pm

      Hi Kivios,
      1. You can try to leverage the worksheetInstance.UsedRange.Rows.Count to find out the last row index for the existing worksheet
      2. Copy the whole range of the worksheetToBeCopiedInstance using the same idea (from rowIndex 1 I believe till the lastRowIndex using the same property above)
      3. Paste the result in the worksheetInstance after the last row index + x (for some space)
      Hope this helps ;)

    JJ said:
    April 26, 2011 at 3:33 pm

    Fantastic code there Bembeng, I appreciate you posting something such as this, as it helps people like myself who would be lost trying to delve into Excel Interop for the first time.

    Only question I have is in regard to the formatting on the sheets that are being copied over. When executed, everything is being copied over, but in all cases of background color/font color, they are adjusted to something other than their original color.

    Is there some property that must be set on any of the workbooks/sheets in the code to maintain formatting?

    If there’s no simple answer, don’t worry bout it, I was just curious for future endeavors.

    Keep up the awesome programming and thanks a mil again.

    -JJ

      Bembeng Arifin responded:
      April 27, 2011 at 3:48 am

      Hi JJ,
      Thanks for the kind words ;)
      Interesting question on the background color/font color, I actually have that code in the working application but decided to exclude that from the code snippet above as it seems to be quite specific requirement ;)
      You can insert the code snippet below between line#93 and #94. The idea is to have CopyColorPaletteFilePath property in the ExcelEngine that indicate which raw file to copy the color palette from into the new merged excel file.
      Hope this helps ;)

      if (filePath == CopyColorPaletteFilePath)
      {
      _log.Debug(“Method: CombineWorkBooks – Get the color palette from the child where it has a custom palette”);
      newBook.set_Colors(Type.Missing, childBook.get_Colors(Type.Missing));
      }

    simmer said:
    July 15, 2011 at 6:30 pm

    I am generating multiple xls files in a folder. Now I want them together in one excel but all should be in different tabs. Please give me the code for that.

    Thanks.

      Bembeng Arifin responded:
      July 16, 2011 at 2:45 pm

      Hi simmer,
      The codes in the post is already trying to merge xls files into different tabs/worksheets, hints below:
      Line#67 – this is where you can specify the files in a certain directory to merge
      Line#93 – this is where you can define the worksheet name for each file
      Hope this helps ;)

    Guest said:
    September 14, 2011 at 9:13 pm

    This article was exellent. Thank you.

    abhi said:
    December 12, 2011 at 11:37 am

    you r too good………..it helped me a lot..thanks a lot bembeng…………….give me ur email id..i have some doubts i ll ask if u dont mind..i m working in mnc…….

    Tiago said:
    January 3, 2012 at 5:48 pm

    Hi there,

    I’m using your code :

    newBook.set_Colors(Type.Missing, childBook.get_Colors(Type.Missing))

    But the sheets that i’m copying do not have the same colors.

    There is any way to union all the colors used inside the different workbooks and then apply it to the final workbook ?

    Thanks a lot.

    Tiago said:
    January 4, 2012 at 10:06 am

    Thanks a lot for your time.
    It’s better to explain all the history behind my problem.
    I’m using Reporting Services with Integration Services to create and export reports to a directory….

    The situation now is very weird and i’m freaking out with the colors.
    I have the same colors in different cells in all the sheets i want to copy but only the sheet that comes from the workbook.get_colors stay with the correct colors.

    I’m using this code( very basic, learning yet :P )
    http://pastebin.com/7BFYgVe0

    In our company we use Office 2010 but the .XLS are created in 97-2003.

    Again, I really appreciate your help.
    Thanks a lot mate.

    prim said:
    May 27, 2012 at 8:30 pm

    HI Bembeng,
    This really helps..!
    Thanks allot..! The output comes in different sheets in one excel file. My only concern is to display the data in one sheet by removing the headers and column names so that I can make use of search and filter options.
    Is there any way to do so ? Thanks allot..!!

    Prim :)

    Shalabh said:
    July 13, 2012 at 9:42 am

    Thanks a lot..Your formatting fix worked like magic :) Thanks again!

    Vivek said:
    April 16, 2013 at 3:49 pm

    Awesome Bembeng..Saved me a lot of time…:)

    sharu said:
    September 5, 2013 at 8:11 am

    Hello Bembeng, It’s really an excellent job!! Although in few cases the format will be messed up.. Anything to specify in the code explicitly for this?

    MK said:
    January 23, 2014 at 2:58 pm

    Exactly what I was looking for. Great stuff !!!

    Barnabeck said:
    January 4, 2016 at 12:26 pm

    This is fantastic!! I use your code in a Web application and it works perfectly, BUT in the end there is always an Excel process running in the background that was not properly closed.

      Bembeng Arifin responded:
      January 4, 2016 at 1:41 pm

      Glad that this code is still useful ;)

      The always running excel process sounds like a process/memory leak to me. I recommend start with a very basic debug start and end excel process test and verify the excel process is properly terminated once the processing completes, if it does then start adding code pieces by pieces until you find the particular code causing the leak. it’s normally the reference of the workbook, worksheet, or range that is not disposed in the code.
      Good luck!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s