Click or drag to resize

ExpertXls - Full Code Sample - Generate Excel Spreadsheets from scratch using a C#/VB.NET Library

The code below is the Getting Started demo of the samples (Features\GettingStartedDemo.cs and GettingStartedDemo.vb). It creates a workbook from scratch in the Excel 97-2003 or Excel 2007 or later format: named cell styles, page setup with a header and footer, merged cells, pictures, a date value with a number format, a second worksheet with a table, formulas and a chart, a named range, a hyperlink to that range and a hyperlink to a web page. The workbook is saved to a stream: a file, a memory stream or an HTTP response.

Call it with the workbook format, the folder that contains the Images folder (logo.jpg and the flag images) and the output stream:

using (FileStream stream = File.Create("GettingStarted.xlsx"))
    ExcelLibraryDemos.GettingStartedDemo.CreateWorkbook(ExcelWorkbookFormat.Xlsx_2007, AppDomain.CurrentDomain.BaseDirectory, stream);
Code
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;

namespace ExcelLibraryDemos
{
    /// <summary>
    /// Getting started: creates a workbook from scratch with named styles, page setup, merged cells, pictures, a
    /// formula, a chart, a named range and hyperlinks. This demo writes everything inline; the other demos reuse the
    /// page setup and title code from DemoLayout.
    /// </summary>
    public static class GettingStartedDemo
    {
        /// <summary>The name of the generated file, without extension.</summary>
        public const string OutputName = "GettingStarted";

        // the greetings shown in the first worksheet, with the flag image of each language and the text color
        private static readonly string[] Greetings = { "Hello World !!!", "Bonjour tout le monde !!!", "Hallo Welt",
            "Hola Mundo", "Привет мир", "Ciao a tutti", "Hallo Wereld", "Olá Mundo" };
        private static readonly string[] Languages = { "English", "French", "German", "Spanish", "Russian", "Italian",
            "Dutch", "Portuguese" };
        private static readonly string[] FlagImages = { "us.png", "fr.png", "de.png", "es.png", "ru.png", "it.png",
            "nl.png", "pt.png" };
        private static readonly Color[] TextColors = { Color.Black, Color.Blue, Color.Red, Color.Orange, Color.Indigo,
            Color.Green, Color.Blue, Color.Red };

        /// <summary>
        /// Creates the workbook in the given format and saves it to the output stream.
        /// </summary>
        /// <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
        /// <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
        /// <param name="output">The stream that receives the workbook: a file, a memory stream or an HTTP response.</param>
        public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
        {
            string imagesFolder = Path.Combine(inputFolder, "Images");

            // a new workbook has one empty worksheet
            ExcelWorkbook workbook = new ExcelWorkbook(format);
            // set the license key before you save the workbook, otherwise it is created in evaluation mode
            // workbook.LicenseKey = "your license key";

            // the document properties are shown by Excel in File > Info
            workbook.DocumentProperties.Title = "Getting started";
            workbook.DocumentProperties.Subject = "Getting started with ExpertXls";
            workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";

            // the images must stay alive until the workbook is saved
            Image logo = Image.FromFile(Path.Combine(imagesFolder, "logo.jpg"));
            Image[] flags = new Image[FlagImages.Length];
            try
            {
                // NAMED STYLES: defined once, applied to many cells

                ExcelCellStyle titleStyle = workbook.Styles.AddStyle("WorksheetTitleStyle");
                titleStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
                titleStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
                titleStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Medium;
                titleStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Medium;
                titleStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Medium;
                titleStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Medium;
                titleStyle.Borders.Color = Color.Green;
                titleStyle.Font.Size = 14;
                titleStyle.Font.Bold = true;
                titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single;
                if (format == ExcelWorkbookFormat.Xls_2003)
                {
                    // the Excel 97-2003 format has no gradient cell fills
                    titleStyle.Fill.FillType = ExcelCellFillType.SolidFill;
                    titleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
                }
                else
                {
                    titleStyle.Fill.FillType = ExcelCellFillType.GradientFill;
                    titleStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204);
                    titleStyle.Fill.GradientFillOptions.Color2 = Color.White;
                }

                ExcelCellStyle indexStyle = workbook.Styles.AddStyle("IndexColumnStyle");
                indexStyle.Font.Size = 12;
                indexStyle.Fill.FillType = ExcelCellFillType.SolidFill;
                indexStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
                indexStyle.Borders.LineStyle = ExcelCellLineStyle.Thin;

                ExcelCellStyle languageStyle = workbook.Styles.AddStyle("LanguageNameStyle");
                languageStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
                languageStyle.Font.Size = 12;
                languageStyle.Font.Bold = true;
                languageStyle.Borders.LineStyle = ExcelCellLineStyle.Dot;
                languageStyle.Fill.FillType = ExcelCellFillType.PatternFill;
                languageStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_12_5_Percent;
                languageStyle.Fill.PatternFillOptions.BackColor = Color.White;
                languageStyle.Fill.PatternFillOptions.PatternColor = Color.Green;

                ExcelCellStyle greetingStyle = workbook.Styles.AddStyle("GreetingStyle");
                greetingStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
                greetingStyle.Font.Size = 14;
                greetingStyle.Font.Bold = true;
                greetingStyle.Borders.LineStyle = ExcelCellLineStyle.Hair;
                if (format == ExcelWorkbookFormat.Xls_2003)
                {
                    greetingStyle.Fill.FillType = ExcelCellFillType.PatternFill;
                    greetingStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
                    greetingStyle.Fill.PatternFillOptions.BackColor = Color.White;
                    greetingStyle.Fill.PatternFillOptions.PatternColor = Color.Orange;
                }
                else
                {
                    greetingStyle.Fill.FillType = ExcelCellFillType.GradientFill;
                    greetingStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical;
                    greetingStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153);
                    greetingStyle.Fill.GradientFillOptions.Color2 = Color.White;
                }

                // FIRST WORKSHEET: "Hello World" in several languages

                ExcelWorksheet worksheet = workbook.Worksheets[0];
                worksheet.Name = "Hello World";

                // the page setup is used when the worksheet is printed; the header and footer use the Excel codes
                // &G picture, &A sheet name, &F file name, &P page number, &D date
                worksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
                worksheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;
                worksheet.PageSetup.LeftMargin = 1;
                worksheet.PageSetup.RightMargin = 1;
                worksheet.PageSetup.TopMargin = 1;
                worksheet.PageSetup.BottomMargin = 1;
                worksheet.PageSetup.LeftHeaderFormat = "&G";
                worksheet.PageSetup.LeftHeaderPicture = logo;
                worksheet.PageSetup.RightHeaderFormat = "&A";
                worksheet.PageSetup.LeftFooterFormat = "&F";
                worksheet.PageSetup.CenterFooterFormat = "&P";
                worksheet.PageSetup.RightFooterFormat = "&D";

                // a merged range shows one value, stored in its top left cell
                ExcelRange titleRange = worksheet["A2:G3"];
                titleRange.Merge();
                worksheet["A2"].Text = "Say 'Hello World' in Different Languages";
                titleRange.RowHeightInPoints = 18;
                titleRange.Style = titleStyle;

                // narrow separator column B and flag column F; the column indexes are one based
                worksheet.SetColumnWidthInChars(2, 3);
                worksheet.SetColumnWidthInChars(6, 5);

                for (int i = 0; i < Greetings.Length; i++)
                {
                    int row = 5 + 2 * i;

                    worksheet[row, 1].Value = i + 1;
                    worksheet[row, 1].Style = indexStyle;

                    worksheet[row, 3].Text = Greetings[i];
                    worksheet[row, 3].Style = greetingStyle;
                    // changing the style of one cell does not change the named style used by the other cells
                    worksheet[row, 3].Style.Font.Color = TextColors[i];
                    worksheet[row, 3].RowHeightInPoints = 19.5;

                    // the picture is placed with its top left corner in column F of this row
                    flags[i] = Image.FromFile(Path.Combine(imagesFolder, FlagImages[i]));
                    worksheet.Pictures.AddPicture(6, row, flags[i]);
                    worksheet[row, 6, row, 7].StyleName = "LanguageNameStyle";
                    worksheet[row, 7].Text = Languages[i];
                }

                // fit the columns to their content
                worksheet.AutofitColumn(1);
                worksheet.AutofitColumn(3);
                worksheet.AutofitColumn(7);

                // a date value is shown with the number format of its cell
                worksheet["A28:C28"].Merge();
                worksheet["A28"].Text = "Workbook Creation Date & Time:";
                worksheet["A28"].Style.Font.Bold = true;
                worksheet["D28:G28"].Merge();
                worksheet["D28"].Value = DateTime.Now;
                worksheet["D28:G28"].Style.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss";
                worksheet["D28:G28"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
                worksheet["D28:G28"].Style.Font.Color = Color.DarkBlue;
                worksheet["D28:G28"].Style.Font.Bold = true;

                // SECOND WORKSHEET: a table of values, a formula and a chart

                ExcelWorksheet chartWorksheet = workbook.Worksheets.AddWorksheet("Simple Chart");
                chartWorksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
                chartWorksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape;
                chartWorksheet.PageSetup.LeftHeaderFormat = "&G";
                chartWorksheet.PageSetup.LeftHeaderPicture = logo;
                chartWorksheet.PageSetup.RightHeaderFormat = "&A";

                ExcelRange chartTitleRange = chartWorksheet["A2:G3"];
                chartTitleRange.Merge();
                chartWorksheet["A2"].Text = "Simple Chart Demo";
                chartTitleRange.RowHeightInPoints = 18;
                chartTitleRange.Style = titleStyle;

                // the first row of the table holds the category names and the first column the series names
                string[] products = { "HTML to PDF", "PDF Merge", "PDF Security", "Web Chart", "Excel Library" };
                string[] quarters = { "First Quarter", "Second Quarter", "Third Quarter", "Fourth Quarter" };
                int[,] unitsSold = { { 1000, 500, 200, 400, 800 }, { 850, 680, 350, 230, 640 },
                    { 950, 450, 175, 350, 520 }, { 500, 700, 250, 460, 320 } };

                for (int p = 0; p < products.Length; p++)
                {
                    chartWorksheet[7, 3 + p].Text = products[p];
                    for (int q = 0; q < quarters.Length; q++)
                        chartWorksheet[8 + q, 3 + p].Value = unitsSold[q, p];
                    // the formula is saved in the workbook and calculated by Excel when the file is opened
                    char column = (char)('C' + p);
                    chartWorksheet[12, 3 + p].Formula = String.Format("=SUM({0}8:{0}11)", column);
                }
                for (int q = 0; q < quarters.Length; q++)
                    chartWorksheet[8 + q, 2].Text = quarters[q];
                chartWorksheet["B12"].Text = "Yearly Total";

                chartWorksheet["B7:G7"].Style.Font.Bold = true;
                chartWorksheet["B7:G7"].ColumnWidthInChars = 16;
                chartWorksheet["B7:G7"].Style.Fill.FillType = ExcelCellFillType.SolidFill;
                chartWorksheet["B7:G7"].Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
                chartWorksheet["B8:B12"].Style.Font.Bold = true;
                chartWorksheet["C12:G12"].Style.Font.Bold = true;
                chartWorksheet["C12:G12"].Style.Font.Color = Color.Blue;

                // the series of the chart are created from the rows of the data source range
                ExcelChart chart = chartWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered,
                    chartWorksheet["B7:G12"], true, 2, 15, 8, 32);
                chart.Title.Text = "Product Units Sold per Quarter";
                chart.Title.Font.Size = 12;
                chart.Title.Font.Color = Color.DarkBlue;
                chart.ValueAxis.Title.Text = "Units sold";
                chart.CategoryAxis.Title.Text = "Analyzed products";
                if (format == ExcelWorkbookFormat.Xlsx_2007)
                {
                    // preset gradients for shapes are an Excel 2007+ feature
                    chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
                    chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
                    chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;
                }
                // show the value of each column of the "Yearly Total" series
                chart.Series["Yearly Total"].DataPoints.All.Label.ContainsValue = true;

                // NAMED RANGE AND HYPERLINKS

                // a hyperlink to a place in the workbook targets a named range
                workbook.NamedRanges.AddNamedRange(chartWorksheet["A1"], "ChartWorksheet");
                worksheet["A23:C23"].Merge();
                ExcelHyperlink sheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheet["A23"],
                    "ChartWorksheet");
                sheetLink.Text = "Go to the chart worksheet";
                sheetLink.ToolTip = "Go to the chart worksheet";

                worksheet["A25:C25"].Merge();
                ExcelHyperlink webLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, worksheet["A25"],
                    "https://www.html-to-pdf.info/excel-library.aspx");
                webLink.Text = "Visit the ExpertXls website";
                webLink.ToolTip = "https://www.html-to-pdf.info/excel-library.aspx";

                // SAVE: the format of the output is the format the workbook was created with
                workbook.Save(output);
            }
            finally
            {
                // Close releases the resources of the workbook; the images can be disposed after the save
                workbook.Close();
                logo.Dispose();
                foreach (Image flag in flags)
                {
                    if (flag != null)
                        flag.Dispose();
                }
            }
        }
    }
}
See Also

Other Resources

Installation