Excel模版建议把需要添加数据行的样式设置好
模版样式
，导出后效果
【2017-11-22 对获取需插入数据的首行样式有时为空报错修改】
/// <summary> /// 根据模版导出Excel /// </summary> /// <param name="templateFile">模版路径（包含后缀） 例："~/Template/Exceltest.xls"</param> /// <param name="strFileName">文件名称（不包含后缀） 例："Excel测试"</param> /// <param name="source">源DataTable</param> /// <param name="cellKes">需要导出的对应的列字段 例：string[] cellKes = { "Date","Remarks" };</param> /// <param name="rowIndex">从第几行开始创建数据行,第一行为0</param> /// <returns>是否导出成功</returns> public static string ExportScMeeting(string templateFile, string strFileName, DataTable source, string[] cellKes, int rowIndex) { templateFile = HttpContext.Current.Server.MapPath(templateFile); int cellCount = cellKes.Length;//总列数，第一列为0 IWorkbook workbook = null; try { using (FileStream file = new FileStream(templateFile, FileMode.Open, FileAccess.Read)) { if (Path.GetExtension(templateFile) == ".xls") workbook = new HSSFWorkbook(file); else if (Path.GetExtension(templateFile) == ".xlsx") workbook = new XSSFWorkbook(file); } ISheet sheet = workbook.GetSheetAt(0); if (sheet != null && source != null && source.Rows.Count > 0) { IRow row; ICell cell; //获取需插入数据的首行样式 IRow styleRow = sheet.GetRow(rowIndex); if (styleRow == null) { for (int i = 0, len = source.Rows.Count; i < len; i++) { row = sheet.CreateRow(rowIndex); //创建列并插入数据 for (int index = 0; index < cellCount; index++) { row.CreateCell(index) .SetCellValue(!(source.Rows[i][cellKes[index]] is DBNull) ? source.Rows[i][cellKes[index]].ToString() : string.Empty); } rowIndex++; } } else { for (int i = 0, len = source.Rows.Count; i < len; i++) { row = sheet.CreateRow(rowIndex); row.HeightInPoints = styleRow.HeightInPoints; row.Height = styleRow.Height; //创建列并插入数据 for (int index = 0; index < cellCount; index++) { cell = row.CreateCell(index, styleRow.GetCell(index).CellType); cell.CellStyle = styleRow.GetCell(index).CellStyle; cell.SetCellValue(!(source.Rows[i][cellKes[index]] is DBNull) ? source.Rows[i][cellKes[index]].ToString() : string.Empty); } rowIndex++; } } } return NPOIExport(strFileName + "." + templateFile.Split('.')[templateFile.Split('.').Length - 1], workbook); } catch (Exception ex) { return ex.Message; } }
附属方法
public static string NPOIExport(string fileName, IWorkbook workbook) { try { System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.Cache.SetCacheability(System.Web.HttpCacheability.Private); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName)); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); ms.Close(); ms.Dispose(); return "导出成功"; } catch (Exception ex) { return "导出失败"; } }
调用方法
/// <summary>
/// 后台调用方法
/// </summary>
/// <returns></returns>
public string Exc()
{
return ExcelUtil.ExportScMeeting("~/Template/MonthlyRepair.xls", "ExcelName", new DataTable(), new string[2] { "name1", "name2" }, 0);
}
//前台js调用 window.open('@Url.Action("Exc")');
注：需要在指定行插入数据的话请使用NPOI自带的方法对Excel进行操作
sheet.ShiftRows(0/*开始行*/, sheet.LastRowNum/*结束行*/, 10/*插入总行数，移动大小(行数)--往下移动*/, true/*是否复制行高*/, false/*是否重置行高*/);
示例方法代码
/// <summary> /// 根据模版导出Excel /// </summary> /// <param name="templateFile">模版路径（包含后缀） 例："~/Template/Exceltest.xls"</param> /// <param name="strFileName">文件名称（不包含后缀） 例："Excel测试"</param> /// <param name="isCover">是否向下覆盖，不覆盖则在rowIndex行插入数据</param> /// <param name="source">源DataTable</param> /// <param name="cellKes">需要导出的对应的列字段 例：string[] cellKes = { "Date","Remarks" };</param> /// <param name="rowIndex">从第几行开始创建数据行,第一行为0</param> /// <returns>是否导出成功</returns> public static string ExportScMeeting(string templateFile, string strFileName, bool isCover, DataTable source, string[] cellKes, int rowIndex) { templateFile = HttpContext.Current.Server.MapPath(templateFile); int cellCount = cellKes.Length;//总列数，第一列为0 IWorkbook workbook = null; try { using (FileStream file = new FileStream(templateFile, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(file); } ISheet sheet = workbook.GetSheetAt(0); if (sheet != null && source != null && source.Rows.Count > 0) { IRow row; //是否向下覆盖 if (!isCover) sheet.ShiftRows(rowIndex, sheet.LastRowNum, source.Rows.Count, true, false); //获取需插入数据的首行样式 IRow styleRow = sheet.GetRow(isCover ? rowIndex : (rowIndex + source.Rows.Count)); for (int i = 0, len = source.Rows.Count; i < len; i++) { row = sheet.CreateRow(rowIndex); //创建列并插入数据 for (int index = 0; index < cellCount; index++) { row.CreateCell(index) .SetCellValue(!(source.Rows[i][cellKes[index]] is DBNull) ? source.Rows[i][cellKes[index]].ToString() : string.Empty); } rowIndex++; } } return NPOIExport(strFileName + ".xls", workbook); } catch (Exception ex) { return ex.Message; } }