YBEE.EQM/YBEE.EQM.Core/Utils/ExcelUtil.cs

using NPOI.HSSF.UserModel;
using NPOI.SS.Formula;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.IO;
using System.Linq;

namespace YBEE.EQM.Core;

/// <summary>
/// Excel操作工具
/// </summary>
public static class ExcelUtil
{
    /// <summary>
    /// 获取 Workbook 实例
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="fileStream"></param>
    /// <returns></returns>
    public static IWorkbook GetWorkbook(string filePath, FileStream fileStream)
    {
        string fileExt = Path.GetExtension(filePath).ToLower();
        if (fileExt == ".xls")
        {
            return new HSSFWorkbook(fileStream);
        }
        else
        {
            return new XSSFWorkbook(fileStream);
        }
    }
    /// <summary>
    /// 创建 Workbook
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public static IWorkbook GetWorkbook(string filePath)
    {
        string fileExt = Path.GetExtension(filePath).ToLower();
        if (fileExt == ".xls")
        {
            return new HSSFWorkbook();
        }
        else
        {
            return new XSSFWorkbook();
        }
    }
    /// <summary>
    /// 获取公式计算器
    /// </summary>
    /// <param name="workbook"></param>
    /// <returns></returns>
    public static BaseFormulaEvaluator GetEvaluator(IWorkbook workbook)
    {
        if (workbook is HSSFWorkbook)
        {
            return new HSSFFormulaEvaluator(workbook);
        }
        return new XSSFFormulaEvaluator(workbook);
    }
    /// <summary>
    /// 根据列索引获取字母列名
    /// </summary>
    /// <param name="index"></param>
    /// <returns></returns>
    public static string GetColumnNameByIndex(int index)
    {
        int num = index;

        var letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToArray();
        string str = "";

        while (num >= 0)
        {
            var n = letters[num % 26];
            str = $"{n}{str}";
            if (num == 0)
            {
                break;
            }
            num = (int)(Math.Floor(num * 1.0 / 26) - 1);
        }

        return str;
    }
}