poi 导出 excel

最近的项目上需要用到导出的功能，我搜集了一资料，发现poi技术是用的最多的一种，有些问题也列的很清楚，但是用到导出的地方太多了，我这里就总结了一下，写了一个工具类，统一来处理导出的问题。

     * excel导出
     *
     */
    public void export() {
        try {
           
            int pageNumber = getParaToInt("pageNumber", 1);
            //条数是可定义的
            int pageSize = 100000;
            //根据当前用户的角色去查询显示出来的数据
            User user = AuthUtils.getLoginUser();
            UserRole role = userRoleService.findByUserId(user.getId());
            String userIds = "0";
            String types;
            if (role.getRoleId().intValue() == Integer.parseInt(AdminType.ONELEVEL) || role.getRoleId().intValue() == Integer.parseInt(AdminType.TWOLEVEL)
                    || role.getRoleId().intValue() == Integer.parseInt(AdminType.THREELEVEL)) {
                if(role.getRoleId().intValue() == Integer.parseInt(AdminType.ONELEVEL)){
                    types = UserType.TWOLEVEL+","+ UserType.THREELEVEL;
                }else if (role.getRoleId().intValue() == Integer.parseInt(AdminType.TWOLEVEL )){
                    types = UserType.THREELEVEL;
                }else {
                    types = "";
                }
                List<MemberInviteSet> memberInviteSets = memberInviteSetService.findListByColumns("invite_id", user.getMemberId());
                if (memberInviteSets != null && memberInviteSets.size() > 0) {
                    userIds = memberInviteSets.stream().map(param -> param.getUserId().toString()).collect(Collectors.joining(","));
                }
            } else {
                userIds = "";
                types = UserType.ONELEVEL +","+ UserType.TWOLEVEL+","+ UserType.THREELEVEL;
            }
            Member member = new Member();
            member.setUserName(getPara("user_name"));
            member.setRealName(getPara("real_name"));
            member.setMobilePhone(getPara("mobile_phone"));
            Page<Member> dataPage = memberService.findAgentPage(member, pageNumber, pageSize, userIds,types);
            String title = "代理商Excel表_" + DateUtil.dateStr3(DateUtil.getNowDate())+".xlsx";
           String[] hearders = new String[]{"ID", "用户名", "姓名", "手机号", "类型", "所属上级代理商", "手续费分佣比例", "利润分佣比例"};
          final String[] fields = new String[]{"id", "user_name", "real_name", "mobile_phone", "user_type", "parent_name", "poundage_scale", "profit_scale"};
            List<Map<String,Object>> exportDatas =  dataPage.getList().stream().map(m->{
                Map<String,Object> map = new HashMap<>();
                for(String k : fields){
                    if(k.equals("user_type")){
                        if(m.get(k) != null){
                            map.put(k,UserType.me().desc(m.get(k).toString()));
                        }else{
                            map.put(k,"");
                        }
                    }else {
                        map.put(k,m.get(k) == null ? "" : m.get(k).toString());
                    }
                }
                return map;
            }).collect(Collectors.toList());
            File file = new File(title);
            file = ExportUtils.saveFile(hearders,fields,exportDatas, file);
            renderFile(file);
        } catch (Exception e) {
            e.printStackTrace();
            renderJson(RestResult.buildError("导出失败"));
        }
    }

上面的用到saveFile()方法，就是一个核心，我们来看一下源码

package com.mi.hx.finance.admin.base.util;

import com.jfinal.kit.PathKit;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 导出工具类
 * @author
 */
public abstract  class ExportUtils {

    public static File saveFile(String[] hearders, String[] fields, List<Map<String,Object>> exportDatas, File file){
        // 创建工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // sheet:一张表的简称
        // row:表里的行
        // 创建工作薄中的工作表
        Sheet xssfSheet = xssfWorkbook.createSheet();
        // 创建行
        Row row = xssfSheet.createRow(0);
        // 创建单元格，设置表头 创建列
        Cell cell = null;
        // 初始化索引
        int rowIndex = 0;
        int cellIndex = 0;

        // 创建标题行
        row = xssfSheet.createRow(rowIndex);
        rowIndex++;
        // 遍历标题
        for (String h : hearders) {
            //创建列
            cell = row.createCell(cellIndex);
            //索引递增
            cellIndex++;
            //逐列插入标题
            cell.setCellValue(h);
        }

        if (exportDatas != null&& exportDatas.size()>0) {
            // 获取所有的记录 有多少条记录就创建多少行
            for (int i = 0; i < exportDatas.size(); i++) {
                row = xssfSheet.createRow(rowIndex);
                // 得到所有的行 一个record就代表 一行数据
               Map<String,Object> record = exportDatas.get(i);
                //下一行索引
                rowIndex++;
                //刷新新行索引
                cellIndex = 0;
                // 在有所有的记录基础之上，便利传入进来的表头,再创建N行
                for (String h : fields) {
                    cell = row.createCell(cellIndex);
                    cellIndex++;
                    //按照每条记录匹配数据
                    cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
                }
            }
        }
        try {
            FileOutputStream fileOutputStreane = new FileOutputStream(file);
            xssfWorkbook.write(fileOutputStreane);
            fileOutputStreane.flush();
            fileOutputStreane.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }
}

有什么不对的地方还请大家多多指教。