一个简单的批量excel转mysql工具

背景：工作中，经常发现需要将excel中的表数据导入到mysql中，实际操作一般都是用navcat，但是使用中也发现navcat只支持单个表导入，对xlsx格式支持不友好。于是写了这么一个导表工具。在此，重新记录一下过程。

先说一下结果：

1. 支持批量文件导入

2. 目前只支持单个数据库导入，稍微改动一下可以支持多个数据库

3. 只支持xlsx格式，改动下可以支持xls格式

效果大概是这样

过程如下：

一：设计前端页面

这个我不是很懂，多年来对前端江湖不敢入。只会一些js操作， css那一块完全不懂。所幸导表工具不需要很复杂的页面，只需要一个简单的上传按钮就可以了，网上找一些页面，简单修改下即可使用，这个略去

二：服务端设计

先说一下具体思路

先将excel文件上传到服务器
服务器解析文件得到sql文件
写入sql文件

项目目录如下

其中，main.go是启动文件， www目录下是静态文件，excel2db_app.go包含一个Start方法，excel2db包含主要逻辑。

下载必须的类库，直接用go get命令即可，主要是这两个

"github.com/go-sql-driver/mysql"

"github.com/tealeg/xlsx"

main.go文件，直接调用excel2db_app.go的Start()方法即可，这里略去

excel2db_app.go文件，

主要代码如下

package excel2db

import (
    "fmt"
    "mime/multipart"
    "net/http"
    //"os"
    //"io"
    "html/template"
    "log"
    //"time"
    "io/ioutil"
    "strconv"
    "strings"
)

func upload(w http.ResponseWriter, r *http.Request) {
    r.ParseForm()
    if r.Method == "GET" {
        t, err := template.ParseFiles("./app/excel2db/www/input_sql.html")
        checkErr(err)
        t.Execute(w, nil)
    } else {
        r.ParseMultipartForm(32 << 20)
        mp := r.MultipartForm
        if mp == nil {
            log.Println("not MultipartForm.")
            w.Write(([]byte)("不是MultipartForm格式"))
            return
        }
        go action(w, mp)
    }
}

func action(w http.ResponseWriter, mp *multipart.Form) {
    fhs := mp.File["file"]
    num := len(fhs)
    log.Printf("总文件数：%d\n", num)

    for n, fheader := range fhs {
        fmt.Printf("%d : %s\n", n, fheader.Filename)
        if !strings.HasSuffix(fheader.Filename, ".xlsx") {
            continue
        }
        uploadFile, err := fheader.Open()
        checkErr(err)
        data, err := ioutil.ReadAll(uploadFile)
        checkErr(err)

        // defer func() {
        //  if e := recover(); e != nil {
        //      fmt.Printf("Panicing %s\r\n", e)
        //  }
        // }()
        readExcelFile(data)
    }

    w.Write(([]byte)("成功"))
    log.Println("upload success")
}

func checkErr(err error) {
    if err != nil {
        // err.Error()
        log.Println(err)
    }
}

func Start() {
    fmt.Println("----Excel to Sql multiple工具启动中----")

    http.Handle("/js/", http.FileServer(http.Dir("app/excel2db/www/")))
    http.Handle("/css/", http.FileServer(http.Dir("app/excel2db/www/")))
    http.HandleFunc("/upload", upload)

    port := 8888

    log.Println("欢迎使用 http://127.0.0.1:" + strconv.Itoa(port) + "/upload")
    err := http.ListenAndServe(":"+strconv.Itoa(port), nil)
    if err != nil {
        log.Fatal("listenAndServe: ", err)
    }
}

可以看到，就是启动了一个http服务器

excel2db.app文件，代码如下

package excel2db

import (
	"bytes"
	"database/sql"
	"fmt"
	"log"
	_ "strconv"
	"strings"
	_ "strings"

	_ "github.com/go-sql-driver/mysql"
	"github.com/tealeg/xlsx"
)

const user_name = "zjs"
const password = "123456"
const db_url = "127.0.0.1:3306"
const db_name = "ddd2h5_dev"

func excelToDb(fileNames []string) {
	// 先将Excel读取
	for _, fname := range fileNames {
		fmt.Println("====", fname)
		xlFile, err := xlsx.OpenFile(fname)
		checkErr(err)
		for _, sheet := range xlFile.Sheets {
			tbName := sheet.Name
			fmt.Println("表名：", tbName)

			var buffer bytes.Buffer
			buffer.WriteString("insert into ")
			buffer.WriteString(tbName)

			for rowIndex, row := range sheet.Rows {
				if rowIndex == 1 {
					buffer.WriteString(" (")
					for _, cell := range row.Cells {
						// 0行是中文解释， 1行是字段名
						text := cell.String()
						fmt.Printf("%s\n", text)
						buffer.WriteString(text)
						buffer.WriteString(",")
					}
					buffer.WriteString(")")
				}

			}
		}
	}
}

func readExcelFile(data []byte) {
	xlFile, err := xlsx.OpenBinary(data)
	checkErr(err)

	tabMap := getAllTables()

	for _, sheet := range xlFile.Sheets {
		tbName := sheet.Name
		_, isExist := tabMap[tbName]
		if !isExist {
			continue
		}
		fmt.Println("表名：", tbName)
		colMap1 := make(map[string]int) // 当成一个set， 判断是否存在column
		colMap2 := make(map[int]string) // 真正的map，得到位置与column的关系

		// 根据表名得到栏目名，一般情况下excel会比db表多出一些字段
		err := getColumnName(tbName, colMap1)
		if err != nil {
			log.Println("查找表报错-----", tbName)
			log.Println(err)
			continue
		}

		var buffer bytes.Buffer
		buffer.WriteString("insert into ")
		buffer.WriteString(tbName)
		buffer.WriteString(" (")

		var findColumnOver = false
		var lastColNumber = -12

		for _, row := range sheet.Rows {
			// 直接循环，直到碰到与栏目名对应的那一行
			if !findColumnOver {
				for index, cell := range row.Cells {
					text := cell.String()
					_, exist := colMap1[text]
					if exist {
						colMap2[index] = text
						buffer.WriteString("`")
						buffer.WriteString(text)
						buffer.WriteString("`")

						if len(colMap1) == len(colMap2) {
							findColumnOver = true
							lastColNumber = index
							buffer.WriteString(") values ")
						} else {
							buffer.WriteString(",")
						}
					}
				}
				if len(colMap2) != len(colMap1) {
					//数据不相等，栏目名找错了,直接清空
					colMap2 = make(map[int]string)
					buffer.Reset()
					buffer.WriteString("insert into ")
					buffer.WriteString(tbName)
					buffer.WriteString(" (")
				}
			} else {
				// 说明这时可以导数据了
				isBlankColumn := false

				for ind, cell := range row.Cells {
					_, exist := colMap2[ind]
					if !exist {
						continue
					}

					text := cell.String()

					if ind == 0 && strings.Compare(text, "") == 0 {
						// 空白数据
						isBlankColumn = true
						break
					}

					if ind == 0 {
						buffer.WriteString("(")
					}

					buffer.WriteString("\"")
					buffer.WriteString(text)
					buffer.WriteString("\"")
					if ind == lastColNumber {
						buffer.WriteString(")")
						break
					} else {
						buffer.WriteString(",")
					}
				}

				// 判断是否还有其他数据
				if len(row.Cells) != 0 && !isBlankColumn {
					buffer.WriteString(",")
				}
			}
		}
		str := buffer.String()
		sql := str[0 : len(str)-1] // 把','去掉
		//log.Println("jieguo: ", sql)

		// 先清空表数据
		clearTable(tbName)
		// 插入新数据
		insertToDb(sql)
	}
}

var MY_DB *sql.DB = nil

func getDb() *sql.DB {
	if MY_DB == nil {
		//db, err := sql.Open("mysql", "zjs:123456@tcp(127.0.0.1:3306)/ddd2h5_dev?charset=utf8")
		db, err := sql.Open("mysql", user_name+":"+password+"@tcp("+db_url+")/"+db_name+"?charset=utf8")
		if err != nil {
			log.Fatal("数据库链接出错： ", err)
		}
		//checkErr(err)
		MY_DB = db
	}
	return MY_DB
}

func getAllTables() map[string]int {
	tabMap := make(map[string]int)
	rows, err := getDb().Query("show tables")
	if err != nil {
		log.Println("得到全部表名报错：", err)
	}
	for rows.Next() {
		var tabName string
		rows.Scan(&tabName)
		tabMap[tabName] = 0
	}
	return tabMap
}

func getColumnName(tbName string, colMap map[string]int) error {
	rows, err := getDb().Query("select * from " + tbName + " limit 1")
	if err != nil {
		return err
	}
	//返回所有列
	cols, _ := rows.Columns()

	for _, col := range cols {
		colMap[col] = 0
	}
	return nil
}

func clearTable(tbName string) {
	_, err := getDb().Exec(" DELETE FROM " + tbName)
	if err != nil {
		log.Println("清空表报错： ", err)
	}
}

func insertToDb(sql string) {
	_, err := getDb().Exec(sql)
	if err != nil {
		log.Println("插入数据报错：", err)
	}
}

至于静态文件，可以直接去项目查看一下地址是这个 https://github.com/doublEeVil/CommonCoding/tree/master/CommonGolang/src/app/excel2db/v1/www

做了一些修改，现在用的这个样子的 https://github.com/doublEeVil/CommonCoding/tree/master/CommonGolang/src/app/excel2db/v2

posted on 2018-05-14 13:12 鬼叶听枫阅读(426) 评论(0) 收藏举报

刷新页面返回顶部

一个简单的批量excel转mysql工具

背景：工作中，经常发现需要将excel中的表数据导入到mysql中，实际操作一般都是用navcat，但是使用中也发现navcat只支持单个表导入，对xlsx格式支持不友好。于是写了这么一个导表工具。在此，重新记录一下过程。

导航

公告