在写C#脚本的时候,有一个这样的需求:
根据一张Excle表中的邮箱字段,来生成多张表,大概样子就是下图这样(图丑了点)
NPOI版本使用的2.3版本
使用方式:
DataTable dt = new DataTable(); //准备好要插入的数据源。 ExportExcelByTemple(dt,strFileName,templetPath,20,3);
下面就是C#NPOI向已有的Excle模版插入数据的封装方法,调用很简单。
/// <summary> /// 用模板导出Excel /// </summary> /// <param name="table"></param> /// <param name="strFileName">导出路径</param> /// <param name="templetPath">模板路径</param> /// <param name="startRow">从第几行开始写数据,从1开始</param> public static void ExportExcelByTemple(System.Data.DataTable dtSource, string strFileName,string templetPath, int rowHeight, int startRow) { try { HSSFWorkbook workbook = getWorkBook(templetPath); HSSFSheet sheet = getSheet(workbook); writeData(workbook, sheet, dtSource, strFileName, rowHeight, startRow); saveData(workbook, strFileName); } catch (Exception ex) { //LogInfo.Log(ex); throw ex; } } /// <summary> /// 解析Excel模板,返回WorkBook /// </summary> /// <param name="templetPath"></param> /// <returns></returns> private static HSSFWorkbook getWorkBook(string templetPath) { FileStream file = new FileStream(templetPath, FileMode.Open, FileAccess.Read); HSSFWorkbook workbook = new HSSFWorkbook(file); return workbook; } /// <summary> /// 返回Sheet /// </summary> /// <param name="workbook"></param> /// <returns></returns> private static HSSFSheet getSheet(HSSFWorkbook workbook) { return workbook.GetSheetAt(0); } /// <summary> /// /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="dtSource"></param> /// <param name="strFileName"></param> /// <param name="rowHeight"></param> /// <param name="startRow"></param> /// <param name="size"></param> private static void writeData(HSSFWorkbook workbook, HSSFSheet sheet, System.Data.DataTable dtSource,string strFileName, int rowHeight, int startRow) { // //填充表头 HSSFRow dataRow = new HSSFRow(); //填充内容 for (int i = 0; i < dtSource.Rows.Count; i++) { dataRow = sheet.CreateRow(i + startRow - 1); dataRow.Height = (short)(rowHeight * 20); for (int j = 0; j < dtSource.Columns.Count; j++) { string drValue = dtSource.Rows[i][j].ToString(); dataRow.CreateCell(j).SetCellValue(drValue); } } } /// <summary> /// 保存数据 /// </summary> /// <param name="workbook"></param> /// <param name="strFileName"></param> private static void saveData(HSSFWorkbook workbook, string strFileName) { //保存 using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } }
原文链接:C#使用NPOI.dll向已有的Excle模版插入数据,转载请注明来源!