POI下载excel通用方法

POI下载excel通用方法

最近遇到一个业务是需要下载excel，使用POI,这里记录一下实现过程

1、导包

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

2.公共方法的编写

package com.pingan.esbx.cassandra.util;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Objects;

public class ExcelDonload {
    /**
     * 26个英文字母表
     **/
    public static final String[] letters = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
    /**
     * 创建excel的行数(随描述 标题等的增加而增加)
     **/
    public static int rowNum = NumberUtils.INTEGER_ZERO;
    /**
     * 指定时间格式
     */
    public static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 下载
     * ①tableTitles 与 titlesInfoKey 个数要一致，不能为空
     * ②fileName、title、subtitle、desc均可不传或者传null
     * ③titlesInfoKey 是与tableTitles对应的字段，需与实体类一致
     *
     * @param title         标题
     * @param resultList    数据
     * @param fileName      文件名
     * @param tableTitles   表头
     * @param titlesInfoKey 表头对应的字段
     * @param subtitle      副标题
     * @param desc          描述
     * @param response
     */
    public static <T> void donloadExcel(
            String title,
            List<T> resultList,
            String fileName,
            String[] tableTitles,
            String[] titlesInfoKey,
            String subtitle,
            String[] desc,
            HttpServletResponse response

    ) {
        //1.参数校验
        if (CollectionUtils.isEmpty(resultList)
                || tableTitles.length <= NumberUtils.INTEGER_ZERO) {
            throw new RuntimeException("请求参数不正确！");
        }
        if (resultList.size() > 60000) {
            throw new RuntimeException("到处数据超过6w,不能导出！");
        }
        //2.创建Excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        //3.设置excel的 描述、标题、副标题、表头
        setExcelTitle(workbook, sheet, title, tableTitles, subtitle, desc);
        //4.写入数据
        HSSFCellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        for (int i = 0; i < resultList.size(); i++) {
            T clazz = resultList.get(i);
            insertDataToCell(sheet, clazz, titlesInfoKey, dataStyle);
        }
        //5.返回数据量
        File file = returnExcelDataStream(fileName, workbook);
        //6.导出
        buildResponseExcelFile(file, response);

    }

    /**
     * 下载文件流
     *
     * @param file
     * @param response
     */
    private static void buildResponseExcelFile(File file, HttpServletResponse response) {
        InputStream in = null;
        OutputStream out = null;

        try {
            in = new FileInputStream(file.getPath());
            response.reset();
            response.setHeader("Content-disposition", "attachment;filename=" + new String(file.getName().getBytes(), "iso-8859-1"));
            response.setContentType("application/octet-stream");
            response.addHeader("Context-Length", "" + file.length());
            response.setCharacterEncoding("utf-8");

            out = response.getOutputStream();
            int b;
            while ((b = in.read()) != -1) {
                out.write(b);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        file.delete();
    }

    /**
     * 返回文件流
     *
     * @param fileName
     * @param workbook
     * @return
     */
    private static File returnExcelDataStream(String fileName, HSSFWorkbook workbook) {

        File file = null;
        try {

            String tempExcelPath = null;
            if (StringUtils.isNotBlank(fileName)) {
                tempExcelPath = File.separator + fileName + ".xls";
            } else {
                tempExcelPath = File.separator + "report.xls";
            }
            file = new File(tempExcelPath);
            file.deleteOnExit();
            file.createNewFile();
            FileOutputStream fileOutputStream = FileUtils.openOutputStream(file);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return file;

    }


    /**
     * 通过反射获取字段值
     *
     * @param sheet
     * @param clazz
     * @param titlesInfoKey
     * @param <T>
     */
    private static <T> void insertDataToCell(HSSFSheet sheet, T clazz, String[] titlesInfoKey, HSSFCellStyle dataStyle) {
        HSSFRow row = sheet.createRow(rowNum);
        row.setHeight((short) (15 * 20));
        for (int j = 0; j < titlesInfoKey.length; j++) {
            HSSFCell cell = row.createCell(j);
            cell.setCellStyle(dataStyle);
            //反射获取对象的值
            Object fieldValue = getValueByReflect(titlesInfoKey[j], clazz);

            String cellResult = null;
            //时间类型 Date 转换为字符串
            if (fieldValue instanceof Date) {
                cellResult = getStringTime((Date) fieldValue);
            } else {
                cellResult = Objects.isNull(fieldValue) ? "" : String.valueOf(fieldValue);
            }

            cell.setCellValue(cellResult);
        }
        rowNum += NumberUtils.INTEGER_ONE;
    }


    /**
     * 设置excel的 描述、标题、副标题、表头
     *
     * @param workbook
     * @param sheet
     * @param title
     * @param tableTitles
     * @param subtitle
     * @param desc
     */
    private static void setExcelTitle(HSSFWorkbook workbook,
                                      HSSFSheet sheet,
                                      String title,
                                      String[] tableTitles,
                                      String subtitle,
                                      String[] desc) {
        //合并单元格的行数 desc描述的数组项数+(beginTime && endTime)+title标题
        //$A$1:$I$1" 的含义是 第1行的A列到第1行的I列合并
        //计算要合并的行数
        int cellMergedRegionNm = NumberUtils.INTEGER_ZERO;
        //描述的长度
        int descLength = NumberUtils.INTEGER_ZERO;
        //当前要创建的excel的行数
        rowNum = NumberUtils.INTEGER_ZERO;
        if (desc != null) {
            descLength = desc.length;
            cellMergedRegionNm += descLength;
            rowNum += descLength;
        }
        //统计时间栏
        if (StringUtils.isNotBlank(subtitle)) {
            cellMergedRegionNm += NumberUtils.INTEGER_ONE;
        }
        //标题栏
        if (StringUtils.isNotBlank(title)) {
            cellMergedRegionNm += NumberUtils.INTEGER_ONE;
        }
        //获取表头的数量(他决定了合并单元格的列数)
        int tableTitleLength = tableTitles.length;
        //获取表头的长度对应的字母
        String letter = getLetterByNum(tableTitleLength);
        //设置需合并的行数与列数(循环次数是行数 ,$A$1:$I$1中的A代表第一列，I代表列的最后一列)
        for (int i = 0; i < cellMergedRegionNm; i++) {
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (i + 1) + ":$" + letter + "$" + (i + 1)));
        }
        //设置颜色
        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex((short) 9, (byte) 240, (byte) 240, (byte) 240);
        palette.setColorAtIndex((short) 10, (byte) 255, (byte) 153, (byte) 102);
        palette.setColorAtIndex((short) 11, (byte) 100, (byte) 149, (byte) 137);
        palette.setColorAtIndex((short) 12, (byte) 176, (byte) 196, (byte) 222);
        //字体
        HSSFFont workbookFont = workbook.createFont();
        workbookFont.setFontName("仿宋");
        workbookFont.setFontHeightInPoints((short) 14);
        //字体
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontName("黑体");
        titleFont.setFontHeightInPoints((short) 18);
        titleFont.setColor(IndexedColors.WHITE.index);
        //单元格的风格1
        HSSFCellStyle descStyleLong = workbook.createCellStyle();
        descStyleLong.setFont(workbookFont);
        descStyleLong.setFillForegroundColor((short) 9);
        descStyleLong.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //单元格的风格2
        HSSFCellStyle descStyleShort = workbook.createCellStyle();
        descStyleShort.setFont(workbookFont);
        descStyleShort.setFillForegroundColor((short) 10);
        descStyleShort.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //单元格的风格3
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);
        titleStyle.setFillForegroundColor((short) 11);
        titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //描述:设置描述，没有就不传
        HSSFRow row = null;
        HSSFCell cell = null;
        if (descLength > 0) {
            for (int i = 0; i < descLength; i++) {
                row = sheet.createRow(i);
                row.setHeight((short) (23 * 20));
                cell = row.createCell(0);
                cell.setCellValue(desc[i]);
                if (i >= 4) {
                    row.setHeight((short) (40 * 20));
                    cell.setCellStyle(descStyleShort);
                } else {
                    cell.setCellStyle(descStyleLong);
                }
            }
        }

        //设置标题：没有就不传
        if (StringUtils.isNoneBlank(title)) {

            row = sheet.createRow(rowNum);
            row.setHeight((short