Excel 工具类 4.1.2

<poi.version>4.1.2</poi.version>

public class ExcelsUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelsUtil.class);


    /**
     * 时间格式
     */
    private static final String TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";
    /**
     * 默认列的宽度
     */
    private static final int COLUMN_WIDTH = 24;
    /**
     * 表头字体大小
     */
    private static final int TITLE_FONT_SIZE = 11;
    /**
     * 表头单元格
     **/
    private static final int TITLE_CELL = 0;
    /**
     * 内容单元格
     **/
    private static final int CONTENT_CELL = 1;

    /**
     * 每个sheeet的最大数据数量
     */
    private static final int MAX_LENGTH = 60000;


    /**
     * 导出
     * 2003版本的xls
     *
     * @param title         表格标题名,文件名
     * @param headers       表格头部标题中文集合
     * @param headerZhWords 表格头部标题的字段名
     * @param dataSet       需要显示的数据集合
     */
    public static <T> void exportExcel(String title, String[] headers, String[] headerZhWords, Collection<T> dataSet,
                                       HttpServletResponse response) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", String.format("attachment;filename=%s.xls", URLEncoder.encode(title, "UTF-8")));
        try (HSSFWorkbook workbook = new HSSFWorkbook();
             ServletOutputStream out = response.getOutputStream()) {
            int pageNum = 1;
            int dataLength = dataSet.size();
            List<T> dataAll = new ArrayList<>(dataSet);
            List<T> dataPage;
            //分sheet处理 每个sheet数据条数为MAX_LENGTH
            while (dataLength >= 0) {
                if (dataLength > MAX_LENGTH) {
                    dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, pageNum * MAX_LENGTH);
                } else {
                    dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, dataAll.size());
                }
                // 生成一个sheet
                HSSFSheet sheet = workbook.createSheet(String.format("%s -%s-", title, pageNum));
                dataLength -= MAX_LENGTH;
                pageNum++;
                // 设置表格默认列宽度
                sheet.setDefaultColumnWidth(COLUMN_WIDTH);
                // 生成标题样式
                HSSFCellStyle titleStyle = setCellStyle(workbook, TITLE_CELL);
                // 产生表格标题行
                HSSFRow row = sheet.createRow(0);
                HSSFCell cellHeader;
                for (int i = 0; i < headers.length; i++) {
                    cellHeader = row.createCell(i);
                    cellHeader.setCellStyle(titleStyle);
                    cellHeader.setCellValue(new HSSFRichTextString(headers[i]));
                }
                //填充内容
                fillContent(headerZhWords, dataPage, workbook, sheet, 0);
            }
            workbook.write(out);
        } catch (IOException e) {
            logger.error("excel生成错误", e);
        }
    }

    /**
     * 设置样式
     **/
    private static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, int cellType) {
        HSSFCellStyle style = null;
        //边框
        BorderStyle borderStyle = BorderStyle.THIN;
        short borderColor = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
        if (cellType == TITLE_CELL) {
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setBorderBottom(borderStyle);
            style.setBorderLeft(borderStyle);
            style.setBorderRight(borderStyle);
            style.setBorderTop(borderStyle);
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            style.setTopBorderColor(borderColor);
            style.setLeftBorderColor(borderColor);
            style.setRightBorderColor(borderColor);
            style.setBottomBorderColor(borderColor);
            //垂直居中
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            //自动换行
            style.setWrapText(false);
            // 生成标题字体
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            font.setFontName("宋体");
            font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
            font.setFontHeightInPoints((short) TITLE_FONT_SIZE);
            // 把字体应用到当前的样式
            style.setFont(font);
        } else if (cellType == CONTENT_CELL) {
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setBorderBottom(borderStyle);
            style.setBorderLeft(borderStyle);
            style.setBorderRight(borderStyle);
            style.setBorderTop(borderStyle);
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置自动换行
            style.setWrapText(false);
            // 生成内容字体
            HSSFFont font = workbook.createFont();
            font.setBold(false);
            style.setFont(font);
        }
        return style;
    }

    /**
     * 填充sheet内容
     *
     * @param headerWords       填充数据的字段
     * @param dataSet           数据集
     * @param workbook          工作上下文
     * @param sheet             sheet
     * @param contentStartIndex 从第contentStartIndex+1行开始填充数据
     */
    private static <T> void fillContent(String[] headerWords, Collection<T> dataSet, HSSFWorkbook workbook, HSSFSheet sheet,
                                        Integer contentStartIndex) {
        //内容样式
        HSSFCellStyle contentStyle = setCellStyle(workbook, CONTENT_CELL);
        SimpleDateFormat sdf = new SimpleDateFormat(TIME_PATTERN);
        //字段名
        String fieldName;
        //get方法名称
        String getMethodName;
        //单元格
        HSSFCell cell;
        Method getMethod;
        Object value = "";
        HSSFRow row;
        contentStartIndex = contentStartIndex == null ? 0 : contentStartIndex;
        T t;
        int index = 0;
        Iterator<T> it = dataSet.iterator();
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index + contentStartIndex);
            t = it.next();
            for (int i = 0; i < headerWords.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(contentStyle);
                fieldName = headerWords[i];
                if (t instanceof Map) {
                    //Map类型
                    value = ((Map<?, ?>) t).get(fieldName);
                } else {
                    getMethodName = getBeanMethodName(fieldName);
                    //Bean类
                    try {
                        getMethod = t.getClass().getMethod(getMethodName);
                        value = getMethod.invoke(t);
                    } catch (SecurityException | NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                        logger.error("excel生成错误", e);
                    }
                }
                if (null == value) {
                    cell.setCellValue("");
                } else if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof Float || value instanceof Double) {
                    cell.setCellValue(String.valueOf(value));
                } else if (value instanceof Long) {
                    cell.setCellValue((Long) value);
                } else if (value instanceof Date) {
                    cell.setCellValue(sdf.format((Date) value));
                } else {
                    cell.setCellValue(String.valueOf(value));
                }
            }
        }
    }

    /**
     * 获取bean的获取值的方法
     *
     * @param fieldName
     * @return
     */
    private static String getBeanMethodName(String fieldName) {
        String cacheName = CommonConstant.CachePre.METHOD + fieldName;
        String methodName = String.valueOf(CommonConstant.BaseCache.COMMON_CACHE.get(cacheName));
        if (CommonConstant.BaseConstant.NULL.equals(methodName)) {
            methodName = "get" + fieldName.substring(0, 1).toUpperCase()
                    + fieldName.substring(1);
            CommonConstant.BaseCache.COMMON_CACHE.put(cacheName, methodName);
        }
        return methodName;
    }
}