java - 使用 org.openxmlformats.schemas.drawingml.x2006.chart 绘制线图

Question

1 我想在 Excel 中使用 Apache POI 和 OpenXmlFormats 在图表中绘制三条线，但是我可以绘制两条线，但无法绘制第三条线，我不知道为什么。请帮助我在一张图中绘制三条线。我正在分享绘制 2 条线的代码..[2] 我在结果中添加了一张我想要的图片。[3]如何在单个Excel文件中绘制多个图表。

public class LineChart {

    public static void main(String[] args) throws Exception {
        String filename = "e:/Graph_5.xlsx";
        if(filename!=null && !filename.equals("")){
            try{
                FileInputStream fis = new FileInputStream(filename);
                XSSFWorkbook wb = new XSSFWorkbook(fis);
                XSSFSheet sheet = wb.getSheetAt(0);
                XSSFDrawing drawing = sheet.createDrawingPatriarch();
                XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 25, 15);
                XSSFChart chart = drawing.createChart(anchor);

                CTChart ctChart = ((XSSFChart)chart).getCTChart();  
                CTPlotArea ctPlotArea = ctChart.getPlotArea();
                //the line chart
                CTLineChart ctLineChart_1 = ctPlotArea.addNewLineChart();
                CTBoolean ctBoolean_1 = ctLineChart_1.addNewVaryColors();
                ctBoolean_1.setVal(false);
                //the line series
                CTLineSer ctLineSer_1 = ctLineChart_1.addNewSer();
                CTSerTx ctSerTx_1 = ctLineSer_1.addNewTx();
                CTStrRef ctStrRef_1 = ctSerTx_1.addNewStrRef();
                ctStrRef_1.setF("Sheet1!$B$1");

                ctLineSer_1.addNewIdx().setVal(0);  
                CTAxDataSource cttAxDataSource_1 = ctLineSer_1.addNewCat();
                ctStrRef_1 = cttAxDataSource_1.addNewStrRef();
                ctStrRef_1.setF("Sheet1!$A$2:$A$65"); 

                CTNumDataSource ctNumDataSource_1 = ctLineSer_1.addNewVal();
                CTNumRef ctNumRef_1 = ctNumDataSource_1.addNewNumRef();
                ctNumRef_1.setF("Sheet1!$B$2:$B$65");
                //at least the border lines in Libreoffice Calc ;-)
                ctLineSer_1.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});   
                //telling the BarChart that it has axes and giving them Ids
                ctLineChart_1.addNewAxId().setVal(123456); //cat axis 1 (line)
                ctLineChart_1.addNewAxId().setVal(123457); //val axis 1 (left)

                // Line-2
                CTLineChart ctLineChart_2 = ctPlotArea.addNewLineChart();
                CTBoolean ctBoolean_2 = ctLineChart_2.addNewVaryColors();
                ctBoolean_2.setVal(false);

                CTLineSer ctLineSer_2 = ctLineChart_2.addNewSer();
                CTSerTx ctSerTx_2 = ctLineSer_2.addNewTx();
                CTStrRef ctStrRef_2 = ctSerTx_2.addNewStrRef();
                ctStrRef_2.setF("Sheet1!$C$1");

                ctLineSer_2.addNewIdx().setVal(1);
                CTAxDataSource cttAxDataSource_2 = ctLineSer_2.addNewCat();
                ctStrRef_2 = cttAxDataSource_2.addNewStrRef();
                ctStrRef_2.setF("Sheet1!$A$2:$A$65");

                CTNumDataSource ctNumDataSource_2 = ctLineSer_2.addNewVal();
                CTNumRef ctNumRef_2 = ctNumDataSource_2.addNewNumRef();
                ctNumRef_2.setF("Sheet1!$C$2:$C$65");

                ctLineSer_2.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{0,0,0});
                ctLineChart_2.addNewAxId().setVal(123458);
                ctLineChart_2.addNewAxId().setVal(123459);

                /*line-1 cat axis(X-axis) 1 (line)*/
                CTCatAx ctCatAx_1 = ctPlotArea.addNewCatAx(); 
                ctCatAx_1.addNewAxId().setVal(123456); //id of the cat axis
                CTScaling ctScaling_1 = ctCatAx_1.addNewScaling();
                ctScaling_1.addNewOrientation().setVal(STOrientation.MIN_MAX);
                ctCatAx_1.addNewDelete().setVal(false);
                ctCatAx_1.addNewAxPos().setVal(STAxPos.B);
                ctCatAx_1.addNewCrossAx().setVal(123457); //id of the val axis
                ctCatAx_1.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

                //val axis 1(Y-axis) (left)
                CTValAx ctValAx_1 = ctPlotArea.addNewValAx(); 
                ctValAx_1.addNewAxId().setVal(123457); //id of the val axis
                ctScaling_1 = ctValAx_1.addNewScaling();
                ctScaling_1.addNewOrientation().setVal(STOrientation.MIN_MAX);
                ctValAx_1.addNewDelete().setVal(false);
                ctValAx_1.addNewAxPos().setVal(STAxPos.L);
                ctValAx_1.addNewCrossAx().setVal(123456); //id of the cat axis
                ctValAx_1.addNewCrosses().setVal(STCrosses.AUTO_ZERO); //this val axis crosses the cat axis at zero
                ctValAx_1.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

                //line-2 cat axis(X-axis) 1 (line)

                CTCatAx ctCatAx_2 = ctPlotArea.addNewCatAx(); 
                ctCatAx_2.addNewAxId().setVal(123458); //id of the cat axis
                CTScaling ctScaling_2 = ctCatAx_2.addNewScaling();
                ctScaling_2.addNewOrientation().setVal(STOrientation.MIN_MAX);
                ctCatAx_2.addNewDelete().setVal(true);
                ctCatAx_2.addNewAxPos().setVal(STAxPos.B);
                ctCatAx_2.addNewCrossAx().setVal(123459); //id of the val axis
                ctCatAx_2.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

                //val axis 1(Y-axis) (left)
                CTValAx ctValAx_2 = ctPlotArea.addNewValAx(); 
                ctValAx_2.addNewAxId().setVal(123459); //id of the val axis
                ctScaling_2 = ctValAx_2.addNewScaling();
                ctScaling_2.addNewOrientation().setVal(STOrientation.MIN_MAX);
                ctValAx_2.addNewDelete().setVal(true);
                ctValAx_2.addNewAxPos().setVal(STAxPos.L);
                ctValAx_2.addNewCrossAx().setVal(123458); //id of the cat axis
                ctValAx_2.addNewCrosses().setVal(STCrosses.AUTO_ZERO); //this val axis crosses the cat axis at zero
                ctValAx_2.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);


                /legend
                CTLegend ctLegend = ctChart.addNewLegend();
                ctLegend.addNewLegendPos().setVal(STLegendPos.B);
                ctLegend.addNewOverlay().setVal(false);

                FileOutputStream fileOut = new FileOutputStream("SingleLineChart.xlsx");
                wb.write(fileOut);
                fileOut.close();
                wb.close();
            }catch(Exception e){}
        }
    }
}

上面的代码接受一个输入 Excel 文件并完美地绘制 2 行，但是当我绘制第 3 行时，它不会给出任何错误。当我打开 Excel 文件时，它没有打开。

我已经分享了我想要的输出的链接。请检查它并帮助我实现相同的目标。我将非常感谢你。 multiple chart in excel multiplecharts.png

*用 3 条线绘制多个图表 *

public class MultipleChartWithThreeLines{
    private static XDDFLineChartData.Series addLineSeriesToChartData(XDDFChartData chartData, XSSFSheet sheet, String categoryDataRef, String valueDataRef, String seriesTitleRef, PresetColor lineColor) {

        XDDFDataSource<Double> categoryData = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress.valueOf(categoryDataRef));
        XDDFNumericalDataSource<Double> valueData = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress.valueOf(valueDataRef));

        XDDFLineChartData.Series series = (XDDFLineChartData.Series) chartData.addSeries(categoryData, valueData);
        series.setTitle("", new CellReference(seriesTitleRef)); // https://stackoverflow.com/questions/21855842
        series.setSmooth(false); // https://stackoverflow.com/questions/29014848

        // define data-point marker
        series.setMarkerStyle(MarkerStyle.CIRCLE); // https://stackoverflow.com/questions/39636138

        // define line color
        // https://stackoverflow.com/questions/24676460
        XDDFShapeProperties shapeProperties = series.getShapeProperties();
        if (shapeProperties == null) {
            shapeProperties = new XDDFShapeProperties();
        }
        shapeProperties.setLineProperties(solidLineWithColor(lineColor));
        series.setShapeProperties(shapeProperties);

        // if your series have missing values like https://stackoverflow.com/questions/29014848
        // chart.displayBlanksAs(DisplayBlanks.GAP);

        return series;
    }

    private static XDDFLineProperties solidLineWithColor(PresetColor color) {
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
        XDDFLineProperties line = new XDDFLineProperties();
        line.setFillProperties(fill);
        return line;
    }

    private static XDDFChartLegend addLegendToChart(XSSFChart chart) {
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.BOTTOM);

        return legend;
    }

    private static XSSFChart createChartOnSheet(XSSFSheet sheet, int col1, int row1, int col2, int row2) {
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, col1, row1, col2, row2);
        XSSFChart chart = drawing.createChart(anchor);

        return chart;
    }

    private static XDDFChartAxis[] addAxesToChart(XSSFChart chart, String titleCategoryBottom, String titleValueLeft) {
        XDDFChartAxis[] axesCatVal = new XDDFChartAxis[4];

        // category axis at the bottom
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        bottomAxis.setTitle(titleCategoryBottom); // https://stackoverflow.com/questions/32010765
        axesCatVal[0] = bottomAxis;

        // value axis at the left
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        leftAxis.setTitle(titleValueLeft);
        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
        axesCatVal[1] = leftAxis;

        return axesCatVal;
    }


    private static void writeWorkbookToFile(XSSFWorkbook wb, String filename) throws IOException {
        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
    }

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

        String workbookFilename = "e:/Graph_5.xlsx"; //"e:/Graph_5.xlsx";

        // open workbook with data
        XSSFWorkbook wb = new XSSFWorkbook(workbookFilename);

        // draw chart with 3 lines
        XSSFSheet sheet = wb.getSheetAt(0);
        String sheetName = sheet.getSheetName();

        System.out.println("Drawing line-chart on sheet: " + sheetName);

        // create chart
        XSSFChart chart = createChartOnSheet(sheet, 6, 0, 25, 15);
         //second chart
        XSSFChart medianAngleChart = createChartOnSheet(sheet,10,17,25,15);

        // add legend to chart
        addLegendToChart(chart);
        addLegendToChart(medianAngleChart);


        // add value (left) and category (bottom) axes
        XDDFChartAxis[] axesCatVal = addAxesToChart(chart, "", "Inscribed Angle");       // Add data (as Line Chart)
        XDDFChartAxis[] axesCatVal_1 = addAxesToChart(medianAngleChart, "", "Median Angle");       // Add data (as Line Chart)

        // add line-chart data-collection to chart
        XDDFLineChartData chartData = (XDDFLineChartData) chart.createData(ChartTypes.LINE, axesCatVal[0], (XDDFValueAxis) axesCatVal[1]);
        XDDFLineChartData chartData_1 = (XDDFLineChartData) medianAngleChart.createData(ChartTypes.LINE, axesCatVal_1[0], (XDDFValueAxis) axesCatVal_1[1]);

        // Line-1
        XDDFLineChartData.Series series1 = addLineSeriesToChartData(chartData
                , sheet
                ,sheetName + "!$B$3:$B$66"
                , sheetName + "!$D$3:$D$66"
                , sheetName + "!$D$2"
                ,  PresetColor.RED
        );
        System.out.println("added line 1: \n" + series1);

        // Line-2
        XDDFLineChartData.Series series2 = addLineSeriesToChartData(chartData
                , sheet
                ,sheetName+"!$B$3:$B$66"
                , sheetName+"!$E$3:$E$66"
                , sheetName+"!$E$2"
                ,  PresetColor.GREEN
        );
        System.out.println("added line 2: \n" + series2);

        // Line-3
        XDDFLineChartData.Series series3 = addLineSeriesToChartData(chartData
                , sheet
                , sheetName+"!$B$3:$B$66"
                , sheetName+"!$F$3:$F$66"
                , sheetName+"!$F$2"
                ,  PresetColor.BLUE
        );
        System.out.println("added line 3: \n" + series3);

        //second chart Line-1
        XDDFLineChartData.Series series4 = addLineSeriesToChartData(chartData_1
                , sheet
                , sheetName+"!$B$3:$B$66"
                , sheetName+"!$G$3:$G$66"
                , sheetName+"!$G$2"
                ,  PresetColor.BLUE
        );
        System.out.println("added line 4: \n" + series4);

        chart.plot(chartData);
        medianAngleChart.plot(chartData_1);

        // save workbook

       writeWorkbookToFile(wb,"ChartWithThreeLines.xlsx");

        // close workbook
        wb.close();
    }

}

Answer 1

备注

由于您没有提供任何测试数据(即 XLS 或 CSV 文件)，我选择了美国的 3 个已知城市，其 average monthly temperature 。

您也没有提供所需的输出(即您首选的图表最终应该是什么样子)。所以我选择了simple line-chart具有单一值(value)轴和 3 个不同系列(属于同一值(value)体系)。

生成的图表如下所示(在 LibreOffice 中):

解决方案

public class ChartWith3Lines {

    private static XDDFLineChartData.Series addLineSeriesToChartData(XDDFChartData chartData, XSSFSheet sheet, String categoryDataRef, String valueDataRef, String seriesTitleRef, PresetColor lineColor) {

        XDDFDataSource<Double> categoryData = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress.valueOf(categoryDataRef));
        XDDFNumericalDataSource<Double> valueData = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress.valueOf(valueDataRef));

        XDDFLineChartData.Series series = (XDDFLineChartData.Series) chartData.addSeries(categoryData, valueData);
        series.setTitle("", new CellReference(seriesTitleRef)); // https://stackoverflow.com/questions/21855842
        series.setSmooth(false); // https://stackoverflow.com/questions/29014848

        // define data-point marker
        series.setMarkerStyle(MarkerStyle.CIRCLE); // https://stackoverflow.com/questions/39636138

        // define line color
        // https://stackoverflow.com/questions/24676460
        XDDFShapeProperties shapeProperties = series.getShapeProperties();
        if (shapeProperties == null) {
            shapeProperties = new XDDFShapeProperties();
        }
        shapeProperties.setLineProperties(solidLineWithColor(lineColor));
        series.setShapeProperties(shapeProperties);

        // if your series have missing values like https://stackoverflow.com/questions/29014848
        // chart.displayBlanksAs(DisplayBlanks.GAP);

        return series;
    }

    private static XDDFLineProperties solidLineWithColor(PresetColor color) {
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
        XDDFLineProperties line = new XDDFLineProperties();
        line.setFillProperties(fill);
        return line;
    }

    private static XDDFChartLegend addLegendToChart(XSSFChart chart) {
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.BOTTOM);

        return legend;
    }

    private static XSSFChart createChartOnSheet(XSSFSheet sheet, int col1, int row1, int col2, int row2) {
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, col1, row1, col2, row2);

        XSSFChart chart = drawing.createChart(anchor);

        return chart;
    }

    private static XDDFChartAxis[] addAxesToChart(XSSFChart chart, String titleCategoryBottom, String titleValueLeft) {
        XDDFChartAxis[] axesCatVal = new XDDFChartAxis[2];

        // category axis at the bottom
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        bottomAxis.setTitle(titleCategoryBottom); // https://stackoverflow.com/questions/32010765
        axesCatVal[0] = bottomAxis;

        // value axis at the left
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        leftAxis.setTitle(titleValueLeft);
        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
        axesCatVal[1] = leftAxis;

        return axesCatVal;
    }

    private static void writeWorkbookToFile(XSSFWorkbook wb, String filename) throws IOException {
        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
    }

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

        String workbookFilename = "SampleData.xlsx"; //"e:/Graph_5.xlsx";

        // open workbook with data
        XSSFWorkbook wb = new XSSFWorkbook(workbookFilename);

        // draw chart with 3 lines
        XSSFSheet sheet = wb.getSheetAt(0);
        String sheetName = sheet.getSheetName();

        System.out.println("Drawing line-chart on sheet: " + sheetName);

        // create chart
        XSSFChart chart = createChartOnSheet(sheet, 4, 0, 10, 15);

        // add legend to chart
        addLegendToChart(chart);

        // add value (left) and category (bottom) axes
        XDDFChartAxis[] axesCatVal = addAxesToChart(chart, "Month", "Avg. Temp. (Fahrenheit)");       // Add data (as Line Chart)
        // add line-chart data-collection to chart
        XDDFLineChartData chartData = (XDDFLineChartData) chart.createData(ChartTypes.LINE, axesCatVal[0], (XDDFValueAxis) axesCatVal[1]);

        // Line-1
        XDDFLineChartData.Series series1 = addLineSeriesToChartData(chartData
                , sheet
                ,sheetName + "!$A$2:$A$13"
                , sheetName + "!$B$2:$B$13"
                , sheetName + "!$B$1"
                ,  PresetColor.RED
        );
        System.out.println("added line 1: \n" + series1);

        // Line-2
        XDDFLineChartData.Series series2 = addLineSeriesToChartData(chartData
                , sheet
                ,sheetName+"!$A$2:$A$13"
                , sheetName+"!$C$2:$C$13"
                , sheetName+"!$C$1"
                ,  PresetColor.GREEN
        );
        System.out.println("added line 2: \n" + series2);

        // Line-3
        XDDFLineChartData.Series series3 = addLineSeriesToChartData(chartData
                , sheet
                , sheetName+"!$A$2:$A$13"
                , sheetName+"!$D$2:$D$13"
                , sheetName+"!$D$1"
                ,  PresetColor.BLUE
        );
        System.out.println("added line 3: \n" + series3);

        chart.plot(chartData);

        // save workbook
        writeWorkbookToFile(wb,"LineChart.xlsx");

        // close workbook
        wb.close();
    }

}

说明

关注..