Skip to content

Bugzilla 59623: code inspired from Axel Richter on StackOverflow #139

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
Alain-Bearez wants to merge 1 commit into from
Closed

Bugzilla 59623: code inspired from Axel Richter on StackOverflow #139

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
55 changes: 16 additions & 39 deletions src/examples/src/org/apache/poi/xssf/usermodel/examples/BarAndLineChart.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -27,19 +27,19 @@
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFFillProperties;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LayoutMode;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
Expand Down Expand Up @@ -97,12 +97,13 @@ public static void main(String[] args) throws Exception {
properties.setItalic(true);
properties.setUnderline(UnderlineType.DOT_DOT_DASH_HEAVY);
properties.setFontSize(22.5);
XDDFFont[] fonts = new XDDFFont[]{
XDDFFont[] fonts = new XDDFFont[] {
new XDDFFont(FontGroup.LATIN, "Calibri", null, null, null),
new XDDFFont(FontGroup.COMPLEX_SCRIPT, "Liberation Sans", null, null, null)
};
properties.setFonts(fonts);
properties.setLineProperties(solidLine(PresetColor.SIENNA));
properties.setLineProperties(new XDDFLineProperties(
new XDDFSolidFillProperties(XDDFColor.from(PresetColor.SIENNA))));
XDDFTextParagraph paragraph = chart.getTitle().getBody().getParagraph(0);
paragraph.setDefaultRunProperties(properties);

Expand Down Expand Up @@ -136,7 +137,7 @@ public static void main(String[] args) throws Exception {
// the bar chart
XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, barCategories, leftValues);
XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(xs, ys1);
series1.setTitle("Bars", new CellReference("Sheet1!$B$1"));
series1.setTitle(null, new CellReference(sheet.getSheetName(), 0, 1, true,true));
bar.setVaryColors(true);
bar.setBarDirection(BarDirection.COL);
chart.plot(bar);
Expand All @@ -152,15 +153,20 @@ public static void main(String[] args) throws Exception {


XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) lines.addSeries(xs, ys2);
series2.setIndex(1);
series2.setOrder(1);
series2.setTitle("Lines", new CellReference("Sheet1!$C$1"));
series2.setTitle(null, new CellReference(sheet.getSheetName(), 0, 2, true, true));
series2.setSmooth(false);
series2.setMarkerStyle(MarkerStyle.DIAMOND);
series2.setMarkerSize((short)14);
lines.setVaryColors(true);
chart.plot(lines);

// some colors
solidFillSeries(bar, 0, PresetColor.CHARTREUSE);
solidLineSeries(lines, 0, PresetColor.TURQUOISE);
XDDFFillProperties solidChartreuse = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHARTREUSE));
XDDFFillProperties solidTurquoise = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.TURQUOISE));
XDDFLineProperties solidLines = new XDDFLineProperties(solidTurquoise);
series1.setFillProperties(solidChartreuse);
series1.setLineProperties(solidLines); // bar border color different from fill
series2.setLineProperties(solidLines);

// legend
XDDFChartLegend legend = chart.getOrAddLegend();
Expand All @@ -177,33 +183,4 @@ public static void main(String[] args) throws Exception {
}
}
}

private static void solidFillSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setFillProperties(fill);
series.setShapeProperties(properties);
}

private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
XDDFLineProperties line = solidLine(color);
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
}

private static XDDFLineProperties solidLine(PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
return line;
}
}
230 changes: 230 additions & 0 deletions src/examples/src/org/apache/poi/xssf/usermodel/examples/ExcelChartWithTargetLine.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,230 @@
/*
* ====================================================================
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* ====================================================================
*/

package org.apache.poi.xssf.usermodel.examples;

import java.io.FileOutputStream;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFFillProperties;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.AxisTickLabelPosition;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFLegendEntry;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFScatterChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xddf.usermodel.text.XDDFRunProperties;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
* This example is based on original contributions by Axel Richter on StackOverflow.
*
* <em>Note from original author</em>:
* This only works for Excel since OpenOffice or LibreOffice Calc is not able having series having literal numeric values set.
*
* @see <a href="https://stackoverflow.com/questions/50772989/">Create target marker in a bar chart with openxmlformats</a>
* @see <a href="https://stackoverflow.com/questions/50873700/">Change axis color and font of the chart in openxmlformats</a>
* @see <a href="https://stackoverflow.com/questions/51530552/">Change colors of line chart Apache POI</a>
*/
class ExcelChartWithTargetLine {

private static final int NUM_OF_ROWS = 6;

private static void createChart(XSSFChart chart, XSSFSheet sheet, int[] chartedCols, double target) {
// some colors
XDDFFillProperties[] fills = new XDDFFillProperties[] {
new XDDFSolidFillProperties(XDDFColor.from(PresetColor.TURQUOISE)),
new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHARTREUSE)),
new XDDFSolidFillProperties(XDDFColor.from(PresetColor.LAVENDER)),
new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHOCOLATE)),
new XDDFSolidFillProperties(XDDFColor.from(PresetColor.TOMATO)),
new XDDFSolidFillProperties(XDDFColor.from(PresetColor.PLUM))
};
XDDFLineProperties solidTurquoise = new XDDFLineProperties(fills[0]);
XDDFLineProperties solidTomato = new XDDFLineProperties(fills[4]);
XDDFLineProperties solidPlum = new XDDFLineProperties(fills[5]);
XDDFSolidFillProperties solidAlmond = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLANCHED_ALMOND));
XDDFSolidFillProperties solidGray = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.DARK_SLATE_GRAY));


// the bar chart

XDDFCategoryAxis barCategories = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFValueAxis leftValues = chart.createValueAxis(AxisPosition.LEFT);
leftValues.crossAxis(barCategories);
barCategories.crossAxis(leftValues);

// from https://stackoverflow.com/questions/50873700/
// colored major grid lines
leftValues.getOrAddMajorGridProperties().setLineProperties(solidTomato);
//colored axis line
leftValues.getOrAddShapeProperties().setLineProperties(solidPlum);
// axis font
XDDFRunProperties props = leftValues.getOrAddTextProperties();
props.setFontSize(14.0);
props.setFillProperties(fills[5]);

XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, barCategories, leftValues);
bar.setVaryColors(true);
bar.setBarDirection(chartedCols.length > 1 ? BarDirection.COL : BarDirection.BAR);

for (int c : chartedCols) {
// the data sources
XDDFCategoryDataSource xs = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(1, NUM_OF_ROWS, 0, 0));
XDDFNumericalDataSource<Double> ys = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(1, NUM_OF_ROWS, c, c));
XDDFBarChartData.Series series = (XDDFBarChartData.Series) bar.addSeries(xs, ys);
series.setTitle(null, new CellReference(sheet.getSheetName(), 0, c, true, true));
series.setFillProperties(fills[c]);
series.setLineProperties(solidTurquoise); // bar border color different from fill
}
chart.plot(bar);


// target line
// line of a scatter chart from 0 (min) to 1 (max) having value of target

XDDFValueAxis scatterX = chart.createValueAxis(AxisPosition.TOP);
scatterX.setVisible(false);
scatterX.setTickLabelPosition(AxisTickLabelPosition.NONE);
XDDFValueAxis scatterY = chart.createValueAxis(AxisPosition.RIGHT);
scatterY.setVisible(false);
scatterY.setTickLabelPosition(AxisTickLabelPosition.NONE);
scatterX.crossAxis(scatterY);
scatterY.crossAxis(scatterX);
if (chartedCols.length > 1) {
scatterX.setMaximum(1.0);
} else {
scatterY.setMaximum(1.0);
}

XDDFScatterChartData scatter = (XDDFScatterChartData) chart.createData(ChartTypes.SCATTER, scatterX, scatterY);
scatter.setVaryColors(true);

// This only works for Excel since OpenOffice or LibreOffice Calc does not support literal numeric data series.
XDDFNumericalDataSource<Double> targetDS = XDDFDataSourcesFactory.fromArray(new Double[] { target, target });
XDDFNumericalDataSource<Double> zeroOneDS = XDDFDataSourcesFactory.fromArray(new Double[] { 0.0, 1.0 });

if (chartedCols.length > 1) {
// BarDirection.COL then X axis is from 0 to 1 and Y axis is target axis
scatter.addSeries(zeroOneDS, targetDS).setLineProperties(solidTurquoise);
} else {
// BarDirection.BAR then X axis is target axis and Y axis is from 0 to 1
scatter.addSeries(targetDS, zeroOneDS).setLineProperties(solidTurquoise);
}

chart.plot(scatter);


// legend
if (chartedCols.length > 1) {
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.LEFT);
legend.setOverlay(false);

// delete additional target line series legend entry
XDDFLegendEntry entry = legend.addEntry();
entry.setIndex(0);
entry.setDelete(true);
}


// from https://stackoverflow.com/questions/51530552/
// customize the chart

// do not auto delete the title
chart.setAutoTitleDeleted(false);

// plot area background and border line
XDDFShapeProperties chartProps = chart.getOrAddShapeProperties();
chartProps.setFillProperties(solidAlmond);
chartProps.setLineProperties(new XDDFLineProperties(solidGray));

// line style of cat axis
XDDFLineProperties categoriesProps = new XDDFLineProperties(solidGray);
categoriesProps.setWidth(2.1);
barCategories.getOrAddShapeProperties().setLineProperties(categoriesProps);
}

public static void main(String[] args) throws Exception {

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("targetline");
final int NUM_OF_COLUMNS = 4;

// create some data
XSSFRow row;
XSSFCell cell;
String[] headings = new String[] { "Year", "Male", "Female", "Other" };
int rowIndex = 0;
row = sheet.createRow(rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
cell = row.createCell(colIndex);
cell.setCellValue(headings[colIndex]);
}
double[][] values = new double[][] { new double[] { 1980, 56.0, 44.1, 12.2 },
new double[] { 1985, 34.5, 41.0, 4 }, new double[] { 1990, 65.0, 68.5, 9.1 },
new double[] { 1995, 34.7, 47.6, 4.9 }, new double[] { 2000, 23.0, 64.5, 11.1 },
new double[] { 2005, 56.3, 69.8, 9.5 } };
for (; rowIndex < NUM_OF_ROWS; rowIndex++) {
row = sheet.createRow(rowIndex + 1);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
cell = row.createCell(colIndex);
cell.setCellValue(values[rowIndex][colIndex]);
}
}

int[] chartedCols = new int[] { 1, 2 , 3 };

XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = null;
if (chartedCols.length > 1) {
anchor = drawing.createAnchor(0, 0, 0, 0, 0, 8, 10, 23);
} else {
anchor = drawing.createAnchor(0, 0, 0, 0, 0, 8, 5, 23);
}
XSSFChart chart = drawing.createChart(anchor);
createChart(chart, sheet, chartedCols, 42.0);

workbook.write(new FileOutputStream("ExcelChartWithTargetLine.xlsx"));
workbook.close();

}
}
36 changes: 36 additions & 0 deletions src/ooxml/java/org/apache/poi/xddf/usermodel/Angles.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,36 @@
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */

package org.apache.poi.xddf.usermodel;

public class Angles {
/**
* OOXML represents an angle in 60,000ths of a degree.
*
* Positive angles are clockwise (i.e., towards the positive y axis);
* negative angles are counter-clockwise (i.e., towards the negative y axis).
*/
public static final int OOXML_DEGREE = 60_000;

public static final int degreesToAttribute(double angle) {
return (int) (OOXML_DEGREE * angle);
}

public static final double attributeToDegrees(int angle) {
return angle / ((double) OOXML_DEGREE);
}
}
Loading