void SetupPageParameters(IXlSheet sheet)
{
// Specify the header and footer for the odd-numbered pages.
sheet.HeaderFooter.OddHeader = XlHeaderFooter.FromLCR("NorthWind Inc.", null, XlHeaderFooter.Date);
sheet.HeaderFooter.OddFooter = XlHeaderFooter.FromLCR("List of employees", null, XlHeaderFooter.PageNumber + " of " + XlHeaderFooter.PageTotal);
// Specify page margins.
sheet.PageMargins = new XlPageMargins();
sheet.PageMargins.PageUnits = XlPageUnits.Centimeters;
sheet.PageMargins.Left = 2.0;
sheet.PageMargins.Right = 1.0;
sheet.PageMargins.Top = 1.4;
sheet.PageMargins.Bottom = 1.4;
sheet.PageMargins.Header = 0.7;
sheet.PageMargins.Footer = 0.7;
// Specify page settings.
sheet.PageSetup = new XlPageSetup();
// Select the paper size.
sheet.PageSetup.PaperKind = PaperKind.A4;
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
}
void SetupPageParameters(IXlSheet sheet)
{
// Specify the header and footer for the odd-numbered pages.
sheet.HeaderFooter.OddHeader = XlHeaderFooter.FromLCR(XlHeaderFooter.Bold + "DevAV", null, XlHeaderFooter.Date);
sheet.HeaderFooter.OddFooter = XlHeaderFooter.FromLCR("Sales report", null, XlHeaderFooter.PageNumber + " of " + XlHeaderFooter.PageTotal);
// Specify page settings.
sheet.PageSetup = new XlPageSetup();
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
}
static void HeadersFooters(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
#region #HeaderAndFooters
// Specify different headers and footers for the odd-numbered and even-numbered pages.
sheet.HeaderFooter.DifferentOddEven = true;
// Add the bold text to the header left section,
// and insert the workbook name into the header right section.
sheet.HeaderFooter.OddHeader = XlHeaderFooter.FromLCR(XlHeaderFooter.Bold + "Sample report", null, XlHeaderFooter.BookName);
// Insert the current page number into the footer right section.
sheet.HeaderFooter.OddFooter = XlHeaderFooter.FromLCR(null, null, XlHeaderFooter.PageNumber);
// Insert the workbook file path into the header left section,
// and add the worksheet name to the header right section.
sheet.HeaderFooter.EvenHeader = XlHeaderFooter.FromLCR(XlHeaderFooter.BookPath, null, XlHeaderFooter.SheetName);
// Insert the current page number into the footer left section
// and add the current date to the footer right section.
sheet.HeaderFooter.EvenFooter = XlHeaderFooter.FromLCR(XlHeaderFooter.PageNumber, null, XlHeaderFooter.Date);
#endregion #HeaderAndFooters
// Generate data for the document.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 250;
}
for (int i = 0; i < 4; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = new XlFont();
rowFormatting.Font.Name = "Century Gothic";
rowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(rowFormatting);
headerRowFormatting.Font.Bold = true;
// Generate the header row.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Product";
cell.ApplyFormatting(headerRowFormatting);
}
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
}
}
}
// Generate data rows.
string[] products = new string[] {
"Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni",
"Gnocchi di nonna Alice", "Gudbrandsdalsost", "Gustaf's Knäckebröd", "Queso Cabrales",
"Queso Manchego La Pastora", "Raclette Courdavault", "Singaporean Hokkien Fried Mee", "Wimmers gute Semmelknödel"
};
Random random = new Random();
for (int i = 0; i < 12; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = products[i];
cell.ApplyFormatting(rowFormatting);
}
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
}
}
}
}
}