99 lines
3.3 KiB
C#
99 lines
3.3 KiB
C#
using NPOI.SS.UserModel;
|
||
using System;
|
||
using System.Data;
|
||
using System.IO;
|
||
|
||
namespace YiDa_WinForm.Utils
|
||
{
|
||
/// <summary>
|
||
/// Excel 解析工具类
|
||
/// </summary>
|
||
public static class ExcelHelper
|
||
{
|
||
/// <summary>
|
||
/// 解析Excel为DataTable
|
||
/// </summary>
|
||
public static DataTable GetExcel(string filePath)
|
||
{
|
||
IWorkbook iwkX = null;
|
||
DataTable dt = new DataTable();
|
||
try
|
||
{
|
||
// 使用 using 包裹 FileStream，确保资源快速释放，且减少文件占用
|
||
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite,
|
||
4096, FileOptions.SequentialScan))
|
||
{
|
||
iwkX = WorkbookFactory.Create(fs);
|
||
}
|
||
|
||
if (iwkX == null || iwkX.NumberOfSheets == 0)
|
||
{
|
||
LogHelper.AppendLog("Excel文件中无Sheet，解析为空");
|
||
return dt;
|
||
}
|
||
|
||
ISheet sheet = iwkX.GetSheetAt(0);
|
||
if (sheet.LastRowNum < 1) // 无数据行（仅表头）
|
||
{
|
||
LogHelper.AppendLog("Excel文件中无有效数据行");
|
||
return dt;
|
||
}
|
||
|
||
// 读取表头（减少重复判断）
|
||
IRow headerRow = sheet.GetRow(0);
|
||
for (int i = 0; i < headerRow.LastCellNum; i++)
|
||
{
|
||
ICell cell = headerRow.GetCell(i);
|
||
string columnName = cell == null ? $"未知列_{i}" : cell.ToString().Trim();
|
||
if (dt.Columns.Contains(columnName))
|
||
{
|
||
columnName += $"_{i}";
|
||
}
|
||
|
||
dt.Columns.Add(columnName);
|
||
}
|
||
|
||
// 批量读取数据行，减少空行判断的冗余操作
|
||
int startRow = 1; // 跳过表头
|
||
for (int i = startRow; i <= sheet.LastRowNum; i++)
|
||
{
|
||
IRow dataRow = sheet.GetRow(i);
|
||
if (dataRow == null) continue;
|
||
|
||
DataRow dr = dt.NewRow();
|
||
bool isEmptyRow = true; // 空行标记
|
||
|
||
for (int j = 0; j < dt.Columns.Count; j++)
|
||
{
|
||
ICell cell = dataRow.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
|
||
string cellValue = cell.ToString().Trim();
|
||
dr[j] = cellValue;
|
||
|
||
// 优化4：只要有一个单元格非空，就不是空行（减少后续 All 遍历）
|
||
if (!string.IsNullOrEmpty(cellValue))
|
||
{
|
||
isEmptyRow = false;
|
||
}
|
||
}
|
||
|
||
if (!isEmptyRow)
|
||
{
|
||
dt.Rows.Add(dr);
|
||
}
|
||
}
|
||
|
||
LogHelper.AppendLog($"Excel解析成功：{dt.Rows.Count}行数据，{dt.Columns.Count}列");
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.AppendLog($"Excel解析失败：{ex.Message}");
|
||
}
|
||
finally
|
||
{
|
||
iwkX?.Close();
|
||
}
|
||
|
||
return dt;
|
||
}
|
||
}
|
||
} |