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 and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 25;
// Create the first cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the second cell in the total row and assign the SUBTOTAL function to it to calculate the average of the subtotal of the cells located in the "Sales" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, row.RowIndex - 1), XlSummary.Average, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Avg=""#,##0,,""M""");
}
// Create the third cell in the row and apply specific formatting settings to this cell.
using (IXlCell cell = row.CreateCell())
cell.ApplyFormatting(totalRowFormatting);
// Create the fourth cell in the total row and assign the SUBTOTAL function to it to calculate the sum of the subtotal of the cells located in the "Profit" column.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting((XlNumberFormat)@"""Sum=""#,##0,,""M""");
}
}
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Create the total row for each inner group of sales in the specific state.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the "Total" cell and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
// Create four successive cells displaying total sales for each quarter individually. Use the SUBTOTAL function to add quarterly sales.
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 2, firstDataRowIndex, j + 2, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
// Create the cell that displays yearly sales for the state. Use the SUBTOTAL function to add yearly sales in the current state for each product.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(6, firstDataRowIndex, 6, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.1)));
}
}
}
void GenerateBillToRow(IXlSheet sheet, string name1, object value1, string name2, object value2, XlBorder specificBorder)
{
using (IXlRow row = sheet.CreateRow()) {
// Set the cell font.
row.ApplyFormatting(infoFont);
// Skip the first cell in the row.
row.SkipCells(1);
// Create the empty cell with the specified formatting settings.
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name1;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value1);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name2;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value2);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create three successive cells, apply specific formatting settings to them and set the cell borders.
for (int i = 0; i < 3; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
cell.ApplyFormatting(specificBorder);
}
}
}
}
void GenerateTitleRow(IXlSheet sheet, string info, string name, object value, int rowHeight, XlFont font, XlNumberFormat specificFormat)
{
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = rowHeight;
// Set the cell font.
row.ApplyFormatting(font);
// Create the first empty cell.
row.SkipCells(1);
// Create the blank cell with the specified formatting settings.
row.BlankCells(1, leftPanelFormatting);
// Create the third cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = info;
cell.ApplyFormatting(leftPanelFormatting);
}
// Create two blank cells with the specified formatting settings.
row.BlankCells(2, leftPanelFormatting);
// Create the cell, apply specific formatting settings to it and set the cell right border.
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(leftPanelFormatting);
cell.ApplyFormatting(leftPanelBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name;
cell.ApplyFormatting(rightPanelFormatting);
cell.Formatting.Alignment.Indent = 1;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value);
cell.ApplyFormatting(rightPanelFormatting);
if (specificFormat != null)
{
cell.ApplyFormatting(specificFormat);
}
}
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, rightPanelFormatting);
}
}
void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Skip one row before starting to generate the total row.
sheet.SkipRows(1);
// Create the total row.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
row.HeightInPixels = 28;
// Set font characteristics for the row cells.
row.ApplyFormatting(infoFont.Clone());
row.Formatting.Font.Bold = true;
// Skip six successive cells in the total row.
row.SkipCells(6);
// Create the "Total" cell.
using (IXlCell cell = row.CreateCell())
cell.Value = "TOTAL";
// Create the cell that displays the total amount.
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount.
cell.SetFormula(string.Format("SUM(H{0}:H{1})", firstDataRowIndex + 1, row.RowIndex - 1));
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
// Create the empty cell.
using (IXlCell cell = row.CreateCell())
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
}
void GenerateHeaderRow(IXlSheet sheet)
{
// Create an array that contains column labels for the header row.
string[] columnNames = new string[] { "Description", null, "QTY", "Unit Price", "Discount", "Amount" };
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
row.HeightInPixels = 28;
// Skip the first cell in the row.
row.SkipCells(1);
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, headerRowFormatting);
// Create cells that display column labels and apply specific formatting settings to them.
foreach (string columnName in columnNames)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = columnName;
cell.ApplyFormatting(headerRowFormatting);
}
}
// Create one blank cell with the specified formatting settings.
row.BlankCells(1, headerRowFormatting);
}
}
void GenerateDataRow(IXlSheet sheet, SalesData data)
{
// Create the row to display sales information for each sale item.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first row in the cell.
row.SkipCells(1);
// Create the cell to display the product name and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Product;
cell.ApplyFormatting(dataRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
// Create the cell to display sales amount in the first quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q1;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the second quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q2;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the third quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q3;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display sales amount in the fourth quarter and specify its format settings.
using (IXlCell cell = row.CreateCell()) {
cell.Value = data.Q4;
cell.ApplyFormatting(dataRowFormatting);
}
// Create the cell to display annual sales for the product. Use the SUM function to add product sales in each quarter.
using (IXlCell cell = row.CreateCell()) {
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(2, row.RowIndex, 5, row.RowIndex)));
cell.ApplyFormatting(dataRowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
void ExportCell(IXlRow row, int gridRowHandle, GridColumn gridColumn)
{
using (IXlCell cell = row.CreateCell()) {
// Set cell value
cell.Value = XlVariantValue.FromObject(this.view.GetRowCellValue(gridRowHandle, gridColumn));
// Get cell appearance
AppearanceObject appearance = GetCellAppearance(gridRowHandle, gridColumn);
// Apply alignment
XlCellAlignment alignment = new XlCellAlignment()
{
WrapText = appearance.TextOptions.WordWrap.HasFlag(WordWrap.Wrap),
VerticalAlignment = ConvertAlignment(appearance.TextOptions.VAlignment),
HorizontalAlignment = ConvertAlignment(appearance.TextOptions.HAlignment)
};
cell.ApplyFormatting(alignment);
// Apply borders
Color borderColor = appearance.GetBorderColor();
if (!DXColor.IsTransparentOrEmpty(borderColor))
{
cell.ApplyFormatting(XlBorder.OutlineBorders(borderColor));
}
// Apply fill
if (appearance.Options.UseBackColor)
{
cell.ApplyFormatting(XlFill.SolidFill(appearance.BackColor));
}
// Apply font
Font appearanceFont = appearance.Font;
XlFont font = XlFont.CustomFont(appearanceFont.Name);
font.Size = appearanceFont.SizeInPoints;
font.Bold = appearanceFont.Bold;
font.Italic = appearanceFont.Italic;
font.StrikeThrough = appearanceFont.Strikeout;
font.Underline = appearanceFont.Underline ? XlUnderlineType.Single : XlUnderlineType.None;
if (appearance.Options.UseForeColor)
{
font.Color = appearance.ForeColor;
}
cell.ApplyFormatting(font);
}
}
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 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, 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 GenerateHeaderRow(IXlSheet sheet)
{
string[] columnNames = new string[] { "Employee ID", "Employee Name", "Salary", "Bonus", "Department" };
// Create the header row and set its height.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 28;
// Create required cells in the header row and apply specific formatting settings to them.
foreach (string columnName in columnNames)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = columnName;
cell.ApplyFormatting(headerRowFormatting);
}
}
}
}
void GenerateInfoRow(IXlSheet sheet, string info)
{
// Skip one row before starting to generate the row with additional information.
sheet.SkipRows(1);
// Create the row.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the cell that contains the invoice payment options and set its font attributes.
using (IXlCell cell = row.CreateCell()) {
cell.Value = info;
cell.ApplyFormatting(infoFont);
}
}
}
static void Fill(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #Fill
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the predefined color.
cell.ApplyFormatting(XlFill.SolidFill(Color.Beige));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the custom RGB color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(0xff, 0x99, 0x66)));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the theme color.
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent3, 0.4)));
}
}
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using predefined colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkDown, Color.Red, Color.White));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using custom RGB colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkTrellis, Color.FromArgb(0xff, 0xff, 0x66), Color.FromArgb(0x66, 0x99, 0xff)));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using theme colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.LightHorizontal, XlColor.FromTheme(XlThemeColor.Accent1, 0.2), XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
#endregion #Fill
}
}
static void Borders(Stream stream, XlDocumentFormat documentFormat)
{
#region #Borders
// Specify a two-dimensional array that stores possible line styles for a border.
XlBorderLineStyle[,] lineStyles = new XlBorderLineStyle[, ] {
{ XlBorderLineStyle.Thin, XlBorderLineStyle.Medium, XlBorderLineStyle.Thick, XlBorderLineStyle.Double },
{ XlBorderLineStyle.Dotted, XlBorderLineStyle.Dashed, XlBorderLineStyle.DashDot, XlBorderLineStyle.DashDotDot },
{ XlBorderLineStyle.SlantDashDot, XlBorderLineStyle.MediumDashed, XlBorderLineStyle.MediumDashDot, XlBorderLineStyle.MediumDashDotDot }
};
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
for (int i = 0; i < 3; i++)
{
sheet.SkipRows(1);
// Create a worksheet row.
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
row.SkipCells(1);
// Create a new cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set outside borders for the created cell using a particular line style from the lineStyles array.
cell.ApplyFormatting(XlBorder.OutlineBorders(Color.SeaGreen, lineStyles[i, j]));
}
}
}
}
}
}
#endregion #Borders
}
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 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 PrintTitles(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
#region #PrintTitles
// Print the first column and the first row on every page.
sheet.PrintTitles.SetColumns(0, 0);
sheet.PrintTitles.SetRows(0, 0);
#endregion #PrintTitles
// Generate data for the document.
// Create worksheet columns and set their widths.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 250;
}
for (int i = 0; i < 4; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = new XlFont();
rowFormatting.Font.Name = "Century Gothic";
rowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(rowFormatting);
headerRowFormatting.Font.Bold = true;
// Generate the header row.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Product";
cell.ApplyFormatting(headerRowFormatting);
}
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
}
}
}
// Generate data rows.
string[] products = new string[] {
"Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni",
"Gnocchi di nonna Alice", "Gudbrandsdalsost", "Gustaf's Knäckebröd", "Queso Cabrales",
"Queso Manchego La Pastora", "Raclette Courdavault", "Singaporean Hokkien Fried Mee", "Wimmers gute Semmelknödel"
};
Random random = new Random();
for (int i = 0; i < 12; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = products[i];
cell.ApplyFormatting(rowFormatting);
}
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
}
}
}
}
}
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 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 Font(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #Font
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create five successive columns and set their widths.
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
}
}
// Create the first row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Body font";
// Apply the theme body font to the cell content.
cell.ApplyFormatting(XlFont.BodyFont());
}
// Create the cell B1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Headings font";
// Apply the theme heading font to the cell content.
cell.ApplyFormatting(XlFont.HeadingsFont());
}
// Create the cell C1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Custom font";
// Specify the custom font attributes.
XlFont font = new XlFont();
font.Name = "Century Gothic";
font.SchemeStyle = XlFontSchemeStyles.None;
// Apply the custom font to the cell content.
cell.ApplyFormatting(font);
}
}
// Create an array that stores different values of font size.
int[] fontSizes = new int[] { 11, 14, 18, 24, 36 };
// Skip one row in the worksheet.
sheet.SkipRows(1);
// Create the third row.
using (IXlRow row = sheet.CreateRow())
{
// Create five successive cells (A3:E3) with different font sizes.
for (int i = 0; i < 5; i++)
{
using (IXlCell cell = row.CreateCell())
{
// Set the cell value that displays the applied font size.
cell.Value = string.Format("{0}pt", fontSizes[i]);
// Create a font instance of the specified size.
XlFont font = new XlFont();
font.Size = fontSizes[i];
// Apply font settings to the cell content.
cell.ApplyFormatting(font);
}
}
}
// Skip one row in the worksheet.
sheet.SkipRows(1);
// Create the fifth row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Red";
// Create a font instance and set its color.
XlFont font = new XlFont()
{
Color = Color.Red
};
// Apply the font color to the cell content.
cell.ApplyFormatting(font);
}
// Create the cell B5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Bold";
// Create a font instance and set its style to bold.
XlFont font = new XlFont()
{
Bold = true
};
// Apply the font style to the cell content.
cell.ApplyFormatting(font);
}
// Create the cell C5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Italic";
// Create a font instance and set its style to italic.
XlFont font = new XlFont()
{
Italic = true
};
// Italicize the cell text.
cell.ApplyFormatting(font);
}
// Create the cell D5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Underline";
// Create a font instance and set the underline type to double.
XlFont font = new XlFont()
{
Underline = XlUnderlineType.Double
};
// Underline the cell text.
cell.ApplyFormatting(font);
}
// Create the cell E5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "StrikeThrough";
// Create a font instance and turn the strikethrough formatting on.
XlFont font = new XlFont()
{
StrikeThrough = true
};
// Strike the cell text through.
cell.ApplyFormatting(font);
}
}
}
#endregion #Font
}
}
static void 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 HeadersFooters(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
#region #HeaderAndFooters
// Specify different headers and footers for the odd-numbered and even-numbered pages.
sheet.HeaderFooter.DifferentOddEven = true;
// Add the bold text to the header left section,
// and insert the workbook name into the header right section.
sheet.HeaderFooter.OddHeader = XlHeaderFooter.FromLCR(XlHeaderFooter.Bold + "Sample report", null, XlHeaderFooter.BookName);
// Insert the current page number into the footer right section.
sheet.HeaderFooter.OddFooter = XlHeaderFooter.FromLCR(null, null, XlHeaderFooter.PageNumber);
// Insert the workbook file path into the header left section,
// and add the worksheet name to the header right section.
sheet.HeaderFooter.EvenHeader = XlHeaderFooter.FromLCR(XlHeaderFooter.BookPath, null, XlHeaderFooter.SheetName);
// Insert the current page number into the footer left section
// and add the current date to the footer right section.
sheet.HeaderFooter.EvenFooter = XlHeaderFooter.FromLCR(XlHeaderFooter.PageNumber, null, XlHeaderFooter.Date);
#endregion #HeaderAndFooters
// Generate data for the document.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 250;
}
for (int i = 0; i < 4; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = new XlFont();
rowFormatting.Font.Name = "Century Gothic";
rowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(rowFormatting);
headerRowFormatting.Font.Bold = true;
// Generate the header row.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Product";
cell.ApplyFormatting(headerRowFormatting);
}
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
}
}
}
// Generate data rows.
string[] products = new string[] {
"Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni",
"Gnocchi di nonna Alice", "Gudbrandsdalsost", "Gustaf's Knäckebröd", "Queso Cabrales",
"Queso Manchego La Pastora", "Raclette Courdavault", "Singaporean Hokkien Fried Mee", "Wimmers gute Semmelknödel"
};
Random random = new Random();
for (int i = 0; i < 12; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = products[i];
cell.ApplyFormatting(rowFormatting);
}
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
}
}
}
}
}
static void 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 Alignment(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #Alignment
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create three successive columns and set their widths.
for (int i = 0; i < 3; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 130;
}
}
// Create the first row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Top));
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Top));
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Top));
}
}
// Create the second row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center));
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center));
}
}
// Create the third row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom));
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Bottom));
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
sheet.SkipRows(1);
// Create the fifth row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "The WrapText property is applied to wrap the text within a cell";
// Wrap the text within the cell.
cell.Formatting = new XlCellAlignment()
{
WrapText = true
};
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Indented text";
// Set the indentation of the cell content.
cell.Formatting = new XlCellAlignment()
{
Indent = 2
};
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Rotated text";
// Rotate the text within the cell.
cell.Formatting = new XlCellAlignment()
{
TextRotation = 90
};
}
}
}
#endregion #Alignment
}
}
static void 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 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 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
}
}
}