java动态传值导出Excel

导出样式如下图

1，导入jar包

compile ('cn.afterturn:easypoi-base:3.2.0')
compile ('cn.afterturn:easypoi-web:3.2.0')
compile ('cn.afterturn:easypoi-annotation:3.2.0')

2、核心参数展示


表体文字部分，数字动态计算出来的

每个等级对应的表体数据

3、核心代码

package com.qxkj.inspect.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import static org.apache.poi.ss.usermodel.BorderStyle.THIN;

/**
 *批量销售单导出工具类
 * @author pantao
 */
public class OrderBatchExportUtil {


    private static Workbook workbook;
    private static Sheet sheet;

    /**
     * 创建行元素
     * @param style    样式
     * @param height   行高
     * @param value    行显示的内容
     * @param row1     起始行
     * @param row2     结束行
     * @param col1     起始列
     * @param col2     结束列
     */
    private static void createRow(CellStyle style, int height, String value, int row1, int row2, int col1, int col2){

        //设置从第row1行合并到第row2行，第col1列合并到col2列
        sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));
        //设置第几行
        Row rows = sheet.createRow(row1);
        //设置行高
        rows.setHeight((short) height);
        //设置内容开始的列
        Cell cell = rows.createCell(col1);
        //设置样式
        cell.setCellStyle(style);
        //设置该行的值
        cell.setCellValue(value);
    }

    /**
     * 创建样式
     * @param fontSize   字体大小
     * @param bold   是否加粗
     * @return
     */
    private static CellStyle getStyle(int fontSize, boolean bold, boolean border,boolean center){
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        if (bold){
            font.setBold(true);
        }
        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        //设置水平垂直居中
        if(center){
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置单元格边框
        if (border){
            style.setBorderRight(THIN);
            style.setBorderLeft(THIN);
            style.setBorderBottom(THIN);
            style.setBorderTop(THIN);
            style.setLocked(true);
        }
        return style;
    }


    /**
     * 根据数据集生成Excel，并返回Excel文件流
     * @param headKey 表头部汉字
     * @param headValue 表头部值
     * @param middleHead 表体文字
     * @param middleValue 表体列表值
     * @return
     * @throws IOException
     */
    public static byte[] getExcelFile(List<String> headKey, List<Object> headValue,Map<String,List<String>> middleHead
            , Map<String, List<List>> middleValue) throws IOException {
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet("表格");
        int startRow = createHeadCell(headKey, headValue);

        // 创建表体数据，建立新的cell样式
        CellStyle cellStyle = getStyle(11,false,true,true);
        setCellData(middleHead,middleValue,startRow,cellStyle);

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        return baos.toByteArray();
    }

    /**
     * 创建表体数据
     * @param middleHead
     * @param cellStyle
     * @param startRow
     * @param middleValue
     */
    private static void setCellData(Map<String,List<String>> middleHead, Map<String, List<List>> middleValue, int startRow, CellStyle cellStyle){
        int m = 0;
        for(String key : middleHead.keySet()){
            List<String> head = middleHead.get(key);
            List<List> values = middleValue.get(key);
            //创建表体数据的表头
            Row row2 = sheet.createRow(startRow + 1 + m);
            row2.setHeight((short) 1000);
            Cell fcell = null;
            // 建立新的cell样式
            for (int i = 0; i < head.size(); i++) {
                fcell = row2.createCell(i);
                fcell.setCellStyle(cellStyle);
                fcell.setCellValue(head.get(i));
                sheet.setColumnWidth(i, 3500);
            }

            //创建数据
            Row row = null;
            Cell cell = null;
            int i = startRow + 2 + m;
            for (List value : values) {
                row = sheet.createRow(i);
                row.setHeight((short) 0x190);
                int j = 0;
                for (Object o : value) {
                    cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(o == null ? "" : o.toString());
                    j++;
                }
                m++;
                i++;
            }
            Row rowLast = sheet.createRow(startRow + 2 + m);
            rowLast.setHeight((short) 0x190);
            //每个等级表体数据中间相隔两行
            m = m+2;
        }
    }

    /**
     * 创建表头
     * @param headKey
     * @param headValue
     */
    private static int createHeadCell(List<String> headKey, List<Object> headValue) {
        // 表头标题
        CellStyle titleStyle = getStyle(18,true,false,true);
        createRow(titleStyle,0x190,"Inspection Analysis",0,0,0,14);
        createRow(titleStyle,0x190,"检验分析报告",1,1,0,14);

        //除去表头的两行,从第三行开始 j=2
        int j = 2;
        for (int i = 0; i < headKey.size(); i++) {
            //增加一行数据，
            Row row = sheet.createRow(j);
            row.setHeight((short) 0x190);
            sheet.setColumnWidth(i,3500);
            //此处目的是多个等级的时候，能有间隔行
            if(i>6){
                row = sheet.createRow(j + 1);
                row.setHeight((short) 0x190);
                j++;
            }
			//特殊逻辑处理，如果你们的数据比较规整，就不用做此处理
            if(i >= 6 || i %2 == 0){
                sheet.addMergedRegion(new CellRangeAddress(j, j, 0, 1));
                Cell cell = row.createCell(0);
                cell.setCellStyle(getStyle(12, false, false,false));
                cell.setCellValue(headKey.get(i));
            }

            if(i < 6){
                if(i % 2 == 0){

                    //合并单元格，sheet.addMergedRegion(new CellRangeAddress(j, j, 2, 5));
                    Cell cell1 = row.createCell(2);
                    cell1.setCellStyle(getStyle(12, false, false,false));
                    cell1.setCellValue(headValue.get(i).toString());

                    sheet.addMergedRegion(new CellRangeAddress(j, j, 6, 7));
                    Cell cell2 = row.createCell(6);
                    cell2.setCellStyle(getStyle(12, false, false,false));
                    cell2.setCellValue(headKey.get(i + 1));

                    Cell cell22 = row.createCell(8);
                    cell22.setCellStyle(getStyle(12, false, false,false));
                    cell22.setCellValue(headValue.get(i + 1).toString());
                }

            }else if (i == 6){
                //设置验后数量展示
                //设置验后米数value值的展示方式
                List<String> str = Arrays.asList(headValue.get(i).toString().split(","));
                for (int m = 0; m < str.size(); m++) {
                    Cell cel11 = row.createCell(2 + 2 * m);
                    cel11.setCellStyle(getStyle(12, false, false,false));
                    cel11.setCellValue(str.get(m));
                }
            }else{

                //设置每个等级数据的展示格式
                List<String> str = Arrays.asList(headValue.get(i).toString().split(","));
                for (int n = 0; n < str.size(); n++) {
                    Cell cel11 = null;
                    if(n == 0){
                        cel11 = row.createCell(2 + n);
                    }else if(n == 1){
                        cel11 = row.createCell(2 + 2 * n);
                    }else {
                        cel11 = row.createCell(5);
                    }
                    row.setHeight((short) 0x190);
                    cel11.setCellStyle(getStyle(12, false, false,false));
                    cel11.setCellValue(str.get(n));
                }
            }
            j++;
        }
        return j;
    }

}


@GetMapping("/export/{id}")
    public ResponseEntity exportOrders(@PathVariable String id, String barCode) throws IOException {
        HttpHeaders heads = new HttpHeaders() {{
            setContentType(MediaType.APPLICATION_OCTET_STREAM);
            ContentDisposition attachment = ContentDisposition.builder("attachment")
                    .filename(id + ".xlsx", Charset.forName("UTF-8")).build();
            setContentDisposition(attachment);
        }};
        return new ResponseEntity<>(inspectOrderService.exportOrder(id,barCode), heads, HttpStatus.OK);
 }

注：如有疑问可以相互交流