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