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.
 
 
 
 

870 lines
23 KiB

  1. package excelize
  2. import (
  3. "bytes"
  4. "fmt"
  5. "path/filepath"
  6. "testing"
  7. "github.com/stretchr/testify/assert"
  8. "github.com/stretchr/testify/require"
  9. )
  10. func TestRows(t *testing.T) {
  11. const sheet2 = "Sheet2"
  12. f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
  13. if !assert.NoError(t, err) {
  14. t.FailNow()
  15. }
  16. rows, err := f.Rows(sheet2)
  17. if !assert.NoError(t, err) {
  18. t.FailNow()
  19. }
  20. var collectedRows [][]string
  21. for rows.Next() {
  22. columns, err := rows.Columns()
  23. assert.NoError(t, err)
  24. collectedRows = append(collectedRows, trimSliceSpace(columns))
  25. }
  26. if !assert.NoError(t, rows.Error()) {
  27. t.FailNow()
  28. }
  29. returnedRows, err := f.GetRows(sheet2)
  30. assert.NoError(t, err)
  31. for i := range returnedRows {
  32. returnedRows[i] = trimSliceSpace(returnedRows[i])
  33. }
  34. if !assert.Equal(t, collectedRows, returnedRows) {
  35. t.FailNow()
  36. }
  37. f = NewFile()
  38. f.XLSX["xl/worksheets/sheet1.xml"] = []byte(`<worksheet><sheetData><row r="1"><c r="A1" t="s"><v>1</v></c></row><row r="A"><c r="2" t="str"><v>B</v></c></row></sheetData></worksheet>`)
  39. _, err = f.Rows("Sheet1")
  40. assert.EqualError(t, err, `strconv.Atoi: parsing "A": invalid syntax`)
  41. }
  42. func TestRowsIterator(t *testing.T) {
  43. const (
  44. sheet2 = "Sheet2"
  45. expectedNumRow = 11
  46. )
  47. f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
  48. require.NoError(t, err)
  49. rows, err := f.Rows(sheet2)
  50. require.NoError(t, err)
  51. var rowCount int
  52. for rows.Next() {
  53. rowCount++
  54. require.True(t, rowCount <= expectedNumRow, "rowCount is greater than expected")
  55. }
  56. assert.Equal(t, expectedNumRow, rowCount)
  57. // Valued cell sparse distribution test
  58. f = NewFile()
  59. cells := []string{"C1", "E1", "A3", "B3", "C3", "D3", "E3"}
  60. for _, cell := range cells {
  61. assert.NoError(t, f.SetCellValue("Sheet1", cell, 1))
  62. }
  63. rows, err = f.Rows("Sheet1")
  64. require.NoError(t, err)
  65. rowCount = 0
  66. for rows.Next() {
  67. rowCount++
  68. require.True(t, rowCount <= 3, "rowCount is greater than expected")
  69. }
  70. assert.Equal(t, 3, rowCount)
  71. }
  72. func TestRowsError(t *testing.T) {
  73. xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
  74. if !assert.NoError(t, err) {
  75. t.FailNow()
  76. }
  77. _, err = xlsx.Rows("SheetN")
  78. assert.EqualError(t, err, "sheet SheetN is not exist")
  79. }
  80. func TestRowHeight(t *testing.T) {
  81. xlsx := NewFile()
  82. sheet1 := xlsx.GetSheetName(0)
  83. assert.EqualError(t, xlsx.SetRowHeight(sheet1, 0, defaultRowHeightPixels+1.0), "invalid row number 0")
  84. _, err := xlsx.GetRowHeight("Sheet1", 0)
  85. assert.EqualError(t, err, "invalid row number 0")
  86. assert.NoError(t, xlsx.SetRowHeight(sheet1, 1, 111.0))
  87. height, err := xlsx.GetRowHeight(sheet1, 1)
  88. assert.NoError(t, err)
  89. assert.Equal(t, 111.0, height)
  90. assert.NoError(t, xlsx.SetRowHeight(sheet1, 4, 444.0))
  91. height, err = xlsx.GetRowHeight(sheet1, 4)
  92. assert.NoError(t, err)
  93. assert.Equal(t, 444.0, height)
  94. // Test get row height that rows index over exists rows.
  95. height, err = xlsx.GetRowHeight(sheet1, 5)
  96. assert.NoError(t, err)
  97. assert.Equal(t, defaultRowHeightPixels, height)
  98. // Test get row height that rows heights haven't changed.
  99. height, err = xlsx.GetRowHeight(sheet1, 3)
  100. assert.NoError(t, err)
  101. assert.Equal(t, defaultRowHeightPixels, height)
  102. // Test set and get row height on not exists worksheet.
  103. assert.EqualError(t, xlsx.SetRowHeight("SheetN", 1, 111.0), "sheet SheetN is not exist")
  104. _, err = xlsx.GetRowHeight("SheetN", 3)
  105. assert.EqualError(t, err, "sheet SheetN is not exist")
  106. err = xlsx.SaveAs(filepath.Join("test", "TestRowHeight.xlsx"))
  107. if !assert.NoError(t, err) {
  108. t.FailNow()
  109. }
  110. convertColWidthToPixels(0)
  111. }
  112. func TestColumns(t *testing.T) {
  113. f := NewFile()
  114. rows, err := f.Rows("Sheet1")
  115. assert.NoError(t, err)
  116. rows.decoder = f.xmlNewDecoder(bytes.NewReader([]byte(`<worksheet><sheetData><row r="2"><c r="A1" t="s"><v>1</v></c></row></sheetData></worksheet>`)))
  117. _, err = rows.Columns()
  118. assert.NoError(t, err)
  119. rows.decoder = f.xmlNewDecoder(bytes.NewReader([]byte(`<worksheet><sheetData><row r="2"><c r="A1" t="s"><v>1</v></c></row></sheetData></worksheet>`)))
  120. rows.curRow = 1
  121. _, err = rows.Columns()
  122. assert.NoError(t, err)
  123. rows.decoder = f.xmlNewDecoder(bytes.NewReader([]byte(`<worksheet><sheetData><row r="A"><c r="A1" t="s"><v>1</v></c></row><row r="A"><c r="2" t="str"><v>B</v></c></row></sheetData></worksheet>`)))
  124. rows.stashRow, rows.curRow = 0, 1
  125. _, err = rows.Columns()
  126. assert.EqualError(t, err, `strconv.Atoi: parsing "A": invalid syntax`)
  127. rows.decoder = f.xmlNewDecoder(bytes.NewReader([]byte(`<worksheet><sheetData><row r="1"><c r="A1" t="s"><v>1</v></c></row><row r="A"><c r="2" t="str"><v>B</v></c></row></sheetData></worksheet>`)))
  128. _, err = rows.Columns()
  129. assert.NoError(t, err)
  130. rows.curRow = 3
  131. rows.decoder = f.xmlNewDecoder(bytes.NewReader([]byte(`<worksheet><sheetData><row r="1"><c r="A" t="s"><v>1</v></c></row></sheetData></worksheet>`)))
  132. _, err = rows.Columns()
  133. assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`)
  134. // Test token is nil
  135. rows.decoder = f.xmlNewDecoder(bytes.NewReader(nil))
  136. _, err = rows.Columns()
  137. assert.NoError(t, err)
  138. }
  139. func TestSharedStringsReader(t *testing.T) {
  140. f := NewFile()
  141. f.XLSX["xl/sharedStrings.xml"] = MacintoshCyrillicCharset
  142. f.sharedStringsReader()
  143. }
  144. func TestRowVisibility(t *testing.T) {
  145. f, err := prepareTestBook1()
  146. if !assert.NoError(t, err) {
  147. t.FailNow()
  148. }
  149. f.NewSheet("Sheet3")
  150. assert.NoError(t, f.SetRowVisible("Sheet3", 2, false))
  151. assert.NoError(t, f.SetRowVisible("Sheet3", 2, true))
  152. visiable, err := f.GetRowVisible("Sheet3", 2)
  153. assert.Equal(t, true, visiable)
  154. assert.NoError(t, err)
  155. visiable, err = f.GetRowVisible("Sheet3", 25)
  156. assert.Equal(t, false, visiable)
  157. assert.NoError(t, err)
  158. assert.EqualError(t, f.SetRowVisible("Sheet3", 0, true), "invalid row number 0")
  159. assert.EqualError(t, f.SetRowVisible("SheetN", 2, false), "sheet SheetN is not exist")
  160. visible, err := f.GetRowVisible("Sheet3", 0)
  161. assert.Equal(t, false, visible)
  162. assert.EqualError(t, err, "invalid row number 0")
  163. _, err = f.GetRowVisible("SheetN", 1)
  164. assert.EqualError(t, err, "sheet SheetN is not exist")
  165. assert.NoError(t, f.SaveAs(filepath.Join("test", "TestRowVisibility.xlsx")))
  166. }
  167. func TestRemoveRow(t *testing.T) {
  168. f := NewFile()
  169. sheet1 := f.GetSheetName(0)
  170. r, err := f.workSheetReader(sheet1)
  171. assert.NoError(t, err)
  172. const (
  173. colCount = 10
  174. rowCount = 10
  175. )
  176. fillCells(f, sheet1, colCount, rowCount)
  177. assert.NoError(t, f.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External"))
  178. assert.EqualError(t, f.RemoveRow(sheet1, -1), "invalid row number -1")
  179. assert.EqualError(t, f.RemoveRow(sheet1, 0), "invalid row number 0")
  180. assert.NoError(t, f.RemoveRow(sheet1, 4))
  181. if !assert.Len(t, r.SheetData.Row, rowCount-1) {
  182. t.FailNow()
  183. }
  184. assert.NoError(t, f.MergeCell(sheet1, "B3", "B5"))
  185. assert.NoError(t, f.RemoveRow(sheet1, 2))
  186. if !assert.Len(t, r.SheetData.Row, rowCount-2) {
  187. t.FailNow()
  188. }
  189. assert.NoError(t, f.RemoveRow(sheet1, 4))
  190. if !assert.Len(t, r.SheetData.Row, rowCount-3) {
  191. t.FailNow()
  192. }
  193. err = f.AutoFilter(sheet1, "A2", "A2", `{"column":"A","expression":"x != blanks"}`)
  194. if !assert.NoError(t, err) {
  195. t.FailNow()
  196. }
  197. assert.NoError(t, f.RemoveRow(sheet1, 1))
  198. if !assert.Len(t, r.SheetData.Row, rowCount-4) {
  199. t.FailNow()
  200. }
  201. assert.NoError(t, f.RemoveRow(sheet1, 2))
  202. if !assert.Len(t, r.SheetData.Row, rowCount-5) {
  203. t.FailNow()
  204. }
  205. assert.NoError(t, f.RemoveRow(sheet1, 1))
  206. if !assert.Len(t, r.SheetData.Row, rowCount-6) {
  207. t.FailNow()
  208. }
  209. assert.NoError(t, f.RemoveRow(sheet1, 10))
  210. assert.NoError(t, f.SaveAs(filepath.Join("test", "TestRemoveRow.xlsx")))
  211. // Test remove row on not exist worksheet
  212. assert.EqualError(t, f.RemoveRow("SheetN", 1), `sheet SheetN is not exist`)
  213. }
  214. func TestInsertRow(t *testing.T) {
  215. xlsx := NewFile()
  216. sheet1 := xlsx.GetSheetName(0)
  217. r, err := xlsx.workSheetReader(sheet1)
  218. assert.NoError(t, err)
  219. const (
  220. colCount = 10
  221. rowCount = 10
  222. )
  223. fillCells(xlsx, sheet1, colCount, rowCount)
  224. assert.NoError(t, xlsx.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External"))
  225. assert.EqualError(t, xlsx.InsertRow(sheet1, -1), "invalid row number -1")
  226. assert.EqualError(t, xlsx.InsertRow(sheet1, 0), "invalid row number 0")
  227. assert.NoError(t, xlsx.InsertRow(sheet1, 1))
  228. if !assert.Len(t, r.SheetData.Row, rowCount+1) {
  229. t.FailNow()
  230. }
  231. assert.NoError(t, xlsx.InsertRow(sheet1, 4))
  232. if !assert.Len(t, r.SheetData.Row, rowCount+2) {
  233. t.FailNow()
  234. }
  235. assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestInsertRow.xlsx")))
  236. }
  237. // Testing internal sructure state after insert operations.
  238. // It is important for insert workflow to be constant to avoid side effect with functions related to internal structure.
  239. func TestInsertRowInEmptyFile(t *testing.T) {
  240. xlsx := NewFile()
  241. sheet1 := xlsx.GetSheetName(0)
  242. r, err := xlsx.workSheetReader(sheet1)
  243. assert.NoError(t, err)
  244. assert.NoError(t, xlsx.InsertRow(sheet1, 1))
  245. assert.Len(t, r.SheetData.Row, 0)
  246. assert.NoError(t, xlsx.InsertRow(sheet1, 2))
  247. assert.Len(t, r.SheetData.Row, 0)
  248. assert.NoError(t, xlsx.InsertRow(sheet1, 99))
  249. assert.Len(t, r.SheetData.Row, 0)
  250. assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestInsertRowInEmptyFile.xlsx")))
  251. }
  252. func TestDuplicateRowFromSingleRow(t *testing.T) {
  253. const sheet = "Sheet1"
  254. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  255. cells := map[string]string{
  256. "A1": "A1 Value",
  257. "A2": "A2 Value",
  258. "A3": "A3 Value",
  259. "B1": "B1 Value",
  260. "B2": "B2 Value",
  261. "B3": "B3 Value",
  262. }
  263. t.Run("FromSingleRow", func(t *testing.T) {
  264. xlsx := NewFile()
  265. assert.NoError(t, xlsx.SetCellStr(sheet, "A1", cells["A1"]))
  266. assert.NoError(t, xlsx.SetCellStr(sheet, "B1", cells["B1"]))
  267. assert.NoError(t, xlsx.DuplicateRow(sheet, 1))
  268. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.FromSingleRow_1"))) {
  269. t.FailNow()
  270. }
  271. expect := map[string]string{
  272. "A1": cells["A1"], "B1": cells["B1"],
  273. "A2": cells["A1"], "B2": cells["B1"],
  274. }
  275. for cell, val := range expect {
  276. v, err := xlsx.GetCellValue(sheet, cell)
  277. assert.NoError(t, err)
  278. if !assert.Equal(t, val, v, cell) {
  279. t.FailNow()
  280. }
  281. }
  282. assert.NoError(t, xlsx.DuplicateRow(sheet, 2))
  283. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.FromSingleRow_2"))) {
  284. t.FailNow()
  285. }
  286. expect = map[string]string{
  287. "A1": cells["A1"], "B1": cells["B1"],
  288. "A2": cells["A1"], "B2": cells["B1"],
  289. "A3": cells["A1"], "B3": cells["B1"],
  290. }
  291. for cell, val := range expect {
  292. v, err := xlsx.GetCellValue(sheet, cell)
  293. assert.NoError(t, err)
  294. if !assert.Equal(t, val, v, cell) {
  295. t.FailNow()
  296. }
  297. }
  298. })
  299. }
  300. func TestDuplicateRowUpdateDuplicatedRows(t *testing.T) {
  301. const sheet = "Sheet1"
  302. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  303. cells := map[string]string{
  304. "A1": "A1 Value",
  305. "A2": "A2 Value",
  306. "A3": "A3 Value",
  307. "B1": "B1 Value",
  308. "B2": "B2 Value",
  309. "B3": "B3 Value",
  310. }
  311. t.Run("UpdateDuplicatedRows", func(t *testing.T) {
  312. xlsx := NewFile()
  313. assert.NoError(t, xlsx.SetCellStr(sheet, "A1", cells["A1"]))
  314. assert.NoError(t, xlsx.SetCellStr(sheet, "B1", cells["B1"]))
  315. assert.NoError(t, xlsx.DuplicateRow(sheet, 1))
  316. assert.NoError(t, xlsx.SetCellStr(sheet, "A2", cells["A2"]))
  317. assert.NoError(t, xlsx.SetCellStr(sheet, "B2", cells["B2"]))
  318. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.UpdateDuplicatedRows"))) {
  319. t.FailNow()
  320. }
  321. expect := map[string]string{
  322. "A1": cells["A1"], "B1": cells["B1"],
  323. "A2": cells["A2"], "B2": cells["B2"],
  324. }
  325. for cell, val := range expect {
  326. v, err := xlsx.GetCellValue(sheet, cell)
  327. assert.NoError(t, err)
  328. if !assert.Equal(t, val, v, cell) {
  329. t.FailNow()
  330. }
  331. }
  332. })
  333. }
  334. func TestDuplicateRowFirstOfMultipleRows(t *testing.T) {
  335. const sheet = "Sheet1"
  336. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  337. cells := map[string]string{
  338. "A1": "A1 Value",
  339. "A2": "A2 Value",
  340. "A3": "A3 Value",
  341. "B1": "B1 Value",
  342. "B2": "B2 Value",
  343. "B3": "B3 Value",
  344. }
  345. newFileWithDefaults := func() *File {
  346. f := NewFile()
  347. for cell, val := range cells {
  348. assert.NoError(t, f.SetCellStr(sheet, cell, val))
  349. }
  350. return f
  351. }
  352. t.Run("FirstOfMultipleRows", func(t *testing.T) {
  353. xlsx := newFileWithDefaults()
  354. assert.NoError(t, xlsx.DuplicateRow(sheet, 1))
  355. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.FirstOfMultipleRows"))) {
  356. t.FailNow()
  357. }
  358. expect := map[string]string{
  359. "A1": cells["A1"], "B1": cells["B1"],
  360. "A2": cells["A1"], "B2": cells["B1"],
  361. "A3": cells["A2"], "B3": cells["B2"],
  362. "A4": cells["A3"], "B4": cells["B3"],
  363. }
  364. for cell, val := range expect {
  365. v, err := xlsx.GetCellValue(sheet, cell)
  366. assert.NoError(t, err)
  367. if !assert.Equal(t, val, v, cell) {
  368. t.FailNow()
  369. }
  370. }
  371. })
  372. }
  373. func TestDuplicateRowZeroWithNoRows(t *testing.T) {
  374. const sheet = "Sheet1"
  375. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  376. t.Run("ZeroWithNoRows", func(t *testing.T) {
  377. xlsx := NewFile()
  378. assert.EqualError(t, xlsx.DuplicateRow(sheet, 0), "invalid row number 0")
  379. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.ZeroWithNoRows"))) {
  380. t.FailNow()
  381. }
  382. val, err := xlsx.GetCellValue(sheet, "A1")
  383. assert.NoError(t, err)
  384. assert.Equal(t, "", val)
  385. val, err = xlsx.GetCellValue(sheet, "B1")
  386. assert.NoError(t, err)
  387. assert.Equal(t, "", val)
  388. val, err = xlsx.GetCellValue(sheet, "A2")
  389. assert.NoError(t, err)
  390. assert.Equal(t, "", val)
  391. val, err = xlsx.GetCellValue(sheet, "B2")
  392. assert.NoError(t, err)
  393. assert.Equal(t, "", val)
  394. assert.NoError(t, err)
  395. expect := map[string]string{
  396. "A1": "", "B1": "",
  397. "A2": "", "B2": "",
  398. }
  399. for cell, val := range expect {
  400. v, err := xlsx.GetCellValue(sheet, cell)
  401. assert.NoError(t, err)
  402. if !assert.Equal(t, val, v, cell) {
  403. t.FailNow()
  404. }
  405. }
  406. })
  407. }
  408. func TestDuplicateRowMiddleRowOfEmptyFile(t *testing.T) {
  409. const sheet = "Sheet1"
  410. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  411. t.Run("MiddleRowOfEmptyFile", func(t *testing.T) {
  412. xlsx := NewFile()
  413. assert.NoError(t, xlsx.DuplicateRow(sheet, 99))
  414. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.MiddleRowOfEmptyFile"))) {
  415. t.FailNow()
  416. }
  417. expect := map[string]string{
  418. "A98": "",
  419. "A99": "",
  420. "A100": "",
  421. }
  422. for cell, val := range expect {
  423. v, err := xlsx.GetCellValue(sheet, cell)
  424. assert.NoError(t, err)
  425. if !assert.Equal(t, val, v, cell) {
  426. t.FailNow()
  427. }
  428. }
  429. })
  430. }
  431. func TestDuplicateRowWithLargeOffsetToMiddleOfData(t *testing.T) {
  432. const sheet = "Sheet1"
  433. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  434. cells := map[string]string{
  435. "A1": "A1 Value",
  436. "A2": "A2 Value",
  437. "A3": "A3 Value",
  438. "B1": "B1 Value",
  439. "B2": "B2 Value",
  440. "B3": "B3 Value",
  441. }
  442. newFileWithDefaults := func() *File {
  443. f := NewFile()
  444. for cell, val := range cells {
  445. assert.NoError(t, f.SetCellStr(sheet, cell, val))
  446. }
  447. return f
  448. }
  449. t.Run("WithLargeOffsetToMiddleOfData", func(t *testing.T) {
  450. xlsx := newFileWithDefaults()
  451. assert.NoError(t, xlsx.DuplicateRowTo(sheet, 1, 3))
  452. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.WithLargeOffsetToMiddleOfData"))) {
  453. t.FailNow()
  454. }
  455. expect := map[string]string{
  456. "A1": cells["A1"], "B1": cells["B1"],
  457. "A2": cells["A2"], "B2": cells["B2"],
  458. "A3": cells["A1"], "B3": cells["B1"],
  459. "A4": cells["A3"], "B4": cells["B3"],
  460. }
  461. for cell, val := range expect {
  462. v, err := xlsx.GetCellValue(sheet, cell)
  463. assert.NoError(t, err)
  464. if !assert.Equal(t, val, v, cell) {
  465. t.FailNow()
  466. }
  467. }
  468. })
  469. }
  470. func TestDuplicateRowWithLargeOffsetToEmptyRows(t *testing.T) {
  471. const sheet = "Sheet1"
  472. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  473. cells := map[string]string{
  474. "A1": "A1 Value",
  475. "A2": "A2 Value",
  476. "A3": "A3 Value",
  477. "B1": "B1 Value",
  478. "B2": "B2 Value",
  479. "B3": "B3 Value",
  480. }
  481. newFileWithDefaults := func() *File {
  482. f := NewFile()
  483. for cell, val := range cells {
  484. assert.NoError(t, f.SetCellStr(sheet, cell, val))
  485. }
  486. return f
  487. }
  488. t.Run("WithLargeOffsetToEmptyRows", func(t *testing.T) {
  489. xlsx := newFileWithDefaults()
  490. assert.NoError(t, xlsx.DuplicateRowTo(sheet, 1, 7))
  491. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.WithLargeOffsetToEmptyRows"))) {
  492. t.FailNow()
  493. }
  494. expect := map[string]string{
  495. "A1": cells["A1"], "B1": cells["B1"],
  496. "A2": cells["A2"], "B2": cells["B2"],
  497. "A3": cells["A3"], "B3": cells["B3"],
  498. "A7": cells["A1"], "B7": cells["B1"],
  499. }
  500. for cell, val := range expect {
  501. v, err := xlsx.GetCellValue(sheet, cell)
  502. assert.NoError(t, err)
  503. if !assert.Equal(t, val, v, cell) {
  504. t.FailNow()
  505. }
  506. }
  507. })
  508. }
  509. func TestDuplicateRowInsertBefore(t *testing.T) {
  510. const sheet = "Sheet1"
  511. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  512. cells := map[string]string{
  513. "A1": "A1 Value",
  514. "A2": "A2 Value",
  515. "A3": "A3 Value",
  516. "B1": "B1 Value",
  517. "B2": "B2 Value",
  518. "B3": "B3 Value",
  519. }
  520. newFileWithDefaults := func() *File {
  521. f := NewFile()
  522. for cell, val := range cells {
  523. assert.NoError(t, f.SetCellStr(sheet, cell, val))
  524. }
  525. return f
  526. }
  527. t.Run("InsertBefore", func(t *testing.T) {
  528. xlsx := newFileWithDefaults()
  529. assert.NoError(t, xlsx.DuplicateRowTo(sheet, 2, 1))
  530. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.InsertBefore"))) {
  531. t.FailNow()
  532. }
  533. expect := map[string]string{
  534. "A1": cells["A2"], "B1": cells["B2"],
  535. "A2": cells["A1"], "B2": cells["B1"],
  536. "A3": cells["A2"], "B3": cells["B2"],
  537. "A4": cells["A3"], "B4": cells["B3"],
  538. }
  539. for cell, val := range expect {
  540. v, err := xlsx.GetCellValue(sheet, cell)
  541. assert.NoError(t, err)
  542. if !assert.Equal(t, val, v, cell) {
  543. t.FailNow()
  544. }
  545. }
  546. })
  547. }
  548. func TestDuplicateRowInsertBeforeWithLargeOffset(t *testing.T) {
  549. const sheet = "Sheet1"
  550. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  551. cells := map[string]string{
  552. "A1": "A1 Value",
  553. "A2": "A2 Value",
  554. "A3": "A3 Value",
  555. "B1": "B1 Value",
  556. "B2": "B2 Value",
  557. "B3": "B3 Value",
  558. }
  559. newFileWithDefaults := func() *File {
  560. f := NewFile()
  561. for cell, val := range cells {
  562. assert.NoError(t, f.SetCellStr(sheet, cell, val))
  563. }
  564. return f
  565. }
  566. t.Run("InsertBeforeWithLargeOffset", func(t *testing.T) {
  567. xlsx := newFileWithDefaults()
  568. assert.NoError(t, xlsx.DuplicateRowTo(sheet, 3, 1))
  569. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.InsertBeforeWithLargeOffset"))) {
  570. t.FailNow()
  571. }
  572. expect := map[string]string{
  573. "A1": cells["A3"], "B1": cells["B3"],
  574. "A2": cells["A1"], "B2": cells["B1"],
  575. "A3": cells["A2"], "B3": cells["B2"],
  576. "A4": cells["A3"], "B4": cells["B3"],
  577. }
  578. for cell, val := range expect {
  579. v, err := xlsx.GetCellValue(sheet, cell)
  580. assert.NoError(t, err)
  581. if !assert.Equal(t, val, v) {
  582. t.FailNow()
  583. }
  584. }
  585. })
  586. }
  587. func TestDuplicateRowInsertBeforeWithMergeCells(t *testing.T) {
  588. const sheet = "Sheet1"
  589. outFile := filepath.Join("test", "TestDuplicateRow.%s.xlsx")
  590. cells := map[string]string{
  591. "A1": "A1 Value",
  592. "A2": "A2 Value",
  593. "A3": "A3 Value",
  594. "B1": "B1 Value",
  595. "B2": "B2 Value",
  596. "B3": "B3 Value",
  597. }
  598. newFileWithDefaults := func() *File {
  599. f := NewFile()
  600. for cell, val := range cells {
  601. assert.NoError(t, f.SetCellStr(sheet, cell, val))
  602. }
  603. assert.NoError(t, f.MergeCell(sheet, "B2", "C2"))
  604. assert.NoError(t, f.MergeCell(sheet, "C6", "C8"))
  605. return f
  606. }
  607. t.Run("InsertBeforeWithLargeOffset", func(t *testing.T) {
  608. xlsx := newFileWithDefaults()
  609. assert.NoError(t, xlsx.DuplicateRowTo(sheet, 2, 1))
  610. assert.NoError(t, xlsx.DuplicateRowTo(sheet, 1, 8))
  611. if !assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.InsertBeforeWithMergeCells"))) {
  612. t.FailNow()
  613. }
  614. expect := []MergeCell{
  615. {"B3:C3", "B2 Value"},
  616. {"C7:C10", ""},
  617. {"B1:C1", "B2 Value"},
  618. }
  619. mergeCells, err := xlsx.GetMergeCells(sheet)
  620. assert.NoError(t, err)
  621. for idx, val := range expect {
  622. if !assert.Equal(t, val, mergeCells[idx]) {
  623. t.FailNow()
  624. }
  625. }
  626. })
  627. }
  628. func TestDuplicateRowInvalidRownum(t *testing.T) {
  629. const sheet = "Sheet1"
  630. outFile := filepath.Join("test", "TestDuplicateRowInvalidRownum.%s.xlsx")
  631. cells := map[string]string{
  632. "A1": "A1 Value",
  633. "A2": "A2 Value",
  634. "A3": "A3 Value",
  635. "B1": "B1 Value",
  636. "B2": "B2 Value",
  637. "B3": "B3 Value",
  638. }
  639. invalidIndexes := []int{-100, -2, -1, 0}
  640. for _, row := range invalidIndexes {
  641. name := fmt.Sprintf("%d", row)
  642. t.Run(name, func(t *testing.T) {
  643. xlsx := NewFile()
  644. for col, val := range cells {
  645. assert.NoError(t, xlsx.SetCellStr(sheet, col, val))
  646. }
  647. assert.EqualError(t, xlsx.DuplicateRow(sheet, row), fmt.Sprintf("invalid row number %d", row))
  648. for col, val := range cells {
  649. v, err := xlsx.GetCellValue(sheet, col)
  650. assert.NoError(t, err)
  651. if !assert.Equal(t, val, v) {
  652. t.FailNow()
  653. }
  654. }
  655. assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, name)))
  656. })
  657. }
  658. for _, row1 := range invalidIndexes {
  659. for _, row2 := range invalidIndexes {
  660. name := fmt.Sprintf("[%d,%d]", row1, row2)
  661. t.Run(name, func(t *testing.T) {
  662. xlsx := NewFile()
  663. for col, val := range cells {
  664. assert.NoError(t, xlsx.SetCellStr(sheet, col, val))
  665. }
  666. assert.EqualError(t, xlsx.DuplicateRowTo(sheet, row1, row2), fmt.Sprintf("invalid row number %d", row1))
  667. for col, val := range cells {
  668. v, err := xlsx.GetCellValue(sheet, col)
  669. assert.NoError(t, err)
  670. if !assert.Equal(t, val, v) {
  671. t.FailNow()
  672. }
  673. }
  674. assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, name)))
  675. })
  676. }
  677. }
  678. }
  679. func TestDuplicateRowTo(t *testing.T) {
  680. f := File{}
  681. assert.EqualError(t, f.DuplicateRowTo("SheetN", 1, 2), "sheet SheetN is not exist")
  682. }
  683. func TestDuplicateMergeCells(t *testing.T) {
  684. f := File{}
  685. xlsx := &xlsxWorksheet{MergeCells: &xlsxMergeCells{
  686. Cells: []*xlsxMergeCell{{Ref: "A1:-"}},
  687. }}
  688. assert.EqualError(t, f.duplicateMergeCells("Sheet1", xlsx, 0, 0), `cannot convert cell "-" to coordinates: invalid cell name "-"`)
  689. xlsx.MergeCells.Cells[0].Ref = "A1:B1"
  690. assert.EqualError(t, f.duplicateMergeCells("SheetN", xlsx, 1, 2), "sheet SheetN is not exist")
  691. }
  692. func TestGetValueFrom(t *testing.T) {
  693. c := &xlsxC{T: "inlineStr"}
  694. f := NewFile()
  695. d := &xlsxSST{}
  696. val, err := c.getValueFrom(f, d)
  697. assert.NoError(t, err)
  698. assert.Equal(t, "", val)
  699. }
  700. func TestErrSheetNotExistError(t *testing.T) {
  701. err := ErrSheetNotExist{SheetName: "Sheet1"}
  702. assert.EqualValues(t, err.Error(), "sheet Sheet1 is not exist")
  703. }
  704. func TestCheckRow(t *testing.T) {
  705. f := NewFile()
  706. f.XLSX["xl/worksheets/sheet1.xml"] = []byte(`<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" ><sheetData><row r="2"><c><v>1</v></c><c r="F2"><v>2</v></c><c><v>3</v></c><c><v>4</v></c><c r="M2"><v>5</v></c></row></sheetData></worksheet>`)
  707. _, err := f.GetRows("Sheet1")
  708. assert.NoError(t, err)
  709. assert.NoError(t, f.SetCellValue("Sheet1", "A1", false))
  710. f = NewFile()
  711. f.XLSX["xl/worksheets/sheet1.xml"] = []byte(`<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" ><sheetData><row r="2"><c><v>1</v></c><c r="-"><v>2</v></c><c><v>3</v></c><c><v>4</v></c><c r="M2"><v>5</v></c></row></sheetData></worksheet>`)
  712. assert.EqualError(t, f.SetCellValue("Sheet1", "A1", false), `cannot convert cell "-" to coordinates: invalid cell name "-"`)
  713. }
  714. func BenchmarkRows(b *testing.B) {
  715. f, _ := OpenFile(filepath.Join("test", "Book1.xlsx"))
  716. for i := 0; i < b.N; i++ {
  717. rows, _ := f.Rows("Sheet2")
  718. for rows.Next() {
  719. row, _ := rows.Columns()
  720. for i := range row {
  721. if i >= 0 {
  722. continue
  723. }
  724. }
  725. }
  726. }
  727. }
  728. func trimSliceSpace(s []string) []string {
  729. for {
  730. if len(s) > 0 && s[len(s)-1] == "" {
  731. s = s[:len(s)-1]
  732. } else {
  733. break
  734. }
  735. }
  736. return s
  737. }