static void PictureHyperlinkClick(Stream stream, XlDocumentFormat documentFormat) { #region #HyperlinkClick // 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()) { // Load a picture from a file and add a hyperlink to it. using (IXlPicture picture = sheet.CreatePicture()) { picture.Image = Image.FromFile(Path.Combine(imagesPath, "DevExpress.png")); picture.HyperlinkClick.TargetUri = "http://www.devexpress.com"; picture.HyperlinkClick.Tooltip = "Developer Express Inc."; picture.SetTwoCellAnchor(new XlAnchorPoint(1, 1, 0, 0), new XlAnchorPoint(10, 5, 2, 15), XlAnchorType.TwoCell); } } } #endregion #HyperlinkClick }
static void CreateRows(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; #region #CreateRows // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Create the first row and set its height to 40 pixels. using (IXlRow row = sheet.CreateRow()) { row.HeightInPixels = 40; } // Hide the third row in the worksheet. using (IXlRow row = sheet.CreateRow(2)) { row.IsHidden = true; } } #endregion #CreateRows } }
static void CreateColumns(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; #region #CreateColumns // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Create the column A and set its width to 100 pixels. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; } // Hide the column B in the worksheet. using (IXlColumn column = sheet.CreateColumn()) { column.IsHidden = true; } // Create the column D and set its width to 24.5 characters. using (IXlColumn column = sheet.CreateColumn(3)) { column.WidthInCharacters = 24.5f; } } #endregion #CreateColumns } }
static void AddTable(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 #AddTable IXlTable table; // Specify an array containing column headings for a table. string[] columnNames = new string[] { "Product", "Category", "Amount" }; // Create the first row in the worksheet from which the table starts. using (IXlRow row = sheet.CreateRow()) { // Start generating the table with a header row displayed. table = row.BeginTable(columnNames, true); // Specify the total row label. table.Columns[0].TotalRowLabel = "Total"; // Specify the function to calculate the total. table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum; // Specify the number format for the "Amount" column and its total cell. XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"; table.Columns[2].DataFormatting = accounting; table.Columns[2].TotalRowFormatting = accounting; } // 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(table, true); #endregion #AddTable } } }
static void SimpleFormula(Stream stream, XlDocumentFormat documentFormat) { #region #SimpleFormula // 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. for (int i = 0; i < 4; i++) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 80; } } // Generate data for the document. string[] header = new string[] { "Description", "QTY", "Price", "Amount" }; string[] product = new string[] { "Camembert", "Gorgonzola", "Mascarpone", "Mozzarella" }; int[] qty = new int[] { 12, 15, 25, 10 }; double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 }; double discount = 0.2; // 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]; } } } // 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 // applying 20% quantity discount on orders more than 15 items. cell.SetFormula(String.Format("=IF(B{0}>15,C{0}*B{0}*(1-{1}),C{0}*B{0})", i + 2, discount)); } } } } } #endregion #SimpleFormula }
static void ColorScale(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Generate data for the document. for (int i = 0; i < 11; i++) { using (IXlRow row = sheet.CreateRow()) { for (int j = 0; j < 4; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = cell.RowIndex + 1; } } } } #region #ColorScaleRule // Create an instance of the XlConditionalFormatting class. XlConditionalFormatting formatting = new XlConditionalFormatting(); // Specify cell ranges to which the conditional formatting rule should be applied (A1:A11 and C1:C11). formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10)); formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10)); // Create the default three-color scale rule to differentiate low, medium and high values in cell ranges. XlCondFmtRuleColorScale rule = new XlCondFmtRuleColorScale(); formatting.Rules.Add(rule); // Add the specified format options to the worksheet collection of conditional formats. sheet.ConditionalFormattings.Add(formatting); // Create an instance of the XlConditionalFormatting class. formatting = new XlConditionalFormatting(); // Specify cell ranges to which the conditional formatting rule should be applied (B1:B11 and D1:D11). formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10)); formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10)); // Create the two-color scale rule to differentiate low and high values in cell ranges. rule = new XlCondFmtRuleColorScale(); rule.ColorScaleType = XlCondFmtColorScaleType.ColorScale2; // Set a color corresponding to the minimum value in the cell range. rule.MinColor = XlColor.FromTheme(XlThemeColor.Light1, 0.0); // Set a color corresponding to the maximum value in the cell range. rule.MaxColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.5); formatting.Rules.Add(rule); // Add the specified format options to the worksheet collection of conditional formats. sheet.ConditionalFormattings.Add(formatting); #endregion #ColorScaleRule } } }
static void MergeCells(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Create the first row in the worksheet. using (IXlRow row = sheet.CreateRow()) { // Create a cell. using (IXlCell cell = row.CreateCell()) { // Set the cell value. cell.Value = "Merged cells A1 to E1"; // Align the cell content. cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center)); } } // Create the second row in the worksheet. using (IXlRow row = sheet.CreateRow()) { // Create a cell. using (IXlCell cell = row.CreateCell()) { // Set the cell value. cell.Value = "Merged cells A2 to A5"; // Align the cell content. cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center)); // Wrap the text within the cell. cell.Formatting.Alignment.WrapText = true; } // Create a cell. using (IXlCell cell = row.CreateCell()) { // Set the cell value. cell.Value = "Merged cells B2 to E5"; // Align the cell content. cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center)); } } #region #MergeCells // Merge cells contained in the range A1:E1. sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 0, 4, 0)); // Merge cells contained in the range A2:A5. sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 1, 0, 4)); // Merge cells contained in the range B2:E5. sheet.MergedCells.Add(XlCellRange.FromLTRB(1, 1, 4, 4)); #endregion #MergeCells } } }
static void Hyperlinks(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 #Hyperlinks // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 300; } // Create a hyperlink to a cell in the current workbook. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Local link"; cell.Formatting = XlCellFormatting.Hyperlink; XlHyperlink hyperlink = new XlHyperlink(); hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex)); hyperlink.TargetUri = "#Sheet1!C5"; sheet.Hyperlinks.Add(hyperlink); } } // Create a hyperlink to a cell located in the external workbook. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "External file link"; cell.Formatting = XlCellFormatting.Hyperlink; XlHyperlink hyperlink = new XlHyperlink(); hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex)); hyperlink.TargetUri = "linked.xlsx#Sheet1!C5"; sheet.Hyperlinks.Add(hyperlink); } } // Create a hyperlink to a web page. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "External URI"; cell.Formatting = XlCellFormatting.Hyperlink; XlHyperlink hyperlink = new XlHyperlink(); hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex)); hyperlink.TargetUri = "http://www.devexpress.com"; sheet.Hyperlinks.Add(hyperlink); } } } #endregion #Hyperlinks } }
public void ExportToExcel() { SaveFileDialogService.Filter = "Excel files (*.xlsx)|*.xlsx"; if (SaveFileDialogService.ShowDialog()) { IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xlsx); string[] columnNames = new string[] { "Word", "Definition" }; string fileName = SaveFileDialogService.GetFullFileName(); using (FileStream stream = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite)) { using (IXlDocument document = exporter.CreateDocument(stream)) { using (IXlSheet sheet = document.CreateSheet()) { sheet.Name = "Dictionary"; using (IXlColumn column = sheet.CreateColumn()) { column.WidthInCharacters = 25; column.Formatting = new XlCellFormatting(); column.Formatting.Alignment = new XlCellAlignment(); column.Formatting.Alignment.VerticalAlignment = XlVerticalAlignment.Center; } using (IXlColumn column = sheet.CreateColumn()) { column.WidthInCharacters = 100; column.Formatting = new XlCellFormatting(); column.Formatting.Alignment = new XlCellAlignment(); column.Formatting.Alignment.WrapText = true; } IXlTable table; using (IXlRow row = sheet.CreateRow()) table = row.BeginTable(columnNames, true); foreach (DictionaryBookmark bookmark in DictionaryBookmarksView) { using (IXlRow row = sheet.CreateRow()) row.BulkCells(new string[] { bookmark.Word, bookmark.Definition }, null); } using (IXlRow row = sheet.CreateRow()) row.EndTable(table, false); table.Style.ShowFirstColumn = true; } } } Process.Start(fileName); } }
static void TimePeriod(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Generate data for the document. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; column.ApplyFormatting(XlNumberFormat.ShortDate); } for (int i = 0; i < 10; i++) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = DateTime.Now.AddDays(row.RowIndex - 5); } } } #region #TimePeriodRule // Create an instance of the XlConditionalFormatting class. XlConditionalFormatting formatting = new XlConditionalFormatting(); // Specify the cell range to which the conditional formatting rules should be applied (A1:A10). formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9)); // Create the rule to highlight yesterday's dates in the cell range. XlCondFmtRuleTimePeriod rule = new XlCondFmtRuleTimePeriod(); rule.TimePeriod = XlCondFmtTimePeriod.Yesterday; // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Bad; formatting.Rules.Add(rule); // Create the rule to highlight today's dates in the cell range. rule = new XlCondFmtRuleTimePeriod(); rule.TimePeriod = XlCondFmtTimePeriod.Today; // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Good; formatting.Rules.Add(rule); // Create the rule to highlight tomorrows's dates in the cell range. rule = new XlCondFmtRuleTimePeriod(); rule.TimePeriod = XlCondFmtTimePeriod.Tomorrow; // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Neutral; formatting.Rules.Add(rule); // Add the specified format options to the worksheet collection of conditional formats. sheet.ConditionalFormattings.Add(formatting); #endregion #TimePeriodRule } } }
static void TableStyle(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 #TableStyle IXlTable table; // Specify an array containing column headings for a table. string[] columnNames = new string[] { "Product", "Category", "Amount" }; // Create the first row in the worksheet from which the table starts. using (IXlRow row = sheet.CreateRow()) { // Start generating the table with a header row displayed. table = row.BeginTable(columnNames, true); // Apply the table style. table.Style.Name = XlBuiltInTableStyleId.Dark7; } // 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); // Create the last table row and finish the table. // The total row is not displayed for the table. using (IXlRow row = sheet.CreateRow()) { row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null); row.EndTable(table, false); } #endregion #TableStyle } } }
static void CreateDocument(Stream stream, XlDocumentFormat documentFormat) { #region #CreateDocument // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document and write it to the specified stream. using (IXlDocument document = exporter.CreateDocument(stream)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; } #endregion #CreateDocument }
static void Blanks(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Generate data for the document. for (int i = 0; i < 10; i++) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { if ((i % 2) == 0) { cell.Value = i + 1; } } } } #region #BlanksRule // Create an instance of the XlConditionalFormatting class. XlConditionalFormatting formatting = new XlConditionalFormatting(); // Specify the cell range to which the conditional formatting rules should be applied (A1:A10). formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9)); // Create the rule to highlight blank cells in the range. XlCondFmtRuleBlanks rule = new XlCondFmtRuleBlanks(true); // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Bad; formatting.Rules.Add(rule); // Create the rule to highlight non-blank cells in the range. rule = new XlCondFmtRuleBlanks(false); // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Good; formatting.Rules.Add(rule); // Add the specified format options to the worksheet collection of conditional formats. sheet.ConditionalFormattings.Add(formatting); #endregion #BlanksRule } } }
MemoryStream CreateBIFF8DataStream() { IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xls); MemoryStream dataStream = new MemoryStream(); using (IXlDocument document = exporter.CreateDocument(dataStream)) { using (IXlSheet sheet = document.CreateSheet()) { ExportColumns(sheet); ExportRows(sheet); this.sheetName = sheet.Name; this.dataRange = sheet.DataRange; } } dataStream.Position = 0; return(dataStream); }
static void Duplicates(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Generate data for the document. for (int i = 0; i < 11; i++) { using (IXlRow row = sheet.CreateRow()) { for (int j = 0; j < 4; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = cell.ColumnIndex * cell.RowIndex + cell.RowIndex + 1; } } } } #region #DuplicatesRule // Create an instance of the XlConditionalFormatting class. XlConditionalFormatting formatting = new XlConditionalFormatting(); // Specify the cell range to which the conditional formatting rules should be applied (A1:D11). formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 10)); // Create the rule to identify duplicate values in the cell range. formatting.Rules.Add(new XlCondFmtRuleDuplicates() { Formatting = XlCellFormatting.Bad }); // Create the rule to identify unique values in the cell range. formatting.Rules.Add(new XlCondFmtRuleUnique() { Formatting = XlCellFormatting.Good }); // Add the specified format options to the worksheet collection of conditional formats. sheet.ConditionalFormattings.Add(formatting); #endregion #DuplicatesRule } } }
static void Top10(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Generate data for the document. for (int i = 0; i < 10; i++) { using (IXlRow row = sheet.CreateRow()) { for (int j = 0; j < 4; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = cell.ColumnIndex * 4 + cell.RowIndex + 1; } } } } #region #TopAndBottomRules // Create an instance of the XlConditionalFormatting class. XlConditionalFormatting formatting = new XlConditionalFormatting(); // Specify the cell range to which the conditional formatting rules should be applied (A1:D10). formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 9)); // Create the rule to identify bottom 10 values in the cell range. XlCondFmtRuleTop10 rule = new XlCondFmtRuleTop10(); rule.Bottom = true; // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Bad; formatting.Rules.Add(rule); // Create the rule to identify top 10 values in the cell range. rule = new XlCondFmtRuleTop10(); // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Good; formatting.Rules.Add(rule); // Add the specified format options to the worksheet collection of conditional formats. sheet.ConditionalFormattings.Add(formatting); #endregion #TopAndBottomRules } } }
static void CreateSheet(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); #region #CreateSheet // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; // Create a new worksheet under the specified name. using (IXlSheet sheet = document.CreateSheet()) { sheet.Name = "Sales report"; } } #endregion #CreateSheet }
static void Fill(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; #region #Fill // Create a new worksheet. using (IXlSheet sheet = document.CreateSheet()) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { // Fill the cell background using the predefined color. cell.ApplyFormatting(XlFill.SolidFill(Color.Beige)); } using (IXlCell cell = row.CreateCell()) { // Fill the cell background using the custom RGB color. cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(0xff, 0x99, 0x66))); } using (IXlCell cell = row.CreateCell()) { // Fill the cell background using the theme color. cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent3, 0.4))); } } using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { // Specify the cell background pattern using predefined colors. cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkDown, Color.Red, Color.White)); } using (IXlCell cell = row.CreateCell()) { // Specify the cell background pattern using custom RGB colors. cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkTrellis, Color.FromArgb(0xff, 0xff, 0x66), Color.FromArgb(0x66, 0x99, 0xff))); } using (IXlCell cell = row.CreateCell()) { // Specify the cell background pattern using theme colors. cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.LightHorizontal, XlColor.FromTheme(XlThemeColor.Accent1, 0.2), XlColor.FromTheme(XlThemeColor.Light2, 0.0))); } } } #endregion #Fill } }
bool ExportToFile(string fileName, XlDocumentFormat documentFormat) { try { using (FileStream stream = new FileStream(fileName, FileMode.Create)) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document and begin to write it to the specified stream. using (IXlDocument document = exporter.CreateDocument(stream)) { // Generate the document content. GenerateDocument(document); } } return(true); } catch (Exception ex) { MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); return(false); } }
static void HideHeaders(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; #region #HideHeaders // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Hide row and column headers in the worksheet. sheet.ViewOptions.ShowRowColumnHeaders = false; } #endregion #HideHeaders } }
static void EncryptDocument(Stream stream, XlDocumentFormat documentFormat) { #region #EncryptDocument // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Specify encryption options. // A workbook will be encrypted using the default encryption mechanism // (agile encryption for XLSX files, and RC4 encryption for XLS files). EncryptionOptions encryptionOptions = new EncryptionOptions(); // Specify the encryption password. encryptionOptions.Password = "******"; // Create a new document and encrypt its contents. using (IXlDocument document = exporter.CreateDocument(stream, encryptionOptions)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; } #endregion #EncryptDocument }
static void HideGridlines(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; #region #HideGridlines // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Hide gridlines on the worksheet. sheet.ViewOptions.ShowGridLines = false; } #endregion #HideGridlines } }
static void PageSetup(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 #PageSetup // Specify page settings for the worksheet. sheet.PageSetup = new XlPageSetup(); // Select the paper size. sheet.PageSetup.PaperKind = System.Drawing.Printing.PaperKind.A4; // Set the page orientation to Landscape. sheet.PageSetup.PageOrientation = XlPageOrientation.Landscape; // Scale the print area to fit to one page wide. sheet.PageSetup.FitToPage = true; sheet.PageSetup.FitToWidth = 1; sheet.PageSetup.FitToHeight = 0; // Print in black and white. sheet.PageSetup.BlackAndWhite = true; // Specify the number of copies. sheet.PageSetup.Copies = 2; #endregion #PageSetup // Generate data for the document. sheet.SkipRows(1); using (IXlRow row = sheet.CreateRow()) { row.SkipCells(1); using (IXlCell cell = row.CreateCell()) { cell.Value = "Invoke the Page Setup dialog to control page settings."; } } } } }
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 Borders(Stream stream, XlDocumentFormat documentFormat) { #region #Borders // Specify a two-dimensional array that stores possible line styles for a border. XlBorderLineStyle[,] lineStyles = new XlBorderLineStyle[, ] { { XlBorderLineStyle.Thin, XlBorderLineStyle.Medium, XlBorderLineStyle.Thick, XlBorderLineStyle.Double }, { XlBorderLineStyle.Dotted, XlBorderLineStyle.Dashed, XlBorderLineStyle.DashDot, XlBorderLineStyle.DashDotDot }, { XlBorderLineStyle.SlantDashDot, XlBorderLineStyle.MediumDashed, XlBorderLineStyle.MediumDashDot, XlBorderLineStyle.MediumDashDotDot } }; // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { for (int i = 0; i < 3; i++) { sheet.SkipRows(1); // Create a worksheet row. using (IXlRow row = sheet.CreateRow()) { for (int j = 0; j < 4; j++) { row.SkipCells(1); // Create a new cell in the row. using (IXlCell cell = row.CreateCell()) { // Set outside borders for the created cell using a particular line style from the lineStyles array. cell.ApplyFormatting(XlBorder.OutlineBorders(Color.SeaGreen, lineStyles[i, j])); } } } } } } #endregion #Borders }
static void DocumentProperties(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); #region #DocumentProperties // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Set the built-in document properties. document.Properties.Title = "XL Export API: document properties example"; document.Properties.Subject = "XL Export API"; document.Properties.Keywords = "XL Export, document generation"; document.Properties.Description = "How to set document properties using the XL Export API"; document.Properties.Category = "Spreadsheet"; document.Properties.Company = "DevExpress Inc."; // Set the custom document properties. document.Properties.Custom["Product Suite"] = "XL Export Library"; document.Properties.Custom["Revision"] = 5; document.Properties.Custom["Date Completed"] = DateTime.Now; document.Properties.Custom["Published"] = true; // Generate data for the document. using (IXlSheet sheet = document.CreateSheet()) { sheet.SkipRows(1); using (IXlRow row = sheet.CreateRow()) { row.SkipCells(1); using (IXlCell cell = row.CreateCell()) { cell.Value = "You can view document properties using the File->Info->Properties->Advanced Properties dialog."; } } } } #endregion #DocumentProperties }
static void FitPicture(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()) { sheet.SkipColumns(1); // Create the column "B" and set its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 300; } sheet.SkipRows(1); // Create the second row and set its height. using (IXlRow row = sheet.CreateRow()) { row.HeightInPixels = 154; } #region #FitPicture // Insert a picture from a file to fit in the cell B2. using (IXlPicture picture = sheet.CreatePicture()) { picture.Image = Image.FromFile(Path.Combine(imagesPath, "image1.jpg")); picture.FitToCell(new XlCellPosition(1, 1), 300, 154, true); } } #endregion #FitPicture } }
static void CreateHiddenSheet(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { // Specify the document culture. document.Options.Culture = CultureInfo.CurrentCulture; #region #CreateHiddenSheet // Create the first worksheet. using (IXlSheet sheet = document.CreateSheet()) { sheet.Name = "Sales report"; } // Create the second worksheet and specify its visibility. using (IXlSheet sheet = document.CreateSheet()) { sheet.Name = "Sales data"; sheet.VisibleState = XlSheetVisibleState.Hidden; } #endregion #CreateHiddenSheet } }
static void InsertPicture(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 #InsertPicture // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { // Insert a picture from a file and anchor it to cells. using (IXlPicture picture = sheet.CreatePicture()) { picture.Image = Image.FromFile(Path.Combine(imagesPath, "image1.jpg")); picture.SetTwoCellAnchor(new XlAnchorPoint(1, 1, 0, 0), new XlAnchorPoint(6, 11, 2, 15), XlAnchorType.TwoCell); } } #endregion #InsertPicture } }
static void PageMargins(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 #PageMargins sheet.PageMargins = new XlPageMargins(); // Set the unit of margin measurement. sheet.PageMargins.PageUnits = XlPageUnits.Centimeters; // Specify page margins. sheet.PageMargins.Left = 2.0; sheet.PageMargins.Right = 1.0; sheet.PageMargins.Top = 1.25; sheet.PageMargins.Bottom = 1.25; // Specify header and footer margins. sheet.PageMargins.Header = 0.7; sheet.PageMargins.Footer = 0.7; #endregion #PageMargins // Generate data for the document. sheet.SkipRows(1); using (IXlRow row = sheet.CreateRow()) { row.SkipCells(1); using (IXlCell cell = row.CreateCell()) { cell.Value = "Invoke the Page Setup dialog to control margin settings."; } } } } }