贵阳市小程序网站开发公司,建网站多少钱合适,网站策划书需求分析,黑糖WordPress主题写在前面的话#xff1a; 【对外承接app API开发、网站建设、系统开发#xff0c;有偿提供帮助#xff0c;联系方式于文章最下方 】
因业务调整#xff0c;不再需要生成错误无excel下载#xff0c;所以先保存代码#xff0c;回头再重新编辑
#region Excel校验部分if (f…写在前面的话 【对外承接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();ListFileTypeModel fileType new ListFileTypeModel();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);//保存成功信息ListDriverImportList importList new ListDriverImportList();ListDriverImportList errorList new ListDriverImportList();//开始读取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 ErrorNot 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 ErrorNot Null;}if (!string.IsNullOrEmpty(cellList.GetCell(7) )){driverModel.License cellList.GetCell(7) ;}else{isDataCorrect false;driverModel.License ErrorNot Null;}if (!string.IsNullOrEmpty(cellList.GetCell(8) )){driverModel.LicenseFirstIssueDate cellList.GetCell(8) ;}else if (!(cellList.GetCell(8) is DateTime)){isDataCorrect false;driverModel.LicenseFirstIssueDate ErrorMust Time Type;}else{isDataCorrect false;driverModel.LicenseFirstIssueDate ErrorNot Null;}if (!string.IsNullOrEmpty(cellList.GetCell(9) )){driverModel.LicenseFromDate cellList.GetCell(9) ;}else if (!(cellList.GetCell(9) is DateTime)){isDataCorrect false;driverModel.LicenseFromDate ErrorMust Time Type;}else{isDataCorrect false;driverModel.LicenseFromDate ErrorNot Null;}if (!string.IsNullOrEmpty(cellList.GetCell(10) )){driverModel.LicenseToDate cellList.GetCell(10) ;}else if (!(cellList.GetCell(10) is DateTime)){isDataCorrect false;driverModel.LicenseToDate ErrorMust Time Type;}else{isDataCorrect false;driverModel.LicenseToDate ErrorNot Null;}#endregion#region 解析司机车辆信息VehicleInformationModel carModel new VehicleInformationModel();//车辆型号if (!string.IsNullOrEmpty(cellList.GetCell(12) )){carModel.CarModelType cellList.GetCell(12) ;}else{isDataCorrect false;carModel.CarModelType ErrorNot Null;}//车身颜色if (!string.IsNullOrEmpty(cellList.GetCell(13) )){carModel.ExteriorColor cellList.GetCell(13) ;}else{isDataCorrect false;carModel.ExteriorColor ErrorNot Null;}//座位数if (!string.IsNullOrEmpty(cellList.GetCell(14) )){carModel.Seats cellList.GetCell(14) ;}else{isDataCorrect false;carModel.Seats ErrorNot Null;}//出厂日期if (!string.IsNullOrEmpty(cellList.GetCell(15) )){carModel.ProductionDate cellList.GetCell(15) ;}else{isDataCorrect false;carModel.ProductionDate ErrorNot Null;}//车牌号if (!string.IsNullOrEmpty(cellList.GetCell(16) )){carModel.LicensePlate cellList.GetCell(16) ;}else{isDataCorrect false;carModel.LicensePlate ErrorNot Null;}//保险开始日期if (!string.IsNullOrEmpty(cellList.GetCell(17) )){carModel.InsuranceFromDate cellList.GetCell(17) ;}else{isDataCorrect false;carModel.InsuranceFromDate ErrorNot Null;}//保险结束日期if (!string.IsNullOrEmpty(cellList.GetCell(18) )){carModel.InsuranceToDate cellList.GetCell(18) ;}else{isDataCorrect false;carModel.InsuranceToDate ErrorNot 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 ErrorNot Null;}}//订单抽成if (!string.IsNullOrEmpty(cellList.GetCell(21) )){otherModel.Percentage cellList.GetCell(21) ;}else{isDataCorrect false;otherModel.Percentage ErrorNot Null;}//账户状态if (!string.IsNullOrEmpty(cellList.GetCell(23) )){otherModel.IsEnabled cellList.GetCell(23) ;}else{isDataCorrect false;otherModel.IsEnabled ErrorNot 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 ListExcelHeader{//司机信息new ExcelHeader{ AdressB2, ValuePersonalInformation\r\n司机信息 },new ExcelHeader{ AdressB3,Width15,ValueFirst_Name},new ExcelHeader{ AdressC3,Width15,ValueLast_Name},new ExcelHeader{ AdressD3,Width15,ValuePhonet_No},new ExcelHeader{ AdressE3,Width15,ValueE_Mail},new ExcelHeader{ AdressF3,Width30,ValueSocial_Insurance\r\n(社会保险)},new ExcelHeader{ AdressG3,Width15,ValueLicense\r\n(驾照)},new ExcelHeader{ AdressH3,Width40,ValueLicense_First_Issue_Date\r\n(驾照首次领证日期)},new ExcelHeader{ AdressI3,Width35,ValueLicense_From_Date\r\n(驾照起始日期)},new ExcelHeader{ AdressJ3,Width35,ValueLicense_To_Date\r\n(驾照结束日期)},//车辆信息new ExcelHeader{ AdressL2, ValueVehicleInformation\r\n车辆信息},new ExcelHeader{ AdressL3,Width25,ValueCar_Model_Type\r\n车辆型号},new ExcelHeader{ AdressM3,Width25,ValueExterior_Color\r\n车辆颜色},new ExcelHeader{ AdressN3,Width25,ValueSeats\r\n座椅数量},new ExcelHeader{ AdressO3,Width25,ValueProduction_Date\r\n出厂日期},new ExcelHeader{ AdressP3,Width25,ValueLicense_Plate\r\n车牌号},new ExcelHeader{ AdressQ3,Width25,ValueInsurance_From_Date\r\n保险起始日期},new ExcelHeader{ AdressR3,Width25,ValueInsurance_To_Date\r\n保险结束日期},//其他信息new ExcelHeader{ AdressT2, ValueOtherInformation\r\n其他信息},new ExcelHeader{ AdressT3,Width30,ValuePercentage_Or_Amount\r\n\r\n台班费},new ExcelHeader{ AdressU3,Width30,ValuePercentage\r\n订单抽成},new ExcelHeader{ AdressV3,Width30,ValueChargeCycle\r\n台班费收费周期},new ExcelHeader{ AdressW3,Width30,ValueIsEnabled\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 namereference单元格地址示例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/// 姓/// /summarypublic string FirstName { get; set; }/// summary/// 名/// /summarypublic string LastName { get; set; }/// summary/// 电话号码/// /summarypublic string PhonetNo { get; set; }/// summary/// 邮箱/// /summarypublic string EMail { get; set; }/// summary/// 社会保障号/// /summarypublic string SocialInsurance { get; set; }/// summary/// 驾照/// /summarypublic string License { get; set; }/// summary/// 初次领证日期/// /summarypublic string LicenseFirstIssueDate { get; set; }/// summary/// 驾照有效期起始时间/// /summarypublic string LicenseFromDate { get; set; }/// summary/// 驾照有效期结束时间/// /summarypublic string LicenseToDate { get; set; }
}/// summary
/// excel上传、车辆相关信息
/// /summary
public class VehicleInformationModel
{/// summary/// 车辆型号/// /summarypublic string CarModelType { get; set; }/// summary/// 车身颜色/// /summarypublic string ExteriorColor { get; set; }/// summary/// 座椅数量/// /summarypublic string Seats { get; set; }/// summary/// 出厂日期/// /summarypublic string ProductionDate { get; set; }/// summary/// 车牌号/// /summarypublic string LicensePlate { get; set; }/// summary/// 保险开始日期/// /summarypublic string InsuranceFromDate { get; set; }/// summary/// 保险结束日期/// /summarypublic string InsuranceToDate { get; set; }
}/// summary
/// excel上传、其他杂项信息
/// /summary
public class OtherInformation
{ /// summary/// 台班费/// /summarypublic string Percentage_Or_Amount { get; set; }/// summary/// 台班费收费周期/// /summarypublic string ChargeCycle { get; set; }/// summary/// 订单抽成/// /summarypublic string Percentage { get; set; }/// summary/// 账户状态/// /summarypublic string IsEnabled { get; set; }
}public class ExcelHeader
{/// summary/// 单元格地址A1,B2等/// /summarypublic string Adress { get; set; }/// summary/// 单元格值/// /summarypublic string Value { get; set; }/// summary/// 单元格合并区域,示例A1:B2为空表示不合并/// /summarypublic string MergeArea { get; set; }/// summary/// 列宽度合并列不需要指定宽度示例/// | A |/// |B|C|/// 表头A是单元格B和单元格C合并而来不需要指定宽度只指定B和C宽度即可/// /summarypublic int Width { get; set; }
} 参考文档
1、Asp.NET Core 导出数据到 Excel 文件 - 码农教程
2、.net5下使用EPPlus导出Excel(复杂表头)_.net 导出excel 多表头_数据的流的博客-CSDN博客
3、Excel操作库--EPPLUS常用操作命令汇总1_韦_恩的博客-CSDN博客
联系方式 wechatQQTel13501715983如查不到请加QQ631931078或352167311 个人邮箱13212644043163.com OK暂且这样~
