private Row CreateRowFromDataset(Dataset dataset, uint FormatId = 1)
{
Row row = new Row();
row.Append(
ExcelUtils.ConstructCell(dataset.Invoice, CellValues.String, FormatId),
ExcelUtils.ConstructCell(dataset.Name, CellValues.String, FormatId),
ExcelUtils.ConstructCell(dataset.InventoryNumber,
dataset.InventoryNumber.All(Char.IsDigit) ? CellValues.Number : CellValues.String, FormatId),
ExcelUtils.ConstructCell(dataset.KFO, CellValues.Number, FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.startPeriodBalance.debit.sum).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.startPeriodBalance.debit.amount).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.startPeriodBalance.credit.sum).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.startPeriodBalance.credit.amount).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.turnover.debit.sum).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.turnover.debit.amount).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.turnover.credit.sum).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.turnover.credit.amount).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.endPeriodBalance.debit.sum).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.endPeriodBalance.debit.amount).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.endPeriodBalance.credit.sum).Replace(',', '.'), FormatId),
ExcelUtils.ConstructCell(Convert.ToString(dataset.endPeriodBalance.credit.amount).Replace(',', '.'), FormatId),
new Cell()
{
StyleIndex = FormatId
},
new Cell()
{
StyleIndex = FormatId
},
new Cell()
{
StyleIndex = FormatId
}
);
return(row);
}
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 void CreateExcelDoc()
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Worksheet worksheet = worksheetPart.Worksheet = new Worksheet();
//worksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Report"
};
sheets.Append(sheet);
Columns columns = new Columns();
for (uint i = 0; i < 19; i++)
{
columns.AppendChild(new Column
{
Min = i + 1,
Max = i + 1,
Width = 15,
BestFit = true,
//CustomWidth = true
});
}
worksheet.AppendChild(columns);
workbookPart.Workbook.Save();
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = ExcelUtils.GenerateStylesheet();
stylePart.Stylesheet.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row row = new Row();
MergeCells mergeCells;
if (worksheet.Elements <MergeCells>().Count() > 0)
{
mergeCells = worksheet.Elements <MergeCells>().First();
}
else
{
mergeCells = new MergeCells();
if (worksheet.Elements <CustomChartsheetView>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements <CustomChartsheetView>().First());
}
else
{
worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.GetFirstChild <SheetData>());
}
}
row.Append(
ExcelUtils.ConstructCell("Счет", CellValues.String, 2),
ExcelUtils.ConstructCell("Наименование", CellValues.String, 2),
ExcelUtils.ConstructCell("Инвентарный номер", CellValues.String, 2),
ExcelUtils.ConstructCell("КФО", CellValues.String, 2),
ExcelUtils.ConstructCell("Сальдо на начало периода", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Обороты за период", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Сальдо на конец периода", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Расположение", CellValues.String, 2),
ExcelUtils.ConstructCell("Комментарий", CellValues.String, 2),
ExcelUtils.ConstructCell("Дата обновления", CellValues.String, 2)
);
sheetData.Append(row);
row = new Row();
row.Append(
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Дебет", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Кредит", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Дебет", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Кредит", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Дебет", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Кредит", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
}
);
sheetData.Append(row);
row = new Row();
row.Append(
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
});
sheetData.Append(row);
List <MergeCell> mergeCellsList = new List <MergeCell>()
{
new MergeCell {
Reference = "A1:A3"
},
new MergeCell {
Reference = "B1:B3"
},
new MergeCell {
Reference = "C1:C3"
},
new MergeCell {
Reference = "D1:D3"
},
new MergeCell {
Reference = "E1:H1"
},
new MergeCell {
Reference = "I1:L1"
},
new MergeCell {
Reference = "M1:P1"
},
new MergeCell {
Reference = "Q1:Q3"
},
new MergeCell {
Reference = "R1:R3"
},
new MergeCell {
Reference = "S1:S3"
},
new MergeCell {
Reference = "E2:F2"
},
new MergeCell {
Reference = "G2:H2"
},
new MergeCell {
Reference = "I2:J2"
},
new MergeCell {
Reference = "K2:L2"
},
new MergeCell {
Reference = "M2:N2"
},
new MergeCell {
Reference = "O2:P2"
}
};
mergeCells.Append(mergeCellsList);
worksheetPart.Worksheet.Save();
}
}
catch (Exception e)
{
Debug.Print(e.Message);
}
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();
}
}