sparkline_test.go 9.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310
  1. package excelize
  2. import (
  3. "fmt"
  4. "path/filepath"
  5. "testing"
  6. "github.com/stretchr/testify/assert"
  7. )
  8. func TestAddSparkline(t *testing.T) {
  9. f := prepareSparklineDataset()
  10. // Set the columns widths to make the output clearer
  11. style, err := f.NewStyle(`{"font":{"bold":true}}`)
  12. assert.NoError(t, err)
  13. assert.NoError(t, f.SetCellStyle("Sheet1", "A1", "B1", style))
  14. assert.NoError(t, f.SetSheetViewOptions("Sheet1", 0, ZoomScale(150)))
  15. assert.NoError(t, f.SetColWidth("Sheet1", "A", "A", 14))
  16. assert.NoError(t, f.SetColWidth("Sheet1", "B", "B", 50))
  17. // Headings
  18. assert.NoError(t, f.SetCellValue("Sheet1", "A1", "Sparkline"))
  19. assert.NoError(t, f.SetCellValue("Sheet1", "B1", "Description"))
  20. assert.NoError(t, f.SetCellValue("Sheet1", "B2", `A default "line" sparkline.`))
  21. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  22. Location: []string{"A2"},
  23. Range: []string{"Sheet3!A1:J1"},
  24. }))
  25. assert.NoError(t, f.SetCellValue("Sheet1", "B3", `A default "column" sparkline.`))
  26. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  27. Location: []string{"A3"},
  28. Range: []string{"Sheet3!A2:J2"},
  29. Type: "column",
  30. }))
  31. assert.NoError(t, f.SetCellValue("Sheet1", "B4", `A default "win/loss" sparkline.`))
  32. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  33. Location: []string{"A4"},
  34. Range: []string{"Sheet3!A3:J3"},
  35. Type: "win_loss",
  36. }))
  37. assert.NoError(t, f.SetCellValue("Sheet1", "B6", "Line with markers."))
  38. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  39. Location: []string{"A6"},
  40. Range: []string{"Sheet3!A1:J1"},
  41. Markers: true,
  42. }))
  43. assert.NoError(t, f.SetCellValue("Sheet1", "B7", "Line with high and low points."))
  44. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  45. Location: []string{"A7"},
  46. Range: []string{"Sheet3!A1:J1"},
  47. High: true,
  48. Low: true,
  49. }))
  50. assert.NoError(t, f.SetCellValue("Sheet1", "B8", "Line with first and last point markers."))
  51. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  52. Location: []string{"A8"},
  53. Range: []string{"Sheet3!A1:J1"},
  54. First: true,
  55. Last: true,
  56. }))
  57. assert.NoError(t, f.SetCellValue("Sheet1", "B9", "Line with negative point markers."))
  58. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  59. Location: []string{"A9"},
  60. Range: []string{"Sheet3!A1:J1"},
  61. Negative: true,
  62. }))
  63. assert.NoError(t, f.SetCellValue("Sheet1", "B10", "Line with axis."))
  64. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  65. Location: []string{"A10"},
  66. Range: []string{"Sheet3!A1:J1"},
  67. Axis: true,
  68. }))
  69. assert.NoError(t, f.SetCellValue("Sheet1", "B12", "Column with default style (1)."))
  70. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  71. Location: []string{"A12"},
  72. Range: []string{"Sheet3!A2:J2"},
  73. Type: "column",
  74. }))
  75. assert.NoError(t, f.SetCellValue("Sheet1", "B13", "Column with style 2."))
  76. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  77. Location: []string{"A13"},
  78. Range: []string{"Sheet3!A2:J2"},
  79. Type: "column",
  80. Style: 2,
  81. }))
  82. assert.NoError(t, f.SetCellValue("Sheet1", "B14", "Column with style 3."))
  83. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  84. Location: []string{"A14"},
  85. Range: []string{"Sheet3!A2:J2"},
  86. Type: "column",
  87. Style: 3,
  88. }))
  89. assert.NoError(t, f.SetCellValue("Sheet1", "B15", "Column with style 4."))
  90. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  91. Location: []string{"A15"},
  92. Range: []string{"Sheet3!A2:J2"},
  93. Type: "column",
  94. Style: 4,
  95. }))
  96. assert.NoError(t, f.SetCellValue("Sheet1", "B16", "Column with style 5."))
  97. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  98. Location: []string{"A16"},
  99. Range: []string{"Sheet3!A2:J2"},
  100. Type: "column",
  101. Style: 5,
  102. }))
  103. assert.NoError(t, f.SetCellValue("Sheet1", "B17", "Column with style 6."))
  104. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  105. Location: []string{"A17"},
  106. Range: []string{"Sheet3!A2:J2"},
  107. Type: "column",
  108. Style: 6,
  109. }))
  110. assert.NoError(t, f.SetCellValue("Sheet1", "B18", "Column with a user defined color."))
  111. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  112. Location: []string{"A18"},
  113. Range: []string{"Sheet3!A2:J2"},
  114. Type: "column",
  115. SeriesColor: "#E965E0",
  116. }))
  117. assert.NoError(t, f.SetCellValue("Sheet1", "B20", "A win/loss sparkline."))
  118. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  119. Location: []string{"A20"},
  120. Range: []string{"Sheet3!A3:J3"},
  121. Type: "win_loss",
  122. }))
  123. assert.NoError(t, f.SetCellValue("Sheet1", "B21", "A win/loss sparkline with negative points highlighted."))
  124. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  125. Location: []string{"A21"},
  126. Range: []string{"Sheet3!A3:J3"},
  127. Type: "win_loss",
  128. Negative: true,
  129. }))
  130. assert.NoError(t, f.SetCellValue("Sheet1", "B23", "A left to right column (the default)."))
  131. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  132. Location: []string{"A23"},
  133. Range: []string{"Sheet3!A4:J4"},
  134. Type: "column",
  135. Style: 20,
  136. }))
  137. assert.NoError(t, f.SetCellValue("Sheet1", "B24", "A right to left column."))
  138. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  139. Location: []string{"A24"},
  140. Range: []string{"Sheet3!A4:J4"},
  141. Type: "column",
  142. Style: 20,
  143. Reverse: true,
  144. }))
  145. assert.NoError(t, f.SetCellValue("Sheet1", "B25", "Sparkline and text in one cell."))
  146. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  147. Location: []string{"A25"},
  148. Range: []string{"Sheet3!A4:J4"},
  149. Type: "column",
  150. Style: 20,
  151. }))
  152. assert.NoError(t, f.SetCellValue("Sheet1", "A25", "Growth"))
  153. assert.NoError(t, f.SetCellValue("Sheet1", "B27", "A grouped sparkline. Changes are applied to all three."))
  154. assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{
  155. Location: []string{"A27", "A28", "A29"},
  156. Range: []string{"Sheet3!A5:J5", "Sheet3!A6:J6", "Sheet3!A7:J7"},
  157. Markers: true,
  158. }))
  159. // Sheet2 sections
  160. assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{
  161. Location: []string{"F3"},
  162. Range: []string{"Sheet2!A3:E3"},
  163. Type: "win_loss",
  164. Negative: true,
  165. }))
  166. assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{
  167. Location: []string{"F1"},
  168. Range: []string{"Sheet2!A1:E1"},
  169. Markers: true,
  170. }))
  171. assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{
  172. Location: []string{"F2"},
  173. Range: []string{"Sheet2!A2:E2"},
  174. Type: "column",
  175. Style: 12,
  176. }))
  177. assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{
  178. Location: []string{"F3"},
  179. Range: []string{"Sheet2!A3:E3"},
  180. Type: "win_loss",
  181. Negative: true,
  182. }))
  183. // Save spreadsheet by the given path.
  184. assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddSparkline.xlsx")))
  185. // Test error exceptions
  186. assert.EqualError(t, f.AddSparkline("SheetN", &SparklineOption{
  187. Location: []string{"F3"},
  188. Range: []string{"Sheet2!A3:E3"},
  189. }), "sheet SheetN is not exist")
  190. assert.EqualError(t, f.AddSparkline("Sheet1", nil), "parameter is required")
  191. assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
  192. Range: []string{"Sheet2!A3:E3"},
  193. }), `parameter 'Location' is required`)
  194. assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
  195. Location: []string{"F3"},
  196. }), `parameter 'Range' is required`)
  197. assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
  198. Location: []string{"F2", "F3"},
  199. Range: []string{"Sheet2!A3:E3"},
  200. }), `must have the same number of 'Location' and 'Range' parameters`)
  201. assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
  202. Location: []string{"F3"},
  203. Range: []string{"Sheet2!A3:E3"},
  204. Type: "unknown_type",
  205. }), `parameter 'Type' must be 'line', 'column' or 'win_loss'`)
  206. assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
  207. Location: []string{"F3"},
  208. Range: []string{"Sheet2!A3:E3"},
  209. Style: -1,
  210. }), `parameter 'Style' must betweent 0-35`)
  211. assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
  212. Location: []string{"F3"},
  213. Range: []string{"Sheet2!A3:E3"},
  214. Style: -1,
  215. }), `parameter 'Style' must betweent 0-35`)
  216. f.Sheet["xl/worksheets/sheet1.xml"].ExtLst.Ext = `<extLst>
  217. <ext x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{05C60535-1F16-4fd2-B633-F4F36F0B64E0}">
  218. <x14:sparklineGroups
  219. xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
  220. <x14:sparklineGroup>
  221. </x14:sparklines>
  222. </x14:sparklineGroup>
  223. </x14:sparklineGroups>
  224. </ext>
  225. </extLst>`
  226. assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{
  227. Location: []string{"A2"},
  228. Range: []string{"Sheet3!A1:J1"},
  229. }), "XML syntax error on line 6: element <sparklineGroup> closed by </sparklines>")
  230. }
  231. func TestAppendSparkline(t *testing.T) {
  232. // Test unsupported charset.
  233. f := NewFile()
  234. ws, err := f.workSheetReader("Sheet1")
  235. assert.NoError(t, err)
  236. ws.ExtLst = &xlsxExtLst{Ext: string(MacintoshCyrillicCharset)}
  237. assert.EqualError(t, f.appendSparkline(ws, &xlsxX14SparklineGroup{}, &xlsxX14SparklineGroups{}), "XML syntax error on line 1: invalid UTF-8")
  238. }
  239. func prepareSparklineDataset() *File {
  240. f := NewFile()
  241. sheet2 := [][]int{
  242. {-2, 2, 3, -1, 0},
  243. {30, 20, 33, 20, 15},
  244. {1, -1, -1, 1, -1},
  245. }
  246. sheet3 := [][]int{
  247. {-2, 2, 3, -1, 0, -2, 3, 2, 1, 0},
  248. {30, 20, 33, 20, 15, 5, 5, 15, 10, 15},
  249. {1, 1, -1, -1, 1, -1, 1, 1, 1, -1},
  250. {5, 6, 7, 10, 15, 20, 30, 50, 70, 100},
  251. {-2, 2, 3, -1, 0, -2, 3, 2, 1, 0},
  252. {3, -1, 0, -2, 3, 2, 1, 0, 2, 1},
  253. {0, -2, 3, 2, 1, 0, 1, 2, 3, 1},
  254. }
  255. f.NewSheet("Sheet2")
  256. f.NewSheet("Sheet3")
  257. for row, data := range sheet2 {
  258. if err := f.SetSheetRow("Sheet2", fmt.Sprintf("A%d", row+1), &data); err != nil {
  259. fmt.Println(err)
  260. }
  261. }
  262. for row, data := range sheet3 {
  263. if err := f.SetSheetRow("Sheet3", fmt.Sprintf("A%d", row+1), &data); err != nil {
  264. fmt.Println(err)
  265. }
  266. }
  267. return f
  268. }