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