GcExcel Java allows users to configure chart series in the following ways:
The Points collection in GcExcel Java is used to represent all the points in a specific series and the indexer notation of the IPoints interface to get a specific point in the series. Also, you can use the getDataLabel method of the IPoint interface in order to get data label of a specific point.
Set the format of DataPoint
In order to set data point format for the chart added in your worksheet, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 250, 20, 360, 230); worksheet.getRange("A1:D6").setValue( new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.getPoints().get(2).getFormat().getFill().getColor().setRGB(Color.FromArgb(0, 176, 240)); series1.getPoints().get(2).getFormat().getLine().getColor().setRGB(Color.GetBlue()); |
Configure secondary section for pie of a pie chart
You can use the setSecondaryPlot method of the IPoint interface to set if the point lies in the secondary section of either a pie of pie chart or a bar of pie chart.
In order to configure secondary section for pie of a pie chart, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.PieOfPie, 250, 20, 360, 230); worksheet.getRange("A1:D6").setValue( new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.setHasDataLabels(true); shape.getChart().getChartGroups().get(0).setSplitType(ChartSplitType.SplitByCustomSplit); series1.getPoints().get(0).setSecondaryPlot(true); series1.getPoints().get(1).setSecondaryPlot(false); series1.getPoints().get(2).setSecondaryPlot(true); series1.getPoints().get(3).setSecondaryPlot(false); series1.getPoints().get(4).setSecondaryPlot(true); |
You can use the DataLabels collection to represent the collection of all the data labels for a specific series.
The getFormat method of the IDataLabel interface can be used to set font style, fill, line and 3-D formatting for all the data labels of a specific series. Users can also configure the layout of the data labels using other methods of the IDataLabel interface.
Set all data labels and specific data label format for series
In order to set all data labels and specific data label format of a series, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 250, 20, 360, 230); worksheet.getRange("A1:B5").setValue( new Object[][] { { null, "S1" }, { "Item1", -20 }, { "Item2", 30 }, { "Item3", 50 }, { "Item3", 40 } }); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:B5"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.setHasDataLabels(true); // set series1's all data label's format. series1.getDataLabels().getFormat().getFill().getColor().setRGB(Color.GetPink()); series1.getDataLabels().getFormat().getLine().getColor().setRGB(Color.GetGreen()); series1.getDataLabels().getFormat().getLine().setWeight(1); // set series1's specific data label's format. series1.getDataLabels().get(2).getFormat().getFill().getColor().setRGB(Color.GetLightGreen()); series1.getPoints().get(2).getDataLabel().getFormat().getLine().getColor().setRGB(Color.GetGray()); series1.getPoints().get(2).getDataLabel().getFormat().getLine().setWeight(2); |
Customize data label text
In order to set the text of the data label as per your choice, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 250, 20, 360, 230); worksheet.getRange("A1:B5").setValue(new Object[][] { { null, "S1", "S2" }, { "Item1", -20 }, { "Item2", 30 }, { "Item3", 50 }, { "Item3", 40 } }); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:B5"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.setHasDataLabels(true); // customize data label's text. series1.getDataLabels().setShowCategoryName(true); series1.getDataLabels().setShowSeriesName(true); series1.getDataLabels().setShowLegendKey(true); |
The Trendlines collection in GcExcel Java is used to represent a collection of trend lines for a specific series. You can use the get method of the ITrendlines interface to create a new trendline for a specific series. Also, the indexer notation of the ITrendlines interface can be used to get a specific trend line.
Add trendline for series and configure its style
In order to add trendline for series and configure its style, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.getTrendlines().add(); series1.getTrendlines().get(0).setType(TrendlineType.Linear); series1.getTrendlines().get(0).setForward(5); series1.getTrendlines().get(0).setBackward(0.5); series1.getTrendlines().get(0).setIntercept(2.5); series1.getTrendlines().get(0).setDisplayEquation(true); series1.getTrendlines().get(0).setDisplayRSquared(true); |
Add two trendlines for one series
In order to add two trendlines for one series, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.getTrendlines().add(); series1.getTrendlines().get(0).setType(TrendlineType.Linear); series1.getTrendlines().get(0).setForward(5); series1.getTrendlines().get(0).setBackward(0.5); series1.getTrendlines().get(0).setIntercept(2.5); series1.getTrendlines().get(0).setDisplayEquation(true); series1.getTrendlines().get(0).setDisplayRSquared(true); series1.getTrendlines().add(); series1.getTrendlines().get(1).setType(TrendlineType.Polynomial); series1.getTrendlines().get(1).setOrder(3); |
Set trendline's name
You can also set the trendline's name in GcExcel using the setName method of ITrendline interface. The trendline's name can also be exported to a PDF document.
Refer to the following example code to set trendline's name in GcExcel.
Java |
Copy Code |
---|---|
// Initialize workbook Workbook workbook = new Workbook(); // Fetch default worksheet IWorksheet worksheet = workbook.getWorksheets().get(0); // Add a chart IShape columnChart = worksheet.getShapes().addChart(ChartType.ColumnClustered, 300, 10, 300, 300); worksheet.getRange("A1:D6").setValue(new Object[][] { {null, "S1", "S2", "S3"}, {"Item1", 10, 25, 25}, {"Item2", -51, -36, 27}, {"Item3", 52, -85, -30}, {"Item4", 22, 65, 65}, {"Item5", 23, 69, 69} }); // Add series columnChart.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); // Get first series ISeries series1 = columnChart.getChart().getSeriesCollection().get(0); // Add a trend line ITrendline trendline = series1.getTrendlines().add(); // Set trend line's name. trendline.setName("Theoretical data"); //save to an excel file workbook.save("TrendLineName.xlsx"); |
A Chart Group possesses common settings for one or more series. Typically, it is a group of specific featured series.
Set varied colors for column chart with one series
In order to set different colors for a column chart (that contains only one series), refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); shape.getChart().getSeriesCollection().get(2).delete(); shape.getChart().getSeriesCollection().get(1).delete(); // Chart's series count is 1. // int count = shape.getChart().getSeriesCollection().getCount(); shape.getChart().getSeriesCollection().getCount(); // set vary colors for column chart which only has one series. shape.getChart().getColumnGroups().get(0).setVaryByCategories(true); |
Set split setting and gap width for pie of a pie chart
In order to set split setting and gap width for pie of a pie chart, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.PieOfPie, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.setHasDataLabels(true); shape.getChart().getPieGroups().get(0).setSplitType(ChartSplitType.SplitByValue); shape.getChart().getPieGroups().get(0).setSplitValue(20); shape.getChart().getPieGroups().get(0).setGapWidth(350); |
Set gap width of column chart and overlap
In order to set the gap width of the column chart along with overlap, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.setHasDataLabels(true); shape.getChart().getColumnGroups().get(0).setGapWidth(120); shape.getChart().getColumnGroups().get(0).setOverlap(-20); |
Configure the layout of the bubble chart
In order to configure the layout of the bubble chart as per your preferences, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.Bubble, 250, 20, 360, 230); Object[][] data = new Object[][] { {"Blue", null, null }, {125, 750, 3 }, {25, 625, 7 }, {75, 875, 5 }, {175, 625, 6}, {"Red",null,null }, {125 ,500 , 10 }, {25, 250, 1 }, {75, 125, 5 }, {175, 250, 8 } }; worksheet.getRange("A2:C10").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A2:C5"), RowCol.Columns, true, true); shape.getChart().getSeriesCollection().add(worksheet.getRange("A7:C10"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.setHasDataLabels(true); shape.getChart().getXYGroups().get(0).setBubbleScale(150); shape.getChart().getXYGroups().get(0).setSizeRepresents(SizeRepresents.SizeIsArea); shape.getChart().getXYGroups().get(0).setShowNegativeBubbles(true); |
Configure the layout of the doughnut chart
Refer to the following example code to configure the layout of the doughnut chart as per your preferences.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.Doughnut, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); ISeries series1 = shape.getChart().getSeriesCollection().get(0); series1.setHasDataLabels(true); shape.getChart().getDoughnutGroups().get(0).setFirstSliceAngle(50); shape.getChart().getDoughnutGroups().get(0).setDoughnutHoleSize(20); |
You can use the methods of the IChartGroup interface to configure Dropline, HiLoline and Series lines in a chart.
Configure the drop lines of the line chart
In order to configure the drop lines of the line chart as per your preferences, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.Line, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); shape.getChart().getLineGroups().get(0).setHasDropLines(true); shape.getChart().getLineGroups().get(0).getDropLines().getFormat().getLine().getColor().setRGB(Color.GetRed()); |
Configure the high-low lines of the line chart
In order to configure the high-low lines of the line chart as per your preferences, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.Line, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); shape.getChart().getLineGroups().get(0).setHasHiLoLines(true); shape.getChart().getLineGroups().get(0).getHiLoLines().getFormat().getLine().getColor().setRGB(Color.GetRed()); |
Configure the series lines for column chart
In order to configure the column chart's series lines as per your preferences, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.ColumnStacked, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); shape.getChart().getColumnGroups().get(0).setHasSeriesLines(true); shape.getChart().getColumnGroups().get(0).getSeriesLines().getFormat().getLine().getColor() .setRGB(Color.GetRed()); |
Configure the connector lines for pie of a pie chart
In order to configure the connector lines for pie of a pie chart as per your preferences, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.PieOfPie, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); shape.getChart().getPieGroups().get(0).setHasSeriesLines(true); shape.getChart().getPieGroups().get(0).getSeriesLines().getFormat().getLine().getColor().setRGB(Color.GetRed()); |
You can use the methods of the IChartGroup interface to configure the style of the up bars and the down bars as per your preferences.
Configure the up-down bars for the line chart
In order to configure the up-down bars for the line chart as per your preferences, refer to the following example code.
Java |
Copy Code |
---|---|
IShape shape = worksheet.getShapes().addChart(ChartType.Line, 200, 30, 300, 300); Object[][] data = new Object[][] { { null, "S1", "S2", "S3" }, { "Item1", 10, 25, 25 }, { "Item2", -51, -36, 27 }, { "Item3", 52, -85, -30 }, { "Item4", 22, 65, 65 }, { "Item5", 23, 69, 69 } }; worksheet.getRange("A1:D6").setValue(data); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:D6"), RowCol.Columns, true, true); shape.getChart().getLineGroups().get(0).setHasUpDownBars(true); shape.getChart().getLineGroups().get(0).getUpBars().getFormat().getFill().getColor().setRGB(Color.GetGreen()); shape.getChart().getLineGroups().get(0).getDownBars().getFormat().getFill().getColor().setRGB(Color.GetRed()); |