poi 灵活导出excel（有这一篇即可~~~~）

大家在开发项目的时候，但凡涉及数据多而杂的时候或多或少需要导出excel，用什么的工具类这时候就很烦恼了，接下来的工具类傻瓜式一键生成大家需要的excel。

1.依赖

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。

HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”
 

这里采用poi依赖

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

2.工具类

构建头部

public class ReflectUtil {

   public static List<String>[] getHeadAndFields(Class clazz){

       List[] list = new List[2];
       List<String> headerList = Lists.newArrayList();
       List<String> fieldList = Lists.newArrayList();

       List<Field> fields = Arrays.stream(clazz.getDeclaredFields()).
               filter(a -> a.getAnnotation(com.xwcx.culture_city.util.Export.class) != null)
               .sorted((field1,field2) -> {
                   com.xwcx.culture_city.util.Export export1 = field1.getAnnotation(com.xwcx.culture_city.util.Export.class);
                   com.xwcx.culture_city.util.Export export2 = field1.getAnnotation(com.xwcx.culture_city.util.Export.class);
                   return export1.sort() - export2.sort();
               })
               .collect(Collectors.toList());
       if(fields.size() == 0){
           return list;
       }

       for(int i = 0 ; i < fields.size() ;i ++){
           Field field = fields.get(i);
           com.xwcx.culture_city.util.Export export = field.getAnnotation(com.xwcx.culture_city.util.Export.class);
           if(export != null){
               headerList.add(i,export.name());
               fieldList.add(i,field.getName());
           }
       }
       list[0] = headerList;
       list[1] = fieldList;
       return list;
   }

    public static void main(String[] args) {

    }
}

循环表格，渲染数据

package com.xwcx.culture_city.util;

import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description
 * @Author cheng
 * @Date2020-01-16 11:08
 * @Version V1.0
 **/
public class ExcelUtil {

    //单个sheet最多写入行数
    private static final int SHEET_MAX_COUNT = 1000000;

    /**
     * 创建Workbook
     * @return
     */
    public static Workbook createWorkbook(){
        Workbook wb = new SXSSFWorkbook(100);
        CellStyle hcs = wb.createCellStyle();
        hcs.setBorderBottom(BorderStyle.THIN);
        hcs.setBorderLeft(BorderStyle.THIN);
        hcs.setBorderRight(BorderStyle.THIN);
        hcs.setBorderTop(BorderStyle.THIN);
        hcs.setAlignment(HorizontalAlignment.CENTER);
        Font hfont = wb.createFont();
        hfont.setFontName("宋体");
        // 设置字体大小
        hfont.setFontHeightInPoints((short) 16);
        // 加粗
        hfont.setBold(true);
        hcs.setFont(hfont);

        CellStyle tcs = wb.createCellStyle();
        tcs.setBorderBottom(BorderStyle.THIN);
        tcs.setBorderLeft(BorderStyle.THIN);
        tcs.setBorderRight(BorderStyle.THIN);
        tcs.setBorderTop(BorderStyle.THIN);
        Font tfont = wb.createFont();
        tfont.setFontName("宋体");
        // 设置字体大小
        tfont.setFontHeightInPoints((short) 12);
        // 加粗
        tfont.setBold(true);
        tcs.setFont(tfont);

        CellStyle cs = wb.createCellStyle();
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        Font font = wb.createFont();
        font.setFontName("宋体");
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
//        font.setColor(HSSFColor.RED.index);
        cs.setFont(font);

        CellStyle cs1 = wb.createCellStyle();
        cs1.setBorderBottom(BorderStyle.THIN);
        cs1.setBorderLeft(BorderStyle.THIN);
        cs1.setBorderRight(BorderStyle.THIN);
        cs1.setBorderTop(BorderStyle.THIN);
        Font font1 = wb.createFont();
        font1.setFontName("宋体");
        // 设置字体大小
        font1.setFontHeightInPoints((short) 12);
        font1.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        cs1.setFont(font1);

        return wb;
    }

    public static <T> void exportExcel(String title, List<String> headers, List<String> fields, int startRow, Workbook wb, List<T> data) throws IOException {

        Sheet sheet = null;
        startRow = startRow > 0 ? startRow + 2 : startRow;
        // 行号、页码、列数
        int index = startRow, pageRowNo = startRow, columnCount = headers.size();
        for (T obj : data) {
            int sheetIndex = index/SHEET_MAX_COUNT;
            if (index % SHEET_MAX_COUNT == 0) {
                sheet = wb.createSheet(title + "_" + (sheetIndex + 1));
                sheet = wb.getSheetAt(sheetIndex);
                // 设置表标题是否有表格边框
                sheet.setDisplayGridlines(false);
//                pageRowNo = 2;
                pageRowNo = 1;
                createHeader(sheet, title, headers);
            }else{
                sheet = wb.getSheetAt(sheetIndex);
            }
            index++;
            Map<String, Object> map = obj instanceof Map ? (Map<String, Object>) obj : beanToMap(obj);
            // 新建行对象
            Row nRow = sheet.createRow(pageRowNo++);
            int k = 0;
            for (int j = 0; j < columnCount; j++) {
                boolean flag = true;
                Cell cell = nRow.createCell(j);
                sheet.setColumnWidth(j, 20 * 256);
                if ("问卷列表".equals(title)) {
                    String[] answer = {"A", "O", "A", "D", "A", "A", "A", "A", "A", "C", "A", "A", "A", "A", "A", "A", "C", "A"};
                   if(j >= 11 && j < 29 ){
                        if((!answer[k].equals(map.get(fields.get(j)))) && k!=1){
                            flag = false;
                        }
                        k++;
                    }
                }
                setCellValue(sheet, cell, map.get(fields.get(j)),flag);
            }
        }

    }


    /**
     * write Workbook
     * @param wb
     * @param filePath
     * @throws IOException
     */
    public static void writeWorkbook(Workbook wb, String filePath, String fileName)throws IOException{
        FileOutputStream fos = new FileOutputStream(filePath + fileName);
        wb.write(fos);
        fos.flush();
        fos.close();
        wb.close();
    }

    /**
     * responseWorkbook
     * @param title  标题
     * @param wb     流
     * @param request http请求
     * @param response http返回
     * @throws IOException
     */
    public static void responseWorkbook(String title, Workbook wb, HttpServletRequest request, HttpServletResponse response)throws IOException{
        String sFileName = title + ".xlsx";
        // 火狐浏览器导出excel乱码
        String agent = request.getHeader("User-Agent");
        // 判断是否火狐浏览器
        boolean isFirefox = agent != null && agent.contains("Firefox");
        if (isFirefox) {
            sFileName = new String(sFileName.getBytes("UTF-8"), "ISO-8859-1");
        } else {
            sFileName = URLEncoder.encode(sFileName, "UTF8");
        }
//        response.setHeader("Content-Disposition", "attachment; filename=".concat(sFileName));
//        response.setHeader("Connection", "close");
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setContentType("application/vnd.ms-excel;chartset=utf-8");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition","attachment; filename=" + sFileName);

        wb.write(response.getOutputStream());
    }

    /**
     * 设置单元格的值
     * @param cell  单元格
     * @param cellVal  单元格的值
     */
    public static void setCellValue(Sheet sheet, Cell cell, Object cellVal,boolean flag){
        if(cellVal == null){
            cell.setCellValue("");
        }else if (String.class.equals(cellVal.getClass())){
            cell.setCellValue((String)cellVal);
        }else if(Integer.class.equals(cellVal.getClass()) || int.class.equals(cellVal.getClass())){
            cell.setCellValue(Integer.valueOf(cellVal.toString()));
        }else if(Long.class.equals(cellVal.getClass()) || long.class.equals(cellVal.getClass())){
            cell.setCellValue(Integer.valueOf(cellVal.toString()));
        }else if(Double.class.equals(cellVal.getClass()) || double.class.equals(cellVal.getClass())){
            cell.setCellValue(Double.valueOf(cellVal.toString()));
        }else if(Float.class.equals(cellVal.getClass()) || float.class.equals(cellVal.getClass())){
            cell.setCellValue(Float.valueOf(cellVal.toString()));
        }else if(BigDecimal.class.equals(cellVal.getClass())){
            cell.setCellValue(new BigDecimal(cellVal.toString()).doubleValue());
        }else if(Date.class.equals(cellVal.getClass())){
            cell.setCellValue(DateUtils.format((Date)cellVal,DateUtils.DATE_TIME_PATTERN));
        }else{
            cell.setCellValue(cellVal.toString());
        }
        if(flag == true){
           cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(3));
        }else{
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(4));
        }
        System.out.println("");
    }

    /**
     * JavaBean转Map
     *
     * @param obj  实体转换对象
     * @return
     */
    public static Map<String, Object> beanToMap(Object obj) {
        Map<String, Object> params = new HashMap<>(0);
        try {
            PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
            PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
            for (int i = 0; i < descriptors.length; i++) {
                String name = descriptors[i].getName();
                if (!StringUtils.equals(name, "class")) {
                    params.put(name, propertyUtilsBean.getNestedProperty(obj, name));
                }
            }
        } catch (Exception e) {

        }
        return params;
    }

    /**
     * 创建表头
     * @param sheet  sheet页
     * @param headers  头部信息
     */
    private static void createHeader(Sheet sheet, String title, List<String> headers){

        //设置标题
//        Row tRow = sheet.createRow(0);
//        Cell hc = tRow.createCell(0);
//        hc.setCellValue(new XSSFRichTextString(title));
//        // 合并标题行：起始行号，终止行号， 起始列号，终止列号
//        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.size() - 1));
//        hc.setCellStyle(sheet.getWorkbook().getCellStyleAt(1));

        //设置表头
        Row nRow = sheet.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = nRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(sheet.getWorkbook().getCellStyleAt(2));
        }
    }

}

由于方法工具类调用的是 SXSSFWorkbook！所以导出的文件类型是.xlsx结尾，顾名思义是excel2003版本以后的，如若需要.xls结尾需要自行修改。

3.调用

String title = "用户管理";
//UserListVo 实体类对象需要额外添加一个注解
            List<String>[] headAndFields =  ReflectUtil.getHeadAndFields(UserListVo.class);
            List<String> headers = headAndFields[0];
            List<String> fields = headAndFields[1];
            Workbook workbook = ExcelUtil.createWorkbook();

//该集合就是自己搜索出来的vo或者do集合
List<UserListVo> listVoList = null;

int startRow = 0;
            ExcelUtil.exportExcel(title, headers, fields, startRow, workbook, listVoList);
            ExcelUtil.responseWorkbook(title, workbook, request, response);
            workbook.close();

仅仅是实体类对象是不够的，我们需要针对需要导出的excel表头去确定导出字段。

@Export(name = "用户名",sort =1) 

@Export(name = "用户名",sort =1)
    private String name;

这个注解可以根据我们业务需要的表头确定需要的字段，表头顺序根据注解对应字段的顺序。

以上！就是针对导出excel需要的工具类，调用方法。很简单，如有问题，欢迎大家评论区讨论！