ExpertXls Excel Spreadsheet Library for .NET

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Drawing;
using ExpertXls.ExcelLib;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    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, 90, 90);
        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, 90, 90);
        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, 90, 90);
        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, 90, 90);
        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, 90, 90);
        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, 90, 90);
        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, 90, 90);
        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, 90, 90);
        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

        // evaluate workbook formulas
        workbook.UpdateFormulaResult();

        // 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-2013 (.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();
       
    }
}