EXECL大数据导出工具类

背景：excel导出方法对比：
1.在POI中使用HSSF对象时，excel 2003最多只允许存65536行数据，一般用来处理较少的数据量，这时对于百万级别数据
2.使用XSSF对象时，它可以直接支持excel2007以上版本，excel可以支持1048576条数据，单个sheet表就支持近104万条数据了,虽然这时导出100万数据能满足要求，但使用XSSF测试后发现偶尔还是会发生堆溢出
3.使用SXSSFWorkbook，文件后缀名：.xlsx, excel可以支持1048576条数据。当数据加工时不是类似前面版本的对象，它可以控制excel数据占用的内存，他通过控制在内存中的行数来实现资源管理，即当创建对象超过了设定的行数，它会自动刷新内存，将数据写入文件，这样导致打印时，占用的CPU，和内存很少。但是需要注意的是，导出大数据量时也不要一次性读取数据库所有数据加载到内存，否则还是会有问题，所以，此处结合分页从数据库读取数据的方式，可以进行大数据量的excel写入。

工具包功能：提供大数据量分页及小数据量一次性读取数据的excel导出功能，业务只需关注取数逻辑，其他excel操作无需关注。

1.自定义导出注解

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
@Documented
public @interface ExportField {
    String name();

    String type() default "String";

    int width() default 100;

    boolean border() default true;

    String enName() default "";
    boolean number() default false;
}

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
@Documented
public @interface ExportSupport {
}

2.定义业务基类

public class BaseVo implements Serializable {
    private static final long serialVersionUID = 697394470607540368L;

public BaseVo() {
}}

3.业务数据获取接口

	public interface DataCallback {
	    List<BaseVo> fetchData(int var1, int var2);
	}

4.工具类

public class OfficeUtil {
public OfficeUtil() {
}
public static class Excel {
    public static final String localeEn = "en";
    public Excel() {
    }
    /**
     * 数据量较小时一次性获取所有源数据
     * @param sources
     * @return
     * @throws IllegalAccessException
     */
    public static SXSSFWorkbook build(List<BaseVo> sources) throws IllegalAccessException {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet();
        SXSSFRow header = sheet.createRow(0);
        if (!sources.isEmpty()) {
            List<Field> exportFields = Lists.newArrayList();
            Class clazz = ((BaseVo)sources.get(0)).getClass();
            Field[] fields = clazz.getDeclaredFields();
            Field[] filedsCopy = fields;
            int length = fields.length;

            for(int k = 0; k < length; ++k) {
                Field field = filedsCopy[k];
                field.setAccessible(true);
                ExportField annotation = (ExportField)field.getAnnotation(ExportField.class);
                if (null != annotation) {
                    exportFields.add(field);
                }
            }

            int i;
            for(i = 0; i < exportFields.size(); ++i) {
                Field field = (Field)exportFields.get(i);
                ExportField annotation = (ExportField)field.getAnnotation(ExportField.class);
                SXSSFCell hssfCell = header.createCell(i);
                hssfCell.setCellValue(annotation.name());
                hssfCell.setCellStyle(style(workbook, annotation));
                if (annotation.width() == 100) {
                    sheet.setColumnWidth(i, annotation.name().getBytes().length * 256);
                } else {
                    sheet.setColumnWidth(i, annotation.width() * 256);
                }
            }

            for(i = 0; i < sources.size(); ++i) {
                SXSSFRow row = sheet.createRow(i + 1);
                BaseVo baseVo = (BaseVo)sources.get(i);

                for(int j = 0; j < exportFields.size(); ++j) {
                    Field field = (Field)exportFields.get(j);
                    ExportField annotation = (ExportField)field.getAnnotation(ExportField.class);
                    SXSSFCell hssfCell = row.createCell(j);
                    hssfCell.setCellValue(String.valueOf(field.get(baseVo)));
                    hssfCell.setCellStyle(style(workbook, annotation));
                }
            }
        }

        return workbook;
    }

    /**
     * 数据量较大时分页获取数据
     * @param pageSize
     * @param dataCallback
     * @return
     * @throws IllegalAccessException
     * @throws IOException
     */
    public static SXSSFWorkbook build(int pageSize, DataCallback dataCallback) throws IllegalAccessException, IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        int sheetNum = 0;
        SXSSFSheet sheet = workbook.createSheet(String.valueOf(sheetNum));
        return collectData(null,workbook,sheet,pageSize,dataCallback);
    }

    public static SXSSFWorkbook build(String local,SXSSFWorkbook workbook, String sheetName,int pageSize, DataCallback dataCallback) throws IllegalAccessException, IOException {
        if(workbook == null) {
            workbook = new SXSSFWorkbook(1000);
        }
        SXSSFSheet sheet = workbook.createSheet(String.valueOf(sheetName));
        return collectData(local,workbook,sheet,pageSize,dataCallback);
    }

    //没有数据时只导出表头
    public static SXSSFWorkbook buildNoDataWithTitle(Class clazz, String local, SXSSFWorkbook workbook, String sheetName)  {
        if(workbook == null) {
            workbook = new SXSSFWorkbook(1000);
        }
        SXSSFSheet sheet = workbook.createSheet(String.valueOf(sheetName));
        SXSSFRow header = sheet.createRow(0);
        Field[] fields = clazz.getDeclaredFields();
        int j;
        Field field;
        List<Field> exportFields = Lists.newArrayList();
        List<CellStyle> cellStyles = Lists.newArrayList();
        for(j = 0; j < fields.length; ++j) {
            field = fields[j];
            field.setAccessible(true);
            ExportField annotation = (ExportField)field.getAnnotation(ExportField.class);
            if (null != annotation) {
                exportFields.add(field);
            }
        }

        for(int i = 0; i < exportFields.size(); ++i) {
            Field fieldValue = (Field)exportFields.get(i);
            ExportField annotation = (ExportField)fieldValue.getAnnotation(ExportField.class);
            SXSSFCell sxssfCell = header.createCell(i);
            if(localeEn.equals(local)) {
                sxssfCell.setCellValue(annotation.enName());
            } else {
                sxssfCell.setCellValue(annotation.name());
            }
            CellStyle cellStyle = style(workbook, annotation);
            cellStyles.add(cellStyle);
            sxssfCell.setCellStyle(cellStyle);
            if (annotation.width() == 100) {
                sheet.setColumnWidth(i, annotation.name().getBytes().length * 256);
            } else {
                sheet.setColumnWidth(i, annotation.width() * 256);
            }
        }
        return workbook;
    }

    //支持表头国际化
    private static SXSSFWorkbook collectData(String local,SXSSFWorkbook workbook, SXSSFSheet sheet, int pageSize, DataCallback dataCallback) throws IOException, IllegalAccessException {
        SXSSFRow header = sheet.createRow(0);
        boolean flag = true;
        int line = 1;
        int totalRow = 0;
        List<Field> exportFields = Lists.newArrayList();
        List<CellStyle> cellStyles = Lists.newArrayList();
        int pageNo = 1;
        while(true) {
            while(flag) {
                List<BaseVo> sources = dataCallback.fetchData(pageNo, pageSize);
                ++pageNo;
                if (!sources.isEmpty()) {
                    int j;
                    Field field;
                    if (line == 1) {
                        Class clazz = ((BaseVo)sources.get(0)).getClass();
                        Field[] fields = clazz.getDeclaredFields();
                        Field[] var15 = fields;
                        int var16 = fields.length;

                        for(j = 0; j < var16; ++j) {
                            field = var15[j];
                            field.setAccessible(true);
                            ExportField annotation = (ExportField)field.getAnnotation(ExportField.class);
                            if (null != annotation) {
                                exportFields.add(field);
                            }
                        }

                        for(int i = 0; i < exportFields.size(); ++i) {
                            Field fieldValue = (Field)exportFields.get(i);
                            ExportField annotation = (ExportField)fieldValue.getAnnotation(ExportField.class);
                            SXSSFCell sxssfCell = header.createCell(i);
                            if(localeEn.equals(local)) {
                                sxssfCell.setCellValue(annotation.enName());
                            } else {
                                sxssfCell.setCellValue(annotation.name());
                            }
                            CellStyle cellStyle = style(workbook, annotation);
                            cellStyles.add(cellStyle);
                            sxssfCell.setCellStyle(cellStyle);
                            if (annotation.width() == 100) {
                                sheet.setColumnWidth(i, annotation.name().getBytes().length * 256);
                            } else {
                                sheet.setColumnWidth(i, annotation.width() * 256);
                            }
                        }

                        ++line;
                    }

                    int flushRows = 100;

                    for(int i = 0; i < sources.size(); ++i) {
                        SXSSFRow row = sheet.createRow(totalRow + 1);
                        ++totalRow;
                        BaseVo baseVo = (BaseVo)sources.get(i);

                        for(j = 0; j < exportFields.size(); ++j) {
                            field = (Field)exportFields.get(j);
                            ExportField annonation = field.getAnnotation(ExportField.class);
                            SXSSFCell hssfCell = row.createCell(j);
                            Object o = field.get(baseVo);
                            String value = "";
                            if(null != o) {
                                value = String.valueOf(o);
                                if (org.apache.commons.lang3.StringUtils.isBlank(value)) {
                                    value = "";
                                }
                            }
                            CellStyle cellStyle  = (CellStyle)cellStyles.get(j);
                            if(annonation.number()) {
                                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
                                if(StringUtils.isBlank(value)) {
                                    hssfCell.setCellValue(0);
                                } else {
                                    hssfCell.setCellValue(Double.parseDouble(value));
                                }
                            } else {
                                hssfCell.setCellValue(value);
                            }
                            hssfCell.setCellStyle(cellStyle);
                        }

                        if (i % flushRows == 0) {
                            sheet.flushRows(flushRows);
                        }
                    }
                } else {
                    flag = false;
                }
            }

            return workbook;
        }
    }
    /**
     *
     * @param is 输入流
     * @param columns 解析列数
     * @param sheetName excel sheet名称
     * @return
     * @throws XmlException
     * @throws OpenXML4JException
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    public static List<ExcelReader.RowData[]> parseExcel(InputStream is, int columns, String sheetName) throws Exception {
        try {
            return ExcelReader.readerExcel(is,columns,Integer.MAX_VALUE,sheetName);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("parse excel error");
        }
    }

    /**
     * @param is 输入流
     * @param columns  列数
     * @param sheetName 工作薄名称
     * @param callBack 回调
     * @throws Exception
     */
    public static void parseExcel(InputStream is, int columns, String sheetName, ExcelReader.ProcessCallBack callBack) throws Exception {
        try {
            ExcelReader.readerExcel(is,columns,Integer.MAX_VALUE,sheetName,callBack);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("parse excel error");
        }
    }



    private static CellStyle style(SXSSFWorkbook sxssfWorkbook, ExportField annotation) {
        CellStyle xssfCellStyle = sxssfWorkbook.createCellStyle();
        if (annotation.border()) {
            xssfCellStyle.setBorderBottom(BorderStyle.THIN);
            xssfCellStyle.setBorderLeft(BorderStyle.THIN);
            xssfCellStyle.setBorderRight(BorderStyle.THIN);
            xssfCellStyle.setBorderTop(BorderStyle.THIN);
        }

        return xssfCellStyle;
    }}}

5.使用
定义业务类

		/**
	 * 导出的结果类
	 * 1.继承BaseVo
	 * 2.类上加@ExportSupport注解
	 * 3.导出字段上加ExportField注解，其中name属性是该列的表头
	 */
	@Getter
	@Setter
	@ExportSupport
	public class UserVo extends BaseVo {
	    @ExportField(name = "工号",enName = "employeeId", width = 200)
	    private String userId;
	    @ExportField(name = "姓名",enName = "employeeEnName")
	    private String uesrName;
	    @ExportField(name = "年龄",enName = "age")
	    private Integer age;
	    @ExportField(name = "出生日期",enName = "birth")
	    private String birthday;
	    private Double salary;
	    //是否在职
	    private String atJob;
	    @ExportField(name = "金额", number = true)
	    private BigDecimal amount = new BigDecimal("111199999.99");
	}

调用方法：

			 sxssfWorkbook = OfficeUtil.Excel.build("en",sxssfWorkbook,"test2",100, new DataCallback() {  /*
			             * 业务侧取数逻辑，实际替换成业务的分页查询数据
			             * @param var1  pageNo
			             * @param var2  pageSize
			             * @return*//*
			
            @Override
            public List<BaseVo> fetchData(int var1, int var2) {
                List<UserVo> users = new ArrayList<>();
                //测试前10页数据
                if (var1 < 1045) {
                    for (int i = 0; i < var2; i++) {
                        UserVo userVo = new UserVo();
                        userVo.setUserId(var1 + "-" + var2);
                        userVo.setUesrName("测试");
                        userVo.setAge(18);
                        userVo.setBirthday("2019-06-16");
                        users.add(userVo);

                    }
                }
                List<BaseVo> result = Lists.newArrayList(users);
                return result;

            }
        });
         FileOutputStream out = new FileOutputStream("/Users/guomingjun/Desktop/分页.xlsx");
        sxssfWorkbook.write(out);
        sxssfWorkbook.close();
        out.close();