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