go excel导入Demo

package main

import (
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	"strconv"
	"strings"
	"time"
)

var Pdb *gorm.DB

type Data struct {
	Rindex         int
	CountryName    string
	Sort           int64
	CountryId      int64
	LineName       string
	LineType       string
	Cname          string //公司名称
	UnitPrice      float64
	UnitGtePrice   float64
	UnitWeight     int64
	LimitWeight    int64
	RegFee         float64
	SpecialFee     float64
	ElectroFee     float64
	HasOversizeFee int64
	OverlenFee     float64
	FuleFeeRate    float64 //燃油附加费比例
	BusyFee        float64
	Cfee           float64
	LowWeight      int64
	ServiceFeeRate float64
	TariffFeeRate  float64
	InsureFeeRate  float64
	DaiyouFee      float64
	WeightRate     float64
}

var DataMap = make(map[int]Data)

//线路表
type ZwPackLine struct {
	Id         int64  `json:"id"`
	Name       string `json:"name"`
	Type       string `json:"type"`
	Spot       string `json:"spot"`
	Process    string `json:"process"`
	CreateTime int64  `json:"create_time"`
	UpdateTime int64  `json:"update_time"`
}

type ZwPackCountryLine struct {
	Id         int64 `json:"id"`
	LineId     int64 `json:"line_id"`
	Sort       int64 `json:"sort"`
	CountryId  int64 `json:"country_id"`
	CreateTime int64 `json:"create_time"`
	UpdateTime int64 `json:"update_time"`
}

//线路公司
type ZwPackCompany struct {
	Id         int64  `json:"id"`
	LineId     int64  `json:"line_id"`
	Cname      string `json:"cname"`
	Score      int    `gorm:"default:5"`
	IsRec      int    `json:"is_rec"`
	CreateTime int64  `json:"create_time"`
	UpdateTime int64  `json:"update_time"`
}

//国家
type ZwCountries struct {
	Id          int64
	ChineseName string
}

//template
type ZwPackFareTemplate struct {
	Id           int64   `json:"id"`
	Name         string  `json:"name"`
	UnitPrice    float64 `json:"unit_price"`
	UnitGtePrice float64 `json:"unit_gte_price"`
	UnitWeight   int64   `json:"unit_weight"`
}

//companyTemplateRelation
type ZwPackCtemplate struct {
	Id         int64 `json:"id"`
	CompanyId  int64 `json:"company_id"`
	TemplateId int64 `json:"template_id"`
	CreateTime int64 `json:"create_time"`
	UpdateTime int64 `json:"update_time"`
}

type ZwPackFareCountry struct {
	Id             int64   `json:"id"`
	Tid            int64   `json:"tid"`
	CountryId      int64   `json:"country_id"`
	LowWeight      int64   `json:"low_weight"`
	WeightLimit    int64   `json:"weight_limit"`
	UnitPrice      float64 `json:"unit_price"`
	UnitWeight     int64   `json:"unit_weight"`
	UnitGtePrice   float64 `json:"unit_gte_price"`
	Cfee           float64 `json:"cfee"`
	RegFee         float64 `json:"reg_fee"`
	FuleFeeRate    float64 `json:"fule_fee_rate"`
	BusyFee        float64 `json:"busy_fee"`
	SpecialFee     float64 `json:"special_fee"`
	OverlenFee     float64 `json:"overlen_fee"`
	HasOversizeFee int64   `json:"has_oversize_fee"`
	ElectroFee     float64 `json:"electro_fee"`
	PackageFee     float64 `json:"package_fee"`
	ServiceFeeRate float64 `json:"service_fee_rate"`
	TariffFeeRate  float64 `json:"tariff_fee_rate"`
	InsureFeeRate  float64 `json:"insure_fee_rate"`
	DaiyouFee      float64 `json:"daiyou_fee"`
	VolumeWeight   float64 `json:"volume_weight"`
	WeightRate     float64 `json:"weight_rate"`
	CreateTime     int64   `json:"create_time"`
	UpdateTime     int64   `json:"update_time"`
}

var typeMap = map[string]string{
	"经济": "economic",
	"快速": "fast",
	"特快": "express",
	"特殊": "special",
}

var countryId = make(map[string]int64)

func init() {
	var err error
	Pdb, err = gorm.Open("mysql", "root:password@tcp(127.0.0.1:3306)/databaseName?charset=utf8&parseTime=True&loc=Local")
	if err != nil {
		panic(err)
		return
	}
	Pdb.SingularTable(true)
	fmt.Println("链接mysql成功")
}

func main() {
	f, err := excelize.OpenFile("/Users/DavidWang/goweb/project/excel/template1.28.xlsx")//excel文件路径
	if err != nil {
		fmt.Println(err)
		return
	}
	// 获取 Sheet1 上所有单元格
	rows := f.GetRows("Sheet1")
	for reindex, row := range rows {
		newData := Data{}
		for index, cell := range row {
			switch index {
			case 1:
				_, cid := getCountryId(cell)
				if cid == 0 {
					break
				}
				newData.CountryName = cell
				newData.CountryId = cid
			case 2:
				val, ok := typeMap[strings.Replace(cell, " ", "", -1)]
				if !ok {
					break
				}
				newData.LineType = val
			case 3:
				newData.Sort = decimalInt(cell)
			case 4:
				newData.LineName = cell
			case 5:
				newData.Cname = cell
			case 6:
				newData.UnitPrice = decimalFloat(cell)
			case 7:
				newData.UnitGtePrice = decimalFloat(cell)
			case 8:
				newData.UnitWeight = decimalInt(cell)
			case 9: //清关费
				newData.RegFee = decimalFloat(cell)
			case 10:
				newData.SpecialFee = decimalFloat(cell)
			case 11:
				newData.ElectroFee = decimalFloat(cell)
			case 12:
				newData.HasOversizeFee = decimalInt(cell)
			case 13:
				if len(cell) == 0 {
					newData.OverlenFee = 0
				} else {
					newData.OverlenFee = decimalFloat(cell)
				}
			case 14:
				newData.FuleFeeRate = decimalFloat(cell)
			case 15:
				newData.BusyFee = decimalFloat(cell)
			case 16:
				newData.Cfee = decimalFloat(cell)
			case 17:
				newData.LowWeight = decimalInt(cell)
			case 18:
				newData.ServiceFeeRate = decimalFloat(cell)
			case 19:
				newData.TariffFeeRate = decimalFloat(cell)
			case 20:
				newData.InsureFeeRate = decimalFloat(cell)
			case 21:
				newData.DaiyouFee = decimalFloat(cell)
			case 22:
				rate := decimalFloat(cell)
				if rate <= 0 {
					rate = 1
				}
				newData.WeightRate = rate
			case 23:
				newData.LimitWeight = decimalInt(cell)

			}
		}
		newData.Rindex = reindex
		DataMap[reindex] = newData
	}
        //todo：这里可以使用协程 
	for _, data := range DataMap {
		if data.LineName == "" {
			continue
		}
		if data.CountryId == 0 {
			continue
		}
		tx := Pdb.Begin()
		err, lineId := insertLine(tx, data.LineName, data.LineType)
		if err != nil {
			fmt.Println("写入线路表失败", data.LineName, data.LineType, err)
			tx.Rollback()
			continue
		}
		//国家线路表
		err, _ = insertCountryLine(tx, data.CountryId, lineId, data.Sort)
		if err != nil {
			fmt.Println("写入国家线路表失败", data.LineName, data.CountryId, err)
			tx.Rollback()
			continue
		}
		fmt.Println("写入线路表成功", data.LineName)
		err, companyId := insertCompany(tx, lineId, data.Cname)
		if err != nil {
			fmt.Println("写入公司表失败表失败", data.LineName, data.LineType, err)
			tx.Rollback()
			continue
		}
		fmt.Println("写入公司表成功", data.Cname)
		err, templateId := insertTemplate(tx, data.CountryName+data.LineName+data.Cname, data.UnitPrice, data.UnitGtePrice, data.UnitWeight)
		if err != nil {
			fmt.Println("写入模板表失败表失败", data.Cname, data.LineName, err)
			tx.Rollback()
			continue
		}
		fmt.Println("写入模板表成功", data.Cname)
		err1, _ := insertCtemplate(tx, companyId, templateId)
		if err1 != nil {
			fmt.Println("写入公司模板关联表失败", companyId, templateId, err1)
			tx.Rollback()
			continue
		}
		fmt.Println("写入公司模板关联表成功", data.Cname, templateId)
		err2, _ := insertFareCountry(tx, templateId, data)
		if err2 != nil {
			fmt.Println("运费模板关联国家失败", countryId, templateId, err2)
			tx.Rollback()
			continue
		}
		tx.Commit()
		fmt.Println("恭喜你成功啦", data.LineName)
	}
	return
	//todo excelize根据指定单元格获取值会有问题(取不到),不建议使用
	//var cellTop = []string{"B", "C", "D", "E", "F", "G", "H", "I", "L", "J", "K", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V"}
	//// 获取工作表中指定单元格的值
	//var startPos int64 = 4
	//for {
	//	if startPos == 691 {
	//		break
	//	}
	//	newData := Data{}
	//	for _, cell := range cellTop {
	//		index := fmt.Sprintf("%s%d", "C", 265)
	//		cellVal := f.GetCellValue("Sheet1", index)
	//		fmt.Println(index, cellVal)
	//		return
	//		switch cell {
	//		case "B":
	//			_, cid := getCountryId(cellVal)
	//			newData.CountryId = cid
	//		case "C":
	//			val, ok := typeMap[strings.Replace(cellVal, " ", "", -1)]
	//			if !ok {
	//				fmt.Println("cellVal", cellVal, startPos)
	//				return
	//				continue
	//			}
	//			newData.LineType = val
	//		}
	//
	//		//println(cellVal)
	//	}
	//	startPos++
	//	println("startPos", startPos)
	//	DataMap[startPos] = newData
	//}
	//for index, data := range DataMap {
	//	fmt.Println("index,", index)
	//	fmt.Println("LineType,", data.LineType)
	//	fmt.Println("CountryId,", data.CountryId)
	//}
	//return
	//
	//cell := f.GetCellValue("Sheet1", "B5")
	//println(cell)
	//println(strings.Replace("你 好啊  哈 哈", " ", "", -1))
	//countryId["美国"] = 1
	//countryId["英国"] = 2
	//cid, ok := countryId["英国"]
	//if !ok {
	//	println("not found ", ok)
	//	return
	//}
	//println("cid=", cid)
	//tx := Pdb.Begin()
	//
	//tx.Commit()
	//
	//return

}

func insertFareCountry(tx *gorm.DB, templateId int64, newData Data) (error, int64) {
	if newData.LimitWeight == 0 {
		newData.LimitWeight = 1000000
	}
	fareCountry := ZwPackFareCountry{
		Tid:            templateId,
		CountryId:      newData.CountryId,
		LowWeight:      newData.LowWeight,
		WeightLimit:    newData.LimitWeight,
		UnitPrice:      newData.UnitPrice,
		UnitWeight:     newData.UnitWeight,
		UnitGtePrice:   newData.UnitGtePrice,
		Cfee:           newData.Cfee,
		RegFee:         newData.RegFee,
		FuleFeeRate:    newData.FuleFeeRate,
		BusyFee:        newData.BusyFee,
		SpecialFee:     newData.SpecialFee,
		OverlenFee:     newData.OverlenFee,
		HasOversizeFee: newData.HasOversizeFee,
		ElectroFee:     newData.ElectroFee,
		PackageFee:     8,
		ServiceFeeRate: newData.ServiceFeeRate,
		TariffFeeRate:  newData.TariffFeeRate,
		InsureFeeRate:  newData.InsureFeeRate,
		DaiyouFee:      newData.DaiyouFee,
		VolumeWeight:   1.1,
		WeightRate:     newData.WeightRate,
		CreateTime:     time.Now().Unix(),
		UpdateTime:     time.Now().Unix(),
	}
	existFare := ZwPackFareCountry{
		Tid:       templateId,
		CountryId: newData.CountryId,
	}
	Pdb.Where("tid= ? and country_id = ?", templateId, newData.CountryId).First(&existFare)
	if existFare.Id > 0 {
		fareCountry.Id = existFare.Id
	}
	err := tx.Table("zw_pack_fare_country").Save(&fareCountry).Error
	if err != nil {
		return err, 0
	}
	return nil, fareCountry.Id
}
func insertCtemplate(tx *gorm.DB, companyId int64, TemplateId int64) (error, int64) {
	ctemplate := ZwPackCtemplate{
		CompanyId:  companyId,
		TemplateId: TemplateId,
		CreateTime: time.Now().Unix(),
		UpdateTime: time.Now().Unix(),
	}
	Pdb.Where("company_id= ? and template_id = ?", companyId, TemplateId).First(&ctemplate)
	if ctemplate.Id > 0 {
		return nil, ctemplate.Id
	}
	err := tx.Table("zw_pack_ctemplate").Create(&ctemplate).Error
	if err != nil {
		return err, 0
	}
	return nil, ctemplate.Id
}

func insertCountryLine(tx *gorm.DB, countryId int64, LineId int64, sort int64) (error, int64) {
	cline := ZwPackCountryLine{
		LineId:     LineId,
		CountryId:  countryId,
		Sort:       sort,
		CreateTime: time.Now().Unix(),
		UpdateTime: time.Now().Unix(),
	}
	existLine := ZwPackCountryLine{
		LineId:    LineId,
		CountryId: countryId,
	}
	Pdb.Where("country_id= ? and line_id = ?", countryId, LineId).First(&existLine)
	if existLine.Id > 0 {
		cline.Id = existLine.Id
	}
	err := tx.Table("zw_pack_country_line").Save(&cline).Error
	if err != nil {
		return err, 0
	}
	return nil, cline.Id
}

func insertLine(tx *gorm.DB, name string, lineTypeStr string) (error, int64) {
	line := ZwPackLine{
		Name:       name,
		Type:       lineTypeStr,
		Spot:       "运输时效快，运费低",
		Process:    "20-30天",
		CreateTime: time.Now().Unix(),
	}
	Pdb.Where("name = ?", name).First(&line)
	if line.Id > 0 {
		return nil, line.Id
	}
	err := tx.Table("zw_pack_line").Create(&line).Error
	if err != nil {
		return err, 0
	}
	return nil, line.Id
}

func insertCompany(tx *gorm.DB, lineId int64, cname string) (error, int64) {
	company := ZwPackCompany{
		LineId:     lineId,
		Cname:      cname,
		Score:      5,
		IsRec:      0,
		CreateTime: time.Now().Unix(),
		UpdateTime: time.Now().Unix(),
	}
	Pdb.Where("line_id= ? and cname = ?", lineId, cname).First(&company)
	if company.Id > 0 {
		return nil, company.Id
	}
	err := tx.Table("zw_pack_company").Create(&company).Error
	if err != nil {
		return err, 0
	}
	return nil, company.Id
}

func insertTemplate(tx *gorm.DB, tName string, unitPrice float64, UnitGtePrice float64, UnitWeight int64) (error, int64) {
	template := ZwPackFareTemplate{
		Name:         tName,
		UnitPrice:    unitPrice,
		UnitGtePrice: UnitGtePrice,
		UnitWeight:   UnitWeight,
	}
	Pdb.Where("name= ?", tName).First(&template)
	if template.Id > 0 {
		return nil, template.Id
	}
	err := tx.Table("zw_pack_fare_template").Create(&template).Error
	if err != nil {
		return err, 0
	}
	return nil, template.Id
}

/**
**根据国家名称获取国家对应的id
 */
func getCountryId(countryName string) (error, int64) {
	var country []ZwCountries

	err := Pdb.Raw("SELECT id,name FROM zw_countries WHERE chinese_name=?", countryName).First(&country).Error
	if err != nil {
		return err, 0
	}
	if len(country) == 0 {
		return err, 0
	}

	return nil, country[0].Id
}

func decimalFloat(value string) float64 {
	f, _ := strconv.ParseFloat(value, 64)
	val, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", f), 64)
	return val
}

func decimalInt(value string) int64 {
	i, _ := strconv.ParseInt(value, 10, 64)
	return i
}