当前位置: 首页>编程语言>正文

golang更新mysql golang mssql

这个小的功能本来想用php开发的,但用php开发的话,需要安装php的mssql sqlserver的扩展,并且还要安装nginx +php-pfm +php的服务,很麻烦,但如果用golang开发的,golang自带web服务,发布后执行一下golang的可执行文件就可以启动服务,并且也不用安装mssql sqlserver 的扩展,简直太方便了,确定方案后就开始动手吧
该项目用到的golang第三方依赖包:

gin
gorm
viper
excelize
sqlserver

一、首先在工作目录建一个项目文件夹 gcuploadexcel

golang更新mysql golang mssql,golang更新mysql golang mssql_golang更新mysql,第1张

二、然后用goland打开这个目录,在goland底部的终端输入 :

go mod init gcuploadexcel

初始化项目,初始化成功后会显示:

golang更新mysql golang mssql,golang更新mysql golang mssql_golang更新mysql_02,第2张

三、创建html模板文件的目录和模板文件tpl/index.tmpl

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<div id="msg">{{.msg}}</div>
<form action="/" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" name="submit" value="提交" />
</form>
</body>
</html>

四、创建项目配置文件 conf.toml

[App]
    Version = "ver1。0"
    Name ="电子表格上传批量入库"
    # debug  release test
    ReleaseMode = "debug"
    Url = "http://localhost"
    Port = ":8082"
[Database]
    Driver = "sqlserver"
    Host = "127.0.0.1"
    Port = "1433"
    Database = "dbname"
    Username = "root"
    Password = "pass"
[Excel]
    Sheet = "sheet1"

五、创建项目主文件main.go

package main

import (
	"fmt"
	"github.com/gin-gonic/gin"
	"github.com/spf13/viper"
	"github.com/xuri/excelize/v2"
	"gorm.io/driver/sqlserver"
	"gorm.io/gorm"
	"html/template"
	"net/url"
	"strconv"
	"strings"

	"net/http"
	"os"
)

var DB *gorm.DB

type Jielonghuodong struct {
	Hdmc string `gorm:"column:hdmc;type:varchar(100);comment:'活动名称'"`
	Ktrq int64  `gorm:"column:ktrq;type:int(10);comment:'开团日期'"`
}
type Kttmxinxi struct {
	Pt     string `gorm:"column:pt;type:varchar(32);comment:'平台'"`
	Dq     string `gorm:"column:dq;type:varchar(32);comment:'地区'"`
	Tm     string `gorm:"column:tm;type:varchar(128);comment:'团名'"`
	Tzname string `gorm:"column:tzname;type:varchar(32);comment:'团长姓名'"`
	Tzlxfs string `gorm:"column:tzlxfs;type:varchar(32);comment:'团长联系方式'"`
	Sdsj   string `gorm:"column:sdsj;type:varchar(32);comment:'收单时间'"`
	Thsj   string `gorm:"column:thsj;type:varchar(32);comment:'提货时间'"`
	Yftc   string `gorm:"column:yftc;type:varchar(32);comment:'提货时间'"`
}
type Kuaituantuanmingxi struct {
	Rq     int64   `gorm:"column:rq;type:int(10);comment:'日期'"`
	Gth    string  `gorm:"column:gth;type:varchar(32);comment:'跟团号'"`
	Ddh    string  `gorm:"column:ddh;type:varchar(50);comment:'订单号'"`
	Tgbt   string  `gorm:"column:tgbt;type:varchar(120);comment:'团购标题'"`
	Xdr    string  `gorm:"column:xdr;type:varchar(120);comment:'下单人'"`
	Bz     string  `gorm:"column:bz;type:varchar(200);comment:'团员备注'"`
	Zfsj   string  `gorm:"column:zfsj;type:varchar(32);comment:'支付时间'"`
	Tzbz   string  `gorm:"column:tzbz;type:varchar(200);comment:'团长备注'"`
	Sp     string  `gorm:"column:sp;type:varchar(100);comment:'商品'"`
	Spid   string  `gorm:"column:spid;type:varchar(50);comment:'商品id'"`
	Spbm   string  `gorm:"column:spbm;type:varchar(50);comment:'商品编码'"`
	Gg     string  `gorm:"column:gg;type:varchar(100);comment:'规格'"`
	Fl     string  `gorm:"column:fl;type:varchar(50);comment:'分类'"`
	Sl     int     `gorm:"column:sl;type:int(10);comment:'数量'"`
	Yhx    int     `gorm:"column:yhx;type:int(10);comment:'已核销'"`
	Dhx    int     `gorm:"column:dhx;type:int(10);comment:'待核销'"`
	Spje   float64 `gorm:"column:spje;type:decimal(10,2);comment:'商品金额'"`
	Spcbj  string  `gorm:"column:Spcbj;type:varchar(50);comment:'商品成本价'"`
	Yf     float64 `gorm:"column:yf;type:decimal(10,2);comment:'运费'"`
	Yh     float64 `gorm:"column:yh;type:decimal(10,2);comment:'优惠'"`
	Ddzfje float64 `gorm:"column:ddzfje;type:decimal(10,2);comment:'订单金额'"`
	Ddtkje float64 `gorm:"column:ddtkje;type:decimal(10,2);comment:'退款金额'"`
	Splrcg string  `gorm:"column:splrcg;type:varchar(50);comment:'商品利润粗估'"`
	Ddzt   string  `gorm:"column:ddzt;type:varchar(50);comment:'订单状态'"`
	Tz     string  `gorm:"column:tz;type:varchar(200);comment:'团长'"`
	Wlfs   string  `gorm:"column:wlfs;type:varchar(100);comment:'物流方式'"`
	Ztd    string  `gorm:"column:ztd;type:varchar(50);comment:'自提点'"`
	Ztdlxr string  `gorm:"column:ztdlxr;type:varchar(50);comment:'自提点联系人'"`
	Ztddh  string  `gorm:"column:Ztddh;type:varchar(50);comment:'自提点电话'"`
	Ztddz  string  `gorm:"column:ztddz;type:varchar(250);comment:'自提点地址'"`
	Shr    string  `gorm:"column:shr;type:varchar(50);comment:'收货人'"`
	Lxdh   string  `gorm:"column:lxdh;type:int(10);comment:'联系电话'"`
	Sheng  string  `gorm:"column:sheng;type:varchar(50);comment:'省'"`
	Shi    string  `gorm:"column:shi;type:varchar(50);comment:'市'"`
	Qu     string  `gorm:"column:qu;type:varchar(50);comment:'地区'"`
	Xxdz   string  `gorm:"column:xxdz;type:varchar(120);comment:''"`
	Id     int64   `gorm:"column:id;type:int(10);comment:'ID'"`
}

func (Jielonghuodong) TableName() string {
	return "jielonghuodong"
}

func (Kuaituantuanmingxi) TableName() string {
	return "kuaituantuanmingxi"
}

func main() {
	r := gin.Default()
	r.MaxMultipartMemory = 256 << 20 // 256MB
	initConfig()
	InitDB()
	r.LoadHTMLGlob("tpl/*")
	r.GET("/ping", func(c *gin.Context) {
		c.JSON(200, gin.H{
			"message": "pong",
		})
	})
	r.GET("/", func(c *gin.Context) {
		c.HTML(http.StatusOK, "index.tmpl", gin.H{
			"title": "上传点点通表格",
		})
	})
	r.POST("/", func(c *gin.Context) {
		// single file
		msg := ""
		file, _ := c.FormFile("file")
		msg += fmt.Sprintf("'%s' uploaded!", file.Filename)
		filepath := "./upload/" + file.Filename
		c.SaveUploadedFile(file, filepath)
		f, err := excelize.OpenFile(filepath)
		if err != nil {
			fmt.Println(err)
			return
		}
		defer func() {
			// Close the spreadsheet.
			if err := f.Close(); err != nil {
				fmt.Println(err)
			}
		}()
		sheetTableName := viper.GetString("Excel.Sheet")
		// Get all the rows in the Sheet1.
		rows, err := f.GetRows(sheetTableName)
		if err != nil {
			fmt.Println(err)
			return
		}

		rowNum := 0
		skipNum := 0

		str := ""
		fldsl := 0
		fldyhx := 0
		flddhx := 0
		fldspje := 0.00
		fldyf := 0.00
		fldyh := 0.00
		fldddzfje := 0.00
		fldddtkje := 0.00
		for i, row := range rows {
			//跳过第一行
			if i == 0 {
				continue
			}
			var kttmxd Kuaituantuanmingxi

			for j, colCell := range row {
				colCell = strings.TrimSpace(colCell)
				if j == 1 && colCell == "" {
					skipNum++
					break
				}
				switch j {
				case 0:
					kttmxd.Gth = colCell
				case 1:
					kttmxd.Ddh = colCell
				case 2:
					kttmxd.Tgbt = colCell
				case 3:
					kttmxd.Xdr = colCell
				case 4:
					kttmxd.Bz = colCell
				case 5:
					kttmxd.Zfsj = colCell
				case 6:
					kttmxd.Tzbz = colCell
				case 7:
					kttmxd.Sp = colCell
				case 8:
					kttmxd.Spid = colCell
				case 9:
					kttmxd.Spbm = colCell
				case 10:
					kttmxd.Gg = colCell
				case 11:
					kttmxd.Fl = colCell
				case 12:
					fldsl, err = strconv.Atoi(colCell)
					if err != nil {
						fldsl = 0
						msg += fmt.Sprintf("第%s行%s列数量数据格式有误<br />", i, j+1)
					}
					kttmxd.Sl = fldsl
				case 13:
					fldyhx, err = strconv.Atoi(colCell)
					if err != nil {
						fldyhx = 0
						msg += fmt.Sprintf("第%s行%s列已核销数据格式有误<br />", i, j+1)
					}
					kttmxd.Yhx = fldyhx
				case 14:
					flddhx, err = strconv.Atoi(colCell)
					if err != nil {
						flddhx = 0
						msg += fmt.Sprintf("第%s行%s列待核销数据格式有误<br />", i, j+1)
					}
					kttmxd.Dhx = flddhx
				case 15:
					fldspje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldspje = 0
						msg += fmt.Sprintf("第%s行%s列商品金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Spje = fldspje
				case 16:
					kttmxd.Spcbj = colCell
				case 17:
					fldyf, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldyf = 0
						msg += fmt.Sprintf("第%s行%s列优惠金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Yf = fldyf
				case 18:
					fldyh, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldyh = 0
						msg += fmt.Sprintf("第%s行%s列运费数据格式有误<br />", i, j+1)
					}
					kttmxd.Yh = fldyh
				case 19:
					fldddzfje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldddzfje = 0
						msg += fmt.Sprintf("第%s行%s列订单支付金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Ddzfje = fldddzfje
				case 20:
					fldddtkje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldddtkje = 0
						msg += fmt.Sprintf("第%s行%s列订单退款金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Ddtkje = fldddtkje
				case 21:
					kttmxd.Splrcg = colCell
				case 22:
					kttmxd.Ddzt = colCell
				case 23:
					kttmxd.Tz = colCell
				case 24:
					kttmxd.Wlfs = colCell
				case 25:
					kttmxd.Ztd = colCell
				case 26:
					kttmxd.Ztdlxr = colCell
				case 27:
					kttmxd.Ztddh = colCell
				case 28:
					kttmxd.Ztddz = colCell
				case 29:
					kttmxd.Shr = colCell
				case 30:
					kttmxd.Lxdh = colCell
				case 31:
					kttmxd.Sheng = colCell
				case 32:
					kttmxd.Shi = colCell
				case 33:
					kttmxd.Qu = colCell
				case 34:
					kttmxd.Xxdz = colCell
				default:
					msg += fmt.Sprintf("第%s行%s列超出范围的数据<br />", i, j+1)
					fmt.Println(" col number err!!!")
				}

			}

			var hdmx Jielonghuodong
			DB.Where("hdmc = ?", kttmxd.Tgbt).Find(&hdmx)
			fmt.Println(hdmx)
			if hdmx.Ktrq != 0 {
				fmt.Println("has")
				kttmxd.Rq = hdmx.Ktrq
				var count int64 = 0
				DB.Model(&Kuaituantuanmingxi{}).Where("ddh = ? and spid = ? ", kttmxd.Ddh, kttmxd.Spid).Count(&count)
				if count == 0 {
					fmt.Println("0")
					DB.Create(&kttmxd)
				} else {
					fmt.Println(" not 0")
					DB.Model(Kuaituantuanmingxi{}).Where("ddh = ? and spid = ? ", kttmxd.Ddh, kttmxd.Spid).Updates(&kttmxd)
				}
				rowNum++
			} else {
				rowNum++
				skipNum++
			}
		}
		msg += "总共处理了" + strconv.Itoa(rowNum) + "行"
		if skipNum > 0 {
			msg += ",其中跳过" + strconv.Itoa(skipNum) + "行"
		}
		msg += str
		c.HTML(http.StatusOK, "index.tmpl", gin.H{
			"title": "上传点点通表格",
			"msg":   template.HTML(msg),
		})

	})
	r.Run(viper.GetString("App.Port"))
}
func initConfig() {
	workDir, _ := os.Getwd()
	viper.SetConfigName("conf")
	viper.SetConfigType("toml")
	viper.AddConfigPath(workDir)
	err := viper.ReadInConfig()
	if err != nil {
		panic(err.Error())
	}
}
func InitDB() *gorm.DB {
	driver := viper.GetString("Database.Driver")
	host := viper.GetString("Database.Host")
	port := viper.GetString("Database.Port")
	database := viper.GetString("Database.Database")
	username := viper.GetString("Database.Username")
	password := viper.GetString("Database.Password")
	query := url.Values{}
	query.Add("database", database)
	query.Add("encrypt", "disable")
	dsn := &url.URL{
		Scheme:   driver,
		User:     url.UserPassword(username, password),
		Host:     host + ":" + port,
		RawQuery: query.Encode(),
	}

	fmt.Println(dsn.String())
	db, err := gorm.Open(sqlserver.Open(dsn.String()), &gorm.Config{})
	if err != nil {
		panic("failed to connect database, err:" + err.Error())
	}
	DB = db
	return db
}

六、 安装依赖包

go mod tidy

七、打包项目

go build main.go

八、执行项目服务

./main.exe

九、项目目录截图:

golang更新mysql golang mssql,golang更新mysql golang mssql_数据格式_03,第3张

十、golang开发的小项目优点

1、快速方便,不用安装一堆的各种web服务和扩展,go打包的windows项目就一个exe文件就全部完成,当然也可以打包成linux环境的包,也可以正常启动服务

2、和php相比,如果用php开发可能需要mssql的php扩展,安装起来比较麻烦,golang 直接用github里各种第三方db驱动很轻松的解决对mssql数据库的支持问题

3、golang打包好的服务,可以直接在配置文件里很轻松的修改db参数,服务端口,方便用户随时根据环境更改配置

最后説明一下,该项目也有我们开发组的梦同学也参与了部分代码的开发及测试

总之用 golang开发一些小应用简直是逆天的方便和舒适

这个小的功能本来想用php开发的,但用php开发的话,需要安装php的mssql sqlserver的扩展,并且还要安装nginx +php-pfm +php的服务,很麻烦,但如果用golang开发的,golang自带web服务,发布后执行一下golang的可执行文件就可以启动服务,并且也不用安装mssql sqlserver 的扩展,简直太方便了,确定方案后就开始动手吧
该项目用到的golang第三方依赖包:

gin
gorm
viper
excelize
sqlserver

一、首先在工作目录建一个项目文件夹 gcuploadexcel

golang更新mysql golang mssql,golang更新mysql golang mssql_golang更新mysql,第1张

二、然后用goland打开这个目录,在goland底部的终端输入 :

go mod init gcuploadexcel

初始化项目,初始化成功后会显示:

golang更新mysql golang mssql,golang更新mysql golang mssql_golang更新mysql_02,第2张

三、创建html模板文件的目录和模板文件tpl/index.tmpl

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<div id="msg">{{.msg}}</div>
<form action="/" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" name="submit" value="提交" />
</form>
</body>
</html>

四、创建项目配置文件 conf.toml

[App]
    Version = "ver1。0"
    Name ="电子表格上传批量入库"
    # debug  release test
    ReleaseMode = "debug"
    Url = "http://localhost"
    Port = ":8082"
[Database]
    Driver = "sqlserver"
    Host = "127.0.0.1"
    Port = "1433"
    Database = "dbname"
    Username = "root"
    Password = "pass"
[Excel]
    Sheet = "sheet1"

五、创建项目主文件main.go

package main

import (
	"fmt"
	"github.com/gin-gonic/gin"
	"github.com/spf13/viper"
	"github.com/xuri/excelize/v2"
	"gorm.io/driver/sqlserver"
	"gorm.io/gorm"
	"html/template"
	"net/url"
	"strconv"
	"strings"

	"net/http"
	"os"
)

var DB *gorm.DB

type Jielonghuodong struct {
	Hdmc string `gorm:"column:hdmc;type:varchar(100);comment:'活动名称'"`
	Ktrq int64  `gorm:"column:ktrq;type:int(10);comment:'开团日期'"`
}
type Kttmxinxi struct {
	Pt     string `gorm:"column:pt;type:varchar(32);comment:'平台'"`
	Dq     string `gorm:"column:dq;type:varchar(32);comment:'地区'"`
	Tm     string `gorm:"column:tm;type:varchar(128);comment:'团名'"`
	Tzname string `gorm:"column:tzname;type:varchar(32);comment:'团长姓名'"`
	Tzlxfs string `gorm:"column:tzlxfs;type:varchar(32);comment:'团长联系方式'"`
	Sdsj   string `gorm:"column:sdsj;type:varchar(32);comment:'收单时间'"`
	Thsj   string `gorm:"column:thsj;type:varchar(32);comment:'提货时间'"`
	Yftc   string `gorm:"column:yftc;type:varchar(32);comment:'提货时间'"`
}
type Kuaituantuanmingxi struct {
	Rq     int64   `gorm:"column:rq;type:int(10);comment:'日期'"`
	Gth    string  `gorm:"column:gth;type:varchar(32);comment:'跟团号'"`
	Ddh    string  `gorm:"column:ddh;type:varchar(50);comment:'订单号'"`
	Tgbt   string  `gorm:"column:tgbt;type:varchar(120);comment:'团购标题'"`
	Xdr    string  `gorm:"column:xdr;type:varchar(120);comment:'下单人'"`
	Bz     string  `gorm:"column:bz;type:varchar(200);comment:'团员备注'"`
	Zfsj   string  `gorm:"column:zfsj;type:varchar(32);comment:'支付时间'"`
	Tzbz   string  `gorm:"column:tzbz;type:varchar(200);comment:'团长备注'"`
	Sp     string  `gorm:"column:sp;type:varchar(100);comment:'商品'"`
	Spid   string  `gorm:"column:spid;type:varchar(50);comment:'商品id'"`
	Spbm   string  `gorm:"column:spbm;type:varchar(50);comment:'商品编码'"`
	Gg     string  `gorm:"column:gg;type:varchar(100);comment:'规格'"`
	Fl     string  `gorm:"column:fl;type:varchar(50);comment:'分类'"`
	Sl     int     `gorm:"column:sl;type:int(10);comment:'数量'"`
	Yhx    int     `gorm:"column:yhx;type:int(10);comment:'已核销'"`
	Dhx    int     `gorm:"column:dhx;type:int(10);comment:'待核销'"`
	Spje   float64 `gorm:"column:spje;type:decimal(10,2);comment:'商品金额'"`
	Spcbj  string  `gorm:"column:Spcbj;type:varchar(50);comment:'商品成本价'"`
	Yf     float64 `gorm:"column:yf;type:decimal(10,2);comment:'运费'"`
	Yh     float64 `gorm:"column:yh;type:decimal(10,2);comment:'优惠'"`
	Ddzfje float64 `gorm:"column:ddzfje;type:decimal(10,2);comment:'订单金额'"`
	Ddtkje float64 `gorm:"column:ddtkje;type:decimal(10,2);comment:'退款金额'"`
	Splrcg string  `gorm:"column:splrcg;type:varchar(50);comment:'商品利润粗估'"`
	Ddzt   string  `gorm:"column:ddzt;type:varchar(50);comment:'订单状态'"`
	Tz     string  `gorm:"column:tz;type:varchar(200);comment:'团长'"`
	Wlfs   string  `gorm:"column:wlfs;type:varchar(100);comment:'物流方式'"`
	Ztd    string  `gorm:"column:ztd;type:varchar(50);comment:'自提点'"`
	Ztdlxr string  `gorm:"column:ztdlxr;type:varchar(50);comment:'自提点联系人'"`
	Ztddh  string  `gorm:"column:Ztddh;type:varchar(50);comment:'自提点电话'"`
	Ztddz  string  `gorm:"column:ztddz;type:varchar(250);comment:'自提点地址'"`
	Shr    string  `gorm:"column:shr;type:varchar(50);comment:'收货人'"`
	Lxdh   string  `gorm:"column:lxdh;type:int(10);comment:'联系电话'"`
	Sheng  string  `gorm:"column:sheng;type:varchar(50);comment:'省'"`
	Shi    string  `gorm:"column:shi;type:varchar(50);comment:'市'"`
	Qu     string  `gorm:"column:qu;type:varchar(50);comment:'地区'"`
	Xxdz   string  `gorm:"column:xxdz;type:varchar(120);comment:''"`
	Id     int64   `gorm:"column:id;type:int(10);comment:'ID'"`
}

func (Jielonghuodong) TableName() string {
	return "jielonghuodong"
}

func (Kuaituantuanmingxi) TableName() string {
	return "kuaituantuanmingxi"
}

func main() {
	r := gin.Default()
	r.MaxMultipartMemory = 256 << 20 // 256MB
	initConfig()
	InitDB()
	r.LoadHTMLGlob("tpl/*")
	r.GET("/ping", func(c *gin.Context) {
		c.JSON(200, gin.H{
			"message": "pong",
		})
	})
	r.GET("/", func(c *gin.Context) {
		c.HTML(http.StatusOK, "index.tmpl", gin.H{
			"title": "上传点点通表格",
		})
	})
	r.POST("/", func(c *gin.Context) {
		// single file
		msg := ""
		file, _ := c.FormFile("file")
		msg += fmt.Sprintf("'%s' uploaded!", file.Filename)
		filepath := "./upload/" + file.Filename
		c.SaveUploadedFile(file, filepath)
		f, err := excelize.OpenFile(filepath)
		if err != nil {
			fmt.Println(err)
			return
		}
		defer func() {
			// Close the spreadsheet.
			if err := f.Close(); err != nil {
				fmt.Println(err)
			}
		}()
		sheetTableName := viper.GetString("Excel.Sheet")
		// Get all the rows in the Sheet1.
		rows, err := f.GetRows(sheetTableName)
		if err != nil {
			fmt.Println(err)
			return
		}

		rowNum := 0
		skipNum := 0

		str := ""
		fldsl := 0
		fldyhx := 0
		flddhx := 0
		fldspje := 0.00
		fldyf := 0.00
		fldyh := 0.00
		fldddzfje := 0.00
		fldddtkje := 0.00
		for i, row := range rows {
			//跳过第一行
			if i == 0 {
				continue
			}
			var kttmxd Kuaituantuanmingxi

			for j, colCell := range row {
				colCell = strings.TrimSpace(colCell)
				if j == 1 && colCell == "" {
					skipNum++
					break
				}
				switch j {
				case 0:
					kttmxd.Gth = colCell
				case 1:
					kttmxd.Ddh = colCell
				case 2:
					kttmxd.Tgbt = colCell
				case 3:
					kttmxd.Xdr = colCell
				case 4:
					kttmxd.Bz = colCell
				case 5:
					kttmxd.Zfsj = colCell
				case 6:
					kttmxd.Tzbz = colCell
				case 7:
					kttmxd.Sp = colCell
				case 8:
					kttmxd.Spid = colCell
				case 9:
					kttmxd.Spbm = colCell
				case 10:
					kttmxd.Gg = colCell
				case 11:
					kttmxd.Fl = colCell
				case 12:
					fldsl, err = strconv.Atoi(colCell)
					if err != nil {
						fldsl = 0
						msg += fmt.Sprintf("第%s行%s列数量数据格式有误<br />", i, j+1)
					}
					kttmxd.Sl = fldsl
				case 13:
					fldyhx, err = strconv.Atoi(colCell)
					if err != nil {
						fldyhx = 0
						msg += fmt.Sprintf("第%s行%s列已核销数据格式有误<br />", i, j+1)
					}
					kttmxd.Yhx = fldyhx
				case 14:
					flddhx, err = strconv.Atoi(colCell)
					if err != nil {
						flddhx = 0
						msg += fmt.Sprintf("第%s行%s列待核销数据格式有误<br />", i, j+1)
					}
					kttmxd.Dhx = flddhx
				case 15:
					fldspje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldspje = 0
						msg += fmt.Sprintf("第%s行%s列商品金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Spje = fldspje
				case 16:
					kttmxd.Spcbj = colCell
				case 17:
					fldyf, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldyf = 0
						msg += fmt.Sprintf("第%s行%s列优惠金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Yf = fldyf
				case 18:
					fldyh, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldyh = 0
						msg += fmt.Sprintf("第%s行%s列运费数据格式有误<br />", i, j+1)
					}
					kttmxd.Yh = fldyh
				case 19:
					fldddzfje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldddzfje = 0
						msg += fmt.Sprintf("第%s行%s列订单支付金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Ddzfje = fldddzfje
				case 20:
					fldddtkje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldddtkje = 0
						msg += fmt.Sprintf("第%s行%s列订单退款金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Ddtkje = fldddtkje
				case 21:
					kttmxd.Splrcg = colCell
				case 22:
					kttmxd.Ddzt = colCell
				case 23:
					kttmxd.Tz = colCell
				case 24:
					kttmxd.Wlfs = colCell
				case 25:
					kttmxd.Ztd = colCell
				case 26:
					kttmxd.Ztdlxr = colCell
				case 27:
					kttmxd.Ztddh = colCell
				case 28:
					kttmxd.Ztddz = colCell
				case 29:
					kttmxd.Shr = colCell
				case 30:
					kttmxd.Lxdh = colCell
				case 31:
					kttmxd.Sheng = colCell
				case 32:
					kttmxd.Shi = colCell
				case 33:
					kttmxd.Qu = colCell
				case 34:
					kttmxd.Xxdz = colCell
				default:
					msg += fmt.Sprintf("第%s行%s列超出范围的数据<br />", i, j+1)
					fmt.Println(" col number err!!!")
				}

			}

			var hdmx Jielonghuodong
			DB.Where("hdmc = ?", kttmxd.Tgbt).Find(&hdmx)
			fmt.Println(hdmx)
			if hdmx.Ktrq != 0 {
				fmt.Println("has")
				kttmxd.Rq = hdmx.Ktrq
				var count int64 = 0
				DB.Model(&Kuaituantuanmingxi{}).Where("ddh = ? and spid = ? ", kttmxd.Ddh, kttmxd.Spid).Count(&count)
				if count == 0 {
					fmt.Println("0")
					DB.Create(&kttmxd)
				} else {
					fmt.Println(" not 0")
					DB.Model(Kuaituantuanmingxi{}).Where("ddh = ? and spid = ? ", kttmxd.Ddh, kttmxd.Spid).Updates(&kttmxd)
				}
				rowNum++
			} else {
				rowNum++
				skipNum++
			}
		}
		msg += "总共处理了" + strconv.Itoa(rowNum) + "行"
		if skipNum > 0 {
			msg += ",其中跳过" + strconv.Itoa(skipNum) + "行"
		}
		msg += str
		c.HTML(http.StatusOK, "index.tmpl", gin.H{
			"title": "上传点点通表格",
			"msg":   template.HTML(msg),
		})

	})
	r.Run(viper.GetString("App.Port"))
}
func initConfig() {
	workDir, _ := os.Getwd()
	viper.SetConfigName("conf")
	viper.SetConfigType("toml")
	viper.AddConfigPath(workDir)
	err := viper.ReadInConfig()
	if err != nil {
		panic(err.Error())
	}
}
func InitDB() *gorm.DB {
	driver := viper.GetString("Database.Driver")
	host := viper.GetString("Database.Host")
	port := viper.GetString("Database.Port")
	database := viper.GetString("Database.Database")
	username := viper.GetString("Database.Username")
	password := viper.GetString("Database.Password")
	query := url.Values{}
	query.Add("database", database)
	query.Add("encrypt", "disable")
	dsn := &url.URL{
		Scheme:   driver,
		User:     url.UserPassword(username, password),
		Host:     host + ":" + port,
		RawQuery: query.Encode(),
	}

	fmt.Println(dsn.String())
	db, err := gorm.Open(sqlserver.Open(dsn.String()), &gorm.Config{})
	if err != nil {
		panic("failed to connect database, err:" + err.Error())
	}
	DB = db
	return db
}

六、 安装依赖包

go mod tidy

七、打包项目

go build main.go

八、执行项目服务

./main.exe

九、项目目录截图:

golang更新mysql golang mssql,golang更新mysql golang mssql_数据格式_03,第3张

十、golang开发的小项目优点

1、快速方便,不用安装一堆的各种web服务和扩展,go打包的windows项目就一个exe文件就全部完成,当然也可以打包成linux环境的包,也可以正常启动服务

2、和php相比,如果用php开发可能需要mssql的php扩展,安装起来比较麻烦,golang 直接用github里各种第三方db驱动很轻松的解决对mssql数据库的支持问题

3、golang打包好的服务,可以直接在配置文件里很轻松的修改db参数,服务端口,方便用户随时根据环境更改配置

最后説明一下,该项目也有我们开发组的梦同学也参与了部分代码的开发及测试

总之用 golang开发一些小应用简直是逆天的方便和舒适

这个小的功能本来想用php开发的,但用php开发的话,需要安装php的mssql sqlserver的扩展,并且还要安装nginx +php-pfm +php的服务,很麻烦,但如果用golang开发的,golang自带web服务,发布后执行一下golang的可执行文件就可以启动服务,并且也不用安装mssql sqlserver 的扩展,简直太方便了,确定方案后就开始动手吧
该项目用到的golang第三方依赖包:

gin
gorm
viper
excelize
sqlserver

一、首先在工作目录建一个项目文件夹 gcuploadexcel

golang更新mysql golang mssql,golang更新mysql golang mssql_golang更新mysql,第1张

二、然后用goland打开这个目录,在goland底部的终端输入 :

go mod init gcuploadexcel

初始化项目,初始化成功后会显示:

golang更新mysql golang mssql,golang更新mysql golang mssql_golang更新mysql_02,第2张

三、创建html模板文件的目录和模板文件tpl/index.tmpl

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<div id="msg">{{.msg}}</div>
<form action="/" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" name="submit" value="提交" />
</form>
</body>
</html>

四、创建项目配置文件 conf.toml

[App]
    Version = "ver1。0"
    Name ="电子表格上传批量入库"
    # debug  release test
    ReleaseMode = "debug"
    Url = "http://localhost"
    Port = ":8082"
[Database]
    Driver = "sqlserver"
    Host = "127.0.0.1"
    Port = "1433"
    Database = "dbname"
    Username = "root"
    Password = "pass"
[Excel]
    Sheet = "sheet1"

五、创建项目主文件main.go

package main

import (
	"fmt"
	"github.com/gin-gonic/gin"
	"github.com/spf13/viper"
	"github.com/xuri/excelize/v2"
	"gorm.io/driver/sqlserver"
	"gorm.io/gorm"
	"html/template"
	"net/url"
	"strconv"
	"strings"

	"net/http"
	"os"
)

var DB *gorm.DB

type Jielonghuodong struct {
	Hdmc string `gorm:"column:hdmc;type:varchar(100);comment:'活动名称'"`
	Ktrq int64  `gorm:"column:ktrq;type:int(10);comment:'开团日期'"`
}
type Kttmxinxi struct {
	Pt     string `gorm:"column:pt;type:varchar(32);comment:'平台'"`
	Dq     string `gorm:"column:dq;type:varchar(32);comment:'地区'"`
	Tm     string `gorm:"column:tm;type:varchar(128);comment:'团名'"`
	Tzname string `gorm:"column:tzname;type:varchar(32);comment:'团长姓名'"`
	Tzlxfs string `gorm:"column:tzlxfs;type:varchar(32);comment:'团长联系方式'"`
	Sdsj   string `gorm:"column:sdsj;type:varchar(32);comment:'收单时间'"`
	Thsj   string `gorm:"column:thsj;type:varchar(32);comment:'提货时间'"`
	Yftc   string `gorm:"column:yftc;type:varchar(32);comment:'提货时间'"`
}
type Kuaituantuanmingxi struct {
	Rq     int64   `gorm:"column:rq;type:int(10);comment:'日期'"`
	Gth    string  `gorm:"column:gth;type:varchar(32);comment:'跟团号'"`
	Ddh    string  `gorm:"column:ddh;type:varchar(50);comment:'订单号'"`
	Tgbt   string  `gorm:"column:tgbt;type:varchar(120);comment:'团购标题'"`
	Xdr    string  `gorm:"column:xdr;type:varchar(120);comment:'下单人'"`
	Bz     string  `gorm:"column:bz;type:varchar(200);comment:'团员备注'"`
	Zfsj   string  `gorm:"column:zfsj;type:varchar(32);comment:'支付时间'"`
	Tzbz   string  `gorm:"column:tzbz;type:varchar(200);comment:'团长备注'"`
	Sp     string  `gorm:"column:sp;type:varchar(100);comment:'商品'"`
	Spid   string  `gorm:"column:spid;type:varchar(50);comment:'商品id'"`
	Spbm   string  `gorm:"column:spbm;type:varchar(50);comment:'商品编码'"`
	Gg     string  `gorm:"column:gg;type:varchar(100);comment:'规格'"`
	Fl     string  `gorm:"column:fl;type:varchar(50);comment:'分类'"`
	Sl     int     `gorm:"column:sl;type:int(10);comment:'数量'"`
	Yhx    int     `gorm:"column:yhx;type:int(10);comment:'已核销'"`
	Dhx    int     `gorm:"column:dhx;type:int(10);comment:'待核销'"`
	Spje   float64 `gorm:"column:spje;type:decimal(10,2);comment:'商品金额'"`
	Spcbj  string  `gorm:"column:Spcbj;type:varchar(50);comment:'商品成本价'"`
	Yf     float64 `gorm:"column:yf;type:decimal(10,2);comment:'运费'"`
	Yh     float64 `gorm:"column:yh;type:decimal(10,2);comment:'优惠'"`
	Ddzfje float64 `gorm:"column:ddzfje;type:decimal(10,2);comment:'订单金额'"`
	Ddtkje float64 `gorm:"column:ddtkje;type:decimal(10,2);comment:'退款金额'"`
	Splrcg string  `gorm:"column:splrcg;type:varchar(50);comment:'商品利润粗估'"`
	Ddzt   string  `gorm:"column:ddzt;type:varchar(50);comment:'订单状态'"`
	Tz     string  `gorm:"column:tz;type:varchar(200);comment:'团长'"`
	Wlfs   string  `gorm:"column:wlfs;type:varchar(100);comment:'物流方式'"`
	Ztd    string  `gorm:"column:ztd;type:varchar(50);comment:'自提点'"`
	Ztdlxr string  `gorm:"column:ztdlxr;type:varchar(50);comment:'自提点联系人'"`
	Ztddh  string  `gorm:"column:Ztddh;type:varchar(50);comment:'自提点电话'"`
	Ztddz  string  `gorm:"column:ztddz;type:varchar(250);comment:'自提点地址'"`
	Shr    string  `gorm:"column:shr;type:varchar(50);comment:'收货人'"`
	Lxdh   string  `gorm:"column:lxdh;type:int(10);comment:'联系电话'"`
	Sheng  string  `gorm:"column:sheng;type:varchar(50);comment:'省'"`
	Shi    string  `gorm:"column:shi;type:varchar(50);comment:'市'"`
	Qu     string  `gorm:"column:qu;type:varchar(50);comment:'地区'"`
	Xxdz   string  `gorm:"column:xxdz;type:varchar(120);comment:''"`
	Id     int64   `gorm:"column:id;type:int(10);comment:'ID'"`
}

func (Jielonghuodong) TableName() string {
	return "jielonghuodong"
}

func (Kuaituantuanmingxi) TableName() string {
	return "kuaituantuanmingxi"
}

func main() {
	r := gin.Default()
	r.MaxMultipartMemory = 256 << 20 // 256MB
	initConfig()
	InitDB()
	r.LoadHTMLGlob("tpl/*")
	r.GET("/ping", func(c *gin.Context) {
		c.JSON(200, gin.H{
			"message": "pong",
		})
	})
	r.GET("/", func(c *gin.Context) {
		c.HTML(http.StatusOK, "index.tmpl", gin.H{
			"title": "上传点点通表格",
		})
	})
	r.POST("/", func(c *gin.Context) {
		// single file
		msg := ""
		file, _ := c.FormFile("file")
		msg += fmt.Sprintf("'%s' uploaded!", file.Filename)
		filepath := "./upload/" + file.Filename
		c.SaveUploadedFile(file, filepath)
		f, err := excelize.OpenFile(filepath)
		if err != nil {
			fmt.Println(err)
			return
		}
		defer func() {
			// Close the spreadsheet.
			if err := f.Close(); err != nil {
				fmt.Println(err)
			}
		}()
		sheetTableName := viper.GetString("Excel.Sheet")
		// Get all the rows in the Sheet1.
		rows, err := f.GetRows(sheetTableName)
		if err != nil {
			fmt.Println(err)
			return
		}

		rowNum := 0
		skipNum := 0

		str := ""
		fldsl := 0
		fldyhx := 0
		flddhx := 0
		fldspje := 0.00
		fldyf := 0.00
		fldyh := 0.00
		fldddzfje := 0.00
		fldddtkje := 0.00
		for i, row := range rows {
			//跳过第一行
			if i == 0 {
				continue
			}
			var kttmxd Kuaituantuanmingxi

			for j, colCell := range row {
				colCell = strings.TrimSpace(colCell)
				if j == 1 && colCell == "" {
					skipNum++
					break
				}
				switch j {
				case 0:
					kttmxd.Gth = colCell
				case 1:
					kttmxd.Ddh = colCell
				case 2:
					kttmxd.Tgbt = colCell
				case 3:
					kttmxd.Xdr = colCell
				case 4:
					kttmxd.Bz = colCell
				case 5:
					kttmxd.Zfsj = colCell
				case 6:
					kttmxd.Tzbz = colCell
				case 7:
					kttmxd.Sp = colCell
				case 8:
					kttmxd.Spid = colCell
				case 9:
					kttmxd.Spbm = colCell
				case 10:
					kttmxd.Gg = colCell
				case 11:
					kttmxd.Fl = colCell
				case 12:
					fldsl, err = strconv.Atoi(colCell)
					if err != nil {
						fldsl = 0
						msg += fmt.Sprintf("第%s行%s列数量数据格式有误<br />", i, j+1)
					}
					kttmxd.Sl = fldsl
				case 13:
					fldyhx, err = strconv.Atoi(colCell)
					if err != nil {
						fldyhx = 0
						msg += fmt.Sprintf("第%s行%s列已核销数据格式有误<br />", i, j+1)
					}
					kttmxd.Yhx = fldyhx
				case 14:
					flddhx, err = strconv.Atoi(colCell)
					if err != nil {
						flddhx = 0
						msg += fmt.Sprintf("第%s行%s列待核销数据格式有误<br />", i, j+1)
					}
					kttmxd.Dhx = flddhx
				case 15:
					fldspje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldspje = 0
						msg += fmt.Sprintf("第%s行%s列商品金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Spje = fldspje
				case 16:
					kttmxd.Spcbj = colCell
				case 17:
					fldyf, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldyf = 0
						msg += fmt.Sprintf("第%s行%s列优惠金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Yf = fldyf
				case 18:
					fldyh, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldyh = 0
						msg += fmt.Sprintf("第%s行%s列运费数据格式有误<br />", i, j+1)
					}
					kttmxd.Yh = fldyh
				case 19:
					fldddzfje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldddzfje = 0
						msg += fmt.Sprintf("第%s行%s列订单支付金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Ddzfje = fldddzfje
				case 20:
					fldddtkje, err = strconv.ParseFloat(colCell, 64)
					if err != nil {
						fldddtkje = 0
						msg += fmt.Sprintf("第%s行%s列订单退款金额数据格式有误<br />", i, j+1)
					}
					kttmxd.Ddtkje = fldddtkje
				case 21:
					kttmxd.Splrcg = colCell
				case 22:
					kttmxd.Ddzt = colCell
				case 23:
					kttmxd.Tz = colCell
				case 24:
					kttmxd.Wlfs = colCell
				case 25:
					kttmxd.Ztd = colCell
				case 26:
					kttmxd.Ztdlxr = colCell
				case 27:
					kttmxd.Ztddh = colCell
				case 28:
					kttmxd.Ztddz = colCell
				case 29:
					kttmxd.Shr = colCell
				case 30:
					kttmxd.Lxdh = colCell
				case 31:
					kttmxd.Sheng = colCell
				case 32:
					kttmxd.Shi = colCell
				case 33:
					kttmxd.Qu = colCell
				case 34:
					kttmxd.Xxdz = colCell
				default:
					msg += fmt.Sprintf("第%s行%s列超出范围的数据<br />", i, j+1)
					fmt.Println(" col number err!!!")
				}

			}

			var hdmx Jielonghuodong
			DB.Where("hdmc = ?", kttmxd.Tgbt).Find(&hdmx)
			fmt.Println(hdmx)
			if hdmx.Ktrq != 0 {
				fmt.Println("has")
				kttmxd.Rq = hdmx.Ktrq
				var count int64 = 0
				DB.Model(&Kuaituantuanmingxi{}).Where("ddh = ? and spid = ? ", kttmxd.Ddh, kttmxd.Spid).Count(&count)
				if count == 0 {
					fmt.Println("0")
					DB.Create(&kttmxd)
				} else {
					fmt.Println(" not 0")
					DB.Model(Kuaituantuanmingxi{}).Where("ddh = ? and spid = ? ", kttmxd.Ddh, kttmxd.Spid).Updates(&kttmxd)
				}
				rowNum++
			} else {
				rowNum++
				skipNum++
			}
		}
		msg += "总共处理了" + strconv.Itoa(rowNum) + "行"
		if skipNum > 0 {
			msg += ",其中跳过" + strconv.Itoa(skipNum) + "行"
		}
		msg += str
		c.HTML(http.StatusOK, "index.tmpl", gin.H{
			"title": "上传点点通表格",
			"msg":   template.HTML(msg),
		})

	})
	r.Run(viper.GetString("App.Port"))
}
func initConfig() {
	workDir, _ := os.Getwd()
	viper.SetConfigName("conf")
	viper.SetConfigType("toml")
	viper.AddConfigPath(workDir)
	err := viper.ReadInConfig()
	if err != nil {
		panic(err.Error())
	}
}
func InitDB() *gorm.DB {
	driver := viper.GetString("Database.Driver")
	host := viper.GetString("Database.Host")
	port := viper.GetString("Database.Port")
	database := viper.GetString("Database.Database")
	username := viper.GetString("Database.Username")
	password := viper.GetString("Database.Password")
	query := url.Values{}
	query.Add("database", database)
	query.Add("encrypt", "disable")
	dsn := &url.URL{
		Scheme:   driver,
		User:     url.UserPassword(username, password),
		Host:     host + ":" + port,
		RawQuery: query.Encode(),
	}

	fmt.Println(dsn.String())
	db, err := gorm.Open(sqlserver.Open(dsn.String()), &gorm.Config{})
	if err != nil {
		panic("failed to connect database, err:" + err.Error())
	}
	DB = db
	return db
}

六、 安装依赖包

go mod tidy

七、打包项目

go build main.go

八、执行项目服务

./main.exe

九、项目目录截图:

golang更新mysql golang mssql,golang更新mysql golang mssql_数据格式_03,第3张

十、golang开发的小项目优点

1、快速方便,不用安装一堆的各种web服务和扩展,go打包的windows项目就一个exe文件就全部完成,当然也可以打包成linux环境的包,也可以正常启动服务

2、和php相比,如果用php开发可能需要mssql的php扩展,安装起来比较麻烦,golang 直接用github里各种第三方db驱动很轻松的解决对mssql数据库的支持问题

3、golang打包好的服务,可以直接在配置文件里很轻松的修改db参数,服务端口,方便用户随时根据环境更改配置

最后説明一下,该项目也有我们开发组的梦同学也参与了部分代码的开发及测试

总之用 golang开发一些小应用简直是逆天的方便和舒适


https://www.xamrdz.com/lan/5c31924436.html

相关文章: