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
|
package excelize
import (
"fmt"
"strings"
)
type DataValidationType int
// Data validation types
const (
_DataValidationType = iota
typeNone //inline use
DataValidationTypeCustom
DataValidationTypeDate
DataValidationTypeDecimal
typeList //inline use
DataValidationTypeTextLeng
DataValidationTypeTime
// DataValidationTypeWhole Integer
DataValidationTypeWhole
)
const (
// dataValidationFormulaStrLen 255 characters+ 2 quotes
dataValidationFormulaStrLen = 257
// dataValidationFormulaStrLenErr
dataValidationFormulaStrLenErr = "data validation must be 0-255 characters"
)
type DataValidationErrorStyle int
// Data validation error styles
const (
_ DataValidationErrorStyle = iota
DataValidationStyleStop
DataValidationStyleWarning
DataValidationStyleInformation
)
// Data validation error styles
const (
styleStop = "stop"
styleWarning = "warning"
styleInformation = "information"
)
// DataValidationOperator operator enum
type DataValidationOperator int
// Data validation operators
const (
_DataValidationOperator = iota
DataValidationOperatorBetween
DataValidationOperatorEqual
DataValidationOperatorGreaterThan
DataValidationOperatorGreaterThanOrEqual
DataValidationOperatorLessThan
DataValidationOperatorLessThanOrEqual
DataValidationOperatorNotBetween
DataValidationOperatorNotEqual
)
// NewDataValidation return data validation struct
func NewDataValidation(allowBlank bool) *DataValidation {
return &DataValidation{
AllowBlank: convBoolToStr(allowBlank),
ShowErrorMessage: convBoolToStr(false),
ShowInputMessage: convBoolToStr(false),
}
}
// SetError set error notice
func (dd *DataValidation) SetError(style DataValidationErrorStyle, title, msg *string) {
dd.Error = msg
dd.ErrorTitle = title
strStyle := styleStop
switch style {
case DataValidationStyleStop:
strStyle = styleStop
case DataValidationStyleWarning:
strStyle = styleWarning
case DataValidationStyleInformation:
strStyle = styleInformation
}
dd.ShowErrorMessage = convBoolToStr(true)
dd.ErrorStyle = &strStyle
}
// SetInput set prompt notice
func (dd *DataValidation) SetInput(title, msg *string) {
dd.ShowInputMessage = convBoolToStr(true)
dd.PromptTitle = title
dd.Prompt = msg
}
// SetDropList data validation list
func (dd *DataValidation) SetDropList(keys []string) error {
dd.Formula1 = "\"" + strings.Join(keys, ",") + "\""
dd.Type = convDataValidationType(typeList)
return nil
}
// SetDropList data validation range
func (dd *DataValidation) SetRange(f1, f2 int, t DataValidationType, o DataValidationOperator) error {
formula1 := fmt.Sprintf("%d", f1)
formula2 := fmt.Sprintf("%d", f2)
if dataValidationFormulaStrLen < len(dd.Formula1) || dataValidationFormulaStrLen < len(dd.Formula2) {
return fmt.Errorf(dataValidationFormulaStrLenErr)
}
switch o {
case DataValidationOperatorBetween:
if f1 > f2 {
tmp := formula1
formula1 = formula2
formula2 = tmp
}
case DataValidationOperatorNotBetween:
if f1 > f2 {
tmp := formula1
formula1 = formula2
formula2 = tmp
}
}
dd.Formula1 = formula1
dd.Formula2 = formula2
dd.Type = convDataValidationType(t)
dd.Operator = convDataValidationOperatior(o)
return nil
}
// SetDropList data validation range
func (dd *DataValidation) SetSqref(sqref string) {
if dd.Sqref == "" {
dd.Sqref = sqref
} else {
dd.Sqref = fmt.Sprintf("%s %s", dd.Sqref, sqref)
}
}
// convBoolToStr convert boolean to string , false to 0, true to 1
func convBoolToStr(bl bool) string {
if bl {
return "1"
}
return "0"
}
// convDataValidationType get excel data validation type
func convDataValidationType(t DataValidationType) string {
typeMap := map[DataValidationType]string{
typeNone: "none",
DataValidationTypeCustom: "custom",
DataValidationTypeDate: "date",
DataValidationTypeDecimal: "decimal",
typeList: "list",
DataValidationTypeTextLeng: "textLength",
DataValidationTypeTime: "time",
DataValidationTypeWhole: "whole",
}
return typeMap[t]
}
// convDataValidationOperatior get excel data validation operator
func convDataValidationOperatior(o DataValidationOperator) string {
typeMap := map[DataValidationOperator]string{
DataValidationOperatorBetween: "between",
DataValidationOperatorEqual: "equal",
DataValidationOperatorGreaterThan: "greaterThan",
DataValidationOperatorGreaterThanOrEqual: "greaterThanOrEqual",
DataValidationOperatorLessThan: "lessThan",
DataValidationOperatorLessThanOrEqual: "lessThanOrEqual",
DataValidationOperatorNotBetween: "notBetween",
DataValidationOperatorNotEqual: "notEqual",
}
return typeMap[o]
}
func (f *File) AddDataValidation(sheet string, dv *DataValidation) {
xlsx := f.workSheetReader(sheet)
if nil == xlsx.DataValidations {
xlsx.DataValidations = new(xlsxDataValidations)
}
xlsx.DataValidations.DataValidation = append(xlsx.DataValidations.DataValidation, dv)
xlsx.DataValidations.Count = len(xlsx.DataValidations.DataValidation)
}
func (f *File) GetDataValidation(sheet, sqref string) {
}
|
