void GenerateColumns(IXlSheet sheet)
{
XlNumberFormat numberFormat = @"#,##0,,""M""";
// Create the "State" column and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 140;
// Create the "Sales" column, adjust its width and set the specific number format for its cells.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 140;
column.ApplyFormatting(numberFormat);
}
// Create the "Sales vs Target" column, adjust its width and format its cells as percentage values.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 120;
column.ApplyFormatting(XlNumberFormat.Percentage2);
}
// Create the "Profit" column, adjust its width and set the specific number format for its cells.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 140;
column.ApplyFormatting(numberFormat);
}
// Create the "Market Share" column, adjust its width and format its cells as percentage values.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 120;
column.ApplyFormatting(XlNumberFormat.Percentage);
}
}
void GenerateColumns(IXlSheet sheet)
{
// Create the "Employee ID" column and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 110;
// Create the "Employee Name" column and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 200;
XlNumberFormat numberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
// Create the "Salary" and "Bonus" columns and set the specific number format for their cells.
for (int i = 0; i < 2; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
column.ApplyFormatting(numberFormat);
}
}
// Create the "Department" column and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 140;
}
void GenerateColumns(IXlSheet sheet)
{
// Create the column "A" and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 18;
// Create the column "B" and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 166;
XlNumberFormat numberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
// Begin to group worksheet columns starting from the column "C" to the column "F".
sheet.BeginGroup(false);
// Create four successive columns ("C", "D", "E" and "F") and set the specific number format for their cells.
for (int i = 0; i < 4; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 117;
column.ApplyFormatting(numberFormat);
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the summary column "G", adjust its width and set the specific number format for its cells.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 117;
column.ApplyFormatting(numberFormat);
}
}
static void AddTable(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())
{
// Create columns "A", "B" and "C" and set their widths.
int[] widths = new int[] { 165, 120, 100 };
for (int i = 0; i < 3; i++)
{
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = widths[i];
}
#region #AddTable
IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Category", "Amount" };
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
// Start generating the table with a header row displayed.
table = row.BeginTable(columnNames, true);
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
// Specify the number format for the "Amount" column and its total cell.
XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
table.Columns[2].DataFormatting = accounting;
table.Columns[2].TotalRowFormatting = accounting;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
row.EndTable(table, true);
#endregion #AddTable
}
}
}
void GenerateTitleRow(IXlSheet sheet, string info, string name, object value, int rowHeight, XlFont font, XlNumberFormat specificFormat)
{
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = rowHeight;
// Set the cell font.
row.ApplyFormatting(font);
// Create the first empty cell.
row.SkipCells(1);
// Create the blank cell with the specified formatting settings.
row.BlankCells(1, leftPanelFormatting);
// Create the third cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = info;
cell.ApplyFormatting(leftPanelFormatting);
}
// Create two blank cells with the specified formatting settings.
row.BlankCells(2, leftPanelFormatting);
// Create the cell, apply specific formatting settings to it and set the cell right border.
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(leftPanelFormatting);
cell.ApplyFormatting(leftPanelBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name;
cell.ApplyFormatting(rightPanelFormatting);
cell.Formatting.Alignment.Indent = 1;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value);
cell.ApplyFormatting(rightPanelFormatting);
if (specificFormat != null)
{
cell.ApplyFormatting(specificFormat);
}
}
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, rightPanelFormatting);
}
}
void GenerateColumns(IXlSheet sheet)
{
XlNumberFormat currencyFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
XlNumberFormat discountFormat = @"0.00%;[Red]-0.00%;;@";
// Create the column "A" and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 21;
// Create the column "B" and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 21;
// Create the column "C" containing the "Description" label in the header row and adjust its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 120;
// Set the horizontal and vertical alignment of cell content.
column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center));
}
// Create the column "D" and adjust its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 263;
// Set the horizontal and vertical alignment of cell content.
column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center));
}
// Create the column "E" containing the "QTY" label in the header row and adjust its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 102;
// Set the horizontal and vertical alignment of cell content.
column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
// Create the column "F" containing the "Unit Price" label in the header row and adjust its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 150;
// Set the horizontal and vertical alignment of cell content.
column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center));
// Apply the currency number format to the column.
column.ApplyFormatting(currencyFormat);
}
// Create the column "G" containing the "Discount" label in the header row and adjust its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 134;
// Set the horizontal and vertical alignment of cell content.
column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
// Apply the custom number format to the column.
column.ApplyFormatting(discountFormat);
}
// Create the column "H" containing the "Amount" label in the header row and adjust its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 174;
// Set the horizontal and vertical alignment of cell content.
column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center));
// Apply the currency number format to the column.
column.ApplyFormatting(currencyFormat);
}
// Create the column "I" and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 21;
}
static void CalculatedColumn(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())
{
// Create worksheet columns (A:F) and set their widths.
int[] widths = new int[] { 165, 100, 100, 100, 100, 110 };
for (int i = 0; i < 6; i++)
{
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = widths[i];
}
#region #CalculatedColumn
IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4", "Yearly Total" };
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
// Start generating the table with a header row displayed.
table = row.BeginTable(columnNames, true);
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[5].TotalRowFunction = XlTotalRowFunction.Sum;
// Specify the number format for numeric values in the table and the total cell of the "Yearly Total" column.
XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
table.DataFormatting = accounting;
table.Columns[5].TotalRowFormatting = accounting;
// Set the formula to calculate annual sales of each product
// and display results in the "Yearly Total" column.
table.Columns[5].SetFormula(XlFunc.Sum(table.GetRowReference("Q1", "Q4")));
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", 17000, 18500, 17500, 18000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", 15500, 14500, 15000, 14000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", 15000, 15750, 16000, 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Ravioli Angelo", 12500, 11000, 13500, 12000 }, null);
// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
row.EndTable(table, true);
#endregion #CalculatedColumn
}
}
}
static void CustomFormatting(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())
{
// Create columns "A", "B" and "C" and set their widths.
int[] widths = new int[] { 165, 120, 100 };
for (int i = 0; i < 3; i++)
{
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = widths[i];
}
#region #CustomFormatting
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
// Create objects containing information about table columns (their names and formatting).
List <XlTableColumnInfo> columns = new List <XlTableColumnInfo>();
columns.Add(new XlTableColumnInfo("Product"));
columns.Add(new XlTableColumnInfo("Category"));
columns.Add(new XlTableColumnInfo("Amount"));
// Specify formatting settings for the last column of the table.
columns[2].HeaderRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, -0.3));
columns[2].DataFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
columns[2].DataFormatting.NumberFormat = accounting;
columns[2].TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.8));
columns[2].TotalRowFormatting.NumberFormat = accounting;
// Specify formatting settings for the header row of the table.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, 0.0));
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.BottomColor = XlColor.FromArgb(0, 0, 0);
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Dashed;
// Start generating the table with a header row displayed.
IXlTable table = row.BeginTable(columns, true, headerRowFormatting);
// Apply the table style.
table.Style.Name = XlBuiltInTableStyleId.Medium16;
// Disable banded row formatting for the table.
table.Style.ShowRowStripes = false;
// Disable the filtering functionality for the table.
table.HasAutoFilter = false;
// Specify formatting settings for the total row of the table.
table.TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
table.TotalRowFormatting.Border = new XlBorder()
{
BottomColor = XlColor.FromTheme(XlThemeColor.Accent6, 0.0),
BottomLineStyle = XlBorderLineStyle.Thick,
TopColor = XlColor.FromArgb(0, 0, 0),
TopLineStyle = XlBorderLineStyle.Dashed
};
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
row.EndTable(sheet.Tables[0], true);
#endregion #CustomFormatting
}
}
}
