public override List <Dataset> Parse(SpreadsheetDocument document) { List <Dataset> datasets = null; try { WorkbookPart workbookPart = document.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(); Sheets sheets = workbookPart.Workbook.Sheets; Sheet sheet = sheets.GetFirstChild <Sheet>(); SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault(); SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart; string startMarker = "Счет"; uint firstRow = 4; int lastRow = sheetData.Elements <Row>().Count(); datasets = new List <Dataset>(); for (uint i = firstRow; i < lastRow + 1; i += 1) { Console.WriteLine("Строка {0}", i); Dataset ds = new Dataset(); Row r = ExcelUtils.GetRow(sheetData, i); var q = r.Elements <Cell>().Where(c => c.CellValue != null).ToList(); ds.Invoice = ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetCellText(r, "A"))); ds.Name = ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetCellText(r, "B"))); ds.InventoryNumber = ExcelUtils.GetCell(r, "C").DataType == "s" ? ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetCellText(r, "C"))).Trim(' ') : ExcelUtils.GetCellText(r, "C").Trim(' '); ds.KFO = ExcelUtils.GetCellText(r, "D").Trim(' '); ds.startPeriodBalance.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "E").Replace('.', ',')); ds.startPeriodBalance.debit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "F").Replace('.', ',')); ds.startPeriodBalance.credit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "G").Replace('.', ',')); ds.startPeriodBalance.credit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "H").Replace('.', ',')); ds.turnover.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "I").Replace('.', ',')); ds.turnover.debit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "J").Replace('.', ',')); ds.turnover.credit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "K").Replace('.', ',')); ds.turnover.credit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "L").Replace('.', ',')); ds.endPeriodBalance.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "M").Replace('.', ',')); ds.endPeriodBalance.debit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "N").Replace('.', ',')); ds.endPeriodBalance.credit.sum = Convert.ToInt32(ExcelUtils.GetCellText(r, "O").Replace('.', ',')); ds.endPeriodBalance.credit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "P").Replace('.', ',')); datasets.Add(ds); } return(datasets); } catch (Exception e) { Debug.Print(e.Message); Debug.Print(e.StackTrace); return(null); } finally { GC.Collect(); } }
protected static uint FindRowIndexByMarker(SpreadsheetDocument document, string marker) { WorkbookPart workbookPart = document.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(); Sheets sheets = workbookPart.Workbook.Sheets; Sheet sheet = sheets.GetFirstChild <Sheet>(); SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault(); SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart; uint firstRow = 1; int startMarkerId = ExcelUtils.FindStringId(sharedStringPart, marker); foreach (Row rr in sheetData.Elements <Row>()) { Cell cc = rr.Elements <Cell>().FirstOrDefault(); if (cc != null) { if ((cc.DataType == "s") && (Convert.ToInt32(cc.CellValue.Text) == startMarkerId)) { break; } } firstRow++; } return(firstRow); }
private static List <ReviewFields> ParceInputFile(MemoryStream input) { List <ReviewFields> rfs = new List <ReviewFields>(); SpreadsheetDocument ssd = SpreadsheetDocument.Open(input, false); WorkbookPart workbookPart = ssd.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(); Sheets sheets = workbookPart.Workbook.Sheets; Sheet sheet = sheets.GetFirstChild <Sheet>(); SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault(); SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart; foreach (var row in sheetData.Elements <Row>()) { if (row.RowIndex == 1) { continue; } ReviewFields rf = new ReviewFields(); rf.Discipline = ExcelUtils.GetCellText(sharedStringPart, row, "A"); if (rf.Discipline == null) { continue; } rf.Theme = ExcelUtils.GetCellText(sharedStringPart, row, "B"); if (rf.Theme == null) { continue; } rf.StudentName = ExcelUtils.GetCellText(sharedStringPart, row, "C"); if (rf.StudentName == null) { continue; } rf.StudentGroup = ExcelUtils.GetCellText(sharedStringPart, row, "D"); if (rf.StudentGroup == null) { continue; } rf.ChiefName = ExcelUtils.GetCellText(sharedStringPart, row, "E"); if (rf.ChiefName == null) { continue; } uint number; if (UInt32.TryParse(ExcelUtils.GetCellText(sharedStringPart, row, "F"), out number)) { rf.Evaluation = number; } else { rf.Evaluation = 0; } rf.EvaluatonsSet = GetRandomEvaluationsSet(rf.Evaluation); rfs.Add(rf); } ssd.Close(); return(rfs); }
public static ExcelParser GetParser(SpreadsheetDocument document) { WorkbookPart workbookPart = document.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(); Sheets sheets = workbookPart.Workbook.Sheets; Sheet sheet = sheets.GetFirstChild <Sheet>(); SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault(); SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart; string startMarker = "Счет"; int startMarkerID = ExcelUtils.FindStringId(sharedStringPart, startMarker); uint firstRow = 1; if (startMarkerID != -100) { firstRow = FindRowIndexByMarker(document, startMarker); } string formatMarker = "КФО"; int formatMarkerID = ExcelUtils.FindStringId(sharedStringPart, formatMarker); if (startMarkerID != -100 || formatMarkerID != -100) { Row r = ExcelUtils.GetRow(sheetData, 1); if (Convert.ToInt32(ExcelUtils.GetCell(r, "A").CellValue.Text) == startMarkerID || Convert.ToInt32(ExcelUtils.GetCell(r, "D").CellValue.Text) == formatMarkerID) { return(new ParserNewFormat()); } } else { Row r = ExcelUtils.GetRow(sheetData, 1); if (ExcelUtils.GetCell(r, "A").DataType == CellValues.String && ExcelUtils.GetCell(r, "A").CellValue.Text == startMarker || ExcelUtils.GetCell(r, "D").DataType == CellValues.String && ExcelUtils.GetCell(r, "D").CellValue.Text == formatMarker) { return(new ParserNewFormat()); } } if (formatMarkerID != -100) { if (Convert.ToInt32(ExcelUtils.GetRow(sheetData, firstRow + 3).GetFirstChild <Cell>().CellValue.Text) == formatMarkerID) { return(new ParserWhithInventoryNumber()); } } return(new ParserWhithoutInventoryNumber()); }
private bool CheckFileStruct(Stream stream) { SpreadsheetDocument ssd = SpreadsheetDocument.Open(stream, true); WorkbookPart workbookPart = ssd.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(); Sheets sheets = workbookPart.Workbook.Sheets; Sheet sheet = sheets.GetFirstChild <Sheet>(); SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault(); SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart; Row r = ExcelUtils.GetRow(sheetData, 1); foreach (var el in ApplicationContext.checkCellList) { var strId = Convert.ToString(ExcelUtils.FindStringId(sharedStringPart, el.Value)); var cl = ExcelUtils.GetCell(r, el.Key); if (!(cl.DataType == "s" && cl.CellValue.Text == strId)) { return(false); } } return(true); }
public override List <Dataset> Parse(SpreadsheetDocument document) { List <Dataset> datasets = null; try { WorkbookPart workbookPart = document.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(); Sheets sheets = workbookPart.Workbook.Sheets; Sheet sheet = sheets.GetFirstChild <Sheet>(); SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault(); SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart; string startMarker = "Счет"; uint firstRow = FindRowIndexByMarker(document, startMarker); string endMarker = "Итого"; uint lastRow = FindRowIndexByMarker(document, endMarker); string invoice = ExcelUtils.GetRow(sheetData, firstRow + 2).Elements <Cell>().FirstOrDefault().DataType == "s" ? ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetRow(sheetData, firstRow + 2).Elements <Cell>().FirstOrDefault().CellValue.Text)) : ExcelUtils.GetRow(sheetData, firstRow + 2).Elements <Cell>().FirstOrDefault().CellValue.Text; datasets = new List <Dataset>(); for (uint i = firstRow + 4; i < lastRow; i++) { Console.WriteLine("Строка {0}", i); Dataset ds = new Dataset(); Row r = ExcelUtils.GetRow(sheetData, i); var q = r.Elements <Cell>().Where(c => c.CellValue != null).ToList(); ds.Invoice = invoice; ds.Name = ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetCellText(r, "A"))); ds.startPeriodBalance.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "K").Replace('.', ',')); ds.startPeriodBalance.credit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "N").Replace('.', ',')); ds.turnover.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "T").Replace('.', ',')); ds.turnover.credit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "AA").Replace('.', ',')); ds.endPeriodBalance.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "AF").Replace('.', ',')); ds.endPeriodBalance.credit.sum = 0.0; r = ExcelUtils.GetRow(sheetData, i += 1); ds.startPeriodBalance.debit.amount = Convert.ToDouble(ExcelUtils.GetCellText(r, "K").Replace('.', ',')); ds.startPeriodBalance.credit.amount = Convert.ToDouble(ExcelUtils.GetCellText(r, "N").Replace('.', ',')); ds.turnover.debit.amount = Convert.ToDouble(ExcelUtils.GetCellText(r, "T").Replace('.', ',')); ds.turnover.credit.amount = Convert.ToDouble(ExcelUtils.GetCellText(r, "AA").Replace('.', ',')); ds.endPeriodBalance.debit.amount = Convert.ToDouble(ExcelUtils.GetCellText(r, "AF").Replace('.', ',')); ds.endPeriodBalance.credit.amount = 0; datasets.Add(ds); } return(datasets); } catch (Exception e) { Debug.Print(e.Message); Debug.Print(e.StackTrace); return(null); } finally { GC.Collect(); } }