diff options
| -rw-r--r-- | datavalidation.go | 196 | ||||
| -rw-r--r-- | xmlWorksheet.go | 26 |
2 files changed, 217 insertions, 5 deletions
diff --git a/datavalidation.go b/datavalidation.go new file mode 100644 index 0000000..5dae7c9 --- /dev/null +++ b/datavalidation.go @@ -0,0 +1,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) { + +} diff --git a/xmlWorksheet.go b/xmlWorksheet.go index 87d66a1..f2ac9fb 100644 --- a/xmlWorksheet.go +++ b/xmlWorksheet.go @@ -294,11 +294,27 @@ type xlsxMergeCells struct { // xlsxDataValidations expresses all data validation information for cells in a // sheet which have data validation features applied. type xlsxDataValidations struct { - Count int `xml:"count,attr,omitempty"` - DisablePrompts bool `xml:"disablePrompts,attr,omitempty"` - XWindow int `xml:"xWindow,attr,omitempty"` - YWindow int `xml:"yWindow,attr,omitempty"` - DataValidation string `xml:",innerxml"` + Count int `xml:"count,attr,omitempty"` + DisablePrompts bool `xml:"disablePrompts,attr,omitempty"` + XWindow int `xml:"xWindow,attr,omitempty"` + YWindow int `xml:"yWindow,attr,omitempty"` + DataValidation []*DataValidation `xml:"dataValidation,innerxml"` +} + +type DataValidation struct { + AllowBlank string `xml:"allowBlank,attr"` // allow empty + ShowInputMessage string `xml:"showInputMessage,attr"` // 1, true,0,false, select cell, Whether the input message is displayed + ShowErrorMessage string `xml:"showErrorMessage,attr"` // 1, true,0,false, input error value, Whether the error message is displayed + ErrorStyle *string `xml:"errorStyle,attr"` //error icon style, warning, infomation,stop + ErrorTitle *string `xml:"errorTitle,attr"` // error title + Operator string `xml:"operator,attr"` // + Error *string `xml:"error,attr"` // input error value, notice message + PromptTitle *string `xml:"promptTitle"` + Prompt *string `xml:"prompt,attr"` + Type string `xml:"type,attr"` //data type, none,custom,date,decimal,list, textLength,time,whole + Sqref string `xml:"sqref,attr"` //Validity of data validation rules, cell and range, eg: A1 OR A1:A20 + Formula1 string `xml:"formula1"` // data validation role + Formula2 string `xml:"formula2"` //data validation role } // xlsxC directly maps the c element in the namespace |