这个小的功能本来想用php开发的，但用php开发的话，需要安装php的mssql sqlserver的扩展，并且还要安装nginx +php-pfm +php的服务，很麻烦，但如果用golang开发的，golang自带web服务，发布后执行一下golang的可执行文件就可以启动服务，并且也不用安装mssql sqlserver 的扩展，简直太方便了，确定方案后就开始动手吧
该项目用到的golang第三方依赖包:
gin
gorm
viper
excelize
sqlserver
一、首先在工作目录建一个项目文件夹 gcuploadexcel
二、然后用goland打开这个目录,在goland底部的终端输入 :
go mod init gcuploadexcel初始化项目，初始化成功后会显示：
三、创建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开发的小项目优点
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
二、然后用goland打开这个目录,在goland底部的终端输入 :
go mod init gcuploadexcel初始化项目，初始化成功后会显示：
三、创建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开发的小项目优点
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
二、然后用goland打开这个目录,在goland底部的终端输入 :
go mod init gcuploadexcel初始化项目，初始化成功后会显示：
三、创建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开发的小项目优点
1、快速方便，不用安装一堆的各种web服务和扩展，go打包的windows项目就一个exe文件就全部完成，当然也可以打包成linux环境的包，也可以正常启动服务
2、和php相比，如果用php开发可能需要mssql的php扩展，安装起来比较麻烦，golang 直接用github里各种第三方db驱动很轻松的解决对mssql数据库的支持问题
3、golang打包好的服务，可以直接在配置文件里很轻松的修改db参数，服务端口，方便用户随时根据环境更改配置
最后説明一下，该项目也有我们开发组的梦同学也参与了部分代码的开发及测试。
总之用 golang开发一些小应用简直是逆天的方便和舒适