void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Skip one row before starting to generate the total row.
sheet.SkipRows(1);
// Create the total row.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
row.HeightInPixels = 28;
// Set font characteristics for the row cells.
row.ApplyFormatting(infoFont.Clone());
row.Formatting.Font.Bold = true;
// Skip six successive cells in the total row.
row.SkipCells(6);
// Create the "Total" cell.
using (IXlCell cell = row.CreateCell())
cell.Value = "TOTAL";
// Create the cell that displays the total amount.
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount.
cell.SetFormula(string.Format("SUM(H{0}:H{1})", firstDataRowIndex + 1, row.RowIndex - 1));
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
// Create the empty cell.
using (IXlCell cell = row.CreateCell())
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
}
void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Employees";
// Specify print settings for the worksheet.
SetupPageParameters(sheet);
// Generate worksheet columns.
GenerateColumns(sheet);
// Add the title to the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
GenerateTitle(sheet);
}
// Create the header row.
GenerateHeaderRow(sheet);
int firstDataRowIndex = sheet.CurrentRowIndex;
// Create the data rows.
for (int i = 0; i < employees.Count; i++)
{
GenerateDataRow(sheet, employees[i], (i + 1) == employees.Count);
}
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
// Create data validation criteria for the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
GenerateDataValidations(sheet, firstDataRowIndex);
}
}
// Create the hidden worksheet containing source data for the data validation drop-down list.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
using (IXlSheet sheet = document.CreateSheet()) {
sheet.Name = "Departments";
sheet.VisibleState = XlSheetVisibleState.Hidden;
foreach (string department in departments)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell())
cell.Value = department;
}
}
}
}
}
void GenerateHeaderRow(IXlSheet sheet)
{
// Create an array that contains column labels for the header row.
string[] columnNames = new string[] { "Description", null, "QTY", "Unit Price", "Discount", "Amount" };
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
row.HeightInPixels = 28;
// Skip the first cell in the row.
row.SkipCells(1);
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, headerRowFormatting);
// Create cells that display column labels and apply specific formatting settings to them.
foreach (string columnName in columnNames)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = columnName;
cell.ApplyFormatting(headerRowFormatting);
}
}
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, headerRowFormatting);
}
}
void GenerateHeaderRow(IXlSheet sheet, string nameOfState)
{
// Create the header row for sales data in the specific state.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the cell that displays the state name and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = nameOfState;
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0)));
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.General, XlVerticalAlignment.Bottom));
}
// Create four successive cells with values "Q1", "Q2", "Q3" and "Q4".
// Apply specific formatting settings to the created cells.
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
}
}
// Create the "Yearly total" cell and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Yearly total";
cell.ApplyFormatting(headerRowFormatting);
}
}
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Create the total row for each inner group of sales in the specific state.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the "Total" cell and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
// Create four successive cells displaying total sales for each quarter individually. Use the SUBTOTAL function to add quarterly sales.
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 2, firstDataRowIndex, j + 2, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
// Create the cell that displays yearly sales for the state. Use the SUBTOTAL function to add yearly sales in the current state for each product.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(6, firstDataRowIndex, 6, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.1)));
}
}
}
void GenerateTitle(IXlSheet sheet)
{
// Specify formatting settings for the document title.
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Name = "Calibri Light";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 24;
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Add the document title.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "SALES 2014";
cell.Formatting = formatting;
}
// Create four empty cells with the title formatting applied.
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell())
cell.Formatting = formatting;
}
}
// Skip one row before starting to generate data rows.
sheet.SkipRows(1);
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Create the total row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Create the first cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the second cell in the total row and assign the SUBTOTAL function to it to calculate the average of the subtotal of the cells located in the "Sales" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, row.RowIndex - 1), XlSummary.Average, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Avg=""#,##0,,""M""");
}
// Create the third cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the fourth cell in the total row and assign the SUBTOTAL function to it to calculate the sum of the subtotal of the cells located in the "Profit" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Sum=""#,##0,,""M""");
}
}
}
void GenerateBillToRow(IXlSheet sheet, string name1, object value1, string name2, object value2, XlBorder specificBorder)
{
using (IXlRow row = sheet.CreateRow()) {
// Set the cell font.
row.ApplyFormatting(infoFont);
// Skip the first cell in the row.
row.SkipCells(1);
// Create the empty cell with the specified formatting settings.
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name1;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value1);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name2;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value2);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create three successive cells, apply specific formatting settings to them and set the cell borders.
for (int i = 0; i < 3; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
cell.ApplyFormatting(specificBorder);
}
}
}
}
static void 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 ColorScale(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Generate data for the document.
for (int i = 0; i < 11; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.RowIndex + 1;
}
}
}
}
#region #ColorScaleRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (A1:A11 and C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the default three-color scale rule to differentiate low, medium and high values in cell ranges.
XlCondFmtRuleColorScale rule = new XlCondFmtRuleColorScale();
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (B1:B11 and D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the two-color scale rule to differentiate low and high values in cell ranges.
rule = new XlCondFmtRuleColorScale();
rule.ColorScaleType = XlCondFmtColorScaleType.ColorScale2;
// Set a color corresponding to the minimum value in the cell range.
rule.MinColor = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
// Set a color corresponding to the maximum value in the cell range.
rule.MaxColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.5);
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #ColorScaleRule
}
}
}
static void Hyperlinks(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #Hyperlinks
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 300;
}
// Create a hyperlink to a cell in the current workbook.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Local link";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "#Sheet1!C5";
sheet.Hyperlinks.Add(hyperlink);
}
}
// Create a hyperlink to a cell located in the external workbook.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "External file link";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "linked.xlsx#Sheet1!C5";
sheet.Hyperlinks.Add(hyperlink);
}
}
// Create a hyperlink to a web page.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "External URI";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "http://www.devexpress.com";
sheet.Hyperlinks.Add(hyperlink);
}
}
}
#endregion #Hyperlinks
}
}
static void MergeCells(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create the first row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells A1 to E1";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
}
// Create the second row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells A2 to A5";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
// Wrap the text within the cell.
cell.Formatting.Alignment.WrapText = true;
}
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells B2 to E5";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
}
#region #MergeCells
// Merge cells contained in the range A1:E1.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 0, 4, 0));
// Merge cells contained in the range A2:A5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 1, 0, 4));
// Merge cells contained in the range B2:E5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(1, 1, 4, 4));
#endregion #MergeCells
}
}
}
static void TimePeriod(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Generate data for the document.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
column.ApplyFormatting(XlNumberFormat.ShortDate);
}
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = DateTime.Now.AddDays(row.RowIndex - 5);
}
}
}
#region #TimePeriodRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight yesterday's dates in the cell range.
XlCondFmtRuleTimePeriod rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Yesterday;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight today's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Today;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Create the rule to highlight tomorrows's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Tomorrow;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #TimePeriodRule
}
}
}
void GenerateTitleRow(IXlSheet sheet, string info, string name, object value, int rowHeight, XlFont font, XlNumberFormat specificFormat)
{
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = rowHeight;
// Set the cell font.
row.ApplyFormatting(font);
// Create the first empty cell.
row.SkipCells(1);
// Create the blank cell with the specified formatting settings.
row.BlankCells(1, leftPanelFormatting);
// Create the third cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = info;
cell.ApplyFormatting(leftPanelFormatting);
}
// Create two blank cells with the specified formatting settings.
row.BlankCells(2, leftPanelFormatting);
// Create the cell, apply specific formatting settings to it and set the cell right border.
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(leftPanelFormatting);
cell.ApplyFormatting(leftPanelBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name;
cell.ApplyFormatting(rightPanelFormatting);
cell.Formatting.Alignment.Indent = 1;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value);
cell.ApplyFormatting(rightPanelFormatting);
if (specificFormat != null)
{
cell.ApplyFormatting(specificFormat);
}
}
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, rightPanelFormatting);
}
}
void GenerateDataRow(IXlSheet sheet, EmployeeData employee, bool isLastRow)
{
// Create the data row to display the employee's information.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 28;
// Specify formatting settings to be applied to the data rows to shade alternate rows.
XlCellFormatting formatting = new XlCellFormatting();
formatting.CopyFrom((row.RowIndex % 2 == 0) ? evenRowFormatting : oddRowFormatting);
// Set the bottom border for the last data row.
if (isLastRow)
{
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Create the cell containing the employee's ID.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Id;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the employee's name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Name;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the employee's salary.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Salary;
cell.ApplyFormatting(formatting);
}
// Create the cell containing information about bonuses.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Bonus;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the department name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Department;
cell.ApplyFormatting(formatting);
}
}
}
static void Blanks(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Generate data for the document.
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
if ((i % 2) == 0)
{
cell.Value = i + 1;
}
}
}
}
#region #BlanksRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight blank cells in the range.
XlCondFmtRuleBlanks rule = new XlCondFmtRuleBlanks(true);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight non-blank cells in the range.
rule = new XlCondFmtRuleBlanks(false);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #BlanksRule
}
}
}
void GenerateDataRow(IXlSheet sheet, SalesData data, bool isLastRow)
{
// Create the data row to display sales information for the specific state.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Specify formatting settings to be applied to the data rows to shade alternate rows.
XlCellFormatting formatting = new XlCellFormatting();
formatting.CopyFrom((row.RowIndex % 2 == 0) ? evenRowFormatting : oddRowFormatting);
// Set the bottom border for the last data row.
if (isLastRow)
{
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Create the cell containing the state name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.State;
cell.ApplyFormatting(formatting);
}
// Create the cell containing sales data.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales;
cell.ApplyFormatting(formatting);
}
// Create the cell that displays the difference between the actual and target sales.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales / data.TargetSales - 1;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the state profit.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Profit;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the percentage of a total market.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.MarketShare;
cell.ApplyFormatting(formatting);
}
}
}
void ExportCell(IXlRow row, int gridRowHandle, GridColumn gridColumn)
{
using (IXlCell cell = row.CreateCell()) {
// Set cell value
cell.Value = XlVariantValue.FromObject(this.view.GetRowCellValue(gridRowHandle, gridColumn));
// Get cell appearance
AppearanceObject appearance = GetCellAppearance(gridRowHandle, gridColumn);
// Apply alignment
XlCellAlignment alignment = new XlCellAlignment()
{
WrapText = appearance.TextOptions.WordWrap.HasFlag(WordWrap.Wrap),
VerticalAlignment = ConvertAlignment(appearance.TextOptions.VAlignment),
HorizontalAlignment = ConvertAlignment(appearance.TextOptions.HAlignment)
};
cell.ApplyFormatting(alignment);
// Apply borders
Color borderColor = appearance.GetBorderColor();
if (!DXColor.IsTransparentOrEmpty(borderColor))
{
cell.ApplyFormatting(XlBorder.OutlineBorders(borderColor));
}
// Apply fill
if (appearance.Options.UseBackColor)
{
cell.ApplyFormatting(XlFill.SolidFill(appearance.BackColor));
}
// Apply font
Font appearanceFont = appearance.Font;
XlFont font = XlFont.CustomFont(appearanceFont.Name);
font.Size = appearanceFont.SizeInPoints;
font.Bold = appearanceFont.Bold;
font.Italic = appearanceFont.Italic;
font.StrikeThrough = appearanceFont.Strikeout;
font.Underline = appearanceFont.Underline ? XlUnderlineType.Single : XlUnderlineType.None;
if (appearance.Options.UseForeColor)
{
font.Color = appearance.ForeColor;
}
cell.ApplyFormatting(font);
}
}
void GenerateHeaderRow(IXlSheet sheet)
{
string[] columnNames = new string[] { "Employee ID", "Employee Name", "Salary", "Bonus", "Department" };
// Create the header row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 28;
// Create required cells in the header row and apply specific formatting settings to them.
foreach (string columnName in columnNames)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = columnName;
cell.ApplyFormatting(headerRowFormatting);
}
}
}
}
void GenerateDataRow(IXlSheet sheet, SalesData data)
{
// Create the row to display sales information for each sale item.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first row in the cell.
row.SkipCells(1);
// Create the cell to display the product name and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Product;
cell.ApplyFormatting(dataRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
// Create the cell to display sales amount in the first quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q1;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the second quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q2;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the third quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q3;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the fourth quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q4;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display annual sales for the product. Use the SUM function to add product sales in each quarter.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(2, row.RowIndex, 5, row.RowIndex)));
cell.ApplyFormatting(dataRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
void GenerateInfoRow(IXlSheet sheet, string info)
{
// Skip one row before starting to generate the row with additional information.
sheet.SkipRows(1);
// Create the row.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the cell that contains the invoice payment options and set its font attributes.
using (IXlCell cell = row.CreateCell()) {
cell.Value = info;
cell.ApplyFormatting(infoFont);
}
}
}
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 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 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 PageSetup(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
#region #PageSetup
// Specify page settings for the worksheet.
sheet.PageSetup = new XlPageSetup();
// Select the paper size.
sheet.PageSetup.PaperKind = System.Drawing.Printing.PaperKind.A4;
// Set the page orientation to Landscape.
sheet.PageSetup.PageOrientation = XlPageOrientation.Landscape;
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
// Print in black and white.
sheet.PageSetup.BlackAndWhite = true;
// Specify the number of copies.
sheet.PageSetup.Copies = 2;
#endregion #PageSetup
// Generate data for the document.
sheet.SkipRows(1);
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(1);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Invoke the Page Setup dialog to control page settings.";
}
}
}
}
}
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 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
}
}
void GenerateTitle(IXlSheet sheet)
{
// Specify formatting settings for the document title.
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Name = "Calibri Light";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 24;
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.5);
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.5);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Add the document title.
using (IXlRow row = sheet.CreateRow()) {
// Skip the cell "A1".
row.SkipCells(1);
// Create the cell "B1" containing the document title.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "SALES ANALYSIS 2014";
cell.Formatting = formatting;
}
// Create five empty cells with the title formatting.
row.BlankCells(5, formatting);
}
// Skip one row before starting to generate data rows.
sheet.SkipRows(1);
// Insert a picture from a file and anchor it to the cell "G1".
string startupPath = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName);
using (IXlPicture picture = sheet.CreatePicture()) {
picture.Image = Image.FromFile(Path.Combine(startupPath, "Logo.png"));
picture.SetOneCellAnchor(new XlAnchorPoint(6, 0, 8, 4), 105, 30);
}
}
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 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.";
}
}
}
}
}
