forked from qax-os/excelize
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathtable_test.go
219 lines (205 loc) · 8.98 KB
/
table_test.go
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
package excelize
import (
"fmt"
"path/filepath"
"strings"
"testing"
"github.com/stretchr/testify/assert"
)
func TestAddTable(t *testing.T) {
f, err := prepareTestBook1()
assert.NoError(t, err)
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "B26:A21"}))
assert.NoError(t, f.AddTable("Sheet2", &Table{
Range: "A2:B5",
Name: "table",
StyleName: "TableStyleMedium2",
ShowColumnStripes: true,
ShowFirstColumn: true,
ShowLastColumn: true,
ShowRowStripes: boolPtr(true),
}))
assert.NoError(t, f.AddTable("Sheet2", &Table{
Range: "D1:D11",
ShowHeaderRow: boolPtr(false),
}))
assert.NoError(t, f.AddTable("Sheet2", &Table{Range: "F1:F1", StyleName: "TableStyleMedium8"}))
// Test get tables in worksheet
tables, err := f.GetTables("Sheet2")
assert.Len(t, tables, 3)
assert.NoError(t, err)
// Test add table with already exist table name
assert.Equal(t, f.AddTable("Sheet2", &Table{Name: "Table1"}), ErrExistsTableName)
// Test add table with invalid table options
assert.Equal(t, f.AddTable("Sheet1", nil), ErrParameterInvalid)
// Test add table in not exist worksheet
assert.EqualError(t, f.AddTable("SheetN", &Table{Range: "B26:A21"}), "sheet SheetN does not exist")
// Test add table with illegal cell reference
assert.Equal(t, f.AddTable("Sheet1", &Table{Range: "A:B1"}), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")))
assert.Equal(t, f.AddTable("Sheet1", &Table{Range: "A1:B"}), newCellNameToCoordinatesError("B", newInvalidCellNameError("B")))
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddTable.xlsx")))
// Test add table with invalid sheet name
assert.EqualError(t, f.AddTable("Sheet:1", &Table{Range: "B26:A21"}), ErrSheetNameInvalid.Error())
// Test addTable with illegal cell reference
f = NewFile()
assert.Equal(t, newCoordinatesToCellNameError(0, 0), f.addTable("sheet1", "", 0, 0, 0, 0, 0, nil))
assert.Equal(t, newCoordinatesToCellNameError(0, 0), f.addTable("sheet1", "", 1, 1, 0, 0, 0, nil))
// Test set defined name and add table with invalid name
for _, cases := range []struct {
name string
err error
}{
{name: "1Table", err: newInvalidNameError("1Table")},
{name: "-Table", err: newInvalidNameError("-Table")},
{name: "'Table", err: newInvalidNameError("'Table")},
{name: "Table 1", err: newInvalidNameError("Table 1")},
{name: "A&B", err: newInvalidNameError("A&B")},
{name: "_1Table'", err: newInvalidNameError("_1Table'")},
{name: "\u0f5f\u0fb3\u0f0b\u0f21", err: newInvalidNameError("\u0f5f\u0fb3\u0f0b\u0f21")},
{name: strings.Repeat("c", MaxFieldLength+1), err: ErrNameLength},
} {
assert.EqualError(t, f.AddTable("Sheet1", &Table{
Range: "A1:B2",
Name: cases.name,
}), cases.err.Error())
assert.EqualError(t, f.SetDefinedName(&DefinedName{
Name: cases.name, RefersTo: "Sheet1!$A$2:$D$5",
}), cases.err.Error())
}
// Test check duplicate table name with unsupported charset table parts
f = NewFile()
f.Pkg.Store("xl/tables/table1.xml", MacintoshCyrillicCharset)
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "A1:B2"}))
assert.NoError(t, f.Close())
f = NewFile()
// Test add table with workbook with single cells parts
f.Pkg.Store("xl/tables/tableSingleCells1.xml", []byte("<singleXmlCells><singleXmlCell id=\"2\" r=\"A1\" connectionId=\"2\" /></singleXmlCells>"))
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "A1:B2"}))
// Test add table with workbook with unsupported charset single cells parts
f.Pkg.Store("xl/tables/tableSingleCells1.xml", MacintoshCyrillicCharset)
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "A1:B2"}))
assert.NoError(t, f.Close())
}
func TestGetTables(t *testing.T) {
f := NewFile()
// Test get tables in none table worksheet
tables, err := f.GetTables("Sheet1")
assert.Len(t, tables, 0)
assert.NoError(t, err)
// Test get tables in not exist worksheet
_, err = f.GetTables("SheetN")
assert.EqualError(t, err, "sheet SheetN does not exist")
// Test adjust table with unsupported charset
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "B26:A21"}))
f.Pkg.Store("xl/tables/table1.xml", MacintoshCyrillicCharset)
_, err = f.GetTables("Sheet1")
assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
// Test adjust table with no exist table parts
f.Pkg.Delete("xl/tables/table1.xml")
tables, err = f.GetTables("Sheet1")
assert.Len(t, tables, 0)
assert.NoError(t, err)
}
func TestDeleteTable(t *testing.T) {
f := NewFile()
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "A1:B4", Name: "Table1"}))
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "B26:A21", Name: "Table2"}))
assert.NoError(t, f.DeleteTable("Table2"))
assert.NoError(t, f.DeleteTable("Table1"))
// Test delete table with invalid table name
assert.EqualError(t, f.DeleteTable("Table 1"), newInvalidNameError("Table 1").Error())
// Test delete table with no exist table name
assert.EqualError(t, f.DeleteTable("Table"), newNoExistTableError("Table").Error())
// Test delete table with unsupported charset
f.Sheet.Delete("xl/worksheets/sheet1.xml")
f.Pkg.Store("xl/worksheets/sheet1.xml", MacintoshCyrillicCharset)
assert.EqualError(t, f.DeleteTable("Table1"), "XML syntax error on line 1: invalid UTF-8")
// Test delete table with invalid table range
f = NewFile()
assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "A1:B4", Name: "Table1"}))
f.Pkg.Store("xl/tables/table1.xml", []byte("<table name=\"Table1\" ref=\"-\" />"))
assert.EqualError(t, f.DeleteTable("Table1"), ErrParameterInvalid.Error())
}
func TestSetTableHeader(t *testing.T) {
f := NewFile()
_, err := f.setTableHeader("Sheet1", true, 1, 0, 1)
assert.Equal(t, newCoordinatesToCellNameError(1, 0), err)
}
func TestAutoFilter(t *testing.T) {
outFile := filepath.Join("test", "TestAutoFilter%d.xlsx")
f, err := prepareTestBook1()
assert.NoError(t, err)
for i, opts := range [][]AutoFilterOptions{
{},
{{Column: "B", Expression: ""}},
{{Column: "B", Expression: "x != blanks"}},
{{Column: "B", Expression: "x == blanks"}},
{{Column: "B", Expression: "x != nonblanks"}},
{{Column: "B", Expression: "x == nonblanks"}},
{{Column: "B", Expression: "x <= 1 and x >= 2"}},
{{Column: "B", Expression: "x == 1 or x == 2"}},
{{Column: "B", Expression: "x == 1 or x == 2*"}},
} {
t.Run(fmt.Sprintf("Expression%d", i+1), func(t *testing.T) {
assert.NoError(t, f.AutoFilter("Sheet1", "D4:B1", opts))
assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, i+1)))
})
}
// Test add auto filter with invalid sheet name
assert.EqualError(t, f.AutoFilter("Sheet:1", "A1:B1", nil), ErrSheetNameInvalid.Error())
// Test add auto filter with illegal cell reference
assert.EqualError(t, f.AutoFilter("Sheet1", "A:B1", nil), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
assert.EqualError(t, f.AutoFilter("Sheet1", "A1:B", nil), newCellNameToCoordinatesError("B", newInvalidCellNameError("B")).Error())
// Test add auto filter with unsupported charset workbook
f.WorkBook = nil
f.Pkg.Store(defaultXMLPathWorkbook, MacintoshCyrillicCharset)
assert.EqualError(t, f.AutoFilter("Sheet1", "D4:B1", nil), "XML syntax error on line 1: invalid UTF-8")
// Test add auto filter with empty local sheet ID
f = NewFile()
f.WorkBook = &xlsxWorkbook{DefinedNames: &xlsxDefinedNames{DefinedName: []xlsxDefinedName{{Name: builtInDefinedNames[2], Hidden: true}}}}
assert.NoError(t, f.AutoFilter("Sheet1", "A1:B1", nil))
}
func TestAutoFilterError(t *testing.T) {
outFile := filepath.Join("test", "TestAutoFilterError%d.xlsx")
f, err := prepareTestBook1()
assert.NoError(t, err)
for i, opts := range [][]AutoFilterOptions{
{{Column: "B", Expression: "x <= 1 and x >= blanks"}},
{{Column: "B", Expression: "x -- y or x == *2*"}},
{{Column: "B", Expression: "x != y or x ? *2"}},
{{Column: "B", Expression: "x -- y o r x == *2"}},
{{Column: "B", Expression: "x -- y"}},
{{Column: "A", Expression: "x -- y"}},
} {
t.Run(fmt.Sprintf("Expression%d", i+1), func(t *testing.T) {
if assert.Error(t, f.AutoFilter("Sheet2", "D4:B1", opts)) {
assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, i+1)))
}
})
}
assert.Equal(t, ErrSheetNotExist{"SheetN"}, f.autoFilter("SheetN", "A1", 1, 1, []AutoFilterOptions{{
Column: "A",
Expression: "",
}}))
assert.Equal(t, newInvalidColumnNameError("-"), f.autoFilter("Sheet1", "A1", 1, 1, []AutoFilterOptions{{
Column: "-",
Expression: "-",
}}))
assert.Equal(t, newInvalidAutoFilterColumnError("A"), f.autoFilter("Sheet1", "A1", 1, 100, []AutoFilterOptions{{
Column: "A",
Expression: "-",
}}))
assert.Equal(t, newInvalidAutoFilterExpError("-"), f.autoFilter("Sheet1", "A1", 1, 1, []AutoFilterOptions{{
Column: "A",
Expression: "-",
}}))
}
func TestParseFilterTokens(t *testing.T) {
f := NewFile()
// Test with unknown operator
_, _, err := f.parseFilterTokens("", []string{"", "!"})
assert.EqualError(t, err, "unknown operator: !")
// Test invalid operator in context
_, _, err = f.parseFilterTokens("", []string{"", "<", "x != blanks"})
assert.Equal(t, newInvalidAutoFilterOperatorError("<", ""), err)
}