/// <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();
}
