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