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.
 
 
 
 

553 lines
15 KiB

  1. // Copyright 2016 - 2020 The excelize Authors. All rights reserved. Use of
  2. // this source code is governed by a BSD-style license that can be found in
  3. // the LICENSE file.
  4. //
  5. // Package excelize providing a set of functions that allow you to write to
  6. // and read from XLSX files. Support reads and writes XLSX file generated by
  7. // Microsoft Excel™ 2007 and later. Support save file without losing original
  8. // charts of XLSX. This library needs Go version 1.10 or later.
  9. package excelize
  10. // SheetPrOption is an option of a view of a worksheet. See SetSheetPrOptions().
  11. type SheetPrOption interface {
  12. setSheetPrOption(view *xlsxSheetPr)
  13. }
  14. // SheetPrOptionPtr is a writable SheetPrOption. See GetSheetPrOptions().
  15. type SheetPrOptionPtr interface {
  16. SheetPrOption
  17. getSheetPrOption(view *xlsxSheetPr)
  18. }
  19. type (
  20. // CodeName is a SheetPrOption
  21. CodeName string
  22. // EnableFormatConditionsCalculation is a SheetPrOption
  23. EnableFormatConditionsCalculation bool
  24. // Published is a SheetPrOption
  25. Published bool
  26. // FitToPage is a SheetPrOption
  27. FitToPage bool
  28. // AutoPageBreaks is a SheetPrOption
  29. AutoPageBreaks bool
  30. // OutlineSummaryBelow is an outlinePr, within SheetPr option
  31. OutlineSummaryBelow bool
  32. )
  33. // setSheetPrOption implements the SheetPrOption interface.
  34. func (o OutlineSummaryBelow) setSheetPrOption(pr *xlsxSheetPr) {
  35. if pr.OutlinePr == nil {
  36. pr.OutlinePr = new(xlsxOutlinePr)
  37. }
  38. pr.OutlinePr.SummaryBelow = bool(o)
  39. }
  40. // getSheetPrOption implements the SheetPrOptionPtr interface.
  41. func (o *OutlineSummaryBelow) getSheetPrOption(pr *xlsxSheetPr) {
  42. // Excel default: true
  43. if pr == nil || pr.OutlinePr == nil {
  44. *o = true
  45. return
  46. }
  47. *o = OutlineSummaryBelow(defaultTrue(&pr.OutlinePr.SummaryBelow))
  48. }
  49. // setSheetPrOption implements the SheetPrOption interface and specifies a
  50. // stable name of the sheet.
  51. func (o CodeName) setSheetPrOption(pr *xlsxSheetPr) {
  52. pr.CodeName = string(o)
  53. }
  54. // getSheetPrOption implements the SheetPrOptionPtr interface and get the
  55. // stable name of the sheet.
  56. func (o *CodeName) getSheetPrOption(pr *xlsxSheetPr) {
  57. if pr == nil {
  58. *o = ""
  59. return
  60. }
  61. *o = CodeName(pr.CodeName)
  62. }
  63. // setSheetPrOption implements the SheetPrOption interface and flag indicating
  64. // whether the conditional formatting calculations shall be evaluated.
  65. func (o EnableFormatConditionsCalculation) setSheetPrOption(pr *xlsxSheetPr) {
  66. pr.EnableFormatConditionsCalculation = boolPtr(bool(o))
  67. }
  68. // getSheetPrOption implements the SheetPrOptionPtr interface and get the
  69. // settings of whether the conditional formatting calculations shall be
  70. // evaluated.
  71. func (o *EnableFormatConditionsCalculation) getSheetPrOption(pr *xlsxSheetPr) {
  72. if pr == nil {
  73. *o = true
  74. return
  75. }
  76. *o = EnableFormatConditionsCalculation(defaultTrue(pr.EnableFormatConditionsCalculation))
  77. }
  78. // setSheetPrOption implements the SheetPrOption interface and flag indicating
  79. // whether the worksheet is published.
  80. func (o Published) setSheetPrOption(pr *xlsxSheetPr) {
  81. pr.Published = boolPtr(bool(o))
  82. }
  83. // getSheetPrOption implements the SheetPrOptionPtr interface and get the
  84. // settings of whether the worksheet is published.
  85. func (o *Published) getSheetPrOption(pr *xlsxSheetPr) {
  86. if pr == nil {
  87. *o = true
  88. return
  89. }
  90. *o = Published(defaultTrue(pr.Published))
  91. }
  92. // setSheetPrOption implements the SheetPrOption interface.
  93. func (o FitToPage) setSheetPrOption(pr *xlsxSheetPr) {
  94. if pr.PageSetUpPr == nil {
  95. if !o {
  96. return
  97. }
  98. pr.PageSetUpPr = new(xlsxPageSetUpPr)
  99. }
  100. pr.PageSetUpPr.FitToPage = bool(o)
  101. }
  102. // getSheetPrOption implements the SheetPrOptionPtr interface.
  103. func (o *FitToPage) getSheetPrOption(pr *xlsxSheetPr) {
  104. // Excel default: false
  105. if pr == nil || pr.PageSetUpPr == nil {
  106. *o = false
  107. return
  108. }
  109. *o = FitToPage(pr.PageSetUpPr.FitToPage)
  110. }
  111. // setSheetPrOption implements the SheetPrOption interface.
  112. func (o AutoPageBreaks) setSheetPrOption(pr *xlsxSheetPr) {
  113. if pr.PageSetUpPr == nil {
  114. if !o {
  115. return
  116. }
  117. pr.PageSetUpPr = new(xlsxPageSetUpPr)
  118. }
  119. pr.PageSetUpPr.AutoPageBreaks = bool(o)
  120. }
  121. // getSheetPrOption implements the SheetPrOptionPtr interface.
  122. func (o *AutoPageBreaks) getSheetPrOption(pr *xlsxSheetPr) {
  123. // Excel default: false
  124. if pr == nil || pr.PageSetUpPr == nil {
  125. *o = false
  126. return
  127. }
  128. *o = AutoPageBreaks(pr.PageSetUpPr.AutoPageBreaks)
  129. }
  130. // SetSheetPrOptions provides a function to sets worksheet properties.
  131. //
  132. // Available options:
  133. // CodeName(string)
  134. // EnableFormatConditionsCalculation(bool)
  135. // Published(bool)
  136. // FitToPage(bool)
  137. // AutoPageBreaks(bool)
  138. // OutlineSummaryBelow(bool)
  139. func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error {
  140. sheet, err := f.workSheetReader(name)
  141. if err != nil {
  142. return err
  143. }
  144. pr := sheet.SheetPr
  145. if pr == nil {
  146. pr = new(xlsxSheetPr)
  147. sheet.SheetPr = pr
  148. }
  149. for _, opt := range opts {
  150. opt.setSheetPrOption(pr)
  151. }
  152. return err
  153. }
  154. // GetSheetPrOptions provides a function to gets worksheet properties.
  155. //
  156. // Available options:
  157. // CodeName(string)
  158. // EnableFormatConditionsCalculation(bool)
  159. // Published(bool)
  160. // FitToPage(bool)
  161. // AutoPageBreaks(bool)
  162. // OutlineSummaryBelow(bool)
  163. func (f *File) GetSheetPrOptions(name string, opts ...SheetPrOptionPtr) error {
  164. sheet, err := f.workSheetReader(name)
  165. if err != nil {
  166. return err
  167. }
  168. pr := sheet.SheetPr
  169. for _, opt := range opts {
  170. opt.getSheetPrOption(pr)
  171. }
  172. return err
  173. }
  174. type (
  175. // PageMarginBottom specifies the bottom margin for the page.
  176. PageMarginBottom float64
  177. // PageMarginFooter specifies the footer margin for the page.
  178. PageMarginFooter float64
  179. // PageMarginHeader specifies the header margin for the page.
  180. PageMarginHeader float64
  181. // PageMarginLeft specifies the left margin for the page.
  182. PageMarginLeft float64
  183. // PageMarginRight specifies the right margin for the page.
  184. PageMarginRight float64
  185. // PageMarginTop specifies the top margin for the page.
  186. PageMarginTop float64
  187. )
  188. // setPageMargins provides a method to set the bottom margin for the worksheet.
  189. func (p PageMarginBottom) setPageMargins(pm *xlsxPageMargins) {
  190. pm.Bottom = float64(p)
  191. }
  192. // setPageMargins provides a method to get the bottom margin for the worksheet.
  193. func (p *PageMarginBottom) getPageMargins(pm *xlsxPageMargins) {
  194. // Excel default: 0.75
  195. if pm == nil || pm.Bottom == 0 {
  196. *p = 0.75
  197. return
  198. }
  199. *p = PageMarginBottom(pm.Bottom)
  200. }
  201. // setPageMargins provides a method to set the footer margin for the worksheet.
  202. func (p PageMarginFooter) setPageMargins(pm *xlsxPageMargins) {
  203. pm.Footer = float64(p)
  204. }
  205. // setPageMargins provides a method to get the footer margin for the worksheet.
  206. func (p *PageMarginFooter) getPageMargins(pm *xlsxPageMargins) {
  207. // Excel default: 0.3
  208. if pm == nil || pm.Footer == 0 {
  209. *p = 0.3
  210. return
  211. }
  212. *p = PageMarginFooter(pm.Footer)
  213. }
  214. // setPageMargins provides a method to set the header margin for the worksheet.
  215. func (p PageMarginHeader) setPageMargins(pm *xlsxPageMargins) {
  216. pm.Header = float64(p)
  217. }
  218. // setPageMargins provides a method to get the header margin for the worksheet.
  219. func (p *PageMarginHeader) getPageMargins(pm *xlsxPageMargins) {
  220. // Excel default: 0.3
  221. if pm == nil || pm.Header == 0 {
  222. *p = 0.3
  223. return
  224. }
  225. *p = PageMarginHeader(pm.Header)
  226. }
  227. // setPageMargins provides a method to set the left margin for the worksheet.
  228. func (p PageMarginLeft) setPageMargins(pm *xlsxPageMargins) {
  229. pm.Left = float64(p)
  230. }
  231. // setPageMargins provides a method to get the left margin for the worksheet.
  232. func (p *PageMarginLeft) getPageMargins(pm *xlsxPageMargins) {
  233. // Excel default: 0.7
  234. if pm == nil || pm.Left == 0 {
  235. *p = 0.7
  236. return
  237. }
  238. *p = PageMarginLeft(pm.Left)
  239. }
  240. // setPageMargins provides a method to set the right margin for the worksheet.
  241. func (p PageMarginRight) setPageMargins(pm *xlsxPageMargins) {
  242. pm.Right = float64(p)
  243. }
  244. // setPageMargins provides a method to get the right margin for the worksheet.
  245. func (p *PageMarginRight) getPageMargins(pm *xlsxPageMargins) {
  246. // Excel default: 0.7
  247. if pm == nil || pm.Right == 0 {
  248. *p = 0.7
  249. return
  250. }
  251. *p = PageMarginRight(pm.Right)
  252. }
  253. // setPageMargins provides a method to set the top margin for the worksheet.
  254. func (p PageMarginTop) setPageMargins(pm *xlsxPageMargins) {
  255. pm.Top = float64(p)
  256. }
  257. // setPageMargins provides a method to get the top margin for the worksheet.
  258. func (p *PageMarginTop) getPageMargins(pm *xlsxPageMargins) {
  259. // Excel default: 0.75
  260. if pm == nil || pm.Top == 0 {
  261. *p = 0.75
  262. return
  263. }
  264. *p = PageMarginTop(pm.Top)
  265. }
  266. // PageMarginsOptions is an option of a page margin of a worksheet. See
  267. // SetPageMargins().
  268. type PageMarginsOptions interface {
  269. setPageMargins(layout *xlsxPageMargins)
  270. }
  271. // PageMarginsOptionsPtr is a writable PageMarginsOptions. See
  272. // GetPageMargins().
  273. type PageMarginsOptionsPtr interface {
  274. PageMarginsOptions
  275. getPageMargins(layout *xlsxPageMargins)
  276. }
  277. // SetPageMargins provides a function to set worksheet page margins.
  278. //
  279. // Available options:
  280. // PageMarginBottom(float64)
  281. // PageMarginFooter(float64)
  282. // PageMarginHeader(float64)
  283. // PageMarginLeft(float64)
  284. // PageMarginRight(float64)
  285. // PageMarginTop(float64)
  286. func (f *File) SetPageMargins(sheet string, opts ...PageMarginsOptions) error {
  287. s, err := f.workSheetReader(sheet)
  288. if err != nil {
  289. return err
  290. }
  291. pm := s.PageMargins
  292. if pm == nil {
  293. pm = new(xlsxPageMargins)
  294. s.PageMargins = pm
  295. }
  296. for _, opt := range opts {
  297. opt.setPageMargins(pm)
  298. }
  299. return err
  300. }
  301. // GetPageMargins provides a function to get worksheet page margins.
  302. //
  303. // Available options:
  304. // PageMarginBottom(float64)
  305. // PageMarginFooter(float64)
  306. // PageMarginHeader(float64)
  307. // PageMarginLeft(float64)
  308. // PageMarginRight(float64)
  309. // PageMarginTop(float64)
  310. func (f *File) GetPageMargins(sheet string, opts ...PageMarginsOptionsPtr) error {
  311. s, err := f.workSheetReader(sheet)
  312. if err != nil {
  313. return err
  314. }
  315. pm := s.PageMargins
  316. for _, opt := range opts {
  317. opt.getPageMargins(pm)
  318. }
  319. return err
  320. }
  321. // SheetFormatPrOptions is an option of the formatting properties of a
  322. // worksheet. See SetSheetFormatPr().
  323. type SheetFormatPrOptions interface {
  324. setSheetFormatPr(formatPr *xlsxSheetFormatPr)
  325. }
  326. // SheetFormatPrOptionsPtr is a writable SheetFormatPrOptions. See
  327. // GetSheetFormatPr().
  328. type SheetFormatPrOptionsPtr interface {
  329. SheetFormatPrOptions
  330. getSheetFormatPr(formatPr *xlsxSheetFormatPr)
  331. }
  332. type (
  333. // BaseColWidth specifies the number of characters of the maximum digit width
  334. // of the normal style's font. This value does not include margin padding or
  335. // extra padding for gridlines. It is only the number of characters.
  336. BaseColWidth uint8
  337. // DefaultColWidth specifies the default column width measured as the number
  338. // of characters of the maximum digit width of the normal style's font.
  339. DefaultColWidth float64
  340. // DefaultRowHeight specifies the default row height measured in point size.
  341. // Optimization so we don't have to write the height on all rows. This can be
  342. // written out if most rows have custom height, to achieve the optimization.
  343. DefaultRowHeight float64
  344. // CustomHeight specifies the custom height.
  345. CustomHeight bool
  346. // ZeroHeight specifies if rows are hidden.
  347. ZeroHeight bool
  348. // ThickTop specifies if rows have a thick top border by default.
  349. ThickTop bool
  350. // ThickBottom specifies if rows have a thick bottom border by default.
  351. ThickBottom bool
  352. )
  353. // setSheetFormatPr provides a method to set the number of characters of the
  354. // maximum digit width of the normal style's font.
  355. func (p BaseColWidth) setSheetFormatPr(fp *xlsxSheetFormatPr) {
  356. fp.BaseColWidth = uint8(p)
  357. }
  358. // setSheetFormatPr provides a method to set the number of characters of the
  359. // maximum digit width of the normal style's font.
  360. func (p *BaseColWidth) getSheetFormatPr(fp *xlsxSheetFormatPr) {
  361. if fp == nil {
  362. *p = 0
  363. return
  364. }
  365. *p = BaseColWidth(fp.BaseColWidth)
  366. }
  367. // setSheetFormatPr provides a method to set the default column width measured
  368. // as the number of characters of the maximum digit width of the normal
  369. // style's font.
  370. func (p DefaultColWidth) setSheetFormatPr(fp *xlsxSheetFormatPr) {
  371. fp.DefaultColWidth = float64(p)
  372. }
  373. // getSheetFormatPr provides a method to get the default column width measured
  374. // as the number of characters of the maximum digit width of the normal
  375. // style's font.
  376. func (p *DefaultColWidth) getSheetFormatPr(fp *xlsxSheetFormatPr) {
  377. if fp == nil {
  378. *p = 0
  379. return
  380. }
  381. *p = DefaultColWidth(fp.DefaultColWidth)
  382. }
  383. // setSheetFormatPr provides a method to set the default row height measured
  384. // in point size.
  385. func (p DefaultRowHeight) setSheetFormatPr(fp *xlsxSheetFormatPr) {
  386. fp.DefaultRowHeight = float64(p)
  387. }
  388. // getSheetFormatPr provides a method to get the default row height measured
  389. // in point size.
  390. func (p *DefaultRowHeight) getSheetFormatPr(fp *xlsxSheetFormatPr) {
  391. if fp == nil {
  392. *p = 15
  393. return
  394. }
  395. *p = DefaultRowHeight(fp.DefaultRowHeight)
  396. }
  397. // setSheetFormatPr provides a method to set the custom height.
  398. func (p CustomHeight) setSheetFormatPr(fp *xlsxSheetFormatPr) {
  399. fp.CustomHeight = bool(p)
  400. }
  401. // getSheetFormatPr provides a method to get the custom height.
  402. func (p *CustomHeight) getSheetFormatPr(fp *xlsxSheetFormatPr) {
  403. if fp == nil {
  404. *p = false
  405. return
  406. }
  407. *p = CustomHeight(fp.CustomHeight)
  408. }
  409. // setSheetFormatPr provides a method to set if rows are hidden.
  410. func (p ZeroHeight) setSheetFormatPr(fp *xlsxSheetFormatPr) {
  411. fp.ZeroHeight = bool(p)
  412. }
  413. // getSheetFormatPr provides a method to get if rows are hidden.
  414. func (p *ZeroHeight) getSheetFormatPr(fp *xlsxSheetFormatPr) {
  415. if fp == nil {
  416. *p = false
  417. return
  418. }
  419. *p = ZeroHeight(fp.ZeroHeight)
  420. }
  421. // setSheetFormatPr provides a method to set if rows have a thick top border
  422. // by default.
  423. func (p ThickTop) setSheetFormatPr(fp *xlsxSheetFormatPr) {
  424. fp.ThickTop = bool(p)
  425. }
  426. // getSheetFormatPr provides a method to get if rows have a thick top border
  427. // by default.
  428. func (p *ThickTop) getSheetFormatPr(fp *xlsxSheetFormatPr) {
  429. if fp == nil {
  430. *p = false
  431. return
  432. }
  433. *p = ThickTop(fp.ThickTop)
  434. }
  435. // setSheetFormatPr provides a method to set if rows have a thick bottom
  436. // border by default.
  437. func (p ThickBottom) setSheetFormatPr(fp *xlsxSheetFormatPr) {
  438. fp.ThickBottom = bool(p)
  439. }
  440. // setSheetFormatPr provides a method to set if rows have a thick bottom
  441. // border by default.
  442. func (p *ThickBottom) getSheetFormatPr(fp *xlsxSheetFormatPr) {
  443. if fp == nil {
  444. *p = false
  445. return
  446. }
  447. *p = ThickBottom(fp.ThickBottom)
  448. }
  449. // SetSheetFormatPr provides a function to set worksheet formatting properties.
  450. //
  451. // Available options:
  452. // BaseColWidth(uint8)
  453. // DefaultColWidth(float64)
  454. // DefaultRowHeight(float64)
  455. // CustomHeight(bool)
  456. // ZeroHeight(bool)
  457. // ThickTop(bool)
  458. // ThickBottom(bool)
  459. func (f *File) SetSheetFormatPr(sheet string, opts ...SheetFormatPrOptions) error {
  460. s, err := f.workSheetReader(sheet)
  461. if err != nil {
  462. return err
  463. }
  464. fp := s.SheetFormatPr
  465. if fp == nil {
  466. fp = new(xlsxSheetFormatPr)
  467. s.SheetFormatPr = fp
  468. }
  469. for _, opt := range opts {
  470. opt.setSheetFormatPr(fp)
  471. }
  472. return err
  473. }
  474. // GetSheetFormatPr provides a function to get worksheet formatting properties.
  475. //
  476. // Available options:
  477. // BaseColWidth(uint8)
  478. // DefaultColWidth(float64)
  479. // DefaultRowHeight(float64)
  480. // CustomHeight(bool)
  481. // ZeroHeight(bool)
  482. // ThickTop(bool)
  483. // ThickBottom(bool)
  484. func (f *File) GetSheetFormatPr(sheet string, opts ...SheetFormatPrOptionsPtr) error {
  485. s, err := f.workSheetReader(sheet)
  486. if err != nil {
  487. return err
  488. }
  489. fp := s.SheetFormatPr
  490. for _, opt := range opts {
  491. opt.getSheetFormatPr(fp)
  492. }
  493. return err
  494. }