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 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""");
}
}
}
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 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
}
}
}
void GenerateDataValidations(IXlSheet sheet, int firstDataRowIndex)
{
// Restrict data entry in the "Employee ID" column using criteria calculated by a worksheet formula (Employee ID must be a 5-digit number).
XlDataValidation validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(0, firstDataRowIndex, 0, sheet.CurrentRowIndex - 1));
validation.Type = XlDataValidationType.Custom;
validation.Criteria1 = string.Format("=AND(ISNUMBER(A{0}),LEN(A{0})=5)", firstDataRowIndex + 1);
validation.InputPrompt = "Please enter a 5-digit number.";
validation.PromptTitle = "Employee ID";
sheet.DataValidations.Add(validation);
// Restrict data entry in the "Salary" column to a whole number from 600 to 2000.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(2, firstDataRowIndex, 2, sheet.CurrentRowIndex - 1));
validation.Type = XlDataValidationType.Whole;
validation.Operator = XlDataValidationOperator.Between;
validation.Criteria1 = 600;
validation.Criteria2 = 2000;
// Specify the error message.
validation.ErrorMessage = "Salary must be greater than $600 and less than $2000.";
validation.ErrorTitle = "Warning";
validation.ErrorStyle = XlDataValidationErrorStyle.Warning;
// Specify the input message.
validation.InputPrompt = "Please enter a whole number in the range 600...2000.";
validation.PromptTitle = "Salary";
validation.ShowErrorMessage = true;
validation.ShowInputMessage = true;
sheet.DataValidations.Add(validation);
// Restrict data entry in the "Bonus" column 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, firstDataRowIndex, 3, sheet.CurrentRowIndex - 1));
validation.Type = XlDataValidationType.Whole;
validation.Operator = XlDataValidationOperator.Between;
validation.Criteria1 = 0;
validation.Criteria2 = string.Format("=C{0}*0.1", firstDataRowIndex + 1);
// Specify the error message.
validation.ErrorMessage = "Bonus cannot be greater than 10% of the salary.";
validation.ErrorTitle = "Information";
validation.ErrorStyle = XlDataValidationErrorStyle.Information;
validation.ShowErrorMessage = true;
sheet.DataValidations.Add(validation);
// Restrict data entry in the "Department" column to values in a drop-down list obtained from the cell range in the hidden "Departments" worksheet.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(4, firstDataRowIndex, 4, sheet.CurrentRowIndex - 1));
validation.Type = XlDataValidationType.List;
XlCellRange sourceRange = XlCellRange.FromLTRB(0, 0, 0, departments.Count - 1).AsAbsolute();
sourceRange.SheetName = "Departments";
validation.Criteria1 = sourceRange;
sheet.DataValidations.Add(validation);
}
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 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
}
}
}
MemoryStream CreateBIFF8DataStream()
{
IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xls);
MemoryStream dataStream = new MemoryStream();
using (IXlDocument document = exporter.CreateDocument(dataStream)) {
using (IXlSheet sheet = document.CreateSheet()) {
ExportColumns(sheet);
ExportRows(sheet);
this.sheetName = sheet.Name;
this.dataRange = sheet.DataRange;
}
}
dataStream.Position = 0;
return(dataStream);
}
void GenerateDataRow(IXlSheet sheet, SalesData data)
{
// Create the row to display sales information for each sale item.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first row in the cell.
row.SkipCells(1);
// Create the cell to display the product name and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Product;
cell.ApplyFormatting(dataRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
// Create the cell to display sales amount in the first quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q1;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the second quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q2;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the third quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q3;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the fourth quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q4;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display annual sales for the product. Use the SUM function to add product sales in each quarter.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(2, row.RowIndex, 5, row.RowIndex)));
cell.ApplyFormatting(dataRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
static void Duplicates(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Generate data for the document.
for (int i = 0; i < 11; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.ColumnIndex * cell.RowIndex + cell.RowIndex + 1;
}
}
}
}
#region #DuplicatesRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 10));
// Create the rule to identify duplicate values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleDuplicates()
{
Formatting = XlCellFormatting.Bad
});
// Create the rule to identify unique values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleUnique()
{
Formatting = XlCellFormatting.Good
});
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #DuplicatesRule
}
}
}
static void Top10(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Generate data for the document.
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.ColumnIndex * 4 + cell.RowIndex + 1;
}
}
}
}
#region #TopAndBottomRules
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:D10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 9));
// Create the rule to identify bottom 10 values in the cell range.
XlCondFmtRuleTop10 rule = new XlCondFmtRuleTop10();
rule.Bottom = true;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to identify top 10 values in the cell range.
rule = new XlCondFmtRuleTop10();
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #TopAndBottomRules
}
}
}
void options_CustomizeSheetHeader(ContextEventArgs e)
{
// Specify cell formatting.
var formatFirstCell = CreateXlFormattingObject(true, 24);
var formatSecondCell = CreateXlFormattingObject(true, 18);
// Add new rows displaying custom information.
for (var i = 0; i < 15; i++)
{
AddCells addCellMethod;
if (methods.TryGetValue(i, out addCellMethod))
{
addCellMethod(e, formatFirstCell, formatSecondCell);
}
else
{
e.ExportContext.AddRow();
}
}
// Merge specific cells.
MergeCells(e);
// Add an image to the top of the document.
if (imageToHeader == null)
{
using (var fileStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("GridDataAwareExportCustomization.Resources.1.jpg"))
if (fileStream != null)
{
imageToHeader = new Bitmap(Image.FromStream(fileStream));
}
}
var imageToHeaderRange = new XlCellRange(new XlCellPosition(0, 0), new XlCellPosition(5, 7));
e.ExportContext.MergeCells(imageToHeaderRange);
e.ExportContext.InsertImage(imageToHeader, imageToHeaderRange);
e.ExportContext.MergeCells(new XlCellRange(new XlCellPosition(0, 8), new XlCellPosition(5, 8)));
}
void GenerateGrandTotalRow(IXlSheet sheet)
{
// Create the grand total row.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the "Grand Total" cell and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Grand Total";
cell.ApplyFormatting(grandTotalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.4));
}
// Create five successive cells displaying quarterly total sales and annual sales for all states. The SUBTOTAL function is used to calculate subtotals for the related rows in each column.
for (int j = 0; j < 5; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 2, 3, j + 2, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(grandTotalRowFormatting);
}
}
}
}
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 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 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
}
}
}
}
}
void GenerateConditionalFormatting(IXlSheet sheet, int firstDataRowIndex)
{
// Create an instance of the XlConditionalFormatting class to define a new rule.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (B4:B38).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, sheet.CurrentRowIndex - 2));
// Create the rule to compare values in the "Sales" column using data bars.
XlCondFmtRuleDataBar rule1 = new XlCondFmtRuleDataBar();
// Specify the color of data bars.
rule1.FillColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.4);
// Set the solid fill type.
rule1.GradientFill = false;
formatting.Rules.Add(rule1);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define new rules.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (C4:C38).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, firstDataRowIndex, 2, sheet.CurrentRowIndex - 2));
// Create the rule to identify negative values in the "Sales vs Target" column.
XlCondFmtRuleCellIs rule2 = new XlCondFmtRuleCellIs();
// Specify the relational operator to be used in the conditional formatting rule.
rule2.Operator = XlCondFmtOperator.LessThan;
// Set the threshold value.
rule2.Value = 0;
// Specify formatting options to be applied to cells if the condition is true.
// Set the font color to dark red.
rule2.Formatting = new XlFont()
{
Color = Color.DarkRed
};
formatting.Rules.Add(rule2);
// Create the rule to identify top five values in the "Sales vs Target" column.
XlCondFmtRuleTop10 rule3 = new XlCondFmtRuleTop10();
rule3.Rank = 5;
// Specify formatting options to be applied to cells if the condition is true.
// Set the font color to dark green.
rule3.Formatting = new XlFont()
{
Color = Color.DarkGreen
};
formatting.Rules.Add(rule3);
// Add the specified rules to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define a new rule.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (D4:D38).
formatting.Ranges.Add(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, sheet.CurrentRowIndex - 2));
// Create the rule to compare values in the "Profit" column using data bars.
XlCondFmtRuleDataBar rule4 = new XlCondFmtRuleDataBar();
// Specify the color of data bars.
rule4.FillColor = Color.FromArgb(99, 195, 132);
// Specify the positive bar border color.
rule4.BorderColor = Color.FromArgb(99, 195, 132);
// Specify the negative bar fill color.
rule4.NegativeFillColor = Color.FromArgb(255, 85, 90);
// Specify the negative bar border color.
rule4.NegativeBorderColor = Color.FromArgb(255, 85, 90);
// Specify the solid fill type.
rule4.GradientFill = false;
formatting.Rules.Add(rule4);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define a new rule.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (E4:E38).
formatting.Ranges.Add(XlCellRange.FromLTRB(4, firstDataRowIndex, 4, sheet.CurrentRowIndex - 2));
// Create the rule to apply a specific icon from the three traffic lights icon set to each cell in the "Market Share" column based on its value.
XlCondFmtRuleIconSet rule5 = new XlCondFmtRuleIconSet();
rule5.IconSetType = XlCondFmtIconSetType.TrafficLights3;
formatting.Rules.Add(rule5);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
}
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 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 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 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 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 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 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 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 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);
}
}
}
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
}
}
}
static void Main(string[] args)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xlsx);
// Create the FileStream object with the specified file path.
using (FileStream stream = new FileStream("Document.xlsx", FileMode.Create, FileAccess.ReadWrite)) {
// Create a new document and begin to write it to the specified stream.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Sales report";
// Create the first column and set its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
// Create the second column and set its width.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 250;
}
// Create the third column 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 cell font attributes.
XlCellFormatting cellFormatting = new XlCellFormatting();
cellFormatting.Font = new XlFont();
cellFormatting.Font.Name = "Century Gothic";
cellFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(cellFormatting);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Create 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 sales report.
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(cellFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = products[i % 4];
cell.ApplyFormatting(cellFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = amount[i];
cell.ApplyFormatting(cellFormatting);
}
}
}
// Enable AutoFilter for the created cell range.
sheet.AutoFilterRange = sheet.DataRange;
// Specify formatting settings for the total row.
XlCellFormatting totalRowFormatting = new XlCellFormatting();
totalRowFormatting.CopyFrom(cellFormatting);
totalRowFormatting.Font.Bold = true;
totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent5, 0.6));
// Create the total row.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total amount";
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
using (IXlCell cell = row.CreateCell()) {
// Add values in the cell range C2 through C9 using the SUBTOTAL function.
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(2, 1, 2, 8), XlSummary.Sum, true));
cell.ApplyFormatting(totalRowFormatting);
}
}
}
}
}
// Open the XLSX document using the default application.
System.Diagnostics.Process.Start("Document.xlsx");
}
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
}
}
}
