C# code of the Charts demo
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.