基于Apache的poi对导出Excel封装的Util àì夳堔傛蜴生んèń 2022-05-25 11:17 59阅读 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);
还没有评论，来说两句吧...