/// <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);
}
