//导出通用方法
不是最好的方案,仅供借鉴参考,直接看代码吧!
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;
namespace Test
{
public class ExcelHelper
{
/// <summary>
/// DataTable转Excel文件
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="modelExlPath">模板相对路径</param>
/// <param name="startRow">从哪一行开始 0</param>
/// <param name="path">相对路径</param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable dt, string modelExlPath,int startRow,string path)
{
string tmpRootDir = AppDomain.CurrentDomain.BaseDirectory;//获取程序根目录
try
{
XSSFWorkbook hssfworkbookDown; //创建一个excel对象
//读入刚复制的要导出的excel文件
using (FileStream file = new FileStream(tmpRootDir + modelExlPath, FileMode.Open, FileAccess.Read)) //路径,打开权限,读取权限
{
hssfworkbookDown = new XSSFWorkbook(file);
file.Close();
}
//模版的一个页面在GetSheetAt方法中,这里取第一个页面是0
XSSFSheet sheet1 = (XSSFSheet)hssfworkbookDown.GetSheetAt(0);
//开始向excel表格中写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//从哪一行开始录入 动态变量
IRow row = sheet1.CreateRow(i + startRow);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//创建文件
using (FileStream files = new FileStream(tmpRootDir + path, FileMode.Create))
{
hssfworkbookDown.Write(files);
}
}
catch (Exception e)
{
return false;
}
return true;
}
}
}