您的位置: 首页  >  文章  >  使用poi 创建Excel 保存到本地并下载

使用poi 创建Excel 保存到本地并下载

分类: 文章 • 2024-07-17 10:19:58

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

最终实现的效果如下：


使用poi 创建Excel 保存到本地并下载
 
        //查询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版本

 

相关推荐