遇到一個項目，將三個表的數據導出到同一表的同一SHeet頁，測試了N多方法未能實現，最后三個表添加到同一dataset，然后導出，遇到問題，CreateRow和GetRow的不同，將代碼貼出，歡迎大神們指導

public static bool GridToExcels(List<DataTable> dts, string strExcelFileName, int indexType){int iRowIndex;bool BSave = false;try{HSSFWorkbook workbook = new HSSFWorkbook();//DataSet set = new DataSet();DataTable dt = dts[0];//DataTable dt1 = dts[1];//DataTable dt2 = dts[2];ISheet sheet = workbook.CreateSheet("三固定表");//創建sheet頁ICellStyle HeadercellStyle = workbook.CreateCellStyle();//表格格式HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//字體NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();headerfont.Boldweight = (short)FontBoldWeight.Bold;HeadercellStyle.SetFont(headerfont);foreach (DataTable dt in dts){CellRangeAddress region = new CellRangeAddress(0, 0, 3, 4);//合并單元格CellRangeAddress region1 = new CellRangeAddress(0, 0, 5, 6);CellRangeAddress region2 = new CellRangeAddress(0, 0, 7, 8);CellRangeAddress region3 = new CellRangeAddress(0, 0, 9, 10);sheet.AddMergedRegion(region);sheet.AddMergedRegion(region1);sheet.AddMergedRegion(region2);sheet.AddMergedRegion(region3);IRow headerRow = sheet.CreateRow(2);IRow row1 = sheet.CreateRow(0);//創建第一行ICell cell0 = row1.CreateCell(0);//創建第一行第一列cell0.SetCellValue("時間");cell0.CellStyle = HeadercellStyle;ICell cell1 = row1.CreateCell(1);//創建第一行第二列cell1.SetCellValue("號");cell1.CellStyle = HeadercellStyle;ICell cell2 = row1.CreateCell(3);//創建第一行第三列cell2.SetCellValue("第一崗");cell2.CellStyle = HeadercellStyle;ICell cell3 = row1.CreateCell(5);//創建第一行第四列cell3.SetCellValue("第二崗");cell3.CellStyle = HeadercellStyle;ICell cell4 = row1.CreateCell(7);//創建第一行第五列cell4.SetCellValue("第三崗");cell4.CellStyle = HeadercellStyle;ICell cell5 = row1.CreateCell(9);//創建第一行第六列cell5.SetCellValue("第四崗");cell5.CellStyle = HeadercellStyle;//創建第二行IRow row2 = sheet.CreateRow(1);//創建第一行ICell cell10 = row2.CreateCell(0);//創建第一行第一列cell10.SetCellValue("week");cell10.CellStyle = HeadercellStyle;ICell cell11 = row2.CreateCell(1);//創建第一行第二列cell11.SetCellValue("roomID");cell11.CellStyle = HeadercellStyle;ICell cell12 = row2.CreateCell(3);//創建第一行第三列cell12.SetCellValue("06:00");ICell cell22 = row2.CreateCell(4);//創建第一行第三列cell22.SetCellValue("08:00");cell22.CellStyle = HeadercellStyle;ICell cell13 = row2.CreateCell(5);//創建第一行第四列cell13.SetCellValue("08:00");cell13.CellStyle = HeadercellStyle;ICell cell23 = row2.CreateCell(6);//創建第一行第四列cell23.SetCellValue("21:00");cell23.CellStyle = HeadercellStyle;ICell cell14 = row2.CreateCell(7);//創建第一行第五列cell14.SetCellValue("01:00");cell14.CellStyle = HeadercellStyle;ICell cell24 = row2.CreateCell(8);//創建第一行第五列cell24.SetCellValue("04:00");cell24.CellStyle = HeadercellStyle;ICell cell15 = row2.CreateCell(9);//創建第一行第六列cell15.SetCellValue("04:00");cell15.CellStyle = HeadercellStyle;ICell cell25 = row2.CreateCell(10);//創建第一行第六列cell25.SetCellValue("06:00");cell25.CellStyle = HeadercellStyle;int icolIndex = 0; if (dt.TableName == "值班表"){ICellStyle cellStyle = workbook.CreateCellStyle();//為避免日期格式被Excel自動替換，所以設定 format 為 『@』 表示一率當成text來看cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();cellfont.Boldweight = (short)FontBoldWeight.Normal;cellStyle.SetFont(cellfont);int iRowIndex1 = 1;//新建150行，while(iRowIndex1< 150){IRow DataRow = sheet.CreateRow(iRowIndex1);iRowIndex1++;}if (indexType == 1){//建立內容行iRowIndex = 3;//第三行int iCellIndex = 0;//;第0列foreach (DataRow Rowitem in dt.Rows){IRow DataRow = sheet.GetRow(iRowIndex);foreach (DataColumn Colitem in dt.Columns){ICell cell = DataRow.CreateCell(iCellIndex);cell.SetCellValue(Rowitem[Colitem].ToString());cell.CellStyle = cellStyle;iCellIndex++;}iCellIndex = 0;iRowIndex++;}for (int i = 0; i < icolIndex; i++){sheet.AutoSizeColumn(i);}}}else if (dt.TableName == "值日表"){ICellStyle cellStyle = workbook.CreateCellStyle();if (indexType == 1){//建立內容行iRowIndex = 3;int iCellIndex2 = 12;foreach (DataRow Rowitem in dt.Rows){IRow DataRow = sheet.GetRow(iRowIndex);foreach (DataColumn Colitem in dt.Columns){ICell cell = DataRow.CreateCell(iCellIndex2);cell.SetCellValue(Rowitem[Colitem].ToString());cell.CellStyle = cellStyle;iCellIndex2++;}iCellIndex2 = 12;iRowIndex++;}//自適應列寬for (int i = 0; i < icolIndex; i++){sheet.AutoSizeColumn(i);}}} else if (dt.TableName == "床位表"){ICellStyle cellStyle = workbook.CreateCellStyle();if (indexType == 1){//建立內容行iRowIndex = 3;int iCellIndex2 = 17;foreach (DataRow Rowitem in dt.Rows){IRow DataRow = sheet.GetRow(iRowIndex);foreach (DataColumn Colitem in dt.Columns){ICell cell = DataRow.CreateCell(iCellIndex2);cell.SetCellValue(Rowitem[Colitem].ToString());cell.CellStyle = cellStyle;iCellIndex2++;}iCellIndex2 = 17;iRowIndex++;}//自適應列寬for (int i = 0; i < icolIndex; i++){sheet.AutoSizeColumn(i);}}}FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);workbook.Write(file);file.Flush();file.Close();BSave = true;}}catch (Exception ex){throw new Exception(ex.Message);}return BSave;}

總結

以上是生活随笔為你收集整理的C#NPoi，将多个表数据导出到同一个ECXCL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。