使用poi 创建Excel 保存到本地并下载_JAVA_编程开发_程序员俱乐部

项目中需要一个提供Excel数据展示下载功能，用于并不是list数据展示，无法通过for来实现。只能通过硬编码实现每一个cell。该Excel数据展示一共用到了四种样式，所以创建了4中style。

最终实现的效果如下：

?
??????? //查询payment，数据源。用于绑定数据到Excel
??????? RfPayment rfPayment=rfFinanceManager.findRfPaymentById(shopCode, paymentId);
??????? XSSFWorkbook workbook = null;
??????? byte[] bytes=null;
??????? try{
??????????? workbook = new XSSFWorkbook();
??????????? XSSFSheet sheet = workbook.createSheet("sheet名称");
??????????? //设置字体
??????????? XSSFFont font = workbook.createFont();
??????????? //字体大小
??????????? font.setFontHeightInPoints((short) 14);
??????????? //字体
??????????? font.setFontName("宋体");
??????????? //加粗
??????????? font.setBold(true);
??????????? //颜色
??????????? font.setColor(IndexedColors.BLACK.index);

??????????? /** 标题样式 */
??????????? XSSFCellStyle styleTitle = workbook.createCellStyle();
??????????? styleTitle.setFont(font);
??????????? //文字居中
??????????? styleTitle.setAlignment(HorizontalAlignment.CENTER);

??????????? /** 通用样式 */
??????????? XSSFCellStyle styleAuto = workbook.createCellStyle();
??????????? styleAuto.setFont(font);
??????????? //文字左对齐
??????????? styleAuto.setAlignment(HorizontalAlignment.LEFT);

??????????? /** 通用样式 带下划线 */
??????????? XSSFCellStyle styleAutoMedium = workbook.createCellStyle();
??????????? styleAutoMedium.setFont(font);
??????????? //文字左对齐
??????????? styleAutoMedium.setAlignment(HorizontalAlignment.LEFT);
??????????? //粗下划线
??????????? styleAutoMedium.setBorderBottom(BorderStyle.MEDIUM);

??????????? /** 通用样式 边框黑色 */
??????????? XSSFCellStyle styleAutoThin = workbook.createCellStyle();
??????????? styleAutoThin.setFont(font);
??????????? //文字左对齐
??????????? styleAutoThin.setAlignment(HorizontalAlignment.LEFT);
??????????? //粗下划线
??????????? styleAutoThin.setBorderTop(BorderStyle.THIN);
??????????? styleAutoThin.setBorderBottom(BorderStyle.THIN);
??????????? styleAutoThin.setBorderLeft(BorderStyle.THIN);
??????????? styleAutoThin.setBorderRight(BorderStyle.THIN);

??????????? /** 店铺名称样式 */
??????????? XSSFCellStyle styleShop = workbook.createCellStyle();
??????????? styleShop.setFont(font);
??????????? //文字左对齐
??????????? styleShop.setAlignment(HorizontalAlignment.LEFT);
??????????? //背景黄色
??????????? styleShop.setFillForegroundColor(IndexedColors.YELLOW.index);//前景颜色
??????????? styleShop.setFillPattern(FillPatternType.SOLID_FOREGROUND);//填充方式，前色填充

??????????? //第一行第一列为标题
??????????? //第一行第一列到第四列合并
??????????? sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
??????????? XSSFRow row = sheet.createRow(0);
??????????? XSSFCell cell = row.createCell(0);
??????????? cell.setCellValue("网银退款申请书");
??????????? cell.setCellStyle(styleTitle);

??????????? row = sheet.createRow(1);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("部门:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? // 店铺名称
??????????? SalesEntity salesEntityParam = new SalesEntity();
??????????? salesEntityParam.setCode(rfPayment.getShopCode());
??????????? List<SalesEntity> salesEntities = salesEntityManager.findListByParam(salesEntityParam);
??????????? cell.setCellValue(!CollectionUtils.isEmpty(salesEntities) ? salesEntities.iterator().next().getName() : rfPayment.getShopCode());
??????????? cell.setCellStyle(styleShop);

??????????? cell = row.createCell(2);
??????????? cell.setCellValue("费用代码:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(3);
??????????? cell.setCellValue("");
??????????? cell.setCellStyle(styleAutoMedium);

??????????? row = sheet.createRow(2);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("退款单创建日期:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getRefundCreateTimeString().substring(0, 10));
??????????? cell.setCellStyle(styleAutoMedium);

??????????? row = sheet.createRow(3);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("导出日期:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(DateFormatHelper.dateToStr(new Date(), "yyyy-MM-dd"));
??????????? cell.setCellStyle(styleAutoMedium);

??????????? row = sheet.createRow(4);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("姓名:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(userName);//
??????????? cell.setCellStyle(styleAutoMedium);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("员工代码:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(3);
??????????? cell.setCellValue(jobNumber);//
??????????? cell.setCellStyle(styleAutoMedium);

??????????? row = sheet.createRow(6);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("付款对象:");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getPayee());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(7);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("退款申请编码：");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getRfDocNo());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("付款原由：");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellValue(sysConfigInit.getSysConfigValue(SysConfigConstants.RF_TYPE, rfPayment.getRefundCategory()));
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(8);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("OMS订单号：");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getOrderDocNo());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("相关退货单号：");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellValue(rfPayment.getRefundCategory().equals(Constants.STRING_NUMBER_3)?"":rfPayment.getRoDocNo());
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(9);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("平台订单编号：");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getPfDocNo());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(10);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue("支付金额(元)");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellValue("备注");
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(11);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("退款单总金额");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(MoneryTransition.number2CNMontrayUnit(rfPayment.getPayTotal()));
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("￥"+rfPayment.getPayTotal().setScale(2, BigDecimal.ROUND_FLOOR).toString());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellValue("");
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(12);
??????????? cell = row.createCell(0);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(13);
??????????? cell = row.createCell(0);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(14);
??????????? cell = row.createCell(0);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(15);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("银行转帐(户名)");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getPayee());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(16);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("(银行帐号)");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getAccount());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(17);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("(银行名称)");
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getBank());
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(2);
??????????? cell.setCellStyle(styleAutoThin);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoThin);

??????????? row = sheet.createRow(19);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("申请人:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? cell.setCellValue(rfPayment.getRefundCreateUser().equals(Long.valueOf("-666"))?"系统创建":omsUserManager.findUserById(Long.valueOf(rfPayment.getRefundCreateUser())).getUserName());
??????????? cell.setCellStyle(styleAutoMedium);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("日期:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(3);
??????????? cell.setCellValue(rfPayment.getRefundCreateTimeString().substring(0, 10));
??????????? cell.setCellStyle(styleAutoMedium);

??????????? row = sheet.createRow(20);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("部门主管:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? cell.setCellStyle(styleAutoMedium);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("日期:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoMedium);

??????????? row = sheet.createRow(21);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("财务总监:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? cell.setCellStyle(styleAutoMedium);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("日期:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoMedium);

??????????? row = sheet.createRow(22);
??????????? cell = row.createCell(0);
??????????? cell.setCellValue("总经理:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(1);
??????????? cell.setCellStyle(styleAutoMedium);
??????????? cell = row.createCell(2);
??????????? cell.setCellValue("日期:");
??????????? cell.setCellStyle(styleAuto);
??????????? cell = row.createCell(3);
??????????? cell.setCellStyle(styleAutoMedium);

??????????? for (int i = 0; i < 4; i++){
??????????????? //宽度自适应
??????????????? sheet.autoSizeColumn(i);
??????????? }
???????????
??????????? ByteArrayOutputStream os = new ByteArrayOutputStream();
??????????? workbook.write(os);
??????????? bytes = os.toByteArray();
??????????? //保存文件到服务器 ，由于不需要保存到本地，所以注释这段了
??????????? //??????? File file = new File("D://" + System.currentTimeMillis() + ".xlsx");
??????????? //??????? FileOutputStream out = new FileOutputStream(file);
??????????? //??????? workbook.write(out);
??????????? //??????? out.close();
??????? }catch (EncryptedDocumentException e){
??????????? // TODO Auto-generated catch block
??????????? e.printStackTrace();
??????? }catch (IOException e){
??????????? // TODO Auto-generated catch block
??????????? e.printStackTrace();
??????? }catch (Exception e){
??????????? e.printStackTrace();
??????????? String uuid = UUID.randomUUID().toString();
??????????? logger.error(String.format("导出退款申请书", uuid), e.getMessage());
??????????? throw new RuntimeException(String.format("退款支出明细编辑", uuid), e);
??????? }finally {
??????????? try{
??????????????? workbook.close();
??????????? }catch (IOException e){
??????????????? // TODO Auto-generated catch block
??????????????? e.printStackTrace();
??????????? }
??????? }
??????? //记录导出次数
??????? rfPaymentExportSchemesManager.insertExportNum(rfPayment);

? ? ? ? //下载文件
? ? ? ? String fileNmae = System.currentTimeMillis() + ".xlsx";

? ? ? ? HttpHeaders headers = new HttpHeaders();

? ? ? ? headers.setContentDispositionFormData("attachment", new String(fileNmae.getBytes("UTF-8"), "iso-8859-1"));

? ? ? ? headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

? ? ? ? ResponseEntity<byte[]> entity = new ResponseEntity<byte[]>(bytes, headers, HttpStatus.CREATED);

? ? ? ? return entity;

? ?poi 的jar包使用的是。3.12版本

?

• class='magplus' title='点击查看原始大小图片' />
• 大小: 26 KB

• 查看图片附件