导出数据表结构到Excel

最近项目复盘，需要提供的资料包括数据字典，也就是项目的表结构，注释，类型，是否可空等信息。表要是少的话，一个一个填倒也没啥。 表一多的话，填写起来就很费劲了。于是顺手写了个小玩意儿，用来导出表结构到Excel。具体思路就是information_schema数据库中的COLUMNS表 记录了所有的表的信息，查询然后填充到Excel中就好了。

package main

import "fmt"

type Config struct {
	User string `json:"user"`
	Password string `json:"password"`
	Host string `json:"host"`
	Port int `json:"port"`
	Database string `json:"database"`
	Include []string `json:"include"`
	Exclude []string `json:"exclude"`
	ProjectName string `json:"projectName"`
	ProjectVersion string `json:"projectVersion"`
	Author string `json:"author"`
	WriteAt string `json:"writeAt"`
	ExpectedRelease string `json:"expectedRelease"`
	Reader string `json:"reader"`
	Purpose string `json:"purpose"`
	Histories []struct{
		Version string `json:"version"`
		Date string `json:"date"`
		Content string `json:"content"`
		Reason string `json:"reason"`
		Author string `json:"author"`
	} `json:"histories"`
}

func (c Config) getDsn() string {
	return fmt.Sprintf("%s:%s@tcp(%s:%d)/information_schema?charset=utf8mb4", c.User, c.Password, c.Host, c.Port)
}

:::

// db.go
package main

import (
	"github.com/jinzhu/gorm"
)

type Column struct {
	TableCatalog string `gorm:"column:TABLE_CATALOG;type:varchar(64)"`
	TableSchema string `gorm:"column:TABLE_SCHEMA;type:varchar(64)"`
	TableNameReal string `gorm:"column:TABLE_NAME;type:varchar(64)"`
	ColumnName string `gorm:"column:COLUMN_NAME;type:varchar(64)"`
	OrdinalPosition uint `gorm:"column:ORDINAL_POSITION"`
	ColumnDefault *string `gorm:"column:COLUMN_DEFAULT;type:text"`
	IsNullable string `gorm:"column:IS_NULLABLE;type:varchar(3)"`
	DataType string `gorm:"column:DATA_TYPE;type:longtext"`
	CharacterMaximumLength *int64 `gorm:"column:CHARACTER_MAXIMUM_LENGTH;type:bigint"`
	CharacterOctetLength *int64 `gorm:"column:CHARACTER_OCTET_LENGTH;type:bigint"`
	NumericPrecision *uint64 `gorm:"column:NUMERIC_PRECISION;type:unsigned bigint"`
	NumericScale *uint64 `gorm:"column:NUMERIC_SCALE;type:unsigned bigint"`
	DatetimePrecision *uint32 `gorm:"column:DATETIME_PRECISION"`
	CharacterSetName *string `gorm:"column:CHARACTER_SET_NAME;type:varchar(64)"`
	CollationName *string `gorm:"column:COLLATION_NAME;type:varchar(64)"`
	ColumnType string `gorm:"column:COLUMN_TYPE;type:mediumtext"`
	ColumnKey string `gorm:"column:COLUMN_KEY;type:enum('','PRI','UNI','MUL')"` // ???
	Extra string `gorm:"column:EXTRA;type:varchar(256)"`
	Privileges string `gorm:"column:PRIVILEGES;type:varchar(154)"`
	ColumnComment string `gorm:"column:COLUMN_COMMENT;type:text"`
	GenerationExpression string `gorm:"column:GENERATION_EXPRESSION;type:longtext"`
	SrsId *uint32 `gorm:"column:SRS_ID;type:unsigned int"`
}

var DB *gorm.DB

func connect(dsn string) {
	var err error
	DB, err = gorm.Open("mysql", dsn)
	if err != nil {
		panic("连接数据库失败")
	}
}

// 获取该数据库下所有的列信息
// @return 返回的是按表名分组的列信息
func getTables(dbName string, include, exclude []string) map[string][]Column {
	var columns []Column
	var count = 100
	if len(include) > 0 {
		DB.Where("TABLE_SCHEMA = ? and TABLE_NAME in (?)", dbName, include).Find(&columns)
	} else if len(exclude) > 0 {
		DB.Where("TABLE_SCHEMA = ? and TABLE_NAME not in (?)", dbName, exclude).Find(&columns)
	} else {
		DB.Where("TABLE_SCHEMA = ?", dbName).Find(&columns)
	}

	var maps = make(map[string][]Column, count)
	for _, c := range columns {
		if _, ok := maps[c.TableNameReal]; !ok {
			maps[c.TableNameReal] = []Column{c}
		} else {
			maps[c.TableNameReal] = append(maps[c.TableNameReal], c)
		}
	}
	return maps
}

func (c Column) TableName() string {
	return "COLUMNS"
}

// main.go
package main

import (
	"encoding/json"
	"errors"
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize/v2"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	"github.com/urfave/cli/v2"
	"io/ioutil"
	"log"
	"os"
	"strconv"
	"time"
)

func main() {
	app := &cli.App{
		Name: "exporter",
		Usage: "导出指定数据库的表元数据",
		Compiled: time.Now(),
		Authors: []*cli.Author{
			{Name: "xycc", Email: "[email protected]"},
		},
		Commands: []*cli.Command{
			{
				Name: "json",
				Category: "config",
				Usage: "生成所需的配置文件模板",
				Aliases: []string{"j"},
				Action: func(context *cli.Context) error {
					f, err := os.Create("config.json")
					if err != nil {
						return errors.New(fmt.Sprintf("创建配置文件失败，请确认对当前目录有读写权限: %v\n", err))
					}
					_, err = f.WriteString(`{
	"user": "root",
	"password": "password",
	"host": "127.0.0.1",
	"port": 3306,
	"database": "xxxx",
	"include": [],
	"exclude": [],
	"projectName": "xxxxx",
	"projectVersion": "1.0.0",
	"author": "作者，例如：xycc",
	"writeAt": "文档撰写时间例如：2009/05/04",
	"expectedRelease": "预计上线时间，例如：2009/06/13",
	"reader": "读者，例如：xxx项目和XXXX业务项目",
	"purpose": "目的，例如本数据字典是为了干嘛的，哪个项目的",
	"histories": [
		{
			"version": "文档版本编号，这个数组是历史修订的数组",
			"date": "修订日期",
			"content": "修订内容",
			"reason": "修订原因",
			"author": "修订人"
		}
	]
}`)
					if err != nil {
						return errors.New(fmt.Sprintf("写入配置文件失败，请确保对当前目录有读写权限: %v\n", err))
					}
					log.Println(`已将示例配置文件写入了当前目录下的"config.json"文件中，修改配置文件，然后运行generate命令生成文档
注意： 1. include配置项的作用为只解析此数组内的表， exclude配置项的作用为此数组内的表除外都解析
       2. include与exclude配置互斥，同时存在的情况下只有include会生效
       3. exclude与include最好二选一， include以上的选项都必填`)
					return nil
				},
			},
			{
				Name: "generate",
				Category: "action",
				Aliases: []string{"g"},
				Usage: "此子命令的作用为根据当前目录下的config.json文件生成数据字典的excel文件",
				Flags: []cli.Flag{
					&cli.StringFlag{
						Name: "config",
						Aliases: []string{"c"},
						Usage: "此选项为指定配置文件，默认为当前目录下的config.json文件, 仅支持json文件且格式与示例文件的格式相同",
						Value: "config.json",
						Required: false,
					},
				},
				Action: func(context *cli.Context) error {
					// 链接数据库 并且获取到指定数据库的指定表的元数据
					fileName := context.String("config")
					file, err := os.OpenFile(fileName, os.O_RDONLY, 0600)
					if err != nil {
						return errors.New(fmt.Sprintf("不能打开文件:[%s]的内容，请检查, 错误原因: %v\n", fileName, err))
					}

					data, err := ioutil.ReadAll(file)
					if err != nil {
						return errors.New(fmt.Sprintf("不能读取文件: [%s]的内容，请检查，错误原因: %v\n", fileName, err))
					}
					var config Config
					err = json.Unmarshal(data, &config)
					if err != nil {
						return errors.New(fmt.Sprintf("不能反序列化json文件: [%s]的内容, 请检查格式是否正确，可以使用json命令生成示例配置文件, 错误原因: %v\n", fileName, err))
					}
					connect(config.getDsn())
					defer func() {
						_ = DB.Close()
					}()

					f := excelize.NewFile()
					sheetName := config.ProjectName + "数据字典"
					f.DeleteSheet("sheet1")
					index := f.NewSheet(sheetName)
					titleStyle, _ := f.NewStyle(`{"font":{"bold":true,"size":24}}`)
					f, startRow := setTitle(f, sheetName, config, titleStyle)
					// 然后插入每张表的数据
					columnMap := getTables(config.Database, config.Include, config.Exclude)
					f = parseColumnMap(f, sheetName, columnMap, startRow)

					f.SetActiveSheet(index)
					_ = f.SaveAs(sheetName + ".xlsx")
					return nil
				},
			},
		},
	}

	err := app.Run(os.Args)
	if err != nil {
		log.Fatalln(err)
	}
}

func setTitle(f *excelize.File, sheetName string, config Config, titleStyle int) (*excelize.File, int) {
	_ = f.MergeCell(sheetName, "A1", "E1")
	f.SetCellValue(sheetName, "A1", "")
	f.SetCellStyle(sheetName, "A1", "A1", titleStyle)

	f.SetCellValue(sheetName, "A2", "文档名称")
	f.MergeCell(sheetName, "B2", "E2")
	f.SetCellValue(sheetName, "B2", config.ProjectName + "项目数据字典")

	f.SetCellValue(sheetName, "A3", "产品版本")
	f.MergeCell(sheetName, "B3", "E3")
	f.SetCellValue(sheetName, "B3", config.ProjectVersion)

	f.SetCellValue(sheetName, "A4", "撰写人")
	f.MergeCell(sheetName, "B4", "E4")
	f.SetCellValue(sheetName, "B4", config.Author)

	f.SetCellValue(sheetName, "A5", "撰写时间")
	f.MergeCell(sheetName, "B5", "E5")
	f.SetCellValue(sheetName, "B5", config.WriteAt)

	f.SetCellValue(sheetName, "A6", "预计上线时间")
	f.MergeCell(sheetName, "B6", "E6")
	f.SetCellValue(sheetName, "B6", config.ExpectedRelease)

	f.SetCellValue(sheetName, "A7", "读者")
	f.MergeCell(sheetName, "B7", "E7")
	f.SetCellValue(sheetName, "B7", config.Reader)

	f.SetCellValue(sheetName, "A8", "目的")
	f.MergeCell(sheetName, "B8", "E8")
	f.SetCellValue(sheetName, "B8", config.Purpose)

	f.SetRowHeight(sheetName, 4, 32)
	f.SetRowHeight(sheetName, 8, 36)

	f.SetRowHeight(sheetName, 2, 28)
	f.SetRowHeight(sheetName, 3, 28)
	f.SetRowHeight(sheetName, 5, 28)
	f.SetRowHeight(sheetName, 6, 28)
	f.SetRowHeight(sheetName, 7, 28)

	f.SetCellValue(sheetName, "A10", "历史修订")
	f.MergeCell(sheetName, "A10", "E10")
	f.SetCellValue(sheetName, "A11", "文档版本编号")
	f.SetCellValue(sheetName, "B11", "修订日期")
	f.SetCellValue(sheetName, "C11", "修订内容")
	f.SetCellValue(sheetName, "D11", "修订原因")
	f.SetCellValue(sheetName, "E11", "修订人")

	startRow := 12

	for _, x := range config.Histories {
		cell := strconv.Itoa(startRow)
		f.SetCellValue(sheetName, "A"+cell, x.Version)
		f.SetCellValue(sheetName, "B"+cell, x.Date)
		f.SetCellValue(sheetName, "C"+cell, x.Content)
		f.SetCellValue(sheetName, "D"+cell, x.Reason)
		f.SetCellValue(sheetName, "E"+cell, x.Author)
		startRow += 1
	}

	startRow += 1

	return f,startRow
}

// 组装每一行的数据
func parseColumnMap(f *excelize.File, sheetName string, columnMap map[string][]Column, startRow int) *excelize.File {
	for tableName, cols := range columnMap {
		f.SetCellValue(sheetName, "A" + strconv.Itoa(startRow), tableName)
		startRow += 1
		cell := strconv.Itoa(startRow)
		f.SetCellValue(sheetName, "A" + cell, "序号")
		f.SetCellValue(sheetName, "B" + cell, "名称")
		f.SetCellValue(sheetName, "C" + cell, "字段（代码）")
		f.SetCellValue(sheetName, "D" + cell, "数据类型（长度）")
		f.SetCellValue(sheetName, "E" + cell, "空")
		f.SetCellValue(sheetName, "F" + cell, "说明")
		f.SetCellValue(sheetName, "G" + cell, "是否必填")

		startRow += 1

		for i, col := range cols {
			cell = strconv.Itoa(startRow)
			f.SetCellValue(sheetName, "A" + cell, i+1)
			f.SetCellValue(sheetName, "B"+cell, col.ColumnName)
			f.SetCellValue(sheetName, "C"+cell, col.ColumnName)
			f.SetCellValue(sheetName, "D"+cell, col.ColumnType)
			f.SetCellValue(sheetName, "E"+cell, col.IsNullable)
			f.SetCellValue(sheetName, "F"+cell, col.ColumnComment)
			f.SetCellValue(sheetName, "G"+cell, col.IsNullable)
			startRow += 1
		}
		startRow += 2 // 空一行
	}

	return f
}