Spreadsheet Control Chart API (Coming soon in v14.1)

When it comes down to it, I am a programmer. That is what I went to school for: it’s what I like to do. While wizards and drag-and-drop are really cool, sometimes we just want to get down to the code and do things ourselves. This is particularly true when we want to generate certain types of documents like Word Documents, PDF’s, or spreadsheets. In upcoming release of .NET Spreadsheet control we introduce a new API which allows to create or modify embedded charts programmatically.

Using Spreadsheet Chart API you can:

  • Create new chart of predefined type (Bar/Column, Line, Pie, etc.) using data from specified cell range;
  • Enumerate existing charts embedded to worksheet;
  • Find existing chart by id or name;
  • Setup chart Z-order and position (using cells as anchor points);
  • Change chart type, select data from cell range or specify literal data, toggle row/column data;
  • Add, remove, reorder chart series, change series type or data references;
  • Adjust axis properties (like position, orientation, scaling, min/max values, etc.);
  • Adjust chart and axis titles;
  • Apply predefined style to whole chart or adjust formatting (fill, outline, font) of chart objects (plot area, legend, series, data points);
  • Adjust data labels, markers, trend lines, error bars, etc.;
  • Copy chart to another worksheet.

For example, let’s create complex chart based on the following data:

Data

First, add a couple of namespaces:

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Charts;
using DevExpress.Spreadsheet.Drawings;

Next create column clustered chart using data from specified cell range:

IWorkbook workbook = spreadsheetControl1.Document;
Worksheet worksheet = workbook.Worksheets.ActiveWorksheet;

Chart chart = worksheet.Charts.Add(ChartType.ColumnClustered, worksheet["B2:D8"]);

And setup chart position:

chart.TopLeftCell = worksheet.Cells["F2"];
chart.BottomRightCell = worksheet.Cells["M18"];

Basic Chart

Change type of second series and use secondary axes to make chart easy to read:

chart.Series[1].ChangeType(ChartType.Line);
chart.Series[1].AxisGroup = AxisGroup.Secondary;

Chart Type

Adjust legend position and apply chart style:

chart.Legend.Position = LegendPosition.Bottom;
chart.Style = ChartStyle.ColorGradient;

Changing Legend

Finally setup chart title, primary and secondary value axis titles and secondary value axis number format:

chart.Title.SetValue("My Sales");
chart.PrimaryAxes[1].Title.SetValue("Units sold");
chart.SecondaryAxes[1].Title.SetValue("Total transactions");
chart.SecondaryAxes[1].NumberFormat.FormatCode = "$#,##0";
chart.SecondaryAxes[1].NumberFormat.IsSourceLinked = false;

Title and Axis Setup

Done! Notice how clean the API is to work with. This truly makes it super easy to generate any kind of spreadsheet in code.

As always, if there are any comments and/or questions, feel free to get a hold of me!

Seth Juarez
Email: sethj@devexpress.com
Twitter: @SethJuarez

7 comment(s)
Mark Harby
Mark Harby

woop woop

28 May, 2014
Nick Whitehurst
Nick Whitehurst

Great, please bring this to Snap, too!

30 May, 2014
Josef Zatko
Josef Zatko

Hello,

will be support for accessing sheet.CustomProperties ? Thanks.

30 May, 2014
Valery (DevExpress)
Valery (DevExpress)

Nick,

We've already supported charts in Snap (refer to the Charts demo). Do you mean some advanced features?

4 June, 2014
Nick Whitehurst
Nick Whitehurst

Hi Valery,

I meant the API. The charts in snap look great initially, but now I'm finding I need more control over the chart objects to customise them but it there isn't any access at all. Sparklines etc do have an API so the lack of one for charts seems a little inconsistent.

Thanks,

15 June, 2014
Nick Whitehurst
Nick Whitehurst

Hi Valery,

I meant the API. The charts in snap look great initially, but now I'm finding I need more control over the chart objects to customise them but it there isn't any access at all. Sparklines etc do have an API so the lack of one for charts seems a little inconsistent.

Thanks,

15 June, 2014
Tony (DevExpress)
Tony (DevExpress)

@Josef, document custom properties will be implemented in the next major version of the Spreadsheet

23 June, 2014

Please login or register to post comments.