java poi导出excel 工具,支持配置SAX导出模式以及导出excel 2003、excel 2007
首先是一个注解类
package com.wl.farmer.action.annotaion;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by Administrator on 2019/9/26.
*/
@Target({ElementType.FIELD,ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExportAnnotation {
int order() default 100;//排序(属性对应excel的列 的列数)
String method() default "";//属性对应的get方法
//导出title
String columnTitle() default "";//属性对应的excel 的title
//title数组长度
int length() default 20;//列的个数
}
导出的实体对象 javaBean(例子)
package com.wl.farmer.action.model;
import com.wl.farmer.action.annotaion.ExportAnnotation;
import com.wl.farmer.action.util.ExportExcelUtil;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.Date;
/**
* Created by wl
*/
@ExportAnnotation(length = 9)
public class User {
@ExportAnnotation(order = 1,method = "getId",columnTitle = "用户id")
private Integer id;
@ExportAnnotation(order = 2,method = "getUserName",columnTitle = "用户名称")
private String userName;
@ExportAnnotation(order = 3,method = "getMobile",columnTitle = "电话号码")
private String mobile;
@ExportAnnotation(order = 4,method = "getEmail",columnTitle = "电子邮箱")
private String email;
@ExportAnnotation(order = 5,method = "getAge",columnTitle = "年龄")
private Integer age;
@ExportAnnotation(order = 5,method = "getCreateTime",columnTitle = "创建时间")
private Date createTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
真正的工具类
package com.wl.farmer.action.util;
import com.wl.farmer.action.annotaion.ExportAnnotation;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* Created by wl
*/
public class ExportExcelUtil{
private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);
private final Workbook wbk;
private Sheet sheet;
private Row row;
private Cell cell;
private final CellStyle titleStyle;
private final CellStyle cellStyle;
private Object cellValue;
//标题[0][] 方法名[1][]
private final String[][] titlesAndMethods = new String[2][];
private final Class<?> exportClass;
private ExportExcelUtil(Builder builder){
this.exportClass = builder.targetClass;
if(builder.isSax){
this.wbk = new SXSSFWorkbook(builder.rowAccessWindowSize);
}else if(builder.is2003){
this.wbk = new HSSFWorkbook();
}else{
this.wbk = new XSSFWorkbook();
}
this.titleStyle = this.wbk.createCellStyle();
this.cellStyle = this.wbk.createCellStyle();
//样式
this.initStyle(this.titleStyle,false);
this.initStyle(this.cellStyle,true);
this.initTitleAndMethod(this.exportClass);
}
private int count = 1;
private static final int MAX_ROW_NUM_2003 = 2000 + 1;
private static final int MAX_ROW_NUM_2007 = 20000 + 1;
public void exportRowSet(Object rowData) {
if(this.sheet ==null){
throw new NullPointerException("sheet 为空 , 先initSheet");
}
if(this.wbk instanceof HSSFWorkbook){
if(this.count % MAX_ROW_NUM_2003 == 0){
this.initSheet(this.wbk.getSheetName(0) + (this.wbk.getSheetIndex(sheet) + 1));
}
}else {
if(this.count % MAX_ROW_NUM_2007 == 0){
this.initSheet(this.wbk.getSheetName(0) + (this.wbk.getSheetIndex(sheet) + 1));
}
}
if(this.count % 10000 == 0){
logger.info("=========="+Runtime.getRuntime().totalMemory()/1024/1024+"M");
//System.runFinalization();
}
this.row = this.sheet.createRow(count);
for(int i=0;i<titlesAndMethods[0].length && titlesAndMethods[1][i] != null;i++) {
try {
this.cellValue = this.exportClass.getMethod(this.titlesAndMethods[1][i]).invoke(rowData);
this.cell = this.row.createCell(i);
this.createCellValue(i);
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
//logger.error("导出统计数据 反射获取值 失败,column:{}",i,e);
}
}
this.count++;
}
public void writeWbk(OutputStream os) {
try {
this.wbk.write(os);
} catch (IOException e) {
//
}finally {
try {
this.wbk.close();
os.close();
} catch (IOException e) {
//
}
}
}
public void initSheet(String sheetName){
this.sheet = this.wbk.createSheet(sheetName);
this.sheet.setDefaultColumnWidth((short) 16);
this.row = this.sheet.createRow(0);
this.createRowTitle();
this.count = 1;
}
private void initTitleAndMethod(Class<?> clazz) {
ExportAnnotation exportAnnotation = clazz.getAnnotation(ExportAnnotation.class);
Field[] fields = clazz.getDeclaredFields();
//初始化 标题[0][0]
{
//第一个title 序号
int length = exportAnnotation.length();
this.titlesAndMethods[0] = new String[length+1];
this.titlesAndMethods[1] = new String[length+1];
this.titlesAndMethods[0][0] = "序号";
}
int index;
for(Field field :fields){
exportAnnotation = field.getAnnotation(ExportAnnotation.class);
if(exportAnnotation != null) {
index = exportAnnotation.order();
//标题
this.titlesAndMethods[0][index] = exportAnnotation.columnTitle();
this.titlesAndMethods[1][index] = exportAnnotation.method();
}
}
}
/**
* 创建标题行
*/
private void createRowTitle() {
String[] titles = titlesAndMethods[0];
for(int i=0;i<titles.length;i++){
this.cell = this.row.createCell(i);
this.cell.setCellStyle(this.titleStyle);
//每次都要实例化一个 如果为final cellValue 全部相同 太耗内存
// this.xssfRichTextString = new XSSFRichTextString();
// this.xssfRichTextString.setString(titles[i]);
this.cell.setCellValue(titles[i]);
}
}
private void initStyle(CellStyle style,boolean isBold) {
// 设置这些样式SOLID_FOREGROUND
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
// 生成一个字体
Font font = this.wbk.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBold(isBold);
style.setFont(font);
}
/**
* 创建 cell
*/
private void createCellValue(int columnIndex){
// 判断值的类型后进行强制类型转换
String textValue = null;
if (this.cellValue instanceof Date) {
textValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) this.cellValue);
}else if (this.cellValue instanceof byte[]) {
Drawing patriarch = this.sheet.createDrawingPatriarch();
// 有图片时,设置行高为60px;
this.row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
this.sheet.setColumnWidth(columnIndex, (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) this.cellValue;
ClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, columnIndex, (short) 6, columnIndex);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
patriarch.createPicture(anchor, this.wbk.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
}else {
// 其它数据类型都当作字符串简单处理
if (this.cellValue != null) textValue = this.cellValue.toString();
}
// 如果不是图片数据,就判断textValue是否全部由数字组成
if (textValue != null) {
this.cell.setCellStyle(this.cellStyle);
if (this.cellValue instanceof Integer) {
//double 强制转换 会有小数点
this.cell.setCellValue((int) this.cellValue);
}else if(this.cellValue instanceof Number){
this.cell.setCellValue((double) this.cellValue);
}
else {
this.cell.setCellValue(textValue);
}
}
}
public static class Builder{
private Class<?> targetClass;
/**
* 导出类型是否是excel 2003
*/
private boolean is2003 = false;
/**
* 是否是Sax方式导入
*/
private boolean isSax = false;
private int rowAccessWindowSize = 100;
public Class<?> getTargetClass() {
return targetClass;
}
private Builder setTargetClass(Class<?> targetClass) {
this.targetClass = targetClass;
return this;
}
public Builder setIs2003(boolean is2003) {
this.is2003 = is2003;
return this;
}
public Builder setSax(boolean sax) {
this.isSax = sax;
return this;
}
public Builder setRowAccessWindowSize(int rowAccessWindowSize) {
this.rowAccessWindowSize = rowAccessWindowSize;
return this;
}
public static Builder create(Class<?> targetClass){
if(targetClass == null){
throw new NullPointerException();
}
return new Builder().setTargetClass(targetClass);
}
public ExportExcelUtil build(){
if(this.isSax){
if(this.is2003){
throw new UnsupportedOperationException("excel2003不支持sax解析方式");
}
}
return new ExportExcelUtil(this);
}
}
public String getExcelSuffix(){
if(this.wbk instanceof HSSFWorkbook){
return ".xls";
}
return ".xlsx";
}
}
下面贴一下工具使用方法
1.实例化工具类(可以配置SAX解析)
ExportExcelUtil excelUtil = ExportExcelUtil.Builder.create(User.class).setIs2003(false).build();
2.初始化sheet
excelUtil.initSheet("用户列表");
3.填充数据
for(int i = 0; i <1000;i++){
User user = new User();
user.setId(i+1);
user.setAge(18);
user.setCreateTime(new Date());
user.setEmail("***@123.com");
user.setMobile("***");
user.setUserName("wl");
excelUtil.exportRowSet(user);
}
导出excel
excelUtil.writeWbk(new FileOutputStream(new File("user" + excelUtil.getExcelSuffix())));
下面是我的测试代码(导出10万条数据)
public static void main(String[] args) throws FileNotFoundException {
ExportExcelUtil excelUtil = ExportExcelUtil.Builder.create(User.class).setIs2003(false).setSax(true).setRowAccessWindowSize(50000).build();
excelUtil.initSheet("用户列表");
for(int i = 0; i <100000;i++){
User user = new User();
user.setId(i+1);
user.setAge(18);
user.setCreateTime(new Date());
user.setEmail("***@123.com");
user.setMobile("***");
user.setUserName("wl");
excelUtil.exportRowSet(user);
}
excelUtil.writeWbk(new FileOutputStream(new File("user" + excelUtil.getExcelSuffix())));
}
打印的内存使用情况如下
Connected to the target VM, address: '127.0.0.1:60590', transport: 'socket'
22:22:38.173 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========245M
22:22:38.282 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========245M
22:22:38.345 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========245M
22:22:38.413 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========245M
22:22:38.475 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========309M
22:22:38.522 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========309M
22:22:38.569 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========309M
22:22:38.647 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========309M
22:22:38.694 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========309M
22:22:38.725 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========309M
Disconnected from the target VM, address: '127.0.0.1:60590', transport: 'socket'
下面我们不采用sax解析
public static void main(String[] args) throws FileNotFoundException {
ExportExcelUtil excelUtil = ExportExcelUtil.Builder.create(User.class).setIs2003(false).setSax(false).build();
excelUtil.initSheet("用户列表");
for(int i = 0; i <100000;i++){
User user = new User();
user.setId(i+1);
user.setAge(18);
user.setCreateTime(new Date());
user.setEmail("***@123.com");
user.setMobile("***");
user.setUserName("wl");
excelUtil.exportRowSet(user);
}
excelUtil.writeWbk(new FileOutputStream(new File("user" + excelUtil.getExcelSuffix())));
}
Connected to the target VM, address: '127.0.0.1:60603', transport: 'socket'
22:24:03.762 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========448M
22:24:04.262 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========706M
22:24:04.770 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========716M
22:24:05.130 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========1024M
22:24:06.458 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========1189M
22:24:06.817 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========1317M
22:24:07.130 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========1317M
22:24:08.370 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========1785M
22:24:08.807 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========1790M
22:24:09.104 [main] INFO com.wl.farmer.action.util.ExportExcelUtil - ==========1790M
Disconnected from the target VM, address: '127.0.0.1:60603', transport: 'socket'
可以看到不仅解析缓慢,而且使用内存巨大
导出文件如下
使用模板
在工具类中加入模板代码
public static class ExportTemplate {
public Result executeExport(ExportCallBackHandler exportCallBackHandler) {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
try {
//doSomething
//回调
return exportCallBackHandler.processExport(bos);
//doSomething
} catch (IOException | RuntimeException e) {
logger.error(e.getMessage(),e);
return Result.fail(e.getMessage());
} finally {
IOUtils.closeQuietly(bos);
}
}
//回调接口
public interface ExportCallBackHandler {
Result processExport(ByteArrayOutputStream bos) throws IOException;
}
}
需要引入commons-io包和Result类
package com.wl.farmer.action.model;
import java.io.Serializable;
/**
* Created by Administrator on 2019/2/20.
*/
public class Result<T> {
private Integer code;
private String msg;
private T body;
public Integer getCode() {
return code;
}
private void setCode(Integer code) {
this.code = code;
}
public String getMsg() {
return msg;
}
private void setMsg(String msg) {
this.msg = msg;
}
public T getBody() {
return body;
}
private void setBody(T body) {
this.body = body;
}
private static class Builder<T>{
private Integer code;
private String msg;
private T body;
private Builder<T> body(T body){
this.body = body;
return this;
}
private Builder<T> code(Integer code){
this.code = code;
return this;
}
private Builder<T> msg(String msg){
this.msg = msg;
return this;
}
private static <T> Builder<T> custom(T body){
return new Builder<T>().body(body);
}
private Result<T> build(){
Result<T> result = new Result<>();
result.setBody(this.body);
result.setCode(this.code);
result.setMsg(this.msg);
return result;
}
}
public static <T> Result<T> success(T t ,String msg){
return Builder.custom(t).code(Code.SUCCESS.CODE).msg(msg).build();
}
public static <T> Result<T> success(T t){
return success(t, Code.SUCCESS.MSG);
}
public static <T> Result<T> success(){
return success(null, Code.SUCCESS.MSG);
}
public static <T> Result<T> success(String msg){
return success(null,msg);
}
//==================================================================
public static <T> Result<T> fail(T t ,String msg){
return Builder.custom(t).code(Code.FAILED.CODE).msg(msg).build();
}
public static <T> Result<T> fail(T t){
return fail(t, Code.FAILED.MSG);
}
public static <T> Result<T> fail(){
return fail(null, Code.FAILED.MSG);
}
public static <T> Result<T> fail(String msg){
return fail(null,msg);
}
public enum Code implements Serializable{
SUCCESS("成功"),
FAILED("失败");
public final String MSG;
Code(String msg){
this.MSG = msg;
}
public final Integer CODE = this.ordinal();
}
}
测试代码
public static void main(String[] args) throws FileNotFoundException {
ExportExcelUtil excelUtil = ExportExcelUtil.Builder.create(User.class).setIs2003(false).setSax(true).setRowAccessWindowSize(50000).build();
Result result = new ExportExcelUtil.ExportTemplate().executeExport(new ExportExcelUtil.ExportTemplate.ExportCallBackHandler() {
@Override
public Result processExport(ByteArrayOutputStream bos) throws IOException {
excelUtil.initSheet("用户列表");
for(int i = 0; i <100000;i++){
User user = new User();
user.setId(i+1);
user.setAge(18);
user.setCreateTime(new Date());
user.setEmail("***@123.com");
user.setMobile("***");
user.setUserName("wl");
excelUtil.exportRowSet(user);
}
excelUtil.writeWbk(bos);
//处理bos
IOUtils.copy(new ByteArrayInputStream(bos.toByteArray()),new FileOutputStream("用户列表" + excelUtil.getExcelSuffix()));
return Result.success();
}
});
System.out.println(result.getMsg());
}