java excel 导出多个sheet

第一步：需要一个通用的装载数据的实体类

package com.yfh.common.core.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
import java.util.Map;


@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelDataVo implements Serializable {
    
    private String sheetName;


    
    private String sheetTopColHeaderTitle;


    
    private String[] sheetTopColHeaderName;


    
    private String[] sheetTopColHeaderAttribute;


    
    private List> sheetDataList;
}

第二步：通用的封装数据的工具类

package com.yfh.common.core.utils;

import com.yfh.common.core.domain.ExcelDataVo;
import com.yfh.common.core.exception.baseException;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class PackExcelSheetsDataUtil {
    
    public void packExcelSheetsData(HttpServletResponse response, String excelName, List excelDataVoList) throws Exception{
        try {
            // 对excel进行判断
            for(int j = 0; j < excelDataVoList.size(); j++){
                String[] headerName = excelDataVoList.get(j).getSheetTopColHeaderName();
                String[] headerAttribute = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
                if(headerName.length != headerAttribute.length){
                    throw new baseException("列头长度与属性长度不对应！");
                }
            }

            // 创建一个excel对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            OutputStream out = response.getOutputStream();

            //定义标题以及设置响应头信息
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(excelName+".xls", "UTF-8"));

            // 循环生成指定数量的sheet表
            for(int j = 0; j < excelDataVoList.size(); j++){
                // 获取单个sheet的数据
                String sheetName = excelDataVoList.get(j).getSheetName();
                String sheetTopColHeaderTitle = excelDataVoList.get(j).getSheetTopColHeaderTitle();
                String[] headers = excelDataVoList.get(j).getSheetTopColHeaderName();
                String[] headerCol = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
                List> sheetDataList = excelDataVoList.get(j).getSheetDataList();

                // 申请一个最后的list集合并封装数据
                List dataList = new ArrayList();
                Object[] objs = null;
                for(int i = 0; i < sheetDataList.size(); i++){
                    // 封装lie数据
                    objs = new Object[headers.length];
                    for (int k = 0; k < headers.length; k++) {
                        objs[k] = sheetDataList.get(i).get(headerCol[k]);
                    }

                    //数据添加到excel表格
                    dataList.add(objs);
                }

                //使用流将数据导出
                new ExcelSheetsUtil(sheetName, sheetTopColHeaderTitle, headers, dataList).export(workbook);
            }

            // 最后将整个excel全部写到浏览器
            workbook.write(out);
            out.flush();
            out.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

第三步：导出工具类

package com.yfh.common.core.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;


public class ExcelSheetsUtil {

    //导出表的列名
    private String[] rowName;

    //导出表的头部标题
    private String oneheaders;

    //sheet表表名
    private String sheettitle;

    // 需要导出的数据集合
    private List dataList;

    
    public ExcelSheetsUtil(String sheettitle, String oneheaders, String[] rowName, List dataList){
        this.dataList = dataList;
        this.oneheaders = oneheaders;
        this.rowName = rowName;
        this.sheettitle = sheettitle;
    }

    
    public void export(HSSFWorkbook workbook) throws Exception{
        try{
            // 创建工作表
            HSSFSheet sheet = workbook.createSheet(sheettitle);

            //获取列头样式对象
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);

            //单元格样式对象
            HSSFCellStyle style = this.getStyle(workbook);

            //第一行
            HSSFRow rowfirstName = sheet.createRow(0);
            //创建列头对应个数的单元格
            HSSFCell oneCellRowName = rowfirstName.createCell(0);
            //设置列头单元格的数据类型
            oneCellRowName.setCellType(CellType.STRING);
            HSSFRichTextString onetext = new HSSFRichTextString(oneheaders);
            //设置列头单元格的值
            oneCellRowName.setCellValue(onetext);
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0,0,0, rowName.length - 1));
            //设置列头单元格样式
            oneCellRowName.setCellStyle(columnTopStyle);


            // 定义所需列数
            int columnNum = rowName.length;

            
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName = sheet.createRow(1);
            //单元格样式对象
            HSSFCellStyle styleHearer = this.getStyle(workbook);
            Font headerFont = workbook.createFont();
            headerFont.setFontName("微软雅黑");
            headerFont.setFontHeightInPoints((short) 10);
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.BLACK.getIndex());
            styleHearer.setFont(headerFont);
            for(int n = 0; n < columnNum; n++){
                //创建列头对应个数的单元格
                HSSFCell  cellRowName = rowRowName.createCell(n);
                //设置列头单元格的数据类型
                cellRowName.setCellType(CellType.STRING);
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(styleHearer);
            }


            
            //将查询出的数据设置到sheet对应的单元格中
            for(int i=0;i 
第四步：测试导出
 
    @ApiOperation(value = "测试多个导出sheet", notes = "测试多个导出sheet", httpMethod = "GET")
    @RequestMapping(value = "/exportTeenagerMoreSheet", method = RequestMethod.GET)
    public void exportTeenagerMoreSheet(HttpServletResponse response){
        // 定义学生信息
        List> list1 = Lists.newArrayList();
        for (int i = 0; i < 3; i++) {
            Map map1 = Maps.newHashMap();
            map1.put("name", "张三"+i);
            map1.put("sex", "男"+i);
            map1.put("age", "23"+i);
            map1.put("isSchool", "是"+i);
            list1.add(map1);
        }

        // 定义学校信息
        List> list2 = Lists.newArrayList();
        for (int i = 0; i < 4; i++) {
            Map map2 = Maps.newHashMap();
            map2.put("name", "杭州天长小学"+i);
            map2.put("adress", "浙江省杭州市"+i);
            map2.put("concat", "王五"+i);
            list2.add(map2);
        }

        // 定义其他信息
        List> list3 = Lists.newArrayList();
        for (int i = 0; i < 10; i++) {
            Map map3 = Maps.newHashMap();
            map3.put("protity", "其他1"+i);
            map3.put("remark", "我是备注"+i);
            list3.add(map3);
        }


        // 封装需要导出的数据
        List excelDataVoList = Lists.newArrayList();

        // 封装学生信息
        ExcelDataVo excelDataSheetOne = new ExcelDataVo();
        excelDataSheetOne.setSheetName("学生信息");
        excelDataSheetOne.setSheetTopColHeaderTitle("学生信息");
        excelDataSheetOne.setSheetTopColHeaderName(new String[] {"姓名","性别","年龄", "学生"});
        excelDataSheetOne.setSheetTopColHeaderAttribute(new String[] {"name","sex","age", "isSchool"});
        excelDataSheetOne.setSheetDataList(list1);
        excelDataVoList.add(excelDataSheetOne);

        // 封装学校信息
        ExcelDataVo excelDataSheetTwo = new ExcelDataVo();
        excelDataSheetTwo.setSheetName("学校信息");
        excelDataSheetTwo.setSheetTopColHeaderTitle("学校信息");
        excelDataSheetTwo.setSheetTopColHeaderName(new String[] {"学校","地址","联系人"});
        excelDataSheetTwo.setSheetTopColHeaderAttribute(new String[] {"name","adress","concat"});
        excelDataSheetTwo.setSheetDataList(list2);
        excelDataVoList.add(excelDataSheetTwo);

        // 封装其他信息
        ExcelDataVo excelDataSheetThree = new ExcelDataVo();
        excelDataSheetThree.setSheetName("其他信息");
        excelDataSheetThree.setSheetTopColHeaderTitle("其他信息");
        excelDataSheetThree.setSheetTopColHeaderName(new String[] {"描述","备注"});
        excelDataSheetThree.setSheetTopColHeaderAttribute(new String[] {"protity","remark"});
        excelDataSheetThree.setSheetDataList(list3);
        excelDataVoList.add(excelDataSheetThree);

        // 导出数据
        try {
            new PackExcelSheetsDataUtil().packExcelSheetsData(response, "学生基础信息", excelDataVoList);
        } catch (Exception e) {
            throw new baseException("导出异常!");
        }
    }