/// <summary>
/// 将表格存储为Excel
/// </summary>
/// <param name="path">路径</param>
/// <param name="dt">表</param>
/// <param name="sheetName">sheet表名</param>
/// <param name="hasFieldRow">是否转表名</param>
/// <returns></returns>
public static bool SaveAsExcel(string path, DataTable dt, string sheetName, bool hasFieldRow = true)
{
try
{
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
IWorkbook workbook;
//新建工作目录
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook();
break;
default:
workbook = new HSSFWorkbook();
break;
}
ISheet sheet;
//表名
if (!string.IsNullOrWhiteSpace(sheetName))
{
//创建工作表
sheet = workbook.CreateSheet(sheetName);
}
else
{
int i = 1;
while (true)
{
//判断表名是否存在
if (workbook.GetSheetIndex("sheet" + i) == -1)
{
//创建表
sheet = workbook.CreateSheet("sheet" + i);
}
else
{
i++;
}
}
}
//创建表是否成功
if (sheet == null)
{
return false;
}
#region//添加表头
if (hasFieldRow)//判断是否添加表头
{
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
#endregion
#region//添加数据
//添加表头为1,不添加为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//获取第i行
DataRow dr = dt.Rows[i];
//创建行
IRow cells = sheet.CreateRow(i + starRow);
//遍历列
for (int j = 0; j < dr.ItemArray.Length; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//设置单元格值
cell.SetCellValue(dr.ItemArray[j].ToString());
}
}
#endregion
//保存至Path
bool success= Export(workbook, path);
return success;
}
catch
{
return false;
}
}
/// <summary>
/// 获取指定excel文件后缀的格式类型,Nullable为可控类型如Nullable<int32>
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private static Nullable<ExcelType> GetExcelFileType(string fileName)
{
var ext = Path.GetExtension(fileName);
if (!string.IsNullOrWhiteSpace(ext) && (ext.ToLower() == ".xls" ||
ext.ToLower() == ".xlsx"))
return ext.ToLower() == ".xls" ? ExcelType.xls : ExcelType.xlsx;
else
return null;
}
/// <summary>
/// 导出至savePath
/// </summary>
/// <param name="workbook"></param>
/// <param name="savePath"></param>
private static bool Export(IWorkbook workbook, string savePath)
{
try
{
// 写入 ,创建其支持存储区为内存的流
System.IO.MemoryStream ms = new System.IO.MemoryStream();
//写入内存
workbook.Write(ms);
workbook = null;
//
FileStream fs = new FileStream(savePath, FileMode.Create,
FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
//关闭
ms.Close();
//释放
ms.Dispose();
fs.Close();
return true;
}
catch
{
return false;
}
}
C#NPOI将DATa Table存储为Excel
最新推荐文章于 2023-02-17 13:26:14 发布