excel导出工具类 简单便捷灵活

excel导出工具类

utlil 利用反射给excel填值

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.ArrayUtils;
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;

@Slf4j
public class ExcelExportUtil {

    /**
     *
     * @param isNeedSerialNum  是否需要序号（true 导出第一列为序号列）
     * @param fileName  导出文件名称
     * @param sheetName sheet名
     * @param headers    表第一行 列名数组
     * @param list    表需要填入数据对象
     * @param fieldNames 属性名数组 根据属性名反射
     * @param formatFieldNames 需要格式化的字段名
     * @param response
     * @param excelFormat 格式化方法
     * @return
     */
    public static boolean  downLoadExcel(boolean isNeedSerialNum, String fileName, String sheetName, int columnCount, String headers[], List list, String fieldNames[], String formatFieldNames[], HttpServletResponse response, ExcelFormat excelFormat) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //第一列为数字序号
        if(isNeedSerialNum){
            if(columnCount!=headers.length || columnCount-1 != fieldNames.length){
                log.info("第一列为序号时，columnCount参数和headers数组长度一致，且比tableNames数组长度大1，请核对代码");
                return false;
            }
        }
        //第一列为正常数据，不为数字
        if(isNeedSerialNum==false){
            if(columnCount!=headers.length || columnCount != fieldNames.length){
                log.info("columnCount参数和headers数组tableNames数组长度应一致，请核对代码");
                return false;
            }
        }
        int rowNum = 1;
        HSSFSheet sheet = null;
        if(sheetName==null){
            sheet = workbook.createSheet();
        }else{
            sheet = workbook.createSheet(sheetName);
        }
        for(int i=0;i<columnCount;i++){
            sheet.setColumnWidth(i, 3766);
        }
        HSSFRow row = sheet.createRow(0);
        //表格每列所有字段名称赋值
        for (int i = 0; i < headers.length; i++) {
            row.createCell(i).setCellValue(new HSSFRichTextString(headers[i]));
        }
        for (Object obj : list) {
            HSSFRow row1 = sheet.createRow(rowNum);
            if(isNeedSerialNum){
                for(int i=0;i<headers.length;i++){
                    if(i==0){
                        row1.createCell(i).setCellValue(rowNum);
                        continue;
                    }
                    Class c = obj.getClass();
                    boolean hasFlag=hasField(c,fieldNames[i-1]);
                    //查看父类中是否包含
                    while (!hasFlag){
                        c=c.getSuperclass();
                        if (c== Object.class){
                            break;
                        }
                        hasFlag=hasField(c,fieldNames[i-1]);
                    }
                    Field f = c.getDeclaredField(fieldNames[i-1]);
                    f.setAccessible(true);
                    if (ArrayUtils.contains(formatFieldNames, fieldNames[i-1])){
                        //需要格式化
                        String value=excelFormat.excelFormat(f.get(obj),fieldNames[i-1]);
                        row1.createCell(i).setCellValue(value);
                    }else {
                        row1.createCell(i).setCellValue(f.get(obj)==null?"": String.valueOf(f.get(obj)));
                    }
                }

            }else{
                for(int i=0;i<headers.length;i++){
                    Class c = obj.getClass();
                    boolean hasFlag=hasField(c,fieldNames[i]);
                    //查看父类中是否包含
                    while (!hasFlag){
                        c=c.getSuperclass();
                        if (c== Object.class){
                            break;
                        }
                        hasFlag=hasField(c,fieldNames[i]);
                    }

                    Field f = c.getDeclaredField(fieldNames[i]);
                    f.setAccessible(true);
                    if (ArrayUtils.contains(formatFieldNames, fieldNames[i])){
                        //需要格式化
                        String value=excelFormat.excelFormat(f.get(obj),fieldNames[i]);
                        row1.createCell(i).setCellValue(value);
                    }else {
                        row1.createCell(i).setCellValue(f.get(obj)==null?"": String.valueOf(f.get(obj)));
                    }
                }
            }
            rowNum++;
        }
        fileName = URLEncoder.encode(fileName,"utf-8");
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls"+";"+"filename*=utf-8''"+fileName+ ".xls");
        response.setCharacterEncoding("UTF-8");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        return true;
    }

    /**
     * 判断class中是否有该属性
     * @param c
     * @param fieldName
     * @return
     */
    public static boolean hasField(Class c, String fieldName){
        Field[] fields = c.getDeclaredFields();
        for (Field f : fields) {
            if (fieldName.equals(f.getName())) {
                return true;
            }
        }
        return false;
    }

}

格式化字段用到的接口

public interface ExcelFormat {
    String excelFormat(Object data, String filedName);
}

下面进行测试
测试实体类

import lombok.Data;
import java.util.Date;

@Data
public class Student {
    String name;

    Integer age;

    Date birthDate;
}

    @GetMapping("/t11")
    public  void  test(HttpServletResponse response) throws Exception {
        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
        List<Student> students=new ArrayList<>();
        Student student1=new Student();
        student1.setName("小明");
        student1.setAge(22);
        student1.setBirthDate(simpleDateFormat.parse("2000-01-01"));
        students.add(student1);

        Student student2=new Student();
        student2.setName("小华");
        student2.setAge(20);
        student2.setBirthDate(simpleDateFormat.parse("2002-02-02"));
        students.add(student2);

        //列名
        String headers[] = {"序号","学生姓名", "年龄", "出生日期"};
        //实体类中的属性名 除了序号外 其他属性名对应列名
        String tableNames[]={"name","age","birthDate"};
        //需要特殊处理的字段  例如出生日期 ，导出后需要展示 xxxx年xx月xx日
        String formatFieldNames[]={"birthDate"};
        //特殊处理的具体方法
        ExcelFormat excelFormat=new ExcelFormat() {
            @Override
            public String excelFormat(Object data, String filedName) {
                try {
                    if (filedName.equals("birthDate")||filedName.equals("birthDate")){
                        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy年MM月dd日");
                        return simpleDateFormat.format(data);
                    }
                    //如果还有其他字段可接着写if
                    return "";
                } catch (Exception e) {
                    e.printStackTrace();
                    return "";
                }
            }
        };
        ExcelExportUtil.downLoadExcel(true, "学生信息","学生信息", 4, headers,students,tableNames,formatFieldNames,response, excelFormat );
    }

调用后导出excel如下