go使用excelize导入excel

小灰灰 2023-10-12 10:48 32阅读 0赞

## model层： ##

package model
    
    import xtime "golux/pkg/time"
    
    type ImportWorkOrder struct {
    	WorkOrderId         string     //工单号
    	LineId              string     //线别
    	LineName            string     //线别描述、名称
    	ProductId           string     //成品料号
    	ProductName         string     //成品描述、名称
    	DeptId              string     //部门
    	DeptName            string     //部门描述
    	EngineerId          string     //工程师id
    	EngineerName        string     //工程师姓名
    	WorkOrderCreateId   string     //工单创建者
    	WorkOrderCreateName string     //工单创建者姓名
    	CreateTime          xtime.Time //创建日期
    	ConveyTime          xtime.Time //下达日期
    	IssuedTime          xtime.Time //实发日期
    	WorkOrderStatus     string     //状态 ->发料 下达 创建 结案 完全入库
    	ClosedTime          xtime.Time //结案日期
    }

## controller层： ##

// 只要以下这些与excel表头相同的列
    const (
    	WORK_ORDER_ID          = "工单号"
    	LINE_ID                = "线别"
    	LINE_NAME              = "线别描述"
    	PRODUCT_ID             = "成品料号"
    	PRODUCT_NAME           = "成品物料描述"
    	DEPT_ID                = "部门"
    	DEPT_NAME              = "部门描述"
    	WORK_ORDER_CREATE_ID   = "工单创建者"
    	WORK_ORDER_CREATE_NAME = "姓名"
    	CREATE_TIME            = "创建日期"
    	CONVEY_TIME            = "下达日期"
    	ISSUED_TIME            = "实发日期"
    	WORK_ORDER_STATUS      = "状态描述"
    	CLOSED_TIME            = "结案日期"
    )
    
    func importWorkOrder(ctx *gin.Context) {
    	//从请求中获取文件
    	file, _, err := ctx.Request.FormFile("file")
    	if checkErr(ctx, err, "上传文件出错", nil) {
    		return
    	}
    	defer file.Close()
    
    	//读excel流
    	xlsx, err := excelize.OpenReader(file)
    	if checkErr(ctx, err, "读取excel文件失败", nil) {
    		return
    	}
    
    	//解析excel的数据
    	workOrders, err := readExcel(xlsx)
    	if checkErr(ctx, err, "解析excel文件失败", nil) {
    		return
    	}
    
    	err = svc.ImportWorkOrderSrv.ImportWorkOr(ctx, workOrders)
    
    	if !checkErr(ctx, err, "", nil) {
    		response.JSONSuccess(ctx, "导入成功")
    	}
    }
    
    func readExcel(xlsx *excelize.File) ([]model.ImportWorkOrder, error) {
    	//根据名字获取cells的内容，返回的是一个[][]string
    	//rows, err := xlsx.GetRows(xlsx.GetSheetName(0))
    
    	rows, err := xlsx.Rows(xlsx.GetSheetName(0))
    
    	if err != nil {
    		return nil, err
    	}
    	//声明一个数组
    	var workOrders []model.ImportWorkOrder
    	var headers []string
    
    	for rows.Next() {
    		row, err := rows.Columns()
    		if err != nil {
    			return nil, err
    		}
    		if len(row) == 0 {
    			continue
    		}
    		if headers == nil {
    			headers = row
    			continue
    		}
    		var data model.ImportWorkOrder
    		for k, v := range row {
    			switch headers[k] {
    			case WORK_ORDER_ID:
    				data.WorkOrderId = v
    			case LINE_ID:
    				data.LineId = v
    			case LINE_NAME:
    				data.LineName = v
    			case PRODUCT_ID:
    				data.ProductId = v
    			case PRODUCT_NAME:
    				data.ProductName = v
    			case DEPT_ID:
    				data.DeptId = v
    			case DEPT_NAME:
    				data.DeptName = v
    			case WORK_ORDER_CREATE_ID:
    				data.WorkOrderCreateId = v
    			case WORK_ORDER_CREATE_NAME:
    				data.WorkOrderCreateName = v
    			case CREATE_TIME:
    				parse, _ := xtime.Parse("01-02-06", v)
    				data.CreateTime = parse
    			case CONVEY_TIME:
    				parse, _ := xtime.Parse("01-02-06", v)
    				data.ConveyTime = parse
    			case ISSUED_TIME:
    				parse, _ := xtime.Parse("01-02-06", v)
    				data.IssuedTime = parse
    			case WORK_ORDER_STATUS:
    				data.WorkOrderStatus = v
    			case CLOSED_TIME:
    				parse, _ := xtime.Parse("01-02-06", v)
    				data.ClosedTime = parse
    			}
    		}
    		if strings.HasPrefix(data.ProductId, "AP") {
    			workOrders = append(workOrders, data)
    			continue
    		}
    	}
    	if err := rows.Error(); err != nil {
    		return nil, err
    	}
    	return workOrders, nil
    }

## dao层： ##

func (d *Dao) ImportWorkOr(ctx context.Context, workOrders []model.ImportWorkOrder) error {
    	lines := []model.Line{}
    	workOrs := []model.WorkOrder{}
    	products := []model.Product{}
    	for _, importWorkOrder := range workOrders {
    		line := model.Line{
    			StrIDModel: postgres.StrIDModel{
    				ID: importWorkOrder.LineId,
    				SoftDelModel: postgres.SoftDelModel{
    					BasicModel: postgres.BasicModel{
    						CreatedTime: xtime.Now(),
    						UpdatedTime: xtime.Now(),
    					},
    				},
    			},
    			LineName: importWorkOrder.LineName,
    			DeptID:   importWorkOrder.DeptId,
    			DeptName: importWorkOrder.DeptName,
    		}
    		workOrder := model.WorkOrder{
    			StrIDModel: postgres.StrIDModel{
    				ID: importWorkOrder.WorkOrderId,
    				SoftDelModel: postgres.SoftDelModel{
    					BasicModel: postgres.BasicModel{
    						CreatedTime: xtime.Now(),
    						UpdatedTime: xtime.Now(),
    					},
    				},
    			},
    			LineID:   importWorkOrder.LineId,
    			LineName: importWorkOrder.LineName,
    		}
    		product := model.Product{
    			StrIDModel: postgres.StrIDModel{
    				ID: importWorkOrder.ProductId,
    				SoftDelModel: postgres.SoftDelModel{
    					BasicModel: postgres.BasicModel{
    						CreatedTime: xtime.Now(),
    						UpdatedTime: xtime.Now(),
    					},
    				},
    			},
    			ProductName:  importWorkOrder.ProductName,
    			EngineerID:   importWorkOrder.EngineerId,
    			EngineerName: importWorkOrder.EngineerName,
    			DeptID:       importWorkOrder.DeptId,
    			DeptName:     importWorkOrder.DeptName,
    			ProductType:  v1.ProductType_ProductTypeUnknown,
    			VersionID:    importWorkOrder.ProductId,
    			Version: &model.ProductVersion{
    				StrIDModel: postgres.StrIDModel{
    					ID: importWorkOrder.ProductId,
    					SoftDelModel: postgres.SoftDelModel{
    						BasicModel: postgres.BasicModel{
    							CreatedTime: xtime.Now(),
    							UpdatedTime: xtime.Now(),
    						},
    					},
    				},
    				ProductID:   importWorkOrder.ProductId,
    				VersionName: "V1",
    				VersionDesc: "V1版本",
    				Remark:      "V1版本好用",
    			},
    			Remark: importWorkOrder.WorkOrderStatus, //成品备注 先把工单状态赋值给他
    		}
    
    		lines = append(lines, line)
    		workOrs = append(workOrs, workOrder)
    		products = append(products, product)
    	}
    
    	//去重
    	filteredLines := []model.Line{}
    	lineIds := map[string]bool{}
    	for _, line := range lines {
    		if !lineIds[line.ID] {
    			lineIds[line.ID] = true
    			filteredLines = append(filteredLines, line)
    		}
    	}
    
    	filteredWorkOrs := []model.WorkOrder{}
    	workOsIds := map[string]bool{}
    	for _, workOr := range workOrs {
    		if !workOsIds[workOr.ID] {
    			workOsIds[workOr.ID] = true
    			filteredWorkOrs = append(filteredWorkOrs, workOr)
    		}
    	}
    
    	filteredProducts := []model.Product{}
    	productIds := map[string]bool{}
    	for _, product := range products {
    		if !productIds[product.ID] {
    			productIds[product.ID] = true
    			filteredProducts = append(filteredProducts, product)
    		}
    	}
    
    	err := pgdb.Tx(d.pg, func(tx *gorm.DB) (err error) {
    		if err = tx.Clauses(clause.OnConflict{
    			UpdateAll: true,
    		}).Create(&filteredLines).Error; err != nil {
    			return err
    		}
    
    		if err = tx.Clauses(clause.OnConflict{
    			UpdateAll: true,
    		}).Create(&filteredWorkOrs).Error; err != nil {
    			return err
    		}
    
    		if err = tx.Clauses(clause.OnConflict{
    			UpdateAll: true,
    		}).Create(&filteredProducts).Error; err != nil {
    			return err
    		}
    		return err
    	})
    	if err != nil {
    		return err
    	}
    	return err
    }