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 InitializeFormatting()
{
// Specify formatting settings for the even rows.
evenRowFormatting = new XlCellFormatting();
evenRowFormatting.Font = new XlFont();
evenRowFormatting.Font.Name = "Century Gothic";
evenRowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
evenRowFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.General, XlVerticalAlignment.Center);
// Specify formatting settings for the odd rows.
oddRowFormatting = new XlCellFormatting();
oddRowFormatting.CopyFrom(evenRowFormatting);
oddRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, -0.15));
// Specify formatting settings for the header row.
headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(evenRowFormatting);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.TopColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
headerRowFormatting.Border.TopLineStyle = XlBorderLineStyle.Medium;
headerRowFormatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Specify formatting settings for the total row.
totalRowFormatting = new XlCellFormatting();
totalRowFormatting.CopyFrom(evenRowFormatting);
totalRowFormatting.Font.Bold = true;
}
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 InitializeFormatting()
{
// Specify formatting settings for the header rows.
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));
headerRowFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom);
// Specify formatting settings for the data rows.
dataRowFormatting = new XlCellFormatting();
dataRowFormatting.Font = XlFont.BodyFont();
dataRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, 0.0));
// Specify formatting settings for the total rows.
totalRowFormatting = new XlCellFormatting();
totalRowFormatting.Font = XlFont.BodyFont();
totalRowFormatting.Font.Bold = true;
totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0));
// Specify formatting settings for the grand total row.
grandTotalRowFormatting = new XlCellFormatting();
grandTotalRowFormatting.Font = XlFont.BodyFont();
grandTotalRowFormatting.Font.Bold = true;
grandTotalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.2));
}
public DataTab(string name, XlColor color, Schema schema, IEnumerable <BaseDTO> list)
{
Name = name;
Color = color;
Schema = MakeSchema(schema);
_list = list == null ? new List <BaseDTO>() : new List <BaseDTO>(list);
}
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
}
}
}
void GenerateDataRow(IXlSheet sheet, EmployeeData employee, bool isLastRow)
{
// Create the data row to display the employee's information.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 28;
// Specify formatting settings to be applied to the data rows to shade alternate rows.
XlCellFormatting formatting = new XlCellFormatting();
formatting.CopyFrom((row.RowIndex % 2 == 0) ? evenRowFormatting : oddRowFormatting);
// Set the bottom border for the last data row.
if (isLastRow)
{
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Create the cell containing the employee's ID.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Id;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the employee's name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Name;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the employee's salary.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Salary;
cell.ApplyFormatting(formatting);
}
// Create the cell containing information about bonuses.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Bonus;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the department name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = employee.Department;
cell.ApplyFormatting(formatting);
}
}
}
void GenerateDataRow(IXlSheet sheet, SalesData data, bool isLastRow)
{
// Create the data row to display sales information for the specific state.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Specify formatting settings to be applied to the data rows to shade alternate rows.
XlCellFormatting formatting = new XlCellFormatting();
formatting.CopyFrom((row.RowIndex % 2 == 0) ? evenRowFormatting : oddRowFormatting);
// Set the bottom border for the last data row.
if (isLastRow)
{
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Create the cell containing the state name.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.State;
cell.ApplyFormatting(formatting);
}
// Create the cell containing sales data.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales;
cell.ApplyFormatting(formatting);
}
// Create the cell that displays the difference between the actual and target sales.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.ActualSales / data.TargetSales - 1;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the state profit.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Profit;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the percentage of a total market.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.MarketShare;
cell.ApplyFormatting(formatting);
}
}
}
void 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 Fill(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #Fill
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the predefined color.
cell.ApplyFormatting(XlFill.SolidFill(Color.Beige));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the custom RGB color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(0xff, 0x99, 0x66)));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the theme color.
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent3, 0.4)));
}
}
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using predefined colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkDown, Color.Red, Color.White));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using custom RGB colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkTrellis, Color.FromArgb(0xff, 0xff, 0x66), Color.FromArgb(0x66, 0x99, 0xff)));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using theme colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.LightHorizontal, XlColor.FromTheme(XlThemeColor.Accent1, 0.2), XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
#endregion #Fill
}
}
void 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);
}
}
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);
}
}
}
}
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 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 RichTextFormatting(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
document.Options.Culture = CultureInfo.CurrentCulture;
#region #RichTextFormatting
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create the first column and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 180;
}
// Create the first row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A1.
using (IXlCell cell = row.CreateCell())
{
// Create an XlRichTextString instance.
XlRichTextString richText = new XlRichTextString();
// Add three text runs to the collection.
richText.Runs.Add(new XlRichTextRun("Formatted ", XlFont.CustomFont("Arial", 14.0, XlColor.FromArgb(0x53, 0xbb, 0xf4))));
richText.Runs.Add(new XlRichTextRun("cell ", XlFont.CustomFont("Century Gothic", 14.0, XlColor.FromArgb(0xf1, 0x77, 0x00))));
richText.Runs.Add(new XlRichTextRun("text", XlFont.CustomFont("Consolas", 14.0, XlColor.FromArgb(0xe3, 0x2c, 0x2e))));
// Add the rich formatted text to the cell.
cell.SetRichText(richText);
}
}
}
#endregion #RichTextFormatting
}
}
static void 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 CustomFormatting(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create columns "A", "B" and "C" and set their widths.
int[] widths = new int[] { 165, 120, 100 };
for (int i = 0; i < 3; i++)
{
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = widths[i];
}
#region #CustomFormatting
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
// Create objects containing information about table columns (their names and formatting).
List <XlTableColumnInfo> columns = new List <XlTableColumnInfo>();
columns.Add(new XlTableColumnInfo("Product"));
columns.Add(new XlTableColumnInfo("Category"));
columns.Add(new XlTableColumnInfo("Amount"));
// Specify formatting settings for the last column of the table.
columns[2].HeaderRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, -0.3));
columns[2].DataFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
columns[2].DataFormatting.NumberFormat = accounting;
columns[2].TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.8));
columns[2].TotalRowFormatting.NumberFormat = accounting;
// Specify formatting settings for the header row of the table.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, 0.0));
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.BottomColor = XlColor.FromArgb(0, 0, 0);
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Dashed;
// Start generating the table with a header row displayed.
IXlTable table = row.BeginTable(columns, true, headerRowFormatting);
// Apply the table style.
table.Style.Name = XlBuiltInTableStyleId.Medium16;
// Disable banded row formatting for the table.
table.Style.ShowRowStripes = false;
// Disable the filtering functionality for the table.
table.HasAutoFilter = false;
// Specify formatting settings for the total row of the table.
table.TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
table.TotalRowFormatting.Border = new XlBorder()
{
BottomColor = XlColor.FromTheme(XlThemeColor.Accent6, 0.0),
BottomLineStyle = XlBorderLineStyle.Thick,
TopColor = XlColor.FromArgb(0, 0, 0),
TopLineStyle = XlBorderLineStyle.Dashed
};
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
row.EndTable(sheet.Tables[0], true);
#endregion #CustomFormatting
}
}
}
static void 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 AddSparkline(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create worksheet columns and set their widths.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)");
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4" };
// Generate the header row.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(columnNames, headerRowFormatting);
}
// Create a group of line sparklines.
XlSparklineGroup group = new XlSparklineGroup();
// Set the sparkline color.
group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, -0.2);
// Set the sparkline weight.
group.LineWeight = 1.25;
sheet.SparklineGroups.Add(group);
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "HD Video Player", "SuperLED 42", "SuperLED 50", "DesktopLED 19", "DesktopLED 21", "Projector Plus HD" };
foreach (string product in products)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = product;
cell.ApplyFormatting(rowFormatting);
}
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell())
{
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
#region #AddSparkline
// Add one more sparkline to the existing group.
int rowIndex = row.RowIndex;
group.Sparklines.Add(new XlSparkline(XlCellRange.FromLTRB(1, rowIndex, 4, rowIndex), XlCellRange.FromLTRB(5, rowIndex, 5, rowIndex)));
#endregion #AddSparkline
}
}
}
}
}
static void 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 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 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 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 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 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");
}
