/// <summary>
/// datatable 写入现有的EXCEL
/// </summary>
/// <param name="dt_source">需要写入的数据源</param>
/// <param name="filepath">excel 文件路径</param>
/// <param name="sheetname">哪个页面 写</param>
/// <param name="startrow">从第几行开始写 </param>
public static void DtWriteExitExcel(DataTable dt_source, string filepath, string sheetname, int startrow)
{
//1.读取现有的EXCEL 文件 获取 IWorkbook 记得关闭 文件流 这里只是要获取SHEET格式
FileStream fs = null;
IWorkbook workbook = null;
ISheet xSheet = null;
fs = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (filepath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (filepath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
fs.Close();
fs.Dispose();
//2.获取需要的SHEET 一个excel 由很多的页组成 这边 sheetname 就是获取需要哪个页的SHEET 格式
try
{
xSheet = workbook.GetSheetAt(workbook.GetSheetIndex(sheetname));
//3.赋值 根据这个SHEET 来新建行 赋值 单元格 这里startrow 是可以指定从第几行开始写入内容
for (int i = 0; i < dt_source.Rows.Count; i++)
{
IRow row1 = xSheet.CreateRow(i + startrow);
for (int j = 0; j < dt_source.Columns.Count; j++)
{
ICell celli = row1.CreateCell(j);
celli.SetCellValue(dt_source.Rows[i][j].ToString());
}
}
//4.再次打开这个文件然后将刚刚的 workbook 写入进去
FileStream fileStream = File.Open(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fileStream);
//5.这里 不能把 workbook.Dispose(); 写到写入下面因为是天然异步的 所以就是没有写进去就被你销毁了
//workbook.Dispose();
fileStream.Close();
}
catch (Exception EX)
{
//6.这边是报错的时候销毁流 不然导致文件锁死状态 只能重启电脑才能打开文件了
fs.Close();
workbook.Close();
fs.Dispose();
}
}
/// <summary>
/// 现有的EXCEL写入datatable
/// </summary>
/// <param name="excelToDataTable">需要写入的数据源</param>
/// <param name="filepath">excel 文件路径</param>
/// <param name="sheetname">哪个页面 写</param>
public static void ExcelWriteExitDt(DataTable excelToDataTable, string filepath, string sheetname)
{
//1.读取现有的EXCEL 文件 获取 IWorkbook 记得关闭 文件流 这里只是要获取SHEET格式
FileStream fs = null;
IWorkbook workbook = null;
//ISheet sheet = null;
fs = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (filepath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (filepath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
fs.Close();
fs.Dispose();
//2.获取需要的SHEET 一个excel 由很多的页组成 这边 sheetname 就是获取需要哪个页的SHEET 格式
try
{
var sheet = workbook.GetSheetAt(workbook.GetSheetIndex(sheetname));
var rows = sheet.GetRowEnumerator();
var headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;//最后一行列数（即为总列数）
//获取第一行标题列数据源,转换为dataTable数据源的表格标题名称
for (var j = 0; j < cellCount; j++)
{
var cell = headerRow.GetCell(j);
excelToDataTable.Columns.Add(cell.ToString());
}
//获取Excel表格中除标题以为的所有数据源，转化为dataTable中的表格数据源
for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
var dataRow = excelToDataTable.NewRow();
var row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)//单元格内容非空验证
{
#region NPOI获取Excel单元格中不同类型的数据
//获取指定的单元格信息
var cell = row.GetCell(j);
switch (cell.CellType)
{
//首先在NPOI中数字和日期都属于Numeric类型
//通过NPOI中自带的DateUtil.IsCellDateFormatted判断是否为时间日期类型
case CellType.Numeric when DateUtil.IsCellDateFormatted(cell):
dataRow[j] = cell.DateCellValue;
break;
case CellType.Numeric:
//其他数字类型
dataRow[j] = cell.NumericCellValue;
break;
//空数据类型
case CellType.Blank:
dataRow[j] = "";
break;
//公式类型
case CellType.Formula:
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
dataRow[j] = eva.Evaluate(cell).StringValue;
break;
}
//布尔类型
case CellType.Boolean:
dataRow[j] = row.GetCell(j).BooleanCellValue;
break;
//错误
//case CellType.Error:
// dataRow[j] = HSSFErrorConstants.GetText(row.GetCell(j).ErrorCellValue);
// break;
//其他类型都按字符串类型来处理（未知类型CellType.Unknown，字符串类型CellType.String）
default:
dataRow[j] = cell.StringCellValue;
break;
}
#endregion
}
}
excelToDataTable.Rows.Add(dataRow);
}
}
catch (Exception EX)
{
//6.这边是报错的时候销毁流 不然导致文件锁死状态 只能重启电脑才能打开文件了
fs.Close();
workbook.Close();
//workbook.Dispose();
}
}
/// <summary>
/// 清空现有的EXCEL数据
/// </summary>
/// <param name="dt_source">需要写入的数据源</param>
/// <param name="filepath">excel 文件路径</param>
/// <param name="sheetname">哪个页面 写</param>
/// <param name="startrow">从第几行开始写 </param>
public static void ClearExcel(string filepath, string sheetname, int startrow)
{
//1.读取现有的EXCEL 文件 获取 IWorkbook 记得关闭 文件流 这里只是要获取SHEET格式
FileStream fs = null;
IWorkbook workbook = null;
ISheet xSheet = null;
fs = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (filepath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (filepath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
fs.Close();
fs.Dispose();
//2.获取需要的SHEET 一个excel 由很多的页组成 这边 sheetname 就是获取需要哪个页的SHEET 格式
try
{
xSheet = workbook.GetSheetAt(workbook.GetSheetIndex(sheetname));
//3.赋值 根据这个SHEET 来新建行 赋值 单元格 这里startrow 是可以指定从第几行开始写入内容
for (int i = 0; i < 100; i++)
{
IRow row1 = xSheet.CreateRow(i + startrow);
xSheet.RemoveRow(row1);
//for (int j = 0; j < 50; j++)
//{
// ICell celli = row1.CreateCell(j);
// celli.SetCellValue(" ");
// //celli.SetCellType(CellType.Blank);
//}
}
//4.再次打开这个文件然后将刚刚的 workbook 写入进去
FileStream fileStream = File.Open(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fileStream);
//5.这里 不能把 workbook.Dispose(); 写到写入下面因为是天然异步的 所以就是没有写进去就被你销毁了
//workbook.Dispose();
fileStream.Close();
workbook.Close();
}
catch (Exception EX)
{
//6.这边是报错的时候销毁流 不然导致文件锁死状态 只能重启电脑才能打开文件了
fs.Close();
workbook.Close();
fs.Dispose();
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="filepath">excel 文件路径</param>
/// <param name="sheetname">处理的sheetname</param>
/// <param name="startRow">从第几行开始（0开始）</param>
/// <param name="delCount">共删除N行</param>
public static void DelRow(string filepath, string sheetname, int startRow, int delCount)
{
//1.读取现有的EXCEL 文件 获取 IWorkbook 记得关闭 文件流 这里只是要获取SHEET格式
FileStream fs = null;
IWorkbook workbook = null;
ISheet xSheet = null;
fs = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (filepath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (filepath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
fs.Close();
fs.Dispose();
//2.获取需要的SHEET 一个excel 由很多的页组成 这边 sheetname 就是获取需要哪个页的SHEET 格式
xSheet = workbook.GetSheetAt(workbook.GetSheetIndex(sheetname));
//xSheet.ShiftRows(startRow + 1, xSheet.LastRowNum, -1, false, false);//删除一行（为负数只能为-1）
for (int i = 0; i < delCount; i++)
{
xSheet.ShiftRows(startRow + 1, xSheet.LastRowNum, -1);
}
//4.再次打开这个文件然后将刚刚的 workbook 写入进去
FileStream fileStream = File.Open(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fileStream);
//5.这里 不能把 workbook.Dispose(); 写到写入下面因为是天然异步的 所以就是没有写进去就被你销毁了
//workbook.Dispose();
fileStream.Close();
}
