void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Specify options for exporting the document in CSV format.
CsvDataAwareExporterOptions csvOptions = document.Options as CsvDataAwareExporterOptions;
if (csvOptions != null)
{
csvOptions.WritePreamble = true;
csvOptions.UseCellNumberFormat = false;
csvOptions.NewlineAfterLastRow = true;
}
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Annual Sales";
// 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;
// Generate worksheet columns.
GenerateColumns(sheet);
// Add the title to the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
GenerateTitle(sheet);
}
// Create the header row.
GenerateHeaderRow(sheet);
int firstDataRowIndex = sheet.CurrentRowIndex;
// Create the data rows.
for (int i = 0; i < sales.Count; i++)
{
GenerateDataRow(sheet, sales[i], (i + 1) == sales.Count);
}
// Create the total row.
GenerateTotalRow(sheet, firstDataRowIndex);
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
// Create conditional formatting rules to be applied to worksheet data.
GenerateConditionalFormatting(sheet, firstDataRowIndex);
}
}
static void NumberFormat(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;
// Specify options for exporting the document to CSV format.
CsvDataAwareExporterOptions csvOptions = document.Options as CsvDataAwareExporterOptions;
if (csvOptions != null)
{
csvOptions.WritePreamble = true;
}
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create six successive columns and set their widths.
for (int i = 0; i < 6; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 180;
}
}
#region #ExcelNumberFormat
// Create the header row for the "Excel number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Excel number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the predefined Excel number formats to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Predefined formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.456 as 123.46.
cell.Value = 123.456;
cell.Formatting = XlNumberFormat.Number2;
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 12,345.
cell.Value = 12345;
cell.Formatting = XlNumberFormat.NumberWithThousandSeparator;
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33%.
cell.Value = 0.33;
cell.Formatting = XlNumberFormat.Percentage;
}
using (IXlCell cell = row.CreateCell()) {
// Display the current date as "mm-dd-yy".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortDate;
}
using (IXlCell cell = row.CreateCell()) {
// Display the current time as "h:mm AM/PM".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortTime12;
}
}
// Use custom number formats to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Custom formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 4310.45 as $4,310.45.
cell.Value = 4310.45;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
using (IXlCell cell = row.CreateCell()) {
// Display 3426.75 as €3,426.75.
cell.Value = 3426.75;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_-[$€-2] * #,##0.00_-;-[$€-2] * #,##0.00_-;_-[$€-2] * "" - ""??_-;_-@_-";
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.333 as 33.3%.
cell.Value = 0.333;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "0.0%";
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom number format to the date value.
// Display days as Sunday–Saturday, months as January–December, days as 1–31 and years as 1900–9999.
cell.Value = DateTime.Now;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "dddd, mmmm d, yyyy";
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.6234 as 341/547.
cell.Value = 0.6234;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "# ???/???";
}
}
#endregion #ExcelNumberFormat
sheet.SkipRows(1);
#region #NETNumberFormat
// Create the header row for the ".NET number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = ".NET number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the standard .NET-style format strings to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Standard formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.45 as 123.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("D", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.234500E+004.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("E", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33.00%.
cell.Value = 0.33;
cell.Formatting = XlCellFormatting.FromNetFormat("P", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display the current date using the short date pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("d", true);
}
using (IXlCell cell = row.CreateCell()) {
// Display the current time using the short time pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("t", true);
}
}
// Use custom format strings to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Custom formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.456 as 123.46.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("#0.00", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.235E+04.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("0.0##e+00", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.333 as Max=33.3%.
cell.Value = 0.333;
cell.Formatting = XlCellFormatting.FromNetFormat("Max={0:#.0%}", false);
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current date.
// Display days as 01–31, months as 01-12 and years as a four-digit number.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("dd-MM-yyyy", true);
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current time.
// Display hours as 01-12, minutes as 00-59, and add the AM/PM designator.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("hh:mm tt", true);
}
}
#endregion #NETNumberFormat
}
}
}
static void CsvExportOptions(Stream stream, XlDocumentFormat documentFormat)
{
#region #CsvOptions
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Specify options for exporting the document to CSV format.
CsvDataAwareExporterOptions csvOptions = document.Options as CsvDataAwareExporterOptions;
if (csvOptions != null)
{
// Set the encoding of the text-based file to which the workbook is exported.
csvOptions.Encoding = Encoding.UTF8;
// Write a preamble that specifies the encoding used.
csvOptions.WritePreamble = true;
// Convert a cell value to a string by using the current culture's format string.
csvOptions.UseCellNumberFormat = false;
// Insert the newline character after the last row of the resulting text.
csvOptions.NewlineAfterLastRow = true;
}
// Generate data for the document.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Product";
}
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = string.Format("Q{0}", i + 1);
}
}
}
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];
}
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
}
}
}
}
}
}
#endregion #CsvOptions
}
