Bite Size C# – XLS File Merger

This will be the last Bite Size C# post revolving around CSV and XLS files (for a while at least). The next one will be on a different and probably a bit more exciting topic.

Today we will look at my XLS File Merger, which resides in the Core.XLS namespace of my Core.dll library. It is a static class with one static method MergeFiles. The purpose of this method is to merge 2 or more XLS files into a single file, i.e. taking the worksheets of 2 or more files and combing them into a single file. The MergeFiles method takes 2 arguments: a list of locations of files to be merged (filePathList) and the destination file that will be created (outputFile).

Here is the code:

using System;
using Microsoft.Office.Interop.Excel;

namespace Core.XLS
    public static class XlsFileMerge
        public static void MergeFiles(string[] filePathList, string outputFile)
            var app = new Application {Visible = false};

            foreach (var file in filePathList)

            for (var i = 2; i <= app.Workbooks.Count; i++)
                var count = app.Workbooks[i].Worksheets.Count;

                for (var j = 1; j <= count; j++)
                    var ws = (_Worksheet) app.Workbooks[i].Worksheets[j];
                    if (ws.UsedRange.Rows.Count <= 1) continue;

                    var sel = (Range) app.Selection;

                    var sheet = (_Worksheet) app.Workbooks[1].Worksheets.Add(
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    sheet.Name = ws.Name;
                    sheet.Paste(Type.Missing, Type.Missing);
Bite Size C# – XLS File Merger

Leave a Reply

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

You are commenting using your 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