Chart Formatting

Setting Chart Appearance

In Chart Types, we gave a brief introduction to the types of charts and charting objects offered by Aspose.Cells.

In this article, we discuss how to customize the appearance of charts by setting a number of different properties:

• Setting the chart area.
• Setting chart lines.
• Applying themes.
• Setting titles to charts and axes.
• Working with gridlines.
• Setting borders for back and side walls.

Setting Chart Area

There are different kinds of areas in a chart and Aspose.Cells provides the flexibility of modifying the appearance of each area. Developers can apply different formatting settings on an area by changing its foreground color, background color and fill format etc.

In the example given below, we have applied different formatting settings on different kinds of areas of a chart. These areas include:

• Plot area
• Chart area
• SeriesCollection area
• The area of a single point in an SeriesCollection

After executing the example code, a column chart will be added to the worksheet as shown below:

A column chart with filled areas

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingChartArea.class) + "charts/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();

// Obtaining the reference of the newly added worksheet by passing its
// sheet index
Worksheet worksheet = worksheets.get(0);
Cells cells = worksheet.getCells();
// Adding a sample value to "A1" cell
cells.get("A1").setValue(50);

// Adding a sample value to "A2" cell
cells.get("A2").setValue(100);

// Adding a sample value to "A3" cell
cells.get("A3").setValue(150);

// Adding a sample value to "B1" cell
cells.get("B1").setValue(60);

// Adding a sample value to "B2" cell
cells.get("B2").setValue(32);

// Adding a sample value to "B3" cell
cells.get("B3").setValue(50);

// Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();

// Accessing the instance of the newly added chart
int chartIndex = charts.add(ChartType.COLUMN, 5, 0, 15, 7);
Chart chart = charts.get(chartIndex);

// Adding NSeries (chart data source) to the chart ranging from "A1"
// cell
SeriesCollection nSeries = chart.getNSeries();
nSeries.add("A1:B3", true);

// Setting the foreground color of the plot area
ChartFrame plotArea = chart.getPlotArea();
Area area = plotArea.getArea();
area.setForegroundColor(Color.getBlue());

// Setting the foreground color of the chart area
ChartArea chartArea = chart.getChartArea();
area = chartArea.getArea();
area.setForegroundColor(Color.getYellow());

// Setting the foreground color of the 1st NSeries area
Series aSeries = nSeries.get(0);
area = aSeries.getArea();
area.setForegroundColor(Color.getRed());

// Setting the foreground color of the area of the 1st NSeries point
ChartPointCollection chartPoints = aSeries.getPoints();
ChartPoint point = chartPoints.get(0);
point.getArea().setForegroundColor(Color.getCyan());

// Save the Excel file
workbook.save(dataDir + "SettingChartArea_out.xls");

// Print message
System.out.println("ChartArea is settled successfully.");

Setting Chart Lines

Developers can also apply different kinds of styles on the lines or data markers of the SeriesCollection as shown below in the example. Executing the example code adds a column chart to the worksheet as shown below:

Column chart after applying line styles

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingChartLines.class) + "charts/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");
WorksheetCollection worksheets = workbook.getWorksheets();

// Obtaining the reference of the newly added worksheet by passing its
// sheet index
Worksheet worksheet = worksheets.get(0);
Cells cells = worksheet.getCells();

// Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();

Chart chart = charts.get(0);

// Adding NSeries (chart data source) to the chart ranging from "A1"
// cell
SeriesCollection nSeries = chart.getNSeries();
nSeries.add("A1:B3", true);

Series aSeries = nSeries.get(0);
Line line = aSeries.getSeriesLines();
line.setStyle(LineType.DOT);

// Applying a triangular marker style on the data markers of an NSeries
aSeries.getMarker().setMarkerStyle(ChartMarkerType.TRIANGLE);
// Setting the weight of all lines in an NSeries to medium
aSeries = nSeries.get(1);
line = aSeries.getSeriesLines();
line.setWeight(WeightType.MEDIUM_LINE);

// Save the Excel file
workbook.save(dataDir + "SettingChartLines_out.xls");

// Print message
System.out.println("ChartArea is settled successfully.");

Applying Microsoft Excel 2007/2010 Themes to Charts

Developers can apply different Microsoft Excel themes and colors to the SeriesCollection or other chart objects as shown in the example below.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ApplyingThemes.class) + "charts/";

// Instantiate the workbook to open the file that contains a chart
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);

// Get the first chart in the sheet
Chart chart = worksheet.getCharts().get(0);

// Specify the FilFormat's type to Solid Fill of the first series
chart.getNSeries().get(0).getArea().getFillFormat().setFillType(FillType.SOLID);

// Get the CellsColor of SolidFill
CellsColor cc = chart.getNSeries().get(0).getArea().getFillFormat().getSolidFill().getCellsColor();

// Create a theme in Accent style
cc.setThemeColor(new ThemeColor(ThemeColorType.ACCENT_6, 0.6));

// Apply the them to the series
chart.getNSeries().get(0).getArea().getFillFormat().getSolidFill().setCellsColor(cc);

// Save the Excel file
workbook.save(dataDir + "AThemes_out.xlsx");

Setting the Titles of Charts or Axes

You can use Microsoft Excel to set the titles of a chart and its axes in a WYSIWYG environment as shown below.

Setting titles of a chart & its axes using Microsoft Excel

Aspose.Cells also allows developers to set the titles of a chart and its axes at runtime. All charts and their axes contain a Title.setText method that can be used to set their titles as shown below in an example. After executing the example code, a column chart will be added to the worksheet as shown below:

Column chart after setting titles

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingTitlesAxes.class) + "charts/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");
WorksheetCollection worksheets = workbook.getWorksheets();

// Obtaining the reference of the newly added worksheet by passing its
// sheet index
Worksheet worksheet = worksheets.get(0);
Cells cells = worksheet.getCells();

// Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();

// Accessing the instance of the newly added chart
int chartIndex = charts.add(ChartType.COLUMN, 5, 0, 15, 7);
Chart chart = charts.get(chartIndex);

// Setting the title of a chart
Title title = chart.getTitle();
title.setText("ASPOSE");

// Setting the font color of the chart title to blue
Font font = title.getFont();
font.setColor(Color.getBlue());

// Setting the title of category axis of the chart
Axis categoryAxis = chart.getCategoryAxis();
title = categoryAxis.getTitle();
title.setText("Category");

// Setting the title of value axis of the chart
Axis valueAxis = chart.getValueAxis();
title = valueAxis.getTitle();
title.setText("Value");

// Adding NSeries (chart data source) to the chart ranging from "A1"
// cell
SeriesCollection nSeries = chart.getNSeries();
nSeries.add("A1:B3", true);

// Setting the foreground color of the plot area
ChartFrame plotArea = chart.getPlotArea();
Area area = plotArea.getArea();
area.setForegroundColor(Color.getBlue());

// Setting the foreground color of the chart area
ChartArea chartArea = chart.getChartArea();
area = chartArea.getArea();
area.setForegroundColor(Color.getYellow());

// Setting the foreground color of the 1st NSeries area
Series aSeries = nSeries.get(0);
area = aSeries.getArea();
area.setForegroundColor(Color.getRed());

// Setting the foreground color of the area of the 1st NSeries point
ChartPointCollection chartPoints = aSeries.getPoints();
ChartPoint point = chartPoints.get(0);
point.getArea().setForegroundColor(Color.getCyan());

// Save the Excel file
workbook.save(dataDir + "SettingTitlesAxes_out.xls");

// Print message
System.out.println("Chart Title is changed successfully.");

Setting Major Gridlines

Hiding Major Gridlines

Developers can control the visibility of major gridlines by using the setVisible method of the Line object. After hiding the major gridlines, a column chart added to the worksheet has the following appearance:

A column chart with hidden major gridlines

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Hiding the major gridlines of value axis
Axis valueAxis = chart.getValueAxis();
Line majorGridLines = valueAxis.getMajorGridLines();
majorGridLines.setVisible(false);

Changing Major Gridlines Settings

Developers cannot only control the visibility of major gridlines but also other properties including its color etc. After setting the color of major gridlines, a column chart added to the worksheet will have the following appearance:

Column chart with colored major gridlines

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Setting the color of major gridlines of value axis to silver
Axis categoryAxis = chart.getCategoryAxis();
categoryAxis.getMajorGridLines().setColor(Color.getSilver());

Setting Borders for Back and Side Walls

Since the release of Microsoft Excel 2007, the walls of a 3D chart have been divided into two parts: side wall and back wall, so we have to use two Walls objects to represent them separately and you can access them by using Chart.getBackWall() and Chart.getSideWall().

The example given below shows how to set the border of the sidewall by using different attributes.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Get the side wall border line
Line sideLine = chart.getSideWall().getBorder();
// Make it visible
sideLine.setVisible(true);
// Set the solid line
sideLine.setStyle(LineType.SOLID);
// Set the line width
sideLine.setWeight(10);
// Set the color
sideLine.setColor(Color.getBlack());

Change the Chart Position and Size

Sometimes, you want to change the position or size of the new or existing chart inside the worksheet. Aspose.Cells provides the Chart.getChartObject() property to achieve this. You can use its sub-properties to re-size the chart with new height and width or re-position it with new X and Y coordinates.

Modifying Chart’s Position and Size

To change the chart’s position (X, Y coordinates) and size (height, width), use these properties:

1. Chart.getChartObject().get/setWidth()
2. Chart.getChartObject().get/setHeight()
3. Chart.getChartObject().get/setX()
4. Chart.getChartObject().get/setY()

The following example explains the usage of the above properties. It loads the existing workbook which contains a chart in its first worksheet. Then it re-sizes and re-positions the chart and save the workbook.

Before the execution of the sample code, the source file looks like this:

Chart size and position before the execution of sample code

After the execution, the output file looks like this:

Chart size and position after the execution of sample code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ChangeChartPositionAndSize.class) + "charts/";

String filePath = dataDir + "book1.xls";

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.getWorksheets().get(0);

// Load the chart from source worksheet
Chart chart = worksheet.getCharts().get(0);

// Resize the chart
chart.getChartObject().setWidth(400);
chart.getChartObject().setHeight(300);

// Reposition the chart
chart.getChartObject().setX(250);
chart.getChartObject().setY(150);

// Output the file
workbook.save(dataDir + "ChangeChartPositionAndSize_out.xls");

// Print message
System.out.println("Position and Size of Chart is changed successfully.");

Manipulating Designer Charts

There is a time where you might need to manipulate or modify the charts in your designer template files. Aspose.Cells fully supports to manipulate designer charts with its contents and elements. The data, chart contents, background image, and formatting can be preserved with accuracy.

Manipulating Designer Charts in the Template Files

To manipulate designer charts in a template file, use all chart related API calls. For example, use Worksheet.getCharts property to get the existing charts collection in the template file.

Creating a Chart

The following example shows how to create a pie chart. We will manipulate this chart later on. The following output is generated by the code.

The input pie chart

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CreateChart.class) + "charts/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);

// Adding some sample value to cells
Cells cells = sheet.getCells();
Cell cell = cells.get("A1");
cell.setValue(50);
cell = cells.get("A2");
cell.setValue(100);
cell = cells.get("A3");
cell.setValue(150);
cell = cells.get("B1");
cell.setValue(4);
cell = cells.get("B2");
cell.setValue(20);
cell = cells.get("B3");
cell.setValue(50);

ChartCollection charts = sheet.getCharts();

// Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.PYRAMID, 5, 0, 15, 5);
Chart chart = charts.get(chartIndex);

// Adding NSeries (chart data source) to the chart ranging from "A1"
// cell to "B3"
SeriesCollection serieses = chart.getNSeries();
serieses.add("A1:B3", true);

// Saving the Excel file
workbook.save(dataDir + "CreateChart_out.xls");

// Print message
System.out.println("Workbook with chart is successfully created.");

Manipulating the Chart

The following example shows how to manipulate the existing chart. In this example we modify the chart created above. The following output is generated by the code. Note that the color of the chart title has changed from blue to black, and ‘England 30000’ has been changed to ‘United Kingdom, 30K’.

The pie chart has been modified

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ModifyPieChart.class) + "charts/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "ModifyCharts.xlsx");

// Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(1);

// Load the chart from source worksheet
Chart chart = sheet.getCharts().get(0);
DataLabels datalabels = chart.getNSeries().get(0).getPoints().get(0).getDataLabels();
datalabels.setText("aspose");

// Saving the Excel file
workbook.save(dataDir + "ModifyPieChart_out.xls");

// Print message
System.out.println("Pie chart is successfully modified.");

Manipulating a Line Chart in the Designer Template

In this example, we will manipulate a line chart. We will add some data series to the existing chart and change their line colors.

First, take a look at the designer line chart.

The input line chart

Now we manipulate the line chart (which is contained in the linechart.xls file) using the following code. The following output is generated by the code.

The manipulated line chart

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ModifyLineChart.class) + "charts/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);

// Load the chart from source worksheet
Chart chart = sheet.getCharts().get(0);

// Adding NSeries (chart data source) to the chart ranging from "A1"
// cell to "B3"
SeriesCollection serieses = chart.getNSeries();
serieses.add("{20,40,90}", true);
serieses.add("{110,70,220}", true);

// Saving the Excel file
workbook.save(dataDir + "ModifyLineChart_out.xls");

// Print message
System.out.println("Line chart is successfully modified.");

Using Sparklines

Microsoft Excel 2010 can analyze information in more ways than ever before. It allows users to track and highlight important data trends with new data analysis and visualization tools. Sparklines are mini-charts that you can place inside cells so that you can view data and chart on the same table. When sparklines are used properly, data analysis is quicker and more to the point. They also provide a simple view of information, avoiding over-crowded worksheets with a lot of busy charts.

Aspose.Cells provides an API for manipulating sparklines in spreadsheets.

Sparklines in Microsoft Excel

To insert sparklines in Microsoft Excel 2010:

1. Select the cells where you want the sparklines to appear. To make them easy to view, select cells at the side of the data.
2. Click Insert on the ribbon and then choose column in the Sparklines group.

1. Select or enter the range of cells in the worksheet that contain the source data. The charts appear.

Sparklines help you see trends, for example, or the win or loss record for a softball league. Sparklines can even sum up the entire season of each team in the league.

Sparklines using Aspose.Cells

Developers can create, delete or read sparklines (in the template file) using the API provided by Aspose.Cells. By adding custom graphics for a given data range, developers have the freedom to add different types of tiny charts to selected cell areas.

The example below demonstrates the Sparklines feature. The example shows how to:

1. Open a simple template file.
2. Read sparklines information for a worksheet.
3. Add new sparklines for a given data range to a cell area.
4. Saves the Excel file to disk.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(UsingSparklines.class) + "charts/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();

// Obtaining the reference of the first worksheet
Worksheet worksheet = worksheets.get(0);
Cells cells = worksheet.getCells();

System.out.println("Sparkline count: " + worksheet.getSparklineGroupCollection().getCount());

for (int i = 0; i < worksheet.getSparklineGroupCollection().getCount(); i++) {
	SparklineGroup g = worksheet.getSparklineGroupCollection().get(i);
	System.out.println("sparkline group: type:" + g.getType());

	for (int j = 0; j < g.getSparklineCollection().getCount(); i++) {
		Sparkline gg = g.getSparklineCollection().get(i);
		System.out.println("sparkline: row:" + gg.getRow() + ", col:" + gg.getColumn() + ", dataRange:"
				+ gg.getDataRange());
	}
}
// Add Sparklines
// Define the CellArea D2:D10
CellArea ca = new CellArea();
ca.StartColumn = 4;
ca.EndColumn = 4;
ca.StartRow = 1;
ca.EndRow = 7;
int idx = worksheet.getSparklineGroupCollection().add(SparklineType.COLUMN, "Sheet1!B2:D8", false, ca);

SparklineGroup group = worksheet.getSparklineGroupCollection().get(idx);
// Create CellsColor
CellsColor clr = workbook.createCellsColor();
clr.setColor(Color.getChocolate());
group.setSeriesColor(clr);
workbook.save(dataDir + "UsingSparklines_out.xls");

// Print message
System.out.println("Workbook with chart is created successfully.");

Applying 3D Format to Chart

You might need 3D charting styles so you can get just the results for your scenario. Aspose.Cells APIs provide the relevant API to apply Microsoft Excel 2007 3D formatting as demonstrated in this article.

Setting 3D Format to Chart

A complete example is given below to demonstrate how to create a chart and apply Microsoft Excel 2007 3D formatting. After executing the above example code, a column chart (with 3D effects) will be added to the worksheet as shown below.

A column chart with 3D formatting

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(Applying3DFormat.class) + "charts/";

// Instantiate a new Workbook
Workbook book = new Workbook();
// Add a Data Worksheet
Worksheet dataSheet = book.getWorksheets().add("DataSheet");
// Add Chart Worksheet
Worksheet sheet = book.getWorksheets().add("MyChart");
// Put some values into the cells in the data worksheet
dataSheet.getCells().get("B1").putValue(1);
dataSheet.getCells().get("B2").putValue(2);
dataSheet.getCells().get("B3").putValue(3);
dataSheet.getCells().get("A1").putValue("A");
dataSheet.getCells().get("A2").putValue("B");
dataSheet.getCells().get("A3").putValue("C");

// Define the Chart Collection
ChartCollection charts = sheet.getCharts();
// Add a Column chart to the Chart Worksheet
int chartSheetIdx = charts.add(ChartType.COLUMN, 5, 0, 25, 15);

// Get the newly added Chart
Chart chart = book.getWorksheets().get(2).getCharts().get(0);

// Set the background/foreground color for PlotArea/ChartArea
chart.getPlotArea().getArea().setBackgroundColor(Color.getWhite());
chart.getChartArea().getArea().setBackgroundColor(Color.getWhite());
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());
chart.getChartArea().getArea().setForegroundColor(Color.getWhite());

// Hide the Legend
chart.setShowLegend(false);

// Add Data Series for the Chart
chart.getNSeries().add("DataSheet!B1:B3", true);
// Specify the Category Data
chart.getNSeries().setCategoryData("DataSheet!A1:A3");

// Get the Data Series
Series ser = chart.getNSeries().get(0);

// Apply the 3D formatting
ShapePropertyCollection spPr = ser.getShapeProperties();
Format3D fmt3d = spPr.getFormat3D();
// Specify Bevel with its height/width
Bevel bevel = fmt3d.getTopBevel();
bevel.setType(BevelPresetType.CIRCLE);
bevel.setHeight(5);
bevel.setWidth(9);
// Specify Surface material type
fmt3d.setSurfaceMaterialType(PresetMaterialType.WARM_MATTE);
// Specify surface lighting type
fmt3d.setSurfaceLightingType(LightRigType.THREE_POINT);
// Specify lighting angle
fmt3d.setLightingAngle(20);

// Specify Series background/foreground and line color
ser.getArea().setBackgroundColor(Color.getMaroon());
ser.getArea().setForegroundColor(Color.getMaroon());
ser.getBorder().setColor(Color.getMaroon());

// Save the Excel file
book.save(dataDir + "A3DFormat_out.xls");

// Print message
System.out.println("3D format is applied successfully.");

For a full list of which 2D and 3D charts are supported, see Supported chart types for rendering.

Advance topics

• Set Picture as Background Fill in the Chart