Winnovative Software Logo

 HTML to PDF Converter - Excel Library - ASP.NET Charts - RTF to PDF Converter
 PDF Merge and Split - PDF Security - PDF Viewers - PDF to Text - Images Extractor

  
Skip Navigation Links
 
Adding Excel Charts with Winnovative Excel Library for .NET
This sample shows how to add charts to a worksheet as embedded shapes and also how to add a chart worksheet to the workbook. The chart series can be automatically created from the specified data source range or the series can be manually added to the chart.
Getting Started
Cells and Ranges
Cell Styles
Excel Data Types
Excel Charts
Adding Images
Adding Formulas
Data Validation
Load/Get DataTable
Import from CSV
Edit Workbooks
To create the Excel workbook first select the format of the generated workbook and press the Create Workbook button. The Excel workbook will be created on the server and sent as an attachment to the browser. You will be prompted to open the generated workbook in an exernal viewer. The free Excel Viewer from Microsoft supports both the Excel 97-2003 (.xls) format and the new Excel 2007 (.xlsx) format.
Select Chart Type:
Series Data By:
Workbook Format:
Create Workbook Button Create Excel Workbook 




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 = "RW51ZXZ0ZXVldGt1ZXZ0a3R3a3x8fHw=";

    // set workbook description properties
    workbook.DocumentProperties.Subject = "Adding charts to a worksheet";
    workbook.DocumentProperties.Comments = 
                "Adding Excel charts with Winnovative 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 subtitles in the workbook

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

    subtitleStyle.Fill.FillType = ExcelCellFillType.SolidFill;
    subtitleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);

    #endregion



    #endregion

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

    // set the default worksheet name
    worksheet.Name = "Charts Demo";

    #region WORKSHEET PAGE SETUP

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

    // add header and footer

    string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
    System.Drawing.Image logoImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "logo.jpg"));

    //display a logo image in the left part of the header
    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 = "Excel Charts Demo";

    // 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 CREATE DATA TABLE FOR THE CHART

    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 
    worksheet["C6:G6"].Merge();
    worksheet["C6"].Text = "Analyzed Products";
    ExcelRange productsTitle = worksheet["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
    worksheet["A8:A11"].Merge();
    worksheet["A8"].Text = "Units Sold per Quarter";
    ExcelRange quartersTitle = worksheet["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 = worksheet["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 = worksheet["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 = worksheet["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
    worksheet["B8"].Text = "First Quarter";
    worksheet["B9"].Text = "Second Quarter";
    worksheet["B10"].Text = "Third Quarter";
    worksheet["B11"].Text = "Fourth Quarter";
    worksheet["B12"].Text = "Yearly Total";

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

    // set the chart value style

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

    // set the chart values

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

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

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

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

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

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

    #endregion

    ExcelChartType chartType = 
        ((ExcelChartItem)((rb2D.Checked ? Charts2D : Charts3D)[ddlChartTypes.SelectedIndex])).ChartType;

    #region ADD CHART SUBTITLE

    // merge the cells in the range to create the subtitle area 
    worksheet["B14:G14"].Merge();
    // gets the merged range containing the top left cell of the range
    ExcelRange subtitleRange = worksheet["B14"].MergeArea;
    // set the text of title area
    string chartDescription = 
        ((ExcelChartItem)((rb2D.Checked ? Charts2D : Charts3D)[ddlChartTypes.SelectedIndex])).Description;
    worksheet["B14"].Text = String.Format("{0}
        Chart - Automatically Generated Series", chartDescription);

    // set a row height of 18 points for each row in the range
    subtitleRange.RowHeightInPoints = 18;
    // set the worksheet top title style
    subtitleRange.Style = subtitleStyle;
    #endregion

    #region ADD A CHART WITH AUTOMATICALLY GENERATED SERIES TO THE WORKSHEET

    ExcelRange dataSourceRange = worksheet["B7:G12"];
    bool seriesDataByRows = rbSeriesByRows.Checked;

    ExcelChart chart = worksheet.Charts.AddChart(chartType, dataSourceRange, seriesDataByRows, 2, 16, 8, 33);

    // set chart title
    chart.Title.Text = "Product Units Sold per Quarter - Auto Generated
        Series";
    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;

    if (seriesDataByRows)
    {
        // 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;
    }
    else
    {
        // show a label with total number of units sold in a year

        foreach (ExcelChartSeries series in chart.Series)
        {
            int lastDataPointIndex = series.DataPoints.Count - 1;

            series.DataPoints[lastDataPointIndex].Label.ContainsValue = true;
            series.DataPoints[lastDataPointIndex].Label.LabelFormat.Font.Size = 8;
            series.DataPoints[lastDataPointIndex].Label.LabelFormat.Font.Italic = true;
        }
    }
    #endregion

    #region ADD CHART SUBTITLE

    // merge the cells in the range to create the subtitle area 
    worksheet["B34:G34"].Merge();
    // gets the merged range containing the top left cell of the range
    subtitleRange = worksheet["B34"].MergeArea;
    // set the text of title area
    chartDescription = 
            ((ExcelChartItem)((rb2D.Checked ? Charts2D : Charts3D)[ddlChartTypes.SelectedIndex])).Description;
    worksheet["B34"].Text = String.Format("{0}
        Chart - Custom Series", chartDescription);

    // set a row height of 18 points for each row in the range
    subtitleRange.RowHeightInPoints = 18;
    // set the worksheet top title style
    subtitleRange.Style = subtitleStyle;
    #endregion


    #region ADD A CHART WITH CUSTOM SERIES TO THE WORKSHEET

    ExcelChart customSeriesChart = worksheet.Charts.AddChart(chartType, 2, 36, 8, 59);

    // set chart data table
    customSeriesChart.ShowDataTable = true;
    customSeriesChart.DataTable.ShowLegendKey = true;

    // set chart title
    customSeriesChart.Title.Text = "Product Units Sold per Quarter - Custom
        Series";
    customSeriesChart.Title.Font.Size = 12;
    customSeriesChart.Title.Font.Color = Color.DarkBlue;

    // create the category names range
    ExcelRange categoryNamesRange = worksheet["C7:E7"];

    // Add chart series

    // add first series for the first quarter sales
    ExcelChartSeries firstQuarterSeries = customSeriesChart.Series.AddSeries("First
        Quarter Sales");
    firstQuarterSeries.ChartType = chartType;
    firstQuarterSeries.CategoryNamesRange = worksheet["C7:E7"];
    firstQuarterSeries.ValuesRange = worksheet["C8:E8"];

    firstQuarterSeries.DataPoints.All.Label.ContainsValue = true;
    firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
    firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;


    //add second series for second quarter sales
    ExcelChartSeries secondQuarterSeries = customSeriesChart.Series.AddSeries("Second
        Quarter Sales");
    secondQuarterSeries.ChartType = chartType;
    secondQuarterSeries.CategoryNamesRange = categoryNamesRange;
    secondQuarterSeries.ValuesRange = worksheet["C9:E9"];

    secondQuarterSeries.DataPoints.All.Label.ContainsValue = true;
    secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
    secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;

    // add third series for fourth quarter sales
    ExcelChartSeries fourthQuarterSeries = customSeriesChart.Series.AddSeries("Fourth
        Quarter Sales");
    fourthQuarterSeries.ChartType = chartType;
    fourthQuarterSeries.CategoryNamesRange = categoryNamesRange;
    fourthQuarterSeries.ValuesRange = worksheet["C11:E11"];

    fourthQuarterSeries.DataPoints.All.Label.ContainsValue = true;
    fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
    fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;

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

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

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

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

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

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

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

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


    #endregion


    #region ADD A CHART WORKSHEET

    ExcelChartWorksheet chartWorksheet = workbook.ChartWorksheets.AddChartWorksheet(chartType, dataSourceRange, 
                    seriesDataByRows, "Chart Worksheet Demo");

    #region CHART WORKSHEET PAGE SETUP

    // set worksheet paper size and orientation, margins, header and footer
    chartWorksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
    chartWorksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape;
    chartWorksheet.PageSetup.LeftMargin = 0.75;
    chartWorksheet.PageSetup.RightMargin = 0.25;
    chartWorksheet.PageSetup.TopMargin = 1;
    chartWorksheet.PageSetup.BottomMargin = 0.1;
    //display a logo image in the left part of the header
    chartWorksheet.PageSetup.LeftHeaderFormat = "&G";
    chartWorksheet.PageSetup.LeftHeaderPicture = logoImg;
    // display worksheet name in the right part of the header
    chartWorksheet.PageSetup.RightHeaderFormat = "&A";


    #endregion

    // set chart title
    chartWorksheet.Title.Text = "Product Units Sold per Quarter - Auto
        Generated Series";
    chartWorksheet.Title.Font.Size = 12;
    chartWorksheet.Title.Font.Color = Color.DarkBlue;

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

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

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

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

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

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

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

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

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

    if (seriesDataByRows)
    {
        // show a label with total number of units sold in a year
        chartWorksheet.Series["Yearly Total"].DataPoints.All.Label.ContainsValue = true;
        chartWorksheet.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Size = 8;
        chartWorksheet.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Italic = true;
    }
    else
    {
        // show a label with total number of units sold in a year

        foreach (ExcelChartSeries series in chartWorksheet.Series)
        {
            int lastDataPointIndex = series.DataPoints.Count - 1;

            series.DataPoints[lastDataPointIndex].Label.ContainsValue = true;
            series.DataPoints[lastDataPointIndex].Label.LabelFormat.Font.Size = 8;
            series.DataPoints[lastDataPointIndex].Label.LabelFormat.Font.Italic = true;
        }
    }
    #endregion

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

    string outFileName = workbookFormat == ExcelWorkbookFormat.Xls_2003 ? "ChartsDemo.xls" : "ChartsDemo.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 (.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

        if (logoImg != null)
            logoImg.Dispose();

        #endregion
    }

    // End the response and finish the execution of this page
    httpResponse.End();
}
Copyright © 2007- Winnovative Software Solutions