private bool SetMovementItem(int rowIndex, MovementItem evi, string cellText) { switch (rowIndex) { case 0: evi.ExternalCode = cellText.Trim(); break; case 1: evi.SubCode = cellText.Trim(); break; case 2: evi.ShortName = cellText.Trim(); break; case 3: case 4: case 5: case 6: case 7: case 8: int i = rowIndex - 3; bool k = new string[] { "1", "Y", "A", "P" }.ToList().IndexOf(cellText) != -1; bool k2 = new string[] { "0", "N", "B" }.ToList().IndexOf(cellText) != -1; if (k || k2) { if (k) { evi.Limits[i] = true; } else { evi.Limits[i] = false; } } break; case 9: evi.Name = cellText.Trim(); break; default: i = rowIndex - 10; k = new string[] { "1", "Y", "A", "P" }.ToList().IndexOf(cellText) != -1; k2 = new string[] { "0", "N", "B" }.ToList().IndexOf(cellText) != -1; if (!k && !k2) { return(false); } if (k) { evi.Items[i].State = true; } else { evi.Items[i].State = false; } break; } return(true); }
private IEnumerable <MovementItem> GetTableFromExcel(WorkbookPart workbookPart, SheetData sheetData, SharedStringTable sst, int operation, int type) { var m = new List <ItemValue>(); var rows = sheetData.Descendants <Row>().Skip(10); int columnIndex = 0; foreach (var r in rows) { var evi = new ItemValue(); columnIndex = 0; foreach (Cell cell in r.Descendants <Cell>()) { string cellText = string.Empty; int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference)); cellColumnIndex--; //zero based index while (columnIndex < cellColumnIndex) { if (!SetItems(columnIndex, evi, cellText)) { break; } columnIndex++; } columnIndex++; var cellValue = cell.CellValue; var text = (cellValue == null) ? cell.InnerText : cellValue.Text; if (cell.DataType?.Value == CellValues.SharedString) { text = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText; } cellText = (text ?? string.Empty).Trim(); if (cell.StyleIndex != null) { var cellFormat = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[int.Parse(cell.StyleIndex.InnerText)] as CellFormat; if (cellFormat != null) { var dateFormat = GetDateTimeFormat(cellFormat.NumberFormatId); if (!string.IsNullOrEmpty(dateFormat)) { if (double.TryParse(cellText, out var cellDouble)) { var theDate = DateTime.FromOADate(cellDouble); cellText = theDate.ToString("dd.MM.yyyy"); } } } } if (!SetItems(cellColumnIndex, evi, cellText) || cellColumnIndex == 1) { break; } } if (!string.IsNullOrWhiteSpace(evi.Prefix)) { m.Add(evi); } } if (m.Count == 0) { yield break; } columnIndex = 2; while (true) { int rowIndex = 0; var ebi = new MovementItem() { Items = m.Select(p => new ItemValue() { Prefix = p.Prefix, Name = p.Name }).ToArray(), Operation = operation, Type = type }; foreach (var r in sheetData.Descendants <Row>()) { foreach (Cell cell in r.Descendants <Cell>().Skip(columnIndex)) { string cellText = string.Empty; int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference)); cellColumnIndex--; //zero based index if (columnIndex < cellColumnIndex) { SetMovementItem(rowIndex, ebi, cellText); //columnIndex++; } //columnIndex++; var cellValue = cell.CellValue; var text = (cellValue == null) ? cell.InnerText : cellValue.Text; if (cell.DataType?.Value == CellValues.SharedString) { text = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText; } cellText = (text ?? string.Empty).Trim(); if (cell.StyleIndex != null) { var cellFormat = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[int.Parse(cell.StyleIndex.InnerText)] as CellFormat; if (cellFormat != null) { var dateFormat = GetDateTimeFormat(cellFormat.NumberFormatId); if (!string.IsNullOrEmpty(dateFormat)) { if (double.TryParse(cellText, out var cellDouble)) { var theDate = DateTime.FromOADate(cellDouble); cellText = theDate.ToString("dd.MM.yyyy"); } } } } SetMovementItem(rowIndex, ebi, cellText); break; } rowIndex++; if (rowIndex > 1 && string.IsNullOrWhiteSpace(ebi.ExternalCode) && string.IsNullOrWhiteSpace(ebi.SubCode)) { yield break; } } //if (!string.IsNullOrWhiteSpace(evi.Prefix)) yield return ebi; yield return(ebi); columnIndex++; } }