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.
 
 
 
 

863 lines
24 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. import (
  11. "encoding/xml"
  12. "errors"
  13. "fmt"
  14. "reflect"
  15. "strconv"
  16. "strings"
  17. "sync"
  18. "time"
  19. )
  20. const (
  21. // STCellFormulaTypeArray defined the formula is an array formula.
  22. STCellFormulaTypeArray = "array"
  23. // STCellFormulaTypeDataTable defined the formula is a data table formula.
  24. STCellFormulaTypeDataTable = "dataTable"
  25. // STCellFormulaTypeNormal defined the formula is a regular cell formula.
  26. STCellFormulaTypeNormal = "normal"
  27. // STCellFormulaTypeShared defined the formula is part of a shared formula.
  28. STCellFormulaTypeShared = "shared"
  29. )
  30. var rwMutex sync.RWMutex
  31. // GetCellValue provides a function to get formatted value from cell by given
  32. // worksheet name and axis in XLSX file. If it is possible to apply a format
  33. // to the cell value, it will do so, if not then an error will be returned,
  34. // along with the raw value of the cell.
  35. func (f *File) GetCellValue(sheet, axis string) (string, error) {
  36. return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
  37. val, err := c.getValueFrom(f, f.sharedStringsReader())
  38. if err != nil {
  39. return val, false, err
  40. }
  41. return val, true, err
  42. })
  43. }
  44. // SetCellValue provides a function to set value of a cell. The specified
  45. // coordinates should not be in the first row of the table. The following
  46. // shows the supported data types:
  47. //
  48. // int
  49. // int8
  50. // int16
  51. // int32
  52. // int64
  53. // uint
  54. // uint8
  55. // uint16
  56. // uint32
  57. // uint64
  58. // float32
  59. // float64
  60. // string
  61. // []byte
  62. // time.Duration
  63. // time.Time
  64. // bool
  65. // nil
  66. //
  67. // Note that default date format is m/d/yy h:mm of time.Time type value. You can
  68. // set numbers format by SetCellStyle() method.
  69. func (f *File) SetCellValue(sheet, axis string, value interface{}) error {
  70. var err error
  71. switch v := value.(type) {
  72. case int, int8, int16, int32, int64, uint, uint8, uint16, uint32, uint64:
  73. err = f.setCellIntFunc(sheet, axis, v)
  74. case float32:
  75. err = f.SetCellFloat(sheet, axis, float64(v), -1, 32)
  76. case float64:
  77. err = f.SetCellFloat(sheet, axis, v, -1, 64)
  78. case string:
  79. err = f.SetCellStr(sheet, axis, v)
  80. case []byte:
  81. err = f.SetCellStr(sheet, axis, string(v))
  82. case time.Duration:
  83. _, d := setCellDuration(v)
  84. err = f.SetCellDefault(sheet, axis, d)
  85. if err != nil {
  86. return err
  87. }
  88. err = f.setDefaultTimeStyle(sheet, axis, 21)
  89. case time.Time:
  90. err = f.setCellTimeFunc(sheet, axis, v)
  91. case bool:
  92. err = f.SetCellBool(sheet, axis, v)
  93. case nil:
  94. err = f.SetCellStr(sheet, axis, "")
  95. default:
  96. err = f.SetCellStr(sheet, axis, fmt.Sprint(value))
  97. }
  98. return err
  99. }
  100. // setCellIntFunc is a wrapper of SetCellInt.
  101. func (f *File) setCellIntFunc(sheet, axis string, value interface{}) error {
  102. var err error
  103. switch v := value.(type) {
  104. case int:
  105. err = f.SetCellInt(sheet, axis, v)
  106. case int8:
  107. err = f.SetCellInt(sheet, axis, int(v))
  108. case int16:
  109. err = f.SetCellInt(sheet, axis, int(v))
  110. case int32:
  111. err = f.SetCellInt(sheet, axis, int(v))
  112. case int64:
  113. err = f.SetCellInt(sheet, axis, int(v))
  114. case uint:
  115. err = f.SetCellInt(sheet, axis, int(v))
  116. case uint8:
  117. err = f.SetCellInt(sheet, axis, int(v))
  118. case uint16:
  119. err = f.SetCellInt(sheet, axis, int(v))
  120. case uint32:
  121. err = f.SetCellInt(sheet, axis, int(v))
  122. case uint64:
  123. err = f.SetCellInt(sheet, axis, int(v))
  124. }
  125. return err
  126. }
  127. // setCellTimeFunc provides a method to process time type of value for
  128. // SetCellValue.
  129. func (f *File) setCellTimeFunc(sheet, axis string, value time.Time) error {
  130. xlsx, err := f.workSheetReader(sheet)
  131. if err != nil {
  132. return err
  133. }
  134. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  135. if err != nil {
  136. return err
  137. }
  138. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  139. var isNum bool
  140. cellData.T, cellData.V, isNum, err = setCellTime(value)
  141. if err != nil {
  142. return err
  143. }
  144. if isNum {
  145. err = f.setDefaultTimeStyle(sheet, axis, 22)
  146. if err != nil {
  147. return err
  148. }
  149. }
  150. return err
  151. }
  152. func setCellTime(value time.Time) (t string, b string, isNum bool, err error) {
  153. var excelTime float64
  154. excelTime, err = timeToExcelTime(value)
  155. if err != nil {
  156. return
  157. }
  158. isNum = excelTime > 0
  159. if isNum {
  160. t, b = setCellDefault(strconv.FormatFloat(excelTime, 'f', -1, 64))
  161. } else {
  162. t, b = setCellDefault(value.Format(time.RFC3339Nano))
  163. }
  164. return
  165. }
  166. func setCellDuration(value time.Duration) (t string, v string) {
  167. v = strconv.FormatFloat(value.Seconds()/86400.0, 'f', -1, 32)
  168. return
  169. }
  170. // SetCellInt provides a function to set int type value of a cell by given
  171. // worksheet name, cell coordinates and cell value.
  172. func (f *File) SetCellInt(sheet, axis string, value int) error {
  173. rwMutex.Lock()
  174. defer rwMutex.Unlock()
  175. xlsx, err := f.workSheetReader(sheet)
  176. if err != nil {
  177. return err
  178. }
  179. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  180. if err != nil {
  181. return err
  182. }
  183. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  184. cellData.T, cellData.V = setCellInt(value)
  185. return err
  186. }
  187. func setCellInt(value int) (t string, v string) {
  188. v = strconv.Itoa(value)
  189. return
  190. }
  191. // SetCellBool provides a function to set bool type value of a cell by given
  192. // worksheet name, cell name and cell value.
  193. func (f *File) SetCellBool(sheet, axis string, value bool) error {
  194. rwMutex.Lock()
  195. defer rwMutex.Unlock()
  196. xlsx, err := f.workSheetReader(sheet)
  197. if err != nil {
  198. return err
  199. }
  200. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  201. if err != nil {
  202. return err
  203. }
  204. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  205. cellData.T, cellData.V = setCellBool(value)
  206. return err
  207. }
  208. func setCellBool(value bool) (t string, v string) {
  209. t = "b"
  210. if value {
  211. v = "1"
  212. } else {
  213. v = "0"
  214. }
  215. return
  216. }
  217. // SetCellFloat sets a floating point value into a cell. The prec parameter
  218. // specifies how many places after the decimal will be shown while -1 is a
  219. // special value that will use as many decimal places as necessary to
  220. // represent the number. bitSize is 32 or 64 depending on if a float32 or
  221. // float64 was originally used for the value. For Example:
  222. //
  223. // var x float32 = 1.325
  224. // f.SetCellFloat("Sheet1", "A1", float64(x), 2, 32)
  225. //
  226. func (f *File) SetCellFloat(sheet, axis string, value float64, prec, bitSize int) error {
  227. rwMutex.Lock()
  228. defer rwMutex.Unlock()
  229. xlsx, err := f.workSheetReader(sheet)
  230. if err != nil {
  231. return err
  232. }
  233. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  234. if err != nil {
  235. return err
  236. }
  237. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  238. cellData.T, cellData.V = setCellFloat(value, prec, bitSize)
  239. return err
  240. }
  241. func setCellFloat(value float64, prec, bitSize int) (t string, v string) {
  242. v = strconv.FormatFloat(value, 'f', prec, bitSize)
  243. return
  244. }
  245. // SetCellStr provides a function to set string type value of a cell. Total
  246. // number of characters that a cell can contain 32767 characters.
  247. func (f *File) SetCellStr(sheet, axis, value string) error {
  248. rwMutex.Lock()
  249. defer rwMutex.Unlock()
  250. xlsx, err := f.workSheetReader(sheet)
  251. if err != nil {
  252. return err
  253. }
  254. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  255. if err != nil {
  256. return err
  257. }
  258. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  259. cellData.T, cellData.V, cellData.XMLSpace = f.setCellString(value)
  260. return err
  261. }
  262. // setCellString provides a function to set string type to shared string
  263. // table.
  264. func (f *File) setCellString(value string) (t string, v string, ns xml.Attr) {
  265. if len(value) > TotalCellChars {
  266. value = value[0:TotalCellChars]
  267. }
  268. // Leading and ending space(s) character detection.
  269. if len(value) > 0 && (value[0] == 32 || value[len(value)-1] == 32) {
  270. ns = xml.Attr{
  271. Name: xml.Name{Space: NameSpaceXML, Local: "space"},
  272. Value: "preserve",
  273. }
  274. }
  275. t = "s"
  276. v = strconv.Itoa(f.setSharedString(value))
  277. return
  278. }
  279. // setSharedString provides a function to add string to the share string table.
  280. func (f *File) setSharedString(val string) int {
  281. sst := f.sharedStringsReader()
  282. if i, ok := f.sharedStringsMap[val]; ok {
  283. return i
  284. }
  285. sst.Count++
  286. sst.UniqueCount++
  287. sst.SI = append(sst.SI, xlsxSI{T: val})
  288. f.sharedStringsMap[val] = sst.UniqueCount - 1
  289. return sst.UniqueCount - 1
  290. }
  291. // setCellStr provides a function to set string type to cell.
  292. func setCellStr(value string) (t string, v string, ns xml.Attr) {
  293. if len(value) > TotalCellChars {
  294. value = value[0:TotalCellChars]
  295. }
  296. // Leading and ending space(s) character detection.
  297. if len(value) > 0 && (value[0] == 32 || value[len(value)-1] == 32) {
  298. ns = xml.Attr{
  299. Name: xml.Name{Space: NameSpaceXML, Local: "space"},
  300. Value: "preserve",
  301. }
  302. }
  303. t = "str"
  304. v = value
  305. return
  306. }
  307. // SetCellDefault provides a function to set string type value of a cell as
  308. // default format without escaping the cell.
  309. func (f *File) SetCellDefault(sheet, axis, value string) error {
  310. xlsx, err := f.workSheetReader(sheet)
  311. if err != nil {
  312. return err
  313. }
  314. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  315. if err != nil {
  316. return err
  317. }
  318. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  319. cellData.T, cellData.V = setCellDefault(value)
  320. return err
  321. }
  322. func setCellDefault(value string) (t string, v string) {
  323. v = value
  324. return
  325. }
  326. // GetCellFormula provides a function to get formula from cell by given
  327. // worksheet name and axis in XLSX file.
  328. func (f *File) GetCellFormula(sheet, axis string) (string, error) {
  329. return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
  330. if c.F == nil {
  331. return "", false, nil
  332. }
  333. if c.F.T == STCellFormulaTypeShared {
  334. return getSharedForumula(x, c.F.Si), true, nil
  335. }
  336. return c.F.Content, true, nil
  337. })
  338. }
  339. // FormulaOpts can be passed to SetCellFormula to use other formula types.
  340. type FormulaOpts struct {
  341. Type *string // Formula type
  342. Ref *string // Shared formula ref
  343. }
  344. // SetCellFormula provides a function to set cell formula by given string and
  345. // worksheet name.
  346. func (f *File) SetCellFormula(sheet, axis, formula string, opts ...FormulaOpts) error {
  347. rwMutex.Lock()
  348. defer rwMutex.Unlock()
  349. xlsx, err := f.workSheetReader(sheet)
  350. if err != nil {
  351. return err
  352. }
  353. cellData, _, _, err := f.prepareCell(xlsx, sheet, axis)
  354. if err != nil {
  355. return err
  356. }
  357. if formula == "" {
  358. cellData.F = nil
  359. f.deleteCalcChain(f.getSheetID(sheet), axis)
  360. return err
  361. }
  362. if cellData.F != nil {
  363. cellData.F.Content = formula
  364. } else {
  365. cellData.F = &xlsxF{Content: formula}
  366. }
  367. for _, o := range opts {
  368. if o.Type != nil {
  369. cellData.F.T = *o.Type
  370. }
  371. if o.Ref != nil {
  372. cellData.F.Ref = *o.Ref
  373. }
  374. }
  375. return err
  376. }
  377. // GetCellHyperLink provides a function to get cell hyperlink by given
  378. // worksheet name and axis. Boolean type value link will be ture if the cell
  379. // has a hyperlink and the target is the address of the hyperlink. Otherwise,
  380. // the value of link will be false and the value of the target will be a blank
  381. // string. For example get hyperlink of Sheet1!H6:
  382. //
  383. // link, target, err := f.GetCellHyperLink("Sheet1", "H6")
  384. //
  385. func (f *File) GetCellHyperLink(sheet, axis string) (bool, string, error) {
  386. // Check for correct cell name
  387. if _, _, err := SplitCellName(axis); err != nil {
  388. return false, "", err
  389. }
  390. xlsx, err := f.workSheetReader(sheet)
  391. if err != nil {
  392. return false, "", err
  393. }
  394. axis, err = f.mergeCellsParser(xlsx, axis)
  395. if err != nil {
  396. return false, "", err
  397. }
  398. if xlsx.Hyperlinks != nil {
  399. for _, link := range xlsx.Hyperlinks.Hyperlink {
  400. if link.Ref == axis {
  401. if link.RID != "" {
  402. return true, f.getSheetRelationshipsTargetByID(sheet, link.RID), err
  403. }
  404. return true, link.Location, err
  405. }
  406. }
  407. }
  408. return false, "", err
  409. }
  410. // SetCellHyperLink provides a function to set cell hyperlink by given
  411. // worksheet name and link URL address. LinkType defines two types of
  412. // hyperlink "External" for web site or "Location" for moving to one of cell
  413. // in this workbook. Maximum limit hyperlinks in a worksheet is 65530. The
  414. // below is example for external link.
  415. //
  416. // err := f.SetCellHyperLink("Sheet1", "A3", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
  417. // // Set underline and font color style for the cell.
  418. // style, err := f.NewStyle(`{"font":{"color":"#1265BE","underline":"single"}}`)
  419. // err = f.SetCellStyle("Sheet1", "A3", "A3", style)
  420. //
  421. // A this is another example for "Location":
  422. //
  423. // err := f.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location")
  424. //
  425. func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) error {
  426. // Check for correct cell name
  427. if _, _, err := SplitCellName(axis); err != nil {
  428. return err
  429. }
  430. xlsx, err := f.workSheetReader(sheet)
  431. if err != nil {
  432. return err
  433. }
  434. axis, err = f.mergeCellsParser(xlsx, axis)
  435. if err != nil {
  436. return err
  437. }
  438. var linkData xlsxHyperlink
  439. if xlsx.Hyperlinks == nil {
  440. xlsx.Hyperlinks = new(xlsxHyperlinks)
  441. }
  442. if len(xlsx.Hyperlinks.Hyperlink) > TotalSheetHyperlinks {
  443. return errors.New("over maximum limit hyperlinks in a worksheet")
  444. }
  445. switch linkType {
  446. case "External":
  447. linkData = xlsxHyperlink{
  448. Ref: axis,
  449. }
  450. sheetPath := f.sheetMap[trimSheetName(sheet)]
  451. sheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(sheetPath, "xl/worksheets/") + ".rels"
  452. rID := f.addRels(sheetRels, SourceRelationshipHyperLink, link, linkType)
  453. linkData.RID = "rId" + strconv.Itoa(rID)
  454. case "Location":
  455. linkData = xlsxHyperlink{
  456. Ref: axis,
  457. Location: link,
  458. }
  459. default:
  460. return fmt.Errorf("invalid link type %q", linkType)
  461. }
  462. xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink, linkData)
  463. return nil
  464. }
  465. // SetCellRichText provides a function to set cell with rich text by given
  466. // worksheet. For example, set rich text on the A1 cell of the worksheet named
  467. // Sheet1:
  468. //
  469. // package main
  470. //
  471. // import (
  472. // "fmt"
  473. //
  474. // "github.com/360EntSecGroup-Skylar/excelize"
  475. // )
  476. //
  477. // func main() {
  478. // f := excelize.NewFile()
  479. // if err := f.SetRowHeight("Sheet1", 1, 35); err != nil {
  480. // fmt.Println(err)
  481. // return
  482. // }
  483. // if err := f.SetColWidth("Sheet1", "A", "A", 44); err != nil {
  484. // fmt.Println(err)
  485. // return
  486. // }
  487. // if err := f.SetCellRichText("Sheet1", "A1", []excelize.RichTextRun{
  488. // {
  489. // Text: "blod",
  490. // Font: &excelize.Font{
  491. // Bold: true,
  492. // Color: "2354e8",
  493. // Family: "Times New Roman",
  494. // },
  495. // },
  496. // {
  497. // Text: " and ",
  498. // Font: &excelize.Font{
  499. // Family: "Times New Roman",
  500. // },
  501. // },
  502. // {
  503. // Text: " italic",
  504. // Font: &excelize.Font{
  505. // Bold: true,
  506. // Color: "e83723",
  507. // Italic: true,
  508. // Family: "Times New Roman",
  509. // },
  510. // },
  511. // {
  512. // Text: "text with color and font-family,",
  513. // Font: &excelize.Font{
  514. // Bold: true,
  515. // Color: "2354e8",
  516. // Family: "Times New Roman",
  517. // },
  518. // },
  519. // {
  520. // Text: "\r\nlarge text with ",
  521. // Font: &excelize.Font{
  522. // Size: 14,
  523. // Color: "ad23e8",
  524. // },
  525. // },
  526. // {
  527. // Text: "strike",
  528. // Font: &excelize.Font{
  529. // Color: "e89923",
  530. // Strike: true,
  531. // },
  532. // },
  533. // {
  534. // Text: " and ",
  535. // Font: &excelize.Font{
  536. // Size: 14,
  537. // Color: "ad23e8",
  538. // },
  539. // },
  540. // {
  541. // Text: "underline.",
  542. // Font: &excelize.Font{
  543. // Color: "23e833",
  544. // Underline: "single",
  545. // },
  546. // },
  547. // }); err != nil {
  548. // fmt.Println(err)
  549. // return
  550. // }
  551. // style, err := f.NewStyle(&excelize.Style{
  552. // Alignment: &excelize.Alignment{
  553. // WrapText: true,
  554. // },
  555. // })
  556. // if err != nil {
  557. // fmt.Println(err)
  558. // return
  559. // }
  560. // if err := f.SetCellStyle("Sheet1", "A1", "A1", style); err != nil {
  561. // fmt.Println(err)
  562. // return
  563. // }
  564. // if err := f.SaveAs("Book1.xlsx"); err != nil {
  565. // fmt.Println(err)
  566. // }
  567. // }
  568. //
  569. func (f *File) SetCellRichText(sheet, cell string, runs []RichTextRun) error {
  570. ws, err := f.workSheetReader(sheet)
  571. if err != nil {
  572. return err
  573. }
  574. cellData, col, _, err := f.prepareCell(ws, sheet, cell)
  575. if err != nil {
  576. return err
  577. }
  578. cellData.S = f.prepareCellStyle(ws, col, cellData.S)
  579. si := xlsxSI{}
  580. sst := f.sharedStringsReader()
  581. textRuns := []xlsxR{}
  582. for _, textRun := range runs {
  583. run := xlsxR{T: &xlsxT{Val: textRun.Text}}
  584. if strings.ContainsAny(textRun.Text, "\r\n ") {
  585. run.T.Space = xml.Attr{Name: xml.Name{Space: NameSpaceXML, Local: "space"}, Value: "preserve"}
  586. }
  587. fnt := textRun.Font
  588. if fnt != nil {
  589. rpr := xlsxRPr{}
  590. if fnt.Bold {
  591. rpr.B = " "
  592. }
  593. if fnt.Italic {
  594. rpr.I = " "
  595. }
  596. if fnt.Strike {
  597. rpr.Strike = " "
  598. }
  599. if fnt.Underline != "" {
  600. rpr.U = &attrValString{Val: &fnt.Underline}
  601. }
  602. if fnt.Family != "" {
  603. rpr.RFont = &attrValString{Val: &fnt.Family}
  604. }
  605. if fnt.Size > 0.0 {
  606. rpr.Sz = &attrValFloat{Val: &fnt.Size}
  607. }
  608. if fnt.Color != "" {
  609. rpr.Color = &xlsxColor{RGB: getPaletteColor(fnt.Color)}
  610. }
  611. run.RPr = &rpr
  612. }
  613. textRuns = append(textRuns, run)
  614. }
  615. si.R = textRuns
  616. sst.SI = append(sst.SI, si)
  617. sst.Count++
  618. sst.UniqueCount++
  619. cellData.T, cellData.V = "s", strconv.Itoa(len(sst.SI)-1)
  620. return err
  621. }
  622. // SetSheetRow writes an array to row by given worksheet name, starting
  623. // coordinate and a pointer to array type 'slice'. For example, writes an
  624. // array to row 6 start with the cell B6 on Sheet1:
  625. //
  626. // err := f.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2})
  627. //
  628. func (f *File) SetSheetRow(sheet, axis string, slice interface{}) error {
  629. col, row, err := CellNameToCoordinates(axis)
  630. if err != nil {
  631. return err
  632. }
  633. // Make sure 'slice' is a Ptr to Slice
  634. v := reflect.ValueOf(slice)
  635. if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice {
  636. return errors.New("pointer to slice expected")
  637. }
  638. v = v.Elem()
  639. for i := 0; i < v.Len(); i++ {
  640. cell, err := CoordinatesToCellName(col+i, row)
  641. // Error should never happens here. But keep checking to early detect regresions
  642. // if it will be introduced in future.
  643. if err != nil {
  644. return err
  645. }
  646. if err := f.SetCellValue(sheet, cell, v.Index(i).Interface()); err != nil {
  647. return err
  648. }
  649. }
  650. return err
  651. }
  652. // getCellInfo does common preparation for all SetCell* methods.
  653. func (f *File) prepareCell(xlsx *xlsxWorksheet, sheet, cell string) (*xlsxC, int, int, error) {
  654. var err error
  655. cell, err = f.mergeCellsParser(xlsx, cell)
  656. if err != nil {
  657. return nil, 0, 0, err
  658. }
  659. col, row, err := CellNameToCoordinates(cell)
  660. if err != nil {
  661. return nil, 0, 0, err
  662. }
  663. prepareSheetXML(xlsx, col, row)
  664. return &xlsx.SheetData.Row[row-1].C[col-1], col, row, err
  665. }
  666. // getCellStringFunc does common value extraction workflow for all GetCell*
  667. // methods. Passed function implements specific part of required logic.
  668. func (f *File) getCellStringFunc(sheet, axis string, fn func(x *xlsxWorksheet, c *xlsxC) (string, bool, error)) (string, error) {
  669. xlsx, err := f.workSheetReader(sheet)
  670. if err != nil {
  671. return "", err
  672. }
  673. axis, err = f.mergeCellsParser(xlsx, axis)
  674. if err != nil {
  675. return "", err
  676. }
  677. _, row, err := CellNameToCoordinates(axis)
  678. if err != nil {
  679. return "", err
  680. }
  681. lastRowNum := 0
  682. if l := len(xlsx.SheetData.Row); l > 0 {
  683. lastRowNum = xlsx.SheetData.Row[l-1].R
  684. }
  685. // keep in mind: row starts from 1
  686. if row > lastRowNum {
  687. return "", nil
  688. }
  689. for rowIdx := range xlsx.SheetData.Row {
  690. rowData := &xlsx.SheetData.Row[rowIdx]
  691. if rowData.R != row {
  692. continue
  693. }
  694. for colIdx := range rowData.C {
  695. colData := &rowData.C[colIdx]
  696. if axis != colData.R {
  697. continue
  698. }
  699. val, ok, err := fn(xlsx, colData)
  700. if err != nil {
  701. return "", err
  702. }
  703. if ok {
  704. return val, nil
  705. }
  706. }
  707. }
  708. return "", nil
  709. }
  710. // formattedValue provides a function to returns a value after formatted. If
  711. // it is possible to apply a format to the cell value, it will do so, if not
  712. // then an error will be returned, along with the raw value of the cell.
  713. func (f *File) formattedValue(s int, v string) string {
  714. if s == 0 {
  715. return v
  716. }
  717. styleSheet := f.stylesReader()
  718. ok := builtInNumFmtFunc[*styleSheet.CellXfs.Xf[s].NumFmtID]
  719. if ok != nil {
  720. return ok(*styleSheet.CellXfs.Xf[s].NumFmtID, v)
  721. }
  722. return v
  723. }
  724. // prepareCellStyle provides a function to prepare style index of cell in
  725. // worksheet by given column index and style index.
  726. func (f *File) prepareCellStyle(xlsx *xlsxWorksheet, col, style int) int {
  727. if xlsx.Cols != nil && style == 0 {
  728. for _, c := range xlsx.Cols.Col {
  729. if c.Min <= col && col <= c.Max {
  730. style = c.Style
  731. }
  732. }
  733. }
  734. return style
  735. }
  736. // mergeCellsParser provides a function to check merged cells in worksheet by
  737. // given axis.
  738. func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) (string, error) {
  739. axis = strings.ToUpper(axis)
  740. if xlsx.MergeCells != nil {
  741. for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
  742. ok, err := f.checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref)
  743. if err != nil {
  744. return axis, err
  745. }
  746. if ok {
  747. axis = strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[0]
  748. }
  749. }
  750. }
  751. return axis, nil
  752. }
  753. // checkCellInArea provides a function to determine if a given coordinate is
  754. // within an area.
  755. func (f *File) checkCellInArea(cell, area string) (bool, error) {
  756. col, row, err := CellNameToCoordinates(cell)
  757. if err != nil {
  758. return false, err
  759. }
  760. rng := strings.Split(area, ":")
  761. if len(rng) != 2 {
  762. return false, err
  763. }
  764. coordinates, err := f.areaRefToCoordinates(area)
  765. if err != nil {
  766. return false, err
  767. }
  768. return cellInRef([]int{col, row}, coordinates), err
  769. }
  770. // cellInRef provides a function to determine if a given range is within an
  771. // range.
  772. func cellInRef(cell, ref []int) bool {
  773. return cell[0] >= ref[0] && cell[0] <= ref[2] && cell[1] >= ref[1] && cell[1] <= ref[3]
  774. }
  775. // isOverlap find if the given two rectangles overlap or not.
  776. func isOverlap(rect1, rect2 []int) bool {
  777. return cellInRef([]int{rect1[0], rect1[1]}, rect2) ||
  778. cellInRef([]int{rect1[2], rect1[1]}, rect2) ||
  779. cellInRef([]int{rect1[0], rect1[3]}, rect2) ||
  780. cellInRef([]int{rect1[2], rect1[3]}, rect2) ||
  781. cellInRef([]int{rect2[0], rect2[1]}, rect1) ||
  782. cellInRef([]int{rect2[2], rect2[1]}, rect1) ||
  783. cellInRef([]int{rect2[0], rect2[3]}, rect1) ||
  784. cellInRef([]int{rect2[2], rect2[3]}, rect1)
  785. }
  786. // getSharedForumula find a cell contains the same formula as another cell,
  787. // the "shared" value can be used for the t attribute and the si attribute can
  788. // be used to refer to the cell containing the formula. Two formulas are
  789. // considered to be the same when their respective representations in
  790. // R1C1-reference notation, are the same.
  791. //
  792. // Note that this function not validate ref tag to check the cell if or not in
  793. // allow area, and always return origin shared formula.
  794. func getSharedForumula(xlsx *xlsxWorksheet, si string) string {
  795. for _, r := range xlsx.SheetData.Row {
  796. for _, c := range r.C {
  797. if c.F != nil && c.F.Ref != "" && c.F.T == STCellFormulaTypeShared && c.F.Si == si {
  798. return c.F.Content
  799. }
  800. }
  801. }
  802. return ""
  803. }