static void CreateRows(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
#region #CreateRows
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create the first row and set its height to 40 pixels.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 40;
}
// Hide the third row in the worksheet.
using (IXlRow row = sheet.CreateRow(2)) {
row.IsHidden = true;
}
}
#endregion #CreateRows
}
}
static void SimpleFormula(Stream stream, XlDocumentFormat documentFormat)
{
#region #SimpleFormula
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// 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 and set their widths.
for (int i = 0; i < 4; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 80;
}
}
// Generate data for the document.
string[] header = new string[] { "Description", "QTY", "Price", "Amount" };
string[] product = new string[] { "Camembert", "Gorgonzola", "Mascarpone", "Mozzarella" };
int[] qty = new int[] { 12, 15, 25, 10 };
double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 };
double discount = 0.2;
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = header[i];
}
}
}
// Create data rows using string formulas.
for (int i = 0; i < 4; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = product[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = qty[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = price[i];
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the amount
// applying 20% quantity discount on orders more than 15 items.
cell.SetFormula(String.Format("=IF(B{0}>15,C{0}*B{0}*(1-{1}),C{0}*B{0})", i + 2, discount));
}
}
}
}
}
#endregion #SimpleFormula
}
static void Hyperlinks(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;
#region #Hyperlinks
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 300;
}
// Create a hyperlink to a cell in the current workbook.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Local link";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "#Sheet1!C5";
sheet.Hyperlinks.Add(hyperlink);
}
}
// Create a hyperlink to a cell located in the external workbook.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "External file link";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "linked.xlsx#Sheet1!C5";
sheet.Hyperlinks.Add(hyperlink);
}
}
// Create a hyperlink to a web page.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "External URI";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "http://www.devexpress.com";
sheet.Hyperlinks.Add(hyperlink);
}
}
}
#endregion #Hyperlinks
}
}
static void MergeCells(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 the first row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells A1 to E1";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
}
// Create the second row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells A2 to A5";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
// Wrap the text within the cell.
cell.Formatting.Alignment.WrapText = true;
}
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells B2 to E5";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
}
#region #MergeCells
// Merge cells contained in the range A1:E1.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 0, 4, 0));
// Merge cells contained in the range A2:A5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 1, 0, 4));
// Merge cells contained in the range B2:E5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(1, 1, 4, 4));
#endregion #MergeCells
}
}
}
static void HiddenHeaderRow(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 #HiddenHeaderRow
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 the hidden header row.
table = row.BeginTable(columnNames, false);
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
// Populate the first table row with data.
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
}
// Generate the remaining table rows and populate them with data.
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 #HiddenHeaderRow
}
}
}
public void ExportToExcel()
{
SaveFileDialogService.Filter = "Excel files (*.xlsx)|*.xlsx";
if (SaveFileDialogService.ShowDialog())
{
IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xlsx);
string[] columnNames = new string[] { "Word", "Definition" };
string fileName = SaveFileDialogService.GetFullFileName();
using (FileStream stream = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite))
{
using (IXlDocument document = exporter.CreateDocument(stream))
{
using (IXlSheet sheet = document.CreateSheet())
{
sheet.Name = "Dictionary";
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInCharacters = 25;
column.Formatting = new XlCellFormatting();
column.Formatting.Alignment = new XlCellAlignment();
column.Formatting.Alignment.VerticalAlignment = XlVerticalAlignment.Center;
}
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInCharacters = 100;
column.Formatting = new XlCellFormatting();
column.Formatting.Alignment = new XlCellAlignment();
column.Formatting.Alignment.WrapText = true;
}
IXlTable table;
using (IXlRow row = sheet.CreateRow())
table = row.BeginTable(columnNames, true);
foreach (DictionaryBookmark bookmark in DictionaryBookmarksView)
{
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { bookmark.Word, bookmark.Definition }, null);
}
using (IXlRow row = sheet.CreateRow())
row.EndTable(table, false);
table.Style.ShowFirstColumn = true;
}
}
}
Process.Start(fileName);
}
}
static void TableStyle(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 #TableStyle
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);
// Apply the table style.
table.Style.Name = XlBuiltInTableStyleId.Dark7;
}
// 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);
// Create the last table row and finish the table.
// The total row is not displayed for the table.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
row.EndTable(table, false);
}
#endregion #TableStyle
}
}
}
void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Employees";
// Specify print settings for the worksheet.
SetupPageParameters(sheet);
// 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 < employees.Count; i++)
{
GenerateDataRow(sheet, employees[i], (i + 1) == employees.Count);
}
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
// Create data validation criteria for the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
GenerateDataValidations(sheet, firstDataRowIndex);
}
}
// Create the hidden worksheet containing source data for the data validation drop-down list.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
using (IXlSheet sheet = document.CreateSheet()) {
sheet.Name = "Departments";
sheet.VisibleState = XlSheetVisibleState.Hidden;
foreach (string department in departments)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell())
cell.Value = department;
}
}
}
}
}
void GenerateTitle(IXlSheet sheet)
{
// Specify formatting settings for the document title.
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Name = "Calibri Light";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 24;
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Add the document title.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "SALES 2014";
cell.Formatting = formatting;
}
// Create four empty cells with the title formatting applied.
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell())
cell.Formatting = formatting;
}
}
// Skip one row before starting to generate data rows.
sheet.SkipRows(1);
}
void GenerateHeaderRow(IXlSheet sheet)
{
// Create an array that contains column labels for the header row.
string[] columnNames = new string[] { "Description", null, "QTY", "Unit Price", "Discount", "Amount" };
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
row.HeightInPixels = 28;
// Skip the first cell in the row.
row.SkipCells(1);
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, headerRowFormatting);
// Create cells that display column labels and apply specific formatting settings to them.
foreach (string columnName in columnNames)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = columnName;
cell.ApplyFormatting(headerRowFormatting);
}
}
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, headerRowFormatting);
}
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Skip one row before starting to generate the total row.
sheet.SkipRows(1);
// Create the total row.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
row.HeightInPixels = 28;
// Set font characteristics for the row cells.
row.ApplyFormatting(infoFont.Clone());
row.Formatting.Font.Bold = true;
// Skip six successive cells in the total row.
row.SkipCells(6);
// Create the "Total" cell.
using (IXlCell cell = row.CreateCell())
cell.Value = "TOTAL";
// Create the cell that displays the total amount.
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount.
cell.SetFormula(string.Format("SUM(H{0}:H{1})", firstDataRowIndex + 1, row.RowIndex - 1));
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
// Create the empty cell.
using (IXlCell cell = row.CreateCell())
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Create the total row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Create the first cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the second cell in the total row and assign the SUBTOTAL function to it to calculate the average of the subtotal of the cells located in the "Sales" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, row.RowIndex - 1), XlSummary.Average, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Avg=""#,##0,,""M""");
}
// Create the third cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the fourth cell in the total row and assign the SUBTOTAL function to it to calculate the sum of the subtotal of the cells located in the "Profit" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Sum=""#,##0,,""M""");
}
}
}
void GenerateHeaderRow(IXlSheet sheet, string nameOfState)
{
// Create the header row for sales data in the specific state.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the cell that displays the state name and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = nameOfState;
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0)));
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.General, XlVerticalAlignment.Bottom));
}
// Create four successive cells with values "Q1", "Q2", "Q3" and "Q4".
// Apply specific formatting settings to the created cells.
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
}
}
// Create the "Yearly total" cell and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Yearly total";
cell.ApplyFormatting(headerRowFormatting);
}
}
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Create the total row for each inner group of sales in the specific state.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the "Total" cell and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
// Create four successive cells displaying total sales for each quarter individually. Use the SUBTOTAL function to add quarterly sales.
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 2, firstDataRowIndex, j + 2, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
// Create the cell that displays yearly sales for the state. Use the SUBTOTAL function to add yearly sales in the current state for each product.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(6, firstDataRowIndex, 6, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.1)));
}
}
}
void GenerateBillToRow(IXlSheet sheet, string name1, object value1, string name2, object value2, XlBorder specificBorder)
{
using (IXlRow row = sheet.CreateRow()) {
// Set the cell font.
row.ApplyFormatting(infoFont);
// Skip the first cell in the row.
row.SkipCells(1);
// Create the empty cell with the specified formatting settings.
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name1;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// 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(value1);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name2;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// 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(value2);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create three successive cells, apply specific formatting settings to them and set the cell borders.
for (int i = 0; i < 3; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
cell.ApplyFormatting(specificBorder);
}
}
}
}
static void Fill(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;
#region #Fill
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the predefined color.
cell.ApplyFormatting(XlFill.SolidFill(Color.Beige));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the custom RGB color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(0xff, 0x99, 0x66)));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the theme color.
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent3, 0.4)));
}
}
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using predefined colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkDown, Color.Red, Color.White));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using custom RGB colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkTrellis, Color.FromArgb(0xff, 0xff, 0x66), Color.FromArgb(0x66, 0x99, 0xff)));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using theme colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.LightHorizontal, XlColor.FromTheme(XlThemeColor.Accent1, 0.2), XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
#endregion #Fill
}
}
void GenerateHeaderRow(IXlSheet sheet)
{
string[] columnNames = new string[] { "State", "Sales", "Sales vs Target", "Profit", "Market Share" };
// Create the header row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Create required cells in the header row, assign values from the columnNames array to them and apply specific formatting settings.
row.BulkCells(columnNames, headerRowFormatting);
}
}
static void ColorScale(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()) {
// Generate data for the document.
for (int i = 0; i < 11; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.RowIndex + 1;
}
}
}
}
#region #ColorScaleRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (A1:A11 and C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the default three-color scale rule to differentiate low, medium and high values in cell ranges.
XlCondFmtRuleColorScale rule = new XlCondFmtRuleColorScale();
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (B1:B11 and D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the two-color scale rule to differentiate low and high values in cell ranges.
rule = new XlCondFmtRuleColorScale();
rule.ColorScaleType = XlCondFmtColorScaleType.ColorScale2;
// Set a color corresponding to the minimum value in the cell range.
rule.MinColor = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
// Set a color corresponding to the maximum value in the cell range.
rule.MaxColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.5);
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #ColorScaleRule
}
}
}
static void TimePeriod(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()) {
// Generate data for the document.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
column.ApplyFormatting(XlNumberFormat.ShortDate);
}
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = DateTime.Now.AddDays(row.RowIndex - 5);
}
}
}
#region #TimePeriodRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight yesterday's dates in the cell range.
XlCondFmtRuleTimePeriod rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Yesterday;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight today's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Today;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Create the rule to highlight tomorrows's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Tomorrow;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #TimePeriodRule
}
}
}
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);
}
}
static void Blanks(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())
{
// Generate data for the document.
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
if ((i % 2) == 0)
{
cell.Value = i + 1;
}
}
}
}
#region #BlanksRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight blank cells in the range.
XlCondFmtRuleBlanks rule = new XlCondFmtRuleBlanks(true);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight non-blank cells in the range.
rule = new XlCondFmtRuleBlanks(false);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #BlanksRule
}
}
}
void GenerateDataRow(IXlSheet sheet, EmployeeData employee, bool isLastRow)
{
// Create the data row to display the employee's information.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 28;
// Specify formatting settings to be applied to the data rows to shade alternate rows.
XlCellFormatting formatting = new XlCellFormatting();
formatting.CopyFrom((row.RowIndex % 2 == 0) ? evenRowFormatting : oddRowFormatting);
// Set the bottom border for the last data row.
if (isLastRow)
{
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Create the cell containing the employee's ID.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Id;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the employee's name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Name;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the employee's salary.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Salary;
cell.ApplyFormatting(formatting);
}
// Create the cell containing information about bonuses.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Bonus;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the department name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Department;
cell.ApplyFormatting(formatting);
}
}
}
void GenerateDataRow(IXlSheet sheet, SalesData data, bool isLastRow)
{
// Create the data row to display sales information for the specific state.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Specify formatting settings to be applied to the data rows to shade alternate rows.
XlCellFormatting formatting = new XlCellFormatting();
formatting.CopyFrom((row.RowIndex % 2 == 0) ? evenRowFormatting : oddRowFormatting);
// Set the bottom border for the last data row.
if (isLastRow)
{
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Create the cell containing the state name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.State;
cell.ApplyFormatting(formatting);
}
// Create the cell containing sales data.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales;
cell.ApplyFormatting(formatting);
}
// Create the cell that displays the difference between the actual and target sales.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales / data.TargetSales - 1;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the state profit.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Profit;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the percentage of a total market.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.MarketShare;
cell.ApplyFormatting(formatting);
}
}
}
void GenerateHeaderRow(IXlSheet sheet)
{
string[] columnNames = new string[] { "Employee ID", "Employee Name", "Salary", "Bonus", "Department" };
// Create the header row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 28;
// Create required cells in the header row and apply specific formatting settings to them.
foreach (string columnName in columnNames)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = columnName;
cell.ApplyFormatting(headerRowFormatting);
}
}
}
}
void GenerateInfoRow(IXlSheet sheet, string info)
{
// Skip one row before starting to generate the row with additional information.
sheet.SkipRows(1);
// Create the row.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the cell that contains the invoice payment options and set its font attributes.
using (IXlCell cell = row.CreateCell()) {
cell.Value = info;
cell.ApplyFormatting(infoFont);
}
}
}
void GenerateDataRow(IXlSheet sheet, SalesData data)
{
// Create the row to display sales information for each sale item.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first row in the cell.
row.SkipCells(1);
// Create the cell to display the product name and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Product;
cell.ApplyFormatting(dataRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
// Create the cell to display sales amount in the first quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q1;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the second quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q2;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the third quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q3;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the fourth quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q4;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display annual sales for the product. Use the SUM function to add product sales in each quarter.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(2, row.RowIndex, 5, row.RowIndex)));
cell.ApplyFormatting(dataRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
static void Duplicates(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()) {
// Generate data for the document.
for (int i = 0; i < 11; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.ColumnIndex * cell.RowIndex + cell.RowIndex + 1;
}
}
}
}
#region #DuplicatesRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 10));
// Create the rule to identify duplicate values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleDuplicates()
{
Formatting = XlCellFormatting.Bad
});
// Create the rule to identify unique values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleUnique()
{
Formatting = XlCellFormatting.Good
});
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #DuplicatesRule
}
}
}
static void Top10(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()) {
// Generate data for the document.
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.ColumnIndex * 4 + cell.RowIndex + 1;
}
}
}
}
#region #TopAndBottomRules
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:D10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 9));
// Create the rule to identify bottom 10 values in the cell range.
XlCondFmtRuleTop10 rule = new XlCondFmtRuleTop10();
rule.Bottom = true;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to identify top 10 values in the cell range.
rule = new XlCondFmtRuleTop10();
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #TopAndBottomRules
}
}
}
void GenerateInvoiceTitle(IXlSheet sheet)
{
// Create the empty row at the top of the worksheet.
using (IXlRow row = sheet.CreateRow()) { }
// Create the row containing the company name and invoice label.
// Set the row height to 58 pixels and specify font attributes of cell content.
GenerateTitleRow(sheet, "Vader Enterprises", null, "INVOICE", 58, titleFont, null);
// Create the empty row with the default height and specific formatting.
GenerateTitleRow(sheet, null, null, null, -1, panelFont, null);
// Create the row containing the company address and invoice date.
// Set the default row height, specify font attributes and number format of cell content.
GenerateTitleRow(sheet, "123 Home Lane", "DATE", invoice.Date, -1, panelFont, "mmmm d, yyyy");
// Create the row containing the company location and invoice number.
// Set the default row height and specify font attributes of cell content.
GenerateTitleRow(sheet, "Homesville, CA, 55555", "INVOICE#", invoice.InvoiceNum, -1, panelFont, null);
// Create the row containing the company phone number and service description.
// Set the default row height and specify font attributes of cell content.
GenerateTitleRow(sheet, "Phone: (111)111-1111, Fax: (111)111-1112", "FOR", "Service description", -1, panelFont, null);
// Create the empty row with the default height and specific formatting.
GenerateTitleRow(sheet, null, null, null, -1, panelFont, null);
}
static void PageSetup(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 #PageSetup
// Specify page settings for the worksheet.
sheet.PageSetup = new XlPageSetup();
// Select the paper size.
sheet.PageSetup.PaperKind = System.Drawing.Printing.PaperKind.A4;
// Set the page orientation to Landscape.
sheet.PageSetup.PageOrientation = XlPageOrientation.Landscape;
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
// Print in black and white.
sheet.PageSetup.BlackAndWhite = true;
// Specify the number of copies.
sheet.PageSetup.Copies = 2;
#endregion #PageSetup
// Generate data for the document.
sheet.SkipRows(1);
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(1);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Invoke the Page Setup dialog to control page settings.";
}
}
}
}
}
