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.
 
 
 
 

105 lines
3.9 KiB

  1. // Copyright 2016 - 2020 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. "path/filepath"
  12. "strings"
  13. "testing"
  14. "github.com/stretchr/testify/assert"
  15. )
  16. func TestDataValidation(t *testing.T) {
  17. resultFile := filepath.Join("test", "TestDataValidation.xlsx")
  18. f := NewFile()
  19. dvRange := NewDataValidation(true)
  20. dvRange.Sqref = "A1:B2"
  21. assert.NoError(t, dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorBetween))
  22. dvRange.SetError(DataValidationErrorStyleStop, "error title", "error body")
  23. dvRange.SetError(DataValidationErrorStyleWarning, "error title", "error body")
  24. dvRange.SetError(DataValidationErrorStyleInformation, "error title", "error body")
  25. assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
  26. assert.NoError(t, f.SaveAs(resultFile))
  27. dvRange = NewDataValidation(true)
  28. dvRange.Sqref = "A3:B4"
  29. assert.NoError(t, dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorGreaterThan))
  30. dvRange.SetInput("input title", "input body")
  31. assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
  32. assert.NoError(t, f.SaveAs(resultFile))
  33. dvRange = NewDataValidation(true)
  34. dvRange.Sqref = "A5:B6"
  35. assert.NoError(t, dvRange.SetDropList([]string{"1", "2", "3"}))
  36. assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
  37. assert.NoError(t, f.SaveAs(resultFile))
  38. }
  39. func TestDataValidationError(t *testing.T) {
  40. resultFile := filepath.Join("test", "TestDataValidationError.xlsx")
  41. f := NewFile()
  42. assert.NoError(t, f.SetCellStr("Sheet1", "E1", "E1"))
  43. assert.NoError(t, f.SetCellStr("Sheet1", "E2", "E2"))
  44. assert.NoError(t, f.SetCellStr("Sheet1", "E3", "E3"))
  45. dvRange := NewDataValidation(true)
  46. dvRange.SetSqref("A7:B8")
  47. dvRange.SetSqref("A7:B8")
  48. assert.NoError(t, dvRange.SetSqrefDropList("$E$1:$E$3", true))
  49. err := dvRange.SetSqrefDropList("$E$1:$E$3", false)
  50. assert.EqualError(t, err, "cross-sheet sqref cell are not supported")
  51. assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
  52. assert.NoError(t, f.SaveAs(resultFile))
  53. dvRange = NewDataValidation(true)
  54. err = dvRange.SetDropList(make([]string, 258))
  55. if dvRange.Formula1 != "" {
  56. t.Errorf("data validation error. Formula1 must be empty!")
  57. return
  58. }
  59. assert.EqualError(t, err, "data validation must be 0-255 characters")
  60. assert.NoError(t, dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorGreaterThan))
  61. dvRange.SetSqref("A9:B10")
  62. assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
  63. assert.NoError(t, f.SaveAs(resultFile))
  64. // Test width invalid data validation formula.
  65. dvRange.Formula1 = strings.Repeat("s", dataValidationFormulaStrLen+22)
  66. assert.EqualError(t, dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorGreaterThan), "data validation must be 0-255 characters")
  67. // Test add data validation on no exists worksheet.
  68. f = NewFile()
  69. assert.EqualError(t, f.AddDataValidation("SheetN", nil), "sheet SheetN is not exist")
  70. }
  71. func TestDeleteDataValidation(t *testing.T) {
  72. f := NewFile()
  73. assert.NoError(t, f.DeleteDataValidation("Sheet1", "A1:B2"))
  74. dvRange := NewDataValidation(true)
  75. dvRange.Sqref = "A1:B2"
  76. assert.NoError(t, dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorBetween))
  77. dvRange.SetInput("input title", "input body")
  78. assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
  79. assert.NoError(t, f.DeleteDataValidation("Sheet1", "A1:B2"))
  80. assert.NoError(t, f.SaveAs(filepath.Join("test", "TestDeleteDataValidation.xlsx")))
  81. // Test delete data validation on no exists worksheet.
  82. assert.EqualError(t, f.DeleteDataValidation("SheetN", "A1:B2"), "sheet SheetN is not exist")
  83. }