SpringBoot 导出excel附带折线图

导入jar包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.4</version>
        </dependency>

折线、柱状、饼图实体类

@Accessors(chain = true)
@Data
public class ChartPosition {
    /**
     * 图表的左上角坐标列
     */
    private int col1;
    /**
     * 图表的左上角坐标行
     */
    private int row1;
    /**
     * 图表的右下角坐标列
     */
    private int col2;
    /**
     * 图表的右下角坐标行t
     */
    private int row2;

    /**
     * 下面的为偏移量均设置为0
     */
    private int dx1 = 0;

    private int dy1 = 0;

    private int dx2 = 0;

    private int dy2 = 0;

}

@Data
@Accessors(chain = true)
public class LineChart {
    /**
     * 图表的名称
     */
    private String chartTitle;

    /**
     * 每条折线的名称
     */
    private List<String> titleList;

    /**
     * 每条折线对应的数据 这里的类型根据自己的实际情况给
     */
    private List<List<Double>> dataList;

    /**
     * x轴 这里的类型根据自己的实际情况给
     */
    private List<Object> xAxisList;
}

@Data
@Accessors(chain = true)
public class PieChart {

    /**
     * 饼图每块的名称
     */
    private List<String> titleList;

    /**
     * 饼图每块的数据 这里的类型根据自己的实际情况给
     */
    private List<Integer> dataList;

    /**
     * 饼图标题名称
     */
    private String titleName;
}

工具类

public class ChartUtils {
    private static XSSFChart createDrawingPatriarch(XSSFSheet sheet, ChartPosition chartPosition, String chartTitle) {
        //创建一个画布
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        //前偏移量四个默认0
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, chartPosition.getCol1(), chartPosition.getRow1(), chartPosition.getCol2(), chartPosition.getRow2());
        //创建一个chart对象
        XSSFChart chart = drawing.createChart(anchor);
        //标题
        chart.setTitleText(chartTitle);
        //标题是否覆盖图表
        chart.setTitleOverlay(false);
        return chart;
    }

    /**
     * 柱状图
     *
     * @param sheet
     * @param chartPosition
     * @param pieChart
     */
    public static void createBar(XSSFSheet sheet, ChartPosition chartPosition, PieChart pieChart) {
        String titleName = pieChart.getTitleName();
        List<String> titleList = pieChart.getTitleList();
        List<Integer> dataList = pieChart.getDataList();
        XSSFChart chart = createDrawingPatriarch(sheet, chartPosition, titleName);
        //分类轴标(X轴),标题位置
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        //值(Y轴)轴,标题位置
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        //分类轴标数据
        XDDFDataSource<String> xData = XDDFDataSourcesFactory.fromArray(titleList.toArray(new String[]{}));
        XDDFNumericalDataSource<Integer> values = XDDFDataSourcesFactory.fromArray(dataList.toArray(new Integer[]{}));
        //bar：条形图
        XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
        leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
        //设置为可变颜色
        bar.setVaryColors(true);
        //条形图方向，纵向/横向：纵向
        bar.setBarDirection(BarDirection.COL);
        //图表加载数据，条形图1
        XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(xData, values);
        //条形图例标题
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE_VIOLET));
        //条形图，填充颜色
        series1.setFillProperties(fill);
        //绘制
        chart.plot(bar);
    }

    /**
     * 创建饼图
     *
     * @param sheet 图表
     * @see com.gideon.entity.PieChart  饼图数据的封装
     * @see com.gideon.entity.ChartPosition 饼图的坐标位置
     */
    public static void createPie(XSSFSheet sheet, ChartPosition chartPosition, PieChart pieChart) {
        String titleName = pieChart.getTitleName();
        List<String> titleList = pieChart.getTitleList();
        List<Integer> dataList = pieChart.getDataList();
        XSSFChart chart = createDrawingPatriarch(sheet, chartPosition, titleName);
        //图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.BOTTOM);
        //分类轴标数据
        XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromArray(titleList.toArray(new String[]{}));
        XDDFNumericalDataSource<Integer> values = XDDFDataSourcesFactory.fromArray(dataList.toArray(new Integer[]{}));
        XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
        //设置为可变颜色
        data.setVaryColors(true);
        //图表加载数据
        data.addSeries(countries, values);
        //绘制
        chart.plot(data);
        CTDLbls ctdLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
        ctdLbls.addNewShowVal().setVal(false);
        ctdLbls.addNewShowLegendKey().setVal(false);
        //类别名称
        ctdLbls.addNewShowCatName().setVal(false);
        //百分比
        ctdLbls.addNewShowSerName().setVal(false);
        ctdLbls.addNewShowPercent().setVal(true);
        //引导线
        ctdLbls.addNewShowLeaderLines().setVal(false);
        //分隔符为分行符
        ctdLbls.setSeparator("\n");
        //数据标签内
        ctdLbls.addNewDLblPos().setVal(STDLblPos.Enum.forString("inEnd"));
    }

    /**
     * 创建折线图
     *
     * @param sheet 图表
     * @see com.gideon.entity.PieChart  饼图数据的封装
     * @see com.gideon.entity.ChartPosition 饼图的坐标位置
     */
    public static void createLine(XSSFSheet sheet, ChartPosition chartPosition, LineChart lineChart) {
        List<Object> xAxisList = lineChart.getXAxisList();
        List<String> chartTitleList = lineChart.getTitleList();
        List<List<Double>> chartDataList = lineChart.getDataList();
        String chartTitle = lineChart.getChartTitle();
        XSSFChart chart = createDrawingPatriarch(sheet, chartPosition, chartTitle);
        //图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP);
        //分类轴标(X轴),标题位置
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        //值(Y轴)轴,标题位置
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        //LINE：折线图
        XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
        XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(Arrays.copyOf(xAxisList.toArray(), xAxisList.toArray().length, String[].class));
        for (int i = 0; i < chartDataList.size(); i++) {
            List<Double> floats = chartDataList.get(i);
            XDDFNumericalDataSource<Double> dataSource = XDDFDataSourcesFactory.fromArray(floats.toArray(new Double[]{}));
            //图表加载数据，折线
            XDDFLineChartData.Series series = (XDDFLineChartData.Series) data.addSeries(countries, dataSource);
            series.setTitle(chartTitleList.get(i), null);
            if (chartTitleList.get(i).equals("警戒值") || chartTitleList.get(i).equals("目标值")) {
                XDDFLineProperties line4 = new XDDFLineProperties();
                line4.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
                series.setLineProperties(line4);
            } else {
				//直线
                series.setSmooth(false);
            }
            //设置标记大小
            series.setMarkerSize((short) 2);
            //添加标签数据，折线图中拐点值展示
            series.setShowLeaderLines(true);
//            series.setShowLeaderLines(false);
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(i).getDLbls()
                    .addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.CTR);
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(i).getDLbls().addNewShowVal().setVal(false);//设置拐点值显示
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(i).getDLbls().addNewShowLegendKey().setVal(false);
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(i).getDLbls().addNewShowCatName().setVal(false);//设置拐点的标注显示
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(i).getDLbls().addNewShowSerName().setVal(false);//设置拐点的标注显示
        }
        //绘制
        chart.plot(data);
        if (chartDataList.size() == 1) {
            chart.getCTChart().getPlotArea().getLineChartArray(0).addNewVaryColors().setVal(false);
        }
    }

    /**
     * 创建列表
     */
    public static void createTable(int rowNum, XSSFWorkbook wb, XSSFSheet sheet) {
        // 样式
        XSSFCellStyle titleStyle = createTitleCellStyle(wb);
        XSSFCellStyle contentStyle = createContentCellStyle(wb);
        // 创建第一页的第一行，索引从0开始
        XSSFRow row = sheet.createRow(rowNum);
        row.setHeight((short) 500);
        XSSFCell cell0 = row.createCell(0);
        cell0.setCellValue("动态列表");
        cell0.setCellStyle(tableNameCellStyle(wb));
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 2));

        XSSFRow row1 = sheet.createRow(rowNum + 1);
        XSSFRow row2 = sheet.createRow(rowNum + 2);
        row1.setHeight((short) 600);
        row2.setHeight((short) 600);

        String title0 = "序号";
        XSSFCell cell = row1.createCell(0);
        cell.setCellValue(title0);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        CellRangeAddress region = new CellRangeAddress(rowNum + 1, rowNum + 2, 0, 0);
        sheet.addMergedRegion(region);
        // 合并之后为合并的单元格设置样式
        setRegionStyle(sheet, region, titleStyle);

        String title = "城市";
        XSSFCell c00 = row1.createCell(1);
        c00.setCellValue(title);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNum + 1, rowNum + 2, 1, 1);
        sheet.addMergedRegion(cellRangeAddress);
        setRegionStyle(sheet, cellRangeAddress, titleStyle);

        String[] years = {"21年", "22年", "23年"};
        int startCellIndex = 2;
        int endCellIndex = 4;
        // 动态年份
        for (int i = 0; i < years.length; i++) {
            XSSFCell cell1 = row1.createCell(startCellIndex);
            cell1.setCellValue(years[i]);
            CellRangeAddress cellAddresses = new CellRangeAddress(rowNum + 1, rowNum + 1, startCellIndex, endCellIndex);
            sheet.addMergedRegion(cellAddresses);
            setRegionStyle(sheet, cellAddresses, titleStyle);
            XSSFCell cell11 = row2.createCell(startCellIndex++);
            cell11.setCellValue("动态列1");
            cell11.setCellStyle(titleStyle);
            XSSFCell cell2 = row2.createCell(startCellIndex++);
            cell2.setCellValue("动态列2");
            cell2.setCellStyle(titleStyle);
            XSSFCell cell3 = row2.createCell(startCellIndex++);
            cell3.setCellValue("动态列3");
            cell3.setCellStyle(titleStyle);
            endCellIndex += 3;
        }

        rowNum += 3;
        for (int j = 0; j < 10; j++) {
            int k = j + 1;
            XSSFRow tempRow = sheet.createRow(rowNum);
            rowNum++;
            // 序号
            XSSFCell cell11 = tempRow.createCell(0);
            cell11.setCellValue(k);
            cell11.setCellStyle(contentStyle);
            // 城市
            XSSFCell cell2 = tempRow.createCell(1);
            cell2.setCellValue("城市" + k);
            cell2.setCellStyle(contentStyle);
            int columnIndex = 2;
            int k1 = 1;
            for (int i = 0; i < years.length; i++) {
                XSSFCell cell3 = tempRow.createCell(columnIndex++);
                cell3.setCellValue("测试" + k1++);
                cell3.setCellStyle(contentStyle);
                XSSFCell cell4 = tempRow.createCell(columnIndex++);
                cell4.setCellValue("测试" + k1++);
                cell4.setCellStyle(contentStyle);
                XSSFCell cell5 = tempRow.createCell(columnIndex++);
                cell5.setCellValue("测试" + k1++);
                cell5.setCellStyle(contentStyle);
            }
        }
    }

    /**
     * 为合并的单元格设置样式（可根据需要自行调整）
     */
    @SuppressWarnings("deprecation")
    public static void setRegionStyle(XSSFSheet sheet, CellRangeAddress region, XSSFCellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            XSSFRow row = sheet.getRow(i);
            if (null == row) row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                XSSFCell cell = row.getCell(j);
                if (null == cell) cell = row.createCell(j);
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 列表名称样式
     *
     * @param wb
     * @return
     */
    public static XSSFCellStyle tableNameCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐

        XSSFFont headerFont1 = wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
        return cellStyle;
    }

    /**
     * 创建标题样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        XSSFFont headerFont1 = wb.createFont(); // 创建字体样式
//        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
        return cellStyle;
    }

    /**
     * 创建内容样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成12号字体
        XSSFFont font = wb.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }
}

接口

    public String exportQualityDataContrast(HttpServletResponse response, List<QualityDataContrast> list, QualityDataContrast qualityDataContrast) {
        String fileName = "";
        String tmpPath = "";
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            int nowYear = Integer.parseInt(qualityDataContrast.getDBRQ().substring(0, 4));
            int lastYear = nowYear - 1;
            String sheetName = lastYear + "、" + nowYear + "数据对比";
            XSSFSheet sheet = workbook.createSheet(sheetName);

            // 第一行标题
            String title = lastYear + "、" + nowYear + "数据对比";
            //	设置表头样式
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            //	自动换行
            style.setWrapText(true);


            //	生成第一个字体
            Font font1 = workbook.createFont();
            //	设置字体大小
            font1.setFontHeightInPoints((short) 16);
            font1.setColor(Font.COLOR_NORMAL);
            font1.setBold(true);
            font1.setFontName("微软雅黑");
            //	把字体应用到当前样式
            style.setFont(font1);

            Font font2 = workbook.createFont();
            font2.setFontHeightInPoints((short) 12);
            font2.setColor(Font.COLOR_NORMAL);
            font1.setBold(true);
            font2.setFontName("微软雅黑");

            Font font3 = workbook.createFont();
            font3.setFontHeightInPoints((short) 12);
            font3.setColor(Font.COLOR_NORMAL);
            font3.setFontName("微软雅黑");

            //设置样式
            CellStyle cellStyleRow2 = workbook.createCellStyle();
            cellStyleRow2.setBorderTop(BorderStyle.THIN);
            cellStyleRow2.setBorderBottom(BorderStyle.THIN);
            cellStyleRow2.setBorderLeft(BorderStyle.THIN);
            cellStyleRow2.setBorderRight(BorderStyle.THIN);
            cellStyleRow2.setAlignment(HorizontalAlignment.CENTER);//水平居中
            cellStyleRow2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            cellStyleRow2.setFont(font2);
            cellStyleRow2.setWrapText(true);

            CellStyle cellStyleRow3 = workbook.createCellStyle();
            cellStyleRow3.setBorderTop(BorderStyle.THIN);
            cellStyleRow3.setBorderBottom(BorderStyle.THIN);
            cellStyleRow3.setBorderLeft(BorderStyle.THIN);
            cellStyleRow3.setBorderRight(BorderStyle.THIN);
            cellStyleRow3.setAlignment(HorizontalAlignment.CENTER);//水平居中
            cellStyleRow3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            cellStyleRow3.setFont(font3);

            //	第一行
            Row row0 = sheet.createRow(0);
            //	设置高
            row0.setHeight((short) 400);
            //	合并第一行第一列到第23列的单元格，合并成一个
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 24));
            //	创建第一行第一列
            Cell cell = row0.createCell(0);
            //	设置这一行标题
            cell.setCellValue(title);
            //	设置这一行样式
            cell.setCellStyle(style);

            //存储最大列宽，自定义列宽使用
            Map<Integer, Integer> maxWidth = new HashMap<>();

            // 第二行
            Row row1 = sheet.createRow(1);
            row1.setHeight((short) 300);
           	//按需求合并单元格
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 8));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 12));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 16));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 17, 20));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 21, 24));
           
            Cell cWeek0 = row1.createCell(0);//第1列
            cWeek0.setCellStyle(cellStyleRow2);
            Cell cWeek1 = row1.createCell(1);//第2列
            cWeek1.setCellValue("患者身份识别正确率");
            cWeek1.setCellStyle(cellStyleRow2);
            Cell cWeek2 = row1.createCell(2);//第2列
            cWeek2.setCellStyle(cellStyleRow2);
            Cell cWeek3 = row1.createCell(3);//第2列
            cWeek3.setCellStyle(cellStyleRow2);
            Cell cWeek4 = row1.createCell(4);//第2列
            cWeek4.setCellStyle(cellStyleRow2);
            Cell cWeek5 = row1.createCell(5);//第2列
            cWeek5.setCellValue("急救仪器完备率");
            cWeek5.setCellStyle(cellStyleRow2);
            Cell cWeek6 = row1.createCell(6);//第2列
            cWeek6.setCellStyle(cellStyleRow2);
            Cell cWeek7 = row1.createCell(7);//第2列
            cWeek7.setCellStyle(cellStyleRow2);
            Cell cWeek8 = row1.createCell(8);//第2列
            cWeek8.setCellStyle(cellStyleRow2);
            Cell cWeek9 = row1.createCell(9);//第2列
            cWeek9.setCellValue("抢救物品完备率");
            cWeek9.setCellStyle(cellStyleRow2);
            Cell cWeek10 = row1.createCell(10);//第2列
            cWeek10.setCellStyle(cellStyleRow2);
            Cell cWeek11 = row1.createCell(11);//第2列
            cWeek11.setCellStyle(cellStyleRow2);
            Cell cWeek12 = row1.createCell(12);//第2列
            cWeek12.setCellStyle(cellStyleRow2);
            Cell cWeek13 = row1.createCell(13);//第2列
            cWeek13.setCellValue("抢救药品完备率");
            cWeek13.setCellStyle(cellStyleRow2);
            Cell cWeek14 = row1.createCell(14);//第2列
            cWeek14.setCellStyle(cellStyleRow2);
            Cell cWeek15 = row1.createCell(15);//第2列
            cWeek15.setCellStyle(cellStyleRow2);
            Cell cWeek16 = row1.createCell(16);//第2列
            cWeek16.setCellStyle(cellStyleRow2);
            Cell cWeek17 = row1.createCell(17);//第2列
            cWeek17.setCellValue("出车护士反应时间达标率");
            cWeek17.setCellStyle(cellStyleRow2);
            Cell cWeek18 = row1.createCell(18);//第2列
            cWeek18.setCellStyle(cellStyleRow2);
            Cell cWeek19 = row1.createCell(19);//第2列
            cWeek19.setCellStyle(cellStyleRow2);
            Cell cWeek20 = row1.createCell(20);//第2列
            cWeek20.setCellStyle(cellStyleRow2);
            Cell cWeek21 = row1.createCell(21);//第2列
            cWeek21.setCellValue("重点环节交接正确率");
            cWeek21.setCellStyle(cellStyleRow2);
            Cell cWeek22 = row1.createCell(22);//第2列
            cWeek22.setCellStyle(cellStyleRow2);
            Cell cWeek23 = row1.createCell(23);//第2列
            cWeek23.setCellStyle(cellStyleRow2);
            Cell cWeek24 = row1.createCell(24);//第2列
            cWeek24.setCellStyle(cellStyleRow2);

            Row row2 = sheet.createRow(2);
            Cell cWeek2_0 = row2.createCell(0);//第2列
            cWeek2_0.setCellValue("月份");
           	//计算当前表头的宽度
            maxWidth.put(0, "月份".getBytes().length * 256 + 200);
            cWeek2_0.setCellStyle(cellStyleRow2);
            Cell cWeek2_1 = row2.createCell(1);//第2列
            cWeek2_1.setCellValue(lastYear + "患者身份识别正确率（%）");
            maxWidth.put(1, "患者身份识别正确率（%）".getBytes().length * 256 + 200);
            cWeek2_1.setCellStyle(cellStyleRow2);
            Cell cWeek2_2 = row2.createCell(2);//第2列
            cWeek2_2.setCellValue(nowYear + "患者身份识别正确率（%）");
            maxWidth.put(2, "患者身份识别正确率（%）".getBytes().length * 256 + 200);
            cWeek2_2.setCellStyle(cellStyleRow2);
            Cell cWeek2_3 = row2.createCell(3);//第2列
            cWeek2_3.setCellValue("警戒值");
            maxWidth.put(3, "警戒值".getBytes().length * 256 + 200);
            cWeek2_3.setCellStyle(cellStyleRow2);
            Cell cWeek2_4 = row2.createCell(4);//第2列
            cWeek2_4.setCellValue("目标值");
            maxWidth.put(4, "目标值".getBytes().length * 256 + 200);
            cWeek2_4.setCellStyle(cellStyleRow2);
            Cell cWeek2_5 = row2.createCell(5);//第2列
            cWeek2_5.setCellValue(lastYear + "急救仪器完备率（%）");
            maxWidth.put(5, "急救仪器完备率（%）".getBytes().length * 256 + 200);
            cWeek2_5.setCellStyle(cellStyleRow2);
            Cell cWeek2_6 = row2.createCell(6);//第2列
            cWeek2_6.setCellValue(nowYear + "急救仪器完备率（%）");
            maxWidth.put(6, "急救仪器完备率（%）".getBytes().length * 256 + 200);
            cWeek2_6.setCellStyle(cellStyleRow2);
            Cell cWeek2_7 = row2.createCell(7);//第2列
            cWeek2_7.setCellValue("警戒值");
            maxWidth.put(7, "警戒值".getBytes().length * 256 + 200);
            cWeek2_7.setCellStyle(cellStyleRow2);
            Cell cWeek2_8 = row2.createCell(8);//第2列
            cWeek2_8.setCellValue("目标值");
            maxWidth.put(8, "目标值".getBytes().length * 256 + 200);
            cWeek2_8.setCellStyle(cellStyleRow2);
            Cell cWeek2_9 = row2.createCell(9);//第2列
            cWeek2_9.setCellValue(lastYear + "抢救物品完备率（%）");
            maxWidth.put(9, "抢救物品完备率（%）".getBytes().length * 256 + 200);
            cWeek2_9.setCellStyle(cellStyleRow2);
            Cell cWeek2_10 = row2.createCell(10);//第2列
            cWeek2_10.setCellValue(nowYear + "抢救物品完备率（%）");
            maxWidth.put(10, "抢救物品完备率（%）".getBytes().length * 256 + 200);
            cWeek2_10.setCellStyle(cellStyleRow2);
            Cell cWeek2_11 = row2.createCell(11);//第1列
            cWeek2_11.setCellValue("警戒值");
            maxWidth.put(11, "警戒值".getBytes().length * 256 + 200);
            cWeek2_11.setCellStyle(cellStyleRow2);
            Cell cWeek2_12 = row2.createCell(12);//第2列
            cWeek2_12.setCellValue("目标值");
            maxWidth.put(12, "目标值".getBytes().length * 256 + 200);
            cWeek2_12.setCellStyle(cellStyleRow2);
            Cell cWeek2_13 = row2.createCell(13);//第1列
            cWeek2_13.setCellValue(lastYear + "抢救药品完备率（%）");
            maxWidth.put(13, "抢救药品完备率（%）".getBytes().length * 256 + 200);
            cWeek2_13.setCellStyle(cellStyleRow2);
            Cell cWeek2_14 = row2.createCell(14);//第2列
            cWeek2_14.setCellValue(nowYear + "抢救药品完备率（%）");
            maxWidth.put(14, "抢救药品完备率（%）".getBytes().length * 256 + 200);
            cWeek2_14.setCellStyle(cellStyleRow2);
            Cell cWeek2_15 = row2.createCell(15);//第2列
            cWeek2_15.setCellValue("警戒值");
            maxWidth.put(15, "警戒值".getBytes().length * 256 + 200);
            cWeek2_15.setCellStyle(cellStyleRow2);
            Cell cWeek2_16 = row2.createCell(16);//第1列
            cWeek2_16.setCellValue("目标值");
            maxWidth.put(16, "目标值".getBytes().length * 256 + 200);
            cWeek2_16.setCellStyle(cellStyleRow2);
            Cell cWeek2_17 = row2.createCell(17);//第1列
            cWeek2_17.setCellValue(lastYear + "出车护士反应时间达标率（%）");
            maxWidth.put(17, "出车护士反应时间达标率（%）".getBytes().length * 256 + 200);
            cWeek2_17.setCellStyle(cellStyleRow2);
            Cell cWeek2_18 = row2.createCell(18);//第2列
            cWeek2_18.setCellValue(nowYear + "出车护士反应时间达标率（%）");
            maxWidth.put(18, "出车护士反应时间达标率（%）".getBytes().length * 256 + 200);
            cWeek2_18.setCellStyle(cellStyleRow2);
            Cell cWeek2_19 = row2.createCell(19);//第1列
            cWeek2_19.setCellValue("警戒值");
            maxWidth.put(19, "警戒值".getBytes().length * 256 + 200);
            cWeek2_19.setCellStyle(cellStyleRow2);
            Cell cWeek2_20 = row2.createCell(20);//第2列
            cWeek2_20.setCellValue("目标值");
            maxWidth.put(20, "目标值".getBytes().length * 256 + 200);
            cWeek2_20.setCellStyle(cellStyleRow2);
            Cell cWeek2_21 = row2.createCell(21);//第2列
            cWeek2_21.setCellValue(lastYear + "重点环节交接正确率（%）");
            maxWidth.put(21, "重点环节交接正确率（%）".getBytes().length * 256 + 200);
            cWeek2_21.setCellStyle(cellStyleRow2);
            Cell cWeek2_22 = row2.createCell(22);//第2列
            cWeek2_22.setCellValue(nowYear + "重点环节交接正确率（%）");
            maxWidth.put(22, "重点环节交接正确率（%）".getBytes().length * 256 + 200);
            cWeek2_22.setCellStyle(cellStyleRow2);
            Cell cWeek2_23 = row2.createCell(23);//第2列
            cWeek2_23.setCellValue("警戒值");
            maxWidth.put(23, "警戒值".getBytes().length * 256 + 200);
            cWeek2_23.setCellStyle(cellStyleRow2);
            Cell cWeek2_24 = row2.createCell(24);//第1列
            cWeek2_24.setCellValue("目标值");
            maxWidth.put(24, "目标值".getBytes().length * 256 + 200);
            cWeek2_24.setCellStyle(cellStyleRow2);
			//设置每个表头的宽度
            for (int i = 0; i < 25; i++) {
                sheet.setColumnWidth(i, maxWidth.get(i));
            }

            qualityDataContrast.setDBRQ(String.valueOf(nowYear));
            //-------------------------折线图--------------------------
            Map<String, List<String>> map1 = dataContrastService.selectQualityDataContrastHzsfsb(qualityDataContrast);
            String cTitle1 = "患者身份识别正确率";
            List<LineChart> lineCharts1 = initLineChart(nowYear, lastYear, map1, cTitle1);
            for (LineChart lineChart : lineCharts1) {
                // 图表位置（左上角坐标，右下角坐标） 左上角坐标的（列，行），（右下角坐标）列，行,偏移量均为0
                chartPosition = new ChartPosition()
                        .setRow1(16)//折线图生成开始行
                        .setCol1(1)//开始列
                        .setRow2(32)//结束行
                        .setCol2(4);//结束列
                ChartUtils.createLine(sheet, chartPosition, lineChart);
            }
            fileName = title;
            tmpPath = EmergencyConfig.getProfile() + "/" + fileName + ".xlsx";
            FileOutputStream fos = new FileOutputStream(tmpPath);
            workbook.write(fos);
            fos.flush();
            fos.close();
            //6.提供前端下载
            down(response, tmpPath, fileName);
        }catch (Exception e){
            return null;
        }finally {
            //7.删除临时文件
            File file = new File(tmpPath);
            file.delete();
        }
        return fileName;
    }

    /**
     * 折线图
     *
     * @return
     */
    public List<LineChart> initLineChart(int nowYear, int lastYear, Map<String, List<String>> map, String title) {
        List<LineChart> lineCharts = new ArrayList<>();
        List<String> strings = new ArrayList<>();
        strings.add(lastYear + title);
        strings.add(nowYear + title);
        strings.add("警戒值");
        strings.add("目标值");
        List<Double> lastData = new ArrayList<>();
        List<Double> nowData = new ArrayList<>();
        if (StringUtils.isNotNull(map.get("hzsfsb1"))) {
            for (String hzsfsb1 : map.get("hzsfsb1")) {
                lastData.add(Double.valueOf(hzsfsb1));
            }
            for (String hzsfsb2 : map.get("hzsfsb2")) {
                nowData.add(Double.valueOf(hzsfsb2));
            }
        } else if (StringUtils.isNotNull(map.get("jjyq1"))) {
            for (String jjyq1 : map.get("jjyq1")) {
                lastData.add(Double.valueOf(jjyq1));
            }
            for (String jjyq2 : map.get("jjyq2")) {
                nowData.add(Double.valueOf(jjyq2));
            }
        } else if (StringUtils.isNotNull(map.get("qjwp1"))) {
            for (String qjwp1 : map.get("qjwp1")) {
                lastData.add(Double.valueOf(qjwp1));
            }
            for (String qjwp2 : map.get("qjwp2")) {
                nowData.add(Double.valueOf(qjwp2));
            }
        } else if (StringUtils.isNotNull(map.get("qjyp1"))) {
            for (String qjyp1 : map.get("qjyp1")) {
                lastData.add(Double.valueOf(qjyp1));
            }
            for (String qjyp2 : map.get("qjyp2")) {
                nowData.add(Double.valueOf(qjyp2));
            }
        } else if (StringUtils.isNotNull(map.get("cchsfysj1"))) {
            for (String cchsfysj1 : map.get("cchsfysj1")) {
                lastData.add(Double.valueOf(cchsfysj1));
            }
            for (String cchsfysj2 : map.get("cchsfysj2")) {
                nowData.add(Double.valueOf(cchsfysj2));
            }
        } else if (StringUtils.isNotNull(map.get("zdhjjj1"))) {
            for (String zdhjjj1 : map.get("zdhjjj1")) {
                lastData.add(Double.valueOf(zdhjjj1));
            }
            for (String zdhjjj2 : map.get("zdhjjj2")) {
                nowData.add(Double.valueOf(zdhjjj2));
            }
        }
        List<Double> jjz = new ArrayList<>();
        List<Double> mbz = new ArrayList<>();
        for (String jjz1 : map.get("jjz")) {
            jjz.add(Double.valueOf(jjz1));
        }
        for (String mbz1 : map.get("mbz")) {
            mbz.add(Double.valueOf(mbz1));
        }

        List<List<Double>> list = Arrays.asList(lastData, nowData, jjz, mbz);
        lineCharts.add(new LineChart()
                .setChartTitle(title)
                .setTitleList(strings)
                .setDataList(list)
                .setXAxisList(Arrays.<Object>asList("1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月")));
        return lineCharts;
    }

    private void down(HttpServletResponse response, String filePath, String realFileName) {
        String percentEncodedFileName = null;
        try {
            percentEncodedFileName = percentEncode(realFileName);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        StringBuilder contentDispositionValue = new StringBuilder();
        contentDispositionValue.append("attachment; filename=").append(percentEncodedFileName).append(";").append("filename*=").append("utf-8''").append(percentEncodedFileName);
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
        response.setHeader("Content-disposition", contentDispositionValue.toString());
        response.setHeader("download-filename", percentEncodedFileName);
        try (BufferedInputStream bis = new BufferedInputStream(new FileInputStream(filePath));
             // 输出流
             BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());) {
            byte[] buff = new byte[1024];
            int len = 0;
            while ((len = bis.read(buff)) > 0) {
                bos.write(buff, 0, len);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 百分号编码工具方法
     *
     * @param s 需要百分号编码的字符串
     * @return 百分号编码后的字符串
     */
    public static String percentEncode(String s) throws UnsupportedEncodingException {
        String encode = URLEncoder.encode(s, StandardCharsets.UTF_8.toString());
        return encode.replaceAll("\\+", "%20");
    }