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