XlsxExcel导出工具XlsxExcelUtils

import java.io.File;import java.io.FileOutputStream;import java.math.BigDecimal;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.UUID;import org.apache.commons.beanutils.PropertyUtils;import org.apache.commons.io.FileUtils;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DataFormat;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCellStyle;/** * [Excel导出工具类] */public class XlsxExcelUtils {    private static final Log log = LogFactory.getLog(XlsxExcelUtils.class);    /***     * Excel工作表数据提供者接口     * XlsxExcelUtils.java     */    public interface SheetDataProvider{        /**         * 默认批量获取数据条数         */        public final static int DEFAULT_BATCH_SIZE = 5000;        /**         * 获取工作表名称         * @return         */        public String getSheetName();        /**         * 获取工作表标题行名称数组         * @return         */        public String[] getHeaderTitles();        /**         * 获取工作表数据行各列样式数组         * @return         */        public CellStyleType[] getStyleTypes();        /**         * 获取工作表数据行关键字         * @return         */        public String[] getDataKeys();        /**         * 获取工作表数据行数据总条数         * @return         */        public int getTotal();        /**         * 分批次抓取工作表数据         * @param offset         * @param batchSize         * @return         */        public List<?> getDatas(int offset,int batchSize);        /**         * 每次获取数据最大条数         * @return         */        public int getBatchSize();        /**         * 获取合计列数据         * SheetDataProvider.getSummaryRowData()         * @return         */        public Map<String,Object> getSummaryRowData();    }    public enum CellStyleType{        STRING,NUMBER,MONEY,PERCENT    }    /**     * 生成Excel工作薄     * XlsxExcelUtils.generateWorkbook()     * @param filePath :String      * @param dataProviders : 工作表数据接口数据     */    public static void  generateWorkbook(String filePath,SheetDataProvider ... dataProviders){        long s = System.currentTimeMillis();        try {            /*** 1.创建工作薄 ***/            SXSSFWorkbook wb = new SXSSFWorkbook(10000);// keep 10000 rows in memory, exceeding rows will be flushed to disk            Map<String, CellStyle> styleMap = createStyles(wb);            /*** 2.生成工作表 ***/            for (SheetDataProvider dataProvider : dataProviders) {                generateSheet(wb,styleMap,dataProvider);            }            /*** 3.将工作薄写入文件 ***/            FileOutputStream out = new FileOutputStream(filePath);            wb.write(out);            out.close();            // dispose of temporary files backing this workbook on disk            wb.dispose();        } catch (Exception e) {            e.printStackTrace();            log.error("生成Excel文件时发生异常" + e.getMessage(), e);        }        long e = System.currentTimeMillis();        log.info(String.format("生成Excel文件耗时[%d]ms", (e - s)));    }    /**     * 生成Excel工作表     * XlsxExcelUtils.generateSheet()     * @param wb     * @param styleMap     * @param dataProvider     */    private static void generateSheet(SXSSFWorkbook wb, Map<String, CellStyle> styleMap, SheetDataProvider dataProvider){        /*** 1.创建工作表***/        String sheetname = dataProvider.getSheetName();        Sheet sheet = wb.createSheet(sheetname);        int rowNumber = 0;        /*** 2.插入表头行 ***/        String[] headerTitles = dataProvider.getHeaderTitles();        rowNumber = insertSheetHeaderRow(sheet, rowNumber, headerTitles, styleMap);        String[] dataKeys = dataProvider.getDataKeys();        Map<String,Object> summaryData = dataProvider.getSummaryRowData();        /*** 3.插入合计汇总行 ***/        if(summaryData!=null){            rowNumber = insertSheetSummaryRow(sheet, rowNumber, dataKeys, styleMap ,summaryData);        }        /*** 4.插入数据行 ***/        int total = dataProvider.getTotal();        int batchSize = dataProvider.getBatchSize();        CellStyleType[] styleTypes = dataProvider.getStyleTypes();        if(total<=0){            log.warn("[excel导出]无满足条件数据! ");            return;        }        int offset = 0;        List<?> datas = null;        if(total <= batchSize){            batchSize=total;            log.info(String.format("[excel导出]一次性抓取数据[%d]条! ",batchSize));            long s = System.currentTimeMillis();            datas = dataProvider.getDatas(offset, batchSize);            long e = System.currentTimeMillis();            log.info(String.format("[excel导出]一次性抓取数据完成，抓取到数据[%d]条，耗时[%d]ms",datas.size(),(e-s)));            rowNumber = insertSheetDataRows(sheet, datas, rowNumber, styleTypes, dataKeys,styleMap);        }        if(total > batchSize){            int times = total%batchSize==0?(total/batchSize):(total/batchSize+1);            int index = 0;            while (times-index>0){                log.info(String.format("[excel导出]第[%d]次批量抓取数据[%d/%d],每次抓取[%d]条,共[%d]条! ", (index+1),                        (index+1),times,batchSize,total));                offset = index * batchSize;                long s = System.currentTimeMillis();                datas = dataProvider.getDatas(offset, batchSize);                long e = System.currentTimeMillis();                log.info(String.format("[excel导出]第[%d]次批量抓取数据完成，抓取到数据[%d]条，耗时[%d]ms", (index+1),datas.size(),(e-s)));                rowNumber = insertSheetDataRows(sheet, datas, rowNumber, styleTypes, dataKeys,styleMap);                index++;            }        }    }    private static int insertSheetSummaryRow(Sheet sheet, int rowNumber,            String[] dataKeys, Map<String, CellStyle> styleMap,            Map<String, Object> summaryData) {        Row dataRow = sheet.createRow(rowNumber);        int dataColumnIndex=0;        for (int i = 0; i < dataKeys.length; i++) {            String dataKey = dataKeys[i];            Object value = summaryData.get(dataKey);            Cell sCell = null;            if(null != value){                sCell = dataRow.createCell(dataColumnIndex);                if(value instanceof String ){                    sCell.setCellStyle(styleMap.get("summary"));                    sCell.setCellValue(String.valueOf(value));                }                if(value instanceof BigDecimal){                    sCell.setCellType(Cell.CELL_TYPE_NUMERIC);                    sCell.setCellStyle(styleMap.get("currency_summary"));                    sCell.setCellValue(((BigDecimal)value).doubleValue());                }                if(value instanceof Integer){                    sCell.setCellType(Cell.CELL_TYPE_NUMERIC);                    sCell.setCellStyle(styleMap.get("number"));                    sCell.setCellValue((Integer)value);                }            }else{                sCell = dataRow.createCell(dataColumnIndex, Cell.CELL_TYPE_BLANK);            }            dataColumnIndex++;        }        rowNumber++;        return rowNumber;    }    /**     * 插入数据行数据     * XlsxExcelUtils.insertSheetDataRows()     * @param sheet     * @param datas     * @param rowNumber     * @param styleTypes     * @param dataKeys     * @param styleMap      * @return     */    private static int insertSheetDataRows(Sheet sheet,List<?> datas,            int rowNumber, CellStyleType[] styleTypes, String[] dataKeys, Map<String, CellStyle> styleMap) {        for(Object data:datas){            Row dataRow = sheet.createRow(rowNumber);            int dataColumnIndex=0;            for (int i = 0; i < dataKeys.length; i++) {                String dataKey = dataKeys[i];                Object value = null;                try {                    if(dataKey.startsWith("MAPPER#")){                        ///固定值                        if(dataKey.startsWith("MAPPER#DEFAULT#")){                            value = dataKey.split("#")[2];                        }                        ///拼接                        if(dataKey.startsWith("MAPPER#CONCAT#")){                            String keyA = dataKey.split("#")[2];                            String keyB = dataKey.split("#")[3];                            value = String.format("%s,%s", PropertyUtils.getProperty(data, keyA),PropertyUtils.getProperty(data, keyB) );                        }                    //其他                        }else{                            value = PropertyUtils.getProperty(data, dataKey);                    }                } catch (Exception e) {                    log.warn("[excel导出]获取Bean属性值异常"+e.getMessage(), e);                }                Cell dataCell = null;                if(null != value){                    dataCell = dataRow.createCell(dataColumnIndex);                    switch(styleTypes[i]){                    case MONEY:                        dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);                        dataCell.setCellStyle(styleMap.get("currency"));                        break;                    case NUMBER:                        break;                    case PERCENT:                        break;                    case STRING:                        break;                    default:                        break;                    }                    if(value instanceof String ){                        dataCell.setCellValue(String.valueOf(value));                    }                    if(value instanceof BigDecimal){                        dataCell.setCellValue(((BigDecimal)value).doubleValue());                    }                    if(value instanceof Integer){                        dataCell.setCellValue(((Integer)value));                    }                    if(value instanceof Long){                        dataCell.setCellValue(((Long)value));                    }                    if(value instanceof Double){                        dataCell.setCellValue((Double)value);                    }                }else{                    dataCell = dataRow.createCell(dataColumnIndex, Cell.CELL_TYPE_BLANK);                }                dataColumnIndex++;            }            rowNumber++;        }        return rowNumber;    }    /**     * 插入表头行数据     * XlsxExcelUtils.insertSheetHeaderRow()     * @param sheet     * @param rowNumber     * @param headerTitles     * @param styleMap     * @return     */    private static int insertSheetHeaderRow(Sheet sheet, int rowNumber, String[] headerTitles, Map<String, CellStyle> styleMap) {        Row headerRow = sheet.createRow(rowNumber);        int haderColumnIndex = 0;        for (String title : headerTitles) {            Cell cell = headerRow.createCell(haderColumnIndex);            cell.setCellValue(title);            cell.setCellStyle(styleMap.get("header"));            haderColumnIndex++;        }        rowNumber++;        return rowNumber;    }    /**     * [创建常用的单元格样式]     */    private static Map<String, CellStyle> createStyles(SXSSFWorkbook wb){        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();        DataFormat fmt = wb.createDataFormat();        CellStyle style1 = wb.createCellStyle();        style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);        style1.setDataFormat(fmt.getFormat("0.0%"));        styles.put("percent", style1);        CellStyle style2 = wb.createCellStyle();        style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);        style2.setDataFormat(fmt.getFormat("0.0X"));        styles.put("coeff", style2);        CellStyle style3 = wb.createCellStyle();        style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);        style3.setDataFormat(fmt.getFormat("#,##0.00"));        styles.put("currency", style3);        CellStyle style4 = wb.createCellStyle();        style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);        style4.setDataFormat(fmt.getFormat("mmm dd"));        styles.put("date", style4);        CellStyle style5 = wb.createCellStyle();        Font headerFont = wb.createFont();        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);        style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());        style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);        style5.setFont(headerFont);        styles.put("header", style5);        CellStyle style6 = wb.createCellStyle();        Font font6 = wb.createFont();        font6.setBoldweight(Font.BOLDWEIGHT_BOLD);        font6.setItalic(true);        style6.setFont(font6);        styles.put("summary", style6);        CellStyle style7 = wb.createCellStyle();        Font font7 = wb.createFont();        font7.setBoldweight(Font.BOLDWEIGHT_BOLD);        font7.setItalic(true);        style7.setFont(font7);        style7.setAlignment(XSSFCellStyle.ALIGN_RIGHT);        style7.setDataFormat(fmt.getFormat("#,##0.00"));        styles.put("currency_summary", style7);        CellStyle style8 = wb.createCellStyle();        Font font8 = wb.createFont();        font8.setBoldweight(Font.BOLDWEIGHT_BOLD);        font8.setItalic(true);        style8.setFont(font8);        style8.setAlignment(XSSFCellStyle.ALIGN_RIGHT);        styles.put("number", style8);        return styles;    }    /**     * 获取临时文件路径     * XlsxExcelUtils.getTempFilePath()<BR>     * @param filePrefix     * @return     */    public static String getTempFilePath(String filePrefix){        //StringBuilder builder = new StringBuilder(FileUtils.getTempDirectoryPath());//系统临时目录        StringBuilder builder = new StringBuilder(FileUtils.getUserDirectoryPath()).append("/temp/");        String tmpDir = builder.toString();        if(!new File(tmpDir).exists()){//目录不存在则创建目录            new File(tmpDir).mkdirs();        }        builder.append(filePrefix).append(UUID.randomUUID()).append(".tmp");        String filePath = builder.toString();        log.debug(filePath);        return filePath;    }}