ＡＢＰ框架的下载模板和导入

  1 /// <summary>
  2 /// 下载物料信息导入模板
  3 /// </summary>
  4 /// <returns></returns>
  5 public async Task<FileResult> ExportMaterialInformationTemplate()
  6 {
  7     await CheckGetPolicyAsync();
  8  
  9     var workbook = new HSSFWorkbook();
 10     
 11     await GetData(workbook);
 12  
 13     string filaName = "物料信息导入模板-" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls";
 14  
 15     var dic = Path.Combine(AppContext.BaseDirectory, "wwwroot\\template\\" + "OutboundPlan_" + DateTime.Now.ToString("yyyyMMdd"));
 16  
 17     if (!Directory.Exists(dic))
 18         Directory.CreateDirectory(dic);
 19  
 20     string fullPath = Path.Combine(dic, filaName);
 21  
 22     using (Stream file = File.Create(fullPath))
 23     {
 24         workbook.Write(file);
 25     }
 26  
 27     var exfile = File.OpenRead(fullPath);
 28  
 29     var actionresult = new FileStreamResult(exfile, new Microsoft.Net.Http.Headers.MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
 30     actionresult.FileDownloadName = filaName;
 31  
 32     return actionresult;
 33 }
 34  
 35 /// <summary>
 36 /// Execl配置下拉框
 37 /// </summary>
 38 /// <param name="sheet"></param>
 39 /// <param name="firstcol"></param>
 40 /// <param name="lastcol"></param>
 41 /// <param name="vals"></param>
 42 public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals)
 43 {
 44     //设置生成下拉框的行和列
 45     var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);
 46  
 47     //设置 下拉框内容
 48     DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);
 49  
 50     //绑定下拉框和作用区域，并设置错误提示信息
 51     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
 52     dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
 53     dataValidate.ShowPromptBox = true;
 54  
 55     sheet.AddValidationData(dataValidate);
 56 }
 57  
 58 public static HSSFDataValidation SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals, IWorkbook workbook, int hiddenSheetIndex)
 59 {
 60  
 61     string hiddenSheetName = "hidden" + DateTime.Now.ToString("yyyyMMddHHmmss") + hiddenSheetIndex.ToString();
 62  
 63     ISheet hidden = workbook.CreateSheet(hiddenSheetName);
 64     ICell cell = null;
 65     for (int i = 0, length = vals.Length; i < length; i++)
 66     {
 67         string name = vals[i];
 68         IRow row = hidden.CreateRow(i);
 69         cell = row.CreateCell(0);
 70         cell.SetCellValue(name);
 71     }
 72  
 73     IName nameCell = workbook.CreateName();
 74     nameCell.NameName = hiddenSheetName;
 75     nameCell.RefersToFormula = hiddenSheetName + "!$A$1:$A$" + vals.Length;
 76  
 77  
 78     DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(hiddenSheetName);
 79  
 80  
 81     //设置生成下拉框的行和列
 82     var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);
 83  
 84     //设置 下拉框内容
 85     // DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);
 86  
 87     //绑定下拉框和作用区域，并设置错误提示信息
 88     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
 89     dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
 90     dataValidate.ShowPromptBox = true;
 91  
 92     workbook.SetSheetHidden(hiddenSheetIndex, SheetState.Hidden);
 93     if (null != dataValidate)
 94     {
 95         sheet.AddValidationData(dataValidate);
 96     }
 97     return dataValidate;
 98  
 99  
100 }
101  
102 /// <summary>
103 /// 获取数据
104 /// </summary>
105 /// <param name="workbook"></param>
106 /// <returns></returns>
107 public async Task<bool> GetData(IWorkbook workbook)
108 {
109     ISheet sheet = workbook.CreateSheet("sheet1");
110  
111     //创建第一行数据并将表头写进去
112     IRow rowHead = sheet.CreateRow(0);
113     rowHead.CreateCell(0).SetCellValue("物料编码");
114     rowHead.CreateCell(1).SetCellValue("物料名称");
115     rowHead.CreateCell(2).SetCellValue("物料分类");
116     rowHead.CreateCell(3).SetCellValue("物料型号");
117     rowHead.CreateCell(4).SetCellValue("计量单位");
118     rowHead.CreateCell(5).SetCellValue("备注");
119     rowHead.CreateCell(6).SetCellValue("是否检验");
120     rowHead.CreateCell(7).SetCellValue("防错方式");
121     rowHead.CreateCell(8).SetCellValue("防错规则");
122     //创建样式
123     ICellStyle style = workbook.CreateCellStyle();
124     style.Alignment = HorizontalAlignment.Center;//设置单元格居中
125     style.VerticalAlignment = VerticalAlignment.Center;
126     IFont font = workbook.CreateFont(); //新建一个字体样式对象            
127     font.FontHeightInPoints = 10;//设置字体大小
128     font.IsBold = true; //设置字体加粗
129     style.SetFont(font);
130  
131     for (int i = 0; i < 9; i++)
132     {
133         rowHead.GetCell(i).CellStyle = style;
134     }
135  
136     sheet.SetColumnWidth(0, 4000);
137     sheet.SetColumnWidth(1, 4000);
138     sheet.SetColumnWidth(2, 3000);
139     sheet.SetColumnWidth(3, 3000);
140     sheet.SetColumnWidth(4, 3000);
141     sheet.SetColumnWidth(5, 3000);
142     sheet.SetColumnWidth(6, 3000);
143     sheet.SetColumnWidth(7, 3000);
144     sheet.SetColumnWidth(8, 3000);
145     sheet.SetColumnWidth(9, 4000);
146     sheet.SetColumnWidth(9, 4000);
147  
148     var list = _dictionaryAppService.GetOptionList("MaterialType").Select(p => new { p.Option_Name }).ToList();
149     var arr1 = list.Select(p => p.Option_Name).ToArray();
150     string[] arr2 = new string[1] { "" };
151     var arr = arr1.Union(arr2).ToArray();
152     SetCellDropdownList(sheet, 2, 2, arr);
153  
154     //var modtype = await _moldtypeRepository.GetListAsync();
155     //SetCellDropdownListHidd(sheet, 3, 3, modtype.Select(p => p.MoldTypeName).ToArray(), workbook, 1);
156  
157     //list = _dictionaryAppService.GetOptionList("PackingSizes").Select(p => new { p.Option_Name }).ToList();
158     //SetCellDropdownList(sheet, 4, 4, list.Select(p => p.Option_Name).ToArray());
159  
160     string[] strTF = new string[2] { "是", "否" };
161     SetCellDropdownList(sheet, 6, 6, strTF);
162  
163     var list2 = _dictionaryAppService.GetOptionList("ErrorPreventionRules").Select(p => new { p.Option_Name }).ToList();
164     var arr3 = list2.Select(p => p.Option_Name).ToArray();
165     string[] arr4 = new string[1] { "" };
166     var arr5 = arr3.Union(arr4).ToArray();
167     SetCellDropdownList(sheet, 7, 7, arr5);
168  
169     return await Task.FromResult(true);
170 }

上面这个是下载模板　　可能会注册一些服务，这个得根据框架不同进行调整。

下面是导入文件　这是处理导入文件　

  1 /// <summary>
  2 /// 导入
  3 /// </summary>
  4 /// <param name="fileId">文件id</param>
  5 /// <param name="realName"></param>
  6 /// <param name="filte">条件（可空）</param>
  7 /// <returns></returns>
  8 /// <exception cref="UserFriendlyException"></exception>
  9 public async Task<ResultDto> Import(string fileId,string realName, string filte = "")
 10 {
 11     try
 12     {
 13         IWorkbook workBookSel = null;
 14         var refFile = await FilesAppService.GetFileNameList(fileId);
 15         if (refFile != null && refFile.Count > 0)
 16         {
 17             var fileEntity = refFile[0];
 18             string filePath = FilesAppService.GetFileSavePath(fileEntity.Path);
 19             if (System.IO.File.Exists(filePath))
 20             {
 21                 if (fileEntity.Extensions != ".xls" && fileEntity.Extensions != ".xlsx")
 22                     throw new UserFriendlyException("请上传正确的Excel格式文件！");
 23  
 24                 //var fileEntity = await FilesAppService.UploadFile(file);
 25                 #region 调用解密接口
 26                 string refPath = "";
 27                 try
 28                 {
 29                     //filePath = FilesAppService.GetFileSavePath(fileEntity.Path);
 30                     byte[] filedata = File.ReadAllBytes(filePath);//根据路径读取文件数据
 31                     PartyInterface partyInterface = new PartyInterface();
 32                     refPath = partyInterface.FileDecrypt(filedata, fileEntity.Extensions);
 33                 }
 34                 catch (Exception ex)
 35                 {
 36  
 37                 }
 38                 if (refPath != "")
 39                     fileEntity.Path = refPath;
 40                 #endregion
 41                 //using (FileStream fs = File.OpenRead(filePath))
 42                 //{
 43                 //    workBookSel = new HSSFWorkbook(fs);
 44                 //}
 45  
 46                 var workBook = GetWorkBook(fileEntity);
 47  
 48                 //删除解密后文件
 49                 if (refPath != "")
 50                     File.Delete(refPath);
 51  
 52                 return await ImportProcess(workBook, filte);
 53             }
 54             else
 55             {
 56                 return new ResultDto(false, "文件读取错误");
 57             }
 58  
 59         }
 60         else
 61         {
 62             return new ResultDto(false, "文件读取错误");
 63         }
 64  
 65     }
 66     catch (Exception ex)
 67     {
 68         return new ResultDto(false, "文件读取错误");
 69     }
 70 }
 71  
 72 /// <summary>
 73 /// 获取文档
 74 /// </summary>
 75 /// <param name="file"></param>
 76 /// <returns></returns>
 77 private IWorkbook GetWorkBook(UploadFileDto file)
 78 {
 79     var filePath = FilesAppService.GetFileSavePath(file.Path);
 80  
 81     using (var fileStream = File.OpenRead(filePath))
 82     {
 83         IWorkbook workBook = null;
 84  
 85         if (filePath.IndexOf(".xlsx") > 0) // 2007
 86             workBook = new XSSFWorkbook(fileStream);
 87         else if (filePath.IndexOf(".xls") > 0) // 2003
 88             workBook = new HSSFWorkbook(fileStream);
 89  
 90         return workBook;
 91     }
 92 }
 93  
 94  
 95  
 96  
 97 /// <summary>
 98 /// 处理导入文件
 99 /// </summary>
100 /// <param name="workBoox"></param>
101 /// <param name="filte"></param>
102 /// <returns></returns>
103 /// <exception cref="UserFriendlyException"></exception>
104 protected override async Task<ResultDto> ImportProcess(IWorkbook workBoox, string filte = "")
105 {
106  
107     ISheet sheet = workBoox.GetSheetAt(0);
108     if (sheet == null)
109     {
110         throw new UserFriendlyException("没有找到sheet");
111     }
112     IRow firstRow = sheet.GetRow(0);
113     int rowCount = sheet.LastRowNum;
114     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
115  
116     string errorMessage = string.Empty;//报错信息
117  
118     if (cellCount != 9)
119     {
120         throw new UserFriendlyException("请使用正确的导入模板!");
121     }
122  
123     List<CreateOrUpdateMaterialInformationDto> inputDtoList = new List<CreateOrUpdateMaterialInformationDto>();
124     for (int i = 1; i <= rowCount; i++)
125  
126     {
127  
128         IRow row = sheet.GetRow(i);
129  
130         if (row == null)
131         {
132             throw new UserFriendlyException("没有找到数据");
133         }
134  
135         #region 物料编码
136  
137         string materialCode = string.Empty;
138         if (row.GetCell(0) != null)
139         {
140             materialCode = row.GetCell(0).ToString();
141         }
142  
143  
144         #endregion
145  
146  
147         #region 物料名称
148  
149         string materialName = string.Empty;
150         if (row.GetCell(1) != null)
151         {
152             materialName = row.GetCell(1).ToString();
153         }
154  
155  
156         #endregion
157  
158  
159         #region 物料分类
160         string materialType = string.Empty;
161         if (row.GetCell(2) != null)
162         {
163             materialType = row.GetCell(2).ToString();
164         }
165         #endregion
166  
167  
168         #region 物料型号
169         string materialModel = string.Empty;
170         if (row.GetCell(3) != null)
171         {
172             materialModel = row.GetCell(3).ToString();
173         }
174         #endregion
175  
176  
177         #region 计量单位
178         string unitMeasurement = string.Empty;
179         if (row.GetCell(4) != null)
180         {
181             unitMeasurement = row.GetCell(4).ToString();
182         }
183  
184         #endregion
185  
186  
187         #region 备注
188  
189         string remark = string.Empty;
190         if (row.GetCell(5) != null)
191         {
192             remark = row.GetCell(5).ToString();
193         }
194         #endregion
195  
196         #region 是否检验
197         string isCheck = string.Empty;
198         if (row.GetCell(6) != null)
199         {
200             isCheck = row.GetCell(6).ToString();
201         }
202         #endregion
203  
204         #region 防错方式
205         string errorPreventionType = string.Empty;
206         if (row.GetCell(7) != null)
207         {
208             errorPreventionType = row.GetCell(7).ToString();
209         }
210         #endregion
211  
212         #region 防错规则
213         string errorPreventionRule = string.Empty;
214         if (row.GetCell(8) != null)
215         {
216             errorPreventionRule = row.GetCell(8).ToString();
217         }
218         #endregion
219  
220  
221         CreateOrUpdateMaterialInformationDto inputDto = new CreateOrUpdateMaterialInformationDto
222         {
223             MaterialCode = materialCode,
224             MaterialName = materialName,
225             MaterialType = _dictionaryAppService.GetDictionaryDetailValue("MaterialType", materialType),
226             MaterialModel = materialModel,
227             UnitMeasurement = unitMeasurement,
228             Remark = remark,
229             IsCheck=isCheck=="是"?true:false,
230             ErrorPreventionType= _dictionaryAppService.GetDictionaryDetailValue("ErrorPreventionRules", errorPreventionType),
231             ErrorPreventionRule = errorPreventionRule,
232             EnableState = true,
233         };
234  
235         inputDtoList.Add(inputDto);
236  
237     }
238  
239  
240     if (!string.IsNullOrEmpty(errorMessage))
241     {
242         throw new UserFriendlyException(errorMessage);
243     }
244  
245     foreach (var input in inputDtoList)
246     {
247         var isHave=await Repository.FirstOrDefaultAsync(x=>x.MaterialCode == input.MaterialCode);
248         if(isHave!=null)
249         {
250             await UpdateAsync(isHave.Id, input);
251         }
252         else
253         {
254             await CreateAsync(input);
255             var oldArticleInfor=await _mes_MomDb.Article.FirstOrDefaultAsync(x=>x.ProductNo==input.MaterialCode);
256             oldArticleInfor.IsSync= true;
257             _mes_MomDb.Article.Update(oldArticleInfor);
258             await _mes_MomDb.SaveChangesAsync();
259         }
260     }
261  
262     //return new ResultDto { IsSuccess = true, Info = "成功导入" + inputDtoList.Count + "条" }
263     return await Task.FromResult(new ResultDto(true));
264 }

导入文件

  1 /// <summary>
  2 /// 文件上传
  3 /// </summary>
  4 /// <param name="file"></param>
  5 /// <param name="type"></param>
  6 /// <returns></returns>
  7 public async Task<UploadFileDto> UploadFile(IFormFile file, FileType type = FileType.全部)
  8 {
  9     string extensions = null;
 10     CheckFile(file, out extensions, type);
 11     //subDirectory = subDirectory ?? string.Empty;
 12     //var target = Path.Combine(_webHostEnvironment.ContentRootPath, subDirectory);
 13  
 14     //取文件名
 15     var fullName = GetFullFileName(extensions, true);
 16  
 17     var savePath = GetFileSavePath(fullName);
 18  
 19     var dic = Path.GetDirectoryName(savePath);
 20  
 21     if (!Directory.Exists(dic))
 22         Directory.CreateDirectory(dic);
 23  
 24     //写入文件
 25     using (FileStream stream = File.Create(savePath))
 26     {
 27         await file.CopyToAsync(stream);
 28     }
 29  
 30     var dto = new CreateOrUpdateUploadFileDto()
 31     {
 32         Name = file.FileName,
 33         ContentType = file.ContentType,
 34         Extensions = extensions,
 35         Size = file.Length,
 36         Path = fullName
 37     };
 38  
 39     return await base.CreateAsync(dto);
 40 }
 41  
 42  
 43  
 44  
 45  
 46  
 47  
 48 /// <summary>
 49 /// 获取文档
 50 /// </summary>
 51 /// <param name="file"></param>
 52 /// <returns></returns>
 53 public IWorkbook GetWorkBook(UploadFileDto file)
 54 {
 55     var filePath = FilesAppService.GetFileSavePath(file.Path);
 56  
 57     using (var fileStream = File.OpenRead(filePath))
 58     {
 59         return GetWorkBook(fileStream, filePath);
 60     }
 61 }
 62  
 63 /// <summary>
 64 /// 获取文档
 65 /// </summary>
 66 /// <param name="stream"></param>
 67 /// <param name="filePath"></param>
 68 /// <returns></returns>
 69 public IWorkbook GetWorkBook(Stream stream, string filePath)
 70 {
 71     IWorkbook workBook = null;
 72  
 73     stream.Position = 0;
 74  
 75     if (filePath.IndexOf(".xlsx") > 0) // 2007
 76         workBook = new XSSFWorkbook(stream);
 77     else if (filePath.IndexOf(".xls") > 0) // 2003
 78         workBook = new HSSFWorkbook(stream);
 79  
 80     return workBook;
 81 }
 82  
 83 /// <summary>
 84 /// 获取文档
 85 /// </summary>
 86 /// <param name="data"></param>
 87 /// <param name="filePath"></param>
 88 /// <returns></returns>
 89 public IWorkbook GetWorkBook(byte[] data, string filePath)
 90 {
 91     using (Stream stream = new MemoryStream())
 92     {
 93         stream.Write(data, 0, data.Length);
 94         stream.Flush();
 95  
 96         return GetWorkBook(stream, filePath);
 97     }
 98 }
 99  
100 /// <summary>
101 /// 解密文件
102 /// </summary>
103 /// <returns></returns>
104 public async Task<byte[]> DecryptFile(UploadFileDto uploadFileDto)
105 {
106     try
107     {
108         //调用解密接口
109         var filePath = FilesAppService.GetFileSavePath(uploadFileDto.Path);
110  
111         await Task.Delay(2000);
112  
113         //根据路径读取文件数据
114         byte[] filedata = File.ReadAllBytes(filePath);
115  
116         PartyInterface partyInterface = new PartyInterface();
117  
118         var data = partyInterface.GetFileDecryptData(filedata, uploadFileDto.Extensions);
119  
120         var _appSettingsSection = FilesAppService.GetConfiguration();
121         //保存解密后的文件
122         if (_appSettingsSection != null && _appSettingsSection.GetSection("GW:SaveDecryptFile").Value.ToBoolean())
123         {
124             var decryptfilePath = uploadFileDto.Path;
125             var dir = Path.GetDirectoryName(decryptfilePath);
126             var fileName = Path.GetFileNameWithoutExtension(decryptfilePath);
127             var fileExtension = Path.GetExtension(decryptfilePath);
128  
129             var newPath = Path.Combine(dir, fileName + "_Decrypt" + fileExtension);
130  
131             File.WriteAllBytes(FilesAppService.GetFileSavePath(newPath), data);
132  
133             Logger.LogInformation("文件解密完成，{0}->{1}。", uploadFileDto.Path, newPath);
134         }
135         else
136             Logger.LogInformation("文件解密完成，{0}->{1}。", uploadFileDto.Path, "未配置本地保存");
137  
138  
139         return await Task.FromResult(data);
140     }
141     catch (Exception ex)
142     {
143         Logger.LogError(ex, "解密文件过程错误，{0}。", uploadFileDto.Path);
144     }
145  
146     return await Task.FromResult<byte[]>(null);
147 }
148  
149 /// <summary>
150 /// 解析上传文件
151 /// </summary>
152 /// <param name="file"></param>
153 /// <param name="filte"></param>
154 /// <returns></returns>
155 public async Task<ResultDto<List<TCreateInput>>> ImportAnalysis([NotNull] IFormFile file, string filte = "")
156 {
157     //await CheckCreatePolicyAsync();
158  
159     if (FilesAppService.IsExcel(file))
160         throw new UserFriendlyException("请上传正确的Excel格式文件！");
161  
162     var fileEntity = await FilesAppService.UploadFile(file);
163  
164     IWorkbook workBook;
165  
166     var _appSettingsSection = FilesAppService.GetConfiguration();
167     //是否解密文件
168     if (_appSettingsSection != null && _appSettingsSection.GetSection("GW:DecryptUpLoadFile").Value.ToBoolean())
169     {
170         var data = await DecryptFile(fileEntity);
171  
172         workBook = GetWorkBook(data, fileEntity.Path);
173     }
174     else
175         workBook = GetWorkBook(fileEntity);
176  
177     return await ImportAnalysisProcess(workBook, filte);
178 }
179  
180 /// <summary>
181 /// 导入
182 /// </summary>
183 /// <param name="file"></param>
184 /// <param name="filte">条件（可空）</param>
185 /// <returns></returns>
186 /// <exception cref="UserFriendlyException"></exception>
187 public async Task<ResultDto> Import([NotNull] IFormFile file, string filte = "")
188 {
189     //await CheckCreatePolicyAsync();
190  
191     if (FilesAppService.IsExcel(file))
192         throw new UserFriendlyException("请上传正确的Excel格式文件！");
193  
194     var fileEntity = await FilesAppService.UploadFile(file);
195  
196     IWorkbook workBook;
197  
198     var _appSettingsSection = FilesAppService.GetConfiguration();
199     //是否解密文件
200     if (_appSettingsSection != null && _appSettingsSection.GetSection("GW:DecryptUpLoadFile").Value.ToBoolean())
201     {
202         var data = await DecryptFile(fileEntity);
203  
204         workBook = GetWorkBook(data, fileEntity.Path);
205     }
206     else
207         workBook = GetWorkBook(fileEntity);
208  
209     return await ImportProcess<TCreateInput>(workBook, filte);
210 }
211  
212 /// <summary>
213 /// 处理导入文件
214 /// </summary>
215 /// <typeparam name="ImportRowType">导入行类型</typeparam>
216 /// <param name="workBoox"></param>
217 /// <param name="filte"></param>
218 /// <returns></returns>
219 /// <exception cref="UserFriendlyException"></exception>
220 protected virtual async Task<ResultDto> ImportProcess(IWorkbook workBoox, string filte = "")
221 {
222     return await ImportProcess<TCreateInput>(workBoox, filte);
223 }
224  
225 /// <summary>
226 /// 处理导入文件
227 /// </summary>
228 /// <typeparam name="ImportRowType">导入行类型</typeparam>
229 /// <param name="workBoox"></param>
230 /// <param name="filte"></param>
231 /// <returns></returns>
232 /// <exception cref="UserFriendlyException"></exception>
233 protected virtual async Task<ResultDto> ImportProcess<ImportRowType>(IWorkbook workBoox, string filte = "")
234 {
235     var config = await GetImportDefaultConfig<ImportRowType>();
236  
237     ISheet sheet = workBoox.GetSheetAt(0);
238     if (sheet == null)
239     {
240         throw new UserFriendlyException("没有找到sheet");
241     }
242  
243     IRow firstRow = sheet.GetRow(0);
244     int rowCount = sheet.LastRowNum;
245     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
246  
247     string errorMessage = string.Empty;//报错信息
248  
249     if (cellCount != config.Columns.Count)
250     {
251         throw new UserFriendlyException("请使用正确的导入模板!");
252     }
253  
254     List<TCreateInput> inputDtoList = new List<TCreateInput>();
255  
256     var properties = typeof(TCreateInput).GetProperties();
257  
258     using (var uow = base.UnitOfWorkManager.Begin())
259     {
260         try
261         {
262             for (int i = 1; i <= rowCount; i++)
263             {
264                 try
265                 {
266                     IRow row = sheet.GetRow(i);
267                     if (row == null)
268                         throw new UserFriendlyException("没有找到数据");
269  
270                     var inputDto = new TCreateInput();
271  
272                     for (var c = 0; c < config.Columns.Count; c++)
273                     {
274  
275                         var column = config.Columns[c];
276  
277                         var propertie = properties.Where(p => p.Name == column.Field && p.SetMethod != null).FirstOrDefault();
278  
279                         if (propertie == null)
280                             continue;
281  
282                         var propertyValue = await GetImportPropertyValue(column.Field, row.GetCell(c), propertie.PropertyType);
283  
284                         propertie.SetValue(inputDto, propertyValue);
285  
286                     }
287  
288                     inputDtoList.Add(inputDto);
289                 }
290                 catch (Exception e)
291                 {
292                     errorMessage += $"第{i}行导入格式错误[{e.Message}]；";
293                 }
294             }
295  
296             if (!string.IsNullOrEmpty(errorMessage))
297             {
298                 throw new UserFriendlyException(errorMessage);
299             }
300  
301             for (var i = 0; i < inputDtoList.Count; i++)
302             {
303                 try
304                 {
305                     await CreateAsync(inputDtoList[i]);
306                 }
307                 catch (Exception e)
308                 {
309                     errorMessage += $"第{i}行保存错误[{e.Message}]；";
310                 }
311             }
312  
313             if (!string.IsNullOrEmpty(errorMessage))
314             {
315                 throw new UserFriendlyException(errorMessage);
316             }
317  
318             await uow.CompleteAsync();
319         }
320         catch (Exception e)
321         {
322             await uow.RollbackAsync();
323  
324             throw;
325         }
326     }
327  
328     return await Task.FromResult(new ResultDto(true));
329 }
330  
331 /// <summary>
332 /// 获取导入属性值
333 /// 可以在子类重写该方法实现数据转换
334 /// </summary>
335 /// <param name="propertyName"></param>
336 /// <param name="cell"></param>
337 /// <param name="propertyType"></param>
338 /// <returns></returns>
339 protected virtual async Task<object> GetImportPropertyValue(string propertyName, ICell cell, Type propertyType)
340 {
341     var pt = propertyType;
342  
343     // 转换时间
344     var propertyValue = (cell != null && (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell)))
345         ? cell.DateCellValue.ToString() : cell?.ToString();
346     
347  
348     if (IsNullableType(propertyType))
349     {
350         if (string.IsNullOrEmpty(propertyValue))
351             return null;
352  
353         pt = System.Nullable.GetUnderlyingType(propertyType);
354     }
355     if (propertyType.IsEnum && propertyType.IsEnumDefined(propertyValue))
356     {
357         return Enum.Parse(propertyType, propertyValue);
358     }
359     var type = Type.GetTypeCode(pt);
360     switch (type)
361     {
362         case TypeCode.Boolean:
363             return await Task.FromResult(Convert.ToBoolean(propertyValue));
364         case TypeCode.Char:
365             return await Task.FromResult(Convert.ToChar(propertyValue));
366         case TypeCode.SByte:
367             return await Task.FromResult(Convert.ToSByte(propertyValue));
368         case TypeCode.Byte:
369             return await Task.FromResult(Convert.ToByte(propertyValue));
370         case TypeCode.Int16:
371             return await Task.FromResult(Convert.ToInt16(propertyValue));
372         case TypeCode.UInt16:
373             return await Task.FromResult(Convert.ToUInt16(propertyValue));
374         case TypeCode.Int32:
375             return await Task.FromResult(Convert.ToInt32(propertyValue));
376         case TypeCode.UInt32:
377             return await Task.FromResult(Convert.ToUInt32(propertyValue));
378         case TypeCode.Int64:
379             return await Task.FromResult(Convert.ToInt64(propertyValue));
380         case TypeCode.UInt64:
381             return await Task.FromResult(Convert.ToUInt64(propertyValue));
382         case TypeCode.Single:
383             return await Task.FromResult(Convert.ToSingle(propertyValue));
384         case TypeCode.Double:
385             return await Task.FromResult(Convert.ToDouble(propertyValue));
386         case TypeCode.Decimal:
387             return await Task.FromResult(Convert.ToDecimal(propertyValue));
388         case TypeCode.DateTime:
389             return await Task.FromResult(Convert.ToDateTime(propertyValue));
390         case TypeCode.String:
391             return await Task.FromResult(Convert.ToString(propertyValue));
392        
393         default:
394             throw new UserFriendlyException($"未知的导入类型{Type.GetTypeCode(propertyType)}！");
395     }
396 }
397  
398 public bool IsNullableType(Type theType)
399 {
400     return (theType.IsGenericType && theType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
401 }
402  
403 /// <summary>
404 /// 处理导入解析文件
405 /// </summary>
406 /// <param name="workBoox"></param>
407 /// <param name="filte"></param>
408 /// <returns></returns>
409 protected virtual async Task<ResultDto<List<TCreateInput>>> ImportAnalysisProcess(IWorkbook workBoox, string filte = "")
410 {
411     return await Task.FromResult(new ResultDto<List<TCreateInput>>(true));
412 }

发表于 2024-05-23 09:37 白衣衬衫两袖清风阅读(61) 评论(0) 编辑收藏举报

公告

搜索

常用链接

随笔档案

阅读排行榜