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:

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)
{
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);
}
}
public class MergeExcel
{
Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Excel.Workbook bookDest = null;
Excel.Worksheet sheetDest = null;
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)
{
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;
}
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;
}
void CloseBook()
{
bookSource.Close(false, Missing.Value, Missing.Value);
}
void CopyHeader()
{
Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
range.Copy(sheetDest.get_Range("A1", Missing.Value));
_currentRowCount += _headerRowCount;
}
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;
}
void Save()
{
bookDest.Saved = true;
bookDest.SaveCopyAs(_destFile);
}
void Quit()
{
app.Quit();
}
void DoMerge()
{
bool b = false;
foreach (string strFile in _sourceFiles)
{
OpenBook(strFile);
if (b == false)
{
CopyHeader();
b = true;
}
CopyData();
CloseBook();
}
Save();
Quit();
}
public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
{
new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
}
}
}

Note: please set the reference property as false.

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. Download it from here. Please refer the code below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.XSSF.UserModel;
using System.Data;
using NPOI.HSSF.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);
}
ExportEasy(dt, @"..\..\result.xls");
}
private static void MergeData(string path, DataTable dt)
{
XSSFWorkbook workbook = new XSSFWorkbook(path);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
if (dt.Rows.Count == 0)
{
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dt.Columns.Add(column);
}
}
else
{
}
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)
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
}
public static void ExportEasy(DataTable dtSource, string strFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = (HSSFRow)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);
}
}
}
}
}

Note: as NPOI could not support the .xlsx document, please set the generated file as the .xls document. 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. Download it from here. Please refer the code below:

using System;
using System.Collections.Generic;
using System.Text;
using org.in2bits.MyXls;
using System.Data;
using System.IO;
using System.ComponentModel;
using System.Reflection;
namespace method3_MyXls_
{
class Program
{
static void Main(string[] args)
{
XlsDocument xls = null;
DataTable dt = new DataTable();
List<string[]> list = new List<string[]>();
String[] str = null;
string[] xlsfiles = new string[] { @"..\..\merge1.xls", @"..\..\merge2.xls" };
for (int i = 0; i < xlsfiles.Length; i++)
{
xls = new XlsDocument(xlsfiles[i]);
Worksheet sheet = xls.Workbook.Worksheets[0];
for (int j = 1; j < sheet.Rows.Count; j++)
{
str = new string[sheet.Rows[ushort.Parse(j.ToString())].CellCount];
for (int z = 1; z < sheet.Rows[ushort.Parse(j.ToString())].CellCount + 1; z++)
{
str[z - 1] = Convert.ToString(sheet.Rows[ushort.Parse(j.ToString())].GetCell(ushort.Parse(z.ToString())).Value);
}
list.Add(str);
}
}
dt = ConvertListToDataTable(list);
ExportToExcel(dt, "result.xls");
}
static DataTable ConvertListToDataTable(List<string[]> list)
{
DataTable table = new DataTable();
int columns = 0;
foreach (var array in list)
{
if (array.Length > columns)
{
columns = array.Length;
}
}
for (int i = 0; i < columns; i++)
{
table.Columns.Add();
}
foreach (var array in list)
{
table.Rows.Add(array);
}
return table;
}
public static void ExportToExcel(DataTable dtSource, string strFileName)
{
XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add("sheet1");
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
sheet.Cells.Add(i + 1, j + 1, dtSource.Rows[i][j].ToString());
}
}
xls.FileName = strFileName;
if (File.Exists(strFileName))
{
File.Delete(strFileName);
}
xls.Save();
}
}
}

Note: as the second solution, you need to set the generated file as .xls document as well. 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. Download it from here. 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();
workbook.LoadFromFile(@"merge1.xlsx");
Workbook workbook2 = new Workbook();
workbook2.LoadFromFile(@"merge2.xlsx");
Worksheet sheet2 = workbook2.Worksheets[0];
DataTable dataTable = sheet2.ExportDataTable();
Worksheet sheet1 = workbook.Worksheets[0];
sheet1.InsertDataTable(dataTable, false, sheet1.LastRow + 1, 1);
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.

4 Comments

Join the conversation! Your thoughts help the community grow.

Mahesh Chand12y ago
Great. Nice style of writing, taking one problem and giving the solution.
Saineshwar Bageri12y ago
nice one
Sam Hobbs12y ago
Another possibility would be to append (merge) each worksheet into a database table. Sometimes it is better to use a database instead of a table. Depending on the amount of data, it might not be possible to merge everything into one worksheet, correct?
- Ring Zhong12y ago
  I agree. As what you say, it depends on the amount of data.