Java poi导出excel代码实战

damoneric_guo

于 2022-05-17 15:39:37 发布

阅读量246

点赞数 1

分类专栏： java总结实用文章标签： java 开发语言

本文链接：https://blog.csdn.net/damoneric_guo/article/details/124822654

版权

java总结实用专栏收录该内容

38 篇文章 0 订阅

订阅专栏

1、Controller层

    @ApiOperation(value = "导出Excel")
    @GetMapping("purchaseOrderSubExport")
    public void purchaseOrderSubExport(HttpServletRequest request, HttpServletResponse response, PurchaseOrderPageParam param) {
        wmsPurchaseOrderService.purchaseOrderSubExport(request, response, param);
    }

2、Service层

    /**
     * 导出Excel
     *
     * @param request
     * @param response
     * @param param
     */
    void purchaseOrderSubExport(HttpServletRequest request, HttpServletResponse response, PurchaseOrderPageParam param);

3、ServiceImpl层

    @Override
    public void purchaseOrderSubExport(HttpServletRequest request, HttpServletResponse response, PurchaseOrderPageParam param) {

        // 查询需要导出的数据（业务逻辑查询）
        List<SpdPurchaseOrderSubVO> purchaseOrderSubVOList = subMapper.selectPurchaseOrderSubByOrderCodes(orderCodes);
        if (CollectionUtils.isEmpty(purchaseOrderSubVOList)) {
            return;
        }


        // 导出excel逻辑处理
        buildExcel(response, purchaseOrderSubVOList);
    }

 /**
     * 导出excel逻辑处理
     *
     * @param response
     * @param purchaseOrderSubVOList
     */
    private void buildExcel(HttpServletResponse response, List<SpdPurchaseOrderSubVO> purchaseOrderSubVOList) {
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook();
            Sheet sheet = workbook.createSheet();
            sheet.setColumnWidth(0, 1500);
            sheet.setColumnWidth(1, 6000);
            sheet.setColumnWidth(2, 6000);
            sheet.setColumnWidth(3, 6000);
            sheet.setColumnWidth(4, 6000);
            sheet.setColumnWidth(5, 3000);
            sheet.setColumnWidth(6, 3000);
            sheet.setColumnWidth(7, 4000);
            sheet.setColumnWidth(8, 8000);
            sheet.setColumnWidth(9, 3000);
            sheet.setColumnWidth(10, 8000);
            sheet.setColumnWidth(11, 3000);
            sheet.setColumnWidth(12, 3000);

            // 设置标题(第一行)内容
            Row row_0 = sheet.createRow(0);
            Cell cell_0 = row_0.createCell(0);
            cell_0.setCellValue("常州四院医用耗材采购计划单");
            CellRangeAddress cellAddresses_0 = new CellRangeAddress(0, 0, 0, 12);
            sheet.addMergedRegion(cellAddresses_0);

            // 设置标题(第一行)样式
            HSSFCellStyle cellStyle_0 = workbook.createCellStyle();
            HSSFFont font_0 = workbook.createFont();
            cellStyle_0.setAlignment(HorizontalAlignment.CENTER);
            cellStyle_0.setVerticalAlignment(VerticalAlignment.CENTER);
            font_0.setFontName("楷体");
            font_0.setFontHeightInPoints((short) 18);
            font_0.setBold(true);
            cellStyle_0.setFont(font_0);
            cell_0.setCellStyle(cellStyle_0);

            // 设置第二行内容
            Row row_1 = sheet.createRow(1);
            Date now = new Date();
            String nowString = "日期：" + new SimpleDateFormat("yyyy年MM月dd日").format(now);
            Cell cell_1 = row_1.createCell(0);
            cell_1.setCellValue(nowString);
            CellRangeAddress cellAddresses_1 = new CellRangeAddress(1, 1, 0, 12);
            sheet.addMergedRegion(cellAddresses_1);

            // 设置第二行样式
            HSSFCellStyle cellStyle_1 = workbook.createCellStyle();
            cellStyle_1.setAlignment(HorizontalAlignment.CENTER);
            cellStyle_1.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont font_1 = workbook.createFont();
            font_1.setFontName("楷体");
            font_1.setFontHeightInPoints((short) 14);
            font_1.setBold(true);
            cellStyle_1.setFont(font_1);
            cell_1.setCellStyle(cellStyle_1);

            // 设置第三行内容和样式
            HSSFCellStyle default_style = workbook.createCellStyle();
            HSSFFont default_font = workbook.createFont();
            default_font.setFontName("宋体");
            default_font.setFontHeightInPoints((short) 11);
            default_font.setBold(true);
            default_style.setFont(default_font);
            default_style.setWrapText(true);

            Row row_2 = sheet.createRow(2);
            Cell cell_10 = row_2.createCell(0);
            cell_10.setCellValue("编号");
            cell_10.setCellStyle(default_style);
            Cell cell_11 = row_2.createCell(1);
            cell_11.setCellValue("物资名称");
            cell_11.setCellStyle(default_style);
            Cell cell_12 = row_2.createCell(2);
            cell_12.setCellValue("中标编码");
            cell_12.setCellStyle(default_style);
            Cell cell_13 = row_2.createCell(3);
            cell_13.setCellValue("规格");
            cell_13.setCellStyle(default_style);
            Cell cell_14 = row_2.createCell(4);
            cell_14.setCellValue("型号");
            cell_14.setCellStyle(default_style);
            Cell cell_15 = row_2.createCell(5);
            cell_15.setCellValue("计量单位");
            cell_15.setCellStyle(default_style);
            Cell cell_16 = row_2.createCell(6);
            cell_16.setCellValue("数量");
            cell_16.setCellStyle(default_style);
            Cell cell_17 = row_2.createCell(7);
            cell_17.setCellValue("采购单价");
            cell_17.setCellStyle(default_style);
            Cell cell_18 = row_2.createCell(8);
            cell_18.setCellValue("生产企业");
            cell_18.setCellStyle(default_style);
            Cell cell_19 = row_2.createCell(9);
            cell_19.setCellValue("需采购部门");
            cell_19.setCellStyle(default_style);
            Cell cell_20 = row_2.createCell(10);
            cell_20.setCellValue("配送单位");
            cell_20.setCellStyle(default_style);
            Cell cell_21 = row_2.createCell(11);
            cell_21.setCellValue("备注");
            cell_21.setCellStyle(default_style);
            Cell cell_22 = row_2.createCell(12);
            cell_22.setCellValue("原中标编码");
            cell_22.setCellStyle(default_style);

            row_2.setHeight((short) 300);
            row_2.setRowStyle(default_style);

            if (CollectionUtils.isEmpty(purchaseOrderSubVOList)) {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("采购单.xls", "UTF-8"));
                workbook.write(response.getOutputStream());
            }

            int row_index = 2;
            // 写入内容
            for (int k = 0; k < purchaseOrderSubVOList.size(); k++) {
                row_index++;
                Row row_1k = sheet.createRow(row_index);
                // style
                row_1k.setRowStyle(default_style);
                SpdPurchaseOrderSubVO purchaseOrderSubVO = purchaseOrderSubVOList.get(k);
                // 编号
                Cell cell_k0 = row_1k.createCell(0);
                cell_k0.setCellValue(k + 1);
                // 物资名称
                Cell cell_k1 = row_1k.createCell(1);
                cell_k1.setCellValue(purchaseOrderSubVO.getCommodityName());
                // 中标编码
                Cell cell_k2 = row_1k.createCell(2);
                cell_k2.setCellValue(purchaseOrderSubVO.getWinCode());
                // 规格
                Cell cell_k3 = row_1k.createCell(3);
                cell_k3.setCellValue(purchaseOrderSubVO.getCommoditySpec());
                // 型号
                Cell cell_k4 = row_1k.createCell(4);
                cell_k4.setCellValue(purchaseOrderSubVO.getCommodityModel());
                // 计量单位
                Cell cell_k5 = row_1k.createCell(5);
                cell_k5.setCellValue(purchaseOrderSubVO.getPurchasePackageUnitValue());
                // 数量
                Cell cell_k6 = row_1k.createCell(6);
                if (Objects.nonNull(purchaseOrderSubVO.getBaseNumber())) {
                    cell_k6.setCellValue(purchaseOrderSubVO.getBaseNumber().stripTrailingZeros().toPlainString());
                }
                // 采购单价
                Cell cell_k7 = row_1k.createCell(7);
                cell_k7.setCellValue(purchaseOrderSubVO.getPriceValue());
                // 生产企业
                Cell cell_k8 = row_1k.createCell(8);
                cell_k8.setCellValue(purchaseOrderSubVO.getManufacturerName());
                // 需采购部门
                Cell cell_k9 = row_1k.createCell(9);
                cell_k9.setCellValue("");
                // 配送单位（供应商名称）
                Cell cell_k10 = row_1k.createCell(10);
                cell_k10.setCellValue(purchaseOrderSubVO.getSupplierName());
                // 备注
                Cell cell_k11 = row_1k.createCell(11);
                cell_k11.setCellValue("");
                // 原中标编码
                Cell cell_k12 = row_1k.createCell(12);
                cell_k12.setCellValue("");

                log.info("导出数据[{}]", row_index);
            }

            log.info("导出数据完成");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("采购单.xls", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.error("导出数据失败，失败原因：{}", e);
        } finally {
            if (Objects.nonNull(workbook)) {
                try {
                    workbook.close();
                } catch (IOException e) {
                }
            }
        }
    }

4、导出结果