POI 导出excel通用工具类

上代码(工具类),文章末尾poi依赖版本:

package com.shanghai.util;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author yxj
 * @created 2019-02-10 12:13
 * @description
 */
public class PoiHelper<T> {

    
    /**
     * excel 文件对象
     */
    private HSSFWorkbook workbook;

    private HSSFSheet sheet;


    private DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");


    public ExportHelper(){
    }

    public HSSFWorkbook getWorkbook() {
        return workbook;
    }

    /**
     *
     * @param workbook
     * @return
     */
    public static CellStyle buildDefaultCellStyle(Workbook workbook) {
        CellStyle newCellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)14);
        font.setBold(true);
        newCellStyle.setFont(font);
        newCellStyle.setWrapText(true);
        newCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        newCellStyle.setAlignment(HorizontalAlignment.CENTER);
        newCellStyle.setLocked(true);
        newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        newCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        newCellStyle.setBorderBottom(BorderStyle.THIN);
        newCellStyle.setBorderLeft(BorderStyle.THIN);
        return newCellStyle;
    }

    /**
     * 初始化工具对象
     */
    public void init(){
        if(this.workbook == null){
            this.workbook = new HSSFWorkbook();
            this.sheet = workbook.createSheet();
        }else {
            if(this.sheet == null){
                sheet = workbook.createSheet();
            }
        }
        buildDefaultCellStyle(workbook);
    }

    /**设置首行
     * @param params
     */
    public void setFirstRow(String...params){
        init();
        HSSFRow firstRow = sheet.createRow(0);
        for(int i = 0; i < params.length; i++){
            firstRow.createCell(i).setCellValue(params[i]);
        }
    }

    /**设置首行
     * @param newSheet
     * @param params
     */
    public void setFirstRow(boolean newSheet,String...params){
        sheet = workbook.createSheet();
        HSSFRow firstRow = sheet.createRow(0);
        for(int i = 0; i < params.length; i++){
            firstRow.createCell(i).setCellValue(params[i]);
        }
    }
    /**注解指定excel下标及首行标题名称
     * @param list
     * @return
     */
    public HSSFWorkbook setDataToWorkBook(boolean newSheet, List<T> list){
        List<HeaderNode> nodeList = new ArrayList<>(32);
        ArrayList<String> params = new ArrayList<>(32);
        ArrayList<String> filedList = new ArrayList<>(32);
        Class clazz = list.get(0).getClass();
        Field[] fields = clazz.getDeclaredFields();
        int size = reflectAndSort(nodeList, params, filedList, fields);
        //得到首行数组
        String[] strings = new String[size];
        if(newSheet){
            setFirstRow(true,params.toArray(strings));
        }
        //字段名称首字母大写
        String[] vars = new String[size];
        String[] fileds = filedList.toArray(vars);

        return getExcelFile(list,fileds);
    }

    /**注解指定excel下标及首行标题名称
     * @param list
     * @return
     */
    public HSSFWorkbook setDataToWorkBook(List<T> list){
        List<HeaderNode> nodeList = new ArrayList<>(32);
        ArrayList<String> params = new ArrayList<>(32);
        ArrayList<String> filedList = new ArrayList<>(32);
        Class clazz = list.get(0).getClass();
        Field[] fields = clazz.getDeclaredFields();
        int size = reflectAndSort(nodeList, params, filedList, fields);
        //得到首行数组
        String[] strings = new String[size];
        setFirstRow(params.toArray(strings));
        //字段名称首字母大写
        String[] vars = new String[size];
        String[] fileds = filedList.toArray(vars);

        return getExcelFile(list,fileds);
    }

    private int reflectAndSort(List<HeaderNode> nodeList, ArrayList<String> params, ArrayList<String> filedList, Field[] fields) {
        for(Field field : fields){
            CellHeader cellHeader;
            try {
                cellHeader = field.getAnnotation(CellHeader.class);
                int index = cellHeader.index();
                String value = cellHeader.value();
                nodeList.add(new HeaderNode(index,value,field.getName()));
            }catch (NullPointerException e){
                continue;
            }
        }
        //排序 反的
        nodeList.stream().sorted(Comparator.comparing(HeaderNode::getIndex));
        int size = nodeList.size();
        for(int i = 0; i < size; i++){
            HeaderNode node = nodeList.get(i);
            params.add(node.getValue());
            String name = node.getFiledName();
            char[] chars = name.toCharArray();
            chars[0] -= 32;
            filedList.add(String.valueOf(chars));
        }
        return size;
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    class HeaderNode{
        private Integer index;
        private String value;
        private String filedName;

    }
    /**
     * @param list 结果集
     * @param params 结果集字段的名称(首字母大写)
     * @return
     */
    public HSSFWorkbook getExcelFile(List<T> list, String...params){
        //init();
        for(int i = 0; i < list.size(); i++){
            T t = list.get(i);
            HSSFRow row = sheet.createRow(i+1);
            Class clazz = t.getClass();
            Method[] methods = clazz.getDeclaredMethods();
            int size = params.length;
            int count = 0;
            //根据行赋值
            for(int j = 0; j <params.length; j++){
                String var = params[j];
                try {
                    //传入参数首字母大写
                    Method method = clazz.getMethod("get" + var);
                    Object res = method.invoke(t,null);
                    parseObjType(row, j, res);
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }

    /**对于反射调用方法的返回值类型解析并写入excel单元格
     * @param row
     * @param j
     * @param res
     */
    private void parseObjType(HSSFRow row, int j, Object res) {
        if(res instanceof Integer){
            row.createCell(j).setCellValue((Integer)res);
        }else if(res instanceof Date){
            row.createCell(j).setCellValue(df.format((Date) res));
        }else if(res instanceof Long){
            row.createCell(j).setCellValue((Long)res);
        }else if(res instanceof Double){
            row.createCell(j).setCellValue((Double) res);
        }else if(res instanceof Boolean){
            row.createCell(j).setCellValue((Boolean) res);
        }else {
            row.createCell(j).setCellValue(String.valueOf(res));
        }
    }

}

自定义注解: 用于导出的实体类上面index表示excel列的下标0开始,value表示首行表头

@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface CellHeader {

    String value() default"";

    int index();
}

 

调用示例:

package com.shanghai.controller;

import com.shanghai.mapper.Yh2019011016Mapper;
import com.shanghai.pojo.Yh2019011016;
import com.shanghai.util.PoiHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**导出excel报表
 * @author yxj
 * @created 2019-02-02 14:01
 * @description
 */
@RestController
@RequestMapping("/export")
public class ExportExcelController {


    @Autowired
    private Yh2019011016Mapper yh2019011016Mapper;


    @GetMapping("/test")
    public void downloadExcel(HttpServletResponse response) {
        List<Yh2019011016> list = yh2019011016Mapper.getFiveHundRedData();

       ExportHelper exportHelper = new ExportHelper();
        exportHelper.setDataToWorkBook(list);
        exportHelper.setDataToWorkBook(true,callInDtoList);
        exportHelper.setDataToWorkBook(true,callOutDtoList);
        String fileName = "云呼数据.xls";
        setHttpServletResponseForExport(response,exportHelper,fileName);
    }
}

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

 

文件名可以根据请求参数及导出的数据分类拼接而成,