private async Task ReadExcelSheet(SpreadsheetDocument doc, string workSheetId)
{
var workpart = (doc.WorkbookPart.GetPartById(workSheetId) as WorksheetPart);
var worksheet = workpart.Worksheet;
if (workpart == null)
{
return;
}
var rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>().Skip(5);
//TODO: verificar se é pos ou extensão
string courseColPos = string.Empty, courseName = string.Empty;
var cellsTitle = worksheet.GetFirstChild <SheetData>()
.Descendants <Row>().ElementAt(2)
.Descendants <Cell>();
courseName = FindCourseName(doc, cellsTitle);
var dataPos = new DataPosition();
var header = rows.First().Descendants <Cell>();
//Map data on sheet
MapDataPosix(doc, dataPos, header);
//Loop for all lines, each line a document will be created
await ProcessData(doc, rows, courseName, dataPos);
}
private void MapDataPosix(SpreadsheetDocument doc, DataPosition dataPos, IEnumerable <Cell> header)
{
for (int i = 0; i < header.Count(); i++)
{
Cell celHeader = header.ElementAt(i);
var cellValue = GetCellValue(doc, celHeader);
dataPos.GetDataPositions(cellValue, celHeader.CellReference.Value);
}
}
private async Task ProcessData(SpreadsheetDocument doc, IEnumerable <Row> rows, string courseName, DataPosition dataPos)
{
foreach (Row row in rows.Skip(1))
{
var cells = row.Descendants <Cell>();
var document = await ExtractFrontDocumentData(doc, cells, courseName,
row.RowIndex.Value, dataPos);
if (string.IsNullOrWhiteSpace(document.StudentName))
{
continue;
}
log.Info($"Send data to queue: {document.StudentName}");
await documentService.Insert(document);
queueCollector.Add(document);
}
}
private async Task <Document> ExtractFrontDocumentData(SpreadsheetDocument doc, IEnumerable <Cell> cells,
string courseName, uint rowIndex, DataPosition data)
{
return(await Task.Factory.StartNew(() =>
{
var documentFront = new Document();
documentFront.StudentName = GetCellValue(doc, cells.FindCell(data.NameCol, rowIndex))?.Trim();
documentFront.StudentDocument = GetCellValue(doc, cells.FindCell(data.DocumentCol, rowIndex))?.Trim();
documentFront.Course = courseName;
//Find by previus header founded
documentFront.StartDate = GetCellValue(doc, cells.FindCell(data.StartDateCol, rowIndex))?.ParseDate();
documentFront.EndDate = GetCellValue(doc, cells.FindCell(data.EndDateCol, rowIndex))?.ParseDate();
documentFront.WorkLoad = GetCellValue(doc, cells.FindCell(data.WorkLoadCol, rowIndex));
//TODO: criar enum para status
documentFront.Status = "Aguardando processamento";
//TODO: colocar data por extenso
documentFront.DateOfIssue = DateTime.Now.ToString("dd/MM/yyyy") + ".";
return documentFront;
}));
}
