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