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.

804 lines
32KB

  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. "encoding/json"
  12. "errors"
  13. "strconv"
  14. "strings"
  15. )
  16. // This section defines the currently supported chart types.
  17. const (
  18. Area = "area"
  19. AreaStacked = "areaStacked"
  20. AreaPercentStacked = "areaPercentStacked"
  21. Area3D = "area3D"
  22. Area3DStacked = "area3DStacked"
  23. Area3DPercentStacked = "area3DPercentStacked"
  24. Bar = "bar"
  25. BarStacked = "barStacked"
  26. BarPercentStacked = "barPercentStacked"
  27. Bar3DClustered = "bar3DClustered"
  28. Bar3DStacked = "bar3DStacked"
  29. Bar3DPercentStacked = "bar3DPercentStacked"
  30. Bar3DConeClustered = "bar3DConeClustered"
  31. Bar3DConeStacked = "bar3DConeStacked"
  32. Bar3DConePercentStacked = "bar3DConePercentStacked"
  33. Bar3DPyramidClustered = "bar3DPyramidClustered"
  34. Bar3DPyramidStacked = "bar3DPyramidStacked"
  35. Bar3DPyramidPercentStacked = "bar3DPyramidPercentStacked"
  36. Bar3DCylinderClustered = "bar3DCylinderClustered"
  37. Bar3DCylinderStacked = "bar3DCylinderStacked"
  38. Bar3DCylinderPercentStacked = "bar3DCylinderPercentStacked"
  39. Col = "col"
  40. ColStacked = "colStacked"
  41. ColPercentStacked = "colPercentStacked"
  42. Col3D = "col3D"
  43. Col3DClustered = "col3DClustered"
  44. Col3DStacked = "col3DStacked"
  45. Col3DPercentStacked = "col3DPercentStacked"
  46. Col3DCone = "col3DCone"
  47. Col3DConeClustered = "col3DConeClustered"
  48. Col3DConeStacked = "col3DConeStacked"
  49. Col3DConePercentStacked = "col3DConePercentStacked"
  50. Col3DPyramid = "col3DPyramid"
  51. Col3DPyramidClustered = "col3DPyramidClustered"
  52. Col3DPyramidStacked = "col3DPyramidStacked"
  53. Col3DPyramidPercentStacked = "col3DPyramidPercentStacked"
  54. Col3DCylinder = "col3DCylinder"
  55. Col3DCylinderClustered = "col3DCylinderClustered"
  56. Col3DCylinderStacked = "col3DCylinderStacked"
  57. Col3DCylinderPercentStacked = "col3DCylinderPercentStacked"
  58. Doughnut = "doughnut"
  59. Line = "line"
  60. Pie = "pie"
  61. Pie3D = "pie3D"
  62. PieOfPieChart = "pieOfPie"
  63. BarOfPieChart = "barOfPie"
  64. Radar = "radar"
  65. Scatter = "scatter"
  66. Surface3D = "surface3D"
  67. WireframeSurface3D = "wireframeSurface3D"
  68. Contour = "contour"
  69. WireframeContour = "wireframeContour"
  70. Bubble = "bubble"
  71. Bubble3D = "bubble3D"
  72. )
  73. // This section defines the default value of chart properties.
  74. var (
  75. chartView3DRotX = map[string]int{
  76. Area: 0,
  77. AreaStacked: 0,
  78. AreaPercentStacked: 0,
  79. Area3D: 15,
  80. Area3DStacked: 15,
  81. Area3DPercentStacked: 15,
  82. Bar: 0,
  83. BarStacked: 0,
  84. BarPercentStacked: 0,
  85. Bar3DClustered: 15,
  86. Bar3DStacked: 15,
  87. Bar3DPercentStacked: 15,
  88. Bar3DConeClustered: 15,
  89. Bar3DConeStacked: 15,
  90. Bar3DConePercentStacked: 15,
  91. Bar3DPyramidClustered: 15,
  92. Bar3DPyramidStacked: 15,
  93. Bar3DPyramidPercentStacked: 15,
  94. Bar3DCylinderClustered: 15,
  95. Bar3DCylinderStacked: 15,
  96. Bar3DCylinderPercentStacked: 15,
  97. Col: 0,
  98. ColStacked: 0,
  99. ColPercentStacked: 0,
  100. Col3D: 15,
  101. Col3DClustered: 15,
  102. Col3DStacked: 15,
  103. Col3DPercentStacked: 15,
  104. Col3DCone: 15,
  105. Col3DConeClustered: 15,
  106. Col3DConeStacked: 15,
  107. Col3DConePercentStacked: 15,
  108. Col3DPyramid: 15,
  109. Col3DPyramidClustered: 15,
  110. Col3DPyramidStacked: 15,
  111. Col3DPyramidPercentStacked: 15,
  112. Col3DCylinder: 15,
  113. Col3DCylinderClustered: 15,
  114. Col3DCylinderStacked: 15,
  115. Col3DCylinderPercentStacked: 15,
  116. Doughnut: 0,
  117. Line: 0,
  118. Pie: 0,
  119. Pie3D: 30,
  120. PieOfPieChart: 0,
  121. BarOfPieChart: 0,
  122. Radar: 0,
  123. Scatter: 0,
  124. Surface3D: 15,
  125. WireframeSurface3D: 15,
  126. Contour: 90,
  127. WireframeContour: 90,
  128. }
  129. chartView3DRotY = map[string]int{
  130. Area: 0,
  131. AreaStacked: 0,
  132. AreaPercentStacked: 0,
  133. Area3D: 20,
  134. Area3DStacked: 20,
  135. Area3DPercentStacked: 20,
  136. Bar: 0,
  137. BarStacked: 0,
  138. BarPercentStacked: 0,
  139. Bar3DClustered: 20,
  140. Bar3DStacked: 20,
  141. Bar3DPercentStacked: 20,
  142. Bar3DConeClustered: 20,
  143. Bar3DConeStacked: 20,
  144. Bar3DConePercentStacked: 20,
  145. Bar3DPyramidClustered: 20,
  146. Bar3DPyramidStacked: 20,
  147. Bar3DPyramidPercentStacked: 20,
  148. Bar3DCylinderClustered: 20,
  149. Bar3DCylinderStacked: 20,
  150. Bar3DCylinderPercentStacked: 20,
  151. Col: 0,
  152. ColStacked: 0,
  153. ColPercentStacked: 0,
  154. Col3D: 20,
  155. Col3DClustered: 20,
  156. Col3DStacked: 20,
  157. Col3DPercentStacked: 20,
  158. Col3DCone: 20,
  159. Col3DConeClustered: 20,
  160. Col3DConeStacked: 20,
  161. Col3DConePercentStacked: 20,
  162. Col3DPyramid: 20,
  163. Col3DPyramidClustered: 20,
  164. Col3DPyramidStacked: 20,
  165. Col3DPyramidPercentStacked: 20,
  166. Col3DCylinder: 20,
  167. Col3DCylinderClustered: 20,
  168. Col3DCylinderStacked: 20,
  169. Col3DCylinderPercentStacked: 20,
  170. Doughnut: 0,
  171. Line: 0,
  172. Pie: 0,
  173. Pie3D: 0,
  174. PieOfPieChart: 0,
  175. BarOfPieChart: 0,
  176. Radar: 0,
  177. Scatter: 0,
  178. Surface3D: 20,
  179. WireframeSurface3D: 20,
  180. Contour: 0,
  181. WireframeContour: 0,
  182. }
  183. plotAreaChartOverlap = map[string]int{
  184. BarStacked: 100,
  185. BarPercentStacked: 100,
  186. ColStacked: 100,
  187. ColPercentStacked: 100,
  188. }
  189. chartView3DPerspective = map[string]int{
  190. Contour: 0,
  191. WireframeContour: 0,
  192. }
  193. chartView3DRAngAx = map[string]int{
  194. Area: 0,
  195. AreaStacked: 0,
  196. AreaPercentStacked: 0,
  197. Area3D: 1,
  198. Area3DStacked: 1,
  199. Area3DPercentStacked: 1,
  200. Bar: 0,
  201. BarStacked: 0,
  202. BarPercentStacked: 0,
  203. Bar3DClustered: 1,
  204. Bar3DStacked: 1,
  205. Bar3DPercentStacked: 1,
  206. Bar3DConeClustered: 1,
  207. Bar3DConeStacked: 1,
  208. Bar3DConePercentStacked: 1,
  209. Bar3DPyramidClustered: 1,
  210. Bar3DPyramidStacked: 1,
  211. Bar3DPyramidPercentStacked: 1,
  212. Bar3DCylinderClustered: 1,
  213. Bar3DCylinderStacked: 1,
  214. Bar3DCylinderPercentStacked: 1,
  215. Col: 0,
  216. ColStacked: 0,
  217. ColPercentStacked: 0,
  218. Col3D: 1,
  219. Col3DClustered: 1,
  220. Col3DStacked: 1,
  221. Col3DPercentStacked: 1,
  222. Col3DCone: 1,
  223. Col3DConeClustered: 1,
  224. Col3DConeStacked: 1,
  225. Col3DConePercentStacked: 1,
  226. Col3DPyramid: 1,
  227. Col3DPyramidClustered: 1,
  228. Col3DPyramidStacked: 1,
  229. Col3DPyramidPercentStacked: 1,
  230. Col3DCylinder: 1,
  231. Col3DCylinderClustered: 1,
  232. Col3DCylinderStacked: 1,
  233. Col3DCylinderPercentStacked: 1,
  234. Doughnut: 0,
  235. Line: 0,
  236. Pie: 0,
  237. Pie3D: 0,
  238. PieOfPieChart: 0,
  239. BarOfPieChart: 0,
  240. Radar: 0,
  241. Scatter: 0,
  242. Surface3D: 0,
  243. WireframeSurface3D: 0,
  244. Contour: 0,
  245. Bubble: 0,
  246. Bubble3D: 0,
  247. }
  248. chartLegendPosition = map[string]string{
  249. "bottom": "b",
  250. "left": "l",
  251. "right": "r",
  252. "top": "t",
  253. "top_right": "tr",
  254. }
  255. chartValAxNumFmtFormatCode = map[string]string{
  256. Area: "General",
  257. AreaStacked: "General",
  258. AreaPercentStacked: "0%",
  259. Area3D: "General",
  260. Area3DStacked: "General",
  261. Area3DPercentStacked: "0%",
  262. Bar: "General",
  263. BarStacked: "General",
  264. BarPercentStacked: "0%",
  265. Bar3DClustered: "General",
  266. Bar3DStacked: "General",
  267. Bar3DPercentStacked: "0%",
  268. Bar3DConeClustered: "General",
  269. Bar3DConeStacked: "General",
  270. Bar3DConePercentStacked: "0%",
  271. Bar3DPyramidClustered: "General",
  272. Bar3DPyramidStacked: "General",
  273. Bar3DPyramidPercentStacked: "0%",
  274. Bar3DCylinderClustered: "General",
  275. Bar3DCylinderStacked: "General",
  276. Bar3DCylinderPercentStacked: "0%",
  277. Col: "General",
  278. ColStacked: "General",
  279. ColPercentStacked: "0%",
  280. Col3D: "General",
  281. Col3DClustered: "General",
  282. Col3DStacked: "General",
  283. Col3DPercentStacked: "0%",
  284. Col3DCone: "General",
  285. Col3DConeClustered: "General",
  286. Col3DConeStacked: "General",
  287. Col3DConePercentStacked: "0%",
  288. Col3DPyramid: "General",
  289. Col3DPyramidClustered: "General",
  290. Col3DPyramidStacked: "General",
  291. Col3DPyramidPercentStacked: "0%",
  292. Col3DCylinder: "General",
  293. Col3DCylinderClustered: "General",
  294. Col3DCylinderStacked: "General",
  295. Col3DCylinderPercentStacked: "0%",
  296. Doughnut: "General",
  297. Line: "General",
  298. Pie: "General",
  299. Pie3D: "General",
  300. PieOfPieChart: "General",
  301. BarOfPieChart: "General",
  302. Radar: "General",
  303. Scatter: "General",
  304. Surface3D: "General",
  305. WireframeSurface3D: "General",
  306. Contour: "General",
  307. WireframeContour: "General",
  308. Bubble: "General",
  309. Bubble3D: "General",
  310. }
  311. chartValAxCrossBetween = map[string]string{
  312. Area: "midCat",
  313. AreaStacked: "midCat",
  314. AreaPercentStacked: "midCat",
  315. Area3D: "midCat",
  316. Area3DStacked: "midCat",
  317. Area3DPercentStacked: "midCat",
  318. Bar: "between",
  319. BarStacked: "between",
  320. BarPercentStacked: "between",
  321. Bar3DClustered: "between",
  322. Bar3DStacked: "between",
  323. Bar3DPercentStacked: "between",
  324. Bar3DConeClustered: "between",
  325. Bar3DConeStacked: "between",
  326. Bar3DConePercentStacked: "between",
  327. Bar3DPyramidClustered: "between",
  328. Bar3DPyramidStacked: "between",
  329. Bar3DPyramidPercentStacked: "between",
  330. Bar3DCylinderClustered: "between",
  331. Bar3DCylinderStacked: "between",
  332. Bar3DCylinderPercentStacked: "between",
  333. Col: "between",
  334. ColStacked: "between",
  335. ColPercentStacked: "between",
  336. Col3D: "between",
  337. Col3DClustered: "between",
  338. Col3DStacked: "between",
  339. Col3DPercentStacked: "between",
  340. Col3DCone: "between",
  341. Col3DConeClustered: "between",
  342. Col3DConeStacked: "between",
  343. Col3DConePercentStacked: "between",
  344. Col3DPyramid: "between",
  345. Col3DPyramidClustered: "between",
  346. Col3DPyramidStacked: "between",
  347. Col3DPyramidPercentStacked: "between",
  348. Col3DCylinder: "between",
  349. Col3DCylinderClustered: "between",
  350. Col3DCylinderStacked: "between",
  351. Col3DCylinderPercentStacked: "between",
  352. Doughnut: "between",
  353. Line: "between",
  354. Pie: "between",
  355. Pie3D: "between",
  356. PieOfPieChart: "between",
  357. BarOfPieChart: "between",
  358. Radar: "between",
  359. Scatter: "between",
  360. Surface3D: "midCat",
  361. WireframeSurface3D: "midCat",
  362. Contour: "midCat",
  363. WireframeContour: "midCat",
  364. Bubble: "midCat",
  365. Bubble3D: "midCat",
  366. }
  367. plotAreaChartGrouping = map[string]string{
  368. Area: "standard",
  369. AreaStacked: "stacked",
  370. AreaPercentStacked: "percentStacked",
  371. Area3D: "standard",
  372. Area3DStacked: "stacked",
  373. Area3DPercentStacked: "percentStacked",
  374. Bar: "clustered",
  375. BarStacked: "stacked",
  376. BarPercentStacked: "percentStacked",
  377. Bar3DClustered: "clustered",
  378. Bar3DStacked: "stacked",
  379. Bar3DPercentStacked: "percentStacked",
  380. Bar3DConeClustered: "clustered",
  381. Bar3DConeStacked: "stacked",
  382. Bar3DConePercentStacked: "percentStacked",
  383. Bar3DPyramidClustered: "clustered",
  384. Bar3DPyramidStacked: "stacked",
  385. Bar3DPyramidPercentStacked: "percentStacked",
  386. Bar3DCylinderClustered: "clustered",
  387. Bar3DCylinderStacked: "stacked",
  388. Bar3DCylinderPercentStacked: "percentStacked",
  389. Col: "clustered",
  390. ColStacked: "stacked",
  391. ColPercentStacked: "percentStacked",
  392. Col3D: "standard",
  393. Col3DClustered: "clustered",
  394. Col3DStacked: "stacked",
  395. Col3DPercentStacked: "percentStacked",
  396. Col3DCone: "standard",
  397. Col3DConeClustered: "clustered",
  398. Col3DConeStacked: "stacked",
  399. Col3DConePercentStacked: "percentStacked",
  400. Col3DPyramid: "standard",
  401. Col3DPyramidClustered: "clustered",
  402. Col3DPyramidStacked: "stacked",
  403. Col3DPyramidPercentStacked: "percentStacked",
  404. Col3DCylinder: "standard",
  405. Col3DCylinderClustered: "clustered",
  406. Col3DCylinderStacked: "stacked",
  407. Col3DCylinderPercentStacked: "percentStacked",
  408. Line: "standard",
  409. }
  410. plotAreaChartBarDir = map[string]string{
  411. Bar: "bar",
  412. BarStacked: "bar",
  413. BarPercentStacked: "bar",
  414. Bar3DClustered: "bar",
  415. Bar3DStacked: "bar",
  416. Bar3DPercentStacked: "bar",
  417. Bar3DConeClustered: "bar",
  418. Bar3DConeStacked: "bar",
  419. Bar3DConePercentStacked: "bar",
  420. Bar3DPyramidClustered: "bar",
  421. Bar3DPyramidStacked: "bar",
  422. Bar3DPyramidPercentStacked: "bar",
  423. Bar3DCylinderClustered: "bar",
  424. Bar3DCylinderStacked: "bar",
  425. Bar3DCylinderPercentStacked: "bar",
  426. Col: "col",
  427. ColStacked: "col",
  428. ColPercentStacked: "col",
  429. Col3D: "col",
  430. Col3DClustered: "col",
  431. Col3DStacked: "col",
  432. Col3DPercentStacked: "col",
  433. Col3DCone: "col",
  434. Col3DConeStacked: "col",
  435. Col3DConeClustered: "col",
  436. Col3DConePercentStacked: "col",
  437. Col3DPyramid: "col",
  438. Col3DPyramidClustered: "col",
  439. Col3DPyramidStacked: "col",
  440. Col3DPyramidPercentStacked: "col",
  441. Col3DCylinder: "col",
  442. Col3DCylinderClustered: "col",
  443. Col3DCylinderStacked: "col",
  444. Col3DCylinderPercentStacked: "col",
  445. Line: "standard",
  446. }
  447. orientation = map[bool]string{
  448. true: "maxMin",
  449. false: "minMax",
  450. }
  451. catAxPos = map[bool]string{
  452. true: "t",
  453. false: "b",
  454. }
  455. valAxPos = map[bool]string{
  456. true: "r",
  457. false: "l",
  458. }
  459. valTickLblPos = map[string]string{
  460. Contour: "none",
  461. WireframeContour: "none",
  462. }
  463. )
  464. // parseFormatChartSet provides a function to parse the format settings of the
  465. // chart with default value.
  466. func parseFormatChartSet(formatSet string) (*formatChart, error) {
  467. format := formatChart{
  468. Dimension: formatChartDimension{
  469. Width: 480,
  470. Height: 290,
  471. },
  472. Format: formatPicture{
  473. FPrintsWithSheet: true,
  474. FLocksWithSheet: false,
  475. NoChangeAspect: false,
  476. OffsetX: 0,
  477. OffsetY: 0,
  478. XScale: 1.0,
  479. YScale: 1.0,
  480. },
  481. Legend: formatChartLegend{
  482. Position: "bottom",
  483. ShowLegendKey: false,
  484. },
  485. Title: formatChartTitle{
  486. Name: " ",
  487. },
  488. ShowBlanksAs: "gap",
  489. }
  490. err := json.Unmarshal([]byte(formatSet), &format)
  491. return &format, err
  492. }
  493. // AddChart provides the method to add chart in a sheet by given chart format
  494. // set (such as offset, scale, aspect ratio setting and print settings) and
  495. // properties set. For example, create 3D clustered column chart with data
  496. // Sheet1!$E$1:$L$15:
  497. //
  498. // package main
  499. //
  500. // import "github.com/360EntSecGroup-Skylar/excelize"
  501. //
  502. // func main() {
  503. // categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
  504. // values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
  505. // f := excelize.NewFile()
  506. // for k, v := range categories {
  507. // f.SetCellValue("Sheet1", k, v)
  508. // }
  509. // for k, v := range values {
  510. // f.SetCellValue("Sheet1", k, v)
  511. // }
  512. // if err := f.AddChart("Sheet1", "E1", `{"type":"col3DClustered","series":[{"name":"Sheet1!$A$2","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"},{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"title":{"name":"Fruit 3D Clustered Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"reverse_order":true},"y_axis":{"maximum":7.5,"minimum":0.5}}`); err != nil {
  513. // println(err.Error())
  514. // return
  515. // }
  516. // // Save xlsx file by the given path.
  517. // if err := f.SaveAs("Book1.xlsx"); err != nil {
  518. // println(err.Error())
  519. // }
  520. // }
  521. //
  522. // The following shows the type of chart supported by excelize:
  523. //
  524. // Type | Chart
  525. // -----------------------------+------------------------------
  526. // area | 2D area chart
  527. // areaStacked | 2D stacked area chart
  528. // areaPercentStacked | 2D 100% stacked area chart
  529. // area3D | 3D area chart
  530. // area3DStacked | 3D stacked area chart
  531. // area3DPercentStacked | 3D 100% stacked area chart
  532. // bar | 2D clustered bar chart
  533. // barStacked | 2D stacked bar chart
  534. // barPercentStacked | 2D 100% stacked bar chart
  535. // bar3DClustered | 3D clustered bar chart
  536. // bar3DStacked | 3D stacked bar chart
  537. // bar3DPercentStacked | 3D 100% stacked bar chart
  538. // bar3DConeClustered | 3D cone clustered bar chart
  539. // bar3DConeStacked | 3D cone stacked bar chart
  540. // bar3DConePercentStacked | 3D cone percent bar chart
  541. // bar3DPyramidClustered | 3D pyramid clustered bar chart
  542. // bar3DPyramidStacked | 3D pyramid stacked bar chart
  543. // bar3DPyramidPercentStacked | 3D pyramid percent stacked bar chart
  544. // bar3DCylinderClustered | 3D cylinder clustered bar chart
  545. // bar3DCylinderStacked | 3D cylinder stacked bar chart
  546. // bar3DCylinderPercentStacked | 3D cylinder percent stacked bar chart
  547. // col | 2D clustered column chart
  548. // colStacked | 2D stacked column chart
  549. // colPercentStacked | 2D 100% stacked column chart
  550. // col3DClustered | 3D clustered column chart
  551. // col3D | 3D column chart
  552. // col3DStacked | 3D stacked column chart
  553. // col3DPercentStacked | 3D 100% stacked column chart
  554. // col3DCone | 3D cone column chart
  555. // col3DConeClustered | 3D cone clustered column chart
  556. // col3DConeStacked | 3D cone stacked column chart
  557. // col3DConePercentStacked | 3D cone percent stacked column chart
  558. // col3DPyramid | 3D pyramid column chart
  559. // col3DPyramidClustered | 3D pyramid clustered column chart
  560. // col3DPyramidStacked | 3D pyramid stacked column chart
  561. // col3DPyramidPercentStacked | 3D pyramid percent stacked column chart
  562. // col3DCylinder | 3D cylinder column chart
  563. // col3DCylinderClustered | 3D cylinder clustered column chart
  564. // col3DCylinderStacked | 3D cylinder stacked column chart
  565. // col3DCylinderPercentStacked | 3D cylinder percent stacked column chart
  566. // doughnut | doughnut chart
  567. // line | line chart
  568. // pie | pie chart
  569. // pie3D | 3D pie chart
  570. // pieOfPie | pie of pie chart
  571. // barOfPie | bar of pie chart
  572. // radar | radar chart
  573. // scatter | scatter chart
  574. // surface3D | 3D surface chart
  575. // wireframeSurface3D | 3D wireframe surface chart
  576. // contour | contour chart
  577. // wireframeContour | wireframe contour chart
  578. // bubble | bubble chart
  579. // bubble3D | 3D bubble chart
  580. //
  581. // In Excel a chart series is a collection of information that defines which data is plotted such as values, axis labels and formatting.
  582. //
  583. // The series options that can be set are:
  584. //
  585. // name
  586. // categories
  587. // values
  588. // line
  589. //
  590. // name: Set the name for the series. The name is displayed in the chart legend and in the formula bar. The name property is optional and if it isn't supplied it will default to Series 1..n. The name can also be a formula such as Sheet1!$A$1
  591. //
  592. // categories: This sets the chart category labels. The category is more or less the same as the X axis. In most chart types the categories property is optional and the chart will just assume a sequential series from 1..n.
  593. //
  594. // values: This is the most important property of a series and is the only mandatory option for every chart object. This option links the chart with the worksheet data that it displays.
  595. //
  596. // line: This sets the line format of the line chart. The line property is optional and if it isn't supplied it will default style. The options that can be set is width. The range of width is 0.25pt - 999pt. If the value of width is outside the range, the default width of the line is 2pt.
  597. //
  598. // Set properties of the chart legend. The options that can be set are:
  599. //
  600. // position
  601. // show_legend_key
  602. //
  603. // position: Set the position of the chart legend. The default legend position is right. The available positions are:
  604. //
  605. // top
  606. // bottom
  607. // left
  608. // right
  609. // top_right
  610. //
  611. // show_legend_key: Set the legend keys shall be shown in data labels. The default value is false.
  612. //
  613. // Set properties of the chart title. The properties that can be set are:
  614. //
  615. // title
  616. //
  617. // name: Set the name (title) for the chart. The name is displayed above the chart. The name can also be a formula such as Sheet1!$A$1 or a list with a sheetname. The name property is optional. The default is to have no chart title.
  618. //
  619. // Specifies how blank cells are plotted on the chart by show_blanks_as. The default value is gap. The options that can be set are:
  620. //
  621. // gap
  622. // span
  623. // zero
  624. //
  625. // gap: Specifies that blank values shall be left as a gap.
  626. //
  627. // sapn: Specifies that blank values shall be spanned with a line.
  628. //
  629. // zero: Specifies that blank values shall be treated as zero.
  630. //
  631. // Set chart offset, scale, aspect ratio setting and print settings by format, same as function AddPicture.
  632. //
  633. // Set the position of the chart plot area by plotarea. The properties that can be set are:
  634. //
  635. // show_bubble_size
  636. // show_cat_name
  637. // show_leader_lines
  638. // show_percent
  639. // show_series_name
  640. // show_val
  641. //
  642. // show_bubble_size: Specifies the bubble size shall be shown in a data label. The show_bubble_size property is optional. The default value is false.
  643. //
  644. // show_cat_name: Specifies that the category name shall be shown in the data label. The show_cat_name property is optional. The default value is true.
  645. //
  646. // show_leader_lines: Specifies leader lines shall be shown for data labels. The show_leader_lines property is optional. The default value is false.
  647. //
  648. // show_percent: Specifies that the percentage shall be shown in a data label. The show_percent property is optional. The default value is false.
  649. //
  650. // show_series_name: Specifies that the series name shall be shown in a data label. The show_series_name property is optional. The default value is false.
  651. //
  652. // show_val: Specifies that the value shall be shown in a data label. The show_val property is optional. The default value is false.
  653. //
  654. // Set the primary horizontal and vertical axis options by x_axis and y_axis. The properties of x_axis that can be set are:
  655. //
  656. // major_grid_lines
  657. // minor_grid_lines
  658. // tick_label_skip
  659. // reverse_order
  660. // maximum
  661. // minimum
  662. //
  663. // The properties of y_axis that can be set are:
  664. //
  665. // major_grid_lines
  666. // minor_grid_lines
  667. // major_unit
  668. // reverse_order
  669. // maximum
  670. // minimum
  671. //
  672. // major_grid_lines: Specifies major gridlines.
  673. //
  674. // minor_grid_lines: Specifies minor gridlines.
  675. //
  676. // major_unit: Specifies the distance between major ticks. Shall contain a positive floating-point number. The major_unit property is optional. The default value is auto.
  677. //
  678. // tick_label_skip: Specifies how many tick labels to skip between label that is drawn. The tick_label_skip property is optional. The default value is auto.
  679. //
  680. // reverse_order: Specifies that the categories or values on reverse order (orientation of the chart). The reverse_order property is optional. The default value is false.
  681. //
  682. // maximum: Specifies that the fixed maximum, 0 is auto. The maximum property is optional. The default value is auto.
  683. //
  684. // minimum: Specifies that the fixed minimum, 0 is auto. The minimum property is optional. The default value is auto.
  685. //
  686. // Set chart size by dimension property. The dimension property is optional. The default width is 480, and height is 290.
  687. //
  688. // combo: Specifies the create a chart that combines two or more chart types
  689. // in a single chart. For example, create a clustered column - line chart with
  690. // data Sheet1!$E$1:$L$15:
  691. //
  692. // package main
  693. //
  694. // import "github.com/360EntSecGroup-Skylar/excelize"
  695. //
  696. // func main() {
  697. // categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
  698. // values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
  699. // f := excelize.NewFile()
  700. // for k, v := range categories {
  701. // f.SetCellValue("Sheet1", k, v)
  702. // }
  703. // for k, v := range values {
  704. // f.SetCellValue("Sheet1", k, v)
  705. // }
  706. // if err := f.AddChart("Sheet1", "E1", `{"type":"col","series":[{"name":"Sheet1!$A$2","categories":"","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Clustered Column - Line Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true}}`, `{"type":"line","series":[{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true}}`); err != nil {
  707. // println(err.Error())
  708. // return
  709. // }
  710. // // Save xlsx file by the given path.
  711. // if err := f.SaveAs("Book1.xlsx"); err != nil {
  712. // println(err.Error())
  713. // }
  714. // }
  715. //
  716. func (f *File) AddChart(sheet, cell, format string, combo ...string) error {
  717. formatSet, err := parseFormatChartSet(format)
  718. if err != nil {
  719. return err
  720. }
  721. comboCharts := []*formatChart{}
  722. for _, comboFormat := range combo {
  723. comboChart, err := parseFormatChartSet(comboFormat)
  724. if err != nil {
  725. return err
  726. }
  727. if _, ok := chartValAxNumFmtFormatCode[comboChart.Type]; !ok {
  728. return errors.New("unsupported chart type " + comboChart.Type)
  729. }
  730. comboCharts = append(comboCharts, comboChart)
  731. }
  732. // Read sheet data.
  733. xlsx, err := f.workSheetReader(sheet)
  734. if err != nil {
  735. return err
  736. }
  737. if _, ok := chartValAxNumFmtFormatCode[formatSet.Type]; !ok {
  738. return errors.New("unsupported chart type " + formatSet.Type)
  739. }
  740. // Add first picture for given sheet, create xl/drawings/ and xl/drawings/_rels/ folder.
  741. drawingID := f.countDrawings() + 1
  742. chartID := f.countCharts() + 1
  743. drawingXML := "xl/drawings/drawing" + strconv.Itoa(drawingID) + ".xml"
  744. drawingID, drawingXML = f.prepareDrawing(xlsx, drawingID, sheet, drawingXML)
  745. drawingRels := "xl/drawings/_rels/drawing" + strconv.Itoa(drawingID) + ".xml.rels"
  746. drawingRID := f.addRels(drawingRels, SourceRelationshipChart, "../charts/chart"+strconv.Itoa(chartID)+".xml", "")
  747. err = f.addDrawingChart(sheet, drawingXML, cell, formatSet.Dimension.Width, formatSet.Dimension.Height, drawingRID, &formatSet.Format)
  748. if err != nil {
  749. return err
  750. }
  751. f.addChart(formatSet, comboCharts)
  752. f.addContentTypePart(chartID, "chart")
  753. f.addContentTypePart(drawingID, "drawings")
  754. return err
  755. }
  756. // DeleteChart provides a function to delete chart in XLSX by given worksheet
  757. // and cell name.
  758. func (f *File) DeleteChart(sheet, cell string) (err error) {
  759. col, row, err := CellNameToCoordinates(cell)
  760. if err != nil {
  761. return
  762. }
  763. col--
  764. row--
  765. ws, err := f.workSheetReader(sheet)
  766. if err != nil {
  767. return
  768. }
  769. if ws.Drawing == nil {
  770. return
  771. }
  772. drawingXML := strings.Replace(f.getSheetRelationshipsTargetByID(sheet, ws.Drawing.RID), "..", "xl", -1)
  773. return f.deleteDrawing(col, row, drawingXML, "Chart")
  774. }
  775. // countCharts provides a function to get chart files count storage in the
  776. // folder xl/charts.
  777. func (f *File) countCharts() int {
  778. count := 0
  779. for k := range f.XLSX {
  780. if strings.Contains(k, "xl/charts/chart") {
  781. count++
  782. }
  783. }
  784. return count
  785. }
  786. // ptToEMUs provides a function to convert pt to EMUs, 1 pt = 12700 EMUs. The
  787. // range of pt is 0.25pt - 999pt. If the value of pt is outside the range, the
  788. // default EMUs will be returned.
  789. func (f *File) ptToEMUs(pt float64) int {
  790. if 0.25 > pt || pt > 999 {
  791. return 25400
  792. }
  793. return int(12700 * pt)
  794. }