正文
NPOI的使用Excel模板导出 可插入到指定行
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
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();
if (sheet != null && source != null && source.Rows.Count > )
{
IRow row; ICell cell;
//获取需插入数据的首行样式
IRow styleRow = sheet.GetRow(rowIndex);
if (styleRow == null)
{
for (int i = , len = source.Rows.Count; i < len; i++)
{
row = sheet.CreateRow(rowIndex);
//创建列并插入数据
for (int index = ; 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 = , len = source.Rows.Count; i < len; i++)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = styleRow.HeightInPoints;
row.Height = styleRow.Height;
//创建列并插入数据
for (int index = ; 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 - ], 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[] { "name1", "name2" }, );
}
//前台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();
if (sheet != null && source != null && source.Rows.Count > )
{
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 = , len = source.Rows.Count; i < len; i++)
{
row = sheet.CreateRow(rowIndex);
//创建列并插入数据
for (int index = ; 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;
}
}
