第一种：插入到指定新建下标下
/// <summary>
/// 往Excel里面插入DataTable
/// </summary>
/// <param name="path">excel路径</param>
/// <param name="dt">表</param>
/// <param name="aimRowIndex">从第几行开始</param>
/// <param name="insertIndex">下标</param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool InsertDataoExcel(string path, DataTable dt, int insertIndex, int aimRowIndex = 0, 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;
int name_i = 1;
while (true)
{
if (workbook.GetSheetIndex("Sheet" + name_i) == -1)
{
//判断插入下标是否符合要求
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建工作表
sheet = workbook.CreateSheet("Sheet" + name_i);
//设置表的索引下标
workbook.SetSheetOrder("Sheet" + name_i, insertIndex);
}
break;
}
else
{
name_i++;
}
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable（包括表头信息）
if (insertIndex < workbook.NumberOfSheets)//插入的下标不能在已有数据范围
{
if (hasFieldRow)
{
//添加表头
IRow headRow = sheet.CreateRow(0 + aimRowIndex);
for (int i = 0; i < dt.Columns.Count; i++)
{
//获取单元格
ICell cell = headRow.CreateCell(i);
//设置单元格数据类型
cell.SetCellType(CellType.String);
//单元格赋值
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
//添加表头starRow为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>
/// 往Excel里面插入DataTable
/// </summary>
/// <param name="path">excel路径</param>
/// <param name="dt">表</param>
/// <param name="sheetName">sheet表名</param>
/// <param name="insertIndex">下标</param>
/// <param name="hasFieldRow"></param>
/// <returns></returns>
public static bool InsertDataoExcel(string path, DataTable dt, string sheetName, int insertIndex, 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.GetSheetIndex(sheetName) == -1)
{
if (insertIndex > (workbook.NumberOfSheets + 1))
{
System.Windows.Forms.MessageBox.Show("不能将表插入该索引下！");
}
else
{
//创建表
sheet = workbook.CreateSheet(sheetName);
//设置表的索引下标
workbook.SetSheetOrder(sheetName, insertIndex);
}
}
else
{
System.Windows.Forms.MessageBox.Show(sheetName+ "表创建失败," + sheetName+"表已经存在!");
return false;
}
//是否已经获取工作表，如果没有则直接返回
if (sheet == null)
{
return false;
}
#region//往表中追加Datatable
if (insertIndex < workbook.NumberOfSheets)//插入的下标不能在已有数据范围
{
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);
}
}
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;
}
}