POI java导出Excel宏文件

前言

宏文件加载不了图标效果
又是一个网上没啥资源教程的问题 😦 特此写一个工具类帮助大家 😃
业务需求需要导出的Excel带有宏文件内容，类似于：👇👇👇

数据导出后自由修改 折线图要联动

准备

首先要实现此功能需要有一个宏模板，结合你自己期望值来制作一个宏模板。我上传自己的供大家参考，文件后缀是.xlsm

链接:https://pan.baidu.com/s/1f91AyQPzq_YDqq5WspuKxA?pwd=mc5p 
提取码:mc5p

注意：创建或者打开新的宏文件一定得启用内容，不启用看不到宏文件效果

明确

其实化繁为简，我们只要在模板中传入数据，就可以制作出宏文件

开发

数据格式参考

首先明确报表数据格式，需要X,Y 轴，表头，折线样式…
在线JSON解析 可用于显示下列数据
echarts报表 看不懂的参数去这随便点开一个看一下就知道了

{
    "yAxis":{
        "splitLine":{
            "show":false
        },
        "name":"单位:%",
        "axisTick":{
            "show":false
        },
        "type":"value"
    },
    "xAxis":{
        "data":[
            "2016-Q1",
            "2016-Q2",
            "2016-Q3",
            "2016-Q4",
            "2017-Q1",
            "2017-Q2" 
        ],
        "splitLine":{
            "show":false
        },
        "axisTick":{
            "show":false
        },
        "type":"category"
    },
    "color":[
        "#3097a1",
        "#b9b9b9",
        "#ff7000",
        "#4db5aa",
        "#b1cf57"
    ],
    "series":[
        {
            "symbol":"circle",
            "data":[
                "100.0",
                "100.0",
                "100.0",
                "100.0" 
            ],
            "name":"MVP",
            "label":{
                "normal":{
                    "show":true,
                    "position":"top"
                }
            },
            "type":"line"
        },
        {
            "symbol":"circle",
            "data":[
                "100.0",
                "100.0",
                "100.0",
                "100.0",
                "100.0",
                "100.0" 
            ],
            "name":"SUV",
            "label":{
                "normal":{
                    "show":true,
                    "position":"top"
                }
            },
            "type":"line"
        }
    ],
    "legend":{
        "data":[
            "MVP",
            "SUV"
        ],
        "bottom":0
    },
    "grid":{
        "bottom":80
    },
    "dataZoom":[
        {
            "filterMode":"filter",
            "bottom":20,
            "xAxisIndex":[
                0
            ],
            "id":"dataZoomX",
            "type":"slider"
        }
    ],
    "tooltip":{
        "trigger":"axis"
    },
    "title":{
        "left":"center",
        "text":"车联网发展趋势"
    }
}

创建数据体

主代码，缺失的代码在下方已补全

public static void exportEquipTrend(HttpServletRequest request, HttpServletResponse response, ExportExcelBo exportExcelBo) {
        JSONObject json = JSONObject.parseObject(exportExcelBo.getExport_json());
        log.info(json.toJSONString());
        String exportConfigName = exportExcelBo.getExport_config_name();
        String configType = exportExcelBo.getExport_config_type();
        String title = "";
        String unit = null ;
        if( configType!= null ){
            switch ( configType){
                case "1":
                    title = "装备率";
                    unit = "%";
                    break;
                case "2":
                    title = "装车率";
                    unit = "%";
                    break;
                default:
                    title = "装车量";
            }
        }

        ChartDto<String> chartDto = new ChartDto<>();
        ArrayList<String> arrayList=new ArrayList();
        arrayList.add("2016-Q1");
        arrayList.add("2016-Q1");
        arrayList.add("2016-Q1");
        arrayList.add("2016-Q1");
        chartDto.setxAxisData(arrayList);

        List<SeriesDataDto<String>> seriesDataDtoList =new ArrayList<>();
        SeriesDataDto<String> seriesDataDto = new SeriesDataDto<>();
        seriesDataDto.setLegend("MVP");
        seriesDataDto.setChartType("line");
        seriesDataDto.setUnit( "PVA走势");
        ArrayList<String> dataList=new ArrayList();
        arrayList.add("100.0");
        arrayList.add("123.0");
        arrayList.add("133.0");
        arrayList.add("143.0");
        seriesDataDto.setDataList(dataList);
        seriesDataDtoList .add( seriesDataDto );
        chartDto.setSeriesData(seriesDataDtoList);
        chartDto.setTitle(title);

 
        chartDto.setTitle(title);
        XSSFWorkbook wb;
        FileInputStream fileInputStream = null;
        try {
        	//获得模板 下便有这个类
            ClassPathResource classPathResource = new ClassPathResource("exceTemplate/product/simple_chart.xlsm");
            wb = new XSSFWorkbook( classPathResource.getInputStream());
          	//设置导出样式 填充数据
            exportChart(chartDto,wb ,(String) request.getSession().getAttribute(exportConfigName) );
            //导出工具类 这个用自定义即可 下边就具体方法 注意设置文件类型
            ExportChatUtils.outPutExcel(response, wb, title+".xlsm");
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(fileInputStream != null){
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

获得模板类

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package org.springframework.core.io;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;
import org.springframework.util.ClassUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

public class ClassPathResource extends AbstractFileResolvingResource {
    private final String path;
    @Nullable
    private ClassLoader classLoader;
    @Nullable
    private Class<?> clazz;

    public ClassPathResource(String path) {
        this(path, (ClassLoader)null);
    }

    public ClassPathResource(String path, @Nullable ClassLoader classLoader) {
        Assert.notNull(path, "Path must not be null");
        String pathToUse = StringUtils.cleanPath(path);
        if (pathToUse.startsWith("/")) {
            pathToUse = pathToUse.substring(1);
        }

        this.path = pathToUse;
        this.classLoader = classLoader != null ? classLoader : ClassUtils.getDefaultClassLoader();
    }

    public ClassPathResource(String path, @Nullable Class<?> clazz) {
        Assert.notNull(path, "Path must not be null");
        this.path = StringUtils.cleanPath(path);
        this.clazz = clazz;
    }

    /** @deprecated */
    @Deprecated
    protected ClassPathResource(String path, @Nullable ClassLoader classLoader, @Nullable Class<?> clazz) {
        this.path = StringUtils.cleanPath(path);
        this.classLoader = classLoader;
        this.clazz = clazz;
    }

    public final String getPath() {
        return this.path;
    }

    @Nullable
    public final ClassLoader getClassLoader() {
        return this.clazz != null ? this.clazz.getClassLoader() : this.classLoader;
    }

    public boolean exists() {
        return this.resolveURL() != null;
    }

    @Nullable
    protected URL resolveURL() {
        try {
            if (this.clazz != null) {
                return this.clazz.getResource(this.path);
            } else {
                return this.classLoader != null ? this.classLoader.getResource(this.path) : ClassLoader.getSystemResource(this.path);
            }
        } catch (IllegalArgumentException var2) {
            return null;
        }
    }

    public InputStream getInputStream() throws IOException {
        InputStream is;
        if (this.clazz != null) {
            is = this.clazz.getResourceAsStream(this.path);
        } else if (this.classLoader != null) {
            is = this.classLoader.getResourceAsStream(this.path);
        } else {
            is = ClassLoader.getSystemResourceAsStream(this.path);
        }

        if (is == null) {
            throw new FileNotFoundException(this.getDescription() + " cannot be opened because it does not exist");
        } else {
            return is;
        }
    }

    public URL getURL() throws IOException {
        URL url = this.resolveURL();
        if (url == null) {
            throw new FileNotFoundException(this.getDescription() + " cannot be resolved to URL because it does not exist");
        } else {
            return url;
        }
    }

    public Resource createRelative(String relativePath) {
        String pathToUse = StringUtils.applyRelativePath(this.path, relativePath);
        return this.clazz != null ? new ClassPathResource(pathToUse, this.clazz) : new ClassPathResource(pathToUse, this.classLoader);
    }

    @Nullable
    public String getFilename() {
        return StringUtils.getFilename(this.path);
    }

    public String getDescription() {
        StringBuilder builder = new StringBuilder("class path resource [");
        String pathToUse = this.path;
        if (this.clazz != null && !pathToUse.startsWith("/")) {
            builder.append(ClassUtils.classPackageAsResourcePath(this.clazz));
            builder.append('/');
        }

        if (pathToUse.startsWith("/")) {
            pathToUse = pathToUse.substring(1);
        }

        builder.append(pathToUse);
        builder.append(']');
        return builder.toString();
    }

    public boolean equals(@Nullable Object other) {
        if (this == other) {
            return true;
        } else if (!(other instanceof ClassPathResource)) {
            return false;
        } else {
            ClassPathResource otherRes = (ClassPathResource)other;
            return this.path.equals(otherRes.path) && ObjectUtils.nullSafeEquals(this.classLoader, otherRes.classLoader) && ObjectUtils.nullSafeEquals(this.clazz, otherRes.clazz);
        }
    }

    public int hashCode() {
        return this.path.hashCode();
    }
}

填充数据

 
    public static void exportChart(ChartDto<String> chartDto, XSSFWorkbook wb, final String headerTitle){

            ExportChatUtils.exportChart(chartDto, wb, new ExportChatUtils.TableHeaderHandler() {
            @Override
            public void createTableHeader(ExcelContext content, List<String> xAxis) {
                int cellIndex = 1;
                content.getRow(3);
                XSSFCell cell = content.getCell(cellIndex++);
                XSSFSheet sheet = content.getCurrentSheet();
                XSSFWorkbook wb = content.getWorkbook();
                sheet.addMergedRegion(new CellRangeAddress(3,4,1,1));
                cell.setCellValue("配置项");
                cell.setCellStyle( content.getDefaultStyle( DefaultStyle.NORMAL ));

                XSSFCellStyle cellStyle = wb.createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                cell = content.getCell(cellIndex);
                cell.setCellValue(headerTitle);
                cell.setCellStyle( cellStyle );

                CellRangeAddress cellRangeAddress = new CellRangeAddress(3, 3, 2, 2 + xAxis.size() - 1);
                RegionUtil.setBorderTop(1, cellRangeAddress , sheet , wb );
                RegionUtil.setBorderRight(1, cellRangeAddress , sheet , wb );
                sheet.addMergedRegion(cellRangeAddress);

                content.getRow(4);
                for (String xAxi : xAxis) {
                    cell = content.getCell( cellIndex++ );
                    cell.setCellValue(xAxi.replace("\n", ""));
                    cell.setCellStyle( content.getDefaultStyle(DefaultStyle.NUMBER) );
                }
            }
        });

    }

导出工具类方法

    public static void outPutExcel(HttpServletResponse response , Workbook wb , String fileName){
        try(ServletOutputStream out  = response.getOutputStream() ) {
            String excelName = java.net.URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="+excelName );
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

完成

导出就可以得到前言中的样式了。其实看代码不难发现，和普通Excel导出没啥区别，麻烦的在于填充数据和制作模板。其他都是按部就班