从指定路径excel获取产品编码和图片，传到文件服务器并保存的接口，用于嘉联为所有产品上传图片。

zhangqian

2024-07-11 6ca4d2757a0489ffd3d32829a763704d83575f30

从指定路径excel获取产品编码和图片，传到文件服务器并保存的接口，用于嘉联为所有产品上传图片。

 controllers/other.go

New file
@@ -0,0 +1,188 @@
package controllers

import (
    "fmt"
    "github.com/gin-gonic/gin"
    "github.com/xuri/excelize/v2"
    "gorm.io/gorm"
    "strings"
    "wms/constvar"
    "wms/extend/code"
    "wms/extend/util"
    "wms/models"
    "wms/pkg/logx"
    "wms/utils/upload"
)

type OtherController struct{}

type saveProductImagesFromExcelRequest struct {
    ExcelFilePath        string `json:"excelFilePath"`        //excel路径 用docker cp传到容器里
    SheetIndex           int    `json:"sheetIndex"`           //第几个sheet
    ProductIdColumnIndex int    `json:"productIdColumnIndex"` //产品ID是第几列(从0开始)
    ImageColumn          string `json:"imageColumn"`          //图片所在列名
}

// SaveProductImagesFromExcel
// @Tags      其他
// @Summary   从excel中获取产品图片并保存
// @Produce   application/json
// @Param     object  body  saveProductImagesFromExcelRequest true  "属性信息"
// @Param     Authorization    header string true "token"
// @Success   200 {object} util.Response "成功"
// @Router    /api-wms/v1/other/saveProductImagesFromExcel [post]
func (slf OtherController) SaveProductImagesFromExcel(c *gin.Context) {
    var params saveProductImagesFromExcelRequest
    if err := c.BindJSON(&params); err != nil {
        util.ResponseFormat(c, code.RequestParamError, "参数解析失败，数据类型错误:"+err.Error())
        return
    }
    if params.ExcelFilePath == "" || params.ImageColumn == "" {
        util.ResponseFormat(c, code.RequestParamError, "参数缺失")
        return
    }

    productImageMap, err := UploadProductImagesFromExcel(params.ExcelFilePath, params.SheetIndex, params.ProductIdColumnIndex, params.ImageColumn)
    if err != nil {
        logx.Errorf("SaveProductImagesFromExcel err:%v", err)
        util.ResponseFormat(c, code.SaveFail, "操作失败")
        return
    }

    for productId, urls := range productImageMap {
        if len(urls) == 0 {
            continue
        }
        attachmentList := make([]*models.Attachment, 0)
        materialAttachmentList := make([]*models.MaterialAttachment, 0)
        for _, url := range urls {
            attachmentList = append(attachmentList, &models.Attachment{
                FileUrl:  url,
                FileType: constvar.FileType_Picture,
            })
        }

        err = models.WithTransaction(func(db *gorm.DB) error {
            //保存附件
            if attachmentList, err = models.NewAttachmentSearch().CreateBatchWithResp(attachmentList); err != nil {
                logx.Errorf("attachment create err: %v", err)
                return err
            }

            //保存物料和附件映射
            for _, v := range attachmentList {
                ma := &models.MaterialAttachment{MaterialID: productId, AttachmentID: v.Id}
                materialAttachmentList = append(materialAttachmentList, ma)
            }
            if err := models.NewMaterialAttachmentSearch().SetOrm(db).CreateBatch(materialAttachmentList); err != nil {
                return err
            }
            return nil
        })
        if err != nil {
            logx.Errorf("SaveProductImagesFromExcel save db  err:%v, productId:%v, urls:%v", err, productId, urls)
        }
    }

    util.ResponseFormat(c, code.Success, "保存成功")
}

// UploadProductImagesFromExcel 从Excel文件中上传产品图片
func UploadProductImagesFromExcel(filePath string, sheetIndex int, productIdColumn int, imageColumn string) (productImageMap map[string][]string, err error) {
    productImageMap = make(map[string][]string)
    // 加载Excel文件
    f, err := excelize.OpenFile(filePath)
    if err != nil {
        logx.Errorf("Failed to open Excel file: %v", err)
        return
    }
    defer f.Close()

    // 获取工作表名称
    sheetName := f.GetSheetName(sheetIndex)
    // 读取工作表中的所有行
    rows, err := f.GetRows(sheetName)
    if err != nil {
        logx.Errorf("Failed to get rows from sheet:%v", err)
        return
    }

    // 获取指定列的合并单元格信息
    mergedCells, err := getColumnMergeCells(f, sheetName, imageColumn)
    if err != nil {
        logx.Errorf("Failed to get merged cells:%v", err)
        return
    }

    mergeCellMap := make(map[string]excelize.MergeCell)
    for _, cell := range mergedCells {
        mergeCellMap[cell.GetStartAxis()] = cell
    }

    var imagePaths []string
    // 遍历Excel行，读取数据
    for rowIndex, row := range rows[1:] { // 假设第一行是标题行，跳过
        if len(row) < 1 {
            continue
        }
        productId := row[productIdColumn]
        currentCell := fmt.Sprintf("%s%d", imageColumn, rowIndex+2)

        // 检查当前单元格是否在合并单元格中
        if mergeCell, ok := mergeCellMap[currentCell]; ok {
            imagePaths = make([]string, 0)
            imageCell := mergeCell.GetStartAxis()

            // 获取图片
            pictures, err := f.GetPictures(sheetName, imageCell)
            if err != nil {
                logx.Errorf("Failed to get picture for cell %s: %v", imageCell, err)
                continue
            }

            if len(pictures) == 0 {
                logx.Errorf("No picture found for cell %s", imageCell)
                continue
            }

            for k, picture := range pictures {
                fileBytes := picture.File
                fileName := fmt.Sprintf("image_%s_%d.png", imageCell, k+1)

                // 保存图片到本地并获取路径
                imagePath, err := upload.UploadFileToSeaWeed(string(constvar.FileType_Picture), fileName, fileBytes)
                if err != nil {
                    logx.Errorf("Failed to save image for product %s: %v\n", productId, err)
                    continue
                }
                imagePaths = append(imagePaths, imagePath)
            }
        }

        if productId == "" {
            continue
        }

        // imagePaths
        productImageMap[productId] = imagePaths
        logx.Infof("UploadProductImagesFromExcel Product ID: %s, Image Urls: %s\n", productId, imagePaths)
    }
    return productImageMap, nil
}

// getColumnMergeCells 获取指定列的合并单元格
func getColumnMergeCells(f *excelize.File, sheetName string, targetColumn string) ([]excelize.MergeCell, error) {
    // 获取所有合并单元格
    mergeCells, err := f.GetMergeCells(sheetName)
    if err != nil {
        return nil, err
    }

    var columnMergeCells []excelize.MergeCell
    for _, mergeCell := range mergeCells {
        if strings.HasPrefix(mergeCell.GetStartAxis(), targetColumn) {
            columnMergeCells = append(columnMergeCells, mergeCell)
        }
    }
    return columnMergeCells, nil
}

 docs/docs.go

@@ -2625,6 +2625,43 @@
                }
            }
        },
        "/api-wms/v1/other/saveProductImagesFromExcel": {
            "post": {
                "produces": [
                    "application/json"
                ],
                "tags": [
                    "其他"
                ],
                "summary": "从excel中获取产品图片并保存",
                "parameters": [
                    {
                        "description": "属性信息",
                        "name": "object",
                        "in": "body",
                        "required": true,
                        "schema": {
                            "$ref": "#/definitions/controllers.saveProductImagesFromExcelRequest"
                        }
                    },
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    }
                ],
                "responses": {
                    "200": {
                        "description": "成功",
                        "schema": {
                            "$ref": "#/definitions/util.Response"
                        }
                    }
                }
            }
        },
        "/api-wms/v1/product/addDisuse": {
            "post": {
                "produces": [
@@ -4395,6 +4432,27 @@
                "WhetherTypeAlways",
                "ReservationNever"
            ]
        },
        "controllers.saveProductImagesFromExcelRequest": {
            "type": "object",
            "properties": {
                "excelFilePath": {
                    "description": "excel路径 用docker cp传到容器里",
                    "type": "string"
                },
                "imageColumn": {
                    "description": "图片所在列名",
                    "type": "string"
                },
                "productIdColumnIndex": {
                    "description": "产品ID是第几列(从0开始)",
                    "type": "integer"
                },
                "sheetIndex": {
                    "description": "第几个sheet",
                    "type": "integer"
                }
            }
        },
        "gorm.DeletedAt": {
            "type": "object",
@@ -6333,9 +6391,9 @@
                    "description": "每页大小",
                    "type": "integer"
                },
                "warehouseCode": {
                    "description": "仓库缩写",
                    "type": "string"
                "warehouseId": {
                    "description": "WarehouseCode string ` + "`" + `json:\"warehouseCode\"` + "`" + ` //仓库缩写",
                    "type": "integer"
                }
            }
        },
@@ -6373,6 +6431,9 @@
                "unit": {
                    "description": "单位",
                    "type": "string"
                },
                "warehouseCode": {
                    "type": "string"
                }
            }
        },

 docs/swagger.json

@@ -2614,6 +2614,43 @@
                }
            }
        },
        "/api-wms/v1/other/saveProductImagesFromExcel": {
            "post": {
                "produces": [
                    "application/json"
                ],
                "tags": [
                    "其他"
                ],
                "summary": "从excel中获取产品图片并保存",
                "parameters": [
                    {
                        "description": "属性信息",
                        "name": "object",
                        "in": "body",
                        "required": true,
                        "schema": {
                            "$ref": "#/definitions/controllers.saveProductImagesFromExcelRequest"
                        }
                    },
                    {
                        "type": "string",
                        "description": "token",
                        "name": "Authorization",
                        "in": "header",
                        "required": true
                    }
                ],
                "responses": {
                    "200": {
                        "description": "成功",
                        "schema": {
                            "$ref": "#/definitions/util.Response"
                        }
                    }
                }
            }
        },
        "/api-wms/v1/product/addDisuse": {
            "post": {
                "produces": [
@@ -4384,6 +4421,27 @@
                "WhetherTypeAlways",
                "ReservationNever"
            ]
        },
        "controllers.saveProductImagesFromExcelRequest": {
            "type": "object",
            "properties": {
                "excelFilePath": {
                    "description": "excel路径 用docker cp传到容器里",
                    "type": "string"
                },
                "imageColumn": {
                    "description": "图片所在列名",
                    "type": "string"
                },
                "productIdColumnIndex": {
                    "description": "产品ID是第几列(从0开始)",
                    "type": "integer"
                },
                "sheetIndex": {
                    "description": "第几个sheet",
                    "type": "integer"
                }
            }
        },
        "gorm.DeletedAt": {
            "type": "object",
@@ -6322,9 +6380,9 @@
                    "description": "每页大小",
                    "type": "integer"
                },
                "warehouseCode": {
                    "description": "仓库缩写",
                    "type": "string"
                "warehouseId": {
                    "description": "WarehouseCode string `json:\"warehouseCode\"` //仓库缩写",
                    "type": "integer"
                }
            }
        },
@@ -6362,6 +6420,9 @@
                "unit": {
                    "description": "单位",
                    "type": "string"
                },
                "warehouseCode": {
                    "type": "string"
                }
            }
        },

 docs/swagger.yaml

@@ -361,6 +361,21 @@
    - WhetherTypeAsk
    - WhetherTypeAlways
    - ReservationNever
  controllers.saveProductImagesFromExcelRequest:
    properties:
      excelFilePath:
        description: excel路径 用docker cp传到容器里
        type: string
      imageColumn:
        description: 图片所在列名
        type: string
      productIdColumnIndex:
        description: 产品ID是第几列(从0开始)
        type: integer
      sheetIndex:
        description: 第几个sheet
        type: integer
    type: object
  gorm.DeletedAt:
    properties:
      time:
@@ -1693,9 +1708,9 @@
      pageSize:
        description: 每页大小
        type: integer
      warehouseCode:
        description: 仓库缩写
        type: string
      warehouseId:
        description: WarehouseCode string `json:"warehouseCode"` //仓库缩写
        type: integer
    type: object
  request.GetInventoryHistory:
    properties:
@@ -1720,6 +1735,8 @@
        type: string
      unit:
        description: 单位
        type: string
      warehouseCode:
        type: string
    type: object
  request.GetList:
@@ -4038,6 +4055,30 @@
      summary: 编辑部门信息
      tags:
      - 部门信息
  /api-wms/v1/other/saveProductImagesFromExcel:
    post:
      parameters:
      - description: 属性信息
        in: body
        name: object
        required: true
        schema:
          $ref: '#/definitions/controllers.saveProductImagesFromExcelRequest'
      - description: token
        in: header
        name: Authorization
        required: true
        type: string
      produces:
      - application/json
      responses:
        "200":
          description: 成功
          schema:
            $ref: '#/definitions/util.Response'
      summary: 从excel中获取产品图片并保存
      tags:
      - 其他
  /api-wms/v1/product/addDisuse:
    post:
      parameters:

 router/router.go

@@ -239,5 +239,12 @@
        sysCfgApi.GET("get", sysCfgCtl.GetSystemConfig) //获取系统配置
    }

    //其他
    otherCtl := new(controllers.OtherController)
    otherApi := r.Group(urlPrefix + "/other")
    {
        otherApi.POST("saveProductImagesFromExcel", otherCtl.SaveProductImagesFromExcel) //从指定路径的excel获取产品编号和图片，上传并保存
    }

    return r
}