Apache POI Excel:如何在图表中设置颜色透明度?

Question

我正在努力解决如何使用Apache POI在图表中设置颜色的不透明度/透明度/alpha。使用XDDF类，似乎没有办法做到这一点。XDDFColor的文档在创建这个对象时没有办法设置这个值。我认为唯一可行的方法是从XDDFColor中访问底层的CTHslColor，如下所示。

XDDFColor xddfColor = XDDFColor.from(PresetColor.LIGHT_GREEN);
CTColor container = xddfColor.getColorContainer();
CTHslColor hslColor = container.addNewHslClr();
CTPositiveFixedPercentage p = hslColor.addNewAlpha();
p.setVal(50000);
XDDFChartData.Series series = data.getSeries(2);
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(xddfColor);
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
series.setFillProperties(fill);

虽然在图表中正确设置了颜色，但透明度不起作用。是否有任何方法可以使用POI设置面积图的填充颜色透明度?

Answer 1

如果XDDFColor为instanceof XDDFColorPreset，则构造器XDDFSolidFillProperties(XDDFColor)创建具有与给定XDDFColor对应的CTPresetColor的CTSolidColorFillProperties。然后是你应该设置alpha，而不是之前操作XDDFColor。此外，您应该设置填充属性以及XDDFShapeProperties的线条属性和系列属性。所以:

private static void solidFillSeries(XDDFChartData data, int index, PresetColor color, int alpha) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties ctSolidColorFillProperties = 
(org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties)fill.getXmlObject();
org.openxmlformats.schemas.drawingml.x2006.main.CTPresetColor ctPresetColor = ctSolidColorFillProperties.getPrstClr();
org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveFixedPercentage ctPositiveFixedPercentage = ctPresetColor.addNewAlpha();
ctPositiveFixedPercentage.setVal(alpha);
XDDFChartData.Series series = data.getSeries(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setFillProperties(fill);
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
properties.setLineProperties(line);
series.setShapeProperties(properties);
}

完整的示例:

import java.io.FileOutputStream;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class CreateExcelXDDFAreaChart {
public static void main(String[] args) throws Exception {
try (XSSFWorkbook document = new XSSFWorkbook()) {
XSSFSheet sheet = document.createSheet("SurfaceChart");

// create the data
String[] categories = new String[] { "c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9" };
Double[] values1 = new Double[] { 10d, 20d, 10d, 15d, 12d, 20d, 10d, 18d, 19d };
Double[] values2 = new Double[] { 20d, 10d, 15d, 20d, 11d, 17d, 18d, 20d, 10d };
Double[] values3 = new Double[] { 14.5d, 14d, 13.5d, 13d, 12.5d, 12d, 11.5d, 11d, 10.5d };
int r = 0;
for (String cat : categories) {
sheet.createRow(r).createCell(0).setCellValue(cat);
sheet.getRow(r).createCell(1).setCellValue(values1[r]);
sheet.getRow(r).createCell(2).setCellValue(values2[r]);
sheet.getRow(r).createCell(3).setCellValue(values3[r]);
r++;
}
// create the chart
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 5, 0, 20, 30);
XDDFChart chart = drawing.createChart(anchor);

// create data sources
int numOfPoints = categories.length;
XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 0, 0));
XDDFNumericalDataSource<Double> valuesData1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 1, 1));
XDDFNumericalDataSource<Double> valuesData2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 2, 2));
XDDFNumericalDataSource<Double> valuesData3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 3, 3));

// area chart
XDDFCategoryAxis categoryAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFValueAxis valueAxis = chart.createValueAxis(AxisPosition.LEFT);
valueAxis.setCrosses(AxisCrosses.AUTO_ZERO);

XDDFChartData data = chart.createData(ChartTypes.AREA, categoryAxis, valueAxis);

XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);
series.setTitle("Series 1", null);
series = data.addSeries(categoriesData, valuesData2);
series.setTitle("Series 2", null);
series = data.addSeries(categoriesData, valuesData3);
series.setTitle("Series 3", null);

chart.plot(data);

// set legend 
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.BOTTOM);

solidFillSeries(data, 0, PresetColor.RED, 50000);
solidFillSeries(data, 1, PresetColor.GREEN, 50000);
solidFillSeries(data, 2, PresetColor.BLUE, 50000);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("./CreateExcelXDDFAreaChart.xlsx")) {
document.write(fileOut);
}
}
}

private static void solidFillSeries(XDDFChartData data, int index, PresetColor color, int alpha) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties ctSolidColorFillProperties = 
(org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties)fill.getXmlObject();
org.openxmlformats.schemas.drawingml.x2006.main.CTPresetColor ctPresetColor = ctSolidColorFillProperties.getPrstClr();
org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveFixedPercentage ctPositiveFixedPercentage = ctPresetColor.addNewAlpha();
ctPositiveFixedPercentage.setVal(alpha);
XDDFChartData.Series series = data.getSeries(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setFillProperties(fill);
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
properties.setLineProperties(line);
series.setShapeProperties(properties);
}

}

提示:CTPositiveFixedPercentagealpha值为百分数，单位为千分之一。所以从0到100000 50000是50%