大家一起学Golang——电子表格读取数据到Mysql数据库

大家一起学Golang——电子表格读取数据到Mysql数据库

创建

要先使用包 github/tealeg/xlsx
具体内容参照链接：https://github.com/tealeg/xlsx
查考 Reading XLSX files、Writing XLSX files部分的内容，可以看出对于excel表的文件、sheet、row和cell都有操作。读取表格中的数据放入到事先定义的结构体中，便于后面数据操作。

package main

import (
	"database/sql"
	"fmt"
	_"github.com/go-sql-driver/mysql"
	"github.com/tealeg/xlsx"
)

type Person struct {
	Name       string
	Education  string
	University string
	Industry   string
	Workyear   string
	Position   string
	Salary     string
	Language   string
}

//func init () {
//
//}
func GetExcel() []Person {
	var per1 []Person
	file, err := xlsx.OpenFile("E:\\student_info.xlsx")  //打开文件
	if err != nil {
		fmt.Println(err)
	}

	for _, sheet := range file.Sheets {
		for _, row := range sheet.Rows {
			var temp1 Person
			var str []string
			for _, cell := range row.Cells {
				str = append(str, cell.String())
			}
			temp1.Name = str[0]
			temp1.Education = str[1]
			temp1.University = str[2]
			temp1.Industry = str[3]
			temp1.Workyear = str[4]
			temp1.Position = str[5]
			temp1.Salary = str[6]
			temp1.Language = str[7]
			if str[1] == "本科" && str[4] == "1-3年" {
				per1 = append(per1, temp1)
			}
		}
	}
	for i, v := range per1 {
		fmt.Println(i, v)
	}
	return per1
}

var (
	db *sql.DB
)

//建立数据库连接
func init() {
	var err error
	db, err = sql.Open("mysql", "mysqluser:password@(127.0.0.1:3306)/goDB")
	if err != nil {
		fmt.Println(err)
		panic(err)
	}
}
func main() {
	//var db *sql.DB    这个和全局的db 重复 报错了！！==
	//"用户名:密码@[连接方式](主机名:端口号)/数据库名"
	//db, _ := sql.Open("mysql", "mysqluser:password@(127.0.0.1:3306)/goDB")
	defer db.Close()
	//连接数据库
	err := db.Ping()
	if err != nil {
		fmt.Println("数据库连接失败")
		return
	}
	per := GetExcel()
	//db.Query("insert into goDB.person values(?,?,?,?,?,?,?,?)")

	stmt, _ := db.Prepare("insert into goDB.person values(?,?,?,?,?,?,?,?)") //获取预处理语句对象
	for _, one := range per {
		fmt.Println(one.Name, one.Education, one.University, one.Industry, one.Workyear, one.Position, one.Salary, one.Language)
		stmt.Exec(one.Name, one.Education, one.University, one.Industry, one.Workyear, one.Position, one.Salary, one.Language) //调用预处理语句
	}

}