java 封装poi导出excel的工具类,根据参数生成excel

用到过好几次的poi，之前用的时候时间赶都是直接写一个特定的方法导出一个特定的excel,一直想总结一个poi导出excel的工具类，主要的问题是怎么把excel表格的表头名称以及列表数据对应出来

pom文件(目前最新的是4.1.0)

         <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

核心的ExcelUtils类

public class ExcelUtils {

    private static final int PAGE_NUM = 65500;
    
     public static ResponseEntity<byte[]> exportExcel(String fileName, List<Map<String,Object>> list, String[] labels){
        HttpHeaders headers = null;
        ByteArrayOutputStream baos=null;
        try{
            HSSFWorkbook wb = getHSSFWorkbook(list,labels);
            headers = new HttpHeaders();
            //服务器控制浏览器发下载方式打开文件,例：Content-Disposition: attachment; filename=aaa.jpg
            headers.setContentDispositionFormData("attachment",new String(fileName.getBytes("UTF-8"), "iso-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

            baos = new ByteArrayOutputStream();
            wb.write(baos);
        }catch (Exception e){
            throw new RuntimeException("导出excel异常",e);
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    /**
     * 获取工作簿,有序的
     * @param list
     * @param labels
     * @return
     * @throws Exception
     */
    public static HSSFWorkbook getHSSFWorkbook(List<Map<String,Object>> list, String[] labels){

        HSSFWorkbook wb = new HSSFWorkbook();
        //对集合进行非空判断
        if(isEmptyOrNull(list) || isEmptyOrNull(labels)){
            wb.createSheet();
            return wb;
        }

        int pageNum = PAGE_NUM;//每页数量
        int size = list.size();//数据数量
        int pages = (size % pageNum > 0) ? (size / pageNum + 1) : (size / pageNum);//导出页数

        HSSFCellStyle cs = null;//单元格样式
        HSSFSheet sheet = null;//页面
        HSSFFont font = wb.createFont();//字体

        font.setFontHeightInPoints((short) 11);//设置字号
        cs = wb.createCellStyle();

        cs.setFont(font);
//        cs.setFillPattern(HSSFCellStyle.FINE_DOTS);

//        Set<String> keySet = columnMap.keySet();
        HSSFRow row = null;//单元格行
        Object columnvalue = null;
        String columnTitle = null;
        Map<String,Object> dataMap = null;

        for (int i = 0; i < pages; i++) {
            sheet = wb.createSheet();
            wb.setSheetName(i, (String.valueOf((i + 1))));
            row = sheet.createRow(0);
            for(String label : labels){
                String[] strs = label.split(":");
                columnTitle = strs[1];
                createStringCell(row, row.getLastCellNum() == -1 ? 0 :row.getLastCellNum(), columnTitle, cs);
            }
            for(int j = 0; j < size; j++){
                dataMap = list.get(j);
                row = sheet.createRow(j + 1 - (pageNum * i));
                for(String label : labels){
                    String[] strs = label.split(":");
                    columnvalue = dataMap.get(strs[0]);
                    createStringCell(row, row.getLastCellNum() == -1 ? 0 :row.getLastCellNum(), columnvalue, cs);
                }
            }
        }
        return wb;

    }

    /**
     * 创建单元格
     * @param row
     * @param index
     * @param value
     * @param cs
     */
    private static void createStringCell(HSSFRow row, short index, Object value, HSSFCellStyle cs) {
        HSSFCell cell = row.createCell(index);
        cell.setCellType(CellType.STRING);//居中样式
        cs.setAlignment(HorizontalAlignment.CENTER);
        if(!isEmptyOrNull(value) && value instanceof Date){
            value = doDate2String((Date)value);
        }
        cell.setCellValue(null2Empty(value));
    }


    /**
     * 将日期转化为String
     * @param confirmDate
     * @param patten
     * @return
     */
    public static String doDate2String(Date confirmDate, String... patten) {
        if(confirmDate == null){
            return "";
        }
        SimpleDateFormat sdf = null;
        if(patten == null || patten.length == 0){
            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        }else{
            sdf = new SimpleDateFormat(patten[0]);
        }
        return sdf.format(new Date(confirmDate.getTime()));
    }

    /**
     * 将null转化为Empty
     * @param areaId
     * @return
     */
    public static String null2Empty(Object areaId) {
        return areaId == null ? "" : areaId.toString();
    }

    /**
     * 非空判断
     * @param obj
     * @return
     */
    public static boolean isEmptyOrNull(Object obj){
        if(obj == null){
            return true;
        }
        if(obj instanceof Map){
            return ((Map) obj).isEmpty();
        }else if(obj instanceof List){
            return ((List) obj).isEmpty();
        }
        return obj.toString().isEmpty();
    }

}

controller方法

@RestController
@RequestMapping("/demo/excel/")
public class ExcelController {

    private static String[] LABELS = {
          "id:主键",
          "name:姓名",
          "phone:联系电话"
    }

    @RequestMapping("exportFile")
    public ResponseEntity exportFile(){
    	List<Map<String,Object>> datalist = new ArrayList<>();
    	//测试数据
    	for(int i=0;i<100;i++){
			Map<String,Object> data = new HashMap<>();
			data.put("id",i+1);
			data.put("name","测试"+i);
			data.put("phone",15010000+i);
			datalist.add(data);
		}
		
		//可动态添加label
		/*List<String> headLabels = new ArrayList<String>(
                Arrays.asList("id:主键",
                        "name:姓名",
                        "phone:联系电话"
                )
        );
        String[] labels = headLabels .toArray(new String[labels.size()]);*/
		return ExcelUtils.exportExcel("xxx测试.xls",datalist,LABELS );
	}

}

下载的excel

参考：
参考了网友King哥2的文章Java poi导出excel封装的工具类