c# - 带有附加工作表的 Excel 导出

Question

我正在将 a 导出DataSet到 Excel ( .xlsx)，无论出于何种原因，我的工作簿中都添加了一个额外的（不需要的）工作表。

我dataTables的设置如下：

DataTable table1 = new DataTable();
table1 = CallReport("ReportName");
DataTable table2 = new DataTable();
table2 = CallReport("ReportName");
DataTable table3 = new DataTable();
table3 = CallReport("ReportName");

//add to DataSet
DataSet exportSet = new DataSet();
table3.TableName = "Combined";
exportSet.Tables.Add(table3);
table2.TableName = "Non-Services";
exportSet.Tables.Add(table2);
table1.TableName = "Services";
exportSet.Tables.Add(table1);

然后我创建一个 Excel 实例，然后使用foreach循环我DataSet的表并将表写入 Excel 工作簿：

MSClass.CreateExcelFile();

foreach (DataTable table in exportSet.Tables)
{
    MSClass.WriteToExcel(table, table.TableName);
}

MSClass.SaveExcelFile(exportPath);

以下是我创建 Excel 文件、写入它并将其保存在我的MSClass：

class MSClass
{
    static Microsoft.Office.Interop.Excel.Application xl;

    public static void CreateExcelFile()
    {
        //Creates new instance of Excel.Application()
        xl = new Excel.Application();
        //Adds new workbook to the application instance
        xl.Workbooks.Add();
    }

    public static void SaveExcelFile(string filePath)
    {
        //Check to make sure the filePath isn't empty, and if it is, just show the file instance
        if (filePath != null && filePath != "")
        {
            //in case there's an error
            try
            {   
                //Makes sure the sheet is in the active session
                Excel._Worksheet sheet = xl.ActiveSheet;
                //Stops from asking to overwrite the file. 
                //If I didn't want to overwrite I would be changing the file name everytime. 
                xl.DisplayAlerts = false;
                //Saves the sheet to the file path
                sheet.SaveAs(filePath);
                //shows the final product
                xl.Visible = true;
            }
            catch (Exception ex)
            {
                //throw up an Exception for any error and show the error message
                throw new Exception("Export To Excel: Excel file could not be saved! Check filePath.\n" + ex.Message);
            }
        }
        else    //no file path is given
        {
            //Just show the current session of the Excel Application
            xl.Visible = true;
        }
    }

    public static void WriteToExcel(DataTable table, string sheetName)
    {
        //Adds new worksheet to workbook
        Excel._Worksheet sheet = xl.ActiveWorkbook.Sheets[1];
        //Gets count of columns within supplied DataTable
        int colCount = table.Columns.Count;
        //Creates object array for headers for each column
        object[] header = new object[colCount];
        //loop to add column names to header object array
        for (int i = 0; i < colCount; i++)
        {
            //Adds column names to header object array
            header[i] = table.Columns[i].ColumnName;
        }

        //Get range of headers
        Excel.Range headerRange = sheet.get_Range((Excel.Range)(sheet.Cells[1, 1]), (Excel.Range)(sheet.Cells[1, colCount]));
        //Applies the header to Excel file
        headerRange.Value = header;
        //Adds color to header to make more distinct
        headerRange.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
        //Bolds the header
        headerRange.Font.Bold = true;
        //gets count of rows from DataTable
        int rowCount = table.Rows.Count;
        //Object multidimensional array for the cells within the dataTable 
        object[,] cells = new object[rowCount, colCount];
        //Adds the cells from DataTable to cell Object array
        for (int j = 0; j < rowCount; j++)
        {
            for (int i = 0; i < colCount; i++)
            {
                //Sets cell values to DataTable values
                cells[j, i] = table.Rows[j][i];
            }
        }

        //prints the cell values to Excel cell(s)
        sheet.get_Range((Excel.Range)(sheet.Cells[2, 1]), (Excel.Range)(sheet.Cells[rowCount + 1, colCount])).Value = cells;

        sheet.Name = sheetName;
        sheet.Activate();
        xl.Worksheets.Add(sheet);
    }
}

我不确定当前是什么导致额外的工作表被导出。工作表按以下顺序导出：Sheet4 | Services | Non-Services | Combined 其中Sheet4完全空白，其他工作表以相反的顺序导出。

是什么导致我的额外（空白）工作表被添加到工作簿中？

标签： c#excel