执行文件与excel表格在同一个目录下

package main

import (
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/mozillazg/go-pinyin"
	"github.com/xuri/excelize/v2"
)

//数据库配置
const (
	userName = "用户名"
	password = "密码"
	ip       = "数据库ip地址"
	port     = "端口"
	dbName   = "数据库名"
)

//Db数据库连接池
var DB *sql.DB

func InitDB() {
	//构建连接："用户名:密码@tcp(IP:端口)/数据库?charset=utf8"
	path := strings.Join([]string{userName, ":", password, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")

	DB, _ = sql.Open("mysql", path)
	//设置数据库最大连接数
	DB.SetConnMaxLifetime(100)
	//设置上数据库最大闲置连接数
	DB.SetMaxIdleConns(10)
	//验证连接
	if err := DB.Ping(); err != nil {
		fmt.Println("opon database fail")
		return
	}
	fmt.Println("connnect success")
}

func exec_sql(insert_sql string, data []interface{}) (sql.Result, error) {
	tx, _ := DB.Begin()
	result, err := tx.Exec(insert_sql, data...)
	if err != nil {
		tx.Rollback().Error()
	} else {
		tx.Commit()
	}
	return result, err
}

func py(words string) string {
	words = strings.TrimSpace(words)
	list_words := pinyin.LazyConvert(words, nil)
	words = ""
	for i, v := range list_words {
		if i != 0 {
			words = strings.Join([]string{words, v}, "_")
		} else {
			words = strings.Join([]string{words, v}, "")
		}
	}
	return words
}

func ph(len_col int) string {
	placeHolders := "(?"
	for i := 0; i < len_col; i++ {
		placeHolders = strings.Join([]string{placeHolders, "?"}, ",")
	}
	placeHolders = strings.Join([]string{placeHolders, ")"}, "")
	return placeHolders
}

func main() {
	start_time_count := time.Now() //获取当前时间
	go InitDB()
  // 文件名
	file_name := "***.xlsx"
	file_name_slice := strings.Split(file_name, ".")
	table_name := file_name_slice[0]
	f, err := excelize.OpenFile(file_name)
	if err != nil {
		fmt.Println(err)
		return
	}
  // 工作薄名称默认Sheet1
	rows, err := f.Rows("Sheet1")
	go fmt.Printf("打开文件耗时 = %v\n", time.Since(start_time_count))
	data_excels := []interface{}{}
	value_strings := make([]string, 0)

	col_name_sql_creats := "CREATE TABLE IF NOT EXISTS " + table_name + "( "

	// 每次插入 行
	deal_c := 1000
	deal_c_now := 0
	placeHolders := ""
	data_counts := 0
	go fmt.Println("正在导入数据……")
	start_time_insert := time.Now()
	for rows.Next() {
		deal_c_now++
		data_counts++
		row, _ := rows.Columns()
		if data_counts == 1 {
			len_col := len(row)
			placeHolders = ph(len_col)
			fmt.Printf("有 %v 列\n", strconv.Itoa(len_col))
			for i, _ := range row {
				c := "c"
				c = strings.Join([]string{c, strconv.Itoa(i+1) + " VARCHAR(255), "}, "")
				col_name_sql_creats = strings.Join([]string{col_name_sql_creats, c}, " ")
			}
			col_name_sql_creats = strings.Join([]string{col_name_sql_creats, "id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) )"}, "")
			DB.Exec(col_name_sql_creats)
			fmt.Println(col_name_sql_creats)
		}
		for _, colCell := range row {
			data_excels = append(data_excels, colCell)
		}

		data_excels = append(data_excels, "")
		value_strings = append(value_strings, placeHolders)
		for deal_c_now > deal_c {
			insert_sql := fmt.Sprintf("INSERT INTO "+table_name+"  VALUES %v", strings.Join(value_strings, ","))
			exec_sql(insert_sql, data_excels)
			// go fmt.Printf("已导入%v条数据\n", deal_c)
			data_excels = []interface{}{}
			value_strings = make([]string, 0)
			deal_c_now = 0
		}
	}
	insert_sql := fmt.Sprintf("INSERT INTO ggxh VALUES %v", strings.Join(value_strings, ","))
	exec_sql(insert_sql, data_excels)
	fmt.Printf("最后一次导入%v条数据\n", deal_c_now)
	fmt.Printf("共计导入%v条数据\n", data_counts)
	fmt.Printf("insert into总时间 = %v\n", time.Since(start_time_insert))
	fmt.Printf("运行 总耗时 = %v\n", time.Since(start_time_count))
	defer DB.Close()
}