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.

2828 lines
86KB

  1. // Copyright 2016 - 2019 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. "encoding/xml"
  13. "fmt"
  14. "math"
  15. "strconv"
  16. "strings"
  17. )
  18. // Excel styles can reference number formats that are built-in, all of which
  19. // have an id less than 164. This is a possibly incomplete list comprised of
  20. // as many of them as I could find.
  21. var builtInNumFmt = map[int]string{
  22. 0: "general",
  23. 1: "0",
  24. 2: "0.00",
  25. 3: "#,##0",
  26. 4: "#,##0.00",
  27. 9: "0%",
  28. 10: "0.00%",
  29. 11: "0.00e+00",
  30. 12: "# ?/?",
  31. 13: "# ??/??",
  32. 14: "mm-dd-yy",
  33. 15: "d-mmm-yy",
  34. 16: "d-mmm",
  35. 17: "mmm-yy",
  36. 18: "h:mm am/pm",
  37. 19: "h:mm:ss am/pm",
  38. 20: "h:mm",
  39. 21: "h:mm:ss",
  40. 22: "m/d/yy h:mm",
  41. 37: "#,##0 ;(#,##0)",
  42. 38: "#,##0 ;[red](#,##0)",
  43. 39: "#,##0.00;(#,##0.00)",
  44. 40: "#,##0.00;[red](#,##0.00)",
  45. 41: `_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)`,
  46. 42: `_("$"* #,##0_);_("$* \(#,##0\);_("$"* "-"_);_(@_)`,
  47. 43: `_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)`,
  48. 44: `_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)`,
  49. 45: "mm:ss",
  50. 46: "[h]:mm:ss",
  51. 47: "mmss.0",
  52. 48: "##0.0e+0",
  53. 49: "@",
  54. }
  55. // langNumFmt defined number format code (with unicode values provided for
  56. // language glyphs where they occur) in different language.
  57. var langNumFmt = map[string]map[int]string{
  58. "zh-tw": {
  59. 27: "[$-404]e/m/d",
  60. 28: `[$-404]e"年"m"月"d"日"`,
  61. 29: `[$-404]e"年"m"月"d"日"`,
  62. 30: "m/d/yy",
  63. 31: `yyyy"年"m"月"d"日"`,
  64. 32: `hh"時"mm"分"`,
  65. 33: `hh"時"mm"分"ss"秒"`,
  66. 34: `上午/下午 hh"時"mm"分"`,
  67. 35: `上午/下午 hh"時"mm"分"ss"秒"`,
  68. 36: "[$-404]e/m/d",
  69. 50: "[$-404]e/m/d",
  70. 51: `[$-404]e"年"m"月"d"日"`,
  71. 52: `上午/下午 hh"時"mm"分"`,
  72. 53: `上午/下午 hh"時"mm"分"ss"秒"`,
  73. 54: `[$-404]e"年"m"月"d"日"`,
  74. 55: `上午/下午 hh"時"mm"分"`,
  75. 56: `上午/下午 hh"時"mm"分"ss"秒"`,
  76. 57: "[$-404]e/m/d",
  77. 58: `[$-404]e"年"m"月"d"日"`,
  78. },
  79. "zh-cn": {
  80. 27: `yyyy"年"m"月"`,
  81. 28: `m"月"d"日"`,
  82. 29: `m"月"d"日"`,
  83. 30: "m-d-yy",
  84. 31: `yyyy"年"m"月"d"日"`,
  85. 32: `h"时"mm"分"`,
  86. 33: `h"时"mm"分"ss"秒"`,
  87. 34: `上午/下午 h"时"mm"分"`,
  88. 35: `上午/下午 h"时"mm"分"ss"秒"`,
  89. 36: `yyyy"年"m"月"`,
  90. 50: `yyyy"年"m"月"`,
  91. 51: `m"月"d"日"`,
  92. 52: `yyyy"年"m"月"`,
  93. 53: `m"月"d"日"`,
  94. 54: `m"月"d"日"`,
  95. 55: `上午/下午 h"时"mm"分"`,
  96. 56: `上午/下午 h"时"mm"分"ss"秒"`,
  97. 57: `yyyy"年"m"月"`,
  98. 58: `m"月"d"日"`,
  99. },
  100. "zh-tw_unicode": {
  101. 27: "[$-404]e/m/d",
  102. 28: `[$-404]e"5E74"m"6708"d"65E5"`,
  103. 29: `[$-404]e"5E74"m"6708"d"65E5"`,
  104. 30: "m/d/yy",
  105. 31: `yyyy"5E74"m"6708"d"65E5"`,
  106. 32: `hh"6642"mm"5206"`,
  107. 33: `hh"6642"mm"5206"ss"79D2"`,
  108. 34: `4E0A5348/4E0B5348hh"6642"mm"5206"`,
  109. 35: `4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2"`,
  110. 36: "[$-404]e/m/d",
  111. 50: "[$-404]e/m/d",
  112. 51: `[$-404]e"5E74"m"6708"d"65E5"`,
  113. 52: `4E0A5348/4E0B5348hh"6642"mm"5206"`,
  114. 53: `4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2"`,
  115. 54: `[$-404]e"5E74"m"6708"d"65E5"`,
  116. 55: `4E0A5348/4E0B5348hh"6642"mm"5206"`,
  117. 56: `4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2"`,
  118. 57: "[$-404]e/m/d",
  119. 58: `[$-404]e"5E74"m"6708"d"65E5"`,
  120. },
  121. "zh-cn_unicode": {
  122. 27: `yyyy"5E74"m"6708"`,
  123. 28: `m"6708"d"65E5"`,
  124. 29: `m"6708"d"65E5"`,
  125. 30: "m-d-yy",
  126. 31: `yyyy"5E74"m"6708"d"65E5"`,
  127. 32: `h"65F6"mm"5206"`,
  128. 33: `h"65F6"mm"5206"ss"79D2"`,
  129. 34: `4E0A5348/4E0B5348h"65F6"mm"5206"`,
  130. 35: `4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2"`,
  131. 36: `yyyy"5E74"m"6708"`,
  132. 50: `yyyy"5E74"m"6708"`,
  133. 51: `m"6708"d"65E5"`,
  134. 52: `yyyy"5E74"m"6708"`,
  135. 53: `m"6708"d"65E5"`,
  136. 54: `m"6708"d"65E5"`,
  137. 55: `4E0A5348/4E0B5348h"65F6"mm"5206"`,
  138. 56: `4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2"`,
  139. 57: `yyyy"5E74"m"6708"`,
  140. 58: `m"6708"d"65E5"`,
  141. },
  142. "ja-jp": {
  143. 27: "[$-411]ge.m.d",
  144. 28: `[$-411]ggge"年"m"月"d"日"`,
  145. 29: `[$-411]ggge"年"m"月"d"日"`,
  146. 30: "m/d/yy",
  147. 31: `yyyy"年"m"月"d"日"`,
  148. 32: `h"時"mm"分"`,
  149. 33: `h"時"mm"分"ss"秒"`,
  150. 34: `yyyy"年"m"月"`,
  151. 35: `m"月"d"日"`,
  152. 36: "[$-411]ge.m.d",
  153. 50: "[$-411]ge.m.d",
  154. 51: `[$-411]ggge"年"m"月"d"日"`,
  155. 52: `yyyy"年"m"月"`,
  156. 53: `m"月"d"日"`,
  157. 54: `[$-411]ggge"年"m"月"d"日"`,
  158. 55: `yyyy"年"m"月"`,
  159. 56: `m"月"d"日"`,
  160. 57: "[$-411]ge.m.d",
  161. 58: `[$-411]ggge"年"m"月"d"日"`,
  162. },
  163. "ko-kr": {
  164. 27: `yyyy"年" mm"月" dd"日"`,
  165. 28: "mm-dd",
  166. 29: "mm-dd",
  167. 30: "mm-dd-yy",
  168. 31: `yyyy"년" mm"월" dd"일"`,
  169. 32: `h"시" mm"분"`,
  170. 33: `h"시" mm"분" ss"초"`,
  171. 34: `yyyy-mm-dd`,
  172. 35: `yyyy-mm-dd`,
  173. 36: `yyyy"年" mm"月" dd"日"`,
  174. 50: `yyyy"年" mm"月" dd"日"`,
  175. 51: "mm-dd",
  176. 52: "yyyy-mm-dd",
  177. 53: "yyyy-mm-dd",
  178. 54: "mm-dd",
  179. 55: "yyyy-mm-dd",
  180. 56: "yyyy-mm-dd",
  181. 57: `yyyy"年" mm"月" dd"日"`,
  182. 58: "mm-dd",
  183. },
  184. "ja-jp_unicode": {
  185. 27: "[$-411]ge.m.d",
  186. 28: `[$-411]ggge"5E74"m"6708"d"65E5"`,
  187. 29: `[$-411]ggge"5E74"m"6708"d"65E5"`,
  188. 30: "m/d/yy",
  189. 31: `yyyy"5E74"m"6708"d"65E5"`,
  190. 32: `h"6642"mm"5206"`,
  191. 33: `h"6642"mm"5206"ss"79D2"`,
  192. 34: `yyyy"5E74"m"6708"`,
  193. 35: `m"6708"d"65E5"`,
  194. 36: "[$-411]ge.m.d",
  195. 50: "[$-411]ge.m.d",
  196. 51: `[$-411]ggge"5E74"m"6708"d"65E5"`,
  197. 52: `yyyy"5E74"m"6708"`,
  198. 53: `m"6708"d"65E5"`,
  199. 54: `[$-411]ggge"5E74"m"6708"d"65E5"`,
  200. 55: `yyyy"5E74"m"6708"`,
  201. 56: `m"6708"d"65E5"`,
  202. 57: "[$-411]ge.m.d",
  203. 58: `[$-411]ggge"5E74"m"6708"d"65E5"`,
  204. },
  205. "ko-kr_unicode": {
  206. 27: `yyyy"5E74" mm"6708" dd"65E5"`,
  207. 28: "mm-dd",
  208. 29: "mm-dd",
  209. 30: "mm-dd-yy",
  210. 31: `yyyy"B144" mm"C6D4" dd"C77C"`,
  211. 32: `h"C2DC" mm"BD84"`,
  212. 33: `h"C2DC" mm"BD84" ss"CD08"`,
  213. 34: "yyyy-mm-dd",
  214. 35: "yyyy-mm-dd",
  215. 36: `yyyy"5E74" mm"6708" dd"65E5"`,
  216. 50: `yyyy"5E74" mm"6708" dd"65E5"`,
  217. 51: "mm-dd",
  218. 52: "yyyy-mm-dd",
  219. 53: "yyyy-mm-dd",
  220. 54: "mm-dd",
  221. 55: "yyyy-mm-dd",
  222. 56: "yyyy-mm-dd",
  223. 57: `yyyy"5E74" mm"6708" dd"65E5"`,
  224. 58: "mm-dd",
  225. },
  226. "th-th": {
  227. 59: "t0",
  228. 60: "t0.00",
  229. 61: "t#,##0",
  230. 62: "t#,##0.00",
  231. 67: "t0%",
  232. 68: "t0.00%",
  233. 69: "t# ?/?",
  234. 70: "t# ??/??",
  235. 71: "ว/ด/ปปปป",
  236. 72: "ว-ดดด-ปป",
  237. 73: "ว-ดดด",
  238. 74: "ดดด-ปป",
  239. 75: "ช:นน",
  240. 76: "ช:นน:ทท",
  241. 77: "ว/ด/ปปปป ช:นน",
  242. 78: "นน:ทท",
  243. 79: "[ช]:นน:ทท",
  244. 80: "นน:ทท.0",
  245. 81: "d/m/bb",
  246. },
  247. "th-th_unicode": {
  248. 59: "t0",
  249. 60: "t0.00",
  250. 61: "t#,##0",
  251. 62: "t#,##0.00",
  252. 67: "t0%",
  253. 68: "t0.00%",
  254. 69: "t# ?/?",
  255. 70: "t# ??/??",
  256. 71: "0E27/0E14/0E1B0E1B0E1B0E1B",
  257. 72: "0E27-0E140E140E14-0E1B0E1B",
  258. 73: "0E27-0E140E140E14",
  259. 74: "0E140E140E14-0E1B0E1B",
  260. 75: "0E0A:0E190E19",
  261. 76: "0E0A:0E190E19:0E170E17",
  262. 77: "0E27/0E14/0E1B0E1B0E1B0E1B 0E0A:0E190E19",
  263. 78: "0E190E19:0E170E17",
  264. 79: "[0E0A]:0E190E19:0E170E17",
  265. 80: "0E190E19:0E170E17.0",
  266. 81: "d/m/bb",
  267. },
  268. }
  269. // currencyNumFmt defined the currency number format map.
  270. var currencyNumFmt = map[int]string{
  271. 164: `"CN¥",##0.00`,
  272. 165: "[$$-409]#,##0.00",
  273. 166: "[$$-45C]#,##0.00",
  274. 167: "[$$-1004]#,##0.00",
  275. 168: "[$$-404]#,##0.00",
  276. 169: "[$$-C09]#,##0.00",
  277. 170: "[$$-2809]#,##0.00",
  278. 171: "[$$-1009]#,##0.00",
  279. 172: "[$$-2009]#,##0.00",
  280. 173: "[$$-1409]#,##0.00",
  281. 174: "[$$-4809]#,##0.00",
  282. 175: "[$$-2C09]#,##0.00",
  283. 176: "[$$-2409]#,##0.00",
  284. 177: "[$$-1000]#,##0.00",
  285. 178: `#,##0.00\ [$$-C0C]`,
  286. 179: "[$$-475]#,##0.00",
  287. 180: "[$$-83E]#,##0.00",
  288. 181: `[$$-86B]\ #,##0.00`,
  289. 182: `[$$-340A]\ #,##0.00`,
  290. 183: "[$$-240A]#,##0.00",
  291. 184: `[$$-300A]\ #,##0.00`,
  292. 185: "[$$-440A]#,##0.00",
  293. 186: "[$$-80A]#,##0.00",
  294. 187: "[$$-500A]#,##0.00",
  295. 188: "[$$-540A]#,##0.00",
  296. 189: `[$$-380A]\ #,##0.00`,
  297. 190: "[$£-809]#,##0.00",
  298. 191: "[$£-491]#,##0.00",
  299. 192: "[$£-452]#,##0.00",
  300. 193: "[$¥-804]#,##0.00",
  301. 194: "[$¥-411]#,##0.00",
  302. 195: "[$¥-478]#,##0.00",
  303. 196: "[$¥-451]#,##0.00",
  304. 197: "[$¥-480]#,##0.00",
  305. 198: "#,##0.00\\ [$\u058F-42B]",
  306. 199: "[$\u060B-463]#,##0.00",
  307. 200: "[$\u060B-48C]#,##0.00",
  308. 201: "[$\u09F3-845]\\ #,##0.00",
  309. 202: "#,##0.00[$\u17DB-453]",
  310. 203: "[$\u20A1-140A]#,##0.00",
  311. 204: "[$\u20A6-468]\\ #,##0.00",
  312. 205: "[$\u20A6-470]\\ #,##0.00",
  313. 206: "[$\u20A9-412]#,##0.00",
  314. 207: "[$\u20AA-40D]\\ #,##0.00",
  315. 208: "#,##0.00\\ [$\u20AB-42A]",
  316. 209: "#,##0.00\\ [$\u20AC-42D]",
  317. 210: "#,##0.00\\ [$\u20AC-47E]",
  318. 211: "#,##0.00\\ [$\u20AC-403]",
  319. 212: "#,##0.00\\ [$\u20AC-483]",
  320. 213: "[$\u20AC-813]\\ #,##0.00",
  321. 214: "[$\u20AC-413]\\ #,##0.00",
  322. 215: "[$\u20AC-1809]#,##0.00",
  323. 216: "#,##0.00\\ [$\u20AC-425]",
  324. 217: "[$\u20AC-2]\\ #,##0.00",
  325. 218: "#,##0.00\\ [$\u20AC-1]",
  326. 219: "#,##0.00\\ [$\u20AC-40B]",
  327. 220: "#,##0.00\\ [$\u20AC-80C]",
  328. 221: "#,##0.00\\ [$\u20AC-40C]",
  329. 222: "#,##0.00\\ [$\u20AC-140C]",
  330. 223: "#,##0.00\\ [$\u20AC-180C]",
  331. 224: "[$\u20AC-200C]#,##0.00",
  332. 225: "#,##0.00\\ [$\u20AC-456]",
  333. 226: "#,##0.00\\ [$\u20AC-C07]",
  334. 227: "#,##0.00\\ [$\u20AC-407]",
  335. 228: "#,##0.00\\ [$\u20AC-1007]",
  336. 229: "#,##0.00\\ [$\u20AC-408]",
  337. 230: "#,##0.00\\ [$\u20AC-243B]",
  338. 231: "[$\u20AC-83C]#,##0.00",
  339. 232: "[$\u20AC-410]\\ #,##0.00",
  340. 233: "[$\u20AC-476]#,##0.00",
  341. 234: "#,##0.00\\ [$\u20AC-2C1A]",
  342. 235: "[$\u20AC-426]\\ #,##0.00",
  343. 236: "#,##0.00\\ [$\u20AC-427]",
  344. 237: "#,##0.00\\ [$\u20AC-82E]",
  345. 238: "#,##0.00\\ [$\u20AC-46E]",
  346. 239: "[$\u20AC-43A]#,##0.00",
  347. 240: "#,##0.00\\ [$\u20AC-C3B]",
  348. 241: "#,##0.00\\ [$\u20AC-482]",
  349. 242: "#,##0.00\\ [$\u20AC-816]",
  350. 243: "#,##0.00\\ [$\u20AC-301A]",
  351. 244: "#,##0.00\\ [$\u20AC-203B]",
  352. 245: "#,##0.00\\ [$\u20AC-41B]",
  353. 246: "#,##0.00\\ [$\u20AC-424]",
  354. 247: "#,##0.00\\ [$\u20AC-C0A]",
  355. 248: "#,##0.00\\ [$\u20AC-81D]",
  356. 249: "#,##0.00\\ [$\u20AC-484]",
  357. 250: "#,##0.00\\ [$\u20AC-42E]",
  358. 251: "[$\u20AC-462]\\ #,##0.00",
  359. 252: "#,##0.00\\ [$₭-454]",
  360. 253: "#,##0.00\\ [$₮-450]",
  361. 254: "[$\u20AE-C50]#,##0.00",
  362. 255: "[$\u20B1-3409]#,##0.00",
  363. 256: "[$\u20B1-464]#,##0.00",
  364. 257: "#,##0.00[$\u20B4-422]",
  365. 258: "[$\u20B8-43F]#,##0.00",
  366. 259: "[$\u20B9-460]#,##0.00",
  367. 260: "[$\u20B9-4009]\\ #,##0.00",
  368. 261: "[$\u20B9-447]\\ #,##0.00",
  369. 262: "[$\u20B9-439]\\ #,##0.00",
  370. 263: "[$\u20B9-44B]\\ #,##0.00",
  371. 264: "[$\u20B9-860]#,##0.00",
  372. 265: "[$\u20B9-457]\\ #,##0.00",
  373. 266: "[$\u20B9-458]#,##0.00",
  374. 267: "[$\u20B9-44E]\\ #,##0.00",
  375. 268: "[$\u20B9-861]#,##0.00",
  376. 269: "[$\u20B9-448]\\ #,##0.00",
  377. 270: "[$\u20B9-446]\\ #,##0.00",
  378. 271: "[$\u20B9-44F]\\ #,##0.00",
  379. 272: "[$\u20B9-459]#,##0.00",
  380. 273: "[$\u20B9-449]\\ #,##0.00",
  381. 274: "[$\u20B9-820]#,##0.00",
  382. 275: "#,##0.00\\ [$\u20BA-41F]",
  383. 276: "#,##0.00\\ [$\u20BC-42C]",
  384. 277: "#,##0.00\\ [$\u20BC-82C]",
  385. 278: "#,##0.00\\ [$\u20BD-419]",
  386. 279: "#,##0.00[$\u20BD-485]",
  387. 280: "#,##0.00\\ [$\u20BE-437]",
  388. 281: "[$B/.-180A]\\ #,##0.00",
  389. 282: "[$Br-472]#,##0.00",
  390. 283: "[$Br-477]#,##0.00",
  391. 284: "#,##0.00[$Br-473]",
  392. 285: "[$Bs-46B]\\ #,##0.00",
  393. 286: "[$Bs-400A]\\ #,##0.00",
  394. 287: "[$Bs.-200A]\\ #,##0.00",
  395. 288: "[$BWP-832]\\ #,##0.00",
  396. 289: "[$C$-4C0A]#,##0.00",
  397. 290: "[$CA$-85D]#,##0.00",
  398. 291: "[$CA$-47C]#,##0.00",
  399. 292: "[$CA$-45D]#,##0.00",
  400. 293: "[$CFA-340C]#,##0.00",
  401. 294: "[$CFA-280C]#,##0.00",
  402. 295: "#,##0.00\\ [$CFA-867]",
  403. 296: "#,##0.00\\ [$CFA-488]",
  404. 297: "#,##0.00\\ [$CHF-100C]",
  405. 298: "[$CHF-1407]\\ #,##0.00",
  406. 299: "[$CHF-807]\\ #,##0.00",
  407. 300: "[$CHF-810]\\ #,##0.00",
  408. 301: "[$CHF-417]\\ #,##0.00",
  409. 302: "[$CLP-47A]\\ #,##0.00",
  410. 303: "[$CN¥-850]#,##0.00",
  411. 304: "#,##0.00\\ [$DZD-85F]",
  412. 305: "[$FCFA-2C0C]#,##0.00",
  413. 306: "#,##0.00\\ [$Ft-40E]",
  414. 307: "[$G-3C0C]#,##0.00",
  415. 308: "[$Gs.-3C0A]\\ #,##0.00",
  416. 309: "[$GTQ-486]#,##0.00",
  417. 310: "[$HK$-C04]#,##0.00",
  418. 311: "[$HK$-3C09]#,##0.00",
  419. 312: "#,##0.00\\ [$HRK-41A]",
  420. 313: "[$IDR-3809]#,##0.00",
  421. 314: "[$IQD-492]#,##0.00",
  422. 315: "#,##0.00\\ [$ISK-40F]",
  423. 316: "[$K-455]#,##0.00",
  424. 317: "#,##0.00\\ [$K\u010D-405]",
  425. 318: "#,##0.00\\ [$KM-141A]",
  426. 319: "#,##0.00\\ [$KM-101A]",
  427. 320: "#,##0.00\\ [$KM-181A]",
  428. 321: "[$kr-438]\\ #,##0.00",
  429. 322: "[$kr-43B]\\ #,##0.00",
  430. 323: "#,##0.00\\ [$kr-83B]",
  431. 324: "[$kr-414]\\ #,##0.00",
  432. 325: "[$kr-814]\\ #,##0.00",
  433. 326: "#,##0.00\\ [$kr-41D]",
  434. 327: "[$kr.-406]\\ #,##0.00",
  435. 328: "[$kr.-46F]\\ #,##0.00",
  436. 329: "[$Ksh-441]#,##0.00",
  437. 330: "[$L-818]#,##0.00",
  438. 331: "[$L-819]#,##0.00",
  439. 332: "[$L-480A]\\ #,##0.00",
  440. 333: "#,##0.00\\ [$Lek\u00EB-41C]",
  441. 334: "[$MAD-45F]#,##0.00",
  442. 335: "[$MAD-380C]#,##0.00",
  443. 336: "#,##0.00\\ [$MAD-105F]",
  444. 337: "[$MOP$-1404]#,##0.00",
  445. 338: "#,##0.00\\ [$MVR-465]_-",
  446. 339: "#,##0.00[$Nfk-873]",
  447. 340: "[$NGN-466]#,##0.00",
  448. 341: "[$NGN-467]#,##0.00",
  449. 342: "[$NGN-469]#,##0.00",
  450. 343: "[$NGN-471]#,##0.00",
  451. 344: "[$NOK-103B]\\ #,##0.00",
  452. 345: "[$NOK-183B]\\ #,##0.00",
  453. 346: "[$NZ$-481]#,##0.00",
  454. 347: "[$PKR-859]\\ #,##0.00",
  455. 348: "[$PYG-474]#,##0.00",
  456. 349: "[$Q-100A]#,##0.00",
  457. 350: "[$R-436]\\ #,##0.00",
  458. 351: "[$R-1C09]\\ #,##0.00",
  459. 352: "[$R-435]\\ #,##0.00",
  460. 353: "[$R$-416]\\ #,##0.00",
  461. 354: "[$RD$-1C0A]#,##0.00",
  462. 355: "#,##0.00\\ [$RF-487]",
  463. 356: "[$RM-4409]#,##0.00",
  464. 357: "[$RM-43E]#,##0.00",
  465. 358: "#,##0.00\\ [$RON-418]",
  466. 359: "[$Rp-421]#,##0.00",
  467. 360: "[$Rs-420]#,##0.00_-",
  468. 361: "[$Rs.-849]\\ #,##0.00",
  469. 362: "#,##0.00\\ [$RSD-81A]",
  470. 363: "#,##0.00\\ [$RSD-C1A]",
  471. 364: "#,##0.00\\ [$RUB-46D]",
  472. 365: "#,##0.00\\ [$RUB-444]",
  473. 366: "[$S/.-C6B]\\ #,##0.00",
  474. 367: "[$S/.-280A]\\ #,##0.00",
  475. 368: "#,##0.00\\ [$SEK-143B]",
  476. 369: "#,##0.00\\ [$SEK-1C3B]",
  477. 370: "#,##0.00\\ [$so\u02BBm-443]",
  478. 371: "#,##0.00\\ [$so\u02BBm-843]",
  479. 372: "#,##0.00\\ [$SYP-45A]",
  480. 373: "[$THB-41E]#,##0.00",
  481. 374: "#,##0.00[$TMT-442]",
  482. 375: "[$US$-3009]#,##0.00",
  483. 376: "[$ZAR-46C]\\ #,##0.00",
  484. 377: "[$ZAR-430]#,##0.00",
  485. 378: "[$ZAR-431]#,##0.00",
  486. 379: "[$ZAR-432]\\ #,##0.00",
  487. 380: "[$ZAR-433]#,##0.00",
  488. 381: "[$ZAR-434]\\ #,##0.00",
  489. 382: "#,##0.00\\ [$z\u0142-415]",
  490. 383: "#,##0.00\\ [$\u0434\u0435\u043D-42F]",
  491. 384: "#,##0.00\\ [$КМ-201A]",
  492. 385: "#,##0.00\\ [$КМ-1C1A]",
  493. 386: "#,##0.00\\ [$\u043B\u0432.-402]",
  494. 387: "#,##0.00\\ [$р.-423]",
  495. 388: "#,##0.00\\ [$\u0441\u043E\u043C-440]",
  496. 389: "#,##0.00\\ [$\u0441\u043E\u043C-428]",
  497. 390: "[$\u062C.\u0645.-C01]\\ #,##0.00_-",
  498. 391: "[$\u062F.\u0623.-2C01]\\ #,##0.00_-",
  499. 392: "[$\u062F.\u0625.-3801]\\ #,##0.00_-",
  500. 393: "[$\u062F.\u0628.-3C01]\\ #,##0.00_-",
  501. 394: "[$\u062F.\u062A.-1C01]\\ #,##0.00_-",
  502. 395: "[$\u062F.\u062C.-1401]\\ #,##0.00_-",
  503. 396: "[$\u062F.\u0639.-801]\\ #,##0.00_-",
  504. 397: "[$\u062F.\u0643.-3401]\\ #,##0.00_-",
  505. 398: "[$\u062F.\u0644.-1001]#,##0.00_-",
  506. 399: "[$\u062F.\u0645.-1801]\\ #,##0.00_-",
  507. 400: "[$\u0631-846]\\ #,##0.00",
  508. 401: "[$\u0631.\u0633.-401]\\ #,##0.00_-",
  509. 402: "[$\u0631.\u0639.-2001]\\ #,##0.00_-",
  510. 403: "[$\u0631.\u0642.-4001]\\ #,##0.00_-",
  511. 404: "[$\u0631.\u064A.-2401]\\ #,##0.00_-",
  512. 405: "[$\u0631\u06CC\u0627\u0644-429]#,##0.00_-",
  513. 406: "[$\u0644.\u0633.-2801]\\ #,##0.00_-",
  514. 407: "[$\u0644.\u0644.-3001]\\ #,##0.00_-",
  515. 408: "[$\u1265\u122D-45E]#,##0.00",
  516. 409: "[$\u0930\u0942-461]#,##0.00",
  517. 410: "[$\u0DBB\u0DD4.-45B]\\ #,##0.00",
  518. 411: "[$ADP]\\ #,##0.00",
  519. 412: "[$AED]\\ #,##0.00",
  520. 413: "[$AFA]\\ #,##0.00",
  521. 414: "[$AFN]\\ #,##0.00",
  522. 415: "[$ALL]\\ #,##0.00",
  523. 416: "[$AMD]\\ #,##0.00",
  524. 417: "[$ANG]\\ #,##0.00",
  525. 418: "[$AOA]\\ #,##0.00",
  526. 419: "[$ARS]\\ #,##0.00",
  527. 420: "[$ATS]\\ #,##0.00",
  528. 421: "[$AUD]\\ #,##0.00",
  529. 422: "[$AWG]\\ #,##0.00",
  530. 423: "[$AZM]\\ #,##0.00",
  531. 424: "[$AZN]\\ #,##0.00",
  532. 425: "[$BAM]\\ #,##0.00",
  533. 426: "[$BBD]\\ #,##0.00",
  534. 427: "[$BDT]\\ #,##0.00",
  535. 428: "[$BEF]\\ #,##0.00",
  536. 429: "[$BGL]\\ #,##0.00",
  537. 430: "[$BGN]\\ #,##0.00",
  538. 431: "[$BHD]\\ #,##0.00",
  539. 432: "[$BIF]\\ #,##0.00",
  540. 433: "[$BMD]\\ #,##0.00",
  541. 434: "[$BND]\\ #,##0.00",
  542. 435: "[$BOB]\\ #,##0.00",
  543. 436: "[$BOV]\\ #,##0.00",
  544. 437: "[$BRL]\\ #,##0.00",
  545. 438: "[$BSD]\\ #,##0.00",
  546. 439: "[$BTN]\\ #,##0.00",
  547. 440: "[$BWP]\\ #,##0.00",
  548. 441: "[$BYR]\\ #,##0.00",
  549. 442: "[$BZD]\\ #,##0.00",
  550. 443: "[$CAD]\\ #,##0.00",
  551. 444: "[$CDF]\\ #,##0.00",
  552. 445: "[$CHE]\\ #,##0.00",
  553. 446: "[$CHF]\\ #,##0.00",
  554. 447: "[$CHW]\\ #,##0.00",
  555. 448: "[$CLF]\\ #,##0.00",
  556. 449: "[$CLP]\\ #,##0.00",
  557. 450: "[$CNY]\\ #,##0.00",
  558. 451: "[$COP]\\ #,##0.00",
  559. 452: "[$COU]\\ #,##0.00",
  560. 453: "[$CRC]\\ #,##0.00",
  561. 454: "[$CSD]\\ #,##0.00",
  562. 455: "[$CUC]\\ #,##0.00",
  563. 456: "[$CVE]\\ #,##0.00",
  564. 457: "[$CYP]\\ #,##0.00",
  565. 458: "[$CZK]\\ #,##0.00",
  566. 459: "[$DEM]\\ #,##0.00",
  567. 460: "[$DJF]\\ #,##0.00",
  568. 461: "[$DKK]\\ #,##0.00",
  569. 462: "[$DOP]\\ #,##0.00",
  570. 463: "[$DZD]\\ #,##0.00",
  571. 464: "[$ECS]\\ #,##0.00",
  572. 465: "[$ECV]\\ #,##0.00",
  573. 466: "[$EEK]\\ #,##0.00",
  574. 467: "[$EGP]\\ #,##0.00",
  575. 468: "[$ERN]\\ #,##0.00",
  576. 469: "[$ESP]\\ #,##0.00",
  577. 470: "[$ETB]\\ #,##0.00",
  578. 471: "[$EUR]\\ #,##0.00",
  579. 472: "[$FIM]\\ #,##0.00",
  580. 473: "[$FJD]\\ #,##0.00",
  581. 474: "[$FKP]\\ #,##0.00",
  582. 475: "[$FRF]\\ #,##0.00",
  583. 476: "[$GBP]\\ #,##0.00",
  584. 477: "[$GEL]\\ #,##0.00",
  585. 478: "[$GHC]\\ #,##0.00",
  586. 479: "[$GHS]\\ #,##0.00",
  587. 480: "[$GIP]\\ #,##0.00",
  588. 481: "[$GMD]\\ #,##0.00",
  589. 482: "[$GNF]\\ #,##0.00",
  590. 483: "[$GRD]\\ #,##0.00",
  591. 484: "[$GTQ]\\ #,##0.00",
  592. 485: "[$GYD]\\ #,##0.00",
  593. 486: "[$HKD]\\ #,##0.00",
  594. 487: "[$HNL]\\ #,##0.00",
  595. 488: "[$HRK]\\ #,##0.00",
  596. 489: "[$HTG]\\ #,##0.00",
  597. 490: "[$HUF]\\ #,##0.00",
  598. 491: "[$IDR]\\ #,##0.00",
  599. 492: "[$IEP]\\ #,##0.00",
  600. 493: "[$ILS]\\ #,##0.00",
  601. 494: "[$INR]\\ #,##0.00",
  602. 495: "[$IQD]\\ #,##0.00",
  603. 496: "[$IRR]\\ #,##0.00",
  604. 497: "[$ISK]\\ #,##0.00",
  605. 498: "[$ITL]\\ #,##0.00",
  606. 499: "[$JMD]\\ #,##0.00",
  607. 500: "[$JOD]\\ #,##0.00",
  608. 501: "[$JPY]\\ #,##0.00",
  609. 502: "[$KAF]\\ #,##0.00",
  610. 503: "[$KES]\\ #,##0.00",
  611. 504: "[$KGS]\\ #,##0.00",
  612. 505: "[$KHR]\\ #,##0.00",
  613. 506: "[$KMF]\\ #,##0.00",
  614. 507: "[$KPW]\\ #,##0.00",
  615. 508: "[$KRW]\\ #,##0.00",
  616. 509: "[$KWD]\\ #,##0.00",
  617. 510: "[$KYD]\\ #,##0.00",
  618. 511: "[$KZT]\\ #,##0.00",
  619. 512: "[$LAK]\\ #,##0.00",
  620. 513: "[$LBP]\\ #,##0.00",
  621. 514: "[$LKR]\\ #,##0.00",
  622. 515: "[$LRD]\\ #,##0.00",
  623. 516: "[$LSL]\\ #,##0.00",
  624. 517: "[$LTL]\\ #,##0.00",
  625. 518: "[$LUF]\\ #,##0.00",
  626. 519: "[$LVL]\\ #,##0.00",
  627. 520: "[$LYD]\\ #,##0.00",
  628. 521: "[$MAD]\\ #,##0.00",
  629. 522: "[$MDL]\\ #,##0.00",
  630. 523: "[$MGA]\\ #,##0.00",
  631. 524: "[$MGF]\\ #,##0.00",
  632. 525: "[$MKD]\\ #,##0.00",
  633. 526: "[$MMK]\\ #,##0.00",
  634. 527: "[$MNT]\\ #,##0.00",
  635. 528: "[$MOP]\\ #,##0.00",
  636. 529: "[$MRO]\\ #,##0.00",
  637. 530: "[$MTL]\\ #,##0.00",
  638. 531: "[$MUR]\\ #,##0.00",
  639. 532: "[$MVR]\\ #,##0.00",
  640. 533: "[$MWK]\\ #,##0.00",
  641. 534: "[$MXN]\\ #,##0.00",
  642. 535: "[$MXV]\\ #,##0.00",
  643. 536: "[$MYR]\\ #,##0.00",
  644. 537: "[$MZM]\\ #,##0.00",
  645. 538: "[$MZN]\\ #,##0.00",
  646. 539: "[$NAD]\\ #,##0.00",
  647. 540: "[$NGN]\\ #,##0.00",
  648. 541: "[$NIO]\\ #,##0.00",
  649. 542: "[$NLG]\\ #,##0.00",
  650. 543: "[$NOK]\\ #,##0.00",
  651. 544: "[$NPR]\\ #,##0.00",
  652. 545: "[$NTD]\\ #,##0.00",
  653. 546: "[$NZD]\\ #,##0.00",
  654. 547: "[$OMR]\\ #,##0.00",
  655. 548: "[$PAB]\\ #,##0.00",
  656. 549: "[$PEN]\\ #,##0.00",
  657. 550: "[$PGK]\\ #,##0.00",
  658. 551: "[$PHP]\\ #,##0.00",
  659. 552: "[$PKR]\\ #,##0.00",
  660. 553: "[$PLN]\\ #,##0.00",
  661. 554: "[$PTE]\\ #,##0.00",
  662. 555: "[$PYG]\\ #,##0.00",
  663. 556: "[$QAR]\\ #,##0.00",
  664. 557: "[$ROL]\\ #,##0.00",
  665. 558: "[$RON]\\ #,##0.00",
  666. 559: "[$RSD]\\ #,##0.00",
  667. 560: "[$RUB]\\ #,##0.00",
  668. 561: "[$RUR]\\ #,##0.00",
  669. 562: "[$RWF]\\ #,##0.00",
  670. 563: "[$SAR]\\ #,##0.00",
  671. 564: "[$SBD]\\ #,##0.00",
  672. 565: "[$SCR]\\ #,##0.00",
  673. 566: "[$SDD]\\ #,##0.00",
  674. 567: "[$SDG]\\ #,##0.00",
  675. 568: "[$SDP]\\ #,##0.00",
  676. 569: "[$SEK]\\ #,##0.00",
  677. 570: "[$SGD]\\ #,##0.00",
  678. 571: "[$SHP]\\ #,##0.00",
  679. 572: "[$SIT]\\ #,##0.00",
  680. 573: "[$SKK]\\ #,##0.00",
  681. 574: "[$SLL]\\ #,##0.00",
  682. 575: "[$SOS]\\ #,##0.00",
  683. 576: "[$SPL]\\ #,##0.00",
  684. 577: "[$SRD]\\ #,##0.00",
  685. 578: "[$SRG]\\ #,##0.00",
  686. 579: "[$STD]\\ #,##0.00",
  687. 580: "[$SVC]\\ #,##0.00",
  688. 581: "[$SYP]\\ #,##0.00",
  689. 582: "[$SZL]\\ #,##0.00",
  690. 583: "[$THB]\\ #,##0.00",
  691. 584: "[$TJR]\\ #,##0.00",
  692. 585: "[$TJS]\\ #,##0.00",
  693. 586: "[$TMM]\\ #,##0.00",
  694. 587: "[$TMT]\\ #,##0.00",
  695. 588: "[$TND]\\ #,##0.00",
  696. 589: "[$TOP]\\ #,##0.00",
  697. 590: "[$TRL]\\ #,##0.00",
  698. 591: "[$TRY]\\ #,##0.00",
  699. 592: "[$TTD]\\ #,##0.00",
  700. 593: "[$TWD]\\ #,##0.00",
  701. 594: "[$TZS]\\ #,##0.00",
  702. 595: "[$UAH]\\ #,##0.00",
  703. 596: "[$UGX]\\ #,##0.00",
  704. 597: "[$USD]\\ #,##0.00",
  705. 598: "[$USN]\\ #,##0.00",
  706. 599: "[$USS]\\ #,##0.00",
  707. 600: "[$UYI]\\ #,##0.00",
  708. 601: "[$UYU]\\ #,##0.00",
  709. 602: "[$UZS]\\ #,##0.00",
  710. 603: "[$VEB]\\ #,##0.00",
  711. 604: "[$VEF]\\ #,##0.00",
  712. 605: "[$VND]\\ #,##0.00",
  713. 606: "[$VUV]\\ #,##0.00",
  714. 607: "[$WST]\\ #,##0.00",
  715. 608: "[$XAF]\\ #,##0.00",
  716. 609: "[$XAG]\\ #,##0.00",
  717. 610: "[$XAU]\\ #,##0.00",
  718. 611: "[$XB5]\\ #,##0.00",
  719. 612: "[$XBA]\\ #,##0.00",
  720. 613: "[$XBB]\\ #,##0.00",
  721. 614: "[$XBC]\\ #,##0.00",
  722. 615: "[$XBD]\\ #,##0.00",
  723. 616: "[$XCD]\\ #,##0.00",
  724. 617: "[$XDR]\\ #,##0.00",
  725. 618: "[$XFO]\\ #,##0.00",
  726. 619: "[$XFU]\\ #,##0.00",
  727. 620: "[$XOF]\\ #,##0.00",
  728. 621: "[$XPD]\\ #,##0.00",
  729. 622: "[$XPF]\\ #,##0.00",
  730. 623: "[$XPT]\\ #,##0.00",
  731. 624: "[$XTS]\\ #,##0.00",
  732. 625: "[$XXX]\\ #,##0.00",
  733. 626: "[$YER]\\ #,##0.00",
  734. 627: "[$YUM]\\ #,##0.00",
  735. 628: "[$ZAR]\\ #,##0.00",
  736. 629: "[$ZMK]\\ #,##0.00",
  737. 630: "[$ZMW]\\ #,##0.00",
  738. 631: "[$ZWD]\\ #,##0.00",
  739. 632: "[$ZWL]\\ #,##0.00",
  740. 633: "[$ZWN]\\ #,##0.00",
  741. 634: "[$ZWR]\\ #,##0.00",
  742. }
  743. // builtInNumFmtFunc defined the format conversion functions map. Partial format
  744. // code doesn't support currently and will return original string.
  745. var builtInNumFmtFunc = map[int]func(i int, v string) string{
  746. 0: formatToString,
  747. 1: formatToInt,
  748. 2: formatToFloat,
  749. 3: formatToInt,
  750. 4: formatToFloat,
  751. 9: formatToC,
  752. 10: formatToD,
  753. 11: formatToE,
  754. 12: formatToString, // Doesn't support currently
  755. 13: formatToString, // Doesn't support currently
  756. 14: parseTime,
  757. 15: parseTime,
  758. 16: parseTime,
  759. 17: parseTime,
  760. 18: parseTime,
  761. 19: parseTime,
  762. 20: parseTime,
  763. 21: parseTime,
  764. 22: parseTime,
  765. 37: formatToA,
  766. 38: formatToA,
  767. 39: formatToB,
  768. 40: formatToB,
  769. 41: formatToString, // Doesn't support currently
  770. 42: formatToString, // Doesn't support currently
  771. 43: formatToString, // Doesn't support currently
  772. 44: formatToString, // Doesn't support currently
  773. 45: parseTime,
  774. 46: parseTime,
  775. 47: parseTime,
  776. 48: formatToE,
  777. 49: formatToString,
  778. }
  779. // validType defined the list of valid validation types.
  780. var validType = map[string]string{
  781. "cell": "cellIs",
  782. "date": "date", // Doesn't support currently
  783. "time": "time", // Doesn't support currently
  784. "average": "aboveAverage",
  785. "duplicate": "duplicateValues",
  786. "unique": "uniqueValues",
  787. "top": "top10",
  788. "bottom": "top10",
  789. "text": "text", // Doesn't support currently
  790. "time_period": "timePeriod", // Doesn't support currently
  791. "blanks": "containsBlanks", // Doesn't support currently
  792. "no_blanks": "notContainsBlanks", // Doesn't support currently
  793. "errors": "containsErrors", // Doesn't support currently
  794. "no_errors": "notContainsErrors", // Doesn't support currently
  795. "2_color_scale": "2_color_scale",
  796. "3_color_scale": "3_color_scale",
  797. "data_bar": "dataBar",
  798. "formula": "expression",
  799. }
  800. // criteriaType defined the list of valid criteria types.
  801. var criteriaType = map[string]string{
  802. "between": "between",
  803. "not between": "notBetween",
  804. "equal to": "equal",
  805. "=": "equal",
  806. "==": "equal",
  807. "not equal to": "notEqual",
  808. "!=": "notEqual",
  809. "<>": "notEqual",
  810. "greater than": "greaterThan",
  811. ">": "greaterThan",
  812. "less than": "lessThan",
  813. "<": "lessThan",
  814. "greater than or equal to": "greaterThanOrEqual",
  815. ">=": "greaterThanOrEqual",
  816. "less than or equal to": "lessThanOrEqual",
  817. "<=": "lessThanOrEqual",
  818. "containing": "containsText",
  819. "not containing": "notContains",
  820. "begins with": "beginsWith",
  821. "ends with": "endsWith",
  822. "yesterday": "yesterday",
  823. "today": "today",
  824. "last 7 days": "last7Days",
  825. "last week": "lastWeek",
  826. "this week": "thisWeek",
  827. "continue week": "continueWeek",
  828. "last month": "lastMonth",
  829. "this month": "thisMonth",
  830. "continue month": "continueMonth",
  831. }
  832. // formatToString provides a function to return original string by given
  833. // built-in number formats code and cell string.
  834. func formatToString(i int, v string) string {
  835. return v
  836. }
  837. // formatToInt provides a function to convert original string to integer
  838. // format as string type by given built-in number formats code and cell
  839. // string.
  840. func formatToInt(i int, v string) string {
  841. f, err := strconv.ParseFloat(v, 64)
  842. if err != nil {
  843. return v
  844. }
  845. return fmt.Sprintf("%d", int64(f))
  846. }
  847. // formatToFloat provides a function to convert original string to float
  848. // format as string type by given built-in number formats code and cell
  849. // string.
  850. func formatToFloat(i int, v string) string {
  851. f, err := strconv.ParseFloat(v, 64)
  852. if err != nil {
  853. return v
  854. }
  855. return fmt.Sprintf("%.2f", f)
  856. }
  857. // formatToA provides a function to convert original string to special format
  858. // as string type by given built-in number formats code and cell string.
  859. func formatToA(i int, v string) string {
  860. f, err := strconv.ParseFloat(v, 64)
  861. if err != nil {
  862. return v
  863. }
  864. if f < 0 {
  865. t := int(math.Abs(f))
  866. return fmt.Sprintf("(%d)", t)
  867. }
  868. t := int(f)
  869. return fmt.Sprintf("%d", t)
  870. }
  871. // formatToB provides a function to convert original string to special format
  872. // as string type by given built-in number formats code and cell string.
  873. func formatToB(i int, v string) string {
  874. f, err := strconv.ParseFloat(v, 64)
  875. if err != nil {
  876. return v
  877. }
  878. if f < 0 {
  879. return fmt.Sprintf("(%.2f)", f)
  880. }
  881. return fmt.Sprintf("%.2f", f)
  882. }
  883. // formatToC provides a function to convert original string to special format
  884. // as string type by given built-in number formats code and cell string.
  885. func formatToC(i int, v string) string {
  886. f, err := strconv.ParseFloat(v, 64)
  887. if err != nil {
  888. return v
  889. }
  890. f = f * 100
  891. return fmt.Sprintf("%d%%", int(f))
  892. }
  893. // formatToD provides a function to convert original string to special format
  894. // as string type by given built-in number formats code and cell string.
  895. func formatToD(i int, v string) string {
  896. f, err := strconv.ParseFloat(v, 64)
  897. if err != nil {
  898. return v
  899. }
  900. f = f * 100
  901. return fmt.Sprintf("%.2f%%", f)
  902. }
  903. // formatToE provides a function to convert original string to special format
  904. // as string type by given built-in number formats code and cell string.
  905. func formatToE(i int, v string) string {
  906. f, err := strconv.ParseFloat(v, 64)
  907. if err != nil {
  908. return v
  909. }
  910. return fmt.Sprintf("%.e", f)
  911. }
  912. // parseTime provides a function to returns a string parsed using time.Time.
  913. // Replace Excel placeholders with Go time placeholders. For example, replace
  914. // yyyy with 2006. These are in a specific order, due to the fact that m is
  915. // used in month, minute, and am/pm. It would be easier to fix that with
  916. // regular expressions, but if it's possible to keep this simple it would be
  917. // easier to maintain. Full-length month and days (e.g. March, Tuesday) have
  918. // letters in them that would be replaced by other characters below (such as
  919. // the 'h' in March, or the 'd' in Tuesday) below. First we convert them to
  920. // arbitrary characters unused in Excel Date formats, and then at the end,
  921. // turn them to what they should actually be. Based off:
  922. // http://www.ozgrid.com/Excel/CustomFormats.htm
  923. func parseTime(i int, v string) string {
  924. f, err := strconv.ParseFloat(v, 64)
  925. if err != nil {
  926. return v
  927. }
  928. val := timeFromExcelTime(f, false)
  929. format := builtInNumFmt[i]
  930. replacements := []struct{ xltime, gotime string }{
  931. {"yyyy", "2006"},
  932. {"yy", "06"},
  933. {"mmmm", "%%%%"},
  934. {"dddd", "&&&&"},
  935. {"dd", "02"},
  936. {"d", "2"},
  937. {"mmm", "Jan"},
  938. {"mmss", "0405"},
  939. {"ss", "05"},
  940. {"mm:", "04:"},
  941. {":mm", ":04"},
  942. {"mm", "01"},
  943. {"am/pm", "pm"},
  944. {"m/", "1/"},
  945. {"%%%%", "January"},
  946. {"&&&&", "Monday"},
  947. }
  948. // It is the presence of the "am/pm" indicator that determines if this is
  949. // a 12 hour or 24 hours time format, not the number of 'h' characters.
  950. if is12HourTime(format) {
  951. format = strings.Replace(format, "hh", "03", 1)
  952. format = strings.Replace(format, "h", "3", 1)
  953. } else {
  954. format = strings.Replace(format, "hh", "15", 1)
  955. format = strings.Replace(format, "h", "15", 1)
  956. }
  957. for _, repl := range replacements {
  958. format = strings.Replace(format, repl.xltime, repl.gotime, 1)
  959. }
  960. // If the hour is optional, strip it out, along with the possible dangling
  961. // colon that would remain.
  962. if val.Hour() < 1 {
  963. format = strings.Replace(format, "]:", "]", 1)
  964. format = strings.Replace(format, "[03]", "", 1)
  965. format = strings.Replace(format, "[3]", "", 1)
  966. format = strings.Replace(format, "[15]", "", 1)
  967. } else {
  968. format = strings.Replace(format, "[3]", "3", 1)
  969. format = strings.Replace(format, "[15]", "15", 1)
  970. }
  971. return val.Format(format)
  972. }
  973. // is12HourTime checks whether an Excel time format string is a 12 hours form.
  974. func is12HourTime(format string) bool {
  975. return strings.Contains(format, "am/pm") || strings.Contains(format, "AM/PM") || strings.Contains(format, "a/p") || strings.Contains(format, "A/P")
  976. }
  977. // stylesReader provides a function to get the pointer to the structure after
  978. // deserialization of xl/styles.xml.
  979. func (f *File) stylesReader() *xlsxStyleSheet {
  980. if f.Styles == nil {
  981. var styleSheet xlsxStyleSheet
  982. _ = xml.Unmarshal(namespaceStrictToTransitional(f.readXML("xl/styles.xml")), &styleSheet)
  983. f.Styles = &styleSheet
  984. }
  985. return f.Styles
  986. }
  987. // styleSheetWriter provides a function to save xl/styles.xml after serialize
  988. // structure.
  989. func (f *File) styleSheetWriter() {
  990. if f.Styles != nil {
  991. output, _ := xml.Marshal(f.Styles)
  992. f.saveFileList("xl/styles.xml", replaceStyleRelationshipsNameSpaceBytes(output))
  993. }
  994. }
  995. // parseFormatStyleSet provides a function to parse the format settings of the
  996. // cells and conditional formats.
  997. func parseFormatStyleSet(style string) (*formatStyle, error) {
  998. format := formatStyle{
  999. DecimalPlaces: 2,
  1000. }
  1001. err := json.Unmarshal([]byte(style), &format)
  1002. return &format, err
  1003. }
  1004. // NewStyle provides a function to create style for cells by given style
  1005. // format. Note that the color field uses RGB color code.
  1006. //
  1007. // The following shows the border styles sorted by excelize index number:
  1008. //
  1009. // Index | Name | Weight | Style
  1010. // -------+---------------+--------+-------------
  1011. // 0 | None | 0 |
  1012. // 1 | Continuous | 1 | -----------
  1013. // 2 | Continuous | 2 | -----------
  1014. // 3 | Dash | 1 | - - - - - -
  1015. // 4 | Dot | 1 | . . . . . .
  1016. // 5 | Continuous | 3 | -----------
  1017. // 6 | Double | 3 | ===========
  1018. // 7 | Continuous | 0 | -----------
  1019. // 8 | Dash | 2 | - - - - - -
  1020. // 9 | Dash Dot | 1 | - . - . - .
  1021. // 10 | Dash Dot | 2 | - . - . - .
  1022. // 11 | Dash Dot Dot | 1 | - . . - . .
  1023. // 12 | Dash Dot Dot | 2 | - . . - . .
  1024. // 13 | SlantDash Dot | 2 | / - . / - .
  1025. //
  1026. // The following shows the borders in the order shown in the Excel dialog:
  1027. //
  1028. // Index | Style | Index | Style
  1029. // -------+-------------+-------+-------------
  1030. // 0 | None | 12 | - . . - . .
  1031. // 7 | ----------- | 13 | / - . / - .
  1032. // 4 | . . . . . . | 10 | - . - . - .
  1033. // 11 | - . . - . . | 8 | - - - - - -
  1034. // 9 | - . - . - . | 2 | -----------
  1035. // 3 | - - - - - - | 5 | -----------
  1036. // 1 | ----------- | 6 | ===========
  1037. //
  1038. // The following shows the shading styles sorted by excelize index number:
  1039. //
  1040. // Index | Style | Index | Style
  1041. // -------+-----------------+-------+-----------------
  1042. // 0 | Horizontal | 3 | Diagonal down
  1043. // 1 | Vertical | 4 | From corner
  1044. // 2 | Diagonal Up | 5 | From center
  1045. //
  1046. // The following shows the patterns styles sorted by excelize index number:
  1047. //
  1048. // Index | Style | Index | Style
  1049. // -------+-----------------+-------+-----------------
  1050. // 0 | None | 10 | darkTrellis
  1051. // 1 | solid | 11 | lightHorizontal
  1052. // 2 | mediumGray | 12 | lightVertical
  1053. // 3 | darkGray | 13 | lightDown
  1054. // 4 | lightGray | 14 | lightUp
  1055. // 5 | darkHorizontal | 15 | lightGrid
  1056. // 6 | darkVertical | 16 | lightTrellis
  1057. // 7 | darkDown | 17 | gray125
  1058. // 8 | darkUp | 18 | gray0625
  1059. // 9 | darkGrid | |
  1060. //
  1061. // The following the type of horizontal alignment in cells:
  1062. //
  1063. // Style
  1064. // ------------------
  1065. // left
  1066. // center
  1067. // right
  1068. // fill
  1069. // justify
  1070. // centerContinuous
  1071. // distributed
  1072. //
  1073. // The following the type of vertical alignment in cells:
  1074. //
  1075. // Style
  1076. // ------------------
  1077. // top
  1078. // center
  1079. // justify
  1080. // distributed
  1081. //
  1082. // The following the type of font underline style:
  1083. //
  1084. // Style
  1085. // ------------------
  1086. // single
  1087. // double
  1088. //
  1089. // Excel's built-in all languages formats are shown in the following table:
  1090. //
  1091. // Index | Format String
  1092. // -------+----------------------------------------------------
  1093. // 0 | General
  1094. // 1 | 0
  1095. // 2 | 0.00
  1096. // 3 | #,##0
  1097. // 4 | #,##0.00
  1098. // 5 | ($#,##0_);($#,##0)
  1099. // 6 | ($#,##0_);[Red]($#,##0)
  1100. // 7 | ($#,##0.00_);($#,##0.00)
  1101. // 8 | ($#,##0.00_);[Red]($#,##0.00)
  1102. // 9 | 0%
  1103. // 10 | 0.00%
  1104. // 11 | 0.00E+00
  1105. // 12 | # ?/?
  1106. // 13 | # ??/??
  1107. // 14 | m/d/yy
  1108. // 15 | d-mmm-yy
  1109. // 16 | d-mmm
  1110. // 17 | mmm-yy
  1111. // 18 | h:mm AM/PM
  1112. // 19 | h:mm:ss AM/PM
  1113. // 20 | h:mm
  1114. // 21 | h:mm:ss
  1115. // 22 | m/d/yy h:mm
  1116. // ... | ...
  1117. // 37 | (#,##0_);(#,##0)
  1118. // 38 | (#,##0_);[Red](#,##0)
  1119. // 39 | (#,##0.00_);(#,##0.00)
  1120. // 40 | (#,##0.00_);[Red](#,##0.00)
  1121. // 41 | _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
  1122. // 42 | _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
  1123. // 43 | _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
  1124. // 44 | _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
  1125. // 45 | mm:ss
  1126. // 46 | [h]:mm:ss
  1127. // 47 | mm:ss.0
  1128. // 48 | ##0.0E+0
  1129. // 49 | @
  1130. //
  1131. // Number format code in zh-tw language:
  1132. //
  1133. // Index | Symbol
  1134. // -------+-------------------------------------------
  1135. // 27 | [$-404]e/m/d
  1136. // 28 | [$-404]e"年"m"月"d"日"
  1137. // 29 | [$-404]e"年"m"月"d"日"
  1138. // 30 | m/d/yy
  1139. // 31 | yyyy"年"m"月"d"日"
  1140. // 32 | hh"時"mm"分"
  1141. // 33 | hh"時"mm"分"ss"秒"
  1142. // 34 | 上午/下午 hh"時"mm"分"
  1143. // 35 | 上午/下午 hh"時"mm"分"ss"秒"
  1144. // 36 | [$-404]e/m/d
  1145. // 50 | [$-404]e/m/d
  1146. // 51 | [$-404]e"年"m"月"d"日"
  1147. // 52 | 上午/下午 hh"時"mm"分"
  1148. // 53 | 上午/下午 hh"時"mm"分"ss"秒"
  1149. // 54 | [$-404]e"年"m"月"d"日"
  1150. // 55 | 上午/下午 hh"時"mm"分"
  1151. // 56 | 上午/下午 hh"時"mm"分"ss"秒"
  1152. // 57 | [$-404]e/m/d
  1153. // 58 | [$-404]e"年"m"月"d"日"
  1154. //
  1155. // Number format code in zh-cn language:
  1156. //
  1157. // Index | Symbol
  1158. // -------+-------------------------------------------
  1159. // 27 | yyyy"年"m"月"
  1160. // 28 | m"月"d"日"
  1161. // 29 | m"月"d"日"
  1162. // 30 | m-d-yy
  1163. // 31 | yyyy"年"m"月"d"日"
  1164. // 32 | h"时"mm"分"
  1165. // 33 | h"时"mm"分"ss"秒"
  1166. // 34 | 上午/下午 h"时"mm"分"
  1167. // 35 | 上午/下午 h"时"mm"分"ss"秒
  1168. // 36 | yyyy"年"m"月
  1169. // 50 | yyyy"年"m"月
  1170. // 51 | m"月"d"日
  1171. // 52 | yyyy"年"m"月
  1172. // 53 | m"月"d"日
  1173. // 54 | m"月"d"日
  1174. // 55 | 上午/下午 h"时"mm"分
  1175. // 56 | 上午/下午 h"时"mm"分"ss"秒
  1176. // 57 | yyyy"年"m"月
  1177. // 58 | m"月"d"日"
  1178. //
  1179. // Number format code with unicode values provided for language glyphs where
  1180. // they occur in zh-tw language:
  1181. //
  1182. // Index | Symbol
  1183. // -------+-------------------------------------------
  1184. // 27 | [$-404]e/m/
  1185. // 28 | [$-404]e"5E74"m"6708"d"65E5
  1186. // 29 | [$-404]e"5E74"m"6708"d"65E5
  1187. // 30 | m/d/y
  1188. // 31 | yyyy"5E74"m"6708"d"65E5
  1189. // 32 | hh"6642"mm"5206
  1190. // 33 | hh"6642"mm"5206"ss"79D2
  1191. // 34 | 4E0A5348/4E0B5348hh"6642"mm"5206
  1192. // 35 | 4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2
  1193. // 36 | [$-404]e/m/
  1194. // 50 | [$-404]e/m/
  1195. // 51 | [$-404]e"5E74"m"6708"d"65E5
  1196. // 52 | 4E0A5348/4E0B5348hh"6642"mm"5206
  1197. // 53 | 4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2
  1198. // 54 | [$-404]e"5E74"m"6708"d"65E5
  1199. // 55 | 4E0A5348/4E0B5348hh"6642"mm"5206
  1200. // 56 | 4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2
  1201. // 57 | [$-404]e/m/
  1202. // 58 | [$-404]e"5E74"m"6708"d"65E5"
  1203. //
  1204. // Number format code with unicode values provided for language glyphs where
  1205. // they occur in zh-cn language:
  1206. //
  1207. // Index | Symbol
  1208. // -------+-------------------------------------------
  1209. // 27 | yyyy"5E74"m"6708
  1210. // 28 | m"6708"d"65E5
  1211. // 29 | m"6708"d"65E5
  1212. // 30 | m-d-y
  1213. // 31 | yyyy"5E74"m"6708"d"65E5
  1214. // 32 | h"65F6"mm"5206
  1215. // 33 | h"65F6"mm"5206"ss"79D2
  1216. // 34 | 4E0A5348/4E0B5348h"65F6"mm"5206
  1217. // 35 | 4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2
  1218. // 36 | yyyy"5E74"m"6708
  1219. // 50 | yyyy"5E74"m"6708
  1220. // 51 | m"6708"d"65E5
  1221. // 52 | yyyy"5E74"m"6708
  1222. // 53 | m"6708"d"65E5
  1223. // 54 | m"6708"d"65E5
  1224. // 55 | 4E0A5348/4E0B5348h"65F6"mm"5206
  1225. // 56 | 4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2
  1226. // 57 | yyyy"5E74"m"6708
  1227. // 58 | m"6708"d"65E5"
  1228. //
  1229. // Number format code in ja-jp language:
  1230. //
  1231. // Index | Symbol
  1232. // -------+-------------------------------------------
  1233. // 27 | [$-411]ge.m.d
  1234. // 28 | [$-411]ggge"年"m"月"d"日
  1235. // 29 | [$-411]ggge"年"m"月"d"日
  1236. // 30 | m/d/y
  1237. // 31 | yyyy"年"m"月"d"日
  1238. // 32 | h"時"mm"分
  1239. // 33 | h"時"mm"分"ss"秒
  1240. // 34 | yyyy"年"m"月
  1241. // 35 | m"月"d"日
  1242. // 36 | [$-411]ge.m.d
  1243. // 50 | [$-411]ge.m.d
  1244. // 51 | [$-411]ggge"年"m"月"d"日
  1245. // 52 | yyyy"年"m"月
  1246. // 53 | m"月"d"日
  1247. // 54 | [$-411]ggge"年"m"月"d"日
  1248. // 55 | yyyy"年"m"月
  1249. // 56 | m"月"d"日
  1250. // 57 | [$-411]ge.m.d
  1251. // 58 | [$-411]ggge"年"m"月"d"日"
  1252. //
  1253. // Number format code in ko-kr language:
  1254. //
  1255. // Index | Symbol
  1256. // -------+-------------------------------------------
  1257. // 27 | yyyy"年" mm"月" dd"日
  1258. // 28 | mm-d
  1259. // 29 | mm-d
  1260. // 30 | mm-dd-y
  1261. // 31 | yyyy"년" mm"월" dd"일
  1262. // 32 | h"시" mm"분
  1263. // 33 | h"시" mm"분" ss"초
  1264. // 34 | yyyy-mm-d
  1265. // 35 | yyyy-mm-d
  1266. // 36 | yyyy"年" mm"月" dd"日
  1267. // 50 | yyyy"年" mm"月" dd"日
  1268. // 51 | mm-d
  1269. // 52 | yyyy-mm-d
  1270. // 53 | yyyy-mm-d
  1271. // 54 | mm-d
  1272. // 55 | yyyy-mm-d
  1273. // 56 | yyyy-mm-d
  1274. // 57 | yyyy"年" mm"月" dd"日
  1275. // 58 | mm-dd
  1276. //
  1277. // Number format code with unicode values provided for language glyphs where
  1278. // they occur in ja-jp language:
  1279. //
  1280. // Index | Symbol
  1281. // -------+-------------------------------------------
  1282. // 27 | [$-411]ge.m.d
  1283. // 28 | [$-411]ggge"5E74"m"6708"d"65E5
  1284. // 29 | [$-411]ggge"5E74"m"6708"d"65E5
  1285. // 30 | m/d/y
  1286. // 31 | yyyy"5E74"m"6708"d"65E5
  1287. // 32 | h"6642"mm"5206
  1288. // 33 | h"6642"mm"5206"ss"79D2
  1289. // 34 | yyyy"5E74"m"6708
  1290. // 35 | m"6708"d"65E5
  1291. // 36 | [$-411]ge.m.d
  1292. // 50 | [$-411]ge.m.d
  1293. // 51 | [$-411]ggge"5E74"m"6708"d"65E5
  1294. // 52 | yyyy"5E74"m"6708
  1295. // 53 | m"6708"d"65E5
  1296. // 54 | [$-411]ggge"5E74"m"6708"d"65E5
  1297. // 55 | yyyy"5E74"m"6708
  1298. // 56 | m"6708"d"65E5
  1299. // 57 | [$-411]ge.m.d
  1300. // 58 | [$-411]ggge"5E74"m"6708"d"65E5"
  1301. //
  1302. // Number format code with unicode values provided for language glyphs where
  1303. // they occur in ko-kr language:
  1304. //
  1305. // Index | Symbol
  1306. // -------+-------------------------------------------
  1307. // 27 | yyyy"5E74" mm"6708" dd"65E5
  1308. // 28 | mm-d
  1309. // 29 | mm-d
  1310. // 30 | mm-dd-y
  1311. // 31 | yyyy"B144" mm"C6D4" dd"C77C
  1312. // 32 | h"C2DC" mm"BD84
  1313. // 33 | h"C2DC" mm"BD84" ss"CD08
  1314. // 34 | yyyy-mm-d
  1315. // 35 | yyyy-mm-d
  1316. // 36 | yyyy"5E74" mm"6708" dd"65E5
  1317. // 50 | yyyy"5E74" mm"6708" dd"65E5
  1318. // 51 | mm-d
  1319. // 52 | yyyy-mm-d
  1320. // 53 | yyyy-mm-d
  1321. // 54 | mm-d
  1322. // 55 | yyyy-mm-d
  1323. // 56 | yyyy-mm-d
  1324. // 57 | yyyy"5E74" mm"6708" dd"65E5
  1325. // 58 | mm-dd
  1326. //
  1327. // Number format code in th-th language:
  1328. //
  1329. // Index | Symbol
  1330. // -------+-------------------------------------------
  1331. // 59 | t
  1332. // 60 | t0.0
  1333. // 61 | t#,##
  1334. // 62 | t#,##0.0
  1335. // 67 | t0
  1336. // 68 | t0.00
  1337. // 69 | t# ?/
  1338. // 70 | t# ??/?
  1339. // 71 | ว/ด/ปปป
  1340. // 72 | ว-ดดด-ป
  1341. // 73 | ว-ดด
  1342. // 74 | ดดด-ป
  1343. // 75 | ช:น
  1344. // 76 | ช:นน:ท
  1345. // 77 | ว/ด/ปปปป ช:น
  1346. // 78 | นน:ท
  1347. // 79 | [ช]:นน:ท
  1348. // 80 | นน:ทท.
  1349. // 81 | d/m/bb
  1350. //
  1351. // Number format code with unicode values provided for language glyphs where
  1352. // they occur in th-th language:
  1353. //
  1354. // Index | Symbol
  1355. // -------+-------------------------------------------
  1356. // 59 | t
  1357. // 60 | t0.0
  1358. // 61 | t#,##
  1359. // 62 | t#,##0.0
  1360. // 67 | t0
  1361. // 68 | t0.00
  1362. // 69 | t# ?/
  1363. // 70 | t# ??/?
  1364. // 71 | 0E27/0E14/0E1B0E1B0E1B0E1
  1365. // 72 | 0E27-0E140E140E14-0E1B0E1
  1366. // 73 | 0E27-0E140E140E1
  1367. // 74 | 0E140E140E14-0E1B0E1
  1368. // 75 | 0E0A:0E190E1
  1369. // 76 | 0E0A:0E190E19:0E170E1
  1370. // 77 | 0E27/0E14/0E1B0E1B0E1B0E1B 0E0A:0E190E1
  1371. // 78 | 0E190E19:0E170E1
  1372. // 79 | [0E0A]:0E190E19:0E170E1
  1373. // 80 | 0E190E19:0E170E17.
  1374. // 81 | d/m/bb
  1375. //
  1376. // Excelize built-in currency formats are shown in the following table, only
  1377. // support these types in the following table (Index number is used only for
  1378. // markup and is not used inside an Excel file and you can't get formatted value
  1379. // by the function GetCellValue) currently:
  1380. //
  1381. // Index | Symbol
  1382. // -------+---------------------------------------------------------------
  1383. // 164 | CN¥
  1384. // 165 | $ English (China)
  1385. // 166 | $ Cherokee (United States)
  1386. // 167 | $ Chinese (Singapore)
  1387. // 168 | $ Chinese (Taiwan)
  1388. // 169 | $ English (Australia)
  1389. // 170 | $ English (Belize)
  1390. // 171 | $ English (Canada)
  1391. // 172 | $ English (Jamaica)
  1392. // 173 | $ English (New Zealand)
  1393. // 174 | $ English (Singapore)
  1394. // 175 | $ English (Trinidad & Tobago)
  1395. // 176 | $ English (U.S. Virgin Islands)
  1396. // 177 | $ English (United States)
  1397. // 178 | $ French (Canada)
  1398. // 179 | $ Hawaiian (United States)
  1399. // 180 | $ Malay (Brunei)
  1400. // 181 | $ Quechua (Ecuador)
  1401. // 182 | $ Spanish (Chile)
  1402. // 183 | $ Spanish (Colombia)
  1403. // 184 | $ Spanish (Ecuador)
  1404. // 185 | $ Spanish (El Salvador)
  1405. // 186 | $ Spanish (Mexico)
  1406. // 187 | $ Spanish (Puerto Rico)
  1407. // 188 | $ Spanish (United States)
  1408. // 189 | $ Spanish (Uruguay)
  1409. // 190 | £ English (United Kingdom)
  1410. // 191 | £ Scottish Gaelic (United Kingdom)
  1411. // 192 | £ Welsh (United Kindom)
  1412. // 193 | ¥ Chinese (China)
  1413. // 194 | ¥ Japanese (Japan)
  1414. // 195 | ¥ Sichuan Yi (China)
  1415. // 196 | ¥ Tibetan (China)
  1416. // 197 | ¥ Uyghur (China)
  1417. // 198 | ֏ Armenian (Armenia)
  1418. // 199 | ؋ Pashto (Afghanistan)
  1419. // 200 | ؋ Persian (Afghanistan)
  1420. // 201 | ৳ Bengali (Bangladesh)
  1421. // 202 | ៛ Khmer (Cambodia)
  1422. // 203 | ₡ Spanish (Costa Rica)
  1423. // 204 | ₦ Hausa (Nigeria)
  1424. // 205 | ₦ Igbo (Nigeria)
  1425. // 206 | ₦ Yoruba (Nigeria)
  1426. // 207 | ₩ Korean (South Korea)
  1427. // 208 | ₪ Hebrew (Israel)
  1428. // 209 | ₫ Vietnamese (Vietnam)
  1429. // 210 | € Basque (Spain)
  1430. // 211 | € Breton (France)
  1431. // 212 | € Catalan (Spain)
  1432. // 213 | € Corsican (France)
  1433. // 214 | € Dutch (Belgium)
  1434. // 215 | € Dutch (Netherlands)
  1435. // 216 | € English (Ireland)
  1436. // 217 | € Estonian (Estonia)
  1437. // 218 | € Euro (€ 123)
  1438. // 219 | € Euro (123 €)
  1439. // 220 | € Finnish (Finland)
  1440. // 221 | € French (Belgium)
  1441. // 222 | € French (France)
  1442. // 223 | € French (Luxembourg)
  1443. // 224 | € French (Monaco)
  1444. // 225 | € French (Réunion)
  1445. // 226 | € Galician (Spain)
  1446. // 227 | € German (Austria)
  1447. // 228 | € German (Luxembourg)
  1448. // 229 | € Greek (Greece)
  1449. // 230 | € Inari Sami (Finland)
  1450. // 231 | € Irish (Ireland)
  1451. // 232 | € Italian (Italy)
  1452. // 233 | € Latin (Italy)
  1453. // 234 | € Latin, Serbian (Montenegro)
  1454. // 235 | € Larvian (Latvia)
  1455. // 236 | € Lithuanian (Lithuania)
  1456. // 237 | € Lower Sorbian (Germany)
  1457. // 238 | € Luxembourgish (Luxembourg)
  1458. // 239 | € Maltese (Malta)
  1459. // 240 | € Northern Sami (Finland)
  1460. // 241 | € Occitan (France)
  1461. // 242 | € Portuguese (Portugal)
  1462. // 243 | € Serbian (Montenegro)
  1463. // 244 | € Skolt Sami (Finland)
  1464. // 245 | € Slovak (Slovakia)
  1465. // 246 | € Slovenian (Slovenia)
  1466. // 247 | € Spanish (Spain)
  1467. // 248 | € Swedish (Finland)
  1468. // 249 | € Swiss German (France)
  1469. // 250 | € Upper Sorbian (Germany)
  1470. // 251 | € Western Frisian (Netherlands)
  1471. // 252 | ₭ Lao (Laos)
  1472. // 253 | ₮ Mongolian (Mongolia)
  1473. // 254 | ₮ Mongolian, Mongolian (Mongolia)
  1474. // 255 | ₱ English (Philippines)
  1475. // 256 | ₱ Filipino (Philippines)
  1476. // 257 | ₴ Ukrainian (Ukraine)
  1477. // 258 | ₸ Kazakh (Kazakhstan)
  1478. // 259 | ₹ Arabic, Kashmiri (India)
  1479. // 260 | ₹ English (India)
  1480. // 261 | ₹ Gujarati (India)
  1481. // 262 | ₹ Hindi (India)
  1482. // 263 | ₹ Kannada (India)
  1483. // 264 | ₹ Kashmiri (India)
  1484. // 265 | ₹ Konkani (India)
  1485. // 266 | ₹ Manipuri (India)
  1486. // 267 | ₹ Marathi (India)
  1487. // 268 | ₹ Nepali (India)
  1488. // 269 | ₹ Oriya (India)
  1489. // 270 | ₹ Punjabi (India)
  1490. // 271 | ₹ Sanskrit (India)
  1491. // 272 | ₹ Sindhi (India)
  1492. // 273 | ₹ Tamil (India)
  1493. // 274 | ₹ Urdu (India)
  1494. // 275 | ₺ Turkish (Turkey)
  1495. // 276 | ₼ Azerbaijani (Azerbaijan)
  1496. // 277 | ₼ Cyrillic, Azerbaijani (Azerbaijan)
  1497. // 278 | ₽ Russian (Russia)
  1498. // 279 | ₽ Sakha (Russia)
  1499. // 280 | ₾ Georgian (Georgia)
  1500. // 281 | B/. Spanish (Panama)
  1501. // 282 | Br Oromo (Ethiopia)
  1502. // 283 | Br Somali (Ethiopia)
  1503. // 284 | Br Tigrinya (Ethiopia)
  1504. // 285 | Bs Quechua (Bolivia)
  1505. // 286 | Bs Spanish (Bolivia)
  1506. // 287 | BS. Spanish (Venezuela)
  1507. // 288 | BWP Tswana (Botswana)
  1508. // 289 | C$ Spanish (Nicaragua)
  1509. // 290 | CA$ Latin, Inuktitut (Canada)
  1510. // 291 | CA$ Mohawk (Canada)
  1511. // 292 | CA$ Unified Canadian Aboriginal Syllabics, Inuktitut (Canada)
  1512. // 293 | CFA French (Mali)
  1513. // 294 | CFA French (Senegal)
  1514. // 295 | CFA Fulah (Senegal)
  1515. // 296 | CFA Wolof (Senegal)
  1516. // 297 | CHF French (Switzerland)
  1517. // 298 | CHF German (Liechtenstein)
  1518. // 299 | CHF German (Switzerland)
  1519. // 300 | CHF Italian (Switzerland)
  1520. // 301 | CHF Romansh (Switzerland)
  1521. // 302 | CLP Mapuche (Chile)
  1522. // 303 | CN¥ Mongolian, Mongolian (China)
  1523. // 304 | DZD Central Atlas Tamazight (Algeria)
  1524. // 305 | FCFA French (Cameroon)
  1525. // 306 | Ft Hungarian (Hungary)
  1526. // 307 | G French (Haiti)
  1527. // 308 | Gs. Spanish (Paraguay)
  1528. // 309 | GTQ K'iche' (Guatemala)
  1529. // 310 | HK$ Chinese (Hong Kong (China))
  1530. // 311 | HK$ English (Hong Kong (China))
  1531. // 312 | HRK Croatian (Croatia)
  1532. // 313 | IDR English (Indonesia)
  1533. // 314 | IQD Arbic, Central Kurdish (Iraq)
  1534. // 315 | ISK Icelandic (Iceland)
  1535. // 316 | K Burmese (Myanmar (Burma))
  1536. // 317 | Kč Czech (Czech Republic)
  1537. // 318 | KM Bosnian (Bosnia & Herzegovina)
  1538. // 319 | KM Croatian (Bosnia & Herzegovina)
  1539. // 320 | KM Latin, Serbian (Bosnia & Herzegovina)
  1540. // 321 | kr Faroese (Faroe Islands)
  1541. // 322 | kr Northern Sami (Norway)
  1542. // 323 | kr Northern Sami (Sweden)
  1543. // 324 | kr Norwegian Bokmål (Norway)
  1544. // 325 | kr Norwegian Nynorsk (Norway)
  1545. // 326 | kr Swedish (Sweden)
  1546. // 327 | kr. Danish (Denmark)
  1547. // 328 | kr. Kalaallisut (Greenland)
  1548. // 329 | Ksh Swahili (kenya)
  1549. // 330 | L Romanian (Moldova)
  1550. // 331 | L Russian (Moldova)
  1551. // 332 | L Spanish (Honduras)
  1552. // 333 | Lekë Albanian (Albania)
  1553. // 334 | MAD Arabic, Central Atlas Tamazight (Morocco)
  1554. // 335 | MAD French (Morocco)
  1555. // 336 | MAD Tifinagh, Central Atlas Tamazight (Morocco)
  1556. // 337 | MOP$ Chinese (Macau (China))
  1557. // 338 | MVR Divehi (Maldives)
  1558. // 339 | Nfk Tigrinya (Eritrea)
  1559. // 340 | NGN Bini (Nigeria)
  1560. // 341 | NGN Fulah (Nigeria)
  1561. // 342 | NGN Ibibio (Nigeria)
  1562. // 343 | NGN Kanuri (Nigeria)
  1563. // 344 | NOK Lule Sami (Norway)
  1564. // 345 | NOK Southern Sami (Norway)
  1565. // 346 | NZ$ Maori (New Zealand)
  1566. // 347 | PKR Sindhi (Pakistan)
  1567. // 348 | PYG Guarani (Paraguay)
  1568. // 349 | Q Spanish (Guatemala)
  1569. // 350 | R Afrikaans (South Africa)
  1570. // 351 | R English (South Africa)
  1571. // 352 | R Zulu (South Africa)
  1572. // 353 | R$ Portuguese (Brazil)
  1573. // 354 | RD$ Spanish (Dominican Republic)
  1574. // 355 | RF Kinyarwanda (Rwanda)
  1575. // 356 | RM English (Malaysia)
  1576. // 357 | RM Malay (Malaysia)
  1577. // 358 | RON Romanian (Romania)
  1578. // 359 | Rp Indonesoan (Indonesia)
  1579. // 360 | Rs Urdu (Pakistan)
  1580. // 361 | Rs. Tamil (Sri Lanka)
  1581. // 362 | RSD Latin, Serbian (Serbia)
  1582. // 363 | RSD Serbian (Serbia)
  1583. // 364 | RUB Bashkir (Russia)
  1584. // 365 | RUB Tatar (Russia)
  1585. // 366 | S/. Quechua (Peru)
  1586. // 367 | S/. Spanish (Peru)
  1587. // 368 | SEK Lule Sami (Sweden)
  1588. // 369 | SEK Southern Sami (Sweden)
  1589. // 370 | soʻm Latin, Uzbek (Uzbekistan)
  1590. // 371 | soʻm Uzbek (Uzbekistan)
  1591. // 372 | SYP Syriac (Syria)
  1592. // 373 | THB Thai (Thailand)
  1593. // 374 | TMT Turkmen (Turkmenistan)
  1594. // 375 | US$ English (Zimbabwe)
  1595. // 376 | ZAR Northern Sotho (South Africa)
  1596. // 377 | ZAR Southern Sotho (South Africa)
  1597. // 378 | ZAR Tsonga (South Africa)
  1598. // 379 | ZAR Tswana (south Africa)
  1599. // 380 | ZAR Venda (South Africa)
  1600. // 381 | ZAR Xhosa (South Africa)
  1601. // 382 | zł Polish (Poland)
  1602. // 383 | ден Macedonian (Macedonia)
  1603. // 384 | KM Cyrillic, Bosnian (Bosnia & Herzegovina)
  1604. // 385 | KM Serbian (Bosnia & Herzegovina)
  1605. // 386 | лв. Bulgarian (Bulgaria)
  1606. // 387 | p. Belarusian (Belarus)
  1607. // 388 | сом Kyrgyz (Kyrgyzstan)
  1608. // 389 | сом Tajik (Tajikistan)
  1609. // 390 | ج.م. Arabic (Egypt)
  1610. // 391 | د.أ. Arabic (Jordan)
  1611. // 392 | د.أ. Arabic (United Arab Emirates)
  1612. // 393 | د.ب. Arabic (Bahrain)
  1613. // 394 | د.ت. Arabic (Tunisia)
  1614. // 395 | د.ج. Arabic (Algeria)
  1615. // 396 | د.ع. Arabic (Iraq)
  1616. // 397 | د.ك. Arabic (Kuwait)
  1617. // 398 | د.ل. Arabic (Libya)
  1618. // 399 | د.م. Arabic (Morocco)
  1619. // 400 | ر Punjabi (Pakistan)
  1620. // 401 | ر.س. Arabic (Saudi Arabia)
  1621. // 402 | ر.ع. Arabic (Oman)
  1622. // 403 | ر.ق. Arabic (Qatar)
  1623. // 404 | ر.ي. Arabic (Yemen)
  1624. // 405 | ریال Persian (Iran)
  1625. // 406 | ل.س. Arabic (Syria)
  1626. // 407 | ل.ل. Arabic (Lebanon)
  1627. // 408 | ብር Amharic (Ethiopia)
  1628. // 409 | रू Nepaol (Nepal)
  1629. // 410 | රු. Sinhala (Sri Lanka)
  1630. // 411 | ADP
  1631. // 412 | AED
  1632. // 413 | AFA
  1633. // 414 | AFN
  1634. // 415 | ALL
  1635. // 416 | AMD
  1636. // 417 | ANG
  1637. // 418 | AOA
  1638. // 419 | ARS
  1639. // 420 | ATS
  1640. // 421 | AUD
  1641. // 422 | AWG
  1642. // 423 | AZM
  1643. // 424 | AZN
  1644. // 425 | BAM
  1645. // 426 | BBD
  1646. // 427 | BDT
  1647. // 428 | BEF
  1648. // 429 | BGL
  1649. // 430 | BGN
  1650. // 431 | BHD
  1651. // 432 | BIF
  1652. // 433 | BMD
  1653. // 434 | BND
  1654. // 435 | BOB
  1655. // 436 | BOV
  1656. // 437 | BRL
  1657. // 438 | BSD
  1658. // 439 | BTN
  1659. // 440 | BWP
  1660. // 441 | BYR
  1661. // 442 | BZD
  1662. // 443 | CAD
  1663. // 444 | CDF
  1664. // 445 | CHE
  1665. // 446 | CHF
  1666. // 447 | CHW
  1667. // 448 | CLF
  1668. // 449 | CLP
  1669. // 450 | CNY
  1670. // 451 | COP
  1671. // 452 | COU
  1672. // 453 | CRC
  1673. // 454 | CSD
  1674. // 455 | CUC
  1675. // 456 | CVE
  1676. // 457 | CYP
  1677. // 458 | CZK
  1678. // 459 | DEM
  1679. // 460 | DJF
  1680. // 461 | DKK
  1681. // 462 | DOP
  1682. // 463 | DZD
  1683. // 464 | ECS
  1684. // 465 | ECV
  1685. // 466 | EEK
  1686. // 467 | EGP
  1687. // 468 | ERN
  1688. // 469 | ESP
  1689. // 470 | ETB
  1690. // 471 | EUR
  1691. // 472 | FIM
  1692. // 473 | FJD
  1693. // 474 | FKP
  1694. // 475 | FRF
  1695. // 476 | GBP
  1696. // 477 | GEL
  1697. // 478 | GHC
  1698. // 479 | GHS
  1699. // 480 | GIP
  1700. // 481 | GMD
  1701. // 482 | GNF
  1702. // 483 | GRD
  1703. // 484 | GTQ
  1704. // 485 | GYD
  1705. // 486 | HKD
  1706. // 487 | HNL
  1707. // 488 | HRK
  1708. // 489 | HTG
  1709. // 490 | HUF
  1710. // 491 | IDR
  1711. // 492 | IEP
  1712. // 493 | ILS
  1713. // 494 | INR
  1714. // 495 | IQD
  1715. // 496 | IRR
  1716. // 497 | ISK
  1717. // 498 | ITL
  1718. // 499 | JMD
  1719. // 500 | JOD
  1720. // 501 | JPY
  1721. // 502 | KAF
  1722. // 503 | KES
  1723. // 504 | KGS
  1724. // 505 | KHR
  1725. // 506 | KMF
  1726. // 507 | KPW
  1727. // 508 | KRW
  1728. // 509 | KWD
  1729. // 510 | KYD
  1730. // 511 | KZT
  1731. // 512 | LAK
  1732. // 513 | LBP
  1733. // 514 | LKR
  1734. // 515 | LRD
  1735. // 516 | LSL
  1736. // 517 | LTL
  1737. // 518 | LUF
  1738. // 519 | LVL
  1739. // 520 | LYD
  1740. // 521 | MAD
  1741. // 522 | MDL
  1742. // 523 | MGA
  1743. // 524 | MGF
  1744. // 525 | MKD
  1745. // 526 | MMK
  1746. // 527 | MNT
  1747. // 528 | MOP
  1748. // 529 | MRO
  1749. // 530 | MTL
  1750. // 531 | MUR
  1751. // 532 | MVR
  1752. // 533 | MWK
  1753. // 534 | MXN
  1754. // 535 | MXV
  1755. // 536 | MYR
  1756. // 537 | MZM
  1757. // 538 | MZN
  1758. // 539 | NAD
  1759. // 540 | NGN
  1760. // 541 | NIO
  1761. // 542 | NLG
  1762. // 543 | NOK
  1763. // 544 | NPR
  1764. // 545 | NTD
  1765. // 546 | NZD
  1766. // 547 | OMR
  1767. // 548 | PAB
  1768. // 549 | PEN
  1769. // 550 | PGK
  1770. // 551 | PHP
  1771. // 552 | PKR
  1772. // 553 | PLN
  1773. // 554 | PTE
  1774. // 555 | PYG
  1775. // 556 | QAR
  1776. // 557 | ROL
  1777. // 558 | RON
  1778. // 559 | RSD
  1779. // 560 | RUB
  1780. // 561 | RUR
  1781. // 562 | RWF
  1782. // 563 | SAR
  1783. // 564 | SBD
  1784. // 565 | SCR
  1785. // 566 | SDD
  1786. // 567 | SDG
  1787. // 568 | SDP
  1788. // 569 | SEK
  1789. // 570 | SGD
  1790. // 571 | SHP
  1791. // 572 | SIT
  1792. // 573 | SKK
  1793. // 574 | SLL
  1794. // 575 | SOS
  1795. // 576 | SPL
  1796. // 577 | SRD
  1797. // 578 | SRG
  1798. // 579 | STD
  1799. // 580 | SVC
  1800. // 581 | SYP
  1801. // 582 | SZL
  1802. // 583 | THB
  1803. // 584 | TJR
  1804. // 585 | TJS
  1805. // 586 | TMM
  1806. // 587 | TMT
  1807. // 588 | TND
  1808. // 589 | TOP
  1809. // 590 | TRL
  1810. // 591 | TRY
  1811. // 592 | TTD
  1812. // 593 | TWD
  1813. // 594 | TZS
  1814. // 595 | UAH
  1815. // 596 | UGX
  1816. // 597 | USD
  1817. // 598 | USN
  1818. // 599 | USS
  1819. // 600 | UYI
  1820. // 601 | UYU
  1821. // 602 | UZS
  1822. // 603 | VEB
  1823. // 604 | VEF
  1824. // 605 | VND
  1825. // 606 | VUV
  1826. // 607 | WST
  1827. // 608 | XAF
  1828. // 609 | XAG
  1829. // 610 | XAU
  1830. // 611 | XB5
  1831. // 612 | XBA
  1832. // 613 | XBB
  1833. // 614 | XBC
  1834. // 615 | XBD
  1835. // 616 | XCD
  1836. // 617 | XDR
  1837. // 618 | XFO
  1838. // 619 | XFU
  1839. // 620 | XOF
  1840. // 621 | XPD
  1841. // 622 | XPF
  1842. // 623 | XPT
  1843. // 624 | XTS
  1844. // 625 | XXX
  1845. // 626 | YER
  1846. // 627 | YUM
  1847. // 628 | ZAR
  1848. // 629 | ZMK
  1849. // 630 | ZMW
  1850. // 631 | ZWD
  1851. // 632 | ZWL
  1852. // 633 | ZWN
  1853. // 634 | ZWR
  1854. //
  1855. // Excelize support set custom number format for cell. For example, set number
  1856. // as date type in Uruguay (Spanish) format for Sheet1!A6:
  1857. //
  1858. // f := excelize.NewFile()
  1859. // f.SetCellValue("Sheet1", "A6", 42920.5)
  1860. // style, err := f.NewStyle(`{"custom_number_format": "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"}`)
  1861. // err = f.SetCellStyle("Sheet1", "A6", "A6", style)
  1862. //
  1863. // Cell Sheet1!A6 in the Excel Application: martes, 04 de Julio de 2017
  1864. //
  1865. func (f *File) NewStyle(style string) (int, error) {
  1866. var cellXfsID, fontID, borderID, fillID int
  1867. s := f.stylesReader()
  1868. fs, err := parseFormatStyleSet(style)
  1869. if err != nil {
  1870. return cellXfsID, err
  1871. }
  1872. numFmtID := setNumFmt(s, fs)
  1873. if fs.Font != nil {
  1874. s.Fonts.Count++
  1875. s.Fonts.Font = append(s.Fonts.Font, f.setFont(fs))
  1876. fontID = s.Fonts.Count - 1
  1877. }
  1878. s.Borders.Count++
  1879. s.Borders.Border = append(s.Borders.Border, setBorders(fs))
  1880. borderID = s.Borders.Count - 1
  1881. if fill := setFills(fs, true); fill != nil {
  1882. s.Fills.Count++
  1883. s.Fills.Fill = append(s.Fills.Fill, fill)
  1884. fillID = s.Fills.Count - 1
  1885. }
  1886. applyAlignment, alignment := fs.Alignment != nil, setAlignment(fs)
  1887. applyProtection, protection := fs.Protection != nil, setProtection(fs)
  1888. cellXfsID = setCellXfs(s, fontID, numFmtID, fillID, borderID, applyAlignment, applyProtection, alignment, protection)
  1889. return cellXfsID, nil
  1890. }
  1891. // NewConditionalStyle provides a function to create style for conditional
  1892. // format by given style format. The parameters are the same as function
  1893. // NewStyle(). Note that the color field uses RGB color code and only support
  1894. // to set font, fills, alignment and borders currently.
  1895. func (f *File) NewConditionalStyle(style string) (int, error) {
  1896. s := f.stylesReader()
  1897. fs, err := parseFormatStyleSet(style)
  1898. if err != nil {
  1899. return 0, err
  1900. }
  1901. dxf := dxf{
  1902. Fill: setFills(fs, false),
  1903. Alignment: setAlignment(fs),
  1904. Border: setBorders(fs),
  1905. }
  1906. if fs.Font != nil {
  1907. dxf.Font = f.setFont(fs)
  1908. }
  1909. dxfStr, _ := xml.Marshal(dxf)
  1910. if s.Dxfs == nil {
  1911. s.Dxfs = &xlsxDxfs{}
  1912. }
  1913. s.Dxfs.Count++
  1914. s.Dxfs.Dxfs = append(s.Dxfs.Dxfs, &xlsxDxf{
  1915. Dxf: string(dxfStr[5 : len(dxfStr)-6]),
  1916. })
  1917. return s.Dxfs.Count - 1, nil
  1918. }
  1919. // GetDefaultFont provides the default font name currently set in the workbook
  1920. // Documents generated by excelize start with Calibri.
  1921. func (f *File) GetDefaultFont() string {
  1922. font := f.readDefaultFont()
  1923. return font.Name.Val
  1924. }
  1925. // SetDefaultFont changes the default font in the workbook.
  1926. func (f *File) SetDefaultFont(fontName string) {
  1927. font := f.readDefaultFont()
  1928. font.Name.Val = fontName
  1929. s := f.stylesReader()
  1930. s.Fonts.Font[0] = font
  1931. custom := true
  1932. s.CellStyles.CellStyle[0].CustomBuiltIn = &custom
  1933. }
  1934. // readDefaultFont provides an unmarshalled font value.
  1935. func (f *File) readDefaultFont() *xlsxFont {
  1936. s := f.stylesReader()
  1937. return s.Fonts.Font[0]
  1938. }
  1939. // setFont provides a function to add font style by given cell format
  1940. // settings.
  1941. func (f *File) setFont(formatStyle *formatStyle) *xlsxFont {
  1942. fontUnderlineType := map[string]string{"single": "single", "double": "double"}
  1943. if formatStyle.Font.Size < 1 {
  1944. formatStyle.Font.Size = 11
  1945. }
  1946. if formatStyle.Font.Color == "" {
  1947. formatStyle.Font.Color = "#000000"
  1948. }
  1949. fnt := xlsxFont{
  1950. Sz: &attrValFloat{Val: formatStyle.Font.Size},
  1951. Color: &xlsxColor{RGB: getPaletteColor(formatStyle.Font.Color)},
  1952. Name: &attrValString{Val: formatStyle.Font.Family},
  1953. Family: &attrValInt{Val: 2},
  1954. }
  1955. if formatStyle.Font.Bold {
  1956. fnt.B = &formatStyle.Font.Bold
  1957. }
  1958. if formatStyle.Font.Italic {
  1959. fnt.I = &formatStyle.Font.Italic
  1960. }
  1961. if fnt.Name.Val == "" {
  1962. fnt.Name.Val = f.GetDefaultFont()
  1963. }
  1964. if formatStyle.Font.Strike {
  1965. strike := true
  1966. fnt.Strike = &strike
  1967. }
  1968. val, ok := fontUnderlineType[formatStyle.Font.Underline]
  1969. if ok {
  1970. fnt.U = &attrValString{Val: val}
  1971. }
  1972. return &fnt
  1973. }
  1974. // setNumFmt provides a function to check if number format code in the range
  1975. // of built-in values.
  1976. func setNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int {
  1977. dp := "0."
  1978. numFmtID := 164 // Default custom number format code from 164.
  1979. if formatStyle.DecimalPlaces < 0 || formatStyle.DecimalPlaces > 30 {
  1980. formatStyle.DecimalPlaces = 2
  1981. }
  1982. for i := 0; i < formatStyle.DecimalPlaces; i++ {
  1983. dp += "0"
  1984. }
  1985. if formatStyle.CustomNumFmt != nil {
  1986. return setCustomNumFmt(style, formatStyle)
  1987. }
  1988. _, ok := builtInNumFmt[formatStyle.NumFmt]
  1989. if !ok {
  1990. fc, currency := currencyNumFmt[formatStyle.NumFmt]
  1991. if !currency {
  1992. return setLangNumFmt(style, formatStyle)
  1993. }
  1994. fc = strings.Replace(fc, "0.00", dp, -1)
  1995. if formatStyle.NegRed {
  1996. fc = fc + ";[Red]" + fc
  1997. }
  1998. if style.NumFmts != nil {
  1999. numFmtID = style.NumFmts.NumFmt[len(style.NumFmts.NumFmt)-1].NumFmtID + 1
  2000. nf := xlsxNumFmt{
  2001. FormatCode: fc,
  2002. NumFmtID: numFmtID,
  2003. }
  2004. style.NumFmts.NumFmt = append(style.NumFmts.NumFmt, &nf)
  2005. style.NumFmts.Count++
  2006. } else {
  2007. nf := xlsxNumFmt{
  2008. FormatCode: fc,
  2009. NumFmtID: numFmtID,
  2010. }
  2011. numFmts := xlsxNumFmts{
  2012. NumFmt: []*xlsxNumFmt{&nf},
  2013. Count: 1,
  2014. }
  2015. style.NumFmts = &numFmts
  2016. }
  2017. return numFmtID
  2018. }
  2019. return formatStyle.NumFmt
  2020. }
  2021. // setCustomNumFmt provides a function to set custom number format code.
  2022. func setCustomNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int {
  2023. nf := xlsxNumFmt{FormatCode: *formatStyle.CustomNumFmt}
  2024. if style.NumFmts != nil {
  2025. nf.NumFmtID = style.NumFmts.NumFmt[len(style.NumFmts.NumFmt)-1].NumFmtID + 1
  2026. style.NumFmts.NumFmt = append(style.NumFmts.NumFmt, &nf)
  2027. style.NumFmts.Count++
  2028. } else {
  2029. nf.NumFmtID = 164
  2030. numFmts := xlsxNumFmts{
  2031. NumFmt: []*xlsxNumFmt{&nf},
  2032. Count: 1,
  2033. }
  2034. style.NumFmts = &numFmts
  2035. }
  2036. return nf.NumFmtID
  2037. }
  2038. // setLangNumFmt provides a function to set number format code with language.
  2039. func setLangNumFmt(style *xlsxStyleSheet, formatStyle *formatStyle) int {
  2040. numFmts, ok := langNumFmt[formatStyle.Lang]
  2041. if !ok {
  2042. return 0
  2043. }
  2044. var fc string
  2045. fc, ok = numFmts[formatStyle.NumFmt]
  2046. if !ok {
  2047. return 0
  2048. }
  2049. nf := xlsxNumFmt{FormatCode: fc}
  2050. if style.NumFmts != nil {
  2051. nf.NumFmtID = style.NumFmts.NumFmt[len(style.NumFmts.NumFmt)-1].NumFmtID + 1
  2052. style.NumFmts.NumFmt = append(style.NumFmts.NumFmt, &nf)
  2053. style.NumFmts.Count++
  2054. } else {
  2055. nf.NumFmtID = formatStyle.NumFmt
  2056. numFmts := xlsxNumFmts{
  2057. NumFmt: []*xlsxNumFmt{&nf},
  2058. Count: 1,
  2059. }
  2060. style.NumFmts = &numFmts
  2061. }
  2062. return nf.NumFmtID
  2063. }
  2064. // setFills provides a function to add fill elements in the styles.xml by
  2065. // given cell format settings.
  2066. func setFills(formatStyle *formatStyle, fg bool) *xlsxFill {
  2067. var patterns = []string{
  2068. "none",
  2069. "solid",
  2070. "mediumGray",
  2071. "darkGray",
  2072. "lightGray",
  2073. "darkHorizontal",
  2074. "darkVertical",
  2075. "darkDown",
  2076. "darkUp",
  2077. "darkGrid",
  2078. "darkTrellis",
  2079. "lightHorizontal",
  2080. "lightVertical",
  2081. "lightDown",
  2082. "lightUp",
  2083. "lightGrid",
  2084. "lightTrellis",
  2085. "gray125",
  2086. "gray0625",
  2087. }
  2088. var variants = []float64{
  2089. 90,
  2090. 0,
  2091. 45,
  2092. 135,
  2093. }
  2094. var fill xlsxFill
  2095. switch formatStyle.Fill.Type {
  2096. case "gradient":
  2097. if len(formatStyle.Fill.Color) != 2 {
  2098. break
  2099. }
  2100. var gradient xlsxGradientFill
  2101. switch formatStyle.Fill.Shading {
  2102. case 0, 1, 2, 3:
  2103. gradient.Degree = variants[formatStyle.Fill.Shading]
  2104. case 4:
  2105. gradient.Type = "path"
  2106. case 5:
  2107. gradient.Type = "path"
  2108. gradient.Bottom = 0.5
  2109. gradient.Left = 0.5
  2110. gradient.Right = 0.5
  2111. gradient.Top = 0.5
  2112. default:
  2113. break
  2114. }
  2115. var stops []*xlsxGradientFillStop
  2116. for index, color := range formatStyle.Fill.Color {
  2117. var stop xlsxGradientFillStop
  2118. stop.Position = float64(index)
  2119. stop.Color.RGB = getPaletteColor(color)
  2120. stops = append(stops, &stop)
  2121. }
  2122. gradient.Stop = stops
  2123. fill.GradientFill = &gradient
  2124. case "pattern":
  2125. if formatStyle.Fill.Pattern > 18 || formatStyle.Fill.Pattern < 0 {
  2126. break
  2127. }
  2128. if len(formatStyle.Fill.Color) < 1 {
  2129. break
  2130. }
  2131. var pattern xlsxPatternFill
  2132. pattern.PatternType = patterns[formatStyle.Fill.Pattern]
  2133. if fg {
  2134. pattern.FgColor.RGB = getPaletteColor(formatStyle.Fill.Color[0])
  2135. } else {
  2136. pattern.BgColor.RGB = getPaletteColor(formatStyle.Fill.Color[0])
  2137. }
  2138. fill.PatternFill = &pattern
  2139. default:
  2140. return nil
  2141. }
  2142. return &fill
  2143. }
  2144. // setAlignment provides a function to formatting information pertaining to
  2145. // text alignment in cells. There are a variety of choices for how text is
  2146. // aligned both horizontally and vertically, as well as indentation settings,
  2147. // and so on.
  2148. func setAlignment(formatStyle *formatStyle) *xlsxAlignment {
  2149. var alignment xlsxAlignment
  2150. if formatStyle.Alignment != nil {
  2151. alignment.Horizontal = formatStyle.Alignment.Horizontal
  2152. alignment.Indent = formatStyle.Alignment.Indent
  2153. alignment.JustifyLastLine = formatStyle.Alignment.JustifyLastLine
  2154. alignment.ReadingOrder = formatStyle.Alignment.ReadingOrder
  2155. alignment.RelativeIndent = formatStyle.Alignment.RelativeIndent
  2156. alignment.ShrinkToFit = formatStyle.Alignment.ShrinkToFit
  2157. alignment.TextRotation = formatStyle.Alignment.TextRotation
  2158. alignment.Vertical = formatStyle.Alignment.Vertical
  2159. alignment.WrapText = formatStyle.Alignment.WrapText
  2160. }
  2161. return &alignment
  2162. }
  2163. // setProtection provides a function to set protection properties associated
  2164. // with the cell.
  2165. func setProtection(formatStyle *formatStyle) *xlsxProtection {
  2166. var protection xlsxProtection
  2167. if formatStyle.Protection != nil {
  2168. protection.Hidden = formatStyle.Protection.Hidden
  2169. protection.Locked = formatStyle.Protection.Locked
  2170. }
  2171. return &protection
  2172. }
  2173. // setBorders provides a function to add border elements in the styles.xml by
  2174. // given borders format settings.
  2175. func setBorders(formatStyle *formatStyle) *xlsxBorder {
  2176. var styles = []string{
  2177. "none",
  2178. "thin",
  2179. "medium",
  2180. "dashed",
  2181. "dotted",
  2182. "thick",
  2183. "double",
  2184. "hair",
  2185. "mediumDashed",
  2186. "dashDot",
  2187. "mediumDashDot",
  2188. "dashDotDot",
  2189. "mediumDashDotDot",
  2190. "slantDashDot",
  2191. }
  2192. var border xlsxBorder
  2193. for _, v := range formatStyle.Border {
  2194. if 0 <= v.Style && v.Style < 14 {
  2195. var color xlsxColor
  2196. color.RGB = getPaletteColor(v.Color)
  2197. switch v.Type {
  2198. case "left":
  2199. border.Left.Style = styles[v.Style]
  2200. border.Left.Color = &color
  2201. case "right":
  2202. border.Right.Style = styles[v.Style]
  2203. border.Right.Color = &color
  2204. case "top":
  2205. border.Top.Style = styles[v.Style]
  2206. border.Top.Color = &color
  2207. case "bottom":
  2208. border.Bottom.Style = styles[v.Style]
  2209. border.Bottom.Color = &color
  2210. case "diagonalUp":
  2211. border.Diagonal.Style = styles[v.Style]
  2212. border.Diagonal.Color = &color
  2213. border.DiagonalUp = true
  2214. case "diagonalDown":
  2215. border.Diagonal.Style = styles[v.Style]
  2216. border.Diagonal.Color = &color
  2217. border.DiagonalDown = true
  2218. }
  2219. }
  2220. }
  2221. return &border
  2222. }
  2223. // setCellXfs provides a function to set describes all of the formatting for a
  2224. // cell.
  2225. func setCellXfs(style *xlsxStyleSheet, fontID, numFmtID, fillID, borderID int, applyAlignment, applyProtection bool, alignment *xlsxAlignment, protection *xlsxProtection) int {
  2226. var xf xlsxXf
  2227. xf.FontID = fontID
  2228. if fontID != 0 {
  2229. xf.ApplyFont = true
  2230. }
  2231. xf.NumFmtID = numFmtID
  2232. if numFmtID != 0 {
  2233. xf.ApplyNumberFormat = true
  2234. }
  2235. xf.FillID = fillID
  2236. xf.BorderID = borderID
  2237. style.CellXfs.Count++
  2238. xf.Alignment = alignment
  2239. xf.ApplyAlignment = applyAlignment
  2240. if applyProtection {
  2241. xf.ApplyProtection = applyProtection
  2242. xf.Protection = protection
  2243. }
  2244. xfID := 0
  2245. xf.XfID = &xfID
  2246. style.CellXfs.Xf = append(style.CellXfs.Xf, xf)
  2247. return style.CellXfs.Count - 1
  2248. }
  2249. // GetCellStyle provides a function to get cell style index by given worksheet
  2250. // name and cell coordinates.
  2251. func (f *File) GetCellStyle(sheet, axis string) (int, error) {
  2252. xlsx, err := f.workSheetReader(sheet)
  2253. if err != nil {
  2254. return 0, err
  2255. }
  2256. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  2257. if err != nil {
  2258. return 0, err
  2259. }
  2260. return f.prepareCellStyle(xlsx, col, cellData.S), err
  2261. }
  2262. // SetCellStyle provides a function to add style attribute for cells by given
  2263. // worksheet name, coordinate area and style ID. Note that diagonalDown and
  2264. // diagonalUp type border should be use same color in the same coordinate
  2265. // area.
  2266. //
  2267. // For example create a borders of cell H9 on Sheet1:
  2268. //
  2269. // style, err := f.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":3},{"type":"top","color":"00FF00","style":4},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":7},{"type":"diagonalUp","color":"A020F0","style":8}]}`)
  2270. // if err != nil {
  2271. // fmt.Println(err)
  2272. // }
  2273. // err = f.SetCellStyle("Sheet1", "H9", "H9", style)
  2274. //
  2275. // Set gradient fill with vertical variants shading styles for cell H9 on
  2276. // Sheet1:
  2277. //
  2278. // style, err := f.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}}`)
  2279. // if err != nil {
  2280. // fmt.Println(err)
  2281. // }
  2282. // err = f.SetCellStyle("Sheet1", "H9", "H9", style)
  2283. //
  2284. // Set solid style pattern fill for cell H9 on Sheet1:
  2285. //
  2286. // style, err := f.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`)
  2287. // if err != nil {
  2288. // fmt.Println(err)
  2289. // }
  2290. // err = f.SetCellStyle("Sheet1", "H9", "H9", style)
  2291. //
  2292. // Set alignment style for cell H9 on Sheet1:
  2293. //
  2294. // style, err := f.NewStyle(`{"alignment":{"horizontal":"center","ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1,"shrink_to_fit":true,"text_rotation":45,"vertical":"","wrap_text":true}}`)
  2295. // if err != nil {
  2296. // fmt.Println(err)
  2297. // }
  2298. // err = f.SetCellStyle("Sheet1", "H9", "H9", style)
  2299. //
  2300. // Dates and times in Excel are represented by real numbers, for example "Apr 7
  2301. // 2017 12:00 PM" is represented by the number 42920.5. Set date and time format
  2302. // for cell H9 on Sheet1:
  2303. //
  2304. // f.SetCellValue("Sheet1", "H9", 42920.5)
  2305. // style, err := f.NewStyle(`{"number_format": 22}`)
  2306. // if err != nil {
  2307. // fmt.Println(err)
  2308. // }
  2309. // err = f.SetCellStyle("Sheet1", "H9", "H9", style)
  2310. //
  2311. // Set font style for cell H9 on Sheet1:
  2312. //
  2313. // style, err := f.NewStyle(`{"font":{"bold":true,"italic":true,"family":"Times New Roman","size":36,"color":"#777777"}}`)
  2314. // if err != nil {
  2315. // fmt.Println(err)
  2316. // }
  2317. // err = f.SetCellStyle("Sheet1", "H9", "H9", style)
  2318. //
  2319. // Hide and lock for cell H9 on Sheet1:
  2320. //
  2321. // style, err := f.NewStyle(`{"protection":{"hidden":true, "locked":true}}`)
  2322. // if err != nil {
  2323. // fmt.Println(err)
  2324. // }
  2325. // err = f.SetCellStyle("Sheet1", "H9", "H9", style)
  2326. //
  2327. func (f *File) SetCellStyle(sheet, hcell, vcell string, styleID int) error {
  2328. hcol, hrow, err := CellNameToCoordinates(hcell)
  2329. if err != nil {
  2330. return err
  2331. }
  2332. vcol, vrow, err := CellNameToCoordinates(vcell)
  2333. if err != nil {
  2334. return err
  2335. }
  2336. // Normalize the coordinate area, such correct C1:B3 to B1:C3.
  2337. if vcol < hcol {
  2338. vcol, hcol = hcol, vcol
  2339. }
  2340. if vrow < hrow {
  2341. vrow, hrow = hrow, vrow
  2342. }
  2343. hcolIdx := hcol - 1
  2344. hrowIdx := hrow - 1
  2345. vcolIdx := vcol - 1
  2346. vrowIdx := vrow - 1
  2347. xlsx, err := f.workSheetReader(sheet)
  2348. if err != nil {
  2349. return err
  2350. }
  2351. prepareSheetXML(xlsx, vcol, vrow)
  2352. makeContiguousColumns(xlsx, hrow, vrow, vcol)
  2353. for r := hrowIdx; r <= vrowIdx; r++ {
  2354. for k := hcolIdx; k <= vcolIdx; k++ {
  2355. xlsx.SheetData.Row[r].C[k].S = styleID
  2356. }
  2357. }
  2358. return err
  2359. }
  2360. // SetConditionalFormat provides a function to create conditional formatting
  2361. // rule for cell value. Conditional formatting is a feature of Excel which
  2362. // allows you to apply a format to a cell or a range of cells based on certain
  2363. // criteria.
  2364. //
  2365. // The type option is a required parameter and it has no default value.
  2366. // Allowable type values and their associated parameters are:
  2367. //
  2368. // Type | Parameters
  2369. // ---------------+------------------------------------
  2370. // cell | criteria
  2371. // | value
  2372. // | minimum
  2373. // | maximum
  2374. // date | criteria
  2375. // | value
  2376. // | minimum
  2377. // | maximum
  2378. // time_period | criteria
  2379. // text | criteria
  2380. // | value
  2381. // average | criteria
  2382. // duplicate | (none)
  2383. // unique | (none)
  2384. // top | criteria
  2385. // | value
  2386. // bottom | criteria
  2387. // | value
  2388. // blanks | (none)
  2389. // no_blanks | (none)
  2390. // errors | (none)
  2391. // no_errors | (none)
  2392. // 2_color_scale | min_type
  2393. // | max_type
  2394. // | min_value
  2395. // | max_value
  2396. // | min_color
  2397. // | max_color
  2398. // 3_color_scale | min_type
  2399. // | mid_type
  2400. // | max_type
  2401. // | min_value
  2402. // | mid_value
  2403. // | max_value
  2404. // | min_color
  2405. // | mid_color
  2406. // | max_color
  2407. // data_bar | min_type
  2408. // | max_type
  2409. // | min_value
  2410. // | max_value
  2411. // | bar_color
  2412. // formula | criteria
  2413. //
  2414. // The criteria parameter is used to set the criteria by which the cell data
  2415. // will be evaluated. It has no default value. The most common criteria as
  2416. // applied to {"type":"cell"} are:
  2417. //
  2418. // between |
  2419. // not between |
  2420. // equal to | ==
  2421. // not equal to | !=
  2422. // greater than | >
  2423. // less than | <
  2424. // greater than or equal to | >=
  2425. // less than or equal to | <=
  2426. //
  2427. // You can either use Excel's textual description strings, in the first column
  2428. // above, or the more common symbolic alternatives.
  2429. //
  2430. // Additional criteria which are specific to other conditional format types are
  2431. // shown in the relevant sections below.
  2432. //
  2433. // value: The value is generally used along with the criteria parameter to set
  2434. // the rule by which the cell data will be evaluated:
  2435. //
  2436. // f.SetConditionalFormat("Sheet1", "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format))
  2437. //
  2438. // The value property can also be an cell reference:
  2439. //
  2440. // f.SetConditionalFormat("Sheet1", "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"$C$1"}]`, format))
  2441. //
  2442. // type: format - The format parameter is used to specify the format that will
  2443. // be applied to the cell when the conditional formatting criterion is met. The
  2444. // format is created using the NewConditionalStyle() method in the same way as
  2445. // cell formats:
  2446. //
  2447. // format, err = f.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`)
  2448. // if err != nil {
  2449. // fmt.Println(err)
  2450. // }
  2451. // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format))
  2452. //
  2453. // Note: In Excel, a conditional format is superimposed over the existing cell
  2454. // format and not all cell format properties can be modified. Properties that
  2455. // cannot be modified in a conditional format are font name, font size,
  2456. // superscript and subscript, diagonal borders, all alignment properties and all
  2457. // protection properties.
  2458. //
  2459. // Excel specifies some default formats to be used with conditional formatting.
  2460. // These can be replicated using the following excelize formats:
  2461. //
  2462. // // Rose format for bad conditional.
  2463. // format1, err = f.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`)
  2464. //
  2465. // // Light yellow format for neutral conditional.
  2466. // format2, err = f.NewConditionalStyle(`{"font":{"color":"#9B5713"},"fill":{"type":"pattern","color":["#FEEAA0"],"pattern":1}}`)
  2467. //
  2468. // // Light green format for good conditional.
  2469. // format3, err = f.NewConditionalStyle(`{"font":{"color":"#09600B"},"fill":{"type":"pattern","color":["#C7EECF"],"pattern":1}}`)
  2470. //
  2471. // type: minimum - The minimum parameter is used to set the lower limiting value
  2472. // when the criteria is either "between" or "not between".
  2473. //
  2474. // // Hightlight cells rules: between...
  2475. // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format))
  2476. //
  2477. // type: maximum - The maximum parameter is used to set the upper limiting value
  2478. // when the criteria is either "between" or "not between". See the previous
  2479. // example.
  2480. //
  2481. // type: average - The average type is used to specify Excel's "Average" style
  2482. // conditional format:
  2483. //
  2484. // // Top/Bottom rules: Above Average...
  2485. // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": true}]`, format1))
  2486. //
  2487. // // Top/Bottom rules: Below Average...
  2488. // f.SetConditionalFormat("Sheet1", "B1:B10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": false}]`, format2))
  2489. //
  2490. // type: duplicate - The duplicate type is used to highlight duplicate cells in a range:
  2491. //
  2492. // // Hightlight cells rules: Duplicate Values...
  2493. // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format))
  2494. //
  2495. // type: unique - The unique type is used to highlight unique cells in a range:
  2496. //
  2497. // // Hightlight cells rules: Not Equal To...
  2498. // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, format))
  2499. //
  2500. // type: top - The top type is used to specify the top n values by number or percentage in a range:
  2501. //
  2502. // // Top/Bottom rules: Top 10.
  2503. // f.SetConditionalFormat("Sheet1", "H1:H10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6"}]`, format))
  2504. //
  2505. // The criteria can be used to indicate that a percentage condition is required:
  2506. //
  2507. // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6","percent":true}]`, format))
  2508. //
  2509. // type: 2_color_scale - The 2_color_scale type is used to specify Excel's "2
  2510. // Color Scale" style conditional format:
  2511. //
  2512. // // Color scales: 2 color.
  2513. // f.SetConditionalFormat("Sheet1", "A1:A10", `[{"type":"2_color_scale","criteria":"=","min_type":"min","max_type":"max","min_color":"#F8696B","max_color":"#63BE7B"}]`)
  2514. //
  2515. // This conditional type can be modified with min_type, max_type, min_value,
  2516. // max_value, min_color and max_color, see below.
  2517. //
  2518. // type: 3_color_scale - The 3_color_scale type is used to specify Excel's "3
  2519. // Color Scale" style conditional format:
  2520. //
  2521. // // Color scales: 3 color.
  2522. // f.SetConditionalFormat("Sheet1", "A1:A10", `[{"type":"3_color_scale","criteria":"=","min_type":"min","mid_type":"percentile","max_type":"max","min_color":"#F8696B","mid_color":"#FFEB84","max_color":"#63BE7B"}]`)
  2523. //
  2524. // This conditional type can be modified with min_type, mid_type, max_type,
  2525. // min_value, mid_value, max_value, min_color, mid_color and max_color, see
  2526. // below.
  2527. //
  2528. // type: data_bar - The data_bar type is used to specify Excel's "Data Bar"
  2529. // style conditional format.
  2530. //
  2531. // min_type - The min_type and max_type properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_type is available for 3_color_scale. The properties are used as follows:
  2532. //
  2533. // // Data Bars: Gradient Fill.
  2534. // f.SetConditionalFormat("Sheet1", "K1:K10", `[{"type":"data_bar", "criteria":"=", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`)
  2535. //
  2536. // The available min/mid/max types are:
  2537. //
  2538. // min (for min_type only)
  2539. // num
  2540. // percent
  2541. // percentile
  2542. // formula
  2543. // max (for max_type only)
  2544. //
  2545. // mid_type - Used for 3_color_scale. Same as min_type, see above.
  2546. //
  2547. // max_type - Same as min_type, see above.
  2548. //
  2549. // min_value - The min_value and max_value properties are available when the
  2550. // conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The
  2551. // mid_value is available for 3_color_scale.
  2552. //
  2553. // mid_value - Used for 3_color_scale. Same as min_value, see above.
  2554. //
  2555. // max_value - Same as min_value, see above.
  2556. //
  2557. // min_color - The min_color and max_color properties are available when the
  2558. // conditional formatting type is 2_color_scale, 3_color_scale or data_bar.
  2559. // The mid_color is available for 3_color_scale. The properties are used as
  2560. // follows:
  2561. //
  2562. // // Color scales: 3 color.
  2563. // f.SetConditionalFormat("Sheet1", "B1:B10", `[{"type":"3_color_scale","criteria":"=","min_type":"min","mid_type":"percentile","max_type":"max","min_color":"#F8696B","mid_color":"#FFEB84","max_color":"#63BE7B"}]`)
  2564. //
  2565. // mid_color - Used for 3_color_scale. Same as min_color, see above.
  2566. //
  2567. // max_color - Same as min_color, see above.
  2568. //
  2569. // bar_color - Used for data_bar. Same as min_color, see above.
  2570. //
  2571. func (f *File) SetConditionalFormat(sheet, area, formatSet string) error {
  2572. var format []*formatConditional
  2573. err := json.Unmarshal([]byte(formatSet), &format)
  2574. if err != nil {
  2575. return err
  2576. }
  2577. drawContFmtFunc := map[string]func(p int, ct string, fmtCond *formatConditional) *xlsxCfRule{
  2578. "cellIs": drawCondFmtCellIs,
  2579. "top10": drawCondFmtTop10,
  2580. "aboveAverage": drawCondFmtAboveAverage,
  2581. "duplicateValues": drawCondFmtDuplicateUniqueValues,
  2582. "uniqueValues": drawCondFmtDuplicateUniqueValues,
  2583. "2_color_scale": drawCondFmtColorScale,
  2584. "3_color_scale": drawCondFmtColorScale,
  2585. "dataBar": drawCondFmtDataBar,
  2586. "expression": drawConfFmtExp,
  2587. }
  2588. xlsx, err := f.workSheetReader(sheet)
  2589. if err != nil {
  2590. return err
  2591. }
  2592. cfRule := []*xlsxCfRule{}
  2593. for p, v := range format {
  2594. var vt, ct string
  2595. var ok bool
  2596. // "type" is a required parameter, check for valid validation types.
  2597. vt, ok = validType[v.Type]
  2598. if ok {
  2599. // Check for valid criteria types.
  2600. ct, ok = criteriaType[v.Criteria]
  2601. if ok || vt == "expression" {
  2602. drawfunc, ok := drawContFmtFunc[vt]
  2603. if ok {
  2604. cfRule = append(cfRule, drawfunc(p, ct, v))
  2605. }
  2606. }
  2607. }
  2608. }
  2609. xlsx.ConditionalFormatting = append(xlsx.ConditionalFormatting, &xlsxConditionalFormatting{
  2610. SQRef: area,
  2611. CfRule: cfRule,
  2612. })
  2613. return err
  2614. }
  2615. // drawCondFmtCellIs provides a function to create conditional formatting rule
  2616. // for cell value (include between, not between, equal, not equal, greater
  2617. // than and less than) by given priority, criteria type and format settings.
  2618. func drawCondFmtCellIs(p int, ct string, format *formatConditional) *xlsxCfRule {
  2619. c := &xlsxCfRule{
  2620. Priority: p + 1,
  2621. Type: validType[format.Type],
  2622. Operator: ct,
  2623. DxfID: &format.Format,
  2624. }
  2625. // "between" and "not between" criteria require 2 values.
  2626. _, ok := map[string]bool{"between": true, "notBetween": true}[ct]
  2627. if ok {
  2628. c.Formula = append(c.Formula, format.Minimum)
  2629. c.Formula = append(c.Formula, format.Maximum)
  2630. }
  2631. _, ok = map[string]bool{"equal": true, "notEqual": true, "greaterThan": true, "lessThan": true}[ct]
  2632. if ok {
  2633. c.Formula = append(c.Formula, format.Value)
  2634. }
  2635. return c
  2636. }
  2637. // drawCondFmtTop10 provides a function to create conditional formatting rule
  2638. // for top N (default is top 10) by given priority, criteria type and format
  2639. // settings.
  2640. func drawCondFmtTop10(p int, ct string, format *formatConditional) *xlsxCfRule {
  2641. c := &xlsxCfRule{
  2642. Priority: p + 1,
  2643. Type: validType[format.Type],
  2644. Rank: 10,
  2645. DxfID: &format.Format,
  2646. Percent: format.Percent,
  2647. }
  2648. rank, err := strconv.Atoi(format.Value)
  2649. if err == nil {
  2650. c.Rank = rank
  2651. }
  2652. return c
  2653. }
  2654. // drawCondFmtAboveAverage provides a function to create conditional
  2655. // formatting rule for above average and below average by given priority,
  2656. // criteria type and format settings.
  2657. func drawCondFmtAboveAverage(p int, ct string, format *formatConditional) *xlsxCfRule {
  2658. return &xlsxCfRule{
  2659. Priority: p + 1,
  2660. Type: validType[format.Type],
  2661. AboveAverage: &format.AboveAverage,
  2662. DxfID: &format.Format,
  2663. }
  2664. }
  2665. // drawCondFmtDuplicateUniqueValues provides a function to create conditional
  2666. // formatting rule for duplicate and unique values by given priority, criteria
  2667. // type and format settings.
  2668. func drawCondFmtDuplicateUniqueValues(p int, ct string, format *formatConditional) *xlsxCfRule {
  2669. return &xlsxCfRule{
  2670. Priority: p + 1,
  2671. Type: validType[format.Type],
  2672. DxfID: &format.Format,
  2673. }
  2674. }
  2675. // drawCondFmtColorScale provides a function to create conditional formatting
  2676. // rule for color scale (include 2 color scale and 3 color scale) by given
  2677. // priority, criteria type and format settings.
  2678. func drawCondFmtColorScale(p int, ct string, format *formatConditional) *xlsxCfRule {
  2679. minValue := format.MinValue
  2680. if minValue == "" {
  2681. minValue = "0"
  2682. }
  2683. maxValue := format.MaxValue
  2684. if maxValue == "" {
  2685. maxValue = "0"
  2686. }
  2687. midValue := format.MidValue
  2688. if midValue == "" {
  2689. midValue = "50"
  2690. }
  2691. c := &xlsxCfRule{
  2692. Priority: p + 1,
  2693. Type: "colorScale",
  2694. ColorScale: &xlsxColorScale{
  2695. Cfvo: []*xlsxCfvo{
  2696. {Type: format.MinType, Val: minValue},
  2697. },
  2698. Color: []*xlsxColor{
  2699. {RGB: getPaletteColor(format.MinColor)},
  2700. },
  2701. },
  2702. }
  2703. if validType[format.Type] == "3_color_scale" {
  2704. c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MidType, Val: midValue})
  2705. c.ColorScale.Color = append(c.ColorScale.Color, &xlsxColor{RGB: getPaletteColor(format.MidColor)})
  2706. }
  2707. c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MaxType, Val: maxValue})
  2708. c.ColorScale.Color = append(c.ColorScale.Color, &xlsxColor{RGB: getPaletteColor(format.MaxColor)})
  2709. return c
  2710. }
  2711. // drawCondFmtDataBar provides a function to create conditional formatting
  2712. // rule for data bar by given priority, criteria type and format settings.
  2713. func drawCondFmtDataBar(p int, ct string, format *formatConditional) *xlsxCfRule {
  2714. return &xlsxCfRule{
  2715. Priority: p + 1,
  2716. Type: validType[format.Type],
  2717. DataBar: &xlsxDataBar{
  2718. Cfvo: []*xlsxCfvo{{Type: format.MinType}, {Type: format.MaxType}},
  2719. Color: []*xlsxColor{{RGB: getPaletteColor(format.BarColor)}},
  2720. },
  2721. }
  2722. }
  2723. // drawConfFmtExp provides a function to create conditional formatting rule
  2724. // for expression by given priority, criteria type and format settings.
  2725. func drawConfFmtExp(p int, ct string, format *formatConditional) *xlsxCfRule {
  2726. return &xlsxCfRule{
  2727. Priority: p + 1,
  2728. Type: validType[format.Type],
  2729. Formula: []string{format.Criteria},
  2730. DxfID: &format.Format,
  2731. }
  2732. }
  2733. // getPaletteColor provides a function to convert the RBG color by given
  2734. // string.
  2735. func getPaletteColor(color string) string {
  2736. return "FF" + strings.Replace(strings.ToUpper(color), "#", "", -1)
  2737. }
  2738. // themeReader provides a function to get the pointer to the xl/theme/theme1.xml
  2739. // structure after deserialization.
  2740. func (f *File) themeReader() *xlsxTheme {
  2741. var theme xlsxTheme
  2742. _ = xml.Unmarshal(namespaceStrictToTransitional(f.readXML("xl/theme/theme1.xml")), &theme)
  2743. return &theme
  2744. }
  2745. // ThemeColor applied the color with tint value.
  2746. func ThemeColor(baseColor string, tint float64) string {
  2747. if tint == 0 {
  2748. return "FF" + baseColor
  2749. }
  2750. r, _ := strconv.ParseInt(baseColor[0:2], 16, 64)
  2751. g, _ := strconv.ParseInt(baseColor[2:4], 16, 64)
  2752. b, _ := strconv.ParseInt(baseColor[4:6], 16, 64)
  2753. h, s, l := RGBToHSL(uint8(r), uint8(g), uint8(b))
  2754. if tint < 0 {
  2755. l *= (1 + tint)
  2756. } else {
  2757. l = l*(1-tint) + (1 - (1 - tint))
  2758. }
  2759. br, bg, bb := HSLToRGB(h, s, l)
  2760. return fmt.Sprintf("FF%02X%02X%02X", br, bg, bb)
  2761. }