ExpertXls Excel Library Blazor demo · .NET 10 and .NET 11

← All demos

C# code of the Charts demo

Features/ChartsDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;

namespace ExcelLibraryDemos
{
    /// <summary>
    /// Adds charts to a workbook: a chart with series generated from a data range, a chart with series added one by
    /// one, and a chart worksheet (a worksheet that contains only a chart).
    /// </summary>
    public static class ChartsDemo
    {
        /// <summary>The name of the generated file, without extension.</summary>
        public const string OutputName = "Charts";

        /// <summary>The 2D chart types offered by the samples.</summary>
        public static readonly ExcelChartType[] ChartTypes2D = { ExcelChartType.ColumnClustered, ExcelChartType.ColumnStacked,
            ExcelChartType.ColumnStacked100, ExcelChartType.BarClustered, ExcelChartType.BarStacked, ExcelChartType.BarStacked100,
            ExcelChartType.Line, ExcelChartType.LineMarkers, ExcelChartType.LineStacked, ExcelChartType.Area,
            ExcelChartType.AreaStacked, ExcelChartType.Pie, ExcelChartType.PieExploded, ExcelChartType.PieOfPie,
            ExcelChartType.BarOfPie, ExcelChartType.Doughnut, ExcelChartType.Radar, ExcelChartType.RadarFilled,
            ExcelChartType.XYScatter, ExcelChartType.XYScatterLines, ExcelChartType.XYScatterSmooth };

        /// <summary>The 3D chart types offered by the samples.</summary>
        public static readonly ExcelChartType[] ChartTypes3D = { ExcelChartType.Column3DClustered, ExcelChartType.Column3D,
            ExcelChartType.Column3DStacked, ExcelChartType.Column3DStacked100, ExcelChartType.Bar3DClustered,
            ExcelChartType.Bar3DStacked, ExcelChartType.Bar3DStacked100, ExcelChartType.Area3D, ExcelChartType.Area3DStacked,
            ExcelChartType.Line3D, ExcelChartType.Pie3D, ExcelChartType.PieExploded3D, ExcelChartType.ConeColClustered,
            ExcelChartType.CylinderColClustered, ExcelChartType.PyramidColClustered, ExcelChartType.ConeBarClustered,
            ExcelChartType.PyramidBarStacked, ExcelChartType.Pyramid3DCol };

        /// <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.</param>
        /// <param name="chartType">The type of the charts.</param>
        /// <param name="seriesByRows">True to create a series from each row of the data range, false for each column.</param>
        public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output,
            ExcelChartType chartType, bool seriesByRows)
        {
            ExcelWorkbook workbook = new ExcelWorkbook(format);
            // workbook.LicenseKey = "your license key";
            workbook.DocumentProperties.Subject = "Excel charts";
            workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";

            Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
            try
            {
                ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
                ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);

                ExcelWorksheet worksheet = workbook.Worksheets[0];
                worksheet.Name = "Charts";
                DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape);
                DemoLayout.WriteTitle(worksheet, "A2:G3", "Excel Charts Demo", titleStyle);

                // THE DATA SOURCE: category names in row 7, series names in column B
                WriteSalesTable(worksheet, format);

                // CHART 1: the series are generated from the data source range. With series by rows, each quarter
                // is a series and the products are the categories; by columns it is the other way round.
                DemoLayout.WriteSection(worksheet, "B14:G14", String.Format("{0} chart - series generated from B7:G12", chartType), sectionStyle);
                ExcelChart chart = worksheet.Charts.AddChart(chartType, worksheet["B7:G12"], seriesByRows, 2, 16, 8, 33);
                chart.Title.Text = "Product Units Sold per Quarter";
                DemoLayout.FormatChart(chart, format, "Units sold", seriesByRows ? "Products" : "Quarters");

                // CHART 2: the series are added one by one, each with its own values and category names
                DemoLayout.WriteSection(worksheet, "B35:G35", String.Format("{0} chart - series added one by one", chartType), sectionStyle);
                ExcelChart customChart = worksheet.Charts.AddChart(chartType, 2, 37, 8, 54);
                customChart.Title.Text = "Units Sold of the First Three Products";
                ExcelRange categoryNames = worksheet["C7:E7"];
                string[] seriesNames = { "First Quarter", "Second Quarter", "Fourth Quarter" };
                string[] valueRanges = { "C8:E8", "C9:E9", "C11:E11" };
                for (int i = 0; i < seriesNames.Length; i++)
                {
                    ExcelChartSeries series = customChart.Series.AddSeries(seriesNames[i]);
                    series.ChartType = chartType;
                    series.CategoryNamesRange = categoryNames;
                    series.ValuesRange = worksheet[valueRanges[i]];
                }
                DemoLayout.FormatChart(customChart, format, "Units sold", null);
                // the data labels show the values of the last series
                customChart.Series["Fourth Quarter"].DataPoints.All.Label.ContainsValue = true;

                // CHART 3: a chart worksheet, placed after the worksheets in the workbook
                ExcelChartWorksheet chartSheet = workbook.ChartWorksheets.AddChartWorksheet(ExcelChartType.ColumnClustered,
                    worksheet["B7:G11"], true, "Sales Chart");
                chartSheet.Title.Text = "Units Sold per Product and Quarter";
                chartSheet.ShowDataTable = true;
                chartSheet.Legend.Position = ExcelChartLegendPosition.Bottom;

                workbook.Save(output);
            }
            finally
            {
                workbook.Close();
                logo.Dispose();
            }
        }

        private static void WriteSalesTable(ExcelWorksheet worksheet, ExcelWorkbookFormat format)
        {
            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 } };

            worksheet["C6:G6"].Merge();
            worksheet["C6"].Text = "Analyzed Products";
            worksheet["C6:G6"].Style.Font.Bold = true;
            worksheet["C6:G6"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            worksheet["C6:G6"].Style.Fill.FillType = ExcelCellFillType.PatternFill;
            worksheet["C6:G6"].Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_25_Percent;
            worksheet["C6:G6"].Style.Fill.PatternFillOptions.BackColor = Color.White;
            worksheet["C6:G6"].Style.Fill.PatternFillOptions.PatternColor = Color.Orange;

            // vertical text in a merged range: orientation 90 rotates the text bottom to top
            worksheet["A8:A11"].Merge();
            worksheet["A8"].Text = "Units Sold per Quarter";
            worksheet["A8:A11"].Style.Alignment.Orientation = 90;
            worksheet["A8:A11"].Style.Alignment.WrapText = true;
            worksheet["A8:A11"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            worksheet["A8:A11"].Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            worksheet["A8:A11"].Style.Font.Bold = true;

            for (int p = 0; p < products.Length; p++)
            {
                worksheet[7, 3 + p].Text = products[p];
                for (int q = 0; q < quarters.Length; q++)
                    worksheet[8 + q, 3 + p].Value = unitsSold[q, p];
                char column = (char)('C' + p);
                worksheet[12, 3 + p].Formula = String.Format("=SUM({0}8:{0}11)", column);
            }
            for (int q = 0; q < quarters.Length; q++)
                worksheet[8 + q, 2].Text = quarters[q];
            worksheet["B12"].Text = "Yearly Total";

            worksheet["B7:G7"].ColumnWidthInChars = 16;
            worksheet["B7:G7"].Style.Font.Bold = true;
            worksheet["B7:G7"].Style.Fill.FillType = ExcelCellFillType.SolidFill;
            worksheet["B7:G7"].Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
            worksheet["B8:B12"].Style.Font.Bold = true;
            worksheet["B8:B12"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right;
            worksheet["C8:G11"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            if (format == ExcelWorkbookFormat.Xlsx_2007)
            {
                worksheet["C8:G11"].Style.Fill.FillType = ExcelCellFillType.GradientFill;
                worksheet["C8:G11"].Style.Fill.GradientFillOptions.Color1 = Color.LightGreen;
                worksheet["C8:G11"].Style.Fill.GradientFillOptions.Color2 = Color.White;
            }
            worksheet["B12:G12"].Style.Font.Bold = true;
            worksheet["B12:G12"].Style.Font.Color = Color.Blue;
            worksheet["B12:G12"].Style.Fill.FillType = ExcelCellFillType.SolidFill;
            worksheet["B12:G12"].Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(204, 255, 255);
        }
    }
}

Create, edit and read Excel workbooks in a Blazor application: the workbooks are created on the server and downloaded by the browser. The code of each workbook is in the Features folder.