导出销售台账送货单

huminmin

2026-04-23 eb807db568325b0bd349c43dbec261646faac52c

导出销售台账送货单

 src/main/java/com/ruoyi/sales/service/impl/SalesLedgerServiceImpl.java

@@ -46,10 +46,14 @@
import com.ruoyi.sales.pojo.*;
import com.ruoyi.sales.service.ISalesLedgerProductService;
import com.ruoyi.sales.service.ISalesLedgerService;
import com.ruoyi.sales.vo.ShippingNoteVo;
import com.ruoyi.stock.mapper.StockInventoryMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
@@ -259,85 +263,193 @@
     */
    private void exportSingleShippingNote(HttpServletResponse response, SalesLedger ledger) {
        try {
            // 设置响应头
            String fileName = String.format("送货单_%s.xlsx",
                    Optional.ofNullable(ledger.getSalesContractNo()).orElse(String.valueOf(ledger.getId())));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

            // 查询明细
            SalesLedgerProduct cond = new SalesLedgerProduct();
            cond.setSalesLedgerId(ledger.getId());
            List<SalesLedgerProduct> productList = salesLedgerProductServiceImpl.selectSalesLedgerProductList(cond);

            // 自动序号+物料编码赋值
            for (int i = 0; i < productList.size(); i++) {
                SalesLedgerProduct product = productList.get(i);
                if (product != null) {
                    product.setSerialNumber(i + 1);
                    product.setOrderNo(ledger.getSalesContractNo());
                    Long productModelId = product.getProductModelId();
                    if (productModelId != null) {
                        ProductModel productModel = productModelMapper.selectById(productModelId);
                        if (productModel != null) {
                            product.setMaterialCode(productModel.getMaterialCode());
                        }
                    }
                }
            }

            // 关联客户信息
            Customer customer = customerMapper.selectById(ledger.getCustomerId());

            // 计算总合计
            BigDecimal totalQuantity = BigDecimal.ZERO;
            for (SalesLedgerProduct product : productList) {
                totalQuantity = totalQuantity.add(product.getQuantity() == null ? BigDecimal.ZERO : product.getQuantity());
            }

            // 组装顶部静态数据
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("customerName", ledger.getCustomerName());
            dataMap.put("companyAddress", customer.getCompanyAddress());
            dataMap.put("contactPerson", customer.getContactPerson());
            dataMap.put("contactPhone", customer.getContactPhone());
            dataMap.put("companyPhone", customer.getCompanyPhone());
            dataMap.put("salesContractNo", ledger.getSalesContractNo());
            dataMap.put("deliveryDate", formatDate(LocalDate.now()));
            dataMap.put("totalQuantity", totalQuantity);
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet(ledger.getSalesContractNo());

            // 使用模板生成Excel
            InputStream templateIs = this.getClass().getResourceAsStream("/static/shipping-note.xlsx");
            if (templateIs == null) {
                throw new BaseException("模板文件不存在");
            sheet.setColumnWidth(0, 2500);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(2, 9000);
            sheet.setColumnWidth(3, 2500);
            sheet.setColumnWidth(4, 3000);
            sheet.setColumnWidth(5, 4500);
            sheet.setColumnWidth(6, 3500);

            // 样式
            CellStyle companyTitle = createBaseStyle(workbook, "宋体", (short) 20, true, true);
            CellStyle billTitle = createBaseStyle(workbook, "宋体", (short) 18, true, true);
            CellStyle headerStyle = createBorderStyle(workbook, "宋体", (short) 12, true, true);
            CellStyle dataCenterStyle = createBorderStyle(workbook, "宋体", (short) 11, false, true); // 内容居中
            CellStyle noBorder = createNoBorderStyle(workbook);
            CellStyle fourDash = createDashBottomStyle(workbook);

            // 标题
            Row row0 = sheet.createRow(0);
            row0.createCell(0).setCellValue("湖南鹏创电子有限公司");
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
            fillMergeRange(sheet, 0, 0, 0, 6, companyTitle);

            Row row1 = sheet.createRow(1);
            row1.createCell(0).setCellValue("送 货 单");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
            fillMergeRange(sheet, 1, 1, 0, 6, billTitle);

            // 客户信息
            Row row2 = sheet.createRow(2);
            row2.createCell(0).setCellValue("客户名称：" + ledger.getCustomerName());
            row2.createCell(4).setCellValue("单据编号：" + ledger.getSalesContractNo());

            Row row3 = sheet.createRow(3);
            row3.createCell(0).setCellValue("送货地址：" + customer.getCompanyAddress());
            row3.createCell(4).setCellValue("送货日期：" + formatDate(LocalDate.now()));

            Row row4 = sheet.createRow(4);
            row4.createCell(0).setCellValue("联系方式：" + customer.getContactPerson() + " " + customer.getContactPhone());
            row4.createCell(4).setCellValue("联系电话：" + customer.getCompanyPhone());

            Row row5 = sheet.createRow(5);
            row5.createCell(0).setCellValue("送货单位：湖南鹏创电子有限公司");
            Row row6 = sheet.createRow(6);
            row6.createCell(0).setCellValue("地址：湖南省耒阳市创新创业园A1栋");
            Row row7 = sheet.createRow(7);
            row7.createCell(0).setCellValue("货物详细信息：");

            // 表头
            int tableStart = 8;
            Row headerRow = sheet.createRow(tableStart);
            String[] headers = {"序号", "物料编号", "品名/规格", "单位", "数量", "订单号", "共四联"};
            for (int i = 0; i < headers.length; i++) {
                Cell hc = headerRow.createCell(i);
                hc.setCellValue(headers[i]);
                hc.setCellStyle(i == 6 ? noBorder : headerStyle);
            }

            ExcelWriter writer = EasyExcel.write(response.getOutputStream()).withTemplate(templateIs).build();
            WriteSheet sheet = EasyExcel.writerSheet().build();
            // 商品数据（全部居中）
            int currentRow = tableStart + 1;
            String[] fourNames = {"存根", "回单", "客户", "仓库"};
            for (int i = 0; i < productList.size(); i++) {
                SalesLedgerProduct p = productList.get(i);
                if (p == null) continue;

            // 先填充数据（包括合计）
            writer.fill(dataMap, sheet);
                String materialCode = "";
                if (p.getProductModelId() != null) {
                    ProductModel m = productModelMapper.selectById(p.getProductModelId());
                    if (m != null) {
                        materialCode = m.getMaterialCode();
                    }
                }

            // 再填充产品列表
            if (!productList.isEmpty()) {
                FillConfig fillConfig = FillConfig.builder()
                        .forceNewRow(Boolean.TRUE)
                        .build();
                writer.fill(productList, fillConfig, sheet);
                Row dataRow = sheet.createRow(currentRow);
                dataRow.createCell(0).setCellValue(i + 1);
                dataRow.createCell(1).setCellValue(materialCode);
                dataRow.createCell(2).setCellValue(p.getProductCategory() + "/" + p.getSpecificationModel());
                dataRow.createCell(3).setCellValue(p.getUnit());
                dataRow.createCell(4).setCellValue(p.getQuantity() == null ? 0 : p.getQuantity().doubleValue());
                dataRow.createCell(5).setCellValue(ledger.getSalesContractNo());

                Cell fourCell = dataRow.createCell(6);
                if (i < fourNames.length) fourCell.setCellValue(fourNames[i]);
                fourCell.setCellStyle(fourDash);

                for (int c = 0; c <= 5; c++) {
                    dataRow.getCell(c).setCellStyle(dataCenterStyle); // 内容居中
                }
                currentRow++;
            }

            writer.finish();
            templateIs.close();
        } catch (BaseException e) {
            log.error("导出失败:{}", e.getMessage(), e);
            throw e;
            // ===================== 最终合计：合并 0-3列（4列） =====================
            Row totalRow = sheet.createRow(currentRow);
            sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, 3)); // 跨4列

            totalRow.createCell(0).setCellValue("合计");
            totalRow.createCell(4).setCellValue(totalQuantity.doubleValue());

            fillMergeRange(sheet, currentRow, currentRow, 0, 5, dataCenterStyle);
            totalRow.createCell(6).setCellStyle(noBorder);

            // 备注
            currentRow++;
            Row remarkRow = sheet.createRow(currentRow);
            remarkRow.createCell(0).setCellValue("备注：贵司在收货后请即刻核实数量及品质，若有异议，请在3日内提出，否则视为收受。");
            sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, 6));

            currentRow++;
            Row sign1 = sheet.createRow(currentRow);
            sign1.createCell(0).setCellValue("送货单位（签章）：");
            sign1.createCell(5).setCellValue("收货单位：");

            currentRow++;
            Row sign2 = sheet.createRow(currentRow);
            sign2.createCell(0).setCellValue("送货人：");
            sign2.createCell(5).setCellValue("收货人：");

            workbook.write(response.getOutputStream());
            workbook.close();

        } catch (Exception e) {
            log.error("导出异常", e);
            throw new BaseException("导出失败：" + e.getMessage());
            throw new BaseException("导出失败");
        }
    }
    

    // 工具类不动
    private CellStyle createBaseStyle(Workbook workbook, String fontName, short fontSize, boolean bold, boolean center) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        font.setBold(bold);
        style.setFont(font);
        if (center) {
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        return style;
    }

    private CellStyle createBorderStyle(Workbook workbook, String fontName, short fontSize, boolean bold, boolean center) {
        CellStyle style = createBaseStyle(workbook, fontName, fontSize, bold, center);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    private CellStyle createNoBorderStyle(Workbook workbook) {
        return createBaseStyle(workbook, "宋体", (short) 11, false, false);
    }

    private CellStyle createDashBottomStyle(Workbook workbook) {
        CellStyle style = createNoBorderStyle(workbook);
        style.setBorderBottom(BorderStyle.DASHED);
        return style;
    }

    private void fillMergeRange(Sheet sheet, int startR, int endR, int startC, int endC, CellStyle style) {
        for (int r = startR; r <= endR; r++) {
            Row row = sheet.getRow(r) == null ? sheet.createRow(r) : sheet.getRow(r);
            for (int c = startC; c <= endC; c++) {
                Cell cell = row.getCell(c) == null ? row.createCell(c) : row.getCell(c);
                cell.setCellStyle(style);
            }
        }
    }
    /**
     * 批量导出送货单为压缩包
     */
@@ -441,6 +553,82 @@
        }
        return date.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    }
    
    /**
     * 设置单元格样式
     */
    private void setCellStyle(Cell cell, boolean isHeader, boolean isTitle) {
        Workbook workbook = cell.getSheet().getWorkbook();
        CellStyle style = workbook.createCellStyle();
        
        // 设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        // 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        
        // 设置字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        if (isTitle) {
            font.setFontHeightInPoints((short) 16);
            font.setBold(true);
        } else if (isHeader) {
            font.setFontHeightInPoints((short) 12);
            font.setBold(true);
        } else {
            font.setFontHeightInPoints((short) 11);
        }
        style.setFont(font);
        
        cell.setCellStyle(style);
    }
    
    
    /**
     * 送货单样式处理器
     */
    public static class ShippingNoteStyleHandler implements com.alibaba.excel.write.handler.CellWriteHandler {
        @Override
        public void beforeCellCreate(com.alibaba.excel.write.metadata.holder.WriteSheetHolder writeSheetHolder, com.alibaba.excel.write.metadata.holder.WriteTableHolder writeTableHolder, org.apache.poi.ss.usermodel.Row row, com.alibaba.excel.metadata.Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        }
        
//        @Override
//        public void afterCellDispose(com.alibaba.excel.write.metadata.holder.WriteSheetHolder writeSheetHolder, com.alibaba.excel.write.metadata.holder.WriteTableHolder writeTableHolder, org.apache.poi.ss.usermodel.Cell cell, com.alibaba.excel.metadata.Head head, Integer relativeRowIndex, Boolean isHead) {
//            org.apache.poi.ss.usermodel.Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
//            org.apache.poi.ss.usermodel.Sheet sheet = writeSheetHolder.getSheet();
//
//            // 设置列宽
//            sheet.setColumnWidth(0, 6000); // 序号
//            sheet.setColumnWidth(1, 6000); // 物料编号
//            sheet.setColumnWidth(2, 10000); // 品名/规格
//            sheet.setColumnWidth(3, 4000); // 单位
//            sheet.setColumnWidth(4, 4000); // 数量
//            sheet.setColumnWidth(5, 6000); // 订单号
//            sheet.setColumnWidth(6, 4000); // 共四联
//
//            // 设置单元格样式
//            org.apache.poi.ss.usermodel.CellStyle style = workbook.createCellStyle();
//            style.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
//            style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
//            style.setBorderTop(org.apache.poi.ss.usermodel.BorderStyle.THIN);
//            style.setBorderBottom(org.apache.poi.ss.usermodel.BorderStyle.THIN);
//            style.setBorderLeft(org.apache.poi.ss.usermodel.BorderStyle.THIN);
//            style.setBorderRight(org.apache.poi.ss.usermodel.BorderStyle.THIN);
//
//            // 设置字体
//            org.apache.poi.ss.usermodel.Font font = workbook.createFont();
//            font.setFontName("宋体");
//            font.setFontHeightInPoints((short) 11);
//            style.setFont(font);
//
//            cell.setCellStyle(style);
//        }
    }

    @Override
    public List<Map<String, Object>> getSalesNo() {

 src/main/java/com/ruoyi/sales/vo/ShippingNoteVo.java

@@ -4,6 +4,7 @@
import com.ruoyi.sales.pojo.SalesLedgerProduct;
import lombok.Data;

import java.math.BigDecimal;
import java.util.List;

@Data
@@ -30,6 +31,32 @@
    private String deliveryDate;

    @Excel(name = "产品信息", type = Excel.Type.EXPORT)
    private List<SalesLedgerProduct> products;
    private List<SalesLedgerProductVo> products;

    @Excel(name = "合计数量", cellType = Excel.ColumnType.NUMERIC)
    private BigDecimal totalQuantity;

    @Data
    public static class SalesLedgerProductVo {
        @Excel(name = "序号", cellType = Excel.ColumnType.NUMERIC)
        private Integer serialNumber;

        @Excel(name = "物料编号", cellType = Excel.ColumnType.STRING)
        private String materialCode;

        @Excel(name = "产品类别", cellType = Excel.ColumnType.STRING)
        private String productCategory;

        @Excel(name = "规格型号", cellType = Excel.ColumnType.STRING)
        private String specificationModel;

        @Excel(name = "单位", cellType = Excel.ColumnType.STRING)
        private String unit;

        @Excel(name = "数量", cellType = Excel.ColumnType.NUMERIC)
        private BigDecimal quantity;

        @Excel(name = "订单号", cellType = Excel.ColumnType.STRING)
        private String orderNo;
    }
}