poi 导出图表双Y轴组合图

有一个需求需要导出excel，但是内容需要体现图表，而且这个图表比较复杂，试过了很多框架，最后使用poi实现了。

用到的jar包maven坐标

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>4.1.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>ooxml-schemas</artifactId>
			<version>1.4</version>
		</dependency>
		<!-- Apache POI 的核心组件 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.1.2</version> <!-- 请替换为当前最新的稳定版本 -->
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
	<!--2.poi官网指出需要poi4.x.x版本抛弃了jdk1.7之前的版本，所以适应此版本需要将jdk升级，如果不想升级还有另一种办法就是，使用springBoot单独做一个服务为你的主项目提供一个接口，让主项目去调用生成word流让主项目去接收即可。-->

		<dependency>
			<groupId>commons-lang</groupId>
			<artifactId>commons-lang</artifactId>
			<version>2.6</version>
		</dependency>


		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.5</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
					<scope>test</scope>
		</dependency>

测试类

import cn.hutool.core.io.FileUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class DemoApplicationAITest {
 @Test
    public  void test() throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        String sheetName = "Sheet1";
        FileOutputStream fileOut = null;
        try {
            XSSFSheet sheet = wb.createSheet(sheetName);
            List<String> testList = new ArrayList<String>();
            testList.add("2024-05-15");
            testList.add("2024-05-16");
            testList.add("2024-05-17");
            testList.add("2024-05-18");
            testList.add("2024-05-19");
            testList.add("2024-05-20");
            testList.add("2024-05-21");

            List<Integer> areaList = new ArrayList<Integer>();
            // 第二行，测试值
            areaList.add(10);
            areaList.add(90);
            areaList.add(98);
            areaList.add(95);
            areaList.add(85);
            areaList.add(77);
            areaList.add(32);
            List<Integer> areaList1 = new ArrayList<Integer>();
               // 第二行，测试值
            areaList2.add(15);
            areaList2.add(83);
            areaList2.add(35);
            areaList2.add(72);
            areaList2.add(16);
            areaList2.add(99);
            areaList2.add(32);
            //内存中添加数据
            initDataTable(wb, sheet, testList, areaList, areaList1, areaList2);
            //创建一个画布
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            //前四个默认0，[0,10]：从0列10行开始;[10,30]:宽度10个单元格，30向下扩展到30行
   XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 10, 10, 20, 30);
            //创建一个chart对象
            XSSFChart chart = drawing.createChart(anchor);
            //标题
            chart.setTitleText("测试标题");
            //标题覆盖
            chart.setTitleOverlay(false);
            //图例位置
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);
          XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 1, 7));
            XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 1, 7));
            XDDFNumericalDataSource<Double> area1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 1, 7));
          XDDFNumericalDataSource<Double> area2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(3, 3, 1, 7));


            //分类轴标(X轴),标题位置
            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            bottomAxis.setTitle("X轴标题");
            bottomAxis.setCrosses(AxisCrosses.MIN);
            //值(Y轴)轴,标题位置
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            leftAxis.setTitle("Y轴标题");
       leftAxis.setCrosses(AxisCrosses.MIN);
            leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

            XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
            XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
            bar.setBarDirection(BarDirection.COL);
            series1.setTitle("测试", null);
          XDDFBarChartData.Series series2 = (XDDFBarChartData.Series) bar.addSeries(countries, area1);
            series2.setTitle("测试1", null);
            chart.plot(bar);

            XDDFValueAxis leftAxis1 = chart.createValueAxis(AxisPosition.RIGHT);
            leftAxis1.setTitle("第二条Y轴标题");
            leftAxis1.setCrossBetween(AxisCrossBetween.BETWEEN);
            leftAxis1.setCrosses(AxisCrosses.MAX);
            leftAxis1.crossAxis(bottomAxis);
              bottomAxis.crossAxis(leftAxis1);
            XDDFChartData data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis1);

            data.addSeries(countries, area2).setTitle("测试2",null);
            chart.plot(data);
            //
//            XDDFValueAxis leftAxis1 = chart.createValueAxis(AxisPosition.RIGHT);
//            leftAxis1.setTitle("第二条Y轴");
//            //XDDFNumericalDataSource<Double> area2 = XDDFDataSourcesFactory.fromArray(areaList2.toArray(new Double[areaList2.size()]));
//            //bar：，XDDFChartData可以看看可以实现的视图种类
//            XDDFLineChartData line = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis1);
//            leftAxis1.setCrosses(AxisCrosses.MAX);
//            leftAxis1.setCrossBetween(AxisCrossBetween.BETWEEN);
//            //设置为可变颜色
//            line.setVaryColors(false);
//            //条形图方向，纵向/横向：纵向
//            //bar.setBarDirection(BarDirection.COL);
//            //图表加载数据，条形图1
//            XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) line.addSeries(countries, area2);
//            //条形图例标题
//            series3.setTitle("测试3", null);
            XDDFSolidFillProperties fill3 = new XDDFSolidFillProperties();
            //条形图，填充颜色
            series3.setFillProperties(fill3);
//            chart.plot(line);
//
//
//            CTPlotArea plotArea = chart.getCTChart().getPlotArea();
//            //柱状图1上显示数值
//            plotArea.getBarChartArray(0).getSerArray(0).addNewDLbls();
//            plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
//            plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
//            plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
//            plotArea.getBarChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
//            //***** 非常重要，可以设置excel中的对应（以互补色代表负值），对于条形图有负数的情况，一定要设置，否则颜色无法填充
//            //            plotArea.getBarChartArray(0).getSerArray(0).addNewInvertIfNegative().setVal(false);
//
//            CTPlotArea plotArea1 = chart.getCTChart().getPlotArea();
//            //柱状图1上显示数值
//            plotArea1.getBarChartArray(0).getSerArray(1).addNewDLbls();
//            plotArea1.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowVal().setVal(true);
//            plotArea1.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowLegendKey().setVal(false);
/            plotArea1.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowCatName().setVal(false);
//            plotArea1.getBarChartArray(0).getSerArray(1).getDLbls().addNewShowSerName().setVal(false);
//            //***** 非常重要，可以设置excel中的对应（以互补色代表负值），对于条形图有负数的情况，一定要设置，否则颜色无法填充
//            plotArea1.getBarChartArray(0).getSerArray(1).addNewInvertIfNegative().setVal(false);
//
    // fixChart(chart);

            System.out.println("===================");
            System.out.println(chart.getCTChart());
            System.out.println("===================");

            // 将输出写入excel文件
            String tmpFileName = "D://" + System.currentTimeMillis() + ".xlsx";
            FileUtil.file(tmpFileName);
            fileOut = new FileOutputStream(tmpFileName);
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
         } finally {
            wb.close();
            if (fileOut != null) {
                fileOut.close();
            }
        }
     }
}