Dotnet6 NPOI操作Excel基本操作总结
背景[*]需要对Excel进行读取和写入，目前使用Dotnet6开发环境，故直接使用。
[*]达到的效果：兼容.xls和.xlsx，识别行为空自动跳过，识别显示值，识别格式内容
步骤
[*]Dotnet 6Nuget 安装 NPOI, 具体版本 2.6.1，tips: 搜索资料时，可能NPOI 1 与 NPOI 2 可能有出入。
使用方法
[*]获取相应文档对象
public static IWorkbook OpenWorkbook(string path)
{
using (var stream = File.OpenRead(path))
{
if (Path.GetExtension(path) == ".xls")
return new HSSFWorkbook(stream);
else
return new XSSFWorkbook(stream);
}
}
[*]根据序号获取相应sheet对象，并复制该sheet，添加到原Excel第一个
public static void MoveAndCopySheet(string path, int index = 0)
{
var workbook = OpenWorkbook(path);
var sheet = workbook.GetSheetAt(index);
if (sheet == null)
throw new Exception($"Sheet 'At {index}' not found.");
var clonedSheet = workbook.CloneSheet(0);
var cloneSheetName =$"{sheet.SheetName}_copy_{DateTime.Now.ToShortTimeString().Replace(":","-")}";
workbook.SetSheetName(workbook.GetSheetIndex(clonedSheet), cloneSheetName);
workbook.SetSheetOrder(cloneSheetName, 0);
using (var stream = File.OpenWrite(path))
{
workbook.Write(stream);
}
}
[*]根据sheet名称，复制，并移动到第一个
public static void MoveAndCopySheet(string path, string sheetName)
{
var workbook = OpenWorkbook(path);
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
throw new Exception($"Sheet '{sheetName}' not found.");
workbook.SetSheetOrder(sheetName, 0);
var clonedSheet = workbook.CloneSheet(0);
workbook.SetSheetName(workbook.GetSheetIndex(clonedSheet), sheetName + "_copy");
using (var stream = File.OpenWrite(path))
{
workbook.Write(stream);
}
}
[*]循环行，判断对应单元格内容类型
public static void ProcessRows(string path, string sheetName) { var workbook = OpenWorkbook(path); var sheet = workbook.GetSheet(sheetName); if (sheet == null) throw new Exception($"Sheet '{sheetName}' not found."); for (int rowIndex = 0; rowIndex
页:
[1]