go使用excelize导入excel

 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
}