Excel数据动态获取与映射

处理代码

动态映射

动态读取 excel 中的数据，并通过 json 配置指定对应列的值映射到模板中的什么字段上

private void GetFreightFeeByExcel(string filePath)
{
    // 文件名需要以快递公司命名 便于映射查询
    string fileName = Path.GetFileNameWithoutExtension(filePath).ToUpper();
    string jsonString = _securityService.QueryByIdAsync("excelConvert" + fileName).Result?.KeyValue ?? throw new RunInterceptException($"缺失 {fileName} 配置json");
    string templatePath = _securityService.QueryByIdAsync("excelConvertTemplatePath").Result.KeyValue ?? throw new RunInterceptException($"缺失 excelConvertTemplatePath 配置");
    // 将JSON字符串解析为JObject
    JObject jsonObject = JObject.Parse(jsonString);
    // 做动态对象属性的存储
    var objectDic = new Dictionary<string, object>();
    // 遍历JObject的所有属性
    foreach (JProperty property in jsonObject.Properties())
    {
        // 获取属性名和属性值 存到字典中，迭代字典总比迭代JObject快吧
        objectDic.Add(property.Name, property.Value.ToString());
    }
    // 存储每行需要的数据
    List<object> dataList = new List<object>();

    // MiniExcel 当判断文件 SharedString 大小超过 5MB，预设会使用本地缓存，空间换时间
    // 若需关闭 var config = new OpenXmlConfiguration { EnableSharedStringCache = false };
    foreach (IDictionary<string, object> row in MiniExcel.Query(filePath, true, "账单明细"))
    {
        // 深拷贝一个字典，作为数据的存储
        var dataDic = new Dictionary<string, object>(objectDic);
        foreach (var item in objectDic)
        {
            string key = item.Value.ToString();
            if (key.IsNullOrEmpty())
                continue;
            string cellValue = row[key].ToString() ?? "";
            if (item.Key == "到件地区") // SF的到件地址可能是多个市的组合 取一个即可
                cellValue = cellValue.Split('/')[0];
            // 修改值
            dataDic[item.Key] = cellValue;
        }
        // 这一步的转换是必须的 因为 MiniExcel 的模板导出依赖对象反射
        dataList.Add(dataDic.DictionaryToDynamicObject());
    }
    string convertFilePath = Path.GetDirectoryName(filePath) + $"\\ConvertedData-{fileName}.xlsx";
    ExportExcelByTemplate(dataList, convertFilePath, templatePath, fileName);
}

字典转动态对象

public static dynamic DictionaryToDynamicObject(this IDictionary<string, object> dictionary)
{
    IDictionary<string, object> expandoDict = new ExpandoObject() as IDictionary<string, object>;
    foreach (var kvp in dictionary)
    {
        expandoDict.Add(kvp);
    }
    return expandoDict;
}

导出代码

private void ExportExcelByTemplate(List<object> dataList, string convertFilePath, string templatePath, string fileName)
{
    var value = new Dictionary<string, object>
    {
        ["data"] = dataList
        };
    try
    {
        MiniExcel.SaveAsByTemplate(convertFilePath, templatePath, value);
    }
    catch (Exception ex)
    {
        // 异常时 删除有问题的Excel
        File.Delete(convertFilePath);
        throw new RunInterceptException($"导出 {fileName} Excel数据异常：{ex.Message}", ex);
    }
}

JSON 映射格式

key：模板的字段

value：数据源的字段

{
  "ExpressNumber": "运单号码",
  "ShippingMethod": "产品类型",
  "ShippingDate": "寄件时间",
  "ShippingAgent": "",
  "CalculationType": "",
  "FeeType": "服务",
  "OtherFeeType": "",
  "Recipients": "收件人",
  "ShipAddress1": "国家或地区",
  "ShipAddress2": "始发地(省名)",
  "ShipAddress3": "寄件地区",
  "DeliveryAddress1": "国家或地区",
  "DeliveryAddress2": "目的地(省名)",
  "DeliveryAddress3": "到件地区",
  "ActualWeight": "实际重量",
  "CalculatedWeight": "计费重量",
  "Discounts": "折扣/促销",
  "Length": "长",
  "Width": "宽",
  "Height": "高",
  "ActualFreightFee": "应付金额"
}