POI 导出Excel Java版

报表导出编码思路解析：

1. 按照一定查询条件，将满足条件的记录查询出来
2. 创建workbook，并命名，如sheet1
3. 使用增强for循环逐一遍历对象，先设置好表头，接着按照对象的字段创建单元格Cell并完成赋值操作
4. 使用HttpServletRespone对象，获取输出流 OutputStream out = response.getOutputStream(); 而这也就意味着Controller层对应的方法是不需要返回值的，即返回值定义为 void
5. 给Excel文件命名
6. 调用 workbook.write(out) 将报表输出客户端。

先引入Jar依赖（项目是Spring Boot + Gradle，使用gradle语法引入依赖）：

compile group: 'org.apache.poi', name: 'poi', version: '3.17'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17'

接下来，展示数据导出的整个流程：

/**
     * @return void
     * @throws
     * @description 导出库存点报备数据列表
     * @params [request, response]
     */
    @Override
    public void exportStockReportData(StockReportSearchRequest request, HttpServletResponse response) {

        // 属性名称数据集
        List<String> fieldNames = Arrays.asList("id", "dealerName", "address", "createdAt", "updatedAt");

        // 组装经销商id列表
        assembleDealerInfoIds(request);

        // 获取库存点报备数据列表
        List<StockReportResponse> stockReportResList = adminStockReportDAO.listStockReport(request);

        try {
            // 自定义Excel文件名 - 兼容".xlsx"格式
            String fileName = "stock-reports";
            String fileNamePlusSuffix = fileName.concat(".xls");
            Workbook workbook = POIUtil.createWorkbook(fileNamePlusSuffix);

            if (CollectionUtil.isEmpty(stockReportResList)) {

                logger.info("AdminStockReportServiceImpl.exportStockReportData ========== 查无库存点报备数据 ========== ");
                // 导出 空Excel文件
                POIUtil.writeDataListToFile(workbook, stockReportResList, fieldNames);
            } else {
                // 关联属性值的数据组装
                assembleAssociateFields(stockReportResList);

                List<StockReportExportResponse> stockReportExportResList = new ArrayList<>();

                for (StockReportResponse stockReportInfo : stockReportResList) {

                    if (Objects.isNull(stockReportInfo)) continue;
                    StockReportExportResponse stockReportExportInfo = new StockReportExportResponse();
                    BeanUtils.copyProperties(stockReportInfo, stockReportExportInfo);
                    // 数据类型转换
                    stockReportExportInfo.setCreatedAt(DateTimeUtil.getDateFormat(stockReportInfo.getCreatedAt()));
                    stockReportExportInfo.setUpdatedAt(DateTimeUtil.getDateFormat(stockReportInfo.getUpdatedAt()));
                    stockReportExportResList.add(stockReportExportInfo);
                }

                // 导出 携带数据的Excel文件
                POIUtil.writeDataListToFile(workbook, stockReportExportResList, fieldNames);
            }

            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileNamePlusSuffix);
            OutputStream out = response.getOutputStream();

            workbook.write(out);
            out.flush();
            out.close();

        } catch (Exception e) {
            logger.error("AdminStockReportServiceImpl.exportStockReportData ========== 库存点报备报表导出异常：[{}] ========== ", e.getMessage());
            logger.error("AdminStockReportServiceImpl.exportStockReportData ========== 完整的异常信息：[{}] ========== ", e);
        }
    }

/**
 * @Description POI工具类
 * @Author blake
 * @Date 2019-01-25 14:51
 * @Version 1.0
 */
public class POIUtil {

    private static final Logger logger = LoggerFactory.getLogger(POIUtil.class);

    /**
     * @return void
     * @throws
     * @description 填充workbook工作表
     * @params [workbook, dataList, fieldNames]
     */
    public static <T> void writeDataListToFile(Workbook workbook, List<T> dataList, List<String> fieldNames) {

        // 新建sheet并给予命名
        Sheet sheet = workbook.createSheet("sheet1");
        // 组装workbook工作表的数据行
        assembleRowDataInWorkbook(dataList, fieldNames, sheet);
    }

    /**
     * @return org.apache.poi.ss.usermodel.Workbook
     * @throws
     * @description 创建workbook工作表
     * @params [fileNamePlusSuffix]
     */
    public static Workbook createWorkbook(String fileNamePlusSuffix) throws Exception {

        // 校验Excel文件格式，并按需创建workbook实例
        return checkFileTypeOfExcel(fileNamePlusSuffix);
    }

    /**
     * @return void
     * @throws
     * @description 组装workbook工作表的数据行
     * @params [dataList, fieldNames, sheet]
     */
    private static <T> void assembleRowDataInWorkbook(List<T> dataList, List<String> fieldNames, Sheet sheet) {

        // 绕过Java反射机制，通过JSON attr 实现Bean对象的属性填充
        for (int dataIndex = 0; dataIndex <= dataList.size(); dataIndex++) {

            Row row = sheet.createRow(dataIndex);
            JSONObject jsonObj = null;

            if (dataIndex != 0) {
                // Bean => JSONObject
                jsonObj = JSONObject.fromObject(dataList.get(dataIndex - 1));
            }

            for (int fieldIndex = 0; fieldIndex < fieldNames.size(); fieldIndex++) {

                // 第一列单元格
                Cell cell = row.createCell(fieldIndex);

                // 属性名称
                String fieldName = fieldNames.get(fieldIndex);

                // 第一行
                if (dataIndex == 0) {
                    // 表头文案
                    cell.setCellValue(fieldName);
                } else {
                    // 将bean对象转换成JSON对象
                    String content = jsonObj.getString(fieldName);
                    if (content == null) {
                        content = "";
                    }
                    cell.setCellValue(content);
                }
            }
        }
    }

    /**
     * @return org.apache.poi.ss.usermodel.Workbook
     * @throws
     * @description 校验Excel文件格式
     * @params [fileNamePlusSuffix]
     */
    private static Workbook checkFileTypeOfExcel(String fileNamePlusSuffix) throws Exception {

        Workbook workbook;

        if (fileNamePlusSuffix.endsWith("xlsx")) {
            logger.info("POIUtil.checkFileTypeOfExcel ============ 当前Excel文件为2007版本 ============");
            workbook = new XSSFWorkbook();
        } else if (fileNamePlusSuffix.endsWith("xls")) {
            logger.info("POIUtil.checkFileTypeOfExcel ============ 当前Excel文件为2003版本 ============");
            workbook = new HSSFWorkbook();
        } else {
            throw new Exception("invalid file name, should be xls or xlsx");
        }
        return workbook;
    }

}