异步excel导入

安装

$ go get -u gitea.cdlsxd.cn/self-tools/l_excel_import

使用

	importExcel, err := excel.NewImportExcel("goods_import",
excel.WithHeader([]string{"条码", "分类名称", "货品名称", "货品编号", "商品货号", "品牌", "单位", "规格参数", "货品说明", "保质期", "保质期单位", "链接", "货品图片", "电商销售价格", "销售价", "供应商报价", "税率", "默认供应商", "默认存放仓库", "备注", "长", "宽", "高", "重量"}),
excel.WithTrimFiled([]string{"分类名称", "默认供应商", "默认存放仓库", "货品编号", "商品货号"}),
excel.WithFiledRegex(map[string]*excel.Regex{
"货品编号": {
Rule:      "[\\u4e00-\\u9fa5]",
MatchBool: true,
Desc:      "不能包含中文",
},
}),
excel.WithSpeedMod(true),
excel.WithRowPicHandle(func(pic *excel.Pic) (url string, err2 error) {
ossConf := s.c.GetOss()
businessMap, exist := ossConf.BusinessMap["goods"]
if !exist {
return "", errors.New("oss配置不存在")
}
//"physicalGoodsSystems/images/goods"
url = s.GoodsBiz.OssPathFile(businessMap.Folder, pic.PicInfos.Format.AltText, pic.PicInfos.Extension)
//	判断endpoint字段中是否存在internal的字段，如果存在则替换为internal
urlEndpoint := businessMap.Endpoint
if strings.Contains(businessMap.Endpoint, "-internal") {
urlEndpoint = strings.Replace(businessMap.Endpoint, "-internal", "", 1)
}
return fmt.Sprintf("https://%s.%s/%s", businessMap.Bucket, urlEndpoint, url), oss.NewOss(ossConf.AccessKeyId, ossConf.AccessKeySecret).UploadBase64Byte(
ctx,
businessMap.Endpoint,
businessMap.Bucket,
url,
pic.PicInfos.File)
}),
excel.WithDeleteOssHandle(func(ObjectName []string) (err error) {
ossConf := s.c.GetOss()
businessMap, exist := ossConf.BusinessMap["goods"]
if !exist {
return errors.New("oss配置不存在")
}
return oss.NewOss(ossConf.AccessKeyId, ossConf.AccessKeySecret).DeleteBatch(ctx, businessMap.Endpoint, businessMap.Bucket, ObjectName)
}),
excel.WithGetFileObject(func(fileObjectUrl string) (body io.ReadCloser, err error) {
ossConf := s.c.GetOss()
businessMap, exist := ossConf.BusinessMap["goods"]
if !exist {
return nil, errors.New("oss配置不存在")
}
return oss.NewOss(ossConf.AccessKeyId, ossConf.AccessKeySecret).GetObject(ctx, businessMap.Endpoint, businessMap.Bucket, fileObjectUrl)
}),
).Init(ctx, func(excel *excel.ImportExcel, rows []map[string]string) {
var sellByData int
//创建子上下文，防止父ctx取消导致无法请求rpc
subCtx, cancel := context.WithCancel(context.Background())
//协程结束后释放掉子上下文
defer cancel()
for k, v := range rows {
var (
supId       int32
WareHouseId int32
CateId      int32
webPrice    float64
price       float64
costPrice   float64
)
//这里用中文作key是为了方便，如果用英文key或者数字作key，需要做映射关系，而且还要考虑key可能是任意类型，而且开发的时候出现excel插入删除也很麻烦
//分类
if len(v["分类名称"]) > 30 {
excel.AddErr("条码过长，请检查", k+1, v)
continue
}
if v["分类名称"] != "" {
if _, exist := cateMap[v["分类名称"]]; !exist {
excel.AddErr("商品分类不存在", k+1, v)
continue
}
CateId = cateMap[v["分类名称"]].Id
}
//供应商
if v["默认供应商"] != "" {
if _, exist := supplierMap[v["默认供应商"]]; !exist {
excel.AddErr("供应商不存在", k+1, v)
continue
}
supId = supplierMap[v["默认供应商"]].Id
}
//仓库
if v["默认存放仓库"] != "" {
if _, exist := wareHouseMap[v["默认存放仓库"]]; !exist {
excel.AddErr("仓库不存在", k+1, v)
continue
}
WareHouseId = int32(wareHouseMap[v["默认存放仓库"]].Id)
}
//电商价
if v["电商销售价格"] != "" {
webPrice, err = strconv.ParseFloat(v["电商销售价格"], 64)
if err != nil {
excel.AddErr("电商销售价格格式不正确", k+1, v)
continue
}
}
if v["销售价"] != "" {
price, err = strconv.ParseFloat(v["销售价"], 64)
if err != nil {
excel.AddErr("销售价格式不正确", k+1, v)
continue
}
}
if v["供应商报价"] != "" {
costPrice, err = strconv.ParseFloat(v["供应商报价"], 64)
if err != nil {
excel.AddErr("供应商报价格式不正确", k+1, v)
continue
}
}

//保质期
if v["保质期"] == "" {
sellByData = 0
} else {
sellByData, err = strconv.Atoi(v["保质期"])
if err != nil {
excel.AddErr("保质期格式不正确", k+1, v)
continue
}
}

//税率
tax := util.RemovePercentSignsWithBuilder(v["税率"])
taxRate, err := strconv.ParseFloat(tax, 64)
if err != nil {
excel.AddErr("税率格式不正确", k+1, v)
continue
}
//"条码", "分类名称", "货品名称", "货品编号", "品牌", "单位", "规格参数", "货品说明", "保质期", "保质期单位", "链接", "货品图片", "电商销售价格", "代发含税运价格", "税率", "默认供应商", "默认存放仓库", "备注"}
goodsInfo := &pb.AddGoodsReqs{
//商品标题
Title: v["货品名称"],
//商品品牌
Brand: v["品牌"],
//商品简介、卖点
Introduction: v["货品说明"],

//商品编码
GoodsNum: v["货品编号"],
//商品货号
GoodsCode: v["商品货号"],
//商品条形码
GoodsBarCode: v["条码"],
//是否组合商品
IsComposeGoods: 2,
//市场价，单位分
Price: float32(price),
//单位
Unit: v["单位"],
//保质期
SellByDate: int32(sellByData),
//保质期单位
SellByDateUnit: v["保质期单位"],
//外部平台链接
ExternalUrl: v["链接"],
//电商平台价格
ExternalPrice: float32(webPrice),
//销售价
SalesPrice: float32(price),
//税率
TaxRate: float32(taxRate),
//商品参数
GoodsAttributes: v["规格参数"],
//商品说明
GoodsIllustration: v["货品说明"],
//备注
Remark: v["备注"],
Status: pojo.STATUS_ENABLE,
IsHot:  2,
}

goodsAdd, err := goods.NewGoodsBiz(types.ToTmplConf(s.c)).Add(subCtx, goodsInfo)
if err != nil {
excel.AddErr(fmt.Sprintf("添加商品失败：%s", err), k+1, v)
continue
}
if CateId != 0 {
_, err = goods.NewGoodsCateGoryRelationBiz(types.ToTmplConf(s.c)).Add(subCtx, &api.AddGoodsCategoryRelationReqs{
GoodsId:     goodsAdd.Id,
CategoryIds: []int32{CateId},
})
if err != nil {
excel.AddErr(fmt.Sprintf("添加商品分类失败：%s", err), k+1, v)
continue
}
}
if WareHouseId != 0 && supId != 0 && costPrice != 0 {
_, err = goods.NewGoodsSupplierRelationBiz(types.ToTmplConf(s.c)).Add(subCtx, &pb.AddGoodsSupplierRelationReqs{
SupplierId:         supId,
GoodsId:            goodsAdd.Id,
WarehouseId:        WareHouseId,
IsDefaultWarehouse: pojo.IS_DEFAULT_WAREHOUSE,
SupplierGoodsPrice: float32(costPrice),
Sort:               1,
})
if err != nil {
excel.AddErr(fmt.Sprintf("添加供应商商品关系失败：%s", err), k+1, v)
continue
}
}
if v["货品图片"] != "" { //商品图片
for key, media := range strings.Split(v["货品图片"], ",") {
_, _err := goods.NewGoodsMediaBiz(types.ToTmplConf(s.c)).Add(subCtx, &pb.AddGoodsMediaReqs{
GoodsId: goodsAdd.Id,
Url:     media,
Sort:    int32(key + 1),
Type:    pojo.GOODS_MEDIA_TYPE_IMAGE,
})
if _err != nil {
excel.AddErr(fmt.Sprintf("添加商品图片失败：%s", err), k+1, v)
continue
}
}
}
excel.Next()
}
})