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
|
package datamaps
import (
"fmt"
"os"
"os/exec"
"path/filepath"
"strings"
"testing"
"github.com/tealeg/xlsx/v3"
)
var (
// filesInMaster is a map of each filename from the header row of a master, mapped to its
// column number
filesInMaster = make(map[string]int)
)
func testSetup() (*Options, error) {
// setup - we need the datamap in the test database
_, err := setupDB("./testdata/test.db")
if err != nil {
fmt.Errorf("Expected to be able to set up the database.")
}
opts := Options{
DBPath: "./testdata/test.db",
DMName: "First Datamap",
DMPath: "./testdata/datamap_for_master_test.csv",
ReturnName: "Unnamed Return",
MasterOutPutPath: "./testdata/",
XLSXPath: "./testdata/",
}
if err := DatamapToDB(&opts); err != nil {
fmt.Errorf("Unable to write datamap to database file because %v.", err)
}
if err := ImportToDB(&opts); err != nil {
fmt.Errorf("cannot read test XLSX files needed before exporting to master - %v", err)
}
return &opts, nil
}
func TestWriteMaster(t *testing.T) {
opts, err := testSetup()
if err != nil {
t.Fatal(err)
}
defer func() {
os.Remove(filepath.Join(opts.MasterOutPutPath, "master.xlsx"))
}()
defer func() {
os.Remove("./testdata/test.db")
}()
if err := CreateMaster(opts); err != nil {
t.Fatal(err)
}
var tests = []struct {
key string
filename string
sheet string
cellref string
value string
}{
{"A Date", "test_template.xlsx", "Summary", "B2", "20/10/19"},
{"A String", "test_template.xlsx", "Summary", "B3", "This is a string"},
{"A String2", "test_template.xlsx", "Summary", "C3", "This is a string"},
{"A String3", "test_template.xlsx", "Summary", "D3", "This is a string"},
{"A Float", "test_template.xlsx", "Summary", "B4", "2.2"},
{"An Integer", "test_template.xlsx", "Summary", "B5", "10"},
{"A Date 1", "test_template.xlsx", "Another Sheet", "B3", "20/10/19"},
{"A String 1", "test_template.xlsx", "Another Sheet", "B4", "This is a string"},
{"A Float 1", "test_template.xlsx", "Another Sheet", "B5", "2.2"},
{"An Integer 1", "test_template.xlsx", "Another Sheet", "B6", "10"},
{"A Date 2", "test_template.xlsx", "Another Sheet", "D3", "21/10/19"},
{"A String 2", "test_template.xlsx", "Another Sheet", "D4", "This is a string"},
{"A Float 3", "test_template.xlsx", "Another Sheet", "D5", "3.2"},
{"An Integer 3", "test_template.xlsx", "Another Sheet", "D6", "11"},
{"A Ten Integer", "test_template.xlsx", "Introduction", "A1", "10"},
{"A Test String", "test_template.xlsx", "Introduction", "C9", "Test Department"},
{"A Vunt String", "test_template.xlsx", "Introduction", "C22", "VUNT"},
{"A Parrot String", "test_template.xlsx", "Introduction", "J9", "Greedy Parrots"},
{"A Date", "test_template.xlsm", "Summary", "B2", "20/10/19"},
{"A String", "test_template.xlsm", "Summary", "B3", "This is a string"},
{"A String2", "test_template.xlsm", "Summary", "C3", "This is a string"},
{"A String3", "test_template.xlsm", "Summary", "D3", "This is a string"},
{"A Float", "test_template.xlsm", "Summary", "B4", "2.2"},
{"An Integer", "test_template.xlsm", "Summary", "B5", "10"},
{"A Date 1", "test_template.xlsm", "Another Sheet", "B3", "20/10/19"},
{"A String 1", "test_template.xlsm", "Another Sheet", "B4", "This is a string"},
{"A Float 1", "test_template.xlsm", "Another Sheet", "B5", "2.2"},
{"An Integer 1", "test_template.xlsm", "Another Sheet", "B6", "10"},
{"A Date 2", "test_template.xlsm", "Another Sheet", "D3", "21/10/19"},
{"A String 2", "test_template.xlsm", "Another Sheet", "D4", "This is a string"},
{"A Float 3", "test_template.xlsm", "Another Sheet", "D5", "3.2"},
{"An Integer 3", "test_template.xlsm", "Another Sheet", "D6", "11"},
{"A Ten Integer", "test_template.xlsm", "Introduction", "A1", "10"},
{"A Test String", "test_template.xlsm", "Introduction", "C9", "Test Department"},
{"A Vunt String", "test_template.xlsm", "Introduction", "C22", "VUNT"},
{"A Parrot String", "test_template.xlsm", "Introduction", "J9", "Greedy Parrots"},
}
// Regular testing of import
// TODO fix date formatting
for _, test := range tests {
sql := fmt.Sprintf(`SELECT return_data.vFormatted FROM return_data, datamap_line
WHERE
(return_data.filename=%q
AND datamap_line.cellref=%q
AND datamap_line.sheet=%q
AND return_data.dml_id=datamap_line.id);`, test.filename, test.cellref, test.sheet)
got, err := exec.Command("sqlite3", opts.DBPath, sql).Output()
if err != nil {
t.Fatalf("something wrong %v", err)
}
got_s := strings.TrimSuffix(string(got), "\n")
if strings.Compare(got_s, test.value) != 0 {
t.Errorf("when testing the database, for key %s in file %s we expected %s but got %s", test.key,
test.filename, test.value, got_s)
}
}
// Open the master and the target sheet
master, err := xlsx.OpenFile("./testdata/master.xlsx")
if err != nil {
t.Fatal(err)
}
sheetName := "Master Data"
sh, ok := master.Sheet[sheetName]
if !ok {
t.Errorf("Sheet named %s does not exist", sheetName)
}
defer sh.Close()
err = sh.ForEachRow(rowVisitorTest)
for _, tt := range tests {
got, err := masterLookup(sh, tt.key, tt.filename)
if err != nil {
t.Fatal("Problem calling masterLookup()")
}
if got != tt.value {
t.Errorf("when testing the master, for key %s we expected value %s in col %s - got %s",
tt.key, tt.value, tt.filename, got)
}
}
}
func masterLookup(sheet *xlsx.Sheet, key string, filename string) (string, error) {
var out string
if err := sheet.ForEachRow(func(r *xlsx.Row) error {
if r.GetCell(0).Value == key {
out = r.GetCell(filesInMaster[filename]).Value
return nil
}
return nil
}); err != nil {
return "", err
}
return out, nil
}
func cellVisitorTest(c *xlsx.Cell) error {
seen := make(map[string]struct{})
if _, ok := seen[c.Value]; !ok {
if c.Value != "" {
x, _ := c.GetCoordinates()
filesInMaster[c.Value] = x
}
seen[c.Value] = struct{}{}
}
return nil
}
func rowVisitorTest(r *xlsx.Row) error {
// TODO here we want to first find the file names from the header row,
// then test that all key (from col 0) matches the value.
if r.GetCoordinate() == 0 {
r.ForEachCell(cellVisitorTest)
return nil
}
return nil
}
|
