C# 如何在同一个execl文件中创建多个sheet表

Question

//结算单的DATAGRID导出EXECL中
        public void OutPutExcel(DataGridView dg, string lei, string pamtime, string printtime)
        {
            Excel.Application excel;
            Excel.Workbook xBk;
            Excel.Worksheet xSt;
            excel = new Excel.ApplicationClass(); 
            xBk = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            excel.Application.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            excel.Application.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); 
            xSt = (Excel.Worksheet)xBk.Sheets[1];  
            //第一个sheet
            xSt.Name = "111";
            xSt.Cells[1, 1] = "11";
            //第二个sheet
            xSt = (Excel.Worksheet)xBk.Sheets[2];
            xSt.Name = "222";
            xSt.Cells[1, 1] = "222";            //第三个sheet
            xSt = (Excel.Worksheet)xBk.Sheets[3];
            xSt.Name = "3333";
            xSt.Cells[1, 1] = "3333";
                        excel.Visible = true;  //EXECL可见
            excel.Cells.WrapText = true;  
        }

Answer 1

//DATASET 导EXCEL
        int state = 0;
        int tablecount = ds.Tables.Count;
        if (tablecount <= 0) return 0;
        int index = 0;
        Excel.Application xlApp = null;
        Excel.Workbook xlBook = null;
        Excel.Worksheet xlSheet = null;
        int rowIndex = 1;
        int colIndex = 0;
        object missing = Missing.Value;        List<System.Data.DataTable> listTable = new List<System.Data.DataTable>();
        try
        {
            xlApp = new Excel.Application();            xlBook = xlApp.Workbooks.Add(true);            foreach (System.Data.DataTable dt in ds.Tables)
            {
                index++;
                rowIndex = 1;
                colIndex = 0;                xlSheet = (Excel.Worksheet)xlApp.Worksheets[1];                xlSheet.Name = dt.TableName;                foreach (DataColumn Col in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    xlApp.Cells[1, colIndex] = " " + Col.ColumnName;
                } 
                int rowNum = dt.Rows.Count;
                int colNum = dt.Columns.Count;
                string[,] finalData = new string[rowNum, colNum];
                for (int i = 0; i < rowNum; i++)
                {
                    for (int j = 0; j < colNum; j++)
                    {
                        finalData[i, j] = dt.Rows[i][j].ToString();
                    }
                }
                xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, colIndex]).Font.Bold = true;
                xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
                xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).Value2 = finalData;
                xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).NumberFormatLocal = "@";                if (index < tablecount) xlSheet = (Excel.Worksheet)xlApp.Worksheets.Add(missing, missing, missing, missing);
            }            if (System.IO.File.Exists(filename)) System.IO.File.Delete(filename);
            xlApp.Visible = false;
            xlSheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            state = 1;
        }
        finally
        {
            object saveChange = true;
            xlBook.Close(saveChange, filename, missing);
            xlApp.Quit();
            if(xlSheet!=null) Marshal.ReleaseComObject(xlSheet);
            if (xlBook != null) Marshal.ReleaseComObject(xlBook);
            if (xlApp != null) Marshal.ReleaseComObject(xlApp);
            xlApp = null;
            xlBook = null;
            xlSheet = null;
            GC.Collect();
        }
        return state;