利用POI高效导出excel

package com.ronglian.bms.commons.excel;

import com.ronglian.bms.commons.utils.Maps;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 导出excel工具类,支持导出List<List<String>>,List<javabean>,List<Map<String,Object>>数据,
 * 可以指定导出数据的格式及需要翻译的内容，使用Map<String,Object>将此类指定数据传入,详见方法调用说明
 * @Author zli
 */
public class ExportExcelUtil {
    private static final Logger LOG = LoggerFactory.getLogger(ExportExcelUtil.class);
    private static final int COLUMN_WIDTH = 6;
    private static SXSSFWorkbook workbook = null;// 工作簿
    private static XSSFCellStyle  headerStyle = null;// 表头样式
    private static XSSFCellStyle  oddStyle = null;// 奇数行样式
    private static XSSFCellStyle  evenStyle = null;// 偶数行样式
    /**
     * 初始化工作簿
     */
    private static void initWorkBook() {
        try {
            if (workbook == null) {
                workbook = new SXSSFWorkbook(500);
            }
            if (headerStyle == null) {
                headerStyle = createHeaderStyle(workbook);
            }
            if (evenStyle == null) {
                evenStyle = createEvenStyle(workbook);
            }
            if (oddStyle == null) {
                oddStyle = createOddStyle(workbook);
            }
        } catch (Exception e) {
            LOG.error("初始化工作簿失败", e);
        }
    }
    /**
     * 创建表头单元格样式
     * @param workbook
     * @return
     */
    private static XSSFCellStyle createHeaderStyle(Workbook workbook) {
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        Font font = workbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体粗细
        font.setBoldweight((short) 20);
        // 将字体应用到样式上面
        cellStyle.setFont(font);
        // 是否自动换行
        cellStyle.setWrapText(false);
        // 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }
    /**
     * 创建偶数行样式
     * @param workbook
     * @return
     */
    private static XSSFCellStyle createEvenStyle(Workbook workbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) workbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat)workbook.createDataFormat();
        Font font = workbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 11);
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }

    /**
     * 创建奇数行样式
     * @param workbook
     * @return
     */
    private static XSSFCellStyle  createOddStyle(Workbook workbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) workbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat)workbook.createDataFormat();
        Font font = workbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 11);
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 前景颜色
        xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        xssfCellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }
    /**
     * 创建工作表并写表头
     * @param title
     * @param tableHeads
     * @param out
     * @param widthRow
     * @return
     */
    private static SXSSFSheet createSheetAndWriteTableHead(String title, List<List<String>> tableHeads, OutputStream out, Integer widthRow) {
        if (out == null) {
            LOG.info("导出excel数据时，输出流不存在");
            return null;
        }
        // 创建表
        initWorkBook();
        SXSSFSheet sheet = null;
        try {
            if (StringUtils.isBlank(title)) {
                sheet = (SXSSFSheet) workbook.createSheet();
            } else {
                sheet = (SXSSFSheet)workbook.createSheet(title);
            }
        } catch (Exception e) {
            LOG.error("创建excel工作簿失败", e);
        }
        if (sheet == null) {
            return null;
        }
        // 写表头
        writeTableHead(sheet, headerStyle, tableHeads, widthRow);
        return sheet;
    }
    /**
     * 写表头
     * @param sheet
     * @param headerStyle
     * @param tableHeads
     * @param widthRow
     */
    private static void writeTableHead(SXSSFSheet sheet, XSSFCellStyle headerStyle, List<List<String>> tableHeads, Integer widthRow) {
        if (widthRow == null || widthRow > tableHeads.size() - 1) {
            widthRow = tableHeads.size() - 1;
        }
        // 定义合并单元格
        List<CellRangeAddress> mergList = new ArrayList<CellRangeAddress>();
        for (int i = 0; i < tableHeads.size(); i++) {
            int merindex = 0;//定义合并单元格到某一列
            Row row = sheet.createRow(i);
            List<String> haderList = tableHeads.get(i);
            for (int j = 0; j < haderList.size(); j++) {
                String value = haderList.get(j);
                if (i == widthRow) {
                    // 设置列宽自适应，较好的支持中文
                    sheet.setColumnWidth(j, StringUtils.isNotBlank(value) ? value.getBytes().length*2*256 : COLUMN_WIDTH*2*256);
                }
                Cell cell = row.createCell(j);
                cell.setCellStyle(headerStyle);
                cell.setCellValue(value);
                // 计算合并项
                if (StringUtils.isBlank(value)) {
                    // 检查再下一个值是否为空，不为空则合并当前，若有值则暂时不合并
                    if ((j + 1) < haderList.size()) {// 防止数组下标越界
                        String valueNext = haderList.get(j + 1);
                        if (StringUtils.isNotBlank(valueNext)) {
                            CellRangeAddress crd = new CellRangeAddress(i , i, merindex, j);
                            mergList.add(crd);
                            merindex = j + 1;
                        }
                    }
                    //检查自己是不是最后一列，若果是，则和前面的合并
                    if (j == haderList.size() -1) {
                        CellRangeAddress crd = new CellRangeAddress(i , i, merindex, j);
                        mergList.add(crd);
                    }
                }
            }
        }
        // 将表头空的部分合并单元格
        for (CellRangeAddress cr: mergList) {
            sheet.addMergedRegion(cr);
        }
    }
    /**
     * 写数据
     * @param sheet
     * @param oddStyle
     * @param evenStyle
     * @param dataList
     * @param startRow
     */
    private static void writeTableBody(SXSSFSheet sheet, XSSFCellStyle oddStyle, XSSFCellStyle evenStyle, List<List<String>> dataList, int startRow) {
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + startRow);
            List<String> data = dataList.get(i);
            for (int j = 0; j < data.size(); j++) {
                String value = data.get(j) != null ? data.get(j) : "";
                Cell cell = row.createCell(j);
                if (i%2 == 0) {
                    cell.setCellStyle(evenStyle);
                } else {
                    cell.setCellStyle(oddStyle);
                }
                cell.setCellValue(value);
            }
        }
    }

    /**
     * 写javabean数据
     * @param sheet
     * @param oddStyle
     * @param evenStyle
     * @param columns
     * @param dataList
     * @param clazz
     * @param translation 字段对应的翻译值
     * @param startRow
     */
    private static void writeTableBody(SXSSFSheet sheet, XSSFCellStyle oddStyle, XSSFCellStyle evenStyle, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, int startRow) {
        Map<String, Method> methodMap = Maps.newHashMap();
        // 如果是javabean类,初始化javabean类的get方法
        if (!clazz.isInstance(java.util.Map.class)) {
            for (String s: columns) {
                if (StringUtils.isBlank(s)) {continue;}
                String methodName = "get" + s.substring(0,1).toUpperCase() + s.substring(1);
                try {
                    Method method = clazz.getDeclaredMethod(methodName);
                    methodMap.put(s, method);
                } catch (NoSuchMethodException e) {
                }
            }
        }
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + startRow);
            Object obj = dataList.get(i);
            if (obj == null) {
                continue;
            }
            for (int j = 0; j < columns.size(); j++) {
                Cell cell = row.createCell(j);
                if (i%2 == 0) {
                    cell.setCellStyle(evenStyle);
                } else {
                    cell.setCellStyle(oddStyle);
                }
                String attr = columns.get(j);
                if (StringUtils.isBlank(attr)) {
                    // 为空的单元格
                    continue;
                }
                String value = "";
                if (obj instanceof java.util.Map) {
                    // 从Map获取数据
                    Map<String, Object> map = (Map<String, Object>) obj;
                    Object objVal = map.get(attr);
                    value = getValue(objVal, attr, translation);
                } else {
                    try {
                        Method method = methodMap.get(attr);
                        if (method != null) {
                            Object reflectVal = method.invoke(obj);
                            value = getValue(reflectVal, attr, translation);
                        }
                    } catch (Exception e) {
                    }
                }
                cell.setCellValue(value);
            }
        }
    }

    /**
     * 获取String类型的值，并做翻译和格式化
     * @param reflectVal 值
     * @param key 键
     * @param translation 内容翻译
     * @return
     */
    private static String getValue(Object reflectVal, String key, Map<String,Object> translation) {
        String value = "";
        if (reflectVal == null) {
            return "";
        }
        if (translation == null) {
            return String.valueOf(reflectVal);
        }
        Object trans = translation.get(key);
        if (trans == null) {
            return String.valueOf(reflectVal);
        }
        if (trans instanceof java.lang.String) {
            String tranStr = trans.toString();
            switch (tranStr) {
                case "yyyy-MM-dd":
                    if (reflectVal instanceof java.util.Date) {
                        return String.format("%tF", (java.util.Date)reflectVal);
                    }
                case "HH:mm:ss":
                    if (reflectVal instanceof java.util.Date) {
                        return String.format("%tT", (java.util.Date)reflectVal);
                    }
                case "yyyy-MM-dd HH:mm:ss":
                    if (reflectVal instanceof java.util.Date) {
                        return String.format("%tF", (java.util.Date)reflectVal) + " " + String.format("%tT", (java.util.Date)reflectVal);
                    }
                default:
                    if (tranStr.startsWith("#")) {
                        DecimalFormat df = new DecimalFormat(tranStr);
                        try {
                            return df.format(reflectVal);
                        } catch (Exception e) {}
                    }
                    break;
            }
        }
        if (trans instanceof java.util.Map) {
            Map<String, Object> transMap = (Map<String, Object>) trans;
            return transMap.get(reflectVal.toString()) != null ? String.valueOf(transMap.get(reflectVal.toString())) : "";
        }
        return value;
    }

    /**
     * 将数据流输出后关闭资源
     * @param out
     */
    private static void finishWriteAndrelease(OutputStream out) {
        try {
            workbook.write(out);
        } catch (Exception e) {
            LOG.error("写excel文件失败:", e.getMessage());
        } finally {
            // 释放资源
            try {
                if(workbook != null) {
                    // dispose of temporary files backing this workbook on disk -> 处理SXSSFWorkbook导出excel时，产生的临时文件
                    workbook.dispose();
                }
                if(out != null) {
                    out.close();
                }
            } catch (Exception e) {
                LOG.error("释放写文件资源失败:", e.getMessage());
            }
        }
    }
    /**
     * 从网络请求获取输出流
     * @param response
     * @param fileName
     * @return
     */
    private static OutputStream getFromResponse(HttpServletResponse response, String fileName) {
        OutputStream out = null;
        if (response == null) {
            return null;
        }
        try {
            response.reset();
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
            out = response.getOutputStream();
        } catch (IOException e) {
            LOG.error("网络请求获取输出流失败:", e);
        }
        return out;
    }
    /**
     * 从文件获取输出流
     * @param fileFullName
     * @return
     */
    private static OutputStream getFromFile(String fileFullName) {
        OutputStream out = null;
        if (StringUtils.isBlank(fileFullName)) {
            return null;
        }
        File file = new File(fileFullName);
        if (!file.exists()) {
            try {
                file.createNewFile();
                out = new FileOutputStream(file);
            } catch (IOException e) {
                LOG.error("创建文件失败:", e);
            }
        }
        return out;
    }
    /**
     * 统一导出Excel文件标准方法
     * @param title 表格标题,可以为空
     * @param tableHeads 表头集合,支持多行表头
     * @param dataList String数据集合,二维list,将按顺序写出数据
     * @param out 输出流
     * @param widthRow 以表头中哪一行为列宽自适应标准，不指定或者指定的行超过表头行，将以表头最后一行为标准，表头内容为空时将采用系统默认列宽
     */
    public static void exportStringData(String title, List<List<String>> tableHeads, List<List<String>> dataList, OutputStream out, Integer widthRow) {
        SXSSFSheet sheet = createSheetAndWriteTableHead(title, tableHeads, out, widthRow);
        if (sheet == null) {return;}
        // 写表数据
        int startRow = tableHeads.size();
        writeTableBody(sheet, oddStyle, evenStyle, dataList, startRow);
        // 输出流后关闭资源
        finishWriteAndrelease(out);
    }
    /**
     * 导出数据到到指定excel文件
     * @param title 表标题头
     * @param tableHeads 表头
     * @param dataList 数据String集
     * @param fileFullName 指定文件全名
     * @param widthRow 以表头中哪一行为列宽自适应标准，不指定或者指定的行超过表头行，将以表头最后一行为标准，表头内容为空时将采用系统默认列宽
     */
    public static void exportStringData2File(String title, List<List<String>> tableHeads, List<List<String>> dataList, String fileFullName, Integer widthRow) {
        OutputStream out = getFromFile(fileFullName);
        if (out != null) {
            exportStringData(title, tableHeads, dataList, out, widthRow);
        }
    }
    /**
     * 下载String数据集到excel文件
     * @param title 表标题头
     * @param tableHeads 表头
     * @param dataList 数据String集
     * @param response 网络响应对象
     * @fileName 文件名
     * @param widthRow 以表头中哪一行为列宽自适应标准，不指定或者指定的行超过表头行，将以表头最后一行为标准，表头内容为空时将采用系统默认列宽
     */
    public static void downLoadStringData(String title, List<List<String>> tableHeads, List<List<String>> dataList, HttpServletResponse response, String fileName, Integer widthRow) {
        OutputStream out = getFromResponse(response, fileName);
        if (out != null) {
            exportStringData(title, tableHeads, dataList, out, widthRow);
        }
    }
    /**
     * 将javabean对象集合导出excel文件
     * @param title excel文件标题 可为空
     * @param tableHeads 表头集合 不可为空
     * @param columns bean属性顺序集合(将按此顺序导出) 不可为空
     * @param dataList 数据集 可为空，结果为空文件
     * @param clazz javabean类或者Map<String, Object> 不可为空
     * @param translation Map<String, Map<String,Object>>内容翻译,支持
     *                    java.util.Date的日期格式翻译 yyyy-MM-dd,HH:mm:ss,yyyy-MM-dd HH:mm:ss,
     *                    Double/Float/BigDecimal保留小数翻译 #,#.#,#.##,#.####等
     *                    具体内容翻译Map<String, Object> 可为空
     *                    例如:{coulumn0="yyyy-MM-dd",column2=".##",column3={01="微信",02="支付宝"}}等
     * @param out 输出流 不可为空
     * @param widthRow 以表头中哪一行为列宽自适应标准，不指定或者指定的行超过表头行，将以表头最后一行为标准，表头内容为空时将采用系统默认列宽,可为空
     */
    public static void exportObjectData(String title, List<List<String>> tableHeads, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, OutputStream out, Integer widthRow) {
        SXSSFSheet sheet = createSheetAndWriteTableHead(title, tableHeads, out, widthRow);
        if (sheet == null) {return;}
        // 写表数据
        int startRow = tableHeads.size();
        writeTableBody(sheet, oddStyle, evenStyle, columns, dataList, clazz, translation, startRow);
        // 输出流后关闭资源
        finishWriteAndrelease(out);
    }
    /**
     * 将javabean对象集合导出excel文件
     * @param title excel文件标题 可为空
     * @param tableHeads 表头集合 不可为空
     * @param columns bean属性顺序集合(将按此顺序导出) 不可为空
     * @param dataList 数据集 可为空，结果为空文件
     * @param clazz javabean类或者Map<String, Object> 不可为空
     * @param translation Map<String, Map<String,Object>>内容翻译,支持
     *                    java.util.Date的日期格式翻译 yyyy-MM-dd,HH:mm:ss,yyyy-MM-dd HH:mm:ss,
     *                    Double/Float/BigDecimal保留小数翻译 #,#.#,#.##,#.####等
     *                    具体内容翻译Map<String, Object> 可为空
     *                    例如:{coulumn0="yyyy-MM-dd",column2=".##",column3={01="微信",02="支付宝"}}等
     * @param fileFullName 文件名
     * @param widthRow 以表头中哪一行为列宽自适应标准，不指定或者指定的行超过表头行，将以表头最后一行为标准，表头内容为空时将采用系统默认列宽,可为空
     */
    public static void exportObjectData2File(String title, List<List<String>> tableHeads, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, String fileFullName, Integer widthRow) {
        OutputStream out = getFromFile(fileFullName);
        if (out != null) {
            exportObjectData(title, tableHeads, columns, dataList, clazz, translation, out, widthRow);
        }
    }
    /**
     * 将javabean对象集合导出excel文件
     * @param title excel文件标题 可为空
     * @param tableHeads 表头集合 不可为空
     * @param columns bean属性顺序集合(将按此顺序导出) 不可为空
     * @param dataList 数据集 可为空，结果为空文件
     * @param clazz javabean类或者Map<String, Object> 不可为空
     * @param translation Map<String, Map<String,Object>>内容翻译,支持
     *                    java.util.Date的日期格式翻译 yyyy-MM-dd,HH:mm:ss,yyyy-MM-dd HH:mm:ss,
     *                    Double/Float/BigDecimal保留小数翻译 #,#.#,#.##,#.####等
     *                    具体内容翻译Map<String, Object> 可为空
     *                    例如:{coulumn0="yyyy-MM-dd",column2=".##",column3={01="微信",02="支付宝"}}等
     * @param  response
     * @param fileName 文件名
     * @param widthRow 以表头中哪一行为列宽自适应标准，不指定或者指定的行超过表头行，将以表头最后一行为标准，表头内容为空时将采用系统默认列宽,可为空
     */
    public static void downloadObjectData(String title, List<List<String>> tableHeads, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, HttpServletResponse response, String fileName,  Integer widthRow) {
        OutputStream out = getFromResponse(response, fileName);
        if (out != null) {
            exportObjectData(title, tableHeads, columns, dataList, clazz, translation, out, widthRow);
        }
    }
}