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 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));
}
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 op_CustomizeCell(DevExpress.Export.CustomizeCellEventArgs e)
{
string _mark = (grvCTCongNo.GetRowCellValue(e.RowHandle, "TenKhach") ?? string.Empty).ToString();
int _LoaiGiaoDich = int.Parse((grvCTCongNo.GetRowCellValue(e.RowHandle, "LoaiGiaoDich") ?? -1).ToString());
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
if (e.Value is DateTime && (DateTime)e.Value == testDT || e.Value == null)
{
e.Formatting.FormatType = DevExpress.Utils.FormatType.None;
e.Value = string.Empty;
}
if (e.AreaType == SheetAreaType.Header || _mark == "TỔNG CỘNG:")
{
formatting.Font.Bold = true;
formatting.Fill = XlFill.SolidFill(Color.DarkSeaGreen);
e.Handled = true;
e.Formatting.CopyFrom(formatting, FormatType.None);
}
if (e.ColumnFieldName == "LoaiGiaoDich")
{
switch (_LoaiGiaoDich)
{
case 0:
case 1:
formatting.Fill = XlFill.SolidFill(Color.FromArgb(237, 105, 28));
break;
case 2:
formatting.Fill = XlFill.SolidFill(Color.Lime);
break;
case 6:
case 7:
formatting.Fill = XlFill.SolidFill(Color.FromArgb(250, 52, 131));
break;
case 8:
case 9:
formatting.Fill = XlFill.SolidFill(Color.FromArgb(232, 72, 72));
break;
case 10:
formatting.Fill = XlFill.SolidFill(Color.Gold);
break;
}
e.Formatting.CopyFrom(formatting, FormatType.None);
}
e.Handled = true;
}
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 ExportColumn(IXlSheet sheet, GridColumn gridColumn)
{
// Skip hidden column
if (!gridColumn.Visible)
{
return;
}
using (IXlColumn column = sheet.CreateColumn()) {
// Setup number format
if (gridColumn.DisplayFormat.FormatType == FormatType.DateTime)
{
column.ApplyFormatting(XlCellFormatting.FromNetFormat(gridColumn.DisplayFormat.FormatString, true));
}
else if (gridColumn.DisplayFormat.FormatType != FormatType.None)
{
column.ApplyFormatting(XlCellFormatting.FromNetFormat(gridColumn.DisplayFormat.FormatString, false));
}
}
}
void GenerateTitle(IXlSheet sheet)
{
// Specify formatting settings for the document title.
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Name = "Calibri Light";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 24;
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.5);
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.5);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Add the document title.
using (IXlRow row = sheet.CreateRow()) {
// Skip the cell "A1".
row.SkipCells(1);
// Create the cell "B1" containing the document title.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "SALES ANALYSIS 2014";
cell.Formatting = formatting;
}
// Create five empty cells with the title formatting.
row.BlankCells(5, formatting);
}
// Skip one row before starting to generate data rows.
sheet.SkipRows(1);
// Insert a picture from a file and anchor it to the cell "G1".
string startupPath = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName);
using (IXlPicture picture = sheet.CreatePicture()) {
picture.Image = Image.FromFile(Path.Combine(startupPath, "Logo.png"));
picture.SetOneCellAnchor(new XlAnchorPoint(6, 0, 8, 4), 105, 30);
}
}
static void 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
}
}
}
void op_CustomizeCell(CustomizeCellEventArgs e)
{
long _mark = long.Parse((GVG.GetRowCellValue(e.RowHandle, "ID") ?? 0).ToString());
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Bold = true;
formatting.Font.Name = "Times New Roman";
if (_mark == 0)
{
formatting.Border = XlBorder.OutlineBorders(Color.FromArgb(216, 228, 188));
if (e.ColumnFieldName == "CongTy")
{
formatting.Border.RightColor = Color.Black;
}
formatting.Fill = XlFill.SolidFill(Color.FromArgb(216, 228, 188));
e.Formatting.FormatType = FormatType.None;
e.Value = string.Empty;
}
else
{
XlCellAlignment alignment = new XlCellAlignment();
alignment.HorizontalAlignment = XlHorizontalAlignment.Center;
alignment.VerticalAlignment = XlVerticalAlignment.Center;
if (e.AreaType == SheetAreaType.Header)
{
e.Formatting.Alignment = alignment;
formatting.Fill = XlFill.SolidFill(Color.DarkSeaGreen);
formatting.Font.Size = 15;
}
else if (_mark == -1)
{
formatting.Border = XlBorder.OutlineBorders(Color.DarkSeaGreen);
if (!"MaCho HanhTrinhDi MaHD SoVe".Contains(e.ColumnFieldName))
{
formatting.Border.RightColor = Color.Black;
}
formatting.Border.BottomColor = Color.Black;
formatting.Fill = XlFill.SolidFill(Color.DarkSeaGreen);
formatting.Font.Size = 12;
if (e.ColumnFieldName == "HanhTrinhVe")
{
e.Value = "Tổng";
}
if ((e.Value ?? string.Empty).ToString() == "0")
{
e.Value = string.Empty;
}
}
else
{
formatting.Fill = XlFill.SolidFill(Color.White);
if (e.Value is string)
{
e.Formatting.Alignment = alignment;
}
}
}
e.Formatting.CopyFrom(formatting, FormatType.None);
e.Handled = true;
}
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 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
}
}
}
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 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 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 ThemedFormatting(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #ThemedFormatting
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create six successive columns and set their widths.
for (int i = 0; i < 6; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
}
// Specify an array that stores six accent colors of the document theme.
XlThemeColor[] themeColors = new XlThemeColor[] { XlThemeColor.Accent1, XlThemeColor.Accent2, XlThemeColor.Accent3, XlThemeColor.Accent4, XlThemeColor.Accent5, XlThemeColor.Accent6 };
// Specify the "20% - AccentN" themed cell formatting.
// Create a worksheet row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 6; i++)
{
// Create a new cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 20%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 80%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.8);
}
}
}
// Specify the "40% - AccentN" themed cell formatting.
// Create a worksheet row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 6; i++)
{
// Create a new cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 40%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 60%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.6);
}
}
}
// Specify the "60% - AccentN" themed cell formatting.
// Create a worksheet row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 6; i++)
{
// Create a new cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 60%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 40%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.4);
}
}
}
// Specify the "AccentN" themed cell formatting.
// Create a worksheet row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 6; i++)
{
// Create a new cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0}", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.0);
}
}
}
}
#endregion #ThemedFormatting
}
}
static void Expression(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Generate data for the document.
int[] width = new int[] { 80, 150, 90 };
for (int i = 0; i < 3; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = width[i];
if (i == 2)
{
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = "[$$-409] #,##0.00";
}
}
}
string[] columnNames = new string[] { "Account ID", "User Name", "Balance" };
using (IXlRow row = sheet.CreateRow()) {
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.BottomColor = Color.Black;
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Thin;
for (int i = 0; i < 3; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = columnNames[i];
cell.ApplyFormatting(headerRowFormatting);
}
}
}
string[] accountIds = new string[] { "A105", "A114", "B013", "C231", "D101", "D105" };
string[] users = new string[] { "Berry Dafoe", "Chris Cadwell", "Esta Mangold", "Liam Bell", "Simon Newman", "Wendy Underwood" };
int[] balance = new int[] { 155, 250, 48, 350, -15, 10 };
for (int i = 0; i < 6; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = accountIds[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = users[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = balance[i];
}
}
}
#region #ExpressionRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A2:C7).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 1, 2, 6));
// Create the rule that uses a formula to highlight cells if a value in the column "C" is greater than 0 and less than 50.
XlCondFmtRuleExpression rule = new XlCondFmtRuleExpression("AND($C2>0,$C2<50)");
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlFill.SolidFill(Color.FromArgb(0xff, 0xff, 0xcc));
formatting.Rules.Add(rule);
// Create the rule that uses a formula to highlight cells if a value in the column "C" is less than or equal to 0.
rule = new XlCondFmtRuleExpression("$C2<=0");
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #ExpressionRule
}
}
}
void GenerateDataRow(IXlSheet sheet, InvoiceData item, bool isLastRow)
{
// Create the data row to display the invoice information on each product.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
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 = Color.FromArgb(89, 89, 89);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
}
// Skip the first cell in the row.
row.SkipCells(1);
// Create the blank cell with the specified formatting settings.
row.BlankCells(1, formatting);
// Create the cell containing the product description.
using (IXlCell cell = row.CreateCell()) {
cell.Value = item.Product;
cell.ApplyFormatting(formatting);
}
// Create the blank cell with the specified formatting settings.
row.BlankCells(1, formatting);
// Create the cell containing the product quantity.
using (IXlCell cell = row.CreateCell()) {
cell.Value = item.Qty;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the unit price.
using (IXlCell cell = row.CreateCell()) {
cell.Value = item.UnitPrice;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the product discount.
using (IXlCell cell = row.CreateCell()) {
cell.Value = item.Discount;
cell.ApplyFormatting(formatting);
}
// Create the cell containing the amount.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = item.Qty * item.UnitPrice * (1 - item.Discount);
// Set the formula to calculate the amount per product.
cell.SetFormula(string.Format("E{0}*F{0}*(1-G{0})", cell.RowIndex + 1));
cell.ApplyFormatting(formatting);
}
// Create the blank cell with the specified formatting settings.
row.BlankCells(1, formatting);
}
}
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.Fill = XlFill.SolidFill(Color.White);
// Specify formatting settings for the odd rows.
oddRowFormatting = new XlCellFormatting();
oddRowFormatting.CopyFrom(evenRowFormatting);
oddRowFormatting.Fill = XlFill.SolidFill(Color.FromArgb(242, 242, 242));
// Specify formatting settings for the header row.
headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(evenRowFormatting);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = Color.White;
headerRowFormatting.Fill = XlFill.SolidFill(Color.FromArgb(192, 0, 0));
// Set borders for the header row.
headerRowFormatting.Border = new XlBorder();
// Specify the top border and set its color to white.
headerRowFormatting.Border.TopColor = Color.White;
// Specify the medium border line style.
headerRowFormatting.Border.TopLineStyle = XlBorderLineStyle.Medium;
// Specify the bottom border for the header row.
// Set the bottom border color to dark gray.
headerRowFormatting.Border.BottomColor = Color.FromArgb(89, 89, 89);
// Specify the medium border line style.
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Specify formatting settings for the invoice header.
panelFont = new XlFont();
panelFont.Name = "Century Gothic";
panelFont.SchemeStyle = XlFontSchemeStyles.None;
panelFont.Color = Color.White;
// Set font attributes for the row displaying the invoice label and company name.
titleFont = panelFont.Clone();
titleFont.Size = 26;
// Specify formatting settings for the worksheet range containing the name and contact details of the seller (the "Vader Enterprises" panel).
leftPanelFormatting = new XlCellFormatting();
// Set the cell background color to dark gray.
leftPanelFormatting.Fill = XlFill.SolidFill(Color.FromArgb(89, 89, 89));
leftPanelFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
leftPanelFormatting.NumberFormat = XlNumberFormat.General;
// Set the right border for this range.
leftPanelBorder = new XlBorder();
// Set the right border color to white.
leftPanelBorder.RightColor = Color.White;
// Specify the medium border line style.
leftPanelBorder.RightLineStyle = XlBorderLineStyle.Medium;
// Specify formatting settings for the worksheet range containing general information about the invoice:
// its date, reference number and service description (the "Invoice" panel).
rightPanelFormatting = new XlCellFormatting();
// Set the cell background color to dark red.
rightPanelFormatting.Fill = XlFill.SolidFill(Color.FromArgb(192, 0, 0));
rightPanelFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
rightPanelFormatting.NumberFormat = XlNumberFormat.General;
// Specify formatting settings and font attributes for the worksheet range containing buyer's contact information (the "Bill To" panel).
infoFormatting = new XlCellFormatting();
// Set the cell background color to light gray.
infoFormatting.Fill = XlFill.SolidFill(Color.FromArgb(217, 217, 217));
infoFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
infoFormatting.NumberFormat = XlNumberFormat.General;
infoFont = panelFont.Clone();
infoFont.Color = Color.Black;
}
static void NumberFormat(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Specify options for exporting the document to CSV format.
CsvDataAwareExporterOptions csvOptions = document.Options as CsvDataAwareExporterOptions;
if (csvOptions != null)
{
csvOptions.WritePreamble = true;
}
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create six successive columns and set their widths.
for (int i = 0; i < 6; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 180;
}
}
#region #ExcelNumberFormat
// Create the header row for the "Excel number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Excel number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the predefined Excel number formats to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Predefined formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.456 as 123.46.
cell.Value = 123.456;
cell.Formatting = XlNumberFormat.Number2;
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 12,345.
cell.Value = 12345;
cell.Formatting = XlNumberFormat.NumberWithThousandSeparator;
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33%.
cell.Value = 0.33;
cell.Formatting = XlNumberFormat.Percentage;
}
using (IXlCell cell = row.CreateCell()) {
// Display the current date as "mm-dd-yy".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortDate;
}
using (IXlCell cell = row.CreateCell()) {
// Display the current time as "h:mm AM/PM".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortTime12;
}
}
// Use custom number formats to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Custom formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 4310.45 as $4,310.45.
cell.Value = 4310.45;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
using (IXlCell cell = row.CreateCell()) {
// Display 3426.75 as €3,426.75.
cell.Value = 3426.75;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_-[$€-2] * #,##0.00_-;-[$€-2] * #,##0.00_-;_-[$€-2] * "" - ""??_-;_-@_-";
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.333 as 33.3%.
cell.Value = 0.333;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "0.0%";
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom number format to the date value.
// Display days as Sunday–Saturday, months as January–December, days as 1–31 and years as 1900–9999.
cell.Value = DateTime.Now;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "dddd, mmmm d, yyyy";
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.6234 as 341/547.
cell.Value = 0.6234;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "# ???/???";
}
}
#endregion #ExcelNumberFormat
sheet.SkipRows(1);
#region #NETNumberFormat
// Create the header row for the ".NET number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = ".NET number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the standard .NET-style format strings to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Standard formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.45 as 123.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("D", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.234500E+004.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("E", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33.00%.
cell.Value = 0.33;
cell.Formatting = XlCellFormatting.FromNetFormat("P", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display the current date using the short date pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("d", true);
}
using (IXlCell cell = row.CreateCell()) {
// Display the current time using the short time pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("t", true);
}
}
// Use custom format strings to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Custom formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.456 as 123.46.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("#0.00", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.235E+04.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("0.0##e+00", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.333 as Max=33.3%.
cell.Value = 0.333;
cell.Formatting = XlCellFormatting.FromNetFormat("Max={0:#.0%}", false);
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current date.
// Display days as 01–31, months as 01-12 and years as a four-digit number.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("dd-MM-yyyy", true);
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current time.
// Display hours as 01-12, minutes as 00-59, and add the AM/PM designator.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("hh:mm tt", true);
}
}
#endregion #NETNumberFormat
}
}
}
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 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 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 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");
}
