C# – How to add additional worksheets to an Excel from DataTable

cexcelnetvsto

I'm trying to create an excel report that has multiple sheets. each of these sheets has data that comes from a DataTable that results from a SQL query. The inital worksheet is created correctly, however, i'm unable to create the second worksheet. I understand what i'm doing wrong, but i'm just not sure how to add a worksheet using the below code without creating a new excel workbook everything.

I was thinking about just creating the workbook and then just adding sheets. But I cant seem to make it all click.

This is what i'm trying:

static void Main(string[] args)
{
        Excel_FromDataTable(testingTable);
        Excel_FromDataTable(testingTable);
    }
}

private static void Excel_FromDataTable(DataTable dt)
{
    var excel = new Microsoft.Office.Interop.Excel.Application();
    var workbook = excel.Workbooks.Add(true);

    int iCol = 0;
    foreach (DataColumn c in dt.Columns)
    {
        iCol++;
        excel.Cells[1, iCol] = c.ColumnName;
    }

    int iRow = 0;
    foreach (DataRow r in dt.Rows)
    {
        iRow++;
        // add each row's cell data...
        iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
            iCol++;
            excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
        }
    }
    // Global missing reference for objects we are not defining...
    object missing = System.Reflection.Missing.Value;
    // If wanting to Save the workbook...
    workbook.SaveAs(@"C:\MyExcelWorkBook2.xlsx");
    workbook.Close();
}

Best Answer

Consider this

Static void Main()
{

    var excel = new Microsoft.Office.Interop.Excel.Application();
    var workbook = excel.Workbooks.Add(true);

    AddExcelSheet(dt1, workbook);
    AddExcelSheet(dt2, workbook);

    workbook.SaveAs(@"C:\MyExcelWorkBook2.xlsx");
    workbook.Close();

}

private static void AddExcelSheet(DataTable dt, Workbook wb)
{    
    Excel.Sheets sheets = wb.Sheets;
    Excel.Worksheet newSheet = sheets.Add();
    int iCol = 0;
    foreach (DataColumn c in dt.Columns)
    {
        iCol++;
        newSheet.Cells[1, iCol] = c.ColumnName;
    }

    int iRow = 0;
    foreach (DataRow r in dt.Rows)
    {
        iRow++;
        // add each row's cell data...
        iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
            iCol++;
            newSheet.Cells[iRow + 1, iCol] = r[c.ColumnName];
        }
}
Related Topic