4 Solutions to Merge Multiple Excel Worksheets into One

Introduction

In the daily routine, Excel is indispensable for us to processing data. We usually need to merge multiple worksheets or workbooks into one when use Excel, so that we can analyze and count the data quickly and conveniently. Generally, we use copy and paste command to achieve the goal for most people. It’s a good solution if you just have a small number of Excel files or worksheets, but if there are numerous worksheets, this method will be excruciating and time-consuming. Thus, this article aims at talking about some useful solutions for you to solve this problem.

Background

In fact, I have some large Excel files that have the same construction, and I need to merge them into one worksheet so that I could extract one of the columns and export it into another Excel file or worksheet. They have 7 columns and more than 1000 rows, I know I cannot complete the process by hand with copy and paste command. So I need certain easier method to help me. Luckily, I collected some useful and verified solutions through Google and I am very willing to share them with you here.

How Many Kinds of Solutions

I have verified 4 solutions so far. One of them is to merge the worksheets through a small tool, and the rest are using C# code to achieve the implementation. Of course, most of them are free methods, which are easily found on the Internet. For the sake of the security, I will not use my own worksheet but the sample sheet in the solutions below. The two sample sheets have 5 columns and 10 rows (not including the first row). What we need do is to merge the two worksheets into one. I hope these solutions are also useful for you guys.

Solution1: using Microsoft.Office.Interop.Excel;

Although the method is free, you must install Microsoft Office on your machine, so that you could run the code successfully. If you have installed the Microsoft Office on your machine, it's a good choice for you. Besides, the code is a little complicated. Anyway, it could help achieve my goal. Please refer the code below:

Note: please set the reference property as false.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication3
{
    class Program
    {

        static void Main(string[] args)
        {
            
            //E is the sign of the table width(M is the last column in the Excel file), 2 means the table height
            MergeExcel.DoMerge(new string[] 
            {
                @"D:\Test Files\ConsoleApplication3\ConsoleApplication3\merge1.xlsx", 
                @"D:\Test Files\ConsoleApplication3\ConsoleApplication3\merge2.xlsx"
            },
                @"D:\Test Files\ConsoleApplication3\ConsoleApplication3\result.xlsx", "E", 2);
        }
    }
    
    //Merge multiple Excel files that have the same structure
    public class MergeExcel
    {
        Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
        
        //initialize the object of saved target 
        Excel.Workbook bookDest = null;
        Excel.Worksheet sheetDest = null;
        
        //initialize the object of read data
        Excel.Workbook bookSource = null;
        Excel.Worksheet sheetSource = null;
        string[] _sourceFiles = null;
        string _destFile = string.Empty;
        string _columnEnd = string.Empty;
        int _headerRowCount = 0;
        int _currentRowCount = 0;
        public MergeExcel(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        {
            
            //Use class Missing case to indicate the missing value. e.g. when you call the method that has default parameter value
            bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            sheetDest.Name = "Data";
            _sourceFiles = sourceFiles;
            _destFile = destFile;
            _columnEnd = columnEnd;
            _headerRowCount = headerRowCount;
        }
        
        //open worksheet
        void OpenBook(string fileName)
        {
            bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
        }
        
        //close worksheet
        void CloseBook()
        {
            bookSource.Close(false, Missing.Value, Missing.Value);
        }
        
        //copy table header
        void CopyHeader()
        {
            Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
            range.Copy(sheetDest.get_Range("A1", Missing.Value));
            _currentRowCount += _headerRowCount;
        }
        
        //copy data
        void CopyData()
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
            range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }
        
        //save the result
        void Save()
        {
            bookDest.Saved = true;
            bookDest.SaveCopyAs(_destFile);
        }
        
        //exit the process
        void Quit()
        {
            app.Quit();
        }
        void DoMerge()
        {
            
            //declare variate bool to judge if copy table header
            bool b = false;
            foreach (string strFile in _sourceFiles)
            {
                OpenBook(strFile);
                if (b == false)
                {
                    CopyHeader();
                    b = true;
                }
                CopyData();
                CloseBook();
            }
            Save();
            Quit();
        }
        /// merge table
        /// source file
        /// object file
        /// the sign of the last column
        /// the number of rows of table header
        public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        {
            new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
        }
    }
}

Here comes to the effect screenshot:

Solution2: using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel;

The second method is to use the third party component NPOI. What I used is the free version. The advantage of NPOI is that it doesn't need Microsoft Office to be installed on the machine. The code is also a little complicated. Please refer the code below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using NPOI.XSSF.UserModel;

namespace method2
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            string[] files = new string[] { @"..\..\merge1.xlsx", @"..\..\merge2.xlsx" };
            for (int i = 0; i < files.Length; i++)
            {
                MergeData(files[i], dt);
            }
            ExportDataTableToExcel(dt, @"..\..\result.xlsx");
        }
        private static void MergeData(string path, DataTable dt)
        {
            // write data in workbook from xls document.
            XSSFWorkbook workbook = new XSSFWorkbook(path);
            // read the current table data
            XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
            // read the current row data
            XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
            // LastCellNum is the number of cells of current rows
            int cellCount = headerRow.LastCellNum;
                 
            if (dt.Rows.Count == 0)
            {
                
                // build header for there is no data after the first implementation
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    // get data as the column header of DataTable
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                       
                    dt.Columns.Add(column);
                }
            }
            else
            {
            
                // TODO: check if the subsequent sheet corresponds
            }
            // LastRowNum is the number of rows of current table
            int rowCount = sheet.LastRowNum + 1;
            for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
            {
                XSSFRow row = (XSSFRow)sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
              // get data and convert them into character string type, then save them into the rows of datatable
                        dataRow[j] = row.GetCell(j).ToString();
                    
                      
                }
                dt.Rows.Add(dataRow);
            }
            workbook = null;
            sheet = null;
        }
        public static void ExportDataTableToExcel(DataTable dtSource, string strFileName)
        {
            // create workbook
            XSSFWorkbook workbook = new XSSFWorkbook(); 
            // the table named mySheet
            XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("mySheet");
            // create the first row
            XSSFRow dataRow = (XSSFRow)sheet.CreateRow(0);
            foreach (DataColumn column in dtSource.Columns)
            {
                // create the cells in the first row, and add data into these cells circularly
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                
            }
            //create rows on the basis of data from datatable(not including table header), and add data into cells in every row
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dataRow = (XSSFRow)sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {

                    workbook.Write(fs);// write mySheet table in xls document and save it
                }
            }
        }
    }
}

What below is the effect screenshot:

Solution3: using org.in2bits.MyXls;

The method is similar with the second method. It also completes the process with the help of a third party component called MyXls. It is an open source software that will let you perform Data Formats and Spreadsheet tasks. Also it doesn't require Excel on the machine or any licensing, but the flaw is that the first row of the second worksheet could not be removed. Please refer the code below:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using org.in2bits.MyXls;

namespace method3_MyXls_
{
    class Program
    {
        static void Main(string[] args)
        {
            // initialize an Excel object
            XlsDocument xls = null;
            // create a DataTable object
            DataTable dt = new DataTable();
            // get the files that need to be merged
            string[] xlsfiles = new string[] { @"..\..\merge1.xls", @"..\..\merge2.xls" };
            for (int i = 0; i < xlsfiles.Length; i++)
            {
                // write data from xls document in the workbook;
                xls = new XlsDocument(xlsfiles[i]);
                // read the data from the first worksheet in the current file
                Worksheet sheet = xls.Workbook.Worksheets[0];
                int FirstRow = (int)sheet.Rows.MinRow;
                if (i == 0)
                {
                    //write data in every cell in the first row in the first worksheet as the column header(note: in order to write data from xls document in DataTable)
                    for (int j = 1; j < sheet.Rows[1].CellCount + 1; j++)
                    
                    {
                        string ColumnName = Convert.ToString(sheet.Rows[1].GetCell(ushort.Parse(j.ToString())).Value);
                        DataColumn column = new DataColumn(ColumnName);
                        dt.Columns.Add(column);

                    }
                    FirstRow++;
                }
                // write data(not including column header) in datatable rows in sequence
                for (int k = FirstRow; k < sheet.Rows.MaxRow + 1; k++)
                {
                    Row row = sheet.Rows[ushort.Parse(k.ToString())];
                    DataRow dataRow = dt.NewRow();
                    for (int z = 1; z < sheet.Rows[ushort.Parse(k.ToString())].CellCount + 1; z++)
                    {
                        // write data in the current cell if it exists
                        if (row.GetCell(ushort.Parse(z.ToString())) != null)
                        {
                            dataRow[z - 1] = row.GetCell(ushort.Parse(z.ToString())).Value.ToString();
                        }

                    }
                    dt.Rows.Add(dataRow);
                }
            }
            ExportToExcel(dt, "result.xls");
        }

        public static void ExportToExcel(DataTable dtSource, string strFileName)
        {
            XlsDocument xls = new XlsDocument();
            Worksheet sheet = xls.Workbook.Worksheets.Add("sheet1");
            int i = 0;
            
            //write data from datatable in Excel file 
            foreach (DataColumn column in dtSource.Columns)
            {
                sheet.Cells.Add(1, i + 1, column.ColumnName);
                i++;
            }
            for (int j = 0; j < dtSource.Rows.Count; j++)
            {
                for (int k = 0; k < dtSource.Columns.Count; k++)
                {
                    sheet.Cells.Add(j + 2, k + 1, dtSource.Rows[j][k].ToString());
                }
            }
            // save
            xls.FileName = strFileName;
            if (File.Exists(strFileName))
            {
                File.Delete(strFileName);
            }
            xls.Save();
        }

    }


}

Note: you need to set the generated file as .xls document as Myxls does't support .xlsx document. Here is the effect screenshot:

Solution4: using Spire.Xls;

This method is the simplest way to merge worksheets into one among these 4 solutions through a third part component called Spire.Xls. It’s a standalone .NET component that dispenses with Microsoft Office installed on the machine as well. Please refer the code below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Data;


namespace Spire.XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            
            Workbook workbook = new Workbook();
            //load the first workbook
            workbook.LoadFromFile(@"merge1.xlsx");
            //load the second workbook
            Workbook workbook2 = new Workbook();
            workbook2.LoadFromFile(@"merge2.xlsx");

            //import the second workbook's worksheet into the first workbook using a datatable
            Worksheet sheet2 = workbook2.Worksheets[0];
            DataTable dataTable = sheet2.ExportDataTable();
            Worksheet sheet1 = workbook.Worksheets[0];
            sheet1.InsertDataTable(dataTable, false, sheet1.LastRow + 1, 1);


            //save the workbook
            workbook.SaveToFile("result.xlsx");
        }
    }
}

Note: Spire.Xls is not a free component, an evaluation warning will be generated in a new worksheet, but it’s unaffected to the result. Here comes to the perfect effect screenshot:

Conclusion 

Please kindly note that all of the components and software are run on the system Windows 7 32 bit. I just installed the Windows 8 theme so that my windows 7 system looks great like the windows 8. Besides, I didn’t spend even 1 dollar on these solutions. You should be on the basis of your own specific circumstance to choose the right solution. Hope these solutions could help you. I think I will continue to share my experience here.