导出数据表结构到Excel

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


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)
}

:::

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69


// 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"
}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230


// 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
}