【POI】Java+poi+ooxml生成Excel（.xlsx）图表（折线图、柱状图、组合图）

最新推荐文章于 2024-11-21 16:18:23 发布

懵a呆

最新推荐文章于 2024-11-21 16:18:23 发布

阅读量9.4k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： POI Java

本文链接：https://blog.csdn.net/u011279583/article/details/105196805

这次开发使用到的是折线图、柱状图、折线+柱状组合图
颇费周折，记个笔记好了

仅通过POI的话，只能实现折线图和散点图
（https://blog.csdn.net/u011279583/article/details/105248887）
但POI还支持Open Xml，通过这种方式可以实现很多类型的chart

感觉ooxml用起来比较复杂，也很少有文档可以参照，再加上excel的图表功能本身就非常庞大，包含了很多细小的设置，所以想要提高效率，首先还是要会一些excel图表的操作，这样在用ooxml开发的时候才能在excel中找到对应，不会一头雾水

效果参考：
折线图
柱状图、组合图
柱状图+组合图
POI和Open Xml版本

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.17</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.17</version>
	</dependency>
	<dependency>
	  <groupId>org.apache.poi</groupId>
	  <artifactId>poi-scratchpad</artifactId>
	  <version>3.17</version>
	</dependency>
       <dependency>
	  <groupId>org.apache.poi</groupId>
	  <artifactId>poi-ooxml-schemas</artifactId>
	  <version>3.17</version>
	</dependency>
	<dependency>
	  <groupId>org.apache.poi</groupId>
	  <artifactId>ooxml-schemas</artifactId>
	  <version>1.3</version>
	</dependency>

代码

import java.awt.Color;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarker;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarkerStyle;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarGrouping;
import org.openxmlformats.schemas.drawingml.x2006.chart.STCrosses;
import org.openxmlformats.schemas.drawingml.x2006.chart.STDispBlanksAs;
import org.openxmlformats.schemas.drawingml.x2006.chart.STGrouping;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLblAlgn;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STMarkerStyle;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickMark;

public class XSSFUtils {
   
   

    public static void testForLineChart(XSSFSheet sheet) {
   
   
        Map<String, Object> arams = new HashMap<String, Object>();

        // 图表位置（B36左上角：AA53左上角），偏移量均为0
        int[] chartPositon = new int[] {
   
   getColumnIndexByAddress("B"), 35, getColumnIndexByAddress("AA"), 52, 0, 0, 0, 0};

        // 设置x轴坐标区域（B60：B90），即“Day”列
        int[] xAxisRange = new int[] {
   
   59, 89, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B")};

        // 设置数据区域，即每个系列的数据（D60：D90、J60：J90、P60：P90）
        // 图例标题（D59、J59、P59）
        List<int[]> seriesRangeList = new ArrayList<int[]>();
        seriesRangeList.add(new int[] {
   
   
                58, 58, XSSFUtils.getColumnIndexByAddress("D"), XSSFUtils.getColumnIndexByAddress("D"),
                59, 89, XSSFUtils.getColumnIndexByAddress("D"), XSSFUtils.getColumnIndexByAddress("D")});
        seriesRangeList.add(new int[] {
   
   
                58, 58, XSSFUtils.getColumnIndexByAddress("J"), XSSFUtils.getColumnIndexByAddress("J"),
                59, 89, XSSFUtils.getColumnIndexByAddress("J"), XSSFUtils.getColumnIndexByAddress("J")});
        seriesRangeList.add(new int[] {
   
   
                58, 58, XSSFUtils.getColumnIndexByAddress("P"), XSSFUtils.getColumnIndexByAddress("P"),
                59, 89, XSSFUtils.getColumnIndexByAddress("P"), XSSFUtils.getColumnIndexByAddress("P")});

        arams.put("chartPosition", chartPositon);
        arams.put("chartTitle", "");
        arams.put("dispBlanksAs", "zero");
        arams.put("legendPosition", "t");
        arams.put("xAxisDataCellRange", xAxisRange);
        arams.put("seriesDataCellRangeList", seriesRangeList);
        arams.put("lineColor", null);
        arams.put("isXAxisDelete", false);
        arams.put("yAxisPosition", "l");

        createLineChart(sheet, arams);
    }

    public static void testForBarChart(XSSFSheet sheet) {
   
   
        Map<String, Object> params = new HashMap<String, Object>();

        // 图表位置（G20自左上角向右偏移50个EMU point：N39自左上角向右偏移50个EMU point）
        int[] chartPositon = new int[] {
   
   XSSFUtils.getColumnIndexByAddress