/// <summary>
/// 追加记录DataTable(不含表头,将表内容直接追加进去至末尾)
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">目标表下标</param>
/// <param name="dt">表格</param>
/// <returns></returns>
public static bool AppendDataTableToExcel(string path, int sheetIndex, DataTable dt,bool hasFieldRow = true)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现,xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空!");
}
//是否已经获取工作表,如果没有则直接返回
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable
if (hasFieldRow)//判断是否添加表头
{
//添加表头(当表为空时从0开始,不为空则加1)
IRow headRow = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName.ToString());
}
}
//添加表头为1,不添加表头为0
int starRow;
if (hasFieldRow)
{
starRow = 1;
}
else
{
starRow = 0;
}
//遍历行
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
IRow cells = sheet.CreateRow(sheet.LastRowNum + starRow);
//遍历列
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
ICell cell = cells.CreateCell(j);
//设置单元格属性
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
#endregion
//保存
bool success = Export(workbook, path);
return success;
}
}
catch
{
return false;
}
}
/// <summary>
/// 追加记录DataRow
/// </summary>
/// <param name="path">路径</param>
/// <param name="sheetIndex">目标表下标</param>
/// <param name="dr">行数据</param>
/// <returns></returns>
public static bool AppendDataRowToExcel(string path, int sheetIndex, DataRow dr)
{
try
{
//获取文件流
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook;
//获取excel的文件类型
ExcelType type = GetExcelFileType(path).Value;
//通过不同的文件类型创建不同的读取接口(xls使用HSSFWorkbook类实现,xlsx使用XSSFWorkbook类实现)
switch (type)
{
case ExcelType.xlsx:
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(stream);
break;
}
//
ISheet sheet = null;
//获取工作表,默认获取第一张
if (workbook.NumberOfSheets > sheetIndex && workbook.NumberOfSheets > 0)
{
//获取工作表
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
System.Windows.Forms.MessageBox.Show("索引超出界限或该文件表格为空!");
}
//是否已经获取工作表,如果没有则直接返回
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable(没有追加表头数据)
//创建行单元(当表为空时从0开始,不为空则加1)
IRow cells = sheet.CreateRow(sheet.LastRowNum+(sheet.LastRowNum==0?0:1));
//遍历列
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
//保存
bool success = Export(workbook, path);
return success;
}
}
catch
{
return false;
}
}
/// <summary>
/// 将表格存储为Excel
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static bool Export(IWorkbook workbook,string path)
{
try
{
FileInfo file = new FileInfo(path);
if (file.Exists)
{
file.Delete();
file = new FileInfo(path);
}
// 写入 ,创建其支持存储区为内存的流
System.IO.MemoryStream ms = new System.IO.MemoryStream();
//写入内存
workbook.Write(ms);
workbook = null;
//
FileStream fs = new FileStream(path, 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 (Exception ex)
{
throw new Exception(ex.Message);
}
}
C#NPOI 往excel里面追加一行或者一个Datatable
最新推荐文章于 2023-08-26 23:24:43 发布