.NET CORE Api 上传excel解析并生成错误excel下载

写在前面的话：

        【对外承接app API开发、网站建设、系统开发，有偿提供帮助，联系方式于文章最下方 】

因业务调整，不再需要生成错误无excel下载，所以先保存代码，回头再重新编辑

#region Excel校验部分if (files == null) throw Oops.Oh("文件不存在");string path = @"upload\{yyyy}\{MM}\{dd}";var reg = new Regex(@"(\{.+?})");var match = reg.Matches(path);match.ToList().ForEach(a =>{var str = DateTime.Now.ToString(a.ToString().Substring(1, a.Length - 2)); // 每天一个目录path = path.Replace(a.ToString(), str);});var sizeKb = (long)(files.Length / 1024.0); // 大小KBif (sizeKb > 1048576)throw Oops.Oh("文件超过允许大小");// 后缀var suffix = Path.GetExtension(files.FileName).ToLower();if (string.IsNullOrWhiteSpace(suffix)){var contentTypeProvider = FS.GetFileExtensionContentTypeProvider();suffix = contentTypeProvider.Mappings.FirstOrDefault(u => u.Value == files.ContentType).Key;}if (string.IsNullOrWhiteSpace(suffix))throw Oops.Oh("文件后缀错误");var finalName = Guid.NewGuid() + suffix;//组合路径并创建文件夹var filePath = Path.Combine(App.WebHostEnvironment.ContentRootPath, path);if (!Directory.Exists(filePath))Directory.CreateDirectory(filePath);//上传文件var realFile = Path.Combine(filePath, finalName);using (var stream = System.IO.File.Create(realFile)){await files.CopyToAsync(stream);}//判断文件格式（通过后缀名及文件头编码判断）byte[] bytes = new byte[4];FileStream fileStream = new FileStream(realFile, FileMode.Open, FileAccess.Read);string temstr = "";if (Convert.ToInt32(fileStream.Length) > 0){fileStream.Read(bytes, 0, 4);fileStream.Close();for (int i = 0; i < bytes.Length; i++){temstr += Convert.ToString(bytes[i], 16);}}var fileHeads = temstr.ToUpper();List<FileTypeModel> fileType = new List<FileTypeModel>();fileType.Add(new FileTypeModel { FileNameStr = "xlsx", FileHeadStr = "504B34" });fileType.Add(new FileTypeModel { FileNameStr = "xls", FileHeadStr = "D0CF11E0" });if (!fileType.Any(p => p.FileHeadStr.Contains(fileHeads)) || string.IsNullOrWhiteSpace(fileHeads)){//删除刚刚上传的文件Directory.Delete(realFile, true);throw Oops.Oh("文件类型错误");}#endregion#region//realFile//接下来就可以开始解析了IWorkbook _wb_xls = null;string fileEx = System.IO.Path.GetExtension(Path.GetFileName(realFile));FileStream _file = new FileStream(realFile, FileMode.Open, FileAccess.Read);if (realFile.IndexOf(".xlsx") > 0)_wb_xls = new XSSFWorkbook(_file);else if (realFile.IndexOf(".xls") > 0)_wb_xls = new HSSFWorkbook(_file);//保存成功信息List<DriverImportList> importList = new List<DriverImportList>();List<DriverImportList> errorList = new List<DriverImportList>();//开始读取excel中数据并作数据校验try{for (int i = 3; i < _wb_xls.GetSheet("DriverData").PhysicalNumberOfRows + 1; i++){var isDataCorrect = true;var cellList = _wb_xls.GetSheet("DriverData").GetRow(i);#region 解析司机个人信息PersonalInformationModel driverModel = new PersonalInformationModel();if (!string.IsNullOrEmpty(cellList.GetCell(1) + ""))driverModel.FirstName = cellList.GetCell(1) + "";//if (!string.IsNullOrEmpty(cellList.GetCell(2) + ""))//    driverModel.MiddleName = cellList.GetCell(2) + "";if (!string.IsNullOrEmpty(cellList.GetCell(3) + ""))driverModel.LastName = cellList.GetCell(3) + "";if (!string.IsNullOrEmpty(cellList.GetCell(4) + "")){driverModel.PhonetNo = cellList.GetCell(4) + "";}else{isDataCorrect = false;driverModel.PhonetNo = "Error！Not Null";}if (!string.IsNullOrEmpty(cellList.GetCell(5) + ""))driverModel.EMail = cellList.GetCell(5) + "";if (!string.IsNullOrEmpty(cellList.GetCell(6) + "")){driverModel.SocialInsurance = cellList.GetCell(6) + "";}else{isDataCorrect = false;driverModel.SocialInsurance = "Error！Not Null";}if (!string.IsNullOrEmpty(cellList.GetCell(7) + "")){driverModel.License = cellList.GetCell(7) + "";}else{isDataCorrect = false;driverModel.License = "Error！Not Null";}if (!string.IsNullOrEmpty(cellList.GetCell(8) + "")){driverModel.LicenseFirstIssueDate = cellList.GetCell(8) + "";}else if (!(cellList.GetCell(8) is DateTime)){isDataCorrect = false;driverModel.LicenseFirstIssueDate = "Error！Must Time Type";}else{isDataCorrect = false;driverModel.LicenseFirstIssueDate = "Error！Not Null";}if (!string.IsNullOrEmpty(cellList.GetCell(9) + "")){driverModel.LicenseFromDate = cellList.GetCell(9) + "";}else if (!(cellList.GetCell(9) is DateTime)){isDataCorrect = false;driverModel.LicenseFromDate = "Error！Must Time Type";}else{isDataCorrect = false;driverModel.LicenseFromDate = "Error！Not Null";}if (!string.IsNullOrEmpty(cellList.GetCell(10) + "")){driverModel.LicenseToDate = cellList.GetCell(10) + "";}else if (!(cellList.GetCell(10) is DateTime)){isDataCorrect = false;driverModel.LicenseToDate = "Error！Must Time Type";}else{isDataCorrect = false;driverModel.LicenseToDate = "Error！Not Null";}#endregion#region 解析司机车辆信息VehicleInformationModel carModel = new VehicleInformationModel();//车辆型号if (!string.IsNullOrEmpty(cellList.GetCell(12) + "")){carModel.CarModelType = cellList.GetCell(12) + "";}else{isDataCorrect = false;carModel.CarModelType = "Error！Not Null";}//车身颜色if (!string.IsNullOrEmpty(cellList.GetCell(13) + "")){carModel.ExteriorColor = cellList.GetCell(13) + "";}else{isDataCorrect = false;carModel.ExteriorColor = "Error！Not Null";}//座位数if (!string.IsNullOrEmpty(cellList.GetCell(14) + "")){carModel.Seats = cellList.GetCell(14) + "";}else{isDataCorrect = false;carModel.Seats = "Error！Not Null";}//出厂日期if (!string.IsNullOrEmpty(cellList.GetCell(15) + "")){carModel.ProductionDate = cellList.GetCell(15) + "";}else{isDataCorrect = false;carModel.ProductionDate = "Error！Not Null";}//车牌号if (!string.IsNullOrEmpty(cellList.GetCell(16) + "")){carModel.LicensePlate = cellList.GetCell(16) + "";}else{isDataCorrect = false;carModel.LicensePlate = "Error！Not Null";}//保险开始日期if (!string.IsNullOrEmpty(cellList.GetCell(17) + "")){carModel.InsuranceFromDate = cellList.GetCell(17) + "";}else{isDataCorrect = false;carModel.InsuranceFromDate = "Error！Not Null";}//保险结束日期if (!string.IsNullOrEmpty(cellList.GetCell(18) + "")){carModel.InsuranceToDate = cellList.GetCell(18) + "";}else{isDataCorrect = false;carModel.InsuranceToDate = "Error！Not Null";}#endregion#region 解析司机其他信息OtherInformation otherModel = new OtherInformation();//台班费if (!string.IsNullOrEmpty(cellList.GetCell(20) + "")){otherModel.Percentage_Or_Amount = cellList.GetCell(20) + "";//台班费收费周期if (!string.IsNullOrEmpty(cellList.GetCell(22) + "")){otherModel.ChargeCycle = cellList.GetCell(22) + "";}else{isDataCorrect = false;otherModel.ChargeCycle = "Error！Not Null";}}//订单抽成if (!string.IsNullOrEmpty(cellList.GetCell(21) + "")){otherModel.Percentage = cellList.GetCell(21) + "";}else{isDataCorrect = false;otherModel.Percentage = "Error！Not Null";}//账户状态if (!string.IsNullOrEmpty(cellList.GetCell(23) + "")){otherModel.IsEnabled = cellList.GetCell(23) + "";}else{isDataCorrect = false;otherModel.IsEnabled = "Error！Not Null";}#endregion//如果该条数据数据校验全通过，则添加到list中准备入库if (isDataCorrect){DriverImportList db_import = new DriverImportList();db_import.PersonalInformationModel = driverModel;db_import.VehicleInformationModel = carModel;db_import.OtherInformation = otherModel;importList.Add(db_import);}else{DriverImportList errorModel = new DriverImportList();errorModel.PersonalInformationModel = driverModel;errorModel.VehicleInformationModel = carModel;errorModel.OtherInformation = otherModel;errorList.Add(errorModel);}}}catch (Exception ex){throw Oops.Oh("解析失败，请在下载的模板文件上进行编辑并上传");}#endregion//表头信息var headers = new List<ExcelHeader>{//司机信息new ExcelHeader{ Adress="B2", Value="PersonalInformation\r\n（司机信息）" },new ExcelHeader{ Adress="B3",Width=15,Value="First_Name"},new ExcelHeader{ Adress="C3",Width=15,Value="Last_Name"},new ExcelHeader{ Adress="D3",Width=15,Value="Phonet_No"},new ExcelHeader{ Adress="E3",Width=15,Value="E_Mail"},new ExcelHeader{ Adress="F3",Width=30,Value="Social_Insurance\r\n(社会保险)"},new ExcelHeader{ Adress="G3",Width=15,Value="License\r\n(驾照)"},new ExcelHeader{ Adress="H3",Width=40,Value="License_First_Issue_Date\r\n(驾照首次领证日期)"},new ExcelHeader{ Adress="I3",Width=35,Value="License_From_Date\r\n(驾照起始日期)"},new ExcelHeader{ Adress="J3",Width=35,Value="License_To_Date\r\n(驾照结束日期)"},//车辆信息new ExcelHeader{ Adress="L2", Value="VehicleInformation\r\n（车辆信息）"},new ExcelHeader{ Adress="L3",Width=25,Value="Car_Model_Type\r\n（车辆型号）"},new ExcelHeader{ Adress="M3",Width=25,Value="Exterior_Color\r\n（车辆颜色）"},new ExcelHeader{ Adress="N3",Width=25,Value="Seats\r\n（座椅数量）"},new ExcelHeader{ Adress="O3",Width=25,Value="Production_Date\r\n（出厂日期）"},new ExcelHeader{ Adress="P3",Width=25,Value="License_Plate\r\n（车牌号）"},new ExcelHeader{ Adress="Q3",Width=25,Value="Insurance_From_Date\r\n（保险起始日期）"},new ExcelHeader{ Adress="R3",Width=25,Value="Insurance_To_Date\r\n（保险结束日期）"},//其他信息new ExcelHeader{ Adress="T2", Value="OtherInformation\r\n（其他信息）"},new ExcelHeader{ Adress="T3",Width=30,Value="Percentage_Or_Amount\r\n\r\n（台班费）"},new ExcelHeader{ Adress="U3",Width=30,Value="Percentage\r\n（订单抽成）"},new ExcelHeader{ Adress="V3",Width=30,Value="ChargeCycle\r\n（台班费收费周期）"},new ExcelHeader{ Adress="W3",Width=30,Value="IsEnabled\r\n（账户状态）"},};string localPath = AppDomain.CurrentDomain.BaseDirectory + System.DateTime.Now.ToString("yyyyMMdd");var filepath = Path.Combine(localPath, $"{DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss")}.xlsx");FileInfo file = new FileInfo(filepath);if (file.Exists){file.Delete();file = new FileInfo(filepath);}//创建文件夹if (!Directory.Exists(localPath))Directory.CreateDirectory(localPath);ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;ExcelPackage package = new ExcelPackage(file);//创建sheetExcelWorksheet worksheet = package.Workbook.Worksheets.Add("DriverData");//生成表头for (int i = 0; i < headers.Count; i++){worksheet.Cells[headers[i].Adress].Value = headers[i].Value;//合并单元格worksheet.Cells[2, 2, 2, 12].Merge = true;worksheet.Cells[2, 14, 2, 23].Merge = true;worksheet.Cells[2, 24, 2, 26].Merge = true;worksheet.Cells[headers[i].Adress].Style.Font.Size = 12; //字体大小worksheet.Cells[headers[i].Adress].Style.Font.Bold = true; //设置粗体worksheet.Cells[headers[i].Adress].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //水平居中对齐worksheet.Cells[headers[i].Adress].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //垂直居中对齐if (headers[i].Width > 0)worksheet.Column(ColumnIndex(headers[i].Adress)).Width = headers[i].Width;//给第三行设置行高worksheet.Row(3).Height = 35;//设置表格边框线(设置单元格所有边框)#region 表头设置边框线worksheet.Cells["B2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["C2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["D2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["E2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["F2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["G2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["H2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["I2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["J2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["L2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["M2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["N2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["O2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["P2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["Q2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["R2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["S2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["T2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["V2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["W2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["X2"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["B3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["C3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["D3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["E3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["F3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["G3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["H3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["I3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["J3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["L3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["M3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["N3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["O3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["P3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["Q3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["R3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["S3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["T3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["V3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["W3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));worksheet.Cells["X3"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));//worksheet.Cells[1, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//单独设置单元格底部边框样式和颜色（上下左右均可分开设置）//worksheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));#endregion}//循环填充内容if (errorList != null){//设置背景色Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#7fcbfe");for (int i = 0; i < errorList.Count; i++){if (errorList[i].PersonalInformationModel != null){//姓worksheet.Cells["B" + (4 + i)].Value = errorList[i].PersonalInformationModel.FirstName;worksheet.Cells["B" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.FirstName != null && errorList[i].PersonalInformationModel.FirstName.IndexOf("Error！") > -1){worksheet.Cells["B" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["B" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//名worksheet.Cells["C" + (4 + i)].Value = errorList[i].PersonalInformationModel.LastName;worksheet.Cells["C" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.LastName != null && errorList[i].PersonalInformationModel.LastName.IndexOf("Error！") > -1){worksheet.Cells["C" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["C" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//电话号码worksheet.Cells["D" + (4 + i)].Value = errorList[i].PersonalInformationModel.PhonetNo;worksheet.Cells["D" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.PhonetNo != null && errorList[i].PersonalInformationModel.PhonetNo.IndexOf("Error！") > -1){worksheet.Cells["D" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["D" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//邮箱worksheet.Cells["E" + (4 + i)].Value = errorList[i].PersonalInformationModel.EMail;worksheet.Cells["E" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.EMail != null && errorList[i].PersonalInformationModel.EMail.IndexOf("Error！") > -1){worksheet.Cells["E" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["E" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//社会保障号worksheet.Cells["F" + (4 + i)].Value = errorList[i].PersonalInformationModel.SocialInsurance;worksheet.Cells["F" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.SocialInsurance != null && errorList[i].PersonalInformationModel.SocialInsurance.IndexOf("Error！") > -1){worksheet.Cells["F" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["F" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//驾照号码worksheet.Cells["G" + (4 + i)].Value = errorList[i].PersonalInformationModel.License;worksheet.Cells["G" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.License != null && errorList[i].PersonalInformationModel.License.IndexOf("Error！") > -1){worksheet.Cells["G" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["G" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//初次领证日期worksheet.Cells["H" + (4 + i)].Value = errorList[i].PersonalInformationModel.LicenseFirstIssueDate;worksheet.Cells["H" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.LicenseFirstIssueDate != null && errorList[i].PersonalInformationModel.LicenseFirstIssueDate.IndexOf("Error！") > -1){worksheet.Cells["H" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["H" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//驾照起始日期worksheet.Cells["I" + (4 + i)].Value = errorList[i].PersonalInformationModel.LicenseFromDate;worksheet.Cells["I" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.LicenseFromDate != null && errorList[i].PersonalInformationModel.LicenseFromDate.IndexOf("Error！") > -1){worksheet.Cells["I" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["I" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//驾照结束日期worksheet.Cells["J" + (4 + i)].Value = errorList[i].PersonalInformationModel.LicenseToDate;worksheet.Cells["J" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].PersonalInformationModel.LicenseToDate != null && errorList[i].PersonalInformationModel.LicenseToDate.IndexOf("Error！") > -1){worksheet.Cells["J" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["J" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}}if (errorList[i].VehicleInformationModel != null){//车辆型号worksheet.Cells["M" + (4 + i)].Value = errorList[i].VehicleInformationModel.CarModelType;worksheet.Cells["M" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].VehicleInformationModel.CarModelType != null && errorList[i].VehicleInformationModel.CarModelType.IndexOf("Error！") > -1){worksheet.Cells["M" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["M" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//车身颜色worksheet.Cells["N" + (4 + i)].Value = errorList[i].VehicleInformationModel.ExteriorColor;worksheet.Cells["N" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].VehicleInformationModel.ExteriorColor != null && errorList[i].VehicleInformationModel.ExteriorColor.IndexOf("Error！") > -1){worksheet.Cells["N" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["N" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//座位数worksheet.Cells["O" + (4 + i)].Value = errorList[i].VehicleInformationModel.Seats;worksheet.Cells["O" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].VehicleInformationModel.Seats != null && errorList[i].VehicleInformationModel.Seats.IndexOf("Error！") > -1){worksheet.Cells["O" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["O" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//出厂日期worksheet.Cells["P" + (4 + i)].Value = errorList[i].VehicleInformationModel.ProductionDate;worksheet.Cells["P" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].VehicleInformationModel.ProductionDate != null && errorList[i].VehicleInformationModel.ProductionDate.IndexOf("Error！") > -1){worksheet.Cells["P" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["P" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//车牌号worksheet.Cells["Q" + (4 + i)].Value = errorList[i].VehicleInformationModel.LicensePlate;worksheet.Cells["Q" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].VehicleInformationModel.LicensePlate != null && errorList[i].VehicleInformationModel.LicensePlate.IndexOf("Error！") > -1){worksheet.Cells["Q" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["Q" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//保险起始日期worksheet.Cells["R" + (4 + i)].Value = errorList[i].VehicleInformationModel.InsuranceFromDate;worksheet.Cells["R" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].VehicleInformationModel.InsuranceFromDate != null && errorList[i].VehicleInformationModel.InsuranceFromDate.IndexOf("Error！") > -1){worksheet.Cells["R" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["R" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//保险结束日期worksheet.Cells["S" + (4 + i)].Value = errorList[i].VehicleInformationModel.InsuranceToDate;worksheet.Cells["S" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].VehicleInformationModel.InsuranceToDate != null && errorList[i].VehicleInformationModel.InsuranceToDate.IndexOf("Error！") > -1){worksheet.Cells["S" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["S" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}}if (errorList[i].OtherInformation != null){//台班费worksheet.Cells["X" + (4 + i)].Value = errorList[i].OtherInformation.Percentage_Or_Amount;worksheet.Cells["X" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].OtherInformation.Percentage_Or_Amount.IndexOf("Error！") > -1){worksheet.Cells["X" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["X" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//订单抽成worksheet.Cells["X" + (4 + i)].Value = errorList[i].OtherInformation.Percentage;worksheet.Cells["X" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].OtherInformation.Percentage.IndexOf("Error！") > -1){worksheet.Cells["X" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["X" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//台班费收费周期worksheet.Cells["X" + (4 + i)].Value = errorList[i].OtherInformation.ChargeCycle;worksheet.Cells["X" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].OtherInformation.ChargeCycle.IndexOf("Error！") > -1){worksheet.Cells["X" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["X" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}//账户状态worksheet.Cells["X" + (4 + i)].Value = errorList[i].OtherInformation.IsEnabled;worksheet.Cells["X" + (4 + i)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));if (errorList[i].OtherInformation.IsEnabled.IndexOf("Error！") > -1){worksheet.Cells["X" + (4 + i)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;worksheet.Cells["X" + (4 + i)].Style.Fill.BackgroundColor.SetColor(colFromHex);}}}}package.Save();//转成流之后下载using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read)){byte[] byteArray = new byte[fs.Length];fs.Read(byteArray, 0, byteArray.Length);//删除昨日生成的excel文件夹System.IO.File.Delete(AppDomain.CurrentDomain.BaseDirectory + System.DateTime.Now.AddDays(-1).ToString("yyyyMMdd"));return new FileContentResult(byteArray, "application/octet-stream"){FileDownloadName = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}错误记录.xlsx"};}/// <summary>
/// 根据单元格地址计算出单元格所在列的索引
/// </summary>
/// <param name="reference">单元格地址，示例A1,AB2</param>
/// <returns></returns>
private static int ColumnIndex(string reference)
{int ci = 0;reference = reference.ToUpper();for (int ix = 0; ix < reference.Length && reference[ix] >= 'A'; ix++)ci = (ci * 26) + ((int)reference[ix] - 64);return ci;
}

model部分


public class DriverImportList
{public PersonalInformationModel PersonalInformationModel { get; set; }public VehicleInformationModel VehicleInformationModel { get; set; }public OtherInformation OtherInformation { get; set; }
}/// <summary>
/// excel上传、司机个人信息
/// </summary>
public class PersonalInformationModel
{/// <summary>/// 姓/// </summary>public string FirstName { get; set; }/// <summary>/// 名/// </summary>public string LastName { get; set; }/// <summary>/// 电话号码/// </summary>public string PhonetNo { get; set; }/// <summary>/// 邮箱/// </summary>public string EMail { get; set; }/// <summary>/// 社会保障号/// </summary>public string SocialInsurance { get; set; }/// <summary>/// 驾照/// </summary>public string License { get; set; }/// <summary>/// 初次领证日期/// </summary>public string LicenseFirstIssueDate { get; set; }/// <summary>/// 驾照有效期起始时间/// </summary>public string LicenseFromDate { get; set; }/// <summary>/// 驾照有效期结束时间/// </summary>public string LicenseToDate { get; set; }
}/// <summary>
/// excel上传、车辆相关信息
/// </summary>
public class VehicleInformationModel
{/// <summary>/// 车辆型号/// </summary>public string CarModelType { get; set; }/// <summary>/// 车身颜色/// </summary>public string ExteriorColor { get; set; }/// <summary>/// 座椅数量/// </summary>public string Seats { get; set; }/// <summary>/// 出厂日期/// </summary>public string ProductionDate { get; set; }/// <summary>/// 车牌号/// </summary>public string LicensePlate { get; set; }/// <summary>/// 保险开始日期/// </summary>public string InsuranceFromDate { get; set; }/// <summary>/// 保险结束日期/// </summary>public string InsuranceToDate { get; set; }
}/// <summary>
/// excel上传、其他杂项信息
/// </summary>
public class OtherInformation
{ /// <summary>/// 台班费/// </summary>public string Percentage_Or_Amount { get; set; }/// <summary>/// 台班费收费周期/// </summary>public string ChargeCycle { get; set; }/// <summary>/// 订单抽成/// </summary>public string Percentage { get; set; }/// <summary>/// 账户状态/// </summary>public string IsEnabled { get; set; } 
}public class ExcelHeader
{/// <summary>/// 单元格地址A1,B2等/// </summary>public string Adress { get; set; }/// <summary>/// 单元格值/// </summary>public string Value { get; set; }/// <summary>/// 单元格合并区域,示例A1:B2，为空表示不合并/// </summary>public string MergeArea { get; set; }/// <summary>/// 列宽度，合并列不需要指定宽度，示例：/// | A |/// |B|C|/// 表头A是单元格B和单元格C合并而来，不需要指定宽度，只指定B和C宽度即可/// </summary>public int Width { get; set; }
}

参考文档

1、Asp.NET Core 导出数据到 Excel 文件 - 码农教程

2、.net5下使用EPPlus导出Excel(复杂表头)_.net 导出excel 多表头_数据的流的博客-CSDN博客

3、Excel操作库--EPPLUS常用操作命令汇总（1）_韦_恩的博客-CSDN博客
 

联系方式：

                 wechat&QQ&Tel：13501715983（如查不到请加QQ：631931078或352167311）

                 个人邮箱：13212644043@163.com

OK，暂且这样~

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/100074.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

江西南昌电气机械三维测量仪机械零件3d扫描-CASAIM中科广电

江西南昌电气机械三维测量仪机械零件3d扫描-CASAIM中科广电

精密机械零部件是指机械设备中起到特定功能的零件&#xff0c;其制造精度要求非常高。这些零部件通常由金属、塑料或陶瓷等材料制成&#xff0c;常见的精密机械零部件包括齿轮、轴承、螺丝、活塞、阀门等。精密机械零部件的制造需要高精度的加工设备和工艺&#xff0c;以确保其…
阅读更多...
微信小程序使用npm引入三方包详解

微信小程序使用npm引入三方包详解

目录 1 前言2 微信小程序npm环境搭建2.1 创建package.json文件2.2 修改 project.config.json2.3 修改project.private.config.json配置2.4 构建 npm 包2.5 typescript 支持2.6 安装组件2.7 引入使用组件 1 前言 从小程序基础库版本 2.2.1 或以上、及开发者工具 1.02.1808300 或…
阅读更多...
Mac常见恶意软件再现，办公应用程序潜藏风险如何防范？

Mac常见恶意软件再现，办公应用程序潜藏风险如何防范？

Mac电脑正受到臭名昭著的XLoader恶意软件的新变种的攻击&#xff0c;该恶意软件已被重写为在最好的MacBook上本地运行。 虽然XLoader至少从2015年开始出现&#xff0c;但在2021年发现macOS变体之前&#xff0c;它主要用于针对Windows PC。然而&#xff0c;该版本是作为Java程序…
阅读更多...
【Redis从头学-7】Redis中的Set数据类型实战场景之用户画像去重、共同关注、专属粉丝

【Redis从头学-7】Redis中的Set数据类型实战场景之用户画像去重、共同关注、专属粉丝

&#x1f9d1;‍&#x1f4bb;作者名称&#xff1a;DaenCode &#x1f3a4;作者简介&#xff1a;啥技术都喜欢捣鼓捣鼓&#xff0c;喜欢分享技术、经验、生活。 &#x1f60e;人生感悟&#xff1a;尝尽人生百味&#xff0c;方知世间冷暖。 &#x1f4d6;所属专栏&#xff1a;Re…
阅读更多...
【leetcode 力扣刷题】移除链表元素 多种解法

【leetcode 力扣刷题】移除链表元素 多种解法

移除链表元素的多种解法 203. 移除链表元素解法①&#xff1a;头节点单独判断解法②&#xff1a;虚拟头节点解法③&#xff1a;递归 203. 移除链表元素 题目链接&#xff1a;203.移除链表元素 题目内容&#xff1a; 理解题意&#xff1a;就是单纯的删除链表中所有值等于给定的…
阅读更多...
0基础学习VR全景平台篇 第88篇：智慧眼-成员管理

0基础学习VR全景平台篇 第88篇：智慧眼-成员管理

一、功能说明 成员管理&#xff0c;是指管理智慧眼项目的成员&#xff0c;拥有相关权限的人可以进行添加成员、分配成员角色、设置成员分类、修改成员以及删除成员五项操作。但是仅限于管理自己的下级成员&#xff0c;上级成员无权管理。 二、前台操作页面 登录智慧眼后台操…
阅读更多...
公文校对的艺术：如何确保你的正式文件零错误？

公文校对的艺术：如何确保你的正式文件零错误？

公文是政府和企业中最重要的正式文件之一。一个小小的错误&#xff0c;不仅会影响公文的专业性&#xff0c;甚至可能带来法律和经济后果。因此&#xff0c;如何进行精准的公文校对成为了一门必不可少的技能。接下来&#xff0c;我们将分享一些专业的公文校对技巧&#xff0c;并…
阅读更多...
Docker容器与虚拟化技术：容器运行时说明与比较

Docker容器与虚拟化技术：容器运行时说明与比较

目录 一、理论 1.容器运行时 2.容器运行时接口 3.容器运行时层级 4.容器运行时比较 5.强隔离容器 二、问题 1.K8S为何难以实现真正的多租户 三、总结 一、理论 1.容器运行时 &#xff08;1&#xff09;概念 Container Runtime 是运行于 k8s 集群每个节点中&#xff…
阅读更多...
greenplum gpfdist外部表(只读)的实现---理论

greenplum gpfdist外部表(只读)的实现---理论

经过项目的验证测试以及初步商用化&#xff0c;本篇将进一步讲解greenplum外部表的实现原理&#xff0c;包括设计原则、交互协议与实现流程。gpfdist工具的简介与使用见回顾greenplum gpfdist工具。 1 设计原则 greenplum作为分布式分析型数据库&#xff0c;其每个节点都是独立…
阅读更多...
如何批量加密PDF文件并设置不同密码 - 批量PDF加密工具使用教程

如何批量加密PDF文件并设置不同密码 - 批量PDF加密工具使用教程

如果你正在寻找一种方法来批量加密和保护你的PDF文件&#xff0c;批量PDF加密工具是一个不错的选择。 它是一个体积小巧但功能强大的Windows工具软件&#xff0c;能够批量给多个PDF文件加密和限制&#xff0c;包括设置打印限制、禁止文字复制&#xff0c;并增加独立的打开密码。…
阅读更多...
【Django】Task4 序列化及其高级使用、ModelViewSet

【Django】Task4 序列化及其高级使用、ModelViewSet

【Django】Task4 序列化及其高级使用、ModelViewSet Task4主要了解序列化及掌握其高级使用&#xff0c;了解ModelViewSet的作用&#xff0c;ModelViewSet 是 Django REST framework&#xff08;DRF&#xff09;中的一个视图集类&#xff0c;用于快速创建处理模型数据的 API 视…
阅读更多...
C++ 网络编程项目fastDFS分布式文件系统（四）-fastCGI项目相关技术以及linux搜狗输入法相关问题。

C++ 网络编程项目fastDFS分布式文件系统（四）-fastCGI项目相关技术以及linux搜狗输入法相关问题。

目录 1. Nginx作为web服务器处理请求 2. http协议复习 Get方式提交数据 Post方式提交数据 3. fastCGI 3.1 CGI 3.2 fastCGI 3.3 fastCGI和spawn-fcgi安装 1. 安装fastCGI 2. 安装spawn-fcgi 3.4 nginx && fastcgi 4其他知识点 1. fastCGI环境变量 - fas…
阅读更多...
滑动验证码-elementui实现

滑动验证码-elementui实现

使用elementui框架实现 html代码 <div class"button-center"><el-popoverplacement"top":width"imgWidth"title"安全验证"trigger"manual"v-model"popoverVisible"hide"popoverHide"show&quo…
阅读更多...
P1328 [NOIP2014 提高组] 生活大爆炸版石头剪刀布

P1328 [NOIP2014 提高组] 生活大爆炸版石头剪刀布

题目描述 石头剪刀布是常见的猜拳游戏:石头胜剪刀,剪刀胜布,布胜石头。如果两个人出拳一样&#xff0c;则不分胜负。在《生活大爆炸》第二季第 8 集中出现了一种石头剪刀布的升级版游戏。 升级版游戏在传统的石头剪刀布游戏的基础上,增加了两个新手势: 斯波克:《星际迷航》主…
阅读更多...
FairyGUI编辑器自定义菜单扩展插件

FairyGUI编辑器自定义菜单扩展插件

本文涉及到的软件有&#xff1a;FairyGUI&#xff0c;VSCode 代码环境涉及到了&#xff1a;Lua VSCode插件&#xff1a;EmmyLua 在编写FairyGUI编辑器菜单前&#xff0c;了解一下FairyGUIEditor的API会有效的帮助我们解决很多问题。FairyGUI的扩展是通过编辑器自带的插件功能…
阅读更多...
面试-快速学习计算机网络-UDP/TCP

面试-快速学习计算机网络-UDP/TCP

1. OSI四层和七层映射 区别&#xff1a; 应用层&#xff0c;表示层&#xff0c;会话层合并为了应用层数据链路层和物理层合并为了网络接口层 2. TCP和UDP的区别&#xff1f; 总结&#xff1a; 1 . TCP 向上层提供面向连接的可靠服务 &#xff0c;UDP 向上层提供无连接不可靠服…
阅读更多...
Lnton羚通算法算力云平台在环境配置中Windows10终端和VSCode下如何打开Anaconda-Prompt

Lnton羚通算法算力云平台在环境配置中Windows10终端和VSCode下如何打开Anaconda-Prompt

在Windows 10的终端和VSCode中&#xff0c;可以直接打开Anaconda Prompt。下面是两种方法&#xff1a; Windows 10终端&#xff1a;在开始菜单中搜索"Anaconda Prompt"&#xff0c;然后点击打开。这将启动Anaconda Prompt终端&#xff0c;你可以在其中执行conda相关命…
阅读更多...
2337. 移动片段得到字符串

2337. 移动片段得到字符串

题目描述&#xff1a; 给你两个字符串 start 和 target &#xff0c;长度均为 n 。每个字符串 仅 由字符 ‘L’、‘R’ 和 ‘_’ 组成&#xff0c;其中&#xff1a; 字符 ‘L’ 和 ‘R’ 表示片段&#xff0c;其中片段 ‘L’ 只有在其左侧直接存在一个 空位 时才能向 左 移动&a…
阅读更多...
通过Matlab编程分析微分方程、SS模型、TF模型、ZPK模型的关系

通过Matlab编程分析微分方程、SS模型、TF模型、ZPK模型的关系

微分方程、SS模型、TF模型、ZPK模型的关系 一、Matlab编程 微分方程、SS模型、TF模型、ZPK模型的关系二、对系统输出进行微分计算三、对系统输出进行积分计算四、总结五、系统的零点与极点的物理意义参考 &#xff1a;[https://www.zhihu.com/question/22031360/answer/3073452…
阅读更多...
SpringCloud Gateway服务网关的介绍与使用

SpringCloud Gateway服务网关的介绍与使用

目录 1、网关介绍2、SpringCloudGateway工作原理3、三大组件3.1 、Route&#xff08;路由&#xff09;3.2、断言 Predicate3.3、过滤器 filter 4、Gateway整合nacos的使用4.1 、引入依赖4.2、 编写基础类和启动类4.3、 编写基础配置和路由规则4.4 、测试结果 1、网关介绍 客户…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023