java - Apache poi在excel图表中添加标签
问题描述
public class LineChart {
public static void main(String[] args) throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFSheet sheet = wb.createSheet("linechart");
final int NUM_OF_ROWS = 3;
final int NUM_OF_COLUMNS = 10;
// Create a row and put some cells in it. Rows are 0 based.
Row row;
Cell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
row = sheet.createRow((short) rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
cell = row.createCell((short) colIndex);
cell.setCellValue(colIndex * (rowIndex + 1.0));
}
}
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
XSSFChart chart = drawing.createChart(anchor);
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.BOTTOM);
// Use a category axis for the bottom axis.
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
//bottomAxis.setTitle("x"); // https://stackoverflow.com/questions/32010765
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
//leftAxis.setTitle("f(x)");
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
XDDFDataSource<Double> xs = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(xs, ys1);
series1.setTitle("2x", null); // https://stackoverflow.com/questions/21855842
series1.setSmooth(false); // https://stackoverflow.com/questions/29014848
series1.setMarkerStyle(MarkerStyle.DOT); // https://stackoverflow.com/questions/39636138
series1.setShowLeaderLines(true);
XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(xs, ys2);
series2.setTitle("3x", null);
//series2.setSmooth(true);
//series2.setMarkerSize((short) 6);
//series2.setMarkerStyle(MarkerStyle.NONE); // https://stackoverflow.com/questions/39636138
series2.setShowLeaderLines(false);
chart.plot(data);
// if your series have missing values like https://stackoverflow.com/questions/29014848
// chart.displayBlanksAs(DisplayBlanks.GAP);
// https://stackoverflow.com/questions/24676460
solidLineSeries(data, 0, PresetColor.CHARTREUSE);
solidLineSeries(data, 1, PresetColor.BLACK);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("ooxml-line-chart.xlsx")) {
wb.write(fileOut);
}
}
}
//CTPresetLineDashProperties
private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFLineProperties line = new XDDFLineProperties();
//line.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));
line.setFillProperties(fill);
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
}
}
这是一个示例:http: //svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/LineChart.java
而我使用的poi版本是4.1.1。
当我使用 series1.setShowLeaderLines(true) 或 series2.setShowLeaderLines(false) 时,它将显示标签为图 1,并包含系列名称、类别名称、值和图例键,标签位置也是正确的。
是否可以更改仅包含值且标签位置为中心的图 2?谢谢!!!
解决方案
问题是XDDF
还没有完成。如果不存在,XDDFLineChartData.Series.setShowLeaderLines
则设置默认数据标签。
在当前Excel
版本中,默认数据标签意味着包括值、图例键、类别名称和系列名称。所以这一切都显示出来了。
XDDFLineChartData.Series
缺乏自定义数据标签的方法,除了引导线。所以这需要使用低级的ooxml-schemas
类和方法来完成。
在你的情况下:
...
series1.setShowLeaderLines(true); // this sets default data lables
// customizing data labels
int seriesNr = 0;
//chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls()
// .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)255,(byte)255,0});
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls()
.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.CTR);
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowVal().setVal(true);
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowLegendKey().setVal(false);
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowCatName().setVal(false);
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowSerName().setVal(false);
...
推荐阅读
- sql - Oracle CASE 缺少“in”限制的右括号
- python - 找到值后退出列表理解
- c# - 在不同的Excel工作表中循环Excel行C#
- scala - apache spark添加列,这是一个复杂的计算
- javascript - 在循环终止之前我无法在这个 JS 中获得输出
- sql - 如何从重复的mobile_number中选择最高分
- wordpress - 自动将 Wordpress 从多种语言翻译成英语
- python - 如何从选择的嵌套列表中获得随机选择?
- html - “Not a Descendant Of”的 CSS 表达式
- bash - 将字符串签入文件并提取一部分以将其写入 BASH 中的文件