/// <summary> /// 导出Excel /// </summary> /// <param name="ds">要导出的数据源</param> /// <param name="excelFilePath">文件的物理路径</param> /// <param name="errorFun"></param> /// <returns></returns> public static bool Output(DataSet ds, string excelFilePath, Action <Exception> errorFun = null) { if (!System.IO.File.Exists(excelFilePath)) { throw new Exception("Excel 文件不存在"); } if (null == ds || ds.Tables.Count == 0) { return(false); } try { //1.0 创建工作薄 和 工作表对象 NPOI.HSSF.UserModel.HSSFWorkbook book; using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite)) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile); } int sheetIndex = 0; foreach (DataTable dt in ds.Tables) { string sheetName = dt.TableName ?? "sheet" + sheetIndex; NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表 if (null == sheet1) { sheet1 = book.CreateSheet(sheetName); } else if (sheet1.SheetName != sheetName) { book.SetSheetName(sheetIndex, sheetName); } int beginRow = 1; //2.0给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行 for (int i = 0; i < dt.Columns.Count; i++) { rowHead.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } //3.0 填充表格数据 for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + beginRow); //创建数据行 for (int j = 0; j < dt.Columns.Count; j++) //填充行数据 { rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } sheetIndex++; } //4.0 写入文件 using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create)) { book.Write(wfile); } return(true); } catch (Exception ex) { if (errorFun != null) { errorFun(ex); } return(false); } }
/// <summary> /// 导出Excel /// </summary> /// <param name="dt">DataTable:要导出的数据源</param> /// <param name="excelFilePath">string:文件的物理路径</param> /// <param name="isCustomHead">bool:是否自定义表头</param> /// <param name="tbColumnNames">List:表头</param> /// <param name="sheetName">string:sheet表名</param> /// <returns></returns> public static bool Output(DataTable dt, string excelFilePath, bool isCustomHead = false, List <string> tbColumnNames = null, string sheetName = "", Action <Exception> errorFun = null, int sheetIndex = 0) { if (!System.IO.File.Exists(excelFilePath)) { throw new Exception("Excel 文件不存在"); } if (null == dt && dt.Rows.Count == 0) { return(false); } try { //1.0 创建工作薄 和 工作表对象 NPOI.HSSF.UserModel.HSSFWorkbook book; using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite)) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile); } sheetName = string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName; NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表 if (null == sheet1) { sheet1 = book.CreateSheet(sheetName); } else if (sheet1.SheetName != sheetName) { book.SetSheetName(sheetIndex, sheetName); } //2.0给sheet1添加第一行的头部标题 if (!isCustomHead || tbColumnNames == null || tbColumnNames.Count != dt.Columns.Count) { tbColumnNames = new List <string>(); foreach (DataColumn item in dt.Columns) { tbColumnNames.Add(item.ColumnName); } } NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行 for (int i = 0; i < tbColumnNames.Count; i++) { rowHead.CreateCell(i).SetCellValue(tbColumnNames[i]); } //3.0 填充表格数据 for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1); //创建数据行 for (int j = 0; j < dt.Columns.Count; j++) //填充行数据 { rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } //4.0 写入文件 using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create)) { book.Write(wfile); } } catch (Exception ex) { if (errorFun != null) { errorFun(ex); } return(false); } return(true); }