merge_test.go 5.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169
  1. package excelize
  2. import (
  3. "path/filepath"
  4. "testing"
  5. "github.com/stretchr/testify/assert"
  6. )
  7. func TestMergeCell(t *testing.T) {
  8. f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
  9. if !assert.NoError(t, err) {
  10. t.FailNow()
  11. }
  12. assert.EqualError(t, f.MergeCell("Sheet1", "A", "B"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
  13. assert.NoError(t, f.MergeCell("Sheet1", "D9", "D9"))
  14. assert.NoError(t, f.MergeCell("Sheet1", "D9", "E9"))
  15. assert.NoError(t, f.MergeCell("Sheet1", "H14", "G13"))
  16. assert.NoError(t, f.MergeCell("Sheet1", "C9", "D8"))
  17. assert.NoError(t, f.MergeCell("Sheet1", "F11", "G13"))
  18. assert.NoError(t, f.MergeCell("Sheet1", "H7", "B15"))
  19. assert.NoError(t, f.MergeCell("Sheet1", "D11", "F13"))
  20. assert.NoError(t, f.MergeCell("Sheet1", "G10", "K12"))
  21. assert.NoError(t, f.SetCellValue("Sheet1", "G11", "set value in merged cell"))
  22. assert.NoError(t, f.SetCellInt("Sheet1", "H11", 100))
  23. assert.NoError(t, f.SetCellValue("Sheet1", "I11", float64(0.5)))
  24. assert.NoError(t, f.SetCellHyperLink("Sheet1", "J11", "https://github.com/360EntSecGroup-Skylar/excelize", "External"))
  25. assert.NoError(t, f.SetCellFormula("Sheet1", "G12", "SUM(Sheet1!B19,Sheet1!C19)"))
  26. value, err := f.GetCellValue("Sheet1", "H11")
  27. assert.Equal(t, "0.5", value)
  28. assert.NoError(t, err)
  29. value, err = f.GetCellValue("Sheet2", "A6") // Merged cell ref is single coordinate.
  30. assert.Equal(t, "", value)
  31. assert.NoError(t, err)
  32. value, err = f.GetCellFormula("Sheet1", "G12")
  33. assert.Equal(t, "SUM(Sheet1!B19,Sheet1!C19)", value)
  34. assert.NoError(t, err)
  35. f.NewSheet("Sheet3")
  36. assert.NoError(t, f.MergeCell("Sheet3", "D11", "F13"))
  37. assert.NoError(t, f.MergeCell("Sheet3", "G10", "K12"))
  38. assert.NoError(t, f.MergeCell("Sheet3", "B1", "D5")) // B1:D5
  39. assert.NoError(t, f.MergeCell("Sheet3", "E1", "F5")) // E1:F5
  40. assert.NoError(t, f.MergeCell("Sheet3", "H2", "I5"))
  41. assert.NoError(t, f.MergeCell("Sheet3", "I4", "J6")) // H2:J6
  42. assert.NoError(t, f.MergeCell("Sheet3", "M2", "N5"))
  43. assert.NoError(t, f.MergeCell("Sheet3", "L4", "M6")) // L2:N6
  44. assert.NoError(t, f.MergeCell("Sheet3", "P4", "Q7"))
  45. assert.NoError(t, f.MergeCell("Sheet3", "O2", "P5")) // O2:Q7
  46. assert.NoError(t, f.MergeCell("Sheet3", "A9", "B12"))
  47. assert.NoError(t, f.MergeCell("Sheet3", "B7", "C9")) // A7:C12
  48. assert.NoError(t, f.MergeCell("Sheet3", "E9", "F10"))
  49. assert.NoError(t, f.MergeCell("Sheet3", "D8", "G12"))
  50. assert.NoError(t, f.MergeCell("Sheet3", "I8", "I12"))
  51. assert.NoError(t, f.MergeCell("Sheet3", "I10", "K10"))
  52. assert.NoError(t, f.MergeCell("Sheet3", "M8", "Q13"))
  53. assert.NoError(t, f.MergeCell("Sheet3", "N10", "O11"))
  54. // Test get merged cells on not exists worksheet.
  55. assert.EqualError(t, f.MergeCell("SheetN", "N10", "O11"), "sheet SheetN is not exist")
  56. assert.NoError(t, f.SaveAs(filepath.Join("test", "TestMergeCell.xlsx")))
  57. f = NewFile()
  58. assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
  59. f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{nil, nil}}
  60. assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
  61. f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}
  62. assert.EqualError(t, f.MergeCell("Sheet1", "A2", "B3"), `invalid area "A1"`)
  63. f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
  64. assert.EqualError(t, f.MergeCell("Sheet1", "A2", "B3"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
  65. }
  66. func TestGetMergeCells(t *testing.T) {
  67. wants := []struct {
  68. value string
  69. start string
  70. end string
  71. }{{
  72. value: "A1",
  73. start: "A1",
  74. end: "B1",
  75. }, {
  76. value: "A2",
  77. start: "A2",
  78. end: "A3",
  79. }, {
  80. value: "A4",
  81. start: "A4",
  82. end: "B5",
  83. }, {
  84. value: "A7",
  85. start: "A7",
  86. end: "C10",
  87. }}
  88. f, err := OpenFile(filepath.Join("test", "MergeCell.xlsx"))
  89. if !assert.NoError(t, err) {
  90. t.FailNow()
  91. }
  92. sheet1 := f.GetSheetName(0)
  93. mergeCells, err := f.GetMergeCells(sheet1)
  94. if !assert.Len(t, mergeCells, len(wants)) {
  95. t.FailNow()
  96. }
  97. assert.NoError(t, err)
  98. for i, m := range mergeCells {
  99. assert.Equal(t, wants[i].value, m.GetCellValue())
  100. assert.Equal(t, wants[i].start, m.GetStartAxis())
  101. assert.Equal(t, wants[i].end, m.GetEndAxis())
  102. }
  103. // Test get merged cells on not exists worksheet.
  104. _, err = f.GetMergeCells("SheetN")
  105. assert.EqualError(t, err, "sheet SheetN is not exist")
  106. }
  107. func TestUnmergeCell(t *testing.T) {
  108. f, err := OpenFile(filepath.Join("test", "MergeCell.xlsx"))
  109. if !assert.NoError(t, err) {
  110. t.FailNow()
  111. }
  112. sheet1 := f.GetSheetName(0)
  113. xlsx, err := f.workSheetReader(sheet1)
  114. assert.NoError(t, err)
  115. mergeCellNum := len(xlsx.MergeCells.Cells)
  116. assert.EqualError(t, f.UnmergeCell("Sheet1", "A", "A"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
  117. // unmerge the mergecell that contains A1
  118. assert.NoError(t, f.UnmergeCell(sheet1, "A1", "A1"))
  119. if len(xlsx.MergeCells.Cells) != mergeCellNum-1 {
  120. t.FailNow()
  121. }
  122. assert.NoError(t, f.SaveAs(filepath.Join("test", "TestUnmergeCell.xlsx")))
  123. f = NewFile()
  124. assert.NoError(t, f.MergeCell("Sheet1", "A2", "B3"))
  125. // Test unmerged area on not exists worksheet.
  126. assert.EqualError(t, f.UnmergeCell("SheetN", "A1", "A1"), "sheet SheetN is not exist")
  127. f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = nil
  128. assert.NoError(t, f.UnmergeCell("Sheet1", "H7", "B15"))
  129. f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{nil, nil}}
  130. assert.NoError(t, f.UnmergeCell("Sheet1", "H15", "B7"))
  131. f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}
  132. assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), `invalid area "A1"`)
  133. f.Sheet["xl/worksheets/sheet1.xml"].MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
  134. assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), `cannot convert cell "A" to coordinates: invalid cell name "A"`)
  135. }