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.

653 lines
18KB

  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/xml"
  12. "errors"
  13. "fmt"
  14. "reflect"
  15. "strconv"
  16. "strings"
  17. "time"
  18. )
  19. const (
  20. // STCellFormulaTypeArray defined the formula is an array formula.
  21. STCellFormulaTypeArray = "array"
  22. // STCellFormulaTypeDataTable defined the formula is a data table formula.
  23. STCellFormulaTypeDataTable = "dataTable"
  24. // STCellFormulaTypeNormal defined the formula is a regular cell formula.
  25. STCellFormulaTypeNormal = "normal"
  26. // STCellFormulaTypeShared defined the formula is part of a shared formula.
  27. STCellFormulaTypeShared = "shared"
  28. )
  29. // GetCellValue provides a function to get formatted value from cell by given
  30. // worksheet name and axis in XLSX file. If it is possible to apply a format
  31. // to the cell value, it will do so, if not then an error will be returned,
  32. // along with the raw value of the cell.
  33. func (f *File) GetCellValue(sheet, axis string) (string, error) {
  34. return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
  35. val, err := c.getValueFrom(f, f.sharedStringsReader())
  36. if err != nil {
  37. return val, false, err
  38. }
  39. return val, true, err
  40. })
  41. }
  42. // SetCellValue provides a function to set value of a cell. The following
  43. // shows the supported data types:
  44. //
  45. // int
  46. // int8
  47. // int16
  48. // int32
  49. // int64
  50. // uint
  51. // uint8
  52. // uint16
  53. // uint32
  54. // uint64
  55. // float32
  56. // float64
  57. // string
  58. // []byte
  59. // time.Duration
  60. // time.Time
  61. // bool
  62. // nil
  63. //
  64. // Note that default date format is m/d/yy h:mm of time.Time type value. You can
  65. // set numbers format by SetCellStyle() method.
  66. func (f *File) SetCellValue(sheet, axis string, value interface{}) error {
  67. var err error
  68. switch v := value.(type) {
  69. case int, int8, int16, int32, int64, uint, uint8, uint16, uint32, uint64:
  70. err = f.setCellIntFunc(sheet, axis, v)
  71. case float32:
  72. err = f.SetCellFloat(sheet, axis, float64(v), -1, 32)
  73. case float64:
  74. err = f.SetCellFloat(sheet, axis, v, -1, 64)
  75. case string:
  76. err = f.SetCellStr(sheet, axis, v)
  77. case []byte:
  78. err = f.SetCellStr(sheet, axis, string(v))
  79. case time.Duration:
  80. err = f.SetCellDefault(sheet, axis, strconv.FormatFloat(v.Seconds()/86400.0, 'f', -1, 32))
  81. if err != nil {
  82. return err
  83. }
  84. err = f.setDefaultTimeStyle(sheet, axis, 21)
  85. case time.Time:
  86. err = f.setCellTimeFunc(sheet, axis, v)
  87. case bool:
  88. err = f.SetCellBool(sheet, axis, v)
  89. case nil:
  90. err = f.SetCellStr(sheet, axis, "")
  91. default:
  92. err = f.SetCellStr(sheet, axis, fmt.Sprint(value))
  93. }
  94. return err
  95. }
  96. // setCellIntFunc is a wrapper of SetCellInt.
  97. func (f *File) setCellIntFunc(sheet, axis string, value interface{}) error {
  98. var err error
  99. switch v := value.(type) {
  100. case int:
  101. err = f.SetCellInt(sheet, axis, v)
  102. case int8:
  103. err = f.SetCellInt(sheet, axis, int(v))
  104. case int16:
  105. err = f.SetCellInt(sheet, axis, int(v))
  106. case int32:
  107. err = f.SetCellInt(sheet, axis, int(v))
  108. case int64:
  109. err = f.SetCellInt(sheet, axis, int(v))
  110. case uint:
  111. err = f.SetCellInt(sheet, axis, int(v))
  112. case uint8:
  113. err = f.SetCellInt(sheet, axis, int(v))
  114. case uint16:
  115. err = f.SetCellInt(sheet, axis, int(v))
  116. case uint32:
  117. err = f.SetCellInt(sheet, axis, int(v))
  118. case uint64:
  119. err = f.SetCellInt(sheet, axis, int(v))
  120. }
  121. return err
  122. }
  123. // setCellTimeFunc provides a method to process time type of value for
  124. // SetCellValue.
  125. func (f *File) setCellTimeFunc(sheet, axis string, value time.Time) error {
  126. excelTime, err := timeToExcelTime(value)
  127. if err != nil {
  128. return err
  129. }
  130. if excelTime > 0 {
  131. err = f.SetCellDefault(sheet, axis, strconv.FormatFloat(excelTime, 'f', -1, 64))
  132. if err != nil {
  133. return err
  134. }
  135. err = f.setDefaultTimeStyle(sheet, axis, 22)
  136. if err != nil {
  137. return err
  138. }
  139. } else {
  140. err = f.SetCellStr(sheet, axis, value.Format(time.RFC3339Nano))
  141. if err != nil {
  142. return err
  143. }
  144. }
  145. return err
  146. }
  147. // SetCellInt provides a function to set int type value of a cell by given
  148. // worksheet name, cell coordinates and cell value.
  149. func (f *File) SetCellInt(sheet, axis string, value int) error {
  150. xlsx, err := f.workSheetReader(sheet)
  151. if err != nil {
  152. return err
  153. }
  154. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  155. if err != nil {
  156. return err
  157. }
  158. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  159. cellData.T = ""
  160. cellData.V = strconv.Itoa(value)
  161. return err
  162. }
  163. // SetCellBool provides a function to set bool type value of a cell by given
  164. // worksheet name, cell name and cell value.
  165. func (f *File) SetCellBool(sheet, axis string, value bool) error {
  166. xlsx, err := f.workSheetReader(sheet)
  167. if err != nil {
  168. return err
  169. }
  170. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  171. if err != nil {
  172. return err
  173. }
  174. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  175. cellData.T = "b"
  176. if value {
  177. cellData.V = "1"
  178. } else {
  179. cellData.V = "0"
  180. }
  181. return err
  182. }
  183. // SetCellFloat sets a floating point value into a cell. The prec parameter
  184. // specifies how many places after the decimal will be shown while -1 is a
  185. // special value that will use as many decimal places as necessary to
  186. // represent the number. bitSize is 32 or 64 depending on if a float32 or
  187. // float64 was originally used for the value. For Example:
  188. //
  189. // var x float32 = 1.325
  190. // f.SetCellFloat("Sheet1", "A1", float64(x), 2, 32)
  191. //
  192. func (f *File) SetCellFloat(sheet, axis string, value float64, prec, bitSize int) error {
  193. xlsx, err := f.workSheetReader(sheet)
  194. if err != nil {
  195. return err
  196. }
  197. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  198. if err != nil {
  199. return err
  200. }
  201. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  202. cellData.T = ""
  203. cellData.V = strconv.FormatFloat(value, 'f', prec, bitSize)
  204. return err
  205. }
  206. // SetCellStr provides a function to set string type value of a cell. Total
  207. // number of characters that a cell can contain 32767 characters.
  208. func (f *File) SetCellStr(sheet, axis, value string) error {
  209. xlsx, err := f.workSheetReader(sheet)
  210. if err != nil {
  211. return err
  212. }
  213. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  214. if err != nil {
  215. return err
  216. }
  217. if len(value) > 32767 {
  218. value = value[0:32767]
  219. }
  220. // Leading and ending space(s) character detection.
  221. if len(value) > 0 && (value[0] == 32 || value[len(value)-1] == 32) {
  222. cellData.XMLSpace = xml.Attr{
  223. Name: xml.Name{Space: NameSpaceXML, Local: "space"},
  224. Value: "preserve",
  225. }
  226. }
  227. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  228. cellData.T = "str"
  229. cellData.V = value
  230. return err
  231. }
  232. // SetCellDefault provides a function to set string type value of a cell as
  233. // default format without escaping the cell.
  234. func (f *File) SetCellDefault(sheet, axis, value string) error {
  235. xlsx, err := f.workSheetReader(sheet)
  236. if err != nil {
  237. return err
  238. }
  239. cellData, col, _, err := f.prepareCell(xlsx, sheet, axis)
  240. if err != nil {
  241. return err
  242. }
  243. cellData.S = f.prepareCellStyle(xlsx, col, cellData.S)
  244. cellData.T = ""
  245. cellData.V = value
  246. return err
  247. }
  248. // GetCellFormula provides a function to get formula from cell by given
  249. // worksheet name and axis in XLSX file.
  250. func (f *File) GetCellFormula(sheet, axis string) (string, error) {
  251. return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
  252. if c.F == nil {
  253. return "", false, nil
  254. }
  255. if c.F.T == STCellFormulaTypeShared {
  256. return getSharedForumula(x, c.F.Si), true, nil
  257. }
  258. return c.F.Content, true, nil
  259. })
  260. }
  261. // FormulaOpts can be passed to SetCellFormula to use other formula types.
  262. type FormulaOpts struct {
  263. Type *string // Formula type
  264. Ref *string // Shared formula ref
  265. }
  266. // SetCellFormula provides a function to set cell formula by given string and
  267. // worksheet name.
  268. func (f *File) SetCellFormula(sheet, axis, formula string, opts ...FormulaOpts) error {
  269. xlsx, err := f.workSheetReader(sheet)
  270. if err != nil {
  271. return err
  272. }
  273. cellData, _, _, err := f.prepareCell(xlsx, sheet, axis)
  274. if err != nil {
  275. return err
  276. }
  277. if formula == "" {
  278. cellData.F = nil
  279. f.deleteCalcChain(f.GetSheetIndex(sheet), axis)
  280. return err
  281. }
  282. if cellData.F != nil {
  283. cellData.F.Content = formula
  284. } else {
  285. cellData.F = &xlsxF{Content: formula}
  286. }
  287. for _, o := range opts {
  288. if o.Type != nil {
  289. cellData.F.T = *o.Type
  290. }
  291. if o.Ref != nil {
  292. cellData.F.Ref = *o.Ref
  293. }
  294. }
  295. return err
  296. }
  297. // GetCellHyperLink provides a function to get cell hyperlink by given
  298. // worksheet name and axis. Boolean type value link will be ture if the cell
  299. // has a hyperlink and the target is the address of the hyperlink. Otherwise,
  300. // the value of link will be false and the value of the target will be a blank
  301. // string. For example get hyperlink of Sheet1!H6:
  302. //
  303. // link, target, err := f.GetCellHyperLink("Sheet1", "H6")
  304. //
  305. func (f *File) GetCellHyperLink(sheet, axis string) (bool, string, error) {
  306. // Check for correct cell name
  307. if _, _, err := SplitCellName(axis); err != nil {
  308. return false, "", err
  309. }
  310. xlsx, err := f.workSheetReader(sheet)
  311. if err != nil {
  312. return false, "", err
  313. }
  314. axis, err = f.mergeCellsParser(xlsx, axis)
  315. if err != nil {
  316. return false, "", err
  317. }
  318. if xlsx.Hyperlinks != nil {
  319. for _, link := range xlsx.Hyperlinks.Hyperlink {
  320. if link.Ref == axis {
  321. if link.RID != "" {
  322. return true, f.getSheetRelationshipsTargetByID(sheet, link.RID), err
  323. }
  324. return true, link.Location, err
  325. }
  326. }
  327. }
  328. return false, "", err
  329. }
  330. // SetCellHyperLink provides a function to set cell hyperlink by given
  331. // worksheet name and link URL address. LinkType defines two types of
  332. // hyperlink "External" for web site or "Location" for moving to one of cell
  333. // in this workbook. Maximum limit hyperlinks in a worksheet is 65530. The
  334. // below is example for external link.
  335. //
  336. // err := f.SetCellHyperLink("Sheet1", "A3", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
  337. // // Set underline and font color style for the cell.
  338. // style, err := f.NewStyle(`{"font":{"color":"#1265BE","underline":"single"}}`)
  339. // err = f.SetCellStyle("Sheet1", "A3", "A3", style)
  340. //
  341. // A this is another example for "Location":
  342. //
  343. // err := f.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location")
  344. //
  345. func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) error {
  346. // Check for correct cell name
  347. if _, _, err := SplitCellName(axis); err != nil {
  348. return err
  349. }
  350. xlsx, err := f.workSheetReader(sheet)
  351. if err != nil {
  352. return err
  353. }
  354. axis, err = f.mergeCellsParser(xlsx, axis)
  355. if err != nil {
  356. return err
  357. }
  358. var linkData xlsxHyperlink
  359. if xlsx.Hyperlinks == nil {
  360. xlsx.Hyperlinks = new(xlsxHyperlinks)
  361. }
  362. if len(xlsx.Hyperlinks.Hyperlink) > 65529 {
  363. return errors.New("over maximum limit hyperlinks in a worksheet")
  364. }
  365. switch linkType {
  366. case "External":
  367. linkData = xlsxHyperlink{
  368. Ref: axis,
  369. }
  370. sheetPath, _ := f.sheetMap[trimSheetName(sheet)]
  371. sheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(sheetPath, "xl/worksheets/") + ".rels"
  372. rID := f.addRels(sheetRels, SourceRelationshipHyperLink, link, linkType)
  373. linkData.RID = "rId" + strconv.Itoa(rID)
  374. case "Location":
  375. linkData = xlsxHyperlink{
  376. Ref: axis,
  377. Location: link,
  378. }
  379. default:
  380. return fmt.Errorf("invalid link type %q", linkType)
  381. }
  382. xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink, linkData)
  383. return nil
  384. }
  385. // MergeCell provides a function to merge cells by given coordinate area and
  386. // sheet name. For example create a merged cell of D3:E9 on Sheet1:
  387. //
  388. // err := f.MergeCell("Sheet1", "D3", "E9")
  389. //
  390. // If you create a merged cell that overlaps with another existing merged cell,
  391. // those merged cells that already exist will be removed.
  392. func (f *File) MergeCell(sheet, hcell, vcell string) error {
  393. coordinates, err := f.areaRefToCoordinates(hcell + ":" + vcell)
  394. if err != nil {
  395. return err
  396. }
  397. x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
  398. if x1 == x2 && y1 == y2 {
  399. return err
  400. }
  401. // Correct the coordinate area, such correct C1:B3 to B1:C3.
  402. if x2 < x1 {
  403. x1, x2 = x2, x1
  404. }
  405. if y2 < y1 {
  406. y1, y2 = y2, y1
  407. }
  408. hcell, _ = CoordinatesToCellName(x1, y1)
  409. vcell, _ = CoordinatesToCellName(x2, y2)
  410. xlsx, err := f.workSheetReader(sheet)
  411. if err != nil {
  412. return err
  413. }
  414. if xlsx.MergeCells != nil {
  415. ref := hcell + ":" + vcell
  416. // Delete the merged cells of the overlapping area.
  417. for _, cellData := range xlsx.MergeCells.Cells {
  418. cc := strings.Split(cellData.Ref, ":")
  419. if len(cc) != 2 {
  420. return fmt.Errorf("invalid area %q", cellData.Ref)
  421. }
  422. c1, _ := checkCellInArea(hcell, cellData.Ref)
  423. c2, _ := checkCellInArea(vcell, cellData.Ref)
  424. c3, _ := checkCellInArea(cc[0], ref)
  425. c4, _ := checkCellInArea(cc[1], ref)
  426. if !(!c1 && !c2 && !c3 && !c4) {
  427. return nil
  428. }
  429. }
  430. xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells, &xlsxMergeCell{Ref: ref})
  431. } else {
  432. xlsx.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: hcell + ":" + vcell}}}
  433. }
  434. return err
  435. }
  436. // SetSheetRow writes an array to row by given worksheet name, starting
  437. // coordinate and a pointer to array type 'slice'. For example, writes an
  438. // array to row 6 start with the cell B6 on Sheet1:
  439. //
  440. // err := f.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2})
  441. //
  442. func (f *File) SetSheetRow(sheet, axis string, slice interface{}) error {
  443. col, row, err := CellNameToCoordinates(axis)
  444. if err != nil {
  445. return err
  446. }
  447. // Make sure 'slice' is a Ptr to Slice
  448. v := reflect.ValueOf(slice)
  449. if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice {
  450. return errors.New("pointer to slice expected")
  451. }
  452. v = v.Elem()
  453. for i := 0; i < v.Len(); i++ {
  454. cell, err := CoordinatesToCellName(col+i, row)
  455. // Error should never happens here. But keep checking to early detect regresions
  456. // if it will be introduced in future.
  457. if err != nil {
  458. return err
  459. }
  460. if err := f.SetCellValue(sheet, cell, v.Index(i).Interface()); err != nil {
  461. return err
  462. }
  463. }
  464. return err
  465. }
  466. // getCellInfo does common preparation for all SetCell* methods.
  467. func (f *File) prepareCell(xlsx *xlsxWorksheet, sheet, cell string) (*xlsxC, int, int, error) {
  468. var err error
  469. cell, err = f.mergeCellsParser(xlsx, cell)
  470. if err != nil {
  471. return nil, 0, 0, err
  472. }
  473. col, row, err := CellNameToCoordinates(cell)
  474. if err != nil {
  475. return nil, 0, 0, err
  476. }
  477. prepareSheetXML(xlsx, col, row)
  478. return &xlsx.SheetData.Row[row-1].C[col-1], col, row, err
  479. }
  480. // getCellStringFunc does common value extraction workflow for all GetCell*
  481. // methods. Passed function implements specific part of required logic.
  482. func (f *File) getCellStringFunc(sheet, axis string, fn func(x *xlsxWorksheet, c *xlsxC) (string, bool, error)) (string, error) {
  483. xlsx, err := f.workSheetReader(sheet)
  484. if err != nil {
  485. return "", err
  486. }
  487. axis, err = f.mergeCellsParser(xlsx, axis)
  488. if err != nil {
  489. return "", err
  490. }
  491. _, row, err := CellNameToCoordinates(axis)
  492. if err != nil {
  493. return "", err
  494. }
  495. lastRowNum := 0
  496. if l := len(xlsx.SheetData.Row); l > 0 {
  497. lastRowNum = xlsx.SheetData.Row[l-1].R
  498. }
  499. // keep in mind: row starts from 1
  500. if row > lastRowNum {
  501. return "", nil
  502. }
  503. for rowIdx := range xlsx.SheetData.Row {
  504. rowData := &xlsx.SheetData.Row[rowIdx]
  505. if rowData.R != row {
  506. continue
  507. }
  508. for colIdx := range rowData.C {
  509. colData := &rowData.C[colIdx]
  510. if axis != colData.R {
  511. continue
  512. }
  513. val, ok, err := fn(xlsx, colData)
  514. if err != nil {
  515. return "", err
  516. }
  517. if ok {
  518. return val, nil
  519. }
  520. }
  521. }
  522. return "", nil
  523. }
  524. // formattedValue provides a function to returns a value after formatted. If
  525. // it is possible to apply a format to the cell value, it will do so, if not
  526. // then an error will be returned, along with the raw value of the cell.
  527. func (f *File) formattedValue(s int, v string) string {
  528. if s == 0 {
  529. return v
  530. }
  531. styleSheet := f.stylesReader()
  532. ok := builtInNumFmtFunc[styleSheet.CellXfs.Xf[s].NumFmtID]
  533. if ok != nil {
  534. return ok(styleSheet.CellXfs.Xf[s].NumFmtID, v)
  535. }
  536. return v
  537. }
  538. // prepareCellStyle provides a function to prepare style index of cell in
  539. // worksheet by given column index and style index.
  540. func (f *File) prepareCellStyle(xlsx *xlsxWorksheet, col, style int) int {
  541. if xlsx.Cols != nil && style == 0 {
  542. for _, c := range xlsx.Cols.Col {
  543. if c.Min <= col && col <= c.Max {
  544. style = c.Style
  545. }
  546. }
  547. }
  548. return style
  549. }
  550. // mergeCellsParser provides a function to check merged cells in worksheet by
  551. // given axis.
  552. func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) (string, error) {
  553. axis = strings.ToUpper(axis)
  554. if xlsx.MergeCells != nil {
  555. for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
  556. ok, err := checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref)
  557. if err != nil {
  558. return axis, err
  559. }
  560. if ok {
  561. axis = strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[0]
  562. }
  563. }
  564. }
  565. return axis, nil
  566. }
  567. // checkCellInArea provides a function to determine if a given coordinate is
  568. // within an area.
  569. func checkCellInArea(cell, area string) (bool, error) {
  570. col, row, err := CellNameToCoordinates(cell)
  571. if err != nil {
  572. return false, err
  573. }
  574. rng := strings.Split(area, ":")
  575. if len(rng) != 2 {
  576. return false, err
  577. }
  578. firstCol, firstRow, _ := CellNameToCoordinates(rng[0])
  579. lastCol, lastRow, _ := CellNameToCoordinates(rng[1])
  580. return col >= firstCol && col <= lastCol && row >= firstRow && row <= lastRow, err
  581. }
  582. // getSharedForumula find a cell contains the same formula as another cell,
  583. // the "shared" value can be used for the t attribute and the si attribute can
  584. // be used to refer to the cell containing the formula. Two formulas are
  585. // considered to be the same when their respective representations in
  586. // R1C1-reference notation, are the same.
  587. //
  588. // Note that this function not validate ref tag to check the cell if or not in
  589. // allow area, and always return origin shared formula.
  590. func getSharedForumula(xlsx *xlsxWorksheet, si string) string {
  591. for _, r := range xlsx.SheetData.Row {
  592. for _, c := range r.C {
  593. if c.F != nil && c.F.Ref != "" && c.F.T == STCellFormulaTypeShared && c.F.Si == si {
  594. return c.F.Content
  595. }
  596. }
  597. }
  598. return ""
  599. }