235 lines
6.9 KiB
Markdown
235 lines
6.9 KiB
Markdown
异步excel导入
|
||
|
||
|
||
|
||
## 安装
|
||
|
||
```bash
|
||
$ go get -u gitea.cdlsxd.cn/self-tools/l_excel_import
|
||
```
|
||
|
||
|
||
## 使用
|
||
```go
|
||
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()
|
||
}
|
||
})
|
||
``` |