如何添加额外的工作表的Excel数据表，从 [英] How to add additional worksheets to an Excel from DataTable

问题描述

我试图创建一个Excel报表具有多个工作表。每个表都有来自一个DataTable而导致的SQL查询的数据。该inital工作正确地创建，但是，我不能创建第二个工作表。我知道我在做什么错了，但我只是不知道如何使用低于code添加一个工作表中，而无需创建一个新的Excel工作簿中的一切。

我在想刚刚创建的工作簿，然后只需添加表。但我似乎无法使这一切的点击。

这就是我想：

 静态无效的主要（字串[] args）
{
        Excel_FromDataTable（testingTable）;
        Excel_FromDataTable（testingTable）;
    }
}

私有静态无效Excel_FromDataTable（数据表DT）
{
    VAR的Excel =新Microsoft.Office.Interop.Excel.Application（）;
    VAR工作簿= excel.Workbooks.Add（真正的）;

    INT ICOL = 0;
    的foreach（在dt.Columns的DataColumn C）
    {
        ICOL ++;
        excel.Cells [1，ICOL] = c.ColumnName;
    }

    INT iRow = 0;
    的foreach（在dt.Rows的DataRow R）
    {
        iRow ++;
        //添加每一行的单元格的数据...
        ICOL = 0;
        的foreach（在dt.Columns的DataColumn C）
        {
            ICOL ++;
            excel.Cells [iRow + 1，ICOL] = R [c.ColumnName];
        }
    }
    //全球丢失的引用，因为我们没有定义对象...
    反对失踪= System.Reflection.Missing.Value;
    //如果想保存工作簿...
    workbook.SaveAs（@C：\ MyExcelWorkBook2.xlsx）;
    workbook.Close（）;
}
 

解决方案

考虑这个

 静态无效的主要（）
{

    VAR的Excel =新Microsoft.Office.Interop.Excel.Application（）;
    VAR工作簿= excel.Workbooks.Add（真正的）;

    AddExcelSheet（DT1，工作簿）;
    AddExcelSheet（DT2，工作簿）;

    workbook.SaveAs（@C：\ MyExcelWorkBook2.xlsx）;
    workbook.Close（）;

}

私有静态无效AddExcelSheet（DataTable的DT，工作簿WB）
{
    Excel.Sheets表= wb.Sheets;
    Excel.Worksheet newSheet = sheets.Add（）;
    INT ICOL = 0;
    的foreach（在dt.Columns的DataColumn C）
    {
        ICOL ++;
        newSheet.Cells [1，ICOL] = c.ColumnName;
    }

    INT iRow = 0;
    的foreach（在dt.Rows的DataRow R）
    {
        iRow ++;
        //添加每一行的单元格的数据...
        ICOL = 0;
        的foreach（在dt.Columns的DataColumn C）
        {
            ICOL ++;
            newSheet.Cells [iRow + 1，ICOL] = R [c.ColumnName];
        }
}
 

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();
}

解决方案

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];
        }
}

这篇关于如何添加额外的工作表的Excel数据表，从的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！