You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

242 lines
7.8KB

  1. // Copyright 2016 - 2019 The excelize Authors. All rights reserved. Use of
  2. // this source code is governed by a BSD-style license that can be found in
  3. // the LICENSE file.
  4. //
  5. // Package excelize providing a set of functions that allow you to write to
  6. // and read from XLSX files. Support reads and writes XLSX file generated by
  7. // Microsoft Excel™ 2007 and later. Support save file without losing original
  8. // charts of XLSX. This library needs Go version 1.10 or later.
  9. package excelize
  10. import (
  11. "fmt"
  12. "strings"
  13. )
  14. // DataValidationType defined the type of data validation.
  15. type DataValidationType int
  16. // Data validation types.
  17. const (
  18. _DataValidationType = iota
  19. typeNone // inline use
  20. DataValidationTypeCustom
  21. DataValidationTypeDate
  22. DataValidationTypeDecimal
  23. typeList // inline use
  24. DataValidationTypeTextLeng
  25. DataValidationTypeTime
  26. // DataValidationTypeWhole Integer
  27. DataValidationTypeWhole
  28. )
  29. const (
  30. // dataValidationFormulaStrLen 255 characters+ 2 quotes
  31. dataValidationFormulaStrLen = 257
  32. // dataValidationFormulaStrLenErr
  33. dataValidationFormulaStrLenErr = "data validation must be 0-255 characters"
  34. )
  35. // DataValidationErrorStyle defined the style of data validation error alert.
  36. type DataValidationErrorStyle int
  37. // Data validation error styles.
  38. const (
  39. _ DataValidationErrorStyle = iota
  40. DataValidationErrorStyleStop
  41. DataValidationErrorStyleWarning
  42. DataValidationErrorStyleInformation
  43. )
  44. // Data validation error styles.
  45. const (
  46. styleStop = "stop"
  47. styleWarning = "warning"
  48. styleInformation = "information"
  49. )
  50. // DataValidationOperator operator enum.
  51. type DataValidationOperator int
  52. // Data validation operators.
  53. const (
  54. _DataValidationOperator = iota
  55. DataValidationOperatorBetween
  56. DataValidationOperatorEqual
  57. DataValidationOperatorGreaterThan
  58. DataValidationOperatorGreaterThanOrEqual
  59. DataValidationOperatorLessThan
  60. DataValidationOperatorLessThanOrEqual
  61. DataValidationOperatorNotBetween
  62. DataValidationOperatorNotEqual
  63. )
  64. // NewDataValidation return data validation struct.
  65. func NewDataValidation(allowBlank bool) *DataValidation {
  66. return &DataValidation{
  67. AllowBlank: allowBlank,
  68. ShowErrorMessage: false,
  69. ShowInputMessage: false,
  70. }
  71. }
  72. // SetError set error notice.
  73. func (dd *DataValidation) SetError(style DataValidationErrorStyle, title, msg string) {
  74. dd.Error = &msg
  75. dd.ErrorTitle = &title
  76. strStyle := styleStop
  77. switch style {
  78. case DataValidationErrorStyleStop:
  79. strStyle = styleStop
  80. case DataValidationErrorStyleWarning:
  81. strStyle = styleWarning
  82. case DataValidationErrorStyleInformation:
  83. strStyle = styleInformation
  84. }
  85. dd.ShowErrorMessage = true
  86. dd.ErrorStyle = &strStyle
  87. }
  88. // SetInput set prompt notice.
  89. func (dd *DataValidation) SetInput(title, msg string) {
  90. dd.ShowInputMessage = true
  91. dd.PromptTitle = &title
  92. dd.Prompt = &msg
  93. }
  94. // SetDropList data validation list.
  95. func (dd *DataValidation) SetDropList(keys []string) error {
  96. formula := "\"" + strings.Join(keys, ",") + "\""
  97. if dataValidationFormulaStrLen < len(formula) {
  98. return fmt.Errorf(dataValidationFormulaStrLenErr)
  99. }
  100. dd.Formula1 = fmt.Sprintf("<formula1>%s</formula1>", formula)
  101. dd.Type = convDataValidationType(typeList)
  102. return nil
  103. }
  104. // SetRange provides function to set data validation range in drop list.
  105. func (dd *DataValidation) SetRange(f1, f2 int, t DataValidationType, o DataValidationOperator) error {
  106. formula1 := fmt.Sprintf("%d", f1)
  107. formula2 := fmt.Sprintf("%d", f2)
  108. if dataValidationFormulaStrLen+21 < len(dd.Formula1) || dataValidationFormulaStrLen+21 < len(dd.Formula2) {
  109. return fmt.Errorf(dataValidationFormulaStrLenErr)
  110. }
  111. dd.Formula1 = fmt.Sprintf("<formula1>%s</formula1>", formula1)
  112. dd.Formula2 = fmt.Sprintf("<formula2>%s</formula2>", formula2)
  113. dd.Type = convDataValidationType(t)
  114. dd.Operator = convDataValidationOperatior(o)
  115. return nil
  116. }
  117. // SetSqrefDropList provides set data validation on a range with source
  118. // reference range of the worksheet by given data validation object and
  119. // worksheet name. The data validation object can be created by
  120. // NewDataValidation function. For example, set data validation on
  121. // Sheet1!A7:B8 with validation criteria source Sheet1!E1:E3 settings, create
  122. // in-cell dropdown by allowing list source:
  123. //
  124. // dvRange := excelize.NewDataValidation(true)
  125. // dvRange.Sqref = "A7:B8"
  126. // dvRange.SetSqrefDropList("$E$1:$E$3", true)
  127. // f.AddDataValidation("Sheet1", dvRange)
  128. //
  129. func (dd *DataValidation) SetSqrefDropList(sqref string, isCurrentSheet bool) error {
  130. if isCurrentSheet {
  131. dd.Formula1 = fmt.Sprintf("<formula1>%s</formula1>", sqref)
  132. dd.Type = convDataValidationType(typeList)
  133. return nil
  134. }
  135. return fmt.Errorf("cross-sheet sqref cell are not supported")
  136. }
  137. // SetSqref provides function to set data validation range in drop list.
  138. func (dd *DataValidation) SetSqref(sqref string) {
  139. if dd.Sqref == "" {
  140. dd.Sqref = sqref
  141. } else {
  142. dd.Sqref = fmt.Sprintf("%s %s", dd.Sqref, sqref)
  143. }
  144. }
  145. // convDataValidationType get excel data validation type.
  146. func convDataValidationType(t DataValidationType) string {
  147. typeMap := map[DataValidationType]string{
  148. typeNone: "none",
  149. DataValidationTypeCustom: "custom",
  150. DataValidationTypeDate: "date",
  151. DataValidationTypeDecimal: "decimal",
  152. typeList: "list",
  153. DataValidationTypeTextLeng: "textLength",
  154. DataValidationTypeTime: "time",
  155. DataValidationTypeWhole: "whole",
  156. }
  157. return typeMap[t]
  158. }
  159. // convDataValidationOperatior get excel data validation operator.
  160. func convDataValidationOperatior(o DataValidationOperator) string {
  161. typeMap := map[DataValidationOperator]string{
  162. DataValidationOperatorBetween: "between",
  163. DataValidationOperatorEqual: "equal",
  164. DataValidationOperatorGreaterThan: "greaterThan",
  165. DataValidationOperatorGreaterThanOrEqual: "greaterThanOrEqual",
  166. DataValidationOperatorLessThan: "lessThan",
  167. DataValidationOperatorLessThanOrEqual: "lessThanOrEqual",
  168. DataValidationOperatorNotBetween: "notBetween",
  169. DataValidationOperatorNotEqual: "notEqual",
  170. }
  171. return typeMap[o]
  172. }
  173. // AddDataValidation provides set data validation on a range of the worksheet
  174. // by given data validation object and worksheet name. The data validation
  175. // object can be created by NewDataValidation function.
  176. //
  177. // Example 1, set data validation on Sheet1!A1:B2 with validation criteria
  178. // settings, show error alert after invalid data is entered with "Stop" style
  179. // and custom title "error body":
  180. //
  181. // dvRange := excelize.NewDataValidation(true)
  182. // dvRange.Sqref = "A1:B2"
  183. // dvRange.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorBetween)
  184. // dvRange.SetError(excelize.DataValidationErrorStyleStop, "error title", "error body")
  185. // err := f.AddDataValidation("Sheet1", dvRange)
  186. //
  187. // Example 2, set data validation on Sheet1!A3:B4 with validation criteria
  188. // settings, and show input message when cell is selected:
  189. //
  190. // dvRange = excelize.NewDataValidation(true)
  191. // dvRange.Sqref = "A3:B4"
  192. // dvRange.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorGreaterThan)
  193. // dvRange.SetInput("input title", "input body")
  194. // err = f.AddDataValidation("Sheet1", dvRange)
  195. //
  196. // Example 3, set data validation on Sheet1!A5:B6 with validation criteria
  197. // settings, create in-cell dropdown by allowing list source:
  198. //
  199. // dvRange = excelize.NewDataValidation(true)
  200. // dvRange.Sqref = "A5:B6"
  201. // dvRange.SetDropList([]string{"1", "2", "3"})
  202. // err = f.AddDataValidation("Sheet1", dvRange)
  203. //
  204. func (f *File) AddDataValidation(sheet string, dv *DataValidation) error {
  205. xlsx, err := f.workSheetReader(sheet)
  206. if err != nil {
  207. return err
  208. }
  209. if nil == xlsx.DataValidations {
  210. xlsx.DataValidations = new(xlsxDataValidations)
  211. }
  212. xlsx.DataValidations.DataValidation = append(xlsx.DataValidations.DataValidation, dv)
  213. xlsx.DataValidations.Count = len(xlsx.DataValidations.DataValidation)
  214. return err
  215. }