// Adds child parts and generates content of the specified part. public void CreateWorksheetPart(WorksheetPart part) { SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = part.AddNewPart <SpreadsheetPrinterSettingsPart>("rId1"); GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1); GeneratePartContent(part); }
// Generates content of spreadsheetPrinterSettingsPart1. private void GenerateSpreadsheetPrinterSettingsPart1Content(SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1) { System.IO.Stream data = GetBinaryDataStream(spreadsheetPrinterSettingsPart1Data); spreadsheetPrinterSettingsPart1.FeedData(data); data.Close(); }
public static byte[] ExportCurrencies() { using (MemoryStream mem = new MemoryStream()) { using (var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new Workbook(); workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>().Stylesheet = createStylesheetCurrencies(); workbook.WorkbookPart.Workbook.Sheets = new Sheets(); var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = sheetPart.AddNewPart <SpreadsheetPrinterSettingsPart>(); sheetPart.Worksheet = new Worksheet(); SheetProperties sp = new SheetProperties(new PageSetupProperties()); sheetPart.Worksheet.Append(sp); sheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true); Columns cols = new Columns(); UInt32Value ind = 1U; cols.AppendChild(new Column() { Width = 15, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 15, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 30, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); ind++; cols.AppendChild(new Column() { Width = 20, CustomWidth = true, Min = ind, Max = ind }); sheetPart.Worksheet.Append(cols); var sheetData = new SheetData(); sheetPart.Worksheet.Append(sheetData); MergeCells mergeCells1 = new MergeCells(); mergeCells1.AppendChild(new MergeCell() { Reference = "A1:A2" }); mergeCells1.AppendChild(new MergeCell() { Reference = "B1:B2" }); mergeCells1.AppendChild(new MergeCell() { Reference = "D1:E1" }); mergeCells1.AppendChild(new MergeCell() { Reference = "F1:G1" }); mergeCells1.AppendChild(new MergeCell() { Reference = "H1:I1" }); mergeCells1.AppendChild(new MergeCell() { Reference = "J1:K1" }); sheetPart.Worksheet.Append(mergeCells1); Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = "Měnové kurzy" }; sheets.Append(sheet); Row row1 = new Row() { Height = 24, CustomHeight = true }; row1.AppendChild(new Cell() { StyleIndex = 1, CellValue = new CellValue("DATUM"), DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 1, CellValue = new CellValue("POČET"), DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 1, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.CNB)), DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.CSOB)), DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 3, DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.RB)), DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 3, DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.KB)), DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 3, DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.SPORITELNA)), DataType = CellValues.String }); row1.AppendChild(new Cell() { StyleIndex = 3, DataType = CellValues.String }); sheetData.AppendChild(row1); Row row2 = new Row() { Height = 24, CustomHeight = true }; row2.AppendChild(new Cell() { StyleIndex = 1, DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 1, DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 1, CellValue = new CellValue("prodej"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String }); row2.AppendChild(new Cell() { StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String }); sheetData.AppendChild(row2); int rowIndex = 3; foreach (var cur in CurrencySingleton.Instance.Currencies) { Row currencyHeaderRow = new Row() { Height = 24, CustomHeight = true }; currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2, CellValue = new CellValue(cur.Name), DataType = CellValues.String }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); currencyHeaderRow.AppendChild(new Cell() { StyleIndex = 2 }); sheetData.AppendChild(currencyHeaderRow); mergeCells1.AppendChild(new MergeCell() { Reference = "A" + rowIndex + ":K" + rowIndex + "" }); rowIndex++; var dates = cur.BankRates[0].Value.Select(x => x.Key).ToList(); foreach (var date in dates) { Row newRow = new Row() { Height = 24, CustomHeight = true }; newRow.AppendChild(new Cell() { StyleIndex = 1, CellValue = new CellValue(date.ToShortDateString()), DataType = CellValues.Date }); newRow.AppendChild(new Cell() { StyleIndex = 1, CellValue = new CellValue(cur.BankRates[0].Value.First(x => x.Key == date).Value.Amount.ToString()), DataType = CellValues.Number }); if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.CNB)) { newRow.AppendChild(new Cell() { StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CNB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); } else { newRow.AppendChild(new Cell() { StyleIndex = 5 }); } if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.CSOB) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CSOB).Value.Any(x => x.Key == date)) { newRow.AppendChild(new Cell() { StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CSOB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); newRow.AppendChild(new Cell() { StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CSOB).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); } else { newRow.AppendChild(new Cell() { StyleIndex = 6 }); newRow.AppendChild(new Cell() { StyleIndex = 5 }); } if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.RB) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.RB).Value.Any(x => x.Key == date)) { newRow.AppendChild(new Cell() { StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.RB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); newRow.AppendChild(new Cell() { StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.RB).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); } else { newRow.AppendChild(new Cell() { StyleIndex = 6 }); newRow.AppendChild(new Cell() { StyleIndex = 5 }); } if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.KB) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.KB).Value.Any(x => x.Key == date)) { newRow.AppendChild(new Cell() { StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.KB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); newRow.AppendChild(new Cell() { StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.KB).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); } else { newRow.AppendChild(new Cell() { StyleIndex = 6 }); newRow.AppendChild(new Cell() { StyleIndex = 5 }); } if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.SPORITELNA) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.SPORITELNA).Value.Any(x => x.Key == date)) { newRow.AppendChild(new Cell() { StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.SPORITELNA).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); newRow.AppendChild(new Cell() { StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.SPORITELNA).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number }); } else { newRow.AppendChild(new Cell() { StyleIndex = 6 }); newRow.AppendChild(new Cell() { StyleIndex = 5 }); } sheetData.AppendChild(newRow); rowIndex++; } } workbook.WorkbookPart.Workbook.Save(); workbook.Close(); } return(mem.ToArray()); } }