java读取excel图表模板，修改选值范围

自定义目录标题)

java读取excel图表模板，修改选值范围

梳理一下思路：
首先使用POI读取excel第一个sheet页，在sheet页中获取到图表饼状图或圆环图，修改图形数据并修改选值范围达到自己需要的表格。

package com.adc.da.util.utils;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;



/**
 * @Author: zhengxz
 * @Date: 2019/11/11 13:54
 */
public class ExportExcel {
    public static void main(String[]args){
        List<List<Object>> list = new ArrayList<>();
        List<Object> list_0 = new ArrayList<>();
        List<Object> list_1 = new ArrayList<>();
        HttpServletRequest request = null;
        HttpServletResponse response = null;
        //1为饼状图2为圆环图
        String type = "1";
        list_0.add("");
        list_0.add("Estate");
        list_0.add("Hatchback");
        list_0.add("MPV");
        list_0.add("Sedan");
        list_0.add("SUV");
        list_0.add("VAN");
        list_1.add("单价");
        list_1.add(0.6);
        list_1.add(9.4);
        list_1.add(12.3);
        list_1.add(35.5);
        list_1.add(41.4);
        list_1.add(0.9);
        list.add(list_0);
        list.add(list_1);
        try {
            pieExcel(request,response,type,list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 饼状图圆环图导出
     * @param request
     * @param response
     * @param type
     * @throws Exception
     * @author: zhengxz
     * @time: 2019/11/11 11:34
     */
    public static void pieExcel(HttpServletRequest request, HttpServletResponse response,String type,List<List<Object>> list) throws Exception {

        //加载图表模版
        String filePath =null;
        if("1".equals(type)){
            filePath = "template/excel/pie.xlsx";
        }else if("2".equals(type)){
            filePath = "template/excel/donut.xlsx";
        }

        File file = new File(filePath);
        FileInputStream tps = new FileInputStream(file);
        /*创建excel*/
        XSSFWorkbook wb = new XSSFWorkbook(tps);
        XSSFSheet sheet = wb.getSheetAt(0);

        //设置全局  行宽
        sheet.setDefaultColumnWidth((short) (15));
        /*excel属性*/
        XSSFCellStyle cs = setStyle(wb);

        XSSFFont font = wb.createFont();
        //设置字体格式
        font.setFontName("宋体");
        /*字体大小*/
        font.setFontHeightInPoints((short) 11);
        cs.setFont(font);

        //遍历第一页元素找到图表
        XSSFChart chart;
        XSSFDrawing drawingPatriarch = sheet.getDrawingPatriarch();
        //拿到图形
        List<XSSFChart> charts = drawingPatriarch.getCharts();

        chart = charts.get(0);

        POIXMLDocumentPart xlsPart = chart.getRelations().get(0);

        CTChart ctChart = chart.getCTChart();
        CTPlotArea plotArea = ctChart.getPlotArea();
        // 获取图表的系列
        CTPieSer ser = null;
        //判断是什么图形
        if("1".equals(type)){
            //饼状图
            CTPieChart pieChartArray = plotArea.getPieChartArray(0);
            ser = pieChartArray.getSerArray(0);
            //圆环图
        }else if("2".equals(type)){
            CTDoughnutChart doughnutChartArray = plotArea.getDoughnutChartArray(0);
            ser = doughnutChartArray.getSerArray(0);
        }

        CTSerTx tx = ser.getTx();
        tx.getStrRef().getStrCache().getPtArray(0).setV("ddd");

        CTAxDataSource cat = ser.getCat();
        CTStrData strData = cat.getStrRef().getStrCache();

        //获取图表的值
        CTNumDataSource val = ser.getVal();
        CTNumData numData = val.getNumRef().getNumCache();

        strData.setPtArray(null);  // unset old axis text
        numData.setPtArray(null);  // unset old values
        // set model
        int idx = 0;
        int rownum = 0;
        XSSFRow row_0 = sheet.createRow(0);
        XSSFRow row_1 = sheet.createRow(1);
        for (int i = 0; i < list.get(0).size(); i++) {
            CTNumVal numVal = numData.addNewPt();
            numVal.setIdx(idx);
            numVal.setV(list.get(0).get(i).toString());

            CTStrVal sVal = strData.addNewPt();
            sVal.setIdx(idx);
            sVal.setV(list.get(1).get(i).toString());
            idx++;
            rownum++;
            row_0.createCell(i).setCellValue(list.get(0).get(i).toString());
            Object  o = list.get(1).get(i);
            //必须是数字类型
            if(o instanceof Double){
                row_1.createCell(i).setCellValue((Double) o);
            }else {
                row_1.createCell(i).setCellValue((String) o);
            }

        }
        numData.getPtCount().setVal(idx);
        strData.getPtCount().setVal(idx);

        String numDataRange = new CellRangeAddress(1, 1, 1
            , rownum-1).formatAsString(sheet.getSheetName(), true);
        val.getNumRef().setF(numDataRange);
        String axisDataRange = new CellRangeAddress(0, 0, 1
            , rownum-1).formatAsString(sheet.getSheetName(), true);
        cat.getStrRef().setF(axisDataRange);

        //更新嵌入的workbook
        OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
        wb.write(xlsOut);
        xlsOut.close();
        /*导出excel数据处理*/

        String fileName = "";
        //初始统计图名称为：csvYYYYMMDD
        String nowDateStr = DateUtils.dateToString(new Date(), "yyyyMMdd");
        String excelName = "";
        excelName = fileName + nowDateStr;
        String headStr = "attachment; filename=" + excelName + ".xlsx";
        response.setHeader("Content-Disposition", headStr);
        ServletOutputStream out = response.getOutputStream();
        wb.write(out);
        out.close();
    }

    /**
     * 设置表格风格
     * @param wb
     * @return
     * @author: zhengxz
     * @time: 2019/11/8 9:01
     */
    public static XSSFCellStyle setStyle(XSSFWorkbook wb){

        XSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        cs.setBorderBottom(BorderStyle.THIN);//下边框
        cs.setBorderLeft(BorderStyle.THIN);//左边框
        cs.setBorderRight(BorderStyle.THIN);//右边框
        cs.setBorderTop(BorderStyle.THIN);//上边框
        cs.setAlignment(HorizontalAlignment.CENTER_SELECTION);//左右居中
        cs.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        cs.setWrapText(true);
        return cs;
    }
}