java - 使用Apache POI在Excel中创建条形图

我正在尝试使用Apache POI在XLSX电子表格中创建条形图，但是Excel一直在说内容存在问题，并在尝试打开文件时删除该图。这是我要执行的操作的完整代码:public static void createBarChart() { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet2"); Row row; Cell cell; row = sheet.createRow(0); row.createCell(0); row.createCell(1).setCellValue("HEADER 1"); row.createCell(2).setCellValue("HEADER 2"); row.createCell(3).setCellValue("HEADER 3"); for (int r = 1; r < 5; r++) { row = sheet.createRow(r); cell = row.createCell(0); cell.setCellValue("Serie " + r); cell = row.createCell(1); cell.setCellValue(123); cell = row.createCell(2); cell.setCellValue(456); cell = row.createCell(3); cell.setCellValue(789); } Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 20); Chart chart = drawing.createChart(anchor); CTChart ctChart = ((XSSFChart) chart).getCTChart(); ctChart.addNewTitle(); CTPlotArea ctPlotArea = ctChart.getPlotArea(); CTBarChart ctBarChart = ctPlotArea.addNewBarChart(); ctBarChart.addNewBarDir().setVal(STBarDir.COL); ctBarChart.addNewGrouping().setVal(STBarGrouping.CLUSTERED); ctBarChart.addNewVaryColors().setVal(false); // First series CTBarSer ctBarSer = ctBarChart.addNewSer(); ctBarSer.addNewIdx().setVal(0); ctBarSer.addNewOrder().setVal(0); // [01] Title CTSerTx ctSerTx = ctBarSer.addNewTx(); CTStrRef ctSerTxStrRef = ctSerTx.addNewStrRef(); ctSerTxStrRef.setF("Sheet2!$A$2"); CTStrData strCache = ctSerTxStrRef.addNewStrCache(); strCache.addNewPtCount().setVal(1); CTStrVal newPt = strCache.addNewPt(); newPt.setV("Serie 1"); newPt.setIdx(0); // [01] SpPr CTShapeProperties spPr = ctBarSer.addNewSpPr(); CTSolidColorFillProperties solidFill = spPr.addNewSolidFill(); solidFill.addNewSchemeClr().setVal(STSchemeColorValImpl.ACCENT_1); spPr.addNewLn().addNewNoFill(); spPr.addNewEffectLst(); // [01] Invert if negative ctBarSer.addNewInvertIfNegative().setVal(false); // [01] Series titles CTAxDataSource newCat = ctBarSer.addNewCat(); CTStrRef addNewStrRef = newCat.addNewStrRef(); addNewStrRef.setF("Sheet2!$B$1:$D$1"); CTStrData addNewStrCache = addNewStrRef.addNewStrCache(); addNewStrCache.addNewPtCount().setVal(3); CTStrVal pt1 = addNewStrCache.addNewPt(); pt1.setIdx(0); pt1.setV(sheet.getRow(0).getCell(1).getStringCellValue()); CTStrVal pt2 = addNewStrCache.addNewPt(); pt2.setIdx(1); pt2.setV(sheet.getRow(0).getCell(2).getStringCellValue()); CTStrVal pt3 = addNewStrCache.addNewPt(); pt3.setIdx(2); pt3.setV(sheet.getRow(0).getCell(3).getStringCellValue()); // [01] Series values CTNumDataSource newVal = ctBarSer.addNewVal(); CTNumRef numRef = newVal.addNewNumRef(); numRef.setF("Sheet2!$B$2:$D$2"); CTNumData numCache = numRef.addNewNumCache(); numCache.addNewPtCount().setVal(3); CTNumVal numpt1 = numCache.addNewPt(); numpt1.setIdx(0); numpt1.setV(String.valueOf(sheet.getRow(1).getCell(1).getNumericCellValue())); CTNumVal numpt2 = numCache.addNewPt(); numpt2.setIdx(1); numpt2.setV(String.valueOf(sheet.getRow(1).getCell(2).getNumericCellValue())); CTNumVal numpt3 = numCache.addNewPt(); numpt3.setIdx(2); numpt3.setV(String.valueOf(sheet.getRow(1).getCell(3).getNumericCellValue())); // dLbls CTDLbls dLbls = ctBarChart.addNewDLbls(); dLbls.addNewShowBubbleSize().setVal(false); dLbls.addNewShowLegendKey().setVal(false); dLbls.addNewShowCatName().setVal(false); dLbls.addNewShowSerName().setVal(false); dLbls.addNewShowPercent().setVal(false); dLbls.addNewShowVal().setVal(false); FileOutputStream fileOut = new FileOutputStream("barchart.xlsx"); wb.write(fileOut); wb.close(); fileOut.close(); }谁能帮助我找到(并且很好地解决)这个问题？提前致谢! 最佳答案 ew，难产；-)。对于不了解背景Apache POI supports only ScatterCharts and LineCharts why?的用户。原则上描述了如何进行。就像我说的。首先使用最简单的条形图进行测试。那是一个有两个值的系列。然后，您会看到您完全忘记了代码中的轴。饼图没有轴。这就是为什么没有必要的原因。但是，没有轴条形图将无法正常工作。相反，您可以肆无忌forget地忘记所有被称为“缓存”的东西。是否需要其他东西取决于尝试和错误。也可以阅读Office OpenXML recommendation。可能缺少所有具有minOccurs “0”`的元素会导致在打开工作簿时删除图形。条形图的简单示例:import java.io.FileOutputStream;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.*;import org.apache.poi.ss.usermodel.charts.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFChart;import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;public class BarChart { public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet1"); Row row; Cell cell; row = sheet.createRow(0); row.createCell(0); row.createCell(1).setCellValue("HEADER 1"); row.createCell(2).setCellValue("HEADER 2"); row.createCell(3).setCellValue("HEADER 3"); for (int r = 1; r < 5; r++) { row = sheet.createRow(r); cell = row.createCell(0); cell.setCellValue("Serie " + r); cell = row.createCell(1); cell.setCellValue(new java.util.Random().nextDouble()); cell = row.createCell(2); cell.setCellValue(new java.util.Random().nextDouble()); cell = row.createCell(3); cell.setCellValue(new java.util.Random().nextDouble()); } Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20); Chart chart = drawing.createChart(anchor); CTChart ctChart = ((XSSFChart)chart).getCTChart(); CTPlotArea ctPlotArea = ctChart.getPlotArea(); CTBarChart ctBarChart = ctPlotArea.addNewBarChart(); CTBoolean ctBoolean = ctBarChart.addNewVaryColors(); ctBoolean.setVal(true); ctBarChart.addNewBarDir().setVal(STBarDir.COL); for (int r = 2; r < 6; r++) { CTBarSer ctBarSer = ctBarChart.addNewSer(); CTSerTx ctSerTx = ctBarSer.addNewTx(); CTStrRef ctStrRef = ctSerTx.addNewStrRef(); ctStrRef.setF("Sheet1!$A$" + r); ctBarSer.addNewIdx().setVal(r-2); CTAxDataSource cttAxDataSource = ctBarSer.addNewCat(); ctStrRef = cttAxDataSource.addNewStrRef(); ctStrRef.setF("Sheet1!$B$1:$D$1"); CTNumDataSource ctNumDataSource = ctBarSer.addNewVal(); CTNumRef ctNumRef = ctNumDataSource.addNewNumRef(); ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r); //at least the border lines in Libreoffice Calc ;-) ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0}); } //telling the BarChart that it has axes and giving them Ids ctBarChart.addNewAxId().setVal(123456); ctBarChart.addNewAxId().setVal(123457); //cat axis CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); ctCatAx.addNewAxId().setVal(123456); //id of the cat axis CTScaling ctScaling = ctCatAx.addNewScaling(); ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX); ctCatAx.addNewDelete().setVal(false); ctCatAx.addNewAxPos().setVal(STAxPos.B); ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO); //val axis CTValAx ctValAx = ctPlotArea.addNewValAx(); ctValAx.addNewAxId().setVal(123457); //id of the val axis ctScaling = ctValAx.addNewScaling(); ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX); ctValAx.addNewDelete().setVal(false); ctValAx.addNewAxPos().setVal(STAxPos.L); ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO); //legend CTLegend ctLegend = ctChart.addNewLegend(); ctLegend.addNewLegendPos().setVal(STLegendPos.B); ctLegend.addNewOverlay().setVal(false);System.out.println(ctChart); FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx"); wb.write(fileOut); fileOut.close(); }}此示例需要FAQ-N10025中提到的所有模式ooxml-schemas-1.3.jar的完整jar。上面的代码一直工作到apache poi 3.17。以下代码使用apache poi 4.1.0起作用。它需要所有模式ooxml-schemas-1.4.jar的完整jar。import java.io.FileOutputStream;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFChart;import org.apache.poi.xssf.usermodel.XSSFDrawing;import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;public class BarChart { public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet1"); Row row; Cell cell; row = sheet.createRow(0); row.createCell(0); row.createCell(1).setCellValue("HEADER 1"); row.createCell(2).setCellValue("HEADER 2"); row.createCell(3).setCellValue("HEADER 3"); for (int r = 1; r < 5; r++) { row = sheet.createRow(r); cell = row.createCell(0); cell.setCellValue("Serie " + r); cell = row.createCell(1); cell.setCellValue(new java.util.Random().nextDouble()); cell = row.createCell(2); cell.setCellValue(new java.util.Random().nextDouble()); cell = row.createCell(3); cell.setCellValue(new java.util.Random().nextDouble()); } XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20); XSSFChart chart = drawing.createChart(anchor); CTChart ctChart = ((XSSFChart)chart).getCTChart(); CTPlotArea ctPlotArea = ctChart.getPlotArea(); CTBarChart ctBarChart = ctPlotArea.addNewBarChart(); CTBoolean ctBoolean = ctBarChart.addNewVaryColors(); ctBoolean.setVal(true); ctBarChart.addNewBarDir().setVal(STBarDir.COL); for (int r = 2; r < 6; r++) { CTBarSer ctBarSer = ctBarChart.addNewSer(); CTSerTx ctSerTx = ctBarSer.addNewTx(); CTStrRef ctStrRef = ctSerTx.addNewStrRef(); ctStrRef.setF("Sheet1!$A$" + r); ctBarSer.addNewIdx().setVal(r-2); CTAxDataSource cttAxDataSource = ctBarSer.addNewCat(); ctStrRef = cttAxDataSource.addNewStrRef(); ctStrRef.setF("Sheet1!$B$1:$D$1"); CTNumDataSource ctNumDataSource = ctBarSer.addNewVal(); CTNumRef ctNumRef = ctNumDataSource.addNewNumRef(); ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r); //at least the border lines in Libreoffice Calc ;-) ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0}); } //telling the BarChart that it has axes and giving them Ids ctBarChart.addNewAxId().setVal(123456); ctBarChart.addNewAxId().setVal(123457); //cat axis CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); ctCatAx.addNewAxId().setVal(123456); //id of the cat axis CTScaling ctScaling = ctCatAx.addNewScaling(); ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX); ctCatAx.addNewDelete().setVal(false); ctCatAx.addNewAxPos().setVal(STAxPos.B); ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO); //val axis CTValAx ctValAx = ctPlotArea.addNewValAx(); ctValAx.addNewAxId().setVal(123457); //id of the val axis ctScaling = ctValAx.addNewScaling(); ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX); ctValAx.addNewDelete().setVal(false); ctValAx.addNewAxPos().setVal(STAxPos.L); ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO); //legend CTLegend ctLegend = ctChart.addNewLegend(); ctLegend.addNewLegendPos().setVal(STLegendPos.B); ctLegend.addNewOverlay().setVal(false);System.out.println(ctChart); FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx"); wb.write(fileOut); fileOut.close(); }}