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 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 RichTextFormatting(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; #region #RichTextFormatting // Create a new worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Create the first column and set its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 180; } // Create the first row. using (IXlRow row = sheet.CreateRow()) { // Create the cell A1. using (IXlCell cell = row.CreateCell()) { // Create an XlRichTextString instance. XlRichTextString richText = new XlRichTextString(); // Add three text runs to the collection. richText.Runs.Add(new XlRichTextRun("Formatted ", XlFont.CustomFont("Arial", 14.0, XlColor.FromArgb(0x53, 0xbb, 0xf4)))); richText.Runs.Add(new XlRichTextRun("cell ", XlFont.CustomFont("Century Gothic", 14.0, XlColor.FromArgb(0xf1, 0x77, 0x00)))); richText.Runs.Add(new XlRichTextRun("text", XlFont.CustomFont("Consolas", 14.0, XlColor.FromArgb(0xe3, 0x2c, 0x2e)))); // Add the rich formatted text to the cell. cell.SetRichText(richText); } } } #endregion #RichTextFormatting } }
static void 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 DisplayXAxis(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Create worksheet columns and set their widths. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 200; } for (int i = 0; i < 9; i++) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"); } } // Specify formatting settings for cells containing data. XlCellFormatting rowFormatting = new XlCellFormatting(); rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0); // Specify formatting settings for the header row. XlCellFormatting headerRowFormatting = rowFormatting.Clone(); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0)); string[] columnNames = new string[] { "State", "Q1'13", "Q2'13", "Q3'13", "Q4'13", "Q1'14", "Q2'14", "Q3'14", "Q4'14" }; // Generate the header row. using (IXlRow row = sheet.CreateRow()) { row.BulkCells(columnNames, headerRowFormatting); } // Generate data for the document. Random random = new Random(); string[] products = new string[] { "Alabama", "Arizona", "California", "Colorado", "Connecticut", "Florida" }; foreach (string product in products) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = product; cell.ApplyFormatting(rowFormatting); } for (int j = 0; j < 8; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = Math.Round((random.NextDouble() + 0.5) * 2000 * Math.Sign(random.NextDouble() - 0.4)); cell.ApplyFormatting(rowFormatting); } } } } #region #DisplayXAxis // Create a sparkline group. XlSparklineGroup group = new XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 8, 6), XlCellRange.FromLTRB(9, 1, 9, 6)); // Change the sparkline group type to "Column". group.SparklineType = XlSparklineType.Column; // Display the horizontal axis. group.DisplayXAxis = true; // Set the series color. group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, 0.0); // Highlight negative points on each sparkline in the group. group.ColorNegative = XlColor.FromTheme(XlThemeColor.Accent2, 0.0); group.HighlightNegative = true; sheet.SparklineGroups.Add(group); #endregion #DisplayXAxis } } }
static void AdjustScaling(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Create worksheet columns and set their widths. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 200; } for (int i = 0; i < 5; i++) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"); } } // Specify formatting settings for cells containing data. XlCellFormatting rowFormatting = new XlCellFormatting(); rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0); // Specify formatting settings for the header row. XlCellFormatting headerRowFormatting = new XlCellFormatting(); headerRowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0)); string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4" }; // Generate the header row. using (IXlRow row = sheet.CreateRow()) { row.BulkCells(columnNames, headerRowFormatting); } // Generate data for the document. Random random = new Random(); string[] products = new string[] { "HD Video Player", "SuperLED 42", "SuperLED 50", "DesktopLED 19", "DesktopLED 21", "Projector Plus HD" }; foreach (string product in products) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = product; cell.ApplyFormatting(rowFormatting); } for (int j = 0; j < 4; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = Math.Round(random.NextDouble() * 2000 + 1500); cell.ApplyFormatting(rowFormatting); } } } } #region #AdjustScaling // Create a sparkline group. XlSparklineGroup group = new XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 4, 6), XlCellRange.FromLTRB(5, 1, 5, 6)); // Set the sparkline color. group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, 0.0); // Change the sparkline group type to "Column". group.SparklineType = XlSparklineType.Column; // Set the custom minimum value for the vertical axis. group.MinScaling = XlSparklineAxisScaling.Custom; group.ManualMin = 1000.0; // Set the automatic maximum value for all sparklines in the group. group.MaxScaling = XlSparklineAxisScaling.Group; sheet.SparklineGroups.Add(group); #endregion #AdjustScaling } } }
static void AddSparkline(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Create worksheet columns and set their widths. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 200; } for (int i = 0; i < 5; i++) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; column.ApplyFormatting((XlNumberFormat)@"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"); } } // Specify formatting settings for cells containing data. XlCellFormatting rowFormatting = new XlCellFormatting(); rowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0); // Specify formatting settings for the header row. XlCellFormatting headerRowFormatting = new XlCellFormatting(); headerRowFormatting.Font = XlFont.CustomFont("Century Gothic", 12.0); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0)); string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4" }; // Generate the header row. using (IXlRow row = sheet.CreateRow()) { row.BulkCells(columnNames, headerRowFormatting); } // Create a group of line sparklines. XlSparklineGroup group = new XlSparklineGroup(); // Set the sparkline color. group.ColorSeries = XlColor.FromTheme(XlThemeColor.Accent1, -0.2); // Set the sparkline weight. group.LineWeight = 1.25; sheet.SparklineGroups.Add(group); // Generate data for the document. Random random = new Random(); string[] products = new string[] { "HD Video Player", "SuperLED 42", "SuperLED 50", "DesktopLED 19", "DesktopLED 21", "Projector Plus HD" }; foreach (string product in products) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = product; cell.ApplyFormatting(rowFormatting); } for (int j = 0; j < 4; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = Math.Round(random.NextDouble() * 2000 + 3000); cell.ApplyFormatting(rowFormatting); } } #region #AddSparkline // Add one more sparkline to the existing group. int rowIndex = row.RowIndex; group.Sparklines.Add(new XlSparkline(XlCellRange.FromLTRB(1, rowIndex, 4, rowIndex), XlCellRange.FromLTRB(5, rowIndex, 5, rowIndex))); #endregion #AddSparkline } } } } }
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 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 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 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 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 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; }