如何使用Apache POI向excel线图添加网格线

this is my code and it generates results successfully. I just want to add gridlines for plot are, but I could not manage it. Can anyone help regarding this issue?

这是我的代码，它成功地生成结果。我只是想为情节添加网格线，但是我做不到。关于这个问题，谁能帮忙吗?

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

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

        Workbook wb = new XSSFWorkbook();
        Sheet dataSheet = wb.createSheet("linechart");
        dataSheet.setDisplayGridlines(true);

        final int NUM_OF_ROWS = 10;
        final int NUM_OF_COLUMNS = 4;

        Row row;
        Cell cell;
        for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
            row = dataSheet.createRow((short) rowIndex);
            for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
                cell = row.createCell((short) colIndex);
                cell.setCellValue(rowIndex * ((colIndex + 1) + ((int) (Math.random() * 10))));
            }
        }

        Drawing drawing = dataSheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, NUM_OF_COLUMNS + 2, 3, NUM_OF_COLUMNS + 15, 20);

        Chart chart = drawing.createChart(anchor);
        ChartLegend legend = chart.getOrCreateLegend();
        legend.setPosition(LegendPosition.RIGHT);

        LineChartData data = chart.getChartDataFactory().createLineChartData();

        ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
        ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

        ChartDataSource<Number> xs = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 0, 0));
        ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 1, 1));
        ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 2, 2));
        ChartDataSource<Number> ys3 = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 3, 3));

        LineChartSeries series1 = data.addSeries(xs, ys1);
        series1.setTitle("one");
        LineChartSeries series2 = data.addSeries(xs, ys2);
        series2.setTitle("two");
        LineChartSeries series3 = data.addSeries(xs, ys3);
        series3.setTitle("three");
        bottomAxis.setMajorTickMark(AxisTickMark.CROSS);
        bottomAxis.setMinorTickMark(AxisTickMark.IN);
        chart.plot(data, bottomAxis, leftAxis);

        XSSFChart xssfChart = (XSSFChart) chart;
        CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();
        plotArea.getLineChartArray()[0].getSmooth();
        CTBoolean ctBool = CTBoolean.Factory.newInstance();
        ctBool.setVal(false);
        plotArea.getLineChartArray()[0].setSmooth(ctBool);
        for (CTLineSer ser : plotArea.getLineChartArray()[0].getSerArray()) {
            ser.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});
            ser.setSmooth(ctBool);
        }


        CTMarker ctMarker = CTMarker.Factory.newInstance();
        ctMarker.setSymbol(CTMarkerStyle.Factory.newInstance());
        for (CTLineSer ser : plotArea.getLineChartArray()[0].getSerArray()) {
            ser.setMarker(ctMarker);
        }

        plotArea.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {11,0,0});

        CTFill myPatternFill= CTFill.Factory.newInstance();                                     
        CTPatternFill pFill=myPatternFill.addNewPatternFill();
        pFill.setPatternType(STPatternType.DARK_DOWN);

        //plotArea.addNewSpPr().addNewLn().addNewGradFill().addNewLin();
       // plotArea.addNewSpPr().addNewBlipFill().addNewBlip();
        //CTTableGrid ctTableGrid = CTTableGrid.Factory.newInstance();
        //ctTableGrid.addNewGridCol();

        FileOutputStream fileOut = new FileOutputStream("chart.xlsx");
        wb.write(fileOut);
        fileOut.close();
        System.out.println("complete!");
    }

}

this is what I got, existing result

这就是我得到的现有结果

this is what I want, desired result

这就是我想要的结果

Can anyone help me about this issue?

在这个问题上有人能帮我吗?

1 个解决方案

#1

You can do it using this code: First create from Chart object CTPlotArea

您可以使用以下代码来完成:首先从Chart对象CTPlotArea创建

CTPlotArea plotArea = chart.getCTChart().getPlotArea();

Then use this for horizontal and vertical axes

然后用这个来做水平轴和垂直轴。

plotArea.getCatAxArray()[0].addNewMajorGridlines();
plotArea.getValAxArray()[0].addNewMajorGridlines();

If you want to display minor grid use this code instead

如果您想显示小网格，请使用此代码

plotArea.getCatAxArray()[0].addNewMinorGridlines();
plotArea.getValAxArray()[0].addNewMinorGridlines();

Code should be placed after chart.plot(data, ChartAxis... chartAxis) method

代码应该放在图表之后。情节(数据,ChartAxis……chartAxis)方法

#1