处理代码

动态映射

动态读取 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字符串解析为JObjectJObject 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){// 异常时 删除有问题的ExcelFile.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": "应付金额"
}

模板样式

数据源