Click or drag to resize

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

Below there is a full code sample illustrating the main features of the ExpertXls Excel Library for .NET.

Code Samples

The code sample below was taken from the "Getting Started" sample available in the Samples\AspNet\CS\ExcelLibraryFeaturesDemo\Default.aspx.cs file.:

Copy
protected void lnkBtnCreateWorkbook_Click(object sender, EventArgs e)
{
    // get the Excel workbook format
    ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;

    // create the workbook in the desired format with a single worksheet
    ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);

    // set the license key before saving the workbook
    //workbook.LicenseKey = "your license key here";

    // set workbook description properties
    workbook.DocumentProperties.Subject = "Getting started sample";
    workbook.DocumentProperties.Comments = "Getting started with ExpertXls Excel library for .NET";

    #region CREATE CUSTOM WORKBOOK STYLES

    #region Add a style used for the cells in the worksheet title area

    ExcelCellStyle titleStyle = workbook.Styles.AddStyle("WorksheetTitleStyle");
    // center the text in the title area
    titleStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
    titleStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    // set the title area borders
    titleStyle.Borders[ExcelCellBorderIndex.Bottom].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Medium;
    titleStyle.Borders[ExcelCellBorderIndex.Top].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Medium;
    titleStyle.Borders[ExcelCellBorderIndex.Left].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Medium;
    titleStyle.Borders[ExcelCellBorderIndex.Right].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Medium;
    if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
    {
        // set the solid fill for the title area range with a custom color
        titleStyle.Fill.FillType = ExcelCellFillType.SolidFill;
        titleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
    }
    else
    {
        // set the gradient fill for the title area range with a custom color
        titleStyle.Fill.FillType = ExcelCellFillType.GradientFill;
        titleStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204);
        titleStyle.Fill.GradientFillOptions.Color2 = Color.White;
    }
    // set the title area font 
    titleStyle.Font.Size = 14;
    titleStyle.Font.Bold = true;
    titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single;

    #endregion

    #region Add a style used for all the cells in the index column

    ExcelCellStyle indexStyle = workbook.Styles.AddStyle("IndexColumnStyle");
    indexStyle.Font.Size = 12;
    indexStyle.Font.Bold = false;
    indexStyle.Fill.FillType = ExcelCellFillType.SolidFill;
    indexStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
    indexStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Thin;
    indexStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Thin;
    indexStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Thin;
    indexStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Thin;

    #endregion

    #region Add a style used for all the cells in the country name column

    ExcelCellStyle countryNameStyle = workbook.Styles.AddStyle("CountryNameStyle");
    countryNameStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
    countryNameStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    countryNameStyle.Font.Size = 12;
    countryNameStyle.Font.Bold = true;
    countryNameStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Dot;
    countryNameStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Dot;
    countryNameStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Dot;
    countryNameStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Dot;

    countryNameStyle.Fill.FillType = ExcelCellFillType.PatternFill;
    countryNameStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_12_5_Percent;
    countryNameStyle.Fill.PatternFillOptions.BackColor = Color.White;
    countryNameStyle.Fill.PatternFillOptions.PatternColor = Color.Green;

    #endregion

    #region Add a style used for all the cells containing the hello world text

    ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle("HelloWorldStyle");
    helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
    helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    helloWorldStyle.Font.Size = 14;
    helloWorldStyle.Font.Bold = true;

    helloWorldStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Hair;
    helloWorldStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Hair;
    helloWorldStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Hair;
    helloWorldStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Hair;

    if (workbook.Format == ExcelWorkbookFormat.Xls_2003)
    {
        helloWorldStyle.Fill.FillType = ExcelCellFillType.PatternFill;
        helloWorldStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
        helloWorldStyle.Fill.PatternFillOptions.BackColor = Color.White;
        helloWorldStyle.Fill.PatternFillOptions.PatternColor = Color.Orange;
    }
    else
    {
        helloWorldStyle.Fill.FillType = ExcelCellFillType.GradientFill;
        helloWorldStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical;
        helloWorldStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153);
        helloWorldStyle.Fill.GradientFillOptions.Color2 = Color.White;
    }


    #endregion

    #endregion

    // get the first worksheet in the workbook
    ExcelWorksheet worksheet = workbook.Worksheets[0];

    // set the default worksheet name
    worksheet.Name = "Hello World";

    #region WORKSHEET PAGE SETUP

    // set worksheet paper size and orientation, margins, header and footer
    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;

    // add header and footer

    //display a logo image in the left part of the header
    string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
    System.Drawing.Image logoImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "logo.jpg"));
    worksheet.PageSetup.LeftHeaderFormat = "&G";
    worksheet.PageSetup.LeftHeaderPicture = logoImg;
    // display worksheet name in the right part of the header
    worksheet.PageSetup.RightHeaderFormat = "&A";

    // add worksheet header and footer
    // display the page number in the center part of the footer
    worksheet.PageSetup.CenterFooterFormat = "&P";
    // display the workbook file name in the left part of the footer
    worksheet.PageSetup.LeftFooterFormat = "&F";
    // display the current date in the right part of the footer
    worksheet.PageSetup.RightFooterFormat = "&D";

    #endregion

    #region WRITE THE WORKSHEET TOP TITLE

    // merge the cells in the range to create the title area 
    worksheet["A2:G3"].Merge();
    // gets the merged range containing the top left cell of the range
    ExcelRange titleRange = worksheet["A2"].MergeArea;
    // set the text of title area
    worksheet["A2"].Text = "Say 'Hello World' in Different Languages";

    // set a row height of 18 points for each row in the range
    titleRange.RowHeightInPoints = 18;
    // set the worksheet top title style
    titleRange.Style = titleStyle;

    #endregion

    #region WRITE 'HELLO WORLD' IN DIFFERENT LANGUAGES

    System.Drawing.Image usFlagImg = null;
    System.Drawing.Image frFlagImg = null;
    System.Drawing.Image deFlagImg = null;
    System.Drawing.Image esFlagImg = null;
    System.Drawing.Image ruFlagImg = null;
    System.Drawing.Image itFlagImg = null;
    System.Drawing.Image ptFlagImg = null;
    System.Drawing.Image nlFlagImg = null;

    // set the separator between index column and text column
    worksheet.SetColumnWidthInChars(2, 3);
    // set the country image column
    worksheet.SetColumnWidthInChars(6, 5);

    #region Say hello world in English

    worksheet["A5"].Style = indexStyle;
    worksheet["A5"].Value = 1;

    worksheet["C5"].Text = "Hello World !!!";
    worksheet["C5"].Style = helloWorldStyle;
    worksheet["C5"].RowHeightInPoints = 19.5;

    usFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "us.png"));
    worksheet.Pictures.AddPicture(6, 5, usFlagImg);
    worksheet[5, 6, 5, 7].StyleName = "CountryNameStyle";
    worksheet[5, 7].Text = "English";

    #endregion

    #region  Say hello world in French

    worksheet["A7"].Style = indexStyle;
    worksheet["A7"].Value = 2;

    worksheet["C7"].Text = "Bonjour tout le monde!!!";
    worksheet["C7"].Style = helloWorldStyle;
    worksheet["C7"].Style.Font.Color = Color.Blue;
    worksheet["C7"].RowHeightInPoints = 19.5;

    frFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "fr.png"));
    worksheet.Pictures.AddPicture(6, 7, frFlagImg);
    worksheet[7, 6, 7, 7].StyleName = "CountryNameStyle";
    worksheet[7, 7].Text = "French";

    #endregion

    #region Say hello world in German

    worksheet["A9"].Style = indexStyle;
    worksheet["A9"].Value = 3;

    worksheet["C9"].Text = "Hallo Welt";
    worksheet["C9"].Style = helloWorldStyle;
    worksheet["C9"].Style.Font.Color = Color.Red;
    worksheet["C9"].RowHeightInPoints = 19.5;

    deFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "de.png"));
    worksheet.Pictures.AddPicture(6, 9, deFlagImg);
    worksheet[9, 6, 9, 7].StyleName = "CountryNameStyle";
    worksheet[9, 7].Text = "German";

    #endregion

    #region Say hello world in Spanish

    worksheet["A11"].Style = indexStyle;
    worksheet["A11"].Value = 4;

    worksheet["C11"].Text = "Hola Mundo";
    worksheet["C11"].Style = helloWorldStyle;
    worksheet["C11"].Style.Font.Color = Color.Orange;
    worksheet["C11"].RowHeightInPoints = 19.5;

    esFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "es.png"));
    worksheet.Pictures.AddPicture(6, 11, esFlagImg);
    worksheet[11, 6, 11, 7].StyleName = "CountryNameStyle";
    worksheet[11, 7].Text = "Spanish";

    #endregion

    #region Say hello world in Russian

    worksheet["A13"].Style = indexStyle;
    worksheet["A13"].Value = 5;

    worksheet["C13"].Text = "Привет мир";
    worksheet["C13"].Style = helloWorldStyle;
    worksheet["C13"].Style.Font.Color = Color.Indigo;
    worksheet["C13"].RowHeightInPoints = 19.5;

    ruFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "ru.png"));
    worksheet.Pictures.AddPicture(6, 13, ruFlagImg);
    worksheet[13, 6, 13, 7].StyleName = "CountryNameStyle";
    worksheet[13, 7].Text = "Russian";

    #endregion

    #region Say hello world in Italian

    worksheet["A15"].Style = indexStyle;
    worksheet["A15"].Value = 6;

    worksheet["C15"].Text = "Ciao a tutti";
    worksheet["C15"].Style = helloWorldStyle;
    worksheet["C15"].Style.Font.Color = Color.Green;
    worksheet["C15"].RowHeightInPoints = 19.5;

    itFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "it.png"));
    worksheet.Pictures.AddPicture(6, 15, itFlagImg);
    worksheet[15, 6, 15, 7].StyleName = "CountryNameStyle";
    worksheet[15, 7].Text = "Italian";

    #endregion

    #region Say hello world in Dutch

    worksheet["A17"].Style = indexStyle;
    worksheet["A17"].Value = 7;


    worksheet["C17"].Text = "Hallo Wereld";
    worksheet["C17"].Style = helloWorldStyle;
    worksheet["C17"].Style.Font.Color = Color.Blue;
    worksheet["C17"].RowHeightInPoints = 19.5;

    nlFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "nl.png"));
    worksheet.Pictures.AddPicture(6, 17, nlFlagImg);
    worksheet[17, 6, 17, 7].StyleName = "CountryNameStyle";
    worksheet[17, 7].Text = "Dutch";

    #endregion

    #region Say hello world in Portuguese

    worksheet["A19"].Style = indexStyle;
    worksheet["A19"].Value = 8;

    worksheet["C19"].Text = "Olá Mundo";
    worksheet["C19"].Style = helloWorldStyle;
    worksheet["C19"].Style.Font.Color = Color.Red;
    worksheet["C19"].RowHeightInPoints = 19.5;

    ptFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "pt.png"));
    worksheet.Pictures.AddPicture(6, 19, ptFlagImg);
    worksheet[19, 6, 19, 7].StyleName = "CountryNameStyle";
    worksheet[19, 7].Text = "Portuguese";

    #endregion

    // autofit the index column width
    worksheet.AutofitColumn(1);
    // autofit the Hello World text column
    worksheet.AutofitColumn(3);
    // autofit the country name column
    worksheet.AutofitColumn(7);

    #endregion

    #region WRITE WORKSHEET CREATION TIMESTAMP

    worksheet[28, 1, 28, 3].Merge();
    ExcelRange timestampTextRange = worksheet[28, 1].MergeArea;
    timestampTextRange.Style.Font.Bold = true;
    worksheet[28, 1].Text = "Workbook Creation Date & Time:";

    worksheet[28, 4, 28, 7].Merge();
    ExcelRange timestampDateRange = worksheet[28, 4].MergeArea;
    timestampDateRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
    timestampDateRange.Style.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss";
    timestampDateRange.Style.Font.Color = Color.DarkBlue;
    timestampDateRange.Style.Font.Bold = true;
    worksheet[28, 4].Value = DateTime.Now;

    #endregion

    #region ADD A SECOND WORKSHEET TO THE WORKBOOK

    ExcelWorksheet secondWorksheet = workbook.Worksheets.AddWorksheet("Simple Chart");

    #region SECOND WORKSHEET PAGE SETUP

    // set worksheet paper size and orientation, margins, header and footer
    secondWorksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
    secondWorksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape;
    secondWorksheet.PageSetup.LeftMargin = 1;
    secondWorksheet.PageSetup.RightMargin = 1;
    secondWorksheet.PageSetup.TopMargin = 1;
    secondWorksheet.PageSetup.BottomMargin = 1;

    // add header and footer

    //display a logo image in the left part of the header
    secondWorksheet.PageSetup.LeftHeaderFormat = "&G";
    secondWorksheet.PageSetup.LeftHeaderPicture = logoImg;
    // display worksheet name in the right part of the header
    secondWorksheet.PageSetup.RightHeaderFormat = "&A";

    // add worksheet header and footer
    // display the page number in the center part of the footer
    secondWorksheet.PageSetup.CenterFooterFormat = "&P";
    // display the workbook file name in the left part of the footer
    secondWorksheet.PageSetup.LeftFooterFormat = "&F";
    // display the current date in the right part of the footer
    secondWorksheet.PageSetup.RightFooterFormat = "&D";

    #endregion

    #region WRITE THE SECOND WORKSHEET TOP TITLE

    // merge the cells in the range to create the title area 
    secondWorksheet["A2:G3"].Merge();
    // gets the merged range containing the top left cell of the range
    ExcelRange secondTitleRange = secondWorksheet["A2"].MergeArea;
    // set the text of title area
    secondWorksheet["A2"].Text = "Simple Chart Demo";

    // set a row height of 18 points for each row in the range
    secondTitleRange.RowHeightInPoints = 18;
    // set the worksheet top title style
    secondTitleRange.Style = titleStyle;


    #endregion

    #region CREATE DATA TABLE FOR THE CHART IN SECOND WORKSHEET

    ExcelCellStyle chartValuesStyle = workbook.Styles.AddStyle("ChartValuesStyle");
    chartValuesStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
    chartValuesStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    chartValuesStyle.Font.Color = Color.Black;
    chartValuesStyle.Font.Bold = true;
    if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
    {
        chartValuesStyle.Fill.FillType = ExcelCellFillType.PatternFill;
        chartValuesStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
        chartValuesStyle.Fill.PatternFillOptions.BackColor = Color.White;
        chartValuesStyle.Fill.PatternFillOptions.PatternColor = Color.Green;
    }
    else
    {
        chartValuesStyle.Fill.FillType = ExcelCellFillType.GradientFill;
        chartValuesStyle.Fill.GradientFillOptions.Color1 = Color.LightGreen;
        chartValuesStyle.Fill.GradientFillOptions.Color2 = Color.White;
        chartValuesStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.DiagonlDown;
    }


    // set the products tile row text and style 
    secondWorksheet["C6:G6"].Merge();
    secondWorksheet["C6"].Text = "Analyzed Products";
    ExcelRange productsTitle = secondWorksheet["C6"].MergeArea;
    productsTitle.RowHeightInPoints = 21;
    productsTitle.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
    productsTitle.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    productsTitle.Style.Font.Size = 12;
    productsTitle.Style.Font.Bold = true;
    productsTitle.Style.Fill.FillType = ExcelCellFillType.PatternFill;
    productsTitle.Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_25_Percent;
    productsTitle.Style.Fill.PatternFillOptions.BackColor = Color.White;
    productsTitle.Style.Fill.PatternFillOptions.PatternColor = Color.Orange;

    // set the quarters title column text and style
    secondWorksheet["A8:A11"].Merge();
    secondWorksheet["A8"].Text = "Units Sold per Quarter";
    ExcelRange quartersTitle = secondWorksheet["A8"].MergeArea;
    // set vertical orientation for the text from bottom to top
    quartersTitle.Style.Alignment.Orientation = 90;
    // wrap text inside the merged range
    quartersTitle.Style.Alignment.WrapText = true;
    quartersTitle.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
    quartersTitle.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    quartersTitle.Style.Font.Size = 12;
    quartersTitle.Style.Font.Bold = true;
    quartersTitle.Style.Fill.FillType = ExcelCellFillType.PatternFill;
    quartersTitle.Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_25_Percent;
    quartersTitle.Style.Fill.PatternFillOptions.BackColor = Color.White;
    quartersTitle.Style.Fill.PatternFillOptions.PatternColor = Color.Green;

    // set the style for the product names row
    ExcelRange productNamesRange = secondWorksheet["B7:G7"];
    productNamesRange.RowHeightInPoints = 21;
    productNamesRange.ColumnWidthInChars = 16;
    productNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
    productNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    productNamesRange.Style.Font.Bold = true;
    productNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
    productNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);


    // set the styles for the quarter names range
    ExcelRange quarterNamesRange = secondWorksheet["B8:B11"];
    quarterNamesRange.RowHeightInPoints = 16.5;
    quarterNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right;
    quarterNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    quarterNamesRange.Style.Font.Bold = true;
    quarterNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
    quarterNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);



    // set the row height for the Yearly Total row
    ExcelRange yearlyTotalRange = secondWorksheet["B12:G12"];
    yearlyTotalRange.RowHeightInPoints = 21;
    yearlyTotalRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
    yearlyTotalRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    yearlyTotalRange.Style.Font.Color = Color.Blue;
    yearlyTotalRange.Style.Font.Bold = true;
    yearlyTotalRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
    yearlyTotalRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(204, 255, 255);

    // set the series name column
    secondWorksheet["B8"].Text = "First Quarter";
    secondWorksheet["B9"].Text = "Second Quarter";
    secondWorksheet["B10"].Text = "Third Quarter";
    secondWorksheet["B11"].Text = "Fourth Quarter";
    secondWorksheet["B12"].Text = "Yearly Total";

    // set the categories name row
    secondWorksheet["C7"].Text = "HTML to PDF";
    secondWorksheet["D7"].Text = "PDF Merge";
    secondWorksheet["E7"].Text = "PDF Security";
    secondWorksheet["F7"].Text = "Web Chart";
    secondWorksheet["G7"].Text = "Excel Library";

    // set the chart value style

    ExcelRange chartValuesRange = secondWorksheet["C8:G11"];
    chartValuesRange.Style = chartValuesStyle;

    // set the chart values

    secondWorksheet["C8"].Value = 1000;
    secondWorksheet["D8"].Value = 500;
    secondWorksheet["E8"].Value = 200;
    secondWorksheet["F8"].Value = 400;
    secondWorksheet["G8"].Value = 800;

    secondWorksheet["C9"].Value = 850;
    secondWorksheet["D9"].Value = 680;
    secondWorksheet["E9"].Value = 350;
    secondWorksheet["F9"].Value = 230;
    secondWorksheet["G9"].Value = 640;

    secondWorksheet["C10"].Value = 950;
    secondWorksheet["D10"].Value = 450;
    secondWorksheet["E10"].Value = 175;
    secondWorksheet["F10"].Value = 350;
    secondWorksheet["G10"].Value = 520;

    secondWorksheet["C11"].Value = 500;
    secondWorksheet["D11"].Value = 700;
    secondWorksheet["E11"].Value = 250;
    secondWorksheet["F11"].Value = 460;
    secondWorksheet["G11"].Value = 320;

    secondWorksheet["C12"].Formula = "=SUM(C8:C11)";
    secondWorksheet["D12"].Formula = "=SUM(D8:D11)";
    secondWorksheet["E12"].Formula = "=SUM(E8:E11)";
    secondWorksheet["F12"].Formula = "=SUM(F8:F11)";
    secondWorksheet["G12"].Formula = "=SUM(G8:G11)";

    // auto fit the width of the quarter names column
    secondWorksheet["B7"].AutofitColumns();

    #endregion

    #region ADD A CHART TO THE SECOND WORKSHEET

    ExcelRange dataSourceRange = secondWorksheet["B7:G12"];
    ExcelChart chart = secondWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered, dataSourceRange, true, 2, 15, 8, 32);
    chart.ShowDataTable = false;

    // set chart title
    chart.Title.Text = "Product Units Sold per Quarter";
    chart.Title.Interior.FillType = ExcelShapeFillType.NoFill;
    chart.Title.Font.Size = 12;
    chart.Title.Font.Color = Color.DarkBlue;

    // set chart area style
    if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
    {
        chart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
        chart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);

        chart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill;
        chart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
    }
    else
    {
        chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
        chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
        chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;

        chart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill;
        chart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery;
    }

    // set value axis title
    chart.ValueAxis.Title.Text = "Units sold";
    chart.ValueAxis.Title.Font.Size = 10;
    chart.ValueAxis.Title.Font.Bold = true;

    // set value axis text style
    chart.ValueAxis.Font.Size = 8;
    chart.ValueAxis.Font.Bold = false;
    chart.ValueAxis.Font.Italic = true;
    chart.ValueAxis.ShowVerticalTitleText();

    // set category axis title
    chart.CategoryAxis.Title.Text = "Analyzed products";
    chart.CategoryAxis.Title.Font.Size = 10;
    chart.CategoryAxis.Title.Font.Bold = true;

    // set category axis text style
    chart.CategoryAxis.Font.Size = 8;
    chart.CategoryAxis.Font.Bold = false;
    chart.CategoryAxis.Font.Italic = true;

    // set chart legend style
    chart.Legend.Interior.FillType = ExcelShapeFillType.SolidFill;
    chart.Legend.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);

    chart.Legend.Font.Size = 8;
    chart.Legend.Font.Bold = true;

    // show a label with total number of units sold in a year
    chart.Series["Yearly Total"].DataPoints.All.Label.ContainsValue = true;
    chart.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Size = 8;
    chart.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Italic = true;

    #endregion

    #endregion

    #region CREATE HYPERLINKS

    // create the merged range where to add the link to the second worksheet
    worksheet[23, 1, 23, 3].Merge();
    // create a named range used as target for the link to second worksheet
    ExcelNamedRange worksheetLinkTarget = workbook.NamedRanges.AddNamedRange(secondWorksheet["A1"], "SecondWorksheet");
    ExcelRange worksheetLinkSource = worksheet[23, 1];
    ExcelHyperlink secondWorksheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheetLinkSource, "SecondWorksheet");
    secondWorksheetLink.Text = "Go To Next Worksheet";
    secondWorksheetLink.ToolTip = "Go To Next Worksheet";


    // creat the merged range where to add the link to the product website
    worksheet[25, 1, 25, 3].Merge();
    // create a hyperlink to the product website
    ExcelRange websiteLinkSource = worksheet[25, 1];
    string websiteUrl = "http://www.html-to-pdf.net/excel-library.aspx";
    ExcelHyperlink websiteHyperlink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, websiteLinkSource, websiteUrl);
    websiteHyperlink.Text = "Visit product website";
    websiteHyperlink.ToolTip = "Visit product website";

    #endregion

    // SAVE THE WORKBOOK

    // Save the Excel document in the current HTTP response stream

    string outFileName = workbookFormat == ExcelWorkbookFormat.Xls_2003 ? "GettingStarted.xls" : "GettingStarted.xlsx";

    System.Web.HttpResponse httpResponse = System.Web.HttpContext.Current.Response;

    // Prepare the HTTP response stream for saving the Excel document

    // Clear any data that might have been previously buffered in the output stream
    httpResponse.Clear();

    // Set output stream content type for Excel 97-2003 (.xls) or Excel 2007-2019 (.xlsx)
    if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
        httpResponse.ContentType = "Application/x-msexcel"; 
    else
        httpResponse.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  

    // Add the HTTP header to announce the Excel document either as an attachment or inline
    httpResponse.AddHeader("Content-Disposition", String.Format("attachment; filename={0}", outFileName));

    // Save the workbook to the current HTTP response output stream
    // and close the workbook after save to release all the allocated resources
    try
    {
        workbook.Save(httpResponse.OutputStream);
    }
    catch (Exception ex)
    {
        // report any error that might occur during save
        Session["ErrorMessage"] = ex.Message;
        Response.Redirect("ErrorPage.aspx");
    }
    finally
    {
        // close the workbook and release the allocated resources
        workbook.Close();

        #region Dispose the Image object
        // release the flag images
        if (usFlagImg != null)
            usFlagImg.Dispose();
        if (frFlagImg != null)
            frFlagImg.Dispose();
        if (deFlagImg != null)
            deFlagImg.Dispose();
        if (esFlagImg != null)
            esFlagImg.Dispose();
        if (ruFlagImg != null)
            ruFlagImg.Dispose();
        if (itFlagImg != null)
            itFlagImg.Dispose();
        if (ptFlagImg != null)
            ptFlagImg.Dispose();
        if (nlFlagImg != null)
            nlFlagImg.Dispose();
        if (logoImg != null)
            logoImg.Dispose();

        #endregion
    }

    // End the response and finish the execution of this page
    httpResponse.End();

}