如何用Java+Apache POI移除Excel图表分类轴刻度线并添加水平网格线

需要移除Excel图表分类轴（Category axis）上的刻度线：数据量较少时无问题，但数据量大（有时需绘制上千条记录）时，横轴附近会形成粗黑条；同时需要为图表添加水平网格线。当前使用Java和Apache POI操作Excel图表。

• 数据量大时的问题：使用200条数据时已出现密集刻度，上千条数据会形成横轴粗黑条
• 期望效果：无分类轴刻度线，带有水平网格线

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
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 EasyChart {

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

        String path = ".\\ExcelFile";

        FileInputStream inputStream = new FileInputStream(path);

        XSSFWorkbook wb = new XSSFWorkbook(inputStream);

        XSSFSheet sheet = wb.getSheetAt(0);

        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 7, 2, 12 ,18); //

        XSSFChart chart = drawing.createChart(anchor);
        chart.setTitleText("Trend");
        chart.setTitleOverlay(false);

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

        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        
        int range= 200;
                
        XDDFDataSource<String> dates = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                new CellRangeAddress(1, range, 0, 0));

        XDDFNumericalDataSource<Double> readIOP = XDDFDataSourcesFactory.fromNumericCellRange(sheet,new CellRangeAddress(2, range, 2, 2));


        XDDFNumericalDataSource<Double> writeIOP = XDDFDataSourcesFactory.fromNumericCellRange(sheet,new CellRangeAddress(2, range, 4, 4));

        XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

        XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(dates, readIOP);
        series1.setTitle("SP");
        series1.setSmooth(false);
        series1.setMarkerStyle(MarkerStyle.NONE);

        XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(dates, writeIOP);
        series2.setTitle("FP");
        series2.setSmooth(true);
        series2.setMarkerStyle(MarkerStyle.NONE);

        chart.plot(data);

        save(wb);
    }

    static void save(XSSFWorkbook wb) throws IOException {
        String path = "D:\\Trends";
        FileOutputStream file = new FileOutputStream(new File(path + "\\output.xlsx"));
        wb.write(file);
        System.out.println("File Saved Successfully");
    }

}

1. 移除分类轴刻度线 #

创建分类轴后，设置主要和次要刻度线样式为NONE，即可消除横轴的密集刻度黑条：

// 移除分类轴刻度线
bottomAxis.setMajorTickMark(TickMark.NONE);
bottomAxis.setMinorTickMark(TickMark.NONE);

如果不需要显示轴标签，还可以添加bottomAxis.setVisible(false)完全隐藏分类轴。

2. 添加水平网格线 #

水平网格线依赖数值轴配置，启用数值轴的主要网格线即可：

// 添加水平网格线
leftAxis.setMajorGridlinesVisible(true);

如果需要自定义网格线样式（比如颜色、宽度），可以补充以下代码：

XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(Color.LIGHT_GRAY));
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
leftAxis.setMajorGridLines(line);

修改后的完整代码 #

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
import org.apache.poi.xddf.usermodel.chart.TickMark;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
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 EasyChart {

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

        String path = ".\\ExcelFile";

        FileInputStream inputStream = new FileInputStream(path);

        XSSFWorkbook wb = new XSSFWorkbook(inputStream);

        XSSFSheet sheet = wb.getSheetAt(0);

        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 7, 2, 12 ,18); //

        XSSFChart chart = drawing.createChart(anchor);
        chart.setTitleText("Trend");
        chart.setTitleOverlay(false);

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

        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        // 移除分类轴刻度线
        bottomAxis.setMajorTickMark(TickMark.NONE);
        bottomAxis.setMinorTickMark(TickMark.NONE);
        
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        // 添加水平网格线
        leftAxis.setMajorGridlinesVisible(true);
        
        int range= 200;
                
        XDDFDataSource<String> dates = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                new CellRangeAddress(1, range, 0, 0));

        XDDFNumericalDataSource<Double> readIOP = XDDFDataSourcesFactory.fromNumericCellRange(sheet,new CellRangeAddress(2, range, 2, 2));


        XDDFNumericalDataSource<Double> writeIOP = XDDFDataSourcesFactory.fromNumericCellRange(sheet,new CellRangeAddress(2, range, 4, 4));

        XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

        XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(dates, readIOP);
        series1.setTitle("SP");
        series1.setSmooth(false);
        series1.setMarkerStyle(MarkerStyle.NONE);

        XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(dates, writeIOP);
        series2.setTitle("FP");
        series2.setSmooth(true);
        series2.setMarkerStyle(MarkerStyle.NONE);

        chart.plot(data);

        save(wb);
    }

    static void save(XSSFWorkbook wb) throws IOException {
        String path = "D:\\Trends";
        FileOutputStream file = new FileOutputStream(new File(path + "\\output.xlsx"));
        wb.write(file);
        System.out.println("File Saved Successfully");
    }

}

内容的提问来源于stack exchange，提问作者Sangharsh Pipare