feat(productionPrintOrderExcel) excel导出

buhuazhen

2026-04-25 6039d8cd64e73970d196094bd66836944f508c59

feat(productionPrintOrderExcel) excel导出

 src/main/java/com/ruoyi/basic/excel/ProductionPrintOrderExcel.java

@@ -1,7 +1,7 @@
package com.ruoyi.basic.excel;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.IdUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
@@ -11,19 +11,14 @@
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.production.dto.ExportProductionPrintOrderDto;
import com.ruoyi.production.pojo.ProductionPrintOrder;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.List;

/**
 * @author buhuazhen
@@ -34,8 +29,65 @@
public class ProductionPrintOrderExcel {


    /**
     * 合并单元格并为合并区域添加左右边框
     *
     * @param sheet    Sheet
     * @param workbook Workbook
     * @param range    要合并的区域
     */
    public static void mergeWithBorder(Sheet sheet, Workbook workbook, CellRangeAddress range, CellStyle s) {

        // 1. 添加合并区域（检测是否重叠）
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress old = sheet.getMergedRegion(i);
            // 判断是否重叠（简单矩形重叠逻辑）
            if (!(range.getLastRow() < old.getFirstRow()
                    || range.getFirstRow() > old.getLastRow()
                    || range.getLastColumn() < old.getFirstColumn()
                    || range.getFirstColumn() > old.getLastColumn())) {

                // 存在重叠 → 删除旧的
                sheet.removeMergedRegion(i);
            }
        }

        sheet.addMergedRegion(range);

        // 2. 为合并区域内的所有单元格添加左右边框
        for (int rowIdx = range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                row = sheet.createRow(rowIdx);
            }

            for (int colIdx = range.getFirstColumn(); colIdx <= range.getLastColumn(); colIdx++) {

                Cell cell = row.getCell(colIdx);
                if (cell == null) {
                    cell = row.createCell(colIdx);
                }

                CellStyle style = workbook.createCellStyle();

                // 若该单元格已有样式，复制
                if (cell.getCellStyle() != null) {
                    style.cloneStyleFrom(cell.getCellStyle());
                }

                // 设置左右边框
                style.setBorderLeft(s.getBorderLeft());
                style.setBorderRight(s.getBorderRight());
                style.setBorderTop(s.getBorderTop());
                style.setBorderBottom(s.getBorderBottom());
                style.setAlignment(s.getAlignment());
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                cell.setCellStyle(style);
            }
        }
    }
    @SneakyThrows
    public void createPrintOrderTemplate(ExportProductionPrintOrderDto printOrderDto, InputStream templateInputStream) {
    public byte[] createPrintOrderTemplate(ExportProductionPrintOrderDto printOrderDto, InputStream templateInputStream) {

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

@@ -62,11 +114,20 @@
        } catch (Exception e) {
            throw new ServiceException("excel 生成失败! " + e.getMessage());
        }

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            Workbook workbook = WorkbookFactory.create(
                    new ByteArrayInputStream(byteArrayOutputStream.toByteArray())
            );

            CellStyle cellStyleTblr = workbook.createCellStyle();
            cellStyleTblr.setBorderBottom(BorderStyle.THIN);
            cellStyleTblr.setBorderTop(BorderStyle.THIN);
            cellStyleTblr.setBorderLeft(BorderStyle.THIN);
            cellStyleTblr.setBorderRight(BorderStyle.THIN);
            cellStyleTblr.setWrapText(true);
            cellStyleTblr.setAlignment(HorizontalAlignment.CENTER);
            cellStyleTblr.setVerticalAlignment(VerticalAlignment.CENTER);

            Sheet sheet = workbook.getSheetAt(0);

@@ -80,25 +141,53 @@
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(6);  // 第7列（G列）
            anchor.setRow1(7);
            anchor.setDx1(1082700); // 向右移动（约半格）
            anchor.setDx1(142700); // 向右移动（约半格）
            anchor.setDy1(222700);


            anchor.setCol2(8);  // 宽度终点
            anchor.setRow2(9);  // 高度终点

            Picture picture = drawing.createPicture(anchor, pictureIdx);
            picture.resize();
//            picture.resize(0.5);

            ByteArrayOutputStream out = new ByteArrayOutputStream();


            // 进行合并操作 切料示意图部分
            int startRow = 7;
            int size = printOrderDto.getMaterialInfo().size();
            int qlEndRow = startRow + size - 1 + 4;
            mergeWithBorder(sheet, workbook, new CellRangeAddress(
                    startRow,
                    qlEndRow,
                    6,
                    7
            ), cellStyleTblr);

            // 产品备注
            mergeWithBorder(sheet, workbook, new CellRangeAddress(
                    6+1+printOrderDto.getMaterialInfo().size() +0,
                    6+1+printOrderDto.getMaterialInfo().size() +1,
                    0,
                    5
            ), cellStyleTblr);

            int zbRowStart = qlEndRow + 2 + CollUtil.size(printOrderDto.getPlateMaking()) +1;
            int zbRowEnd = zbRowStart + CollUtil.size(printOrderDto.getProcessContent());
            mergeWithBorder(sheet, workbook, new CellRangeAddress(
                    zbRowStart,
                    zbRowEnd,
                    4,
                    7
            ), cellStyleTblr);


            workbook.write(out);
            workbook.close();

            FileUtil.writeBytes(
                    out.toByteArray(),
                    "/Users/ONEX/Downloads/uploads/" + IdUtil.simpleUUID() + ".xlsx"
            );

        } catch (Exception e) {
            throw new ServiceException("图片处理失败! " + e.getMessage());
        }

        return out.toByteArray();
    }

}

 src/main/java/com/ruoyi/production/controller/ProductionPrintOrderController.java

@@ -6,7 +6,12 @@
import com.ruoyi.production.service.ProductionPrintOrderService;
import io.swagger.annotations.Api;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;

/**
 * @author buhuazhen
@@ -22,20 +27,29 @@
    private final ProductionPrintOrderService productionPrintOrderService;

    @PostMapping("/save")
    public R save(@RequestBody SaveProductionPrintOrderDto dto){
    public R save(@RequestBody SaveProductionPrintOrderDto dto) {
        productionPrintOrderService.save(dto);
        return R.ok();
    }


    @PostMapping("/getByProductWordId/{id}")
    public ProductionPrintOrder getByProductWordId(@PathVariable Long id){
    public ProductionPrintOrder getByProductWordId(@PathVariable Long id) {
        return productionPrintOrderService.getByProductWordId(id);
    }

    @PostMapping("/export/{id}")
    public R  export(@PathVariable Long id){
        productionPrintOrderService.exportPrintExcelByWordId(id);
        return R.ok();
    @SneakyThrows
    public void export(@PathVariable Long id, HttpServletResponse response) {
        byte[] bytes = productionPrintOrderService.exportPrintExcelByWordId(id);
        String fileName = "印刷定印单.xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 解决中文文件名乱码
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        ServletOutputStream out = response.getOutputStream();
        out.write(bytes);
        out.flush();
    }
}

 src/main/java/com/ruoyi/production/pojo/ProductionPrintOrder.java

@@ -6,6 +6,7 @@

import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
@@ -119,19 +120,19 @@
     * 制版
     */
    @TableField(value = "plate_making",typeHandler = JacksonTypeHandler.class)
    private List<PlateMakingDto> plateMaking;
    private List<PlateMakingDto> plateMaking = new ArrayList<>();

    /**
     * 工序加工内容
     */
    @TableField(value = "process_content",typeHandler = JacksonTypeHandler.class)
    private List<ProcessContentDto> processContent;
    private List<ProcessContentDto> processContent = new ArrayList<>();

    /**
     * 材料信息
     */
    @TableField(value = "material_info",typeHandler = JacksonTypeHandler.class)
    private List<MaterialInfoDto> materialInfo;
    private List<MaterialInfoDto> materialInfo = new ArrayList<>();

    /**
     * 工艺要求

 src/main/java/com/ruoyi/production/service/ProductionPrintOrderService.java

@@ -35,6 +35,6 @@
     * 生成导出excel 印刷单模版
     * @param orderId 订单id
     */
    void exportPrintExcelByWordId(@Nullable Long orderId);
    byte[] exportPrintExcelByWordId(@Nullable Long orderId);

}

 src/main/java/com/ruoyi/production/service/impl/ProductionPrintOrderServiceImpl.java

@@ -7,6 +7,7 @@
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
@@ -122,7 +123,7 @@
    }

    @Override
    public void exportPrintExcelByWordId(@Nullable Long orderId) {
    public byte[] exportPrintExcelByWordId(@Nullable Long orderId) {
        ProductionPrintOrderDto printOrderDto = this.getByProductWordId(orderId);
        List<MaterialInfoDto> materialInfo = printOrderDto.getMaterialInfo();
        Assert.isTrue(CollUtil.isNotEmpty(materialInfo),"未有材料信息，请添加改信息!");
@@ -153,10 +154,11 @@
            byte[] bytes = FileUtil.readBytes(exportProductionPrintOrderDto.getCuttingFileVo().getFileUrl());
            exportProductionPrintOrderDto.setCuttingImage(bytes);
        }
        // cutNum 为小盒数量+中盒数量
        exportProductionPrintOrderDto.setCutNum(String.valueOf(NumberUtil.add(exportProductionPrintOrderDto.getSmallBoxQty(),exportProductionPrintOrderDto.getMediumBoxQty())));

        productionPrintOrderExcel.createPrintOrderTemplate(exportProductionPrintOrderDto, IoUtil.toStream(new File("/Users/ONEX/Downloads/printOrderTemp.xlsx")));


        byte[] printOrderTemplate = productionPrintOrderExcel.createPrintOrderTemplate(exportProductionPrintOrderDto, this.getClass().getResourceAsStream("/static/printOrderTemp.xlsx"));
        return printOrderTemplate;
    }



 src/main/resources/static/printOrderTemp.xlsx

Binary files differ