493 lines
16 KiB
C#
493 lines
16 KiB
C#
using NPOI.SS.Formula.Functions;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.XSSF.UserModel;
|
||
using System.DirectoryServices;
|
||
using System.IO;
|
||
using System.Windows.Controls;
|
||
|
||
namespace Ramitta.lib
|
||
{
|
||
public static class Excel
|
||
{
|
||
|
||
public static List<Dictionary<string, string>> ReadExcelAsDictRow(
|
||
string filePath,
|
||
object sheetName = null,
|
||
List<string> headerInit = null)
|
||
{
|
||
var result = new List<Dictionary<string, string>>();
|
||
|
||
// 打开 Excel 文件
|
||
using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
|
||
{
|
||
var workbook = new XSSFWorkbook(fs);
|
||
ISheet sheet = null;
|
||
|
||
// 判断 sheetName 类型
|
||
if (sheetName is string sheetNameStr)
|
||
{
|
||
sheet = workbook.GetSheet(sheetNameStr); // 根据工作表名称获取
|
||
}
|
||
else if (sheetName is int sheetNameInt)
|
||
{
|
||
sheet = workbook.GetSheetAt(sheetNameInt); // 根据索引获取
|
||
}
|
||
else
|
||
{
|
||
sheet = workbook.GetSheetAt(0); // 默认获取第一个工作表
|
||
}
|
||
|
||
// 如果没有提供表头，则默认使用第一行作为表头
|
||
List<string> header = null;
|
||
|
||
if (headerInit != null && headerInit.Count > 0)
|
||
{
|
||
header = headerInit; // 使用提供的表头
|
||
}
|
||
else
|
||
{
|
||
var headerRow = sheet.GetRow(0);
|
||
if (headerRow == null)
|
||
return result; // 如果第一行为空，直接返回空列表
|
||
|
||
header = new List<string>();
|
||
for (int i = 0; i < headerRow.Cells.Count; i++)
|
||
{
|
||
var cell = headerRow.GetCell(i);
|
||
header.Add(cell?.ToString().Trim() ?? $"Column_{i}"); // 处理空单元格
|
||
}
|
||
}
|
||
|
||
// 如果提供了表头数据，则从第二行开始读取数据
|
||
int startRowIndex = headerInit != null && headerInit.Count > 0 ? 0 : 1;
|
||
|
||
// 遍历每一行数据
|
||
for (int rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++) // 从第二行开始
|
||
{
|
||
var row = sheet.GetRow(rowIndex);
|
||
if (row == null || IsRowEmpty(row)) continue; // 跳过空行
|
||
|
||
var rowDict = new Dictionary<string, string>();
|
||
|
||
// 遍历每一列
|
||
for (int colIndex = 0; colIndex < header.Count; colIndex++)
|
||
{
|
||
var cell = row.GetCell(colIndex);
|
||
rowDict[header[colIndex]] = cell?.ToString().Trim() ?? ""; // 直接转换为字符串
|
||
}
|
||
|
||
result.Add(rowDict);
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
public static Dictionary<string, List<string>> ReadExcelAsDictCol(
|
||
string filePath,
|
||
object sheetName = null,
|
||
List<string> headerInit = null)
|
||
{
|
||
var result = new Dictionary<string, List<string>>();
|
||
|
||
// 打开 Excel 文件
|
||
using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
|
||
{
|
||
var workbook = new XSSFWorkbook(fs);
|
||
ISheet sheet = null;
|
||
|
||
// 如果 sheetName 不为 null，按名字找到对应的工作表
|
||
if (sheetName != null && sheetName is string sheetNameStr)
|
||
{
|
||
sheet = workbook.GetSheet(sheetNameStr); // 根据工作表名称获取
|
||
}
|
||
else
|
||
{
|
||
sheet = workbook.GetSheetAt(0); // 默认第一个工作表
|
||
}
|
||
|
||
if (sheet == null)
|
||
return result; // 如果没有找到工作表，直接返回空字典
|
||
|
||
// 如果 headerInit 不为 null，使用它来初始化表头，否则从 Excel 中的第一行获取
|
||
var header = headerInit ?? new List<string>();
|
||
|
||
if (header.Count == 0)
|
||
{
|
||
// 获取第一行作为键（headers）
|
||
var headerRow = sheet.GetRow(0);
|
||
if (headerRow == null)
|
||
return result; // 如果第一行为空，直接返回空字典
|
||
|
||
for (int i = 0; i < headerRow.Cells.Count; i++)
|
||
{
|
||
var cell = headerRow.GetCell(i);
|
||
string headerText = cell?.ToString().Trim() ?? $"Column_{i}"; // 处理空单元格
|
||
header.Add(headerText);
|
||
}
|
||
}
|
||
|
||
// 初始化每一列对应的 List<string>
|
||
foreach (var headerText in header)
|
||
{
|
||
result[headerText] = new List<string>();
|
||
}
|
||
|
||
// 从第一行开始遍历数据，而不是从第二行开始
|
||
for (int rowIndex = headerInit == null ? 1 : 0; rowIndex <= sheet.LastRowNum; rowIndex++) // 修改这里，开始从第0行
|
||
{
|
||
var row = sheet.GetRow(rowIndex);
|
||
if (row == null || IsRowEmpty(row)) continue; // 跳过空行
|
||
|
||
// 遍历每一列数据并添加到对应的 List<string> 中
|
||
for (int colIndex = 0; colIndex < header.Count; colIndex++)
|
||
{
|
||
var cell = row.GetCell(colIndex);
|
||
string cellValue = cell?.ToString().Trim() ?? ""; // 直接转换为字符串
|
||
|
||
if (!string.IsNullOrEmpty(cellValue))
|
||
{
|
||
result[header[colIndex]].Add(cellValue);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
private static bool IsRowEmpty(IRow row)
|
||
{
|
||
foreach (var cell in row.Cells)
|
||
{
|
||
if (cell != null && !string.IsNullOrEmpty(cell.ToString().Trim()))
|
||
return false;
|
||
}
|
||
return true;
|
||
}
|
||
|
||
public static ICell? getRowCell(ISheet sheet, int rowIndex, object cellIndex)
|
||
{
|
||
int actualCellIndex = 0;
|
||
if (cellIndex is int intIndex)
|
||
{
|
||
// 如果输入是数字，直接使用
|
||
actualCellIndex = intIndex;
|
||
}
|
||
else if (cellIndex is string strIndex)
|
||
{
|
||
actualCellIndex = ColToIndex(strIndex);
|
||
}
|
||
else
|
||
{
|
||
return null;
|
||
}
|
||
|
||
IRow row = sheet.GetRow(rowIndex);
|
||
if (row == null) return null;
|
||
|
||
ICell cell = row.GetCell(actualCellIndex);
|
||
if (cell == null) return null;
|
||
|
||
return cell;
|
||
}
|
||
public static ICell? getRowCell(IRow row, object cellIndex)
|
||
{
|
||
int actualCellIndex = 0;
|
||
if (cellIndex is int intIndex)
|
||
{
|
||
// 如果输入是数字，直接使用
|
||
actualCellIndex = intIndex;
|
||
}
|
||
else if (cellIndex is string strIndex)
|
||
{
|
||
actualCellIndex = ColToIndex(strIndex);
|
||
}
|
||
else
|
||
{
|
||
return null;
|
||
}
|
||
|
||
if (row == null) return null;
|
||
|
||
ICell cell = row.GetCell(actualCellIndex);
|
||
if (cell == null) return null;
|
||
|
||
return cell;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取单元格的字符串值，如果是公式则先计算
|
||
/// </summary>
|
||
/// <param name="cell">NPOI单元格对象</param>
|
||
/// <returns>单元格的值字符串，如果为空返回null</returns>
|
||
public static string? getFormula(ICell cell)
|
||
{
|
||
if (cell == null)
|
||
return null;
|
||
|
||
try
|
||
{
|
||
// 如果是公式单元格
|
||
if (cell.CellType == CellType.Formula)
|
||
{
|
||
// 获取公式计算器
|
||
if (cell.Sheet?.Workbook != null)
|
||
{
|
||
var evaluator = cell.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
|
||
var cellValue = evaluator.Evaluate(cell);
|
||
|
||
// 将计算结果转为字符串
|
||
if (cellValue == null) return null;
|
||
|
||
switch (cellValue.CellType)
|
||
{
|
||
case CellType.String:
|
||
return cellValue.StringValue?.Trim();
|
||
case CellType.Numeric:
|
||
return cellValue.NumberValue.ToString();
|
||
case CellType.Boolean:
|
||
return cellValue.BooleanValue.ToString();
|
||
case CellType.Error:
|
||
return "#ERROR";
|
||
case CellType.Blank:
|
||
return null;
|
||
default:
|
||
return cellValue.FormatAsString()?.Trim();
|
||
}
|
||
}
|
||
else
|
||
{
|
||
// 没有计算器，获取单元格的字符串表示
|
||
return cell.ToString()?.Trim();
|
||
}
|
||
}
|
||
else
|
||
{
|
||
// 非公式单元格，直接获取字符串表示
|
||
return cell.ToString()?.Trim();
|
||
}
|
||
}
|
||
catch (Exception)
|
||
{
|
||
// 计算出错时返回空
|
||
return null;
|
||
}
|
||
}
|
||
|
||
|
||
public static String? getRowCellStr(ISheet sheet, int rowIndex, object cellIndex)
|
||
{
|
||
int actualCellIndex = 0;
|
||
if (cellIndex is int intIndex)
|
||
{
|
||
// 如果输入是数字，直接使用
|
||
actualCellIndex = intIndex;
|
||
}
|
||
else if (cellIndex is string strIndex)
|
||
{
|
||
actualCellIndex = ColToIndex(strIndex);
|
||
}
|
||
else
|
||
{
|
||
return null;
|
||
}
|
||
|
||
var cellValue = getRowCell(sheet, rowIndex, actualCellIndex)?.ToString();
|
||
return string.IsNullOrWhiteSpace(cellValue) ? null : cellValue;
|
||
}
|
||
public static String? getRowCellStr(IRow row, object cellIndex,bool Formula=false)
|
||
{
|
||
int actualCellIndex = 0;
|
||
if (cellIndex is int intIndex)
|
||
{
|
||
// 如果输入是数字，直接使用
|
||
actualCellIndex = intIndex;
|
||
}
|
||
else if (cellIndex is string strIndex)
|
||
{
|
||
actualCellIndex = ColToIndex(strIndex);
|
||
}
|
||
else
|
||
{
|
||
return null;
|
||
}
|
||
|
||
if (Formula) {
|
||
return getFormula(getRowCell(row, actualCellIndex));
|
||
} else {
|
||
var cellValue = getRowCell(row, actualCellIndex)?.ToString();
|
||
return string.IsNullOrWhiteSpace(cellValue) ? null : cellValue;
|
||
}
|
||
|
||
|
||
}
|
||
public static float? getRowCellFloat(ISheet sheet, int rowIndex, object cellIndex)
|
||
{
|
||
int actualCellIndex = 0;
|
||
if (cellIndex is int intIndex)
|
||
{
|
||
actualCellIndex = intIndex;
|
||
}
|
||
else if (cellIndex is string strIndex)
|
||
{
|
||
actualCellIndex = ColToIndex(strIndex);
|
||
}
|
||
else
|
||
{
|
||
return null;
|
||
}
|
||
|
||
var cell = getRowCell(sheet, rowIndex, actualCellIndex);
|
||
if (cell == null) return null;
|
||
|
||
// 获取单元格的实际值（不是公式本身）
|
||
string cellValue;
|
||
if (cell.CellType == CellType.Formula)
|
||
{
|
||
// 如果是公式，获取公式计算后的值
|
||
cellValue = cell.NumericCellValue.ToString();
|
||
}
|
||
else
|
||
{
|
||
cellValue = cell.ToString();
|
||
}
|
||
|
||
// 解析为float
|
||
if (float.TryParse(cellValue, out float result))
|
||
{
|
||
return result;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
public static float? getRowCellFloat(IRow row, object cellIndex)
|
||
{
|
||
int actualCellIndex = 0;
|
||
if (cellIndex is int intIndex)
|
||
{
|
||
actualCellIndex = intIndex;
|
||
}
|
||
else if (cellIndex is string strIndex)
|
||
{
|
||
actualCellIndex = ColToIndex(strIndex);
|
||
}
|
||
else
|
||
{
|
||
return null;
|
||
}
|
||
|
||
var cell = getRowCell(row, actualCellIndex);
|
||
if (cell == null) return null;
|
||
|
||
// 获取单元格的实际值（不是公式本身）
|
||
string cellValue;
|
||
if (cell.CellType == CellType.Formula)
|
||
{
|
||
// 如果是公式，获取公式计算后的值
|
||
cellValue = cell.NumericCellValue.ToString();
|
||
}
|
||
else
|
||
{
|
||
cellValue = cell.ToString();
|
||
}
|
||
|
||
// 解析为float
|
||
if (float.TryParse(cellValue, out float result))
|
||
{
|
||
return result;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
public static double? getRowCellDouble(IRow row, object cellIndex)
|
||
{
|
||
int actualCellIndex = 0;
|
||
if (cellIndex is int intIndex)
|
||
{
|
||
actualCellIndex = intIndex;
|
||
}
|
||
else if (cellIndex is string strIndex)
|
||
{
|
||
actualCellIndex = ColToIndex(strIndex);
|
||
}
|
||
else
|
||
{
|
||
return null;
|
||
}
|
||
|
||
var cell = getRowCell(row, actualCellIndex);
|
||
if (cell == null) return null;
|
||
|
||
// 获取单元格的实际值（不是公式本身）
|
||
string cellValue;
|
||
if (cell.CellType == CellType.Formula)
|
||
{
|
||
// 如果是公式，获取公式计算后的值
|
||
cellValue = cell.NumericCellValue.ToString();
|
||
}
|
||
else
|
||
{
|
||
cellValue = cell.ToString();
|
||
}
|
||
|
||
if (double.TryParse(cellValue, out double result))
|
||
{
|
||
return result;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
// 简短版本
|
||
// 列名字转为列号
|
||
public static int ColToIndex(string col)
|
||
{
|
||
return col.ToUpper().Aggregate(0, (cur, ch) => cur * 26 + (ch - 'A'));
|
||
}
|
||
|
||
public static ICellStyle CreateStyle(
|
||
IWorkbook workbook,
|
||
bool Border = true,
|
||
short? fontSize = null,
|
||
bool isBold = false,
|
||
bool isItalic = false,
|
||
string fontName = "宋体",
|
||
HorizontalAlignment hAlign = HorizontalAlignment.Left, // 水平对齐
|
||
VerticalAlignment vAlign = VerticalAlignment.Center) // 垂直对齐) // 新增：字体名称，默认为宋体
|
||
{
|
||
ICellStyle style = workbook.CreateCellStyle();
|
||
|
||
if (Border)
|
||
{
|
||
// 设置边框
|
||
style.BorderTop = BorderStyle.Thin;
|
||
style.BorderBottom = BorderStyle.Thin;
|
||
style.BorderLeft = BorderStyle.Thin;
|
||
style.BorderRight = BorderStyle.Thin;
|
||
}
|
||
// 设置对齐方式
|
||
style.Alignment = hAlign;
|
||
style.VerticalAlignment = vAlign;
|
||
|
||
// 创建字体
|
||
IFont font = workbook.CreateFont();
|
||
|
||
// 设置字体名称
|
||
font.FontName = fontName;
|
||
|
||
// 设置字号
|
||
if (fontSize.HasValue)
|
||
font.FontHeightInPoints = fontSize.Value;
|
||
|
||
// 设置粗体斜体
|
||
font.IsBold = isBold;
|
||
font.IsItalic = isItalic;
|
||
|
||
|
||
style.SetFont(font);
|
||
return style;
|
||
}
|
||
|
||
}
|
||
}
|