This example demonstrates how to use the Excel Export Library to create spreadsheet files in XLSX, XLS and CSV formats.
[!Important]
The Universal Subscription or an additional Office File API Subscription is required to use this example in production code. For pricing information, please refer to the DevExpress Subscription page.
This project introduces API properties and methods used to perform the following operations:
- Create a workbook, worksheets,rows, columns and particular cells
- Apply predefined, themed and custom formatting to worksheet cells
- Apply conditional formatting rules
- Enable filtering and group data
- Apply data validation rules
- Assign formulas to worksheet cells
- Specify page settings
- Specify print options
- Insert pictures
- Insert hyperlinks
- Specify document properties
- Insert and modify sparklines
- Apply different fonts to specific text regions within a cell
- Create image hyperlinks
- Display a worksheet from right to left
- Create a table
- Apply a table style to a table
- Format the table elements
- Total data in a table
- Create calculated columns
- Password protect a document
- Filter the data by a list of values
- Apply a number filter
- Apply a date filter
- Apply a dynamic filter
- Apply a Top 10 filter
- Filter by a fill color
Files to look at
- CellFormattingActions.cs (VB: CellFormattingActions.vb)
- ConditionalFormattingActions.cs (VB: ConditionalFormattingActions.vb)
- DataActions.cs (VB: DataActions.vb)
- FormulaActions.cs (VB: FormulaActions.vb)
- GeneralActions.cs (VB: GeneralActions.vb)
- MiscellaneousActions.cs (VB: MiscellaneousActions.vb)
- PageViewAndLayoutActions.cs (VB: PageViewAndLayoutActions.vb)
- PictureActions.cs (VB: PictureActions.vb)
- SparklineActions.cs (VB: SparklineActions.vb)
- TableActions.cs (VB: TableActions.vb)
Documentation
- Get Started with the Excel Export Library - Generate an Excel File
- Excel Export Library Overview
- How to: Create a New Document
- How to: Create a New Worksheet
- Rows and Columns
Does this example address your development requirements/objectives?
(you will be redirected to DevExpress.com to submit your response)
Example Code
XLExportExamples/SpreadsheetActions/CellFormattingActions.cs(vb)
C#using DevExpress.Export.Xl;
using DevExpress.XtraExport.Csv;
using System;
using System.Drawing;
using System.Globalization;
using System.IO;
namespace XLExportExamples {
public static class CellFormattingActions {
#region Actions
public static Action<Stream, XlDocumentFormat> PredefinedFormattingAction = PredefinedFormatting;
public static Action<Stream, XlDocumentFormat> ThemedFormattingAction = ThemedFormatting;
public static Action<Stream, XlDocumentFormat> AlignmentAction = Alignment;
public static Action<Stream, XlDocumentFormat> BordersAction = Borders;
public static Action<Stream, XlDocumentFormat> FillAction = Fill;
public static Action<Stream, XlDocumentFormat> FontAction = Font;
public static Action<Stream, XlDocumentFormat> NumberFormatAction = NumberFormat;
public static Action<Stream, XlDocumentFormat> RichTextFormattingAction = RichTextFormatting;
#endregion
static void RichTextFormatting(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 #RichTextFormatting
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create the first column and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 180;
}
// Create the first row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A1.
using (IXlCell cell = row.CreateCell())
{
// Create an XlRichTextString instance.
XlRichTextString richText = new XlRichTextString();
// Add three text runs to the collection.
richText.Runs.Add(new XlRichTextRun("Formatted ", XlFont.CustomFont("Arial", 14.0, XlColor.FromArgb(0x53, 0xbb, 0xf4))));
richText.Runs.Add(new XlRichTextRun("cell ", XlFont.CustomFont("Century Gothic", 14.0, XlColor.FromArgb(0xf1, 0x77, 0x00))));
richText.Runs.Add(new XlRichTextRun("text", XlFont.CustomFont("Consolas", 14.0, XlColor.FromArgb(0xe3, 0x2c, 0x2e))));
// Add the rich formatted text to the cell.
cell.SetRichText(richText);
}
}
}
#endregion #RichTextFormatting
}
}
static void PredefinedFormatting(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 #PredefinedFormatting
// Create a new 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 = 100;
}
}
// Specify the "Good, Bad and Neutral" formatting category.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Good, Bad and Neutral";
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell with the default "Normal" formatting.
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Normal";
}
// Create a cell and apply the "Bad" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Bad";
cell.Formatting = XlCellFormatting.Bad;
}
// Create a cell and apply the "Good" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Good";
cell.Formatting = XlCellFormatting.Good;
}
// Create a cell and apply the "Neutral" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Neutral";
cell.Formatting = XlCellFormatting.Neutral;
}
}
sheet.SkipRows(1);
// Specify the "Data and Model" formatting category.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Data and Model";
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Calculation" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Calculation";
cell.Formatting = XlCellFormatting.Calculation;
}
// Create a cell and apply the "Check Cell" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Check Cell";
cell.Formatting = XlCellFormatting.CheckCell;
}
// Create a cell and apply the "Explanatory..." predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Explanatory";
cell.Formatting = XlCellFormatting.Explanatory;
}
// Create a cell and apply the "Input" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Input";
cell.Formatting = XlCellFormatting.Input;
}
// Create a cell and apply the "Linked Cell" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Linked Cell";
cell.Formatting = XlCellFormatting.LinkedCell;
}
// Create a cell and apply the "Note" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Note";
cell.Formatting = XlCellFormatting.Note;
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Output" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Output";
cell.Formatting = XlCellFormatting.Output;
}
// Create a cell and apply the "Warning Text" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Warning Text";
cell.Formatting = XlCellFormatting.WarningText;
}
}
sheet.SkipRows(1);
// Specify the "Titles and Headings" formatting category.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Titles and Headings";
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Heading 1" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 1";
cell.Formatting = XlCellFormatting.Heading1;
}
// Create a cell and apply the "Heading 2" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 2";
cell.Formatting = XlCellFormatting.Heading2;
}
// Create a cell and apply the "Heading 3" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 3";
cell.Formatting = XlCellFormatting.Heading3;
}
// Create a cell and apply the "Heading 4" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 4";
cell.Formatting = XlCellFormatting.Heading4;
}
// Create a cell and apply the "Title" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Title";
cell.Formatting = XlCellFormatting.Title;
}
// Create a cell and apply the "Total" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total";
cell.Formatting = XlCellFormatting.Total;
}
}
}
#endregion #PredefinedFormatting
}
}
static void ThemedFormatting(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 #ThemedFormatting
// 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 = 100;
}
}
// Specify an array that stores six accent colors of the document theme.
XlThemeColor[] themeColors = new XlThemeColor[] { XlThemeColor.Accent1, XlThemeColor.Accent2, XlThemeColor.Accent3, XlThemeColor.Accent4, XlThemeColor.Accent5, XlThemeColor.Accent6 };
// Specify the "20% - AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 20%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 80%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.8);
}
}
}
// Specify the "40% - AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 40%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 60%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.6);
}
}
}
// Specify the "60% - AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 60%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 40%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.4);
}
}
}
// Specify the "AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0}", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.0);
}
}
}
}
#endregion #ThemedFormatting
}
}
static void Alignment(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 #Alignment
// Create a worksheet.
using(IXlSheet sheet = document.CreateSheet()) {
// Create three successive columns and set their widths.
for(int i = 0; i < 3; i++) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 130;
}
}
// Create the first row in the worksheet.
using(IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Top));
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Top));
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Top));
}
}
// Create the second row in the worksheet.
using(IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center));
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center));
}
}
// Create the third row in the worksheet.
using(IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom));
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Bottom));
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
sheet.SkipRows(1);
// Create the fifth row in the worksheet.
using(IXlRow row = sheet.CreateRow()) {
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "The WrapText property is applied to wrap the text within a cell";
// Wrap the text within the cell.
cell.Formatting = new XlCellAlignment() { WrapText = true };
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Indented text";
// Set the indentation of the cell content.
cell.Formatting = new XlCellAlignment() { Indent = 2 };
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Rotated text";
// Rotate the text within the cell.
cell.Formatting = new XlCellAlignment() { TextRotation = 90 };
}
}
}
#endregion #Alignment
}
}
static void Borders(Stream stream, XlDocumentFormat documentFormat) {
#region #Borders
// Specify a two-dimensional array that stores possible line styles for a border.
XlBorderLineStyle[,] lineStyles = new XlBorderLineStyle[,] {
{ XlBorderLineStyle.Thin, XlBorderLineStyle.Medium, XlBorderLineStyle.Thick, XlBorderLineStyle.Double },
{ XlBorderLineStyle.Dotted, XlBorderLineStyle.Dashed, XlBorderLineStyle.DashDot, XlBorderLineStyle.DashDotDot },
{ XlBorderLineStyle.SlantDashDot, XlBorderLineStyle.MediumDashed, XlBorderLineStyle.MediumDashDot, XlBorderLineStyle.MediumDashDotDot }
};
// 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()) {
for(int i = 0; i < 3; i++) {
sheet.SkipRows(1);
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int j = 0; j < 4; j++) {
row.SkipCells(1);
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set outside borders for the created cell using a particular line style from the lineStyles array.
cell.ApplyFormatting(XlBorder.OutlineBorders(Color.SeaGreen, lineStyles[i, j]));
}
}
}
}
}
}
#endregion #Borders
}
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
}
}
static void Font(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 #Font
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create five successive columns and set their widths.
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
}
}
// Create the first row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Body font";
// Apply the theme body font to the cell content.
cell.ApplyFormatting(XlFont.BodyFont());
}
// Create the cell B1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Headings font";
// Apply the theme heading font to the cell content.
cell.ApplyFormatting(XlFont.HeadingsFont());
}
// Create the cell C1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Custom font";
// Specify the custom font attributes.
XlFont font = new XlFont();
font.Name = "Century Gothic";
font.SchemeStyle = XlFontSchemeStyles.None;
// Apply the custom font to the cell content.
cell.ApplyFormatting(font);
}
}
// Create an array that stores different values of font size.
int[] fontSizes = new int[] { 11, 14, 18, 24, 36 };
// Skip one row in the worksheet.
sheet.SkipRows(1);
// Create the third row.
using (IXlRow row = sheet.CreateRow())
{
// Create five successive cells (A3:E3) with different font sizes.
for (int i = 0; i < 5; i++)
{
using (IXlCell cell = row.CreateCell())
{
// Set the cell value that displays the applied font size.
cell.Value = string.Format("{0}pt", fontSizes[i]);
// Create a font instance of the specified size.
XlFont font = new XlFont();
font.Size = fontSizes[i];
// Apply font settings to the cell content.
cell.ApplyFormatting(font);
}
}
}
// Skip one row in the worksheet.
sheet.SkipRows(1);
// Create the fifth row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Red";
// Create a font instance and set its color.
XlFont font = new XlFont() { Color = Color.Red };
// Apply the font color to the cell content.
cell.ApplyFormatting(font);
}
// Create the cell B5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Bold";
// Create a font instance and set its style to bold.
XlFont font = new XlFont() { Bold = true };
// Apply the font style to the cell content.
cell.ApplyFormatting(font);
}
// Create the cell C5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Italic";
// Create a font instance and set its style to italic.
XlFont font = new XlFont() { Italic = true };
// Italicize the cell text.
cell.ApplyFormatting(font);
}
// Create the cell D5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Underline";
// Create a font instance and set the underline type to double.
XlFont font = new XlFont() { Underline = XlUnderlineType.Double };
// Underline the cell text.
cell.ApplyFormatting(font);
}
// Create the cell E5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "StrikeThrough";
// Create a font instance and turn the strikethrough formatting on.
XlFont font = new XlFont() { StrikeThrough = true };
// Strike the cell text through.
cell.ApplyFormatting(font);
}
}
}
#endregion #Font
}
}
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 = "# ???/???";
}
using (IXlCell cell = row.CreateCell())
{
// Display text value
cell.Value = "test";
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = XlNumberFormat.Text;
}
}
#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
}
}
}
}
}
XLExportExamples/SpreadsheetActions/ConditionalFormattingActions.cs(vb)
C#using System;
using System.Drawing;
using System.Globalization;
using System.IO;
using DevExpress.Export.Xl;
using DevExpress.Spreadsheet;
namespace XLExportExamples
{
public static class ConditionalFormattingActions {
#region Actions
public static Action<Stream, XlDocumentFormat> AverageAction = Average;
public static Action<Stream, XlDocumentFormat> CellIsAction = CellIs;
public static Action<Stream, XlDocumentFormat> BlanksAction = Blanks;
public static Action<Stream, XlDocumentFormat> DuplicatesAction = Duplicates;
public static Action<Stream, XlDocumentFormat> ExpressionAction = Expression;
public static Action<Stream, XlDocumentFormat> SpecificTextAction = SpecificText;
public static Action<Stream, XlDocumentFormat> TimePeriodAction = TimePeriod;
public static Action<Stream, XlDocumentFormat> Top10Action = Top10;
public static Action<Stream, XlDocumentFormat> DataBarAction = DataBar;
public static Action<Stream, XlDocumentFormat> IconSetAction = IconSet;
public static Action<Stream, XlDocumentFormat> ColorScaleAction = ColorScale;
#endregion
static void Average(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 = i + 1;
}
}
}
}
#region #AverageRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule highlighting values that are above the average in the cell range.
XlCondFmtRuleAboveAverage rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.Above;
// 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);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule highlighting values that are above or equal to the average value in the cell range.
rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.AboveOrEqual;
// 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);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the rule highlighting values that are below the average in the cell range.
rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.Below;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
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 the cell range to which the conditional formatting rule should be applied (D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the rule highlighting values that are below or equal to the average value in the cell range.
rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.BelowOrEqual;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #AverageRule
}
}
}
static void CellIs(Stream stream, XlDocumentFormat documentFormat) {
// 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()) {
// Generate data for the document.
for(int i = 0; i < 11; i++) {
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = i + 1;
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = 12 - i;
}
}
}
#region #CellIsRule
// 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:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule to highlight cells whose values are less than 5.
XlCondFmtRuleCellIs rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.LessThan;
rule.Value = 5;
// 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 cells whose values are between 5 and 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.Between;
rule.Value = 5;
rule.SecondValue = 8;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Create the rule to highlight cells whose values are greater than 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = 8;
// 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);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule to highlight cells whose values are greater than a value calculated by a formula.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = "=$A1+3";
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #CellIsRule
}
}
}
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
}
}
}
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 Expression(Stream stream, XlDocumentFormat documentFormat) {
// 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()) {
// Generate data for the document.
int[] width = new int[] { 80, 150, 90 };
for(int i = 0; i < 3; i++) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = width[i];
if(i == 2) {
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = "[$$-409] #,##0.00";
}
}
}
string[] columnNames = new string[] { "Account ID", "User Name", "Balance" };
using(IXlRow row = sheet.CreateRow()) {
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.BottomColor = Color.Black;
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Thin;
for(int i = 0; i < 3; i++) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = columnNames[i];
cell.ApplyFormatting(headerRowFormatting);
}
}
}
string[] accountIds = new string[] { "A105", "A114", "B013", "C231", "D101", "D105" };
string[] users = new string[] { "Berry Dafoe", "Chris Cadwell", "Esta Mangold", "Liam Bell", "Simon Newman", "Wendy Underwood" };
int[] balance = new int[] { 155, 250, 48, 350, -15, 10 };
for(int i = 0; i < 6; i++) {
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = accountIds[i];
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = users[i];
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = balance[i];
}
}
}
#region #ExpressionRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A2:C7).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 1, 2, 6));
// Create the rule that uses a formula to highlight cells if a value in the column "C" is greater than 0 and less than 50.
XlCondFmtRuleExpression rule = new XlCondFmtRuleExpression("AND($C2>0,$C2<50)");
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlFill.SolidFill(Color.FromArgb(0xff, 0xff, 0xcc));
formatting.Rules.Add(rule);
// Create the rule that uses a formula to highlight cells if a value in the column "C" is less than or equal to 0.
rule = new XlCondFmtRuleExpression("$C2<=0");
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #ExpressionRule
}
}
}
static void SpecificText(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.
int[] width = new int[] { 250, 180, 100 };
for(int i = 0; i < 3; i++) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = width[i];
if(i == 2) {
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
}
string[] columnNames = new string[] { "Product", "Delivery", "Sales" };
using(IXlRow row = sheet.CreateRow()) {
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.BottomColor = Color.Black;
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Thin;
for(int i = 0; i < 3; i++) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = columnNames[i];
cell.ApplyFormatting(headerRowFormatting);
}
}
}
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni", "Queso Cabrales", "Raclette Courdavault" };
string[] deliveries = new string[] { "USA", "Worldwide", "USA", "Ships worldwide", "Worldwide except EU", "EU" };
int[] sales = new int[] { 15500, 20250, 12634, 35010, 15234, 10050 };
for(int i = 0; i < 6; i++) {
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = products[i];
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = deliveries[i];
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = sales[i];
}
}
}
#region #SpecificTextRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (B2:B7).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 1, 1, 6));
// Create the rule to highlight cells that contain the given text.
XlCondFmtRuleSpecificText rule = new XlCondFmtRuleSpecificText(XlCondFmtSpecificTextType.Contains, "worldwide");
// 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 #SpecificTextRule
}
}
}
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
}
}
}
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
}
}
}
static void DataBar(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 < 3; i++) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
}
for(int i = 0; i < 11; i++) {
using(IXlRow row = sheet.CreateRow()) {
for(int j = 0; j < 3; j++) {
using(IXlCell cell = row.CreateCell()) {
int rowIndex = cell.RowIndex;
int columnIndex = cell.ColumnIndex;
if(columnIndex == 0)
cell.Value = rowIndex + 1;
else if(columnIndex == 1)
cell.Value = rowIndex - 5;
else
cell.Value = (rowIndex < 5) ? rowIndex + 1 : 11 - rowIndex;
}
}
}
}
#region #DataBarRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule to compare values in the cell range using data bars.
XlCondFmtRuleDataBar rule = new XlCondFmtRuleDataBar();
// Specify the bar color.
rule.FillColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.2);
// Specify the solid fill type.
rule.GradientFill = false;
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 the cell range to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule to compare values in the cell range using data bars.
rule = new XlCondFmtRuleDataBar();
// Set the positive bar color to green.
rule.FillColor = Color.Green;
// Set the border color of positive bars to green.
rule.BorderColor = Color.Green;
// Set the axis color to brown.
rule.AxisColor = Color.Brown;
// Use the gradient fill type
rule.GradientFill = true;
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 the cell range to which the conditional formatting rule should be applied (C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the rule to compare values in the cell range using data bars.
rule = new XlCondFmtRuleDataBar();
// Specify the bar color.
rule.FillColor = XlColor.FromTheme(XlThemeColor.Accent4, 0.2);
// Set the minimum length of the data bar.
rule.MinLength = 10;
// Set the maximum length of the data bar.
rule.MaxLength = 90;
// Set the value corresponding to the shortest bar.
rule.MinValue.ObjectType = XlCondFmtValueObjectType.Number;
rule.MinValue.Value = 3;
// Set the direction of data bars.
rule.Direction = XlDataBarDirection.RightToLeft;
// Hide values of cells to which the rule is applied.
rule.ShowValues = false;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #DataBarRule
}
}
}
static void IconSet(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()) {
if(cell.ColumnIndex % 2 == 0)
cell.Value = cell.RowIndex + 1;
else
cell.Value = cell.RowIndex - 5;
}
}
}
}
#region #IconSetRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule to apply a specific icon from the "3 Arrows" icon set to each cell in the range based on its value.
XlCondFmtRuleIconSet rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.Arrows3;
// Set the rule priority.
rule.Priority = 1;
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 the cell range to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule to apply a specific icon from the "3 Flags" icon set to each cell in the range based on its value.
rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.Flags3;
// Set the rule priority.
rule.Priority = 2;
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 the cell range to which the conditional formatting rule should be applied (C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the rule to apply a specific icon from the "5 Ratings" icon set to each cell in the range based on its value.
rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.Rating5;
// Hide values of cells to which the rule is applied.
rule.ShowValues = false;
// Set the rule priority.
rule.Priority = 3;
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 the cell range to which the conditional formatting rule should be applied (D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the rule to apply a specific icon from the "4 Traffic Lights" icon set to each cell in the range based on its value.
rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.TrafficLights4;
// Reverse the icon order.
rule.Reverse = true;
// Set the rule priority.
rule.Priority = 4;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #IconSetRule
}
}
}
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
}
}
}
}
}
XLExportExamples/SpreadsheetActions/DataActions.cs(vb)
C#using System;
using System.Globalization;
using System.IO;
using DevExpress.Export.Xl;
using DevExpress.Spreadsheet;
namespace XLExportExamples
{
public static class DataActions
{
#region Actions
public static Action<Stream, XlDocumentFormat> AutoFilterAction = AutoFilter;
public static Action<Stream, XlDocumentFormat> CustomFilterAction = CustomFilter;
public static Action<Stream, XlDocumentFormat> ValuesFilterAction = ValuesFilter;
public static Action<Stream, XlDocumentFormat> DateFilterAction = DateFilter;
public static Action<Stream, XlDocumentFormat> DynamicFilterAction = DynamicFilter;
public static Action<Stream, XlDocumentFormat> Top10FilterAction = Top10Filter;
public static Action<Stream, XlDocumentFormat> ColorFilterAction = ColorFilter;
public static Action<Stream, XlDocumentFormat> OutlineGroupingAction = OutlineGrouping;
public static Action<Stream, XlDocumentFormat> DataValidationAction = DataValidation;
#endregion
static void AutoFilter(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 and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
}
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 250;
}
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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Region";
cell.ApplyFormatting(headerRowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Product";
cell.ApplyFormatting(headerRowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Sales";
cell.ApplyFormatting(headerRowFormatting);
}
}
// Generate data for the document.
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = (i < 4) ? "East" : "West";
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = products[i % 4];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = amount[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
#region #AutoFilter
// Enable filtering for the data range.
sheet.AutoFilterRange = sheet.DataRange;
#endregion #AutoFilter
}
}
}
static void CustomFilter(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, set their widths and number format.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 100;
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 250;
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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
#region #CustomFilter
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { "Region", "Product", "Sales" }, headerRowFormatting);
// Create a custom filter to display values in the "Sales" column that are greater than $4500.
XlCustomFilters filter = new XlCustomFilters(new XlCustomFilterCriteria(XlFilterOperator.GreaterThanOrEqual, 4500));
sheet.AutoFilterColumns.Add(new XlFilterColumn(2, filter));
// Start filtering data.
sheet.BeginFiltering(sheet.DataRange);
// Generate data for the document.
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = (i < 4) ? "East" : "West";
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = products[i % 4];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = amount[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Finish filtering.
sheet.EndFiltering();
#endregion #CustomFilter
}
}
}
static void ValuesFilter(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, set their widths and number format.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 100;
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 250;
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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
#region #ValuesFilter
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { "Region", "Product", "Sales" }, headerRowFormatting);
// Start filtering data in the "Product" column by a list of values.
XlValuesFilter filter = new XlValuesFilter();
filter.Values.Add("Mascarpone Fabioli");
filter.Values.Add("Mozzarella di Giovanni");
sheet.AutoFilterColumns.Add(new XlFilterColumn(1, filter));
sheet.BeginFiltering(sheet.DataRange);
// Generate data for the document.
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = (i < 4) ? "East" : "West";
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = products[i % 4];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = amount[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Finish filtering.
sheet.EndFiltering();
#endregion #ValuesFilter
}
}
}
static void DateFilter(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, set their widths and number format.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.Formatting = XlNumberFormat.ShortDate;
}
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 250;
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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
#region #DateFilter
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { "Date", "Customer", "Total" }, headerRowFormatting);
// Create a date filter to display sales data for the current year.
XlValuesFilter filter = new XlValuesFilter();
filter.DateGroups.Add(new XlDateGroupItem(DateTime.Today, XlDateTimeGroupingType.Year));
sheet.AutoFilterColumns.Add(new XlFilterColumn(0, filter));
sheet.BeginFiltering(sheet.DataRange);
// Generate data for the document.
string[] customers = new string[] { "Tom's Club", "E-Mart", "K&S Music", "Walters" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = (i < 4) ? new DateTime(DateTime.Today.AddYears(-1).Year, 9 + i, 2 * i + 7) : new DateTime(DateTime.Today.Year, i - 3, 2 * i + 7);
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = customers[i % 4];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = amount[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Finish filtering.
sheet.EndFiltering();
#endregion #DateFilter
}
}
}
static void DynamicFilter(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, set their widths and number format.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.Formatting = XlNumberFormat.ShortDate;
}
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 250;
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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
#region #DynamicFilter
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { "Date", "Customer", "Total" }, headerRowFormatting);
// Create a dynamic filter to display dates that occur this month.
XlDynamicFilter filter = new XlDynamicFilter(XlDynamicFilterType.ThisMonth);
sheet.AutoFilterColumns.Add(new XlFilterColumn(0, filter));
// Start filtering data.
sheet.BeginFiltering(sheet.DataRange);
// Generate data for the document.
string[] customers = new string[] { "Tom's Club", "E-Mart", "K&S Music", "Walters" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = DateTime.Now.AddDays(-7 * (7 - i));
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = customers[i % 4];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = amount[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Finish filtering.
sheet.EndFiltering();
#endregion #DynamicFilter
}
}
}
static void Top10Filter(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, set their widths and number format.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.Formatting = XlNumberFormat.ShortDate;
}
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 250;
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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
#region #Top10Filter
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { "Region", "Product", "Sales" }, headerRowFormatting);
// Create a Top 10 filter to display three products with the highest sales.
XlTop10Filter filter = new XlTop10Filter(3, 5500, true, false);
sheet.AutoFilterColumns.Add(new XlFilterColumn(2, filter));
// Start filtering data.
sheet.BeginFiltering(sheet.DataRange);
// Generate data for the document.
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = (i < 4) ? "East" : "West";
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = products[i % 4];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = amount[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Finish filtering.
sheet.EndFiltering();
#endregion #Top10Filter
}
}
}
static void ColorFilter(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, set their widths and number format.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 100;
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 250;
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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
#region #ColorFilter
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { "Region", "Product", "Sales" }, headerRowFormatting);
// Start filtering data in the "Product" column by the specified fill color.
XlColorFilter filter = new XlColorFilter();
filter.Color = XlColor.FromArgb(0x00ffcc99);
filter.FilterByCellColor = true;
sheet.AutoFilterColumns.Add(new XlFilterColumn(1, filter));
sheet.BeginFiltering(sheet.DataRange);
// Generate data for the document.
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
for (int i = 0; i < 8; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = (i < 4) ? "East" : "West";
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = products[i % 4];
cell.ApplyFormatting(rowFormatting);
if (i % 4 == 0)
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromArgb(0xffcc99)));
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = amount[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Finish filtering.
sheet.EndFiltering();
#endregion #ColorFilter
}
}
}
static void OutlineGrouping(Stream stream, XlDocumentFormat documentFormat)
{
#region #Group/Outline
// 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())
{
// Specify the summary row and summary column location for the grouped data.
sheet.OutlineProperties.SummaryBelow = true;
sheet.OutlineProperties.SummaryRight = true;
// Create the column "A" and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
// Begin to group worksheet columns starting from the column "B" to the column "E".
sheet.BeginGroup(false);
// Create four successive columns ("B", "C", "D" and "E") and set the specific number format for their cells.
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]* ""-""??_);_(@_)";
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the column "F", adjust its width and set the specific number format for its cells.
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 = XlFont.BodyFont();
rowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, 0.0));
// Specify formatting settings for the header rows.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
// Specify formatting settings for the total rows.
XlCellFormatting totalRowFormatting = new XlCellFormatting();
totalRowFormatting.Font = XlFont.BodyFont();
totalRowFormatting.Font.Bold = true;
totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0));
// Specify formatting settings for the grand total row.
XlCellFormatting grandTotalRowFormatting = new XlCellFormatting();
grandTotalRowFormatting.Font = XlFont.BodyFont();
grandTotalRowFormatting.Font.Bold = true;
grandTotalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.2));
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
// Begin to group worksheet rows (create the outer group of rows).
sheet.BeginGroup(false);
for (int p = 0; p < 2; p++)
{
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = (p == 0) ? "East" : "West";
cell.ApplyFormatting(headerRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
}
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Yearly total";
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
// Create and group data rows (create the inner group of rows containing sales data for the specific region).
sheet.BeginGroup(false);
for (int i = 0; i < 4; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = products[i];
cell.ApplyFormatting(rowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
using (IXlCell cell = row.CreateCell())
{
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(1, row.RowIndex, 4, row.RowIndex)));
cell.ApplyFormatting(rowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the total row.
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Total";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
for (int j = 0; j < 5; j++)
{
using (IXlCell cell = row.CreateCell())
{
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, row.RowIndex - 4, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the grand total row.
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Grand total";
cell.ApplyFormatting(grandTotalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.4));
}
for (int j = 0; j < 5; j++)
{
using (IXlCell cell = row.CreateCell())
{
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, 1, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(grandTotalRowFormatting);
}
}
}
}
}
#endregion #Group/Outline
}
static void DataValidation(Stream stream, XlDocumentFormat documentFormat)
{
// 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.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 110;
column.Formatting = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
}
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 190;
}
for (int i = 0; i < 2; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 90;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 130;
}
sheet.SkipColumns(1);
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 130;
}
// 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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
string[] columnNames = new string[] { "Employee ID", "Employee name", "Salary", "Bonus", "Department" };
row.BulkCells(columnNames, headerRowFormatting);
row.SkipCells(1);
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Departments";
cell.ApplyFormatting(headerRowFormatting);
}
}
// Generate data for the document.
int[] id = new int[] { 10115, 10709, 10401, 10204 };
string[] name = new string[] { "Augusta Delono", "Chris Cadwell", "Frank Diamond", "Simon Newman" };
int[] salary = new int[] { 1100, 2000, 1750, 1250 };
int[] bonus = new int[] { 50, 180, 100, 80 };
int[] deptid = new int[] { 0, 2, 3, 3 };
string[] department = new string[] { "Accounting", "IT", "Management", "Manufacturing" };
for (int i = 0; i < 4; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = id[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = name[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = salary[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = bonus[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell())
{
cell.Value = department[deptid[i]];
cell.ApplyFormatting(rowFormatting);
}
row.SkipCells(1);
using (IXlCell cell = row.CreateCell())
{
cell.Value = department[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
#region #DataValidation
// Apply data validation to cells.
// Restrict data entry in the range A2:A5 to a 5-digit number.
XlDataValidation validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(0, 1, 0, 4));
validation.Type = XlDataValidationType.Custom;
validation.Criteria1 = "=AND(ISNUMBER(A2),LEN(A2)=5)";
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);
// Restrict data entry in the cell range C2:C5 to a whole number between 600 and 2000.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(2, 1, 2, 4));
validation.Type = XlDataValidationType.Whole;
validation.Operator = XlDataValidationOperator.Between;
validation.Criteria1 = 600;
validation.Criteria2 = 2000;
// Display the error message.
validation.ErrorMessage = "The salary amount must be between 600$ and 2000$.";
validation.ErrorTitle = "Warning";
validation.ErrorStyle = XlDataValidationErrorStyle.Warning;
validation.ShowErrorMessage = true;
// Display the input message.
validation.InputPrompt = "Please enter a whole number between 600 and 2000";
validation.PromptTitle = "Salary";
validation.ShowInputMessage = true;
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);
// Restrict data entry in the cell range D2:D5 to a decimal number within the specified limits.
// Bonus cannot be greater than 10% of the salary.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(3, 1, 3, 4));
validation.Type = XlDataValidationType.Decimal;
validation.Operator = XlDataValidationOperator.Between;
validation.Criteria1 = 0;
// Use a formula to specify the validation criterion.
validation.Criteria2 = "=C2*0.1";
// Display the error message.
validation.ErrorMessage = "Bonus cannot be greater than 10% of the salary.";
validation.ErrorTitle = "Information";
validation.ErrorStyle = XlDataValidationErrorStyle.Information;
validation.ShowErrorMessage = true;
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);
// Restrict data entry in the cell range E2:E5 to values in a drop-down list obtained from the cells G2:G5.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(4, 1, 4, 4));
validation.Type = XlDataValidationType.List;
validation.ListRange = XlCellRange.FromLTRB(6, 1, 6, 4).AsAbsolute();
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);
#endregion #DataValidation
}
}
}
}
}
XLExportExamples/SpreadsheetActions/FormulaActions.cs(vb)
C#using System;
using System.Globalization;
using System.IO;
using DevExpress.Export.Xl;
using DevExpress.Spreadsheet;
namespace XLExportExamples {
public static class FormulaActions {
#region Actions
public static Action<Stream, XlDocumentFormat> SimpleFormulasAction = SimpleFormula;
public static Action<Stream, XlDocumentFormat> ComplexFormulasAction = ComplexFormulas;
public static Action<Stream, XlDocumentFormat> SharedFormulasAction = SharedFormulas;
public static Action<Stream, XlDocumentFormat> SubtotalsAction = Subtotals;
#endregion
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 ComplexFormulas(Stream stream, XlDocumentFormat documentFormat) {
// 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.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 200;
}
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 50;
}
for (int i = 0; i < 2; i++) {
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 80;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Specify formatting settings for the total row.
XlCellFormatting totalRowFormatting = new XlCellFormatting();
totalRowFormatting.Font = XlFont.BodyFont();
totalRowFormatting.Font.Bold = true;
// Generate data for the document.
string[] header = new string[] { "Description", "QTY", "Price", "Amount" };
string[] product = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] qty = new int[] { 12, 15, 25, 10 };
double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 };
// 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];
cell.ApplyFormatting(headerRowFormatting);
}
}
}
#region #Formula_String
// 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 per product.
cell.SetFormula(String.Format("B{0}*C{0}", i + 2));
}
}
}
#endregion #Formula_String
#region #Formula_IXlFormulaParameter
// Create the total row using IXlFormulaParameter.
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(2);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total:";
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount plus 10 handling fee.
// =SUM($D$2:$D$5)+10
IXlFormulaParameter const10 = XlFunc.Param(10);
IXlFormulaParameter sumAmountFunction = XlFunc.Sum(XlCellRange.FromLTRB(cell.ColumnIndex, 1, cell.ColumnIndex, row.RowIndex - 1).AsAbsolute());
cell.SetFormula(XlOper.Add(sumAmountFunction, const10));
cell.ApplyFormatting(totalRowFormatting);
}
}
#endregion #Formula_IXlFormulaParameter
#region #Formula_XlExpression
// Create a formula using XlExpression.
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(2);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Mean value:";
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the mean value.
// =$D$6/4
XlExpression expression = new XlExpression();
expression.Add(new XlPtgRef(new XlCellPosition(cell.ColumnIndex, row.RowIndex - 1, XlPositionType.Absolute, XlPositionType.Absolute)));
expression.Add(new XlPtgInt(row.RowIndex - 2));
expression.Add(new XlPtgBinaryOperator(XlPtgTypeCode.Div));
cell.SetFormula(expression);
cell.ApplyFormatting(totalRowFormatting);
}
}
#endregion #Formula_XlExpression
}
}
}
static void SharedFormulas(Stream stream, XlDocumentFormat documentFormat) {
// 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.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 200;
}
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 50;
}
for (int i = 0; i < 2; i++) {
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 80;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Specify formatting settings for the total row.
XlCellFormatting totalRowFormatting = new XlCellFormatting();
totalRowFormatting.Font = XlFont.BodyFont();
totalRowFormatting.Font.Bold = true;
// Generate data for the document.
string[] header = new string[] { "Description", "QTY", "Price", "Amount" };
string[] product = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
int[] qty = new int[] { 12, 15, 25, 10 };
double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 };
// 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];
cell.ApplyFormatting(headerRowFormatting);
}
}
}
#region #SharedFormulas
// Create data rows.
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()) {
// Use the shared formula to calculate the amount per product.
if (i == 0)
cell.SetSharedFormula("B2*C2", XlCellRange.FromLTRB(3, 1, 3, 4));
else
cell.SetSharedFormula(new XlCellPosition(3, 1));
}
}
}
#endregion #SharedFormulas
// Create the total row.
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(2);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total:";
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount.
cell.SetFormula("SUM(D2:D5)");
cell.ApplyFormatting(totalRowFormatting);
}
}
}
}
}
static void Subtotals(Stream stream, XlDocumentFormat documentFormat) {
// Declare a variable that indicates the start of the data rows to calculate grand totals.
int startDataRowForGrandTotal;
// 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 column "A" and set its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 200;
}
// Create five successive columns and set the specific number format for their cells.
for (int i = 0; i < 5; 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 = XlFont.BodyFont();
rowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, 0.0));
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
// Specify formatting settings for the total row.
XlCellFormatting totalRowFormatting = new XlCellFormatting();
totalRowFormatting.Font = XlFont.BodyFont();
totalRowFormatting.Font.Bold = true;
totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0));
// Generate data for the document.
Random random = new Random();
string[] productsDairy = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
string[] productsCereals = new string[] { "Gnocchi di nonna Alice", "Gustaf's Knäckebröd", "Ravioli Angelo", "Singaporean Hokkien Fried Mee" };
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
startDataRowForGrandTotal = row.RowIndex + 1;
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Product";
cell.ApplyFormatting(headerRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
}
for (int i = 0; i < 4; i++) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Yearly total";
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
// Create data rows for Dairy products.
for (int i = 0; i < 4; i++) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = productsDairy[i];
cell.ApplyFormatting(rowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
for (int j = 0; j < 4; j++) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
using (IXlCell cell = row.CreateCell()) {
// Use the SUM function to calculate annual sales for each product.
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(1, row.RowIndex, 4, row.RowIndex)));
cell.ApplyFormatting(rowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
// Create the total row for Dairies.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Subtotal";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
for (int j = 0; j < 5; j++) {
using (IXlCell cell = row.CreateCell()) {
// Use the SUBTOTAL function to calculate total sales for each quarter and the entire year.
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, row.RowIndex - 4, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
}
// Create data rows for Cereals.
for (int i = 0; i < 4; i++) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = productsCereals[i];
cell.ApplyFormatting(rowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
for (int j = 0; j < 4; j++) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
using (IXlCell cell = row.CreateCell()) {
// Use the SUM function to calculate annual sales for each product.
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(1, row.RowIndex, 4, row.RowIndex)));
cell.ApplyFormatting(rowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
// Create the total row for Cereals.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Subtotal";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
for (int j = 0; j < 5; j++) {
using (IXlCell cell = row.CreateCell()) {
// Use the SUBTOTAL function to calculate total sales for each quarter and the entire year.
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, row.RowIndex - 4, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
}
#region #SubtotalFunction
// Create the grand total row.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Grand Total";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
for (int j = 0; j < 5; j++) {
using (IXlCell cell = row.CreateCell()) {
// Use the SUBTOTAL function to calculate grand total sales for each quarter and the entire year.
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, startDataRowForGrandTotal, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
}
#endregion #SubtotalFunction
}
}
}
}
}
XLExportExamples/SpreadsheetActions/GeneralActions.cs(vb)
C#using System;
using System.Globalization;
using System.IO;
using DevExpress.Export.Xl;
using DevExpress.Office.Crypto;
namespace XLExportExamples
{
public static class GeneralActions {
#region Actions
public static Action<Stream, XlDocumentFormat> CreateDocumentAction = CreateDocument;
public static Action<Stream, XlDocumentFormat> EncryptDocumentAction = EncryptDocument;
public static Action<Stream, XlDocumentFormat> CreateSheetAction = CreateSheet;
public static Action<Stream, XlDocumentFormat> CreateHiddenSheetAction = CreateHiddenSheet;
public static Action<Stream, XlDocumentFormat> HideGridlinesAction = HideGridlines;
public static Action<Stream, XlDocumentFormat> HideHeadersAction = HideHeaders;
public static Action<Stream, XlDocumentFormat> CreateColumnsAction = CreateColumns;
public static Action<Stream, XlDocumentFormat> CreateRowsAction = CreateRows;
public static Action<Stream, XlDocumentFormat> CreateCellsAction = CreateCells;
public static Action<Stream, XlDocumentFormat> MergeCellsAction = MergeCells;
#endregion
static void CreateDocument(Stream stream, XlDocumentFormat documentFormat) {
#region #CreateDocument
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document and write it to the specified stream.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
}
#endregion #CreateDocument
}
static void EncryptDocument(Stream stream, XlDocumentFormat documentFormat)
{
#region #EncryptDocument
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Specify encryption options.
// A workbook will be encrypted using the default encryption mechanism
// (agile encryption for XLSX files, and RC4 encryption for XLS files).
EncryptionOptions encryptionOptions = new EncryptionOptions();
// Specify the encryption password.
encryptionOptions.Password = "password";
// Create a new document and encrypt its contents.
using (IXlDocument document = exporter.CreateDocument(stream, encryptionOptions))
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
}
#endregion #EncryptDocument
}
static void CreateSheet(Stream stream, XlDocumentFormat documentFormat) {
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
#region #CreateSheet
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a new worksheet under the specified name.
using (IXlSheet sheet = document.CreateSheet()) {
sheet.Name = "Sales report";
}
}
#endregion #CreateSheet
}
static void CreateHiddenSheet(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 #CreateHiddenSheet
// Create the first worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
sheet.Name = "Sales report";
}
// Create the second worksheet and specify its visibility.
using (IXlSheet sheet = document.CreateSheet()) {
sheet.Name = "Sales data";
sheet.VisibleState = XlSheetVisibleState.Hidden;
}
#endregion #CreateHiddenSheet
}
}
static void HideHeaders(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 #HideHeaders
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Hide row and column headers in the worksheet.
sheet.ViewOptions.ShowRowColumnHeaders = false;
}
#endregion #HideHeaders
}
}
static void HideGridlines(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 #HideGridlines
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Hide gridlines on the worksheet.
sheet.ViewOptions.ShowGridLines = false;
}
#endregion #HideGridlines
}
}
static void CreateColumns(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 #CreateColumns
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create the column A and set its width to 100 pixels.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
// Hide the column B in the worksheet.
using (IXlColumn column = sheet.CreateColumn()) {
column.IsHidden = true;
}
// Create the column D and set its width to 24.5 characters.
using (IXlColumn column = sheet.CreateColumn(3)) {
column.WidthInCharacters = 24.5f;
}
}
#endregion #CreateColumns
}
}
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 CreateCells(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 #CreateCells
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create the column A and set its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 150;
}
// Create the first row.
using (IXlRow row = sheet.CreateRow()) {
// Create the cell A1 and set its value.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Numeric value:";
}
// Create the cell B1 and assign the numeric value to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = 123.45;
}
}
// Create the second row.
using (IXlRow row = sheet.CreateRow()) {
// Create the cell A2 and set its value.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Text value:";
}
// Create the cell B2 and assign the text value to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "abc";
}
}
// Create the third row.
using (IXlRow row = sheet.CreateRow()) {
// Create the cell A3 and set its value.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Boolean value:";
}
// Create the cell B3 and assign the boolean value to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = true;
}
}
// Create the fourth row.
using (IXlRow row = sheet.CreateRow()) {
// Create the cell A4 and set its value.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Error value:";
}
// Create the cell B4 and assign an error value to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.ErrorName;
}
}
}
#endregion #CreateCells
}
}
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
}
}
}
}
}
XLExportExamples/SpreadsheetActions/MiscellaneousActions.cs(vb)
C#using System;
using System.Globalization;
using System.IO;
using System.Text;
using DevExpress.Export.Xl;
using DevExpress.XtraExport.Csv;
namespace XLExportExamples
{
public static class MiscellaneousActions {
#region Actions
public static Action<Stream, XlDocumentFormat> HyperlinksAction = Hyperlinks;
public static Action<Stream, XlDocumentFormat> DocumentPropertiesAction = DocumentProperties;
public static Action<Stream, XlDocumentFormat> DocumentOptionsAction = DocumentOptions;
public static Action<Stream, XlDocumentFormat> CsvExportOptionsAction = CsvExportOptions;
#endregion
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 DocumentProperties(Stream stream, XlDocumentFormat documentFormat) {
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
#region #DocumentProperties
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Set the built-in document properties.
document.Properties.Title = "XL Export API: document properties example";
document.Properties.Subject = "XL Export API";
document.Properties.Keywords = "XL Export, document generation";
document.Properties.Description = "How to set document properties using the XL Export API";
document.Properties.Category = "Spreadsheet";
document.Properties.Company = "DevExpress Inc.";
// Set the custom document properties.
document.Properties.Custom["Product Suite"] = "XL Export Library";
document.Properties.Custom["Revision"] = 5;
document.Properties.Custom["Date Completed"] = DateTime.Now;
document.Properties.Custom["Published"] = true;
// Generate data for the document.
using(IXlSheet sheet = document.CreateSheet()) {
sheet.SkipRows(1);
using(IXlRow row = sheet.CreateRow()) {
row.SkipCells(1);
using(IXlCell cell = row.CreateCell()) {
cell.Value = "You can view document properties using the File->Info->Properties->Advanced Properties dialog.";
}
}
}
}
#endregion #DocumentProperties
}
static void DocumentOptions(Stream stream, XlDocumentFormat documentFormat) {
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
#region #DocumentOptions
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using(IXlSheet sheet = document.CreateSheet()) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 200;
}
using(IXlColumn column = sheet.CreateColumn()) {
column.Formatting = XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Bottom);
}
// Display the file format to which the document is exported.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Document format:";
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = document.Options.DocumentFormat.ToString().ToUpper();
}
}
// Display the maximum number of columns allowed by the output file format.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Maximum number of columns:";
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = document.Options.MaxColumnCount;
}
}
// Display the maximum number of rows allowed by the output file format.
using (IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Maximum number of rows:";
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = document.Options.MaxRowCount;
}
}
// Display whether the document can contain multiple worksheets.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Supports document parts:";
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = document.Options.SupportsDocumentParts;
}
}
// Display whether the document can contain formulas.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Supports formulas:";
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = document.Options.SupportsFormulas;
}
}
// Display whether the document supports grouping functionality.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Supports outline/grouping:";
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = document.Options.SupportsOutlineGrouping;
}
}
}
}
#endregion #DocumentOptions
}
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
}
}
}
XLExportExamples/SpreadsheetActions/PageViewAndLayoutActions.cs(vb)
C#using System;
using System.Globalization;
using System.IO;
using DevExpress.Export.Xl;
namespace XLExportExamples
{
public static class PageViewAndLayoutActions {
#region Actions
public static Action<Stream, XlDocumentFormat> FreezeRowAction = FreezeRow;
public static Action<Stream, XlDocumentFormat> FreezeColumnAction = FreezeColumn;
public static Action<Stream, XlDocumentFormat> FreezePanesAction = FreezePanes;
public static Action<Stream, XlDocumentFormat> SheetViewRTLAction = SheetViewRTL;
public static Action<Stream, XlDocumentFormat> HeadersFootersAction = HeadersFooters;
public static Action<Stream, XlDocumentFormat> PageBreaksAction = PageBreaks;
public static Action<Stream, XlDocumentFormat> PageMarginsAction = PageMargins;
public static Action<Stream, XlDocumentFormat> PageSetupAction = PageSetup;
public static Action<Stream, XlDocumentFormat> PrintAreaAction = PrintArea;
public static Action<Stream, XlDocumentFormat> PrintOptionsAction = PrintOptions;
public static Action<Stream, XlDocumentFormat> PrintTitlesAction = PrintTitles;
#endregion
static void FreezeRow(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 #FreezeRow
// Freeze the first row in the worksheet.
sheet.SplitPosition = new XlCellPosition(0, 1);
#endregion #FreezeRow
// 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);
}
}
}
}
}
}
}
static void FreezeColumn(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 #FreezeColumn
// Freeze the first column in the worksheet.
sheet.SplitPosition = new XlCellPosition(1, 0);
#endregion #FreezeColumn
// 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);
}
}
}
}
}
}
}
static void FreezePanes(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 #FreezePanes
// Freeze the first column and the first row.
sheet.SplitPosition = new XlCellPosition(1, 1);
#endregion #FreezePanes
// 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);
}
}
}
}
}
}
}
static void SheetViewRTL(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 #RightToLeftSheetView
// Display the worksheet from right to left.
sheet.ViewOptions.RightToLeft = true;
#endregion #RightToLeftSheetView
// Freeze the first column and the first row.
sheet.SplitPosition = new XlCellPosition(1, 1);
// 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);
}
}
}
}
}
}
}
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);
}
}
}
}
}
}
}
static void PageBreaks(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 #PageBreaks
// Insert a page break after the column "B".
sheet.ColumnPageBreaks.Add(2);
// Insert a page break after the tenth row.
sheet.RowPageBreaks.Add(10);
#endregion #PageBreaks
// Generate data for the document.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 250;
}
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);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Sales";
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);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
}
}
}
}
static void PageMargins(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 #PageMargins
sheet.PageMargins = new XlPageMargins();
// Set the unit of margin measurement.
sheet.PageMargins.PageUnits = XlPageUnits.Centimeters;
// Specify page margins.
sheet.PageMargins.Left = 2.0;
sheet.PageMargins.Right = 1.0;
sheet.PageMargins.Top = 1.25;
sheet.PageMargins.Bottom = 1.25;
// Specify header and footer margins.
sheet.PageMargins.Header = 0.7;
sheet.PageMargins.Footer = 0.7;
#endregion #PageMargins
// 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 margin settings.";
}
}
}
}
}
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 = DevExpress.Drawing.Printing.DXPaperKind.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.";
}
}
}
}
}
static void PrintArea(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 #PrintArea
// Set the print area to cells A1:E5.
sheet.PrintArea = XlCellRange.FromLTRB(0, 0, 4, 4);
#endregion #PrintArea
// Create worksheet columns and set their widths.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 110;
column.Formatting = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
}
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 190;
}
for(int i = 0; i < 2; i++) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 90;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 130;
}
sheet.SkipColumns(1);
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 130;
}
// 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;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Generate the header row.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Employee ID";
cell.ApplyFormatting(headerRowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Employee name";
cell.ApplyFormatting(headerRowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Salary";
cell.ApplyFormatting(headerRowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Bonus";
cell.ApplyFormatting(headerRowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Department";
cell.ApplyFormatting(headerRowFormatting);
}
row.SkipCells(1);
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Departments";
cell.ApplyFormatting(headerRowFormatting);
}
}
// Generate data for the document.
int[] id = new int[] { 10115, 10709, 10401, 10204 };
string[] name = new string[] { "Augusta Delono", "Chris Cadwell", "Frank Diamond", "Simon Newman" };
int[] salary = new int[] { 1100, 2000, 1750, 1250 };
int[] bonus = new int[] { 50, 180, 100, 80 };
int[] deptid = new int[] { 0, 2, 3, 3 };
string[] department = new string[] { "Accounting", "IT", "Management", "Manufacturing" };
for(int i = 0; i < 4; i++) {
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = id[i];
cell.ApplyFormatting(rowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = name[i];
cell.ApplyFormatting(rowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = salary[i];
cell.ApplyFormatting(rowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = bonus[i];
cell.ApplyFormatting(rowFormatting);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = department[deptid[i]];
cell.ApplyFormatting(rowFormatting);
}
row.SkipCells(1);
using(IXlCell cell = row.CreateCell()) {
cell.Value = department[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Restrict data entry in the cell range E2:E5 to values in a drop-down list obtained from the cells G2:G5.
XlDataValidation validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(4, 1, 4, 4));
validation.Type = XlDataValidationType.List;
validation.Criteria1 = XlCellRange.FromLTRB(6, 1, 6, 4).AsAbsolute();
sheet.DataValidations.Add(validation);
}
}
}
static void PrintOptions(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 #PrintOptions
// Specify print options for the worksheet.
sheet.PrintOptions = new XlPrintOptions();
// Print row and column headings.
sheet.PrintOptions.Headings = true;
// Print gridlines.
sheet.PrintOptions.GridLines = true;
// Center worksheet data on a printed page.
sheet.PrintOptions.HorizontalCentered = true;
sheet.PrintOptions.VerticalCentered = true;
#endregion #PrintOptions
// Generate data for the document.
// Create worksheet columns and set their widths.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 250;
}
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);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Sales";
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);
}
using(IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
}
}
}
}
static void PrintTitles(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 #PrintTitles
// Print the first column and the first row on every page.
sheet.PrintTitles.SetColumns(0, 0);
sheet.PrintTitles.SetRows(0, 0);
#endregion #PrintTitles
// Generate data for the document.
// Create worksheet columns and set their widths.
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);
}
}
}
}
}
}
}
}
}
XLExportExamples/SpreadsheetActions/PictureActions.cs(vb)
C#using System;
using System.Drawing;
using System.Drawing.Imaging;
using System.Globalization;
using System.IO;
using DevExpress.Export.Xl;
namespace XLExportExamples
{
public static class PictureActions
{
static string imagesPath = Path.Combine(System.Windows.Forms.Application.StartupPath, "Images");
#region Actions
public static Action<Stream, XlDocumentFormat> InsertPictureAction = InsertPicture;
public static Action<Stream, XlDocumentFormat> StretchPictureAction = StretchPicture;
public static Action<Stream, XlDocumentFormat> FitPictureAction = FitPicture;
public static Action<Stream, XlDocumentFormat> PictureHyperlinkClickAction = PictureHyperlinkClick;
#endregion
static void InsertPicture(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 #InsertPicture
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Insert a picture from a file and anchor it to cells.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.SetImage(Image.FromFile(Path.Combine(imagesPath, "image1.jpg")), ImageFormat.Jpeg);
picture.SetTwoCellAnchor(new XlAnchorPoint(1, 1, 0, 0), new XlAnchorPoint(6, 11, 2, 15), XlAnchorType.TwoCell);
}
}
#endregion #InsertPicture
}
}
static void StretchPicture(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())
{
sheet.SkipColumns(1);
// Create the column "B" and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 205;
}
sheet.SkipRows(1);
// Create the second row and set its height.
using (IXlRow row = sheet.CreateRow())
{
row.HeightInPixels = 154;
}
#region #StretchPicture
// Insert a picture from a file and stretch it to fill the cell B2.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.SetImage(Image.FromFile(Path.Combine(imagesPath, "image1.jpg")), ImageFormat.Jpeg);
picture.StretchToCell(new XlCellPosition(1, 1));
}
}
#endregion #StretchPicture
}
}
static void FitPicture(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())
{
sheet.SkipColumns(1);
// Create the column "B" and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 300;
}
sheet.SkipRows(1);
// Create the second row and set its height.
using (IXlRow row = sheet.CreateRow())
{
row.HeightInPixels = 154;
}
#region #FitPicture
// Insert a picture from a file to fit in the cell B2.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.SetImage(Image.FromFile(Path.Combine(imagesPath, "image1.jpg")), ImageFormat.Jpeg);
picture.FitToCell(new XlCellPosition(1, 1), 300, 154, true);
}
}
#endregion #FitPicture
}
}
static void PictureHyperlinkClick(Stream stream, XlDocumentFormat documentFormat)
{
#region #HyperlinkClick
// 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())
{
// Load a picture from a file and add a hyperlink to it.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.SetImage(Image.FromFile(Path.Combine(imagesPath, "DevExpress.png")), ImageFormat.Png);
picture.HyperlinkClick.TargetUri = "http://www.devexpress.com";
picture.HyperlinkClick.Tooltip = "Developer Express Inc.";
picture.SetTwoCellAnchor(new XlAnchorPoint(1, 1, 0, 0), new XlAnchorPoint(10, 5, 2, 15), XlAnchorType.TwoCell);
}
}
}
#endregion #HyperlinkClick
}
}
}
XLExportExamples/SpreadsheetActions/SparklineActions.cs(vb)
C#using System;
using System.Globalization;
using System.IO;
using DevExpress.Export.Xl;
namespace XLExportExamples
{
class SparklineActions
{
#region Actions
public static Action<Stream, XlDocumentFormat> AddSparklineGroupAction = AddSparklineGroup;
public static Action<Stream, XlDocumentFormat> AddSparklineAction = AddSparkline;
public static Action<Stream, XlDocumentFormat> AdjustScalingAction = AdjustScaling;
public static Action<Stream, XlDocumentFormat> HighlightValuesAction = HighlightValues;
public static Action<Stream, XlDocumentFormat> DisplayXAxisAction = DisplayXAxis;
public static Action<Stream, XlDocumentFormat> SetDateRangeAction = SetDateRange;
#endregion
static void AddSparklineGroup(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 and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)");
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4" };
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(columnNames, headerRowFormatting);
}
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "HD Video Player", "SuperLED 42", "SuperLED 50", "DesktopLED 19", "DesktopLED 21", "Projector Plus HD" };
foreach (string product in products)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = product;
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);
}
}
}
}
#region #AddSparklineGroup
// Create a group of line sparklines.
XlSparklineGroup group = new XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 4, 6), XlCellRange.FromLTRB(5, 1, 5, 6));
// Set the sparkline weight.
group.LineWeight = 1.25;
// Display data markers on the sparklines.
group.DisplayMarkers = true;
sheet.SparklineGroups.Add(group);
#endregion #AddSparklineGroup
}
}
}
static void AddSparkline(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 and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)");
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4" };
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(columnNames, headerRowFormatting);
}
// Create a group of line sparklines.
XlSparklineGroup group = new XlSparklineGroup();
// Set the sparkline color.
group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, -0.2);
// Set the sparkline weight.
group.LineWeight = 1.25;
sheet.SparklineGroups.Add(group);
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "HD Video Player", "SuperLED 42", "SuperLED 50", "DesktopLED 19", "DesktopLED 21", "Projector Plus HD" };
foreach (string product in products)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = product;
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);
}
}
#region #AddSparkline
// Add one more sparkline to the existing group.
int rowIndex = row.RowIndex;
group.Sparklines.Add(new XlSparkline(XlCellRange.FromLTRB(1, rowIndex, 4, rowIndex), XlCellRange.FromLTRB(5, rowIndex, 5, rowIndex)));
#endregion #AddSparkline
}
}
}
}
}
static void AdjustScaling(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 and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)");
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4" };
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(columnNames, headerRowFormatting);
}
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "HD Video Player", "SuperLED 42", "SuperLED 50", "DesktopLED 19", "DesktopLED 21", "Projector Plus HD" };
foreach (string product in products)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = product;
cell.ApplyFormatting(rowFormatting);
}
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = Math.Round(random.NextDouble() * 2000 + 1500);
cell.ApplyFormatting(rowFormatting);
}
}
}
}
#region #AdjustScaling
// Create a sparkline group.
XlSparklineGroup group = new XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 4, 6), XlCellRange.FromLTRB(5, 1, 5, 6));
// Set the sparkline color.
group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, 0.0);
// Change the sparkline group type to "Column".
group.SparklineType = XlSparklineType.Column;
// Set the custom minimum value for the vertical axis.
group.MinScaling = XlSparklineAxisScaling.Custom;
group.ManualMin = 1000.0;
// Set the automatic maximum value for all sparklines in the group.
group.MaxScaling = XlSparklineAxisScaling.Group;
sheet.SparklineGroups.Add(group);
#endregion #AdjustScaling
}
}
}
static void HighlightValues(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 and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
for (int i = 0; i < 9; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)");
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = rowFormatting.Clone();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
string[] columnNames = new string[] { "State", "Q1'13", "Q2'13", "Q3'13", "Q4'13", "Q1'14", "Q2'14", "Q3'14", "Q4'14" };
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(columnNames, headerRowFormatting);
}
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "Alabama", "Arizona", "California", "Colorado", "Connecticut", "Florida" };
foreach (string product in products)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = product;
cell.ApplyFormatting(rowFormatting);
}
for (int j = 0; j < 8; j++)
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = Math.Round((random.NextDouble() + 0.5) * 2000 * Math.Sign(random.NextDouble() - 0.4));
cell.ApplyFormatting(rowFormatting);
}
}
}
}
#region #HighlightValues
// Create a sparkline group.
XlSparklineGroup group = new XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 8, 6), XlCellRange.FromLTRB(9, 1, 9, 6));
// Change the sparkline group type to "Column".
group.SparklineType = XlSparklineType.Column;
// Set the series color.
group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, 0.0);
// Set the color for negative points on sparklines.
group.ColorNegative = XlColor.FromTheme(XlThemeColor.Accent2, 0.0);
// Set the color for the highest points on sparklines.
group.ColorHigh = XlColor.FromTheme(XlThemeColor.Accent6, 0.0);
// Highlight the highest and negative points on each sparkline in the group.
group.HighlightNegative = true;
group.HighlightHighest = true;
sheet.SparklineGroups.Add(group);
#endregion #HighlightValues
}
}
}
static void DisplayXAxis(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 and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
for (int i = 0; i < 9; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)");
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = rowFormatting.Clone();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
string[] columnNames = new string[] { "State", "Q1'13", "Q2'13", "Q3'13", "Q4'13", "Q1'14", "Q2'14", "Q3'14", "Q4'14" };
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(columnNames, headerRowFormatting);
}
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "Alabama", "Arizona", "California", "Colorado", "Connecticut", "Florida" };
foreach (string product in products)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = product;
cell.ApplyFormatting(rowFormatting);
}
for (int j = 0; j < 8; j++)
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = Math.Round((random.NextDouble() + 0.5) * 2000 * Math.Sign(random.NextDouble() - 0.4));
cell.ApplyFormatting(rowFormatting);
}
}
}
}
#region #DisplayXAxis
// Create a sparkline group.
XlSparklineGroup group = new XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 8, 6), XlCellRange.FromLTRB(9, 1, 9, 6));
// Change the sparkline group type to "Column".
group.SparklineType = XlSparklineType.Column;
// Display the horizontal axis.
group.DisplayXAxis = true;
// Set the series color.
group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, 0.0);
// Highlight negative points on each sparkline in the group.
group.ColorNegative = XlColor.FromTheme(XlThemeColor.Accent2, 0.0);
group.HighlightNegative = true;
sheet.SparklineGroups.Add(group);
#endregion #DisplayXAxis
}
}
}
static void SetDateRange(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 and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)");
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
headerRowFormatting.NumberFormat = XlNumberFormat.ShortDate;
object[] headerValues = new object[] { "Product", new DateTime(2015, 3, 15), new DateTime(2015, 4, 1), new DateTime(2015, 6, 1), new DateTime(2015, 10, 1), "Date Axis", "General Axis" };
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(headerValues, headerRowFormatting);
}
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "HD Video Player", "SuperLED 42", "SuperLED 50", "DesktopLED 19", "DesktopLED 21", "Projector Plus HD" };
foreach (string product in products)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = product;
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);
}
}
}
}
#region #SetDateRange
// Create a group of line sparklines.
XlSparklineGroup group = new XlSparklineGroup(XlCellRange.Parse("B2:E7"), XlCellRange.Parse("F2:F7"));
// Specify the date range for the sparkline group.
group.DateRange = XlCellRange.Parse("B1:E1");
// Set the sparkline weight.
group.LineWeight = 1.25;
// Display data markers on the sparklines.
group.DisplayMarkers = true;
sheet.SparklineGroups.Add(group);
#endregion #SetDateRange
// Create another group of line sparklines with defaul general axis type.
XlSparklineGroup group1 = new XlSparklineGroup(XlCellRange.Parse("B2:E7"), XlCellRange.Parse("G2:G7"));
group1.LineWeight = 1.25;
group1.DisplayMarkers = true;
sheet.SparklineGroups.Add(group1);
}
}
}
}
}
XLExportExamples/SpreadsheetActions/TableActions.cs(vb)
C#using System;
using DevExpress.Export.Xl;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
namespace XLExportExamples
{
public static class TableActions
{
#region Actions
public static Action<Stream, XlDocumentFormat> AddTableAction = AddTable;
public static Action<Stream, XlDocumentFormat> DisableFilteringAction = DisableFiltering;
public static Action<Stream, XlDocumentFormat> HiddenHeaderRowAction = HiddenHeaderRow;
public static Action<Stream, XlDocumentFormat> HiddenTotalRowAction = HiddenTotalRow;
public static Action<Stream, XlDocumentFormat> SideBySideAction = SideBySide;
public static Action<Stream, XlDocumentFormat> TableStyleAction = TableStyle;
public static Action<Stream, XlDocumentFormat> TableStyleOptionsAction = TableStyleOptions;
public static Action<Stream, XlDocumentFormat> CustomFormattingAction = CustomFormatting;
public static Action<Stream, XlDocumentFormat> CalculatedColumnAction = CalculatedColumn;
#endregion
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
}
}
}
static void DisableFiltering(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 #DisableFiltering
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);
// Disable the filtering functionality for the table.
table.HasAutoFilter = false;
// 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(table, true);
#endregion #DisableFiltering
}
}
}
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
}
}
}
static void HiddenTotalRow(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 #HiddenTotalRow
IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Category", "Amount" };
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
}
// 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 #HiddenTotalRow
}
}
}
static void SideBySide(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:G) and set their widths.
int[] widths = new int[] { 165, 120, 100, 20, 100, 120, 100 };
for (int i = 0; i < 7; i++)
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = widths[i];
#region #SideBySideTables
// Specify two arrays containing column headings for the tables.
string[] columnNames1 = new string[] { "Product", "Category", "Amount" };
string[] columnNames2 = new string[] { "Region", "Category", "Amount" };
// Create the first row in the worksheet from which the tables start.
using (IXlRow row = sheet.CreateRow())
{
// Start generating the first table with a header row displayed.
IXlTable table = row.BeginTable(columnNames1, 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;
row.SkipCells(1);
// Start generating the second table with a header row displayed.
table = row.BeginTable(columnNames2, 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.
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);
row.SkipCells(1);
row.BulkCells(new object[] { "East", "Dairy Products", 16000 }, null);
}
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
row.SkipCells(1);
row.BulkCells(new object[] { "East", "Grains/Cereals", 14500 }, null);
}
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15500 }, null);
row.SkipCells(1);
row.BulkCells(new object[] { "West", "Dairy Products", 16500 }, null);
}
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
row.SkipCells(1);
row.BulkCells(new object[] { "West", "Grains/Cereals", 13500 }, null);
}
// Create the total rows and finish the tables.
using (IXlRow row = sheet.CreateRow())
{
foreach (IXlTable table in sheet.Tables)
row.EndTable(table, true);
}
#endregion #SideBySideTables
}
}
}
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
}
}
}
static void TableStyleOptions(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 #TableStyleOptions
IXlTable table;
// Specify an array containing column headings for tables.
string[] columnNames = new string[] { "Product", "Category", "Amount" };
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Disable banded rows" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Disable banded row formatting for the table.
table.Style.ShowRowStripes = false;
}
// 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);
}
sheet.SkipRows(1);
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Enable banded columns" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Apply banded column formatting to the table.
table.Style.ShowRowStripes = false;
table.Style.ShowColumnStripes = true;
}
// 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);
}
sheet.SkipRows(1);
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Highlight first column" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Display special formatting for the first column of the table.
table.Style.ShowFirstColumn = true;
}
// 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);
}
sheet.SkipRows(1);
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Highlight last column" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Display special formatting for the last column of the table.
table.Style.ShowLastColumn = true;
}
// 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 #TableStyleOptions
}
}
}
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
}
}
}
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
}
}
}
}
}