基于Apache的poi对导出Excel封装的Util

àì夳堔傛蜴生んèń 2022-05-25 11:17 125阅读 0赞

主要利用了Java反射，导出时不需要在关注单元格、单元格对应的描述、以及单元格的值之间的对应关系。  
util方法如下：

/** * 导出发票信息到excel * * @param dataList 发票信息list * @param response * @param title 工作表标题 * @param columns 单元格标题 >> columnName:columnProperty */
        public static void exportTradeInvoiceExcel(List<? extends Object> dataList,
                                                   HttpServletResponse response, String title, String[] columns) throws PayPlatformException {
            try {
                // 创建工作簿对象
                HSSFWorkbook workbook = new HSSFWorkbook();
                // 创建工作表
                HSSFSheet sheet = workbook.createSheet(title);
    
                // 产生表格标题行
                HSSFRow rowColumn = sheet.createRow(0);
                HSSFCell cellTitle = rowColumn.createCell(0);
    
                //设置标题和单元格样式
                //获取列头样式对象
                HSSFCellStyle columnTopStyle = createColumnTopCellStyle(workbook);
    
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columns.length - 1)));
                cellTitle.setCellStyle(columnTopStyle);
                cellTitle.setCellValue(title);
    
                // 定义所需列数
                int columnNum = columns.length;
                // 在索引2的位置创建行(最顶端的行开始的第二行)
                HSSFRow rowRowName = sheet.createRow(2);
    
                // 将列头设置到sheet的单元格中
                for (int n = 0; n < columnNum; n++) {
                    //创建列头对应个数的单元格
                    HSSFCell cellRowName = rowRowName.createCell(n);
                    //设置列头单元格的数据类型
                    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                    String columnName = "";
                    if (StringUtils.isNotBlank(columns[n])) {
                        String[] columnSplit = columns[n].split(":");
                        columnName = columnSplit[0];
                    }
                    HSSFRichTextString text = new HSSFRichTextString(columnName);
                    //设置列头单元格的值
                    cellRowName.setCellValue(text);
                    //设置列头单元格样式
                    cellRowName.setCellStyle(columnTopStyle);
                }
    
                //将查询出的数据设置到sheet对应的单元格中
                for (int i = 0; i < dataList.size(); i++) {
                    Object obj = dataList.get(i);
                    //创建所需的行数
                    HSSFRow row = sheet.createRow(i + 3);
                    for (int j = 0; j < columnNum; j++) {
    
                        String filedName = "";
                        if (StringUtils.isNotBlank(columns[j])) {
                            String[] columnSplit = columns[j].split(":");
                            filedName = columnSplit[1];
                        }
                        if (StringUtils.isNotBlank(filedName)) {
                        // 获取类中的属性。
                            Field field = obj.getClass().getDeclaredField(filedName);
                            field.setAccessible(true);
                            //设置单元格的数据类型
                            setHSSFCell(field, obj, row, j,workbook);
                        }
                    }
    
                }
                if (workbook != null) {
                    String fileName = title + ".xls";
                    String headStr = "attachment; filename=\"" + fileName + "\"";
                    response.setContentType("APPLICATION/OCTET-STREAM");
                    response.setHeader("Content-Disposition", headStr);
                    ServletOutputStream out = response.getOutputStream();
                    workbook.write(out);
                    out.close();
                    workbook.flush();
                }
            } catch (Exception e) {
                log.warn("exportUserTradeInvoiceExcel fail ,errMsg:{}", JSON.toJSONString(e));
                throw new PayPlatformException(StatusEnum.SERVICE_ERROR, "导出excel失败!");
            }
        }
    
        private static void setHSSFCell(Field field, Object object, HSSFRow row, int j,HSSFWorkbook workbook) throws Exception {
            HSSFCell cell = null;
            //单元格样式对象
            HSSFCellStyle style = ctrateStyle(workbook);
    
            String genericType = field.getGenericType().toString();
            // 获取每个属性的getter方法
            Method m = object.getClass().getMethod(String.format("%s%s", "get", StringUtils.capitalize(field.getName())));
            // 如果类型是String
            if (Objects.equals(STRING_TYPE.type(), genericType)) {
                // 拿到该属性的getter方法
                /** * 这里需要说明一下：他是根据拼凑的字符来找你写的getter方法的 * 在Boolean值的时候是isXXX（默认使用ide生成getter的都是isXXX） * 如果出现NoSuchMethod异常 就说明它找不到那个getter方法 需要做个规范 */
    
                // 调用getter方法获取属性值
                String val = (String) m.invoke(object);
                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(val);
                //设置单元格样式
                cell.setCellStyle(style);
            }
    
            // 如果类型是Integer
            if (Objects.equals(genericType, INTEGER_TYPE.type())) {
                Integer val = (Integer) m.invoke(object);
                cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(val);
                //设置单元格样式
                cell.setCellStyle(style);
            }
    
            // 如果类型是Double
            if (Objects.equals(DOUBLE_TYPE.type(), genericType)) {
                cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                Double val = (Double) m.invoke(object);
                cell.setCellValue(val);
                HSSFDataFormat dataFormat  = workbook.createDataFormat();
                style.setDataFormat(dataFormat.getFormat("¥#,##0.00"));
                //设置单元格样式
                cell.setCellStyle(style);
            }
    
            // 如果类型是Short
            if (Objects.equals(SHORT_TYPE.type(), genericType)) {
                Short val = (Short) m.invoke(object);
                cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(val);
                //设置单元格样式
                cell.setCellStyle(style);
            }
        }

然后只需要把单元格描述与单元格的值对应起来即可：

List<ExportTradeInvoiceDetailVO> detailVOS = this.exportExcel(tradeInvoiceBO);
    
    // 单元格描述与取值对应起来
            String[] columns = {
       "创建时间:createTime","账单月份:tradeTime","发票抬头:invoiceTitle","发票税号:taxRegistrationNo",
                    "金额:fund","付款账户:payerAccountName","账户类型:personShow","状态:statusShow","审核意见:auditOpinion"};
    
            String startDate = DateFormatUtils.format(tradeInvoiceBO.getInvoiceStartDate(),DAY_CHINSE_FORMAT);
            String endDate = DateFormatUtils.format(tradeInvoiceBO.getInvoiceEndDate(),DAY_CHINSE_FORMAT);
    
            StringBuffer title = new StringBuffer(startDate);
            if (!Objects.equals(startDate,endDate)){
                title.append("-").append(endDate);
            }
    
            if (Objects.equals(-1,invoiceRequest.getInvoiceStatus())){
                title.append(TradeInvoiceStatusEnum.getMessageByCode(invoiceRequest.getInvoiceStatus()));
            }
            title.append("发票信息");
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-download");
            ExcelUtils.exportTradeInvoiceExcel(detailVOS,response,title.toString(),columns);