使用DocumentFormat.OpenXml 从Excel 导入数据

 public List<DailyStock> ReadExcelFile(string fileName)
  {
    List<DailyStock> list = new List<DailyStock>();
    DataTable dt = ReadExcelFileToDataTable(fileName);
    if (dt != null && dt.Rows.Count > 0)
    {
      foreach (DataRow row in dt.Rows)
      {
        var model = UtilityHelper.ConvertToModel<DailyStock>(row, new DailyStock());
        list.Add(model);
      }
    }
    return list;
  }

  public DataTable ReadExcelFileToDataTable(string fileName)
  {
    System.Data.DataTable dt = UtilityHelper.GenerateDailyStockDataTable();
    try
    {
      //Lets open the existing excel file and read through its content . Open the excel using openxml sdk
      using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
      {
        //create the object for workbook part  
        WorkbookPart workbookPart = doc.WorkbookPart;
        Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();

        //using for each loop to get the sheet from the sheetcollection  
        foreach (Sheet thesheet in thesheetcollection)
        {
          //statement to get the worksheet object by using the sheet id  
          Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
          ExcelDailyStockColumnMapping columnMapping = new ExcelDailyStockColumnMapping();
          SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild<SheetData>();
          foreach (Row thecurrentrow in thesheetdata)
          {
            if (thecurrentrow.RowIndex == 1) { continue; }//忽略第一行
            DataRow row = dt.NewRow();
            foreach (Cell thecurrentcell in thecurrentrow)
            {
              string value = thecurrentcell.CellValue.InnerText;
              if (thecurrentcell.DataType != null)
              {
                int id;
                if (Int32.TryParse(thecurrentcell.InnerText, out id))
                {
                  SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
                  value = item.InnerText;
                }
              }
              SetVaule(thecurrentcell.CellReference.ToString(), value, row, columnMapping);
            }
            dt.Rows.Add(row);
          }
        }
      }
    }
    catch (Exception ex)
    {
      logger.LogError(ex, "读取Excel失败");
      throw ex;
    }
    return dt;
  }
  private void SetVaule(string cellReference, string value, DataRow row, ExcelDailyStockColumnMapping columnMapping)
  {
    string columnName = UtilityHelper.GetABC(cellReference);
    if (columnMapping.ColumnNames.ContainsKey(columnName))
    {
      var keyValue = columnMapping.ColumnNames[columnName];
      if (keyValue.Value == CellValues.Number)
      {
        row[keyValue.Key] = UtilityHelper.ToDouble(value.Trim());
      }
      else
      {
        row[keyValue.Key] = value.Trim();
      }
    }
  }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Ardalis.Result;
using DocumentFormat.OpenXml.Drawing.Charts;
using Humanizer;
using Stock.Core.Aggregate.DailyStockAggregate;
using System.Data;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Stock.Infrastructure;
public class UtilityHelper
{
  public const string THOUSAND = "万";
  public const string MILLION = "亿";
  public static double ToDouble(string value)
  {
    CurrencyUnits unit = GetUnit(value);
    string numberStr = GetNumber(value, unit);

    double result = 0;
    if (double.TryParse(numberStr, out result))
    {
      result = ConvertToNumbersByUnit(result, unit);
    }
    return result;
  }
  private static double ConvertToNumbersByUnit(double value, CurrencyUnits unit)
  {
    double result = value;
    switch (unit)
    {
      case CurrencyUnits.Thousand:
        result = value * 10000;
        break;
      case CurrencyUnits.Million:
        result = value * 10000 * 10000;
        break;
    }
    return result;
  }
  private static CurrencyUnits GetUnit(string value)
  {
    CurrencyUnits unit = CurrencyUnits.None;
    if (value.Contains(THOUSAND))
    {
      unit = CurrencyUnits.Thousand;
    }
    else if (value.Contains(MILLION))
    {
      unit = CurrencyUnits.Million;
    }
    return unit;
  }
  private static string GetNumber(string value, CurrencyUnits unit)
  {
    string result = value.Trim();
    switch (unit)
    {
      case CurrencyUnits.Thousand:
        result = result.Replace(THOUSAND, string.Empty);
        break;
      case CurrencyUnits.Million:
        result = result.Replace(MILLION, string.Empty);
        break;
    }
    return result;
  }
  private static System.Data.DataTable dailyStockDataTable = null;
  public static System.Data.DataTable GenerateDailyStockDataTable()
  {
    if (dailyStockDataTable == null)
    {
      DailyStock dailyStock = new DailyStock();
      dailyStockDataTable = ToDataTable(dailyStock);
      return dailyStockDataTable;
    }
    else
    {
      dailyStockDataTable.Clear();
      return dailyStockDataTable;
    }
  }
  /// <summary>
  /// 将实体转换成具有相同结构的DataTable
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="model">要转换的实体</param>
  /// <returns></returns>
  public static System.Data.DataTable ToDataTable<T>(T model)
  {
    //检查实体集合不能为空
    if (model == null)
    {
      throw new Exception("需转换的集合为空");
    }
    //取出第一个实体的所有Propertie
    Type entityType = model.GetType();
    PropertyInfo[] entityProperties = entityType.GetProperties();

    //生成DataTable的structure
    //生产代码中，应将生成的DataTable结构Cache起来，此处略
    System.Data.DataTable dt = new();
    for (int i = 0; i < entityProperties.Length; i++)
    {
      dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
    }
    return dt;
  }
  /// <summary>
  /// DataRow转化为实体
  /// </summary>
  /// <typeparam name="T">实体</typeparam>
  /// <param name="pDataRow">DataRow</param>
  /// <param name="model"></param>
  /// <returns></returns>
  public static T ConvertToModel<T>(DataRow pDataRow, T model)
  {
    object proValue = null;
    PropertyInfo propertyInfo = null;
    try
    {
      if (pDataRow != null)
      {
        foreach (DataColumn dc in pDataRow.Table.Columns)
        {
          //忽略绑定时的大小写
          propertyInfo = model.GetType().GetProperty(dc.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
          proValue = pDataRow[dc];
          //当值不为空时
          if (proValue != DBNull.Value)
          {
            try
            {   //给属性赋值
              propertyInfo.SetValue(model, Convert.ChangeType(proValue, dc.DataType), null);
            }
            catch //如果有错误,继续下一个属性的赋值
            {
              continue;
            }
          }
        }
      }
    }
    catch
    {
      model = default(T);
    }
    return model;
  }

  /// <summary>
  /// DataTable转化为泛型
  /// </summary>
  /// <typeparam name="T">实体</typeparam>
  /// <param name="dt">DataTable</param>
  /// <param name="model"></param>
  /// <returns></returns>
  public static List<T> ConvertToList<T>(System.Data.DataTable dt, T model)
  {
    List<T> _list = new List<T>();
    foreach (DataRow dr in dt.Rows)
    {
      _list.Add(ConvertToModel<T>(dr, model));
    }
    return _list;
  }
  /// <summary>
  /// 得到字符串中的字母
  /// </summary>
  /// <param name="value"></param>
  /// <returns></returns>
  public static string GetABC(string value)
  {
    return Regex.Replace(value, "[0-9]", "", RegexOptions.IgnoreCase);
  }

}