· 8 years ago · Jan 30, 2018, 04:56 PM
1package main
2
3import (
4 "fmt"
5 "io/ioutil"
6 "os"
7 "strings"
8
9 "github.com/tealeg/xlsx"
10)
11
12const FLG_ON = "â—‹"
13
14const ROW_TABLE_NAME = 1
15const CLM_TABLE_NAME = 2 // C
16
17const ROW_START = 4
18
19const CLM_NAME = 2 // C
20const CLM_TYPE = 3 // D
21const CLM_PK = 4 // E
22const CLM_NN = 5 // F
23
24func main() {
25 dirName := "/home/tables"
26 xlFile, err := xlsx.OpenFile(dirName + "/table.xlsx")
27 if err != nil {
28 fmt.Println("file open error.")
29 return
30 }
31 for _, sheet := range xlFile.Sheets {
32 tableName := sheet.Cell(ROW_TABLE_NAME, CLM_TABLE_NAME).String()
33 sqlStr, _ := createSQL(sheet, tableName)
34 ioutil.WriteFile(dirName+"/"+tableName+".sql", []byte(sqlStr), os.ModePerm)
35 }
36}
37
38func createSQL(s *xlsx.Sheet, tableName string) (string, error) {
39 var pkArray []string
40
41 sqlStr := "DROP TABLE IF EXISTS " + tableName + ";\n"
42 sqlStr += "CREATE TABLE `" + tableName + "` (\n"
43
44 for i, r := range s.Rows {
45 if i < ROW_START {
46 continue
47 }
48 if r.Cells[CLM_NAME].String() == "" {
49 break
50 }
51
52 sqlStr += " `" + r.Cells[CLM_NAME].String() + "` "
53 sqlStr += r.Cells[CLM_TYPE].String() + " "
54
55 if r.Cells[CLM_NN].String() == FLG_ON {
56 sqlStr += "NOT NULL,\n"
57 } else {
58 sqlStr += "DEFAULT NULL,\n"
59 }
60
61 if r.Cells[CLM_PK].String() == FLG_ON {
62 pkArray = append(pkArray, r.Cells[CLM_NAME].String())
63 }
64 }
65
66 sqlStr += " PRIMARY KEY (" + strings.Join(pkArray, ", ") + ")\n);"
67 return sqlStr, nil
68}