Exemple #1
0
        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
                }
            }
        }
Exemple #2
0
        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);
            }
        }
Exemple #3
0
        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
                }
            }
        }
Exemple #4
0
        static void CalculatedColumn(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 (A:F) and set their widths.
                    int[] widths = new int[] { 165, 100, 100, 100, 100, 110 };
                    for (int i = 0; i < 6; i++)
                    {
                        using (IXlColumn column = sheet.CreateColumn())
                            column.WidthInPixels = widths[i];
                    }

                    #region #CalculatedColumn
                    IXlTable table;
                    // Specify an array containing column headings for a table.
                    string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4", "Yearly Total" };

                    // 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[5].TotalRowFunction = XlTotalRowFunction.Sum;
                        // Specify the number format for numeric values in the table and the total cell of the "Yearly Total" column.
                        XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
                        table.DataFormatting = accounting;
                        table.Columns[5].TotalRowFormatting = accounting;
                        // Set the formula to calculate annual sales of each product
                        // and display results in the "Yearly Total" column.
                        table.Columns[5].SetFormula(XlFunc.Sum(table.GetRowReference("Q1", "Q4")));
                    }

                    // Generate table rows and populate them with data.
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Camembert Pierrot", 17000, 18500, 17500, 18000 }, null);
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Gnocchi di nonna Alice", 15500, 14500, 15000, 14000 }, null);
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Mascarpone Fabioli", 15000, 15750, 16000, 15500 }, null);
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Ravioli Angelo", 12500, 11000, 13500, 12000 }, null);

                    // Create the total row and finish the table.
                    using (IXlRow row = sheet.CreateRow())
                        row.EndTable(table, true);
                    #endregion #CalculatedColumn
                }
            }
        }
Exemple #5
0
        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
                }
            }
        }
Exemple #6
0
        static void TableStyleOptions(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 #TableStyleOptions
                    IXlTable table;
                    // Specify an array containing column headings for tables.
                    string[] columnNames = new string[] { "Product", "Category", "Amount" };

                    // Create the row containing the table title.
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Disable banded rows" }, XlCellFormatting.Title);
                    sheet.SkipRows(1);

                    // Start generating the table with a header row displayed.
                    using (IXlRow row = sheet.CreateRow())
                    {
                        table = row.BeginTable(columnNames, true);
                        // Disable banded row formatting for the table.
                        table.Style.ShowRowStripes = false;
                    }
                    // 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);
                    }
                    sheet.SkipRows(1);

                    // Create the row containing the table title.
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Enable banded columns" }, XlCellFormatting.Title);
                    sheet.SkipRows(1);

                    // Start generating the table with a header row displayed.
                    using (IXlRow row = sheet.CreateRow())
                    {
                        table = row.BeginTable(columnNames, true);
                        // Apply banded column formatting to the table.
                        table.Style.ShowRowStripes    = false;
                        table.Style.ShowColumnStripes = true;
                    }
                    // 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);
                    }
                    sheet.SkipRows(1);

                    // Create the row containing the table title.
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Highlight first column" }, XlCellFormatting.Title);
                    sheet.SkipRows(1);

                    // Start generating the table with a header row displayed.
                    using (IXlRow row = sheet.CreateRow())
                    {
                        table = row.BeginTable(columnNames, true);
                        // Display special formatting for the first column of the table.
                        table.Style.ShowFirstColumn = true;
                    }
                    // 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);
                    }
                    sheet.SkipRows(1);

                    // Create the row containing the table title.
                    using (IXlRow row = sheet.CreateRow())
                        row.BulkCells(new object[] { "Highlight last column" }, XlCellFormatting.Title);
                    sheet.SkipRows(1);

                    // Start generating the table with a header row displayed.
                    using (IXlRow row = sheet.CreateRow())
                    {
                        table = row.BeginTable(columnNames, true);
                        // Display special formatting for the last column of the table.
                        table.Style.ShowLastColumn = true;
                    }
                    // 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 #TableStyleOptions
                }
            }
        }