poi导出案例

poi导出案例

  1. 一般导出(不合并单元格)
public void downloadOrdersUnSettlement(HttpServletResponse response, List<OrderSalesRecords> dataList) {
        // 用于金额保留两位小数的格式转换
        DecimalFormat df = new DecimalFormat("#.00");
        String path = "tempfile" + File.separator + "temp-unSettlement.xlsx";
        XSSFWorkbook book = null;
        OutputStream out = null;
        String fpath = "tempfile" + File.separator + UUID.randomUUID().toString() + ".xlsx";
        File newFile = new File(fpath);
        try {
            File file = new File(path);
            Files.copy(file.toPath(), newFile.toPath());
            book = new XSSFWorkbook(new FileInputStream(newFile));
            XSSFSheet sheet = book.getSheetAt(0);
            int lastRow = sheet.getLastRowNum();
            for (int i = 1; i <= lastRow; i++) {
                sheet.removeRow(sheet.getRow(i));
            }
            if (MyListUtils.isNotEmpty(dataList)) {
                int rowNum = 1;
                for (OrderSalesRecords e : dataList) {
                    XSSFRow row = sheet.createRow(rowNum++);
                    if (e.getOrderNumber() != null) {
                        row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(e.getOrderNumber());
                    }
                    if (e.getOrderCtime() != null) {
                        row.createCell(1, Cell.CELL_TYPE_STRING).setCellValue(e.getOrderCtime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                    }
                    if (e.getSalesAmount() != null) {
                        Double value = e.getSalesAmount() / 100.0;
                        Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                        row.createCell(2, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                    }
                    if (e.getDiscount() != null) {
                        Double value = e.getDiscount() / 100.0;
                        Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                        row.createCell(3, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                    }
                    if (e.getServiceFee() != null) {
                        Double value = e.getServiceFee() / 100.0;
                        Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                        row.createCell(4, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                    }
                    if (e.getDistrib() != null) {
                        Double value = e.getDistrib() / 100.0;
                        Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                        row.createCell(5, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                    }
                    if (e.getSettlementAmount() != null) {
                        Double value = e.getSettlementAmount() / 100.0;
                        Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                        row.createCell(6, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                    }
                }
            }
                out = response.getOutputStream();
                response.reset();
                response.setHeader("Content-disposition", "attachment; filename=orders.xlsx");
                response.setContentType("application/vnd.ms-excel");
                book.write(out);
                out.close();
        } catch (Exception e) {
            log.error("导出文件异常", e);
        } finally {
            try {
                if (null != out)
                    out.close();
                if (null != newFile)
                    newFile.delete();
            } catch (IOException e1) {
            }
        }
    }

2 合并单元格导出

/**
     * 微信流水导出
     *
     * @param response
     * @param dataList
     */
    public void downloadFlowExcel(HttpServletResponse response, List<FlowParams> dataList) {
        // 用于金额保留两位小数的格式转换
        DecimalFormat df = new DecimalFormat("#.00");
        String path = "tempfile" + File.separator + "temp-flow.xlsx";
        XSSFWorkbook book = null;
        OutputStream out = null;
        String fpath = "tempfile" + File.separator + UUID.randomUUID().toString() + ".xlsx";
        File newFile = new File(fpath);
        try {
            File file = new File(path);
            Files.copy(file.toPath(), newFile.toPath());
            book = new XSSFWorkbook(new FileInputStream(newFile));
            XSSFSheet sheet = book.getSheetAt(0);
            int lastRow = sheet.getLastRowNum();
            for (int i = 1; i <= lastRow; i++) {
                sheet.removeRow(sheet.getRow(i));
            }
            if (MyListUtils.isNotEmpty(dataList)) {
                int rowNum = 1;
                for (FlowParams e : dataList) {
                    if (e.getOrders().size() > 0) {
                        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 0, 0));
                        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 1, 1));
                        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 2, 2));
                        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 3, 3));
                        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 4, 4));
                        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 5, 5));
                        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 6, 6));
                    }
                    XSSFRow row = sheet.createRow(rowNum++);
                    if (MyStringUtils.isNotEmpty(e.getFlowNumber())) {
                        row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(e.getFlowNumber());
                    }
                    if (e.getPayTime() != null) {
                        row.createCell(1, Cell.CELL_TYPE_STRING).setCellValue(e.getPayTime().toString().replace("T", " "));
                    }
                    if (e.getRefundTime() != null) {
                        row.createCell(2, Cell.CELL_TYPE_STRING).setCellValue(e.getRefundTime().replaceAll("T", " "));
                    }
                    if (MyStringUtils.isNotEmpty(e.getInAmount())) {
                        Double value = Double.valueOf(e.getInAmount());
                        Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                        row.createCell(3, Cell.CELL_TYPE_STRING).setCellValue(last);
                    } else {
                        row.createCell(3, Cell.CELL_TYPE_STRING).setCellValue(0);
                    }
                    if (MyStringUtils.isNotEmpty(e.getOutAmount())) {
                        Double value = Double.valueOf(e.getOutAmount());
                        Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                        row.createCell(4, Cell.CELL_TYPE_STRING).setCellValue(last);
                    } else {
                        row.createCell(4, Cell.CELL_TYPE_STRING).setCellValue(0);
                    }
                    if (MyStringUtils.isNotEmpty(e.getServiceFee())) {
                        row.createCell(5, Cell.CELL_TYPE_NUMERIC).setCellValue(e.getServiceFee());
                    } else {
                        row.createCell(5, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
                    }
                    if (MyStringUtils.isNotEmpty(e.getWechatOrderId()))
                        row.createCell(6, Cell.CELL_TYPE_NUMERIC).setCellValue(e.getWechatOrderId());
                    for (int i = 0; i < e.getOrders().size(); i++) {
                        Orders o = e.getOrders().get(i);
                        row.createCell(7, Cell.CELL_TYPE_STRING).setCellValue(o.getOrderNumber());
                        row.createCell(8, Cell.CELL_TYPE_STRING).setCellValue(o.getMerchantCode());
                        if (MyStringUtils.isNotEmpty(o.getShopName())) {
                            row.createCell(9, Cell.CELL_TYPE_STRING).setCellValue(o.getShopName());
                        } else {
                            row.createCell(9, Cell.CELL_TYPE_STRING).setCellValue(o.getShopName());
                        }
                        if (MyStringUtils.isNotEmpty(o.getUserName())) {
                            row.createCell(10, Cell.CELL_TYPE_STRING).setCellValue(o.getUserName());
                        } else {
                            row.createCell(10, Cell.CELL_TYPE_STRING).setCellValue(o.getUserName());
                        }
                        if (null != o.getTurnover()) {
                            Double value = o.getTurnover() / 100.0;
                            Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                            row.createCell(11, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                        }
                        if (null != o.getTotal()) {
                            Double value = o.getTotal() / 100.0;
                            Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                            row.createCell(12, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                        }
                        if (o.getPayRefundePrice() != null) {
                            Double value = o.getPayRefundePrice() / 100.0;
                            Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                            row.createCell(13, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                        } else {
                            row.createCell(13, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
                        }
                        if (1 == 1) {
                            String value = (o.getIsAfterSale() != null && o.getIsAfterSale() == 1) ? "售后订单" : "支付订单";
                            row.createCell(14, Cell.CELL_TYPE_STRING).setCellValue(value);
                        }
                        if (MyStringUtils.isNotEmpty(o.getRemark()))
                            row.createCell(15, Cell.CELL_TYPE_STRING).setCellValue(o.getRemark());
                        if (o.getStatus() != null)
                            row.createCell(16, Cell.CELL_TYPE_STRING).setCellValue(OrderStatus.getValueByCode(o.getStatus()));
                        if (1 == 1) {
                            Double value = (o.getDistrib() == null ? 0 : o.getDistrib()) / 100.0;
                            Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                            row.createCell(17, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                        }
                        if (1 == 1) {
                            Double value = (o.getServiceFee() == null ? 0 : o.getServiceFee()) / 100.0;
                            Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                            row.createCell(18, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                        }
                        if (1 == 1) {
                            int turnover = o.getTurnover() == null ? 0 : o.getTurnover();
                            int serviceFee = o.getServiceFee() == null ? 0 : o.getServiceFee();
                            int distrib = o.getDistrib() == null ? 0 : o.getDistrib();
                            Double value = (turnover - serviceFee - distrib) / 100.0;
                            Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
                            row.createCell(19, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
                        }
                        if (1 == 1) {
                            row.createCell(20, Cell.CELL_TYPE_STRING).setCellValue(o.getAccountType() == null ? "-" : o.getAccountType());
                        }
                        if (i < e.getOrders().size() - 1) {
                            row = sheet.createRow(rowNum++);
                        }
                    }
                }
            }
            out = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=temp-order-settlement.xlsx");
            response.setContentType("application/vnd.ms-excel");
            book.write(out);
            out.close();
        } catch (Exception e) {
            log.error("导出文件异常", e);
        } finally {
            try {
                if (null != out)
                    out.close();
                if (null != newFile)
                    newFile.delete();
            } catch (IOException e1) {
            }
        }
    }
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值