java poi导出通用工具

1、导出工具接口定义

import java.io.OutputStream;
import java.util.List;

/**
 * Created by huang on 17/2/20.
 */
public interface ExportExcel<T> {

    void export(String sheetName, List<T> dataList, Class beanClazz, OutputStream os);
}

 2、导出工具接口实现

import com.huang.excel.entiry.BaseEnum;
import com.huang.excel.parser.EnumParser;
import com.huang.excel.parser.FieldParser;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;

/**
 * Created by huang on 17/2/20.
 */
@Component
public class ExportExcelImpl<T> implements ExportExcel<T> {

    private static final int max_rows = 65535;

    private static Logger logger = LoggerFactory.getLogger(ExportExcelImpl.class);

    public void export(String sheetName, List<T> dataList, Class beanClazz, OutputStream os) {
        try {
            if (CollectionUtils.isEmpty(dataList)) {
                return;
            }
            if (dataList.size() > max_rows) {
                throw new RuntimeException("导出数据超出了最大限制行数");
            }
            long startTime = System.currentTimeMillis();
            //创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个sheet
            HSSFSheet sheet = workbook.createSheet(sheetName);
            //创建第一行
            HSSFRow firstRow = sheet.createRow(0);

            List<ExcelExportParseBean> parseBeanList = ExcelParser.parseExcelExportBean(beanClazz);

            if (parseBeanList.size() == 0) {
                throw new RuntimeException("无需要导出的列");
            }
            for (ExcelExportParseBean parseBean : parseBeanList) {
                firstRow.createCell(parseBean.getColumnSequence()).setCellValue(parseBean.getColumnName());
            }
            int rowNum = 1;
            int columnSequence = 0;
            for (T data : dataList) {
                HSSFRow row = sheet.createRow(rowNum);
                for (ExcelExportParseBean bean : parseBeanList) {
                    HSSFCell cell = row.createCell(bean.getColumnSequence());
                    String cellValue = null;
                    if (bean.getAutoIncrement()) {
                        cellValue = String.valueOf(columnSequence);
                        columnSequence++;
                        cell.setCellValue(cellValue);
                        continue;
                    }
                    Field field = data.getClass().getDeclaredField(bean.getFieldName());
                    field.setAccessible(true);
                    Object value = field.get(data);
                    if (value == null) {
                        continue;
                    }
                    Class handleClass = bean.getHandleClass();
                    FieldParser fieldParser = (FieldParser) handleClass.newInstance();
                    if (fieldParser instanceof EnumParser) {
                        Class enumClass = bean.getEnumClass();
                        Enum[] enumConstants = (Enum[]) enumClass.getEnumConstants();
                        for (Enum e : enumConstants) {
                            BaseEnum baseEnum = (BaseEnum) e;
                            String s = baseEnum.getEnumDataValue(value.toString());
                            if (s != null) {
                                cellValue = s;
                                break;
                            }
                        }
                    } else {
                        cellValue = fieldParser.doParse(value);
                    }
                    cell.setCellValue(cellValue);
                }
                rowNum++;
            }
            workbook.write(os);
            logger.info("数据导出总耗时{}毫秒", (System.currentTimeMillis() - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3、变量的转换bean

import com.google.common.collect.Lists;

import java.lang.reflect.Field;
import java.util.List;

/**
 * Created by huang on 17/2/20.
 */
public class ExcelParser {

    public static List<ExcelExportParseBean> parseExcelExportBean(Class clazz) {
        try {
            Object object = clazz.newInstance();
            List<ExcelExportParseBean> result = Lists.newArrayList();
            if (object.getClass().isAnnotationPresent(ExcelExportAnnotation.class)) {
                ExcelExportAnnotation annotation = object.getClass().getAnnotation(ExcelExportAnnotation.class);
                ExcelExportParseBean parseBean = new ExcelExportParseBean();
                parseBean.setColumnSequence(annotation.columnSequence());
                parseBean.setColumnName(annotation.columnName());
                parseBean.setAutoIncrement(annotation.autoIncrement());
                result.add(parseBean);
            }
            Field[] fields = object.getClass().getDeclaredFields();
            for (Field field : fields) {
                ExcelExportAnnotation annotation = field.getAnnotation(ExcelExportAnnotation.class);
                if (annotation == null) {
                    continue;
                }
                ExcelExportParseBean parseBean = new ExcelExportParseBean();
                parseBean.setAutoIncrement(annotation.autoIncrement());
                parseBean.setColumnName(annotation.columnName());
                parseBean.setColumnSequence(annotation.columnSequence());
                parseBean.setHandleClass(annotation.handleClass());
                parseBean.setFieldName(field.getName());
                parseBean.setEnumClass(annotation.enumClass());
                result.add(parseBean);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        throw new RuntimeException("parse excel exportBean exception!!");
    }
}

4、注解转换bean

import lombok.Data;

/**
 * Created by huang on 17/2/20.
 */
@Data
public class ExcelExportParseBean implements Comparable {

    private int columnSequence;//列的序列号

    private String columnName;

    private String fieldName;

    private String formatStr;

    private Boolean autoIncrement;

    private Class handleClass;

    private Class enumClass;

    public int compareTo(Object o) {
        if (o == null) {
            throw new RuntimeException("ExcelExportParseBean compare object is null exception！！");
        }
        ExcelExportParseBean parseBean = (ExcelExportParseBean) o;
        if (columnSequence < parseBean.columnSequence) {
            return -1;
        } else if (columnSequence > parseBean.columnSequence) {
            return 1;
        } else {
            return 0;
        }
    }
}

5、自定义注解，用于变量和类上

import com.huang.excel.entiry.BaseEnum;
import com.huang.excel.parser.DefaultFieldParser;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Created by huang on 17/2/20.
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.TYPE})
public @interface ExcelExportAnnotation {
    /**
     * 列序号，比如第1列是姓名，第2列是电话号码，第3列是地址
     */
    int columnSequence();

    /**
     * 列名
     */
    String columnName();

    /**
     * 枚举的名称，枚举需要单独处理
     */
    Class enumClass() default BaseEnum.class;

    /**
     * 处理的类
     */
    Class handleClass() default DefaultFieldParser.class;

    /**
     * 是否有自增序列
     */
    boolean autoIncrement() default false;
}

6、日期格式处理转换器

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by huang on 17/2/20.
 */
public class DateParser extends FieldParser {

    private final static String DATE_FORMAT_STR = "yyyy-MM-dd HH:mm:ss";

    @Override
    public String doParse(Object o) {
        if (o == null) {
            return "";
        }
        if (o instanceof Date) {
            Date date = (Date) o;
            SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_STR);
            return sdf.format(date);
        }
        return "";
    }
}

7、枚举类型转换器

/**
 * Created by huang on 17/2/20.
 */
public class EnumParser extends FieldParser {

    @Override
    public String doParse(Object o) {
        return "";
    }
}

8、unixtime时间转换器

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by huang on 17/2/20.
 */
public class UnixtimeParser extends FieldParser {

    private final static String DATE_FORMAT_STR = "yyyy-MM-dd HH:mm:ss";

    @Override
    public String doParse(Object o) {
        if (o == null) {
            return "";
        }
        if (o instanceof Integer) {
            Integer unixTime = (Integer) o;
            Date date = new Date(unixTime * 1000l);
            SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_STR);
            return sdf.format(date);
        }
        return "";
    }
}

9、基础数据类型转换器，可自行添加

import java.math.BigDecimal;

/**
 * Created by huang on 17/2/20.
 */
public class DefaultFieldParser extends FieldParser {
    @Override
    public String doParse(Object o) {
        String textValue = "";
        if (o == null) {
            return textValue;
        }
        if (o instanceof String) {
            textValue = (String) o;
        } else if (o instanceof Integer) {
            Integer value = (Integer) o;
            textValue = String.valueOf(value);
        } else if (o instanceof Float) {
            Float value = (Float) o;
            textValue = String.valueOf(value);
        } else if (o instanceof Double) {
            Double value = (Double) o;
            textValue = String.valueOf(value);
        } else if (o instanceof BigDecimal) {
            BigDecimal value = (BigDecimal) o;
            textValue = String.valueOf(value);
        } else {
            textValue = o.toString();
        }
        return textValue;
    }
}

10、抽象的变量转换器

/**
 * Created by huang on 17/2/20.
 */
public abstract class FieldParser {

    public abstract String doParse(Object o);
}

11、枚举类型转换接口，需要导出字段的枚举类型均实现此接口

/**
 * Created by huang on 17/2/20.
 */
public interface BaseEnum {

    String getEnumDataValue(String key);

}

12、导出demo

import lombok.Getter;
import lombok.Setter;

/**
 * Created by huang on 17/2/20.
 */
public enum SexEnum implements BaseEnum {
    MAN(1, "男"),
    WOMAN(2, "女");

    @Getter
    @Setter
    private Integer key;

    @Getter
    @Setter
    private String desc;

    SexEnum(Integer key, String desc) {
        this.key = key;
        this.desc = desc;
    }

    public String getEnumDataValue(String key) {
        for (SexEnum sexEnum : values()) {
            if (Integer.parseInt(key) == sexEnum.getKey()) {
                return sexEnum.getDesc();
            }
        }
        return "";
    }
}



import java.util.Date;

/**导出的bean对象，没有添加注释的列不会导出
 * Created by huang on 17/2/20.
 */
@Data
@ExcelExportAnnotation(columnSequence = 0, columnName = "序列号", autoIncrement = true)
public class UsersBean {

    @ExcelExportAnnotation(columnSequence = 1, columnName = "用户id")
    private Integer id;

    @ExcelExportAnnotation(columnSequence = 2, columnName = "用户名称")
    private String userName;

    @ExcelExportAnnotation(columnSequence = 3, columnName = "密码")
    private String passWord;

    @ExcelExportAnnotation(columnSequence = 4, columnName = "用户手机号")
    private String mobile;

    @ExcelExportAnnotation(columnSequence = 5, columnName = "性别", handleClass = EnumParser.class, enumClass = SexEnum.class)
    private String sex;

    @ExcelExportAnnotation(columnSequence = 6, columnName = "用户邮箱")
    private String email;

    @ExcelExportAnnotation(columnSequence = 7, columnName = "出生日期", handleClass = DateParser.class)
    private Date birth;

    private Integer age;

    @ExcelExportAnnotation(columnSequence = 8, columnName = "创建时间", handleClass = DateParser.class)
    private Date createTime;

    private Date modifyTime;

    private String isDel;

    private String description;
}


controller对象

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;

/**
 * Created by huang on 17/2/20.
 */
@Controller
@RequestMapping("excel")
public class ExcelController {

    @Resource
    private UsersMapper usersMapper;
    @Resource
    private ExportExcel exportExcel;

    @RequestMapping(value = "export", method = RequestMethod.GET, produces = "application/json")
    @ResponseBody
    public void excelExport(HttpServletResponse response) {
        try {
            OutputStream os = response.getOutputStream();
            //在controller中直接查数据库不规范,此处只是demo
            List<UsersDO> userList = usersMapper.selectAll();
            exportExcel.export("用户信息", userList, UsersBean.class, os);
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

 

转载于:https://my.oschina.net/460981219/blog/842414