/// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="sheetName">导出的Sheet 名称</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="fileName">文件名称</param> /// <returns></returns> public MemoryStream DataTableToExcelSession(DataTable data, string sheetName, bool isColumnWritten, string fileName) { int i = 0; int j = 0; int count = 0; ISheet sheet = null; if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); try { if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return null; } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } //合并行行列索引 sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 10)); //sheet.AddMergedRegion(new CellRangeAddress(4, 4, 0, 10)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 0, 0)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 1, 1)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 2, 2)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 3, 3)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 4, 4)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 5, 5)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 6, 6)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 7, 7)); if (workbook != null) { System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return ms; } return null; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } }