/// <summary> /// 工作表转换为数据表 /// </summary> /// <param name="sheet">工作表</param> /// <param name="startRowNum">开始行数</param> /// <returns>数据表</returns> public DataTable SheetToDataTable(ISheet sheet, int startRowNum = 0) { var result = new DataTable(sheet.SheetName); ExcelRowModel excelRowModel = GetRows(sheet, startRowNum); for (int i = 0; i < excelRowModel.MaxCellNum; i++) { var dataColumn = new DataColumn(i.ToString(), typeof(string)); result.Columns.Add(dataColumn); } foreach (IRow row in excelRowModel.Rows) { DataRow dataRow = result.NewRow(); bool isAdd = false; for (int i = 0; i < row.LastCellNum; i++) { if (row.GetCell(i).IsNullOrEmptyString()) { continue; } isAdd = true; dataRow[i] = row.GetCell(i).ToString(); } if (isAdd) { result.Rows.Add(dataRow); } } return(result); }
/// <summary> /// Конвертировать лист (worksheet) /// </summary> /// <param name="sheet">Лист</param> /// <returns></returns> public ExcelSheetModel ConvertSheet(ExcelWorksheet sheet) { var rows = new List <ExcelRowModel>(sheet.Cells.Rows); var cells = sheet.Cells; cells.Reset(); int previosRowIndex = 1; var currentCells = new List <ExcelCellModel>(); var currentRow = new ExcelRowModel { RowIndex = 1, Cells = currentCells }; rows.Add(currentRow); while (cells.MoveNext()) { if (cells.Current.Start.Row > previosRowIndex) { currentCells = new List <ExcelCellModel>(); currentRow = new ExcelRowModel { RowIndex = cells.Current.Start.Row, Cells = currentCells }; rows.Add(currentRow); } var currentCell = new ExcelCellModel { ColumnIndex = cells.Current.Start.Column, Value = cells.Current.Value, Address = cells.Current.Address, FullAddress = cells.Current.FullAddress }; currentCells.Add(currentCell); previosRowIndex = cells.Current.Start.Row; } var sheetModel = new ExcelSheetModel { SheetName = sheet.Name, Rows = rows }; return(sheetModel); }
/// <summary> /// 获得行 /// </summary> /// <param name="sheet">工作表对象</param> /// <param name="startRowNum">开始行数</param> /// <returns>工作行</returns> public ExcelRowModel GetRows(ISheet sheet, int startRowNum = 0) { var result = new ExcelRowModel { Rows = new List <IRow>() }; if (sheet.LastRowNum < startRowNum) { throw new MateralException($"表{sheet.SheetName}无数据"); } for (int i = startRowNum; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); result.Rows.Add(row); } return(result); }
public async Task Import(string fileName) { ExcelPackage.LicenseContext = LicenseContext.NonCommercial; var file = new FileInfo(fileName); var excelRows = new List <ExcelRowModel>(); using (var excelFile = new ExcelPackage(file)) { ExcelWorksheet worksheet = excelFile.Workbook.Worksheets[0]; for (int i = 2; i <= worksheet.Dimension.End.Row; i++) { if (worksheet.Cells[$"A{i}"].Value == null) { continue; } var excelRow = new ExcelRowModel() { RowNumber = i, HackneyID = worksheet.Cells[$"A{i}"].Value?.ToString(), ServiceTypeGroup = worksheet.Cells[$"G{i}"].Value?.ToString(), ServiceType = worksheet.Cells[$"H{i}"].Value?.ToString(), ElementType = worksheet.Cells[$"I{i}"].Value?.ToString(), CostPer = worksheet.Cells[$"L{i}"].Value?.ToString(), Quantity = worksheet.Cells[$"N{i}"].Value?.ToString(), UnitOfMeasure = worksheet.Cells[$"N{i}"].Value?.ToString(), StartDateOA = worksheet.Cells[$"W{i}"].Value?.ToString(), EndDateOA = worksheet.Cells[$"X{i}"].Value?.ToString(), BudgetCode = worksheet.Cells[$"Y{i}"].Value?.ToString(), SupplierID = worksheet.Cells[$"AB{i}"].Value?.ToString(), SupplierSite = worksheet.Cells[$"AC{i}"].Value?.ToString() }; excelRows.Add(excelRow); } } var serviceUserPackages = excelRows.GroupBy(x => x.HackneyID, x => x).ToList(); List <string> logs = new List <string>(); foreach (var serviceUserPackage in serviceUserPackages) { bool hasError = false; Guid serviceUserID = await CreateOrSkipUser(serviceUserPackage.Key); if (serviceUserID == Guid.Empty) { logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tService user {serviceUserPackage.Key} not found"); hasError = true; } var primarySupportReasonID = GetPrimarySupportReasonID(serviceUserPackage.FirstOrDefault().BudgetCode.Replace("-", "").Substring(0, 5)); if (primarySupportReasonID == 0) { logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tPrimary support reason {serviceUserPackage.FirstOrDefault().BudgetCode.Substring(0, 5)} not found"); hasError = true; } if (!int.TryParse(serviceUserPackage.FirstOrDefault().SupplierID, out int supplierId)) { logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tSupplierID {serviceUserPackage.FirstOrDefault().SupplierID} must be number."); hasError = true; } var supplierID = GetSupplierID(supplierId, serviceUserPackage.FirstOrDefault().SupplierSite); if (supplierID == 0) { logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tService user {serviceUserPackage.Key}\tSupplierID: {supplierId}\tSupplierSite: {serviceUserPackage.FirstOrDefault().SupplierSite} not found."); hasError = true; } if (hasError) { continue; } var carePackage = new CarePackage() { Id = Guid.NewGuid(), PackageType = ExcelPackageModel.GetPackageType(serviceUserPackage.FirstOrDefault().ServiceType), Status = PackageStatus.Approved, PackageScheduling = PackageScheduling.Temporary, DateCreated = DateTimeOffset.UtcNow, CreatorId = _applicationID, Settings = new CarePackageSettings() { Id = Guid.NewGuid(), CreatorId = _applicationID, DateCreated = DateTimeOffset.UtcNow, }, SupplierId = supplierID, ServiceUserId = serviceUserID, PrimarySupportReasonId = primarySupportReasonID, }; foreach (var package in serviceUserPackage) { var excelPackageModel = new ExcelPackageModel(package.ElementType); if (excelPackageModel.SubPackageType == ExcelPackageModel.ExcelPackageType.Detail) { var corePackage = new CarePackageDetail() { CarePackageId = carePackage.Id, CreatorId = _applicationID, Cost = Math.Abs(package.Cost), DateCreated = DateTimeOffset.UtcNow, Id = Guid.NewGuid(), Type = excelPackageModel.PackageDetailType, UnitOfMeasure = package.UnitOfMeasure, StartDate = package.StartDate.UtcDateTime, CostPeriod = excelPackageModel.CostPeriod, EndDate = package.EndDate?.UtcDateTime, Subjective = package.Subjective }; carePackage.Details.Add(corePackage); } else if (excelPackageModel.SubPackageType == ExcelPackageModel.ExcelPackageType.Reclaim) { var reclaim = new CarePackageReclaim() { CarePackageId = carePackage.Id, CreatorId = _applicationID, Cost = excelPackageModel.ReclaimType == ReclaimType.Fnc ? package.Cost : Math.Abs(package.Cost), Description = package.ElementType, DateCreated = DateTimeOffset.UtcNow, Id = Guid.NewGuid(), StartDate = package.StartDate.UtcDateTime, EndDate = package.EndDate?.UtcDateTime, ClaimCollector = excelPackageModel.ClaimCollector, SubType = excelPackageModel.ReclaimSubType, Type = excelPackageModel.ReclaimType, Status = ReclaimStatus.Active, Subjective = package.Subjective }; carePackage.Reclaims.Add(reclaim); } else { logs.Add($"{DateTimeOffset.UtcNow}\tRow Number: {serviceUserPackage.FirstOrDefault().RowNumber}\tService user {serviceUserPackage.Key}\tPackage Type {package.ElementType} is not valid."); } } SyncFncClaimCollector(carePackage.Reclaims); _databaseContext.CarePackages.Add(carePackage); } File.WriteAllLines($"{fileName}_logs.txt", logs); _databaseContext.SaveChanges(); }