Append Data Tables Side By Side Using Excel Interop

Article

AppenddatatablessidebysideusingexcelinteropC.zip

Introduction

In this article, we will discuss how to append multiple data tables on a single worksheet in different columns. First of all, let's overview what is Microsoft Office Interloop? Yes, Microsoft Office Interop (Excel Automation) is an option when creating/reading Excel files (XLS, XLSX, CSV) from C# or VB.NET application.

Scenario

We have multiple data tables which have different data with same columns. We can export all the tables in a worksheet side by side. Let’s assume as have ‘n’ tables and each table has ‘m’ columns then we will have ‘n x m’ total columns. We will see how to achieve this using excel interop Excel in C#.

Following are the data tables which we have. First two tables have same columns with different data but third table has different data as well as different columns.

Requirements

Following DLLs will be required to implement this scenario,

using System.Data;
using System.Data.SqlClient;

Download Microsoft.Office.Interop.Excel through NuGet package.

How to merge/append these tables side-by-side

First, we will get the data from SQL to populate dataset of all the above data tables.

SqlConnection sqlConn = new SqlConnection("YourConnectionString");
SqlDataAdapter adapter = new SqlDataAdapter("procGetEmployee", sqlConn);
DataSet resultDS = new DataSet();
adapter.Fill(resultDS, "dsEmployees");
// Create objects for Excel Application, Workbook and Worksheet
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel._Workbook xlWorkBook;
Microsoft.Office.Interop.Excel._Worksheet xlWorkSheet;
// Initialize the objects of Excel Application, Workbook and Worksheet
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = (Microsoft.Office.Interop.Excel._Workbook)(xlApp.Workbooks.Add(""));
xlWorkSheet = (Microsoft.Office.Interop.Excel._Worksheet) xlWorkBook.ActiveSheet;
// Some local veraibales to control the RowIndex, ColumnIndex and ActualIndex
int rowIndex = 2, colIndex = 1, actualIndex = 1;
// To travrse all tables of dataset
for (int i = 0; i < resultDS.Tables.Count; i++) {
    rowIndex = 2;
    colIndex = 1;
    if (i > 0) actualIndex += resultDS.Tables[i - 1].Columns.Count;
    foreach(DataRow row in resultDS.Tables[i].Rows) {
        colIndex = actualIndex;
        for (int k = 0; k < resultDS.Tables[i].Columns.Count; k++) {
            if (rowIndex == 2) // For Worksheet Header
                xlWorkSheet.Cells[rowIndex - 1, colIndex] = resultDS.Tables[i].Columns[k].ColumnName;
            xlWorkSheet.Cells[rowIndex, colIndex] = row[resultDS.Tables[i].Columns[k]];
            colIndex++;
        }
        rowIndex++;
    }
}
// Save Excel file with different parameters. You can speficy parameters according to your requirement
xlWorkBook.SaveAs(@ "Yourpath\dtEmployee.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Close and Quit the Excel
xlWorkBook.Close();
xlApp.Quit();

Result

After successfully execution of code, the final result you will get: Code merge/append all the data tables side by side, as shown in the figure.