cell.go 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418
  1. package xlsx
  2. import (
  3. "fmt"
  4. "math"
  5. "strconv"
  6. "time"
  7. )
  8. const (
  9. maxNonScientificNumber = 1e11
  10. minNonScientificNumber = 1e-9
  11. )
  12. // CellType is an int type for storing metadata about the data type in the cell.
  13. type CellType int
  14. // These are the cell types from the ST_CellType spec
  15. const (
  16. CellTypeString CellType = iota
  17. // CellTypeStringFormula is a specific format for formulas that return string values. Formulas that return numbers
  18. // and booleans are stored as those types.
  19. CellTypeStringFormula
  20. CellTypeNumeric
  21. CellTypeBool
  22. // CellTypeInline is not respected on save, all inline string cells will be saved as SharedStrings
  23. // when saving to an XLSX file. This the same behavior as that found in Excel.
  24. CellTypeInline
  25. CellTypeError
  26. // d (Date): Cell contains a date in the ISO 8601 format.
  27. // That is the only mention of this format in the XLSX spec.
  28. // Date seems to be unused by the current version of Excel, it stores dates as Numeric cells with a date format string.
  29. // For now these cells will have their value output directly. It is unclear if the value is supposed to be parsed
  30. // into a number and then formatted using the formatting or not.
  31. CellTypeDate
  32. )
  33. func (ct CellType) Ptr() *CellType {
  34. return &ct
  35. }
  36. func (ct *CellType) fallbackTo(cellData string, fallback CellType) CellType {
  37. if ct != nil {
  38. switch *ct {
  39. case CellTypeNumeric:
  40. if _, err := strconv.ParseFloat(cellData, 64); err == nil {
  41. return *ct
  42. }
  43. default:
  44. }
  45. }
  46. return fallback
  47. }
  48. // Cell is a high level structure intended to provide user access to
  49. // the contents of Cell within an xlsx.Row.
  50. type Cell struct {
  51. Row *Row
  52. Value string
  53. formula string
  54. style *Style
  55. NumFmt string
  56. parsedNumFmt *parsedNumberFormat
  57. date1904 bool
  58. Hidden bool
  59. HMerge int
  60. VMerge int
  61. cellType CellType
  62. DataValidation *xlsxCellDataValidation
  63. }
  64. // CellInterface defines the public API of the Cell.
  65. type CellInterface interface {
  66. String() string
  67. FormattedValue() string
  68. }
  69. // NewCell creates a cell and adds it to a row.
  70. func NewCell(r *Row) *Cell {
  71. return &Cell{Row: r}
  72. }
  73. // Merge with other cells, horizontally and/or vertically.
  74. func (c *Cell) Merge(hcells, vcells int) {
  75. c.HMerge = hcells
  76. c.VMerge = vcells
  77. }
  78. // Type returns the CellType of a cell. See CellType constants for more details.
  79. func (c *Cell) Type() CellType {
  80. return c.cellType
  81. }
  82. // SetString sets the value of a cell to a string.
  83. func (c *Cell) SetString(s string) {
  84. c.Value = s
  85. c.formula = ""
  86. c.cellType = CellTypeString
  87. }
  88. // String returns the value of a Cell as a string. If you'd like to
  89. // see errors returned from formatting then please use
  90. // Cell.FormattedValue() instead.
  91. func (c *Cell) String() string {
  92. // To preserve the String() interface we'll throw away errors.
  93. // Not that using FormattedValue is therefore strongly
  94. // preferred.
  95. value, _ := c.FormattedValue()
  96. return value
  97. }
  98. // SetFloat sets the value of a cell to a float.
  99. func (c *Cell) SetFloat(n float64) {
  100. c.SetValue(n)
  101. }
  102. // IsTime returns true if the cell stores a time value.
  103. func (c *Cell) IsTime() bool {
  104. c.getNumberFormat()
  105. return c.parsedNumFmt.isTimeFormat
  106. }
  107. //GetTime returns the value of a Cell as a time.Time
  108. func (c *Cell) GetTime(date1904 bool) (t time.Time, err error) {
  109. f, err := c.Float()
  110. if err != nil {
  111. return t, err
  112. }
  113. return TimeFromExcelTime(f, date1904), nil
  114. }
  115. /*
  116. The following are samples of format samples.
  117. * "0.00e+00"
  118. * "0", "#,##0"
  119. * "0.00", "#,##0.00", "@"
  120. * "#,##0 ;(#,##0)", "#,##0 ;[red](#,##0)"
  121. * "#,##0.00;(#,##0.00)", "#,##0.00;[red](#,##0.00)"
  122. * "0%", "0.00%"
  123. * "0.00e+00", "##0.0e+0"
  124. */
  125. // SetFloatWithFormat sets the value of a cell to a float and applies
  126. // formatting to the cell.
  127. func (c *Cell) SetFloatWithFormat(n float64, format string) {
  128. c.SetValue(n)
  129. c.NumFmt = format
  130. c.formula = ""
  131. }
  132. // SetCellFormat set cell value format
  133. func (c *Cell) SetFormat(format string) {
  134. c.NumFmt = format
  135. }
  136. // DateTimeOptions are additional options for exporting times
  137. type DateTimeOptions struct {
  138. // Location allows calculating times in other timezones/locations
  139. Location *time.Location
  140. // ExcelTimeFormat is the string you want excel to use to format the datetime
  141. ExcelTimeFormat string
  142. }
  143. var (
  144. DefaultDateFormat = builtInNumFmt[14]
  145. DefaultDateTimeFormat = builtInNumFmt[22]
  146. DefaultDateOptions = DateTimeOptions{
  147. Location: timeLocationUTC,
  148. ExcelTimeFormat: DefaultDateFormat,
  149. }
  150. DefaultDateTimeOptions = DateTimeOptions{
  151. Location: timeLocationUTC,
  152. ExcelTimeFormat: DefaultDateTimeFormat,
  153. }
  154. )
  155. // SetDate sets the value of a cell to a float.
  156. func (c *Cell) SetDate(t time.Time) {
  157. c.SetDateWithOptions(t, DefaultDateOptions)
  158. }
  159. func (c *Cell) SetDateTime(t time.Time) {
  160. c.SetDateWithOptions(t, DefaultDateTimeOptions)
  161. }
  162. // SetDateWithOptions allows for more granular control when exporting dates and times
  163. func (c *Cell) SetDateWithOptions(t time.Time, options DateTimeOptions) {
  164. _, offset := t.In(options.Location).Zone()
  165. t = time.Unix(t.Unix()+int64(offset), 0)
  166. c.SetDateTimeWithFormat(TimeToExcelTime(t.In(timeLocationUTC), c.date1904), options.ExcelTimeFormat)
  167. }
  168. func (c *Cell) SetDateTimeWithFormat(n float64, format string) {
  169. c.Value = strconv.FormatFloat(n, 'f', -1, 64)
  170. c.NumFmt = format
  171. c.formula = ""
  172. c.cellType = CellTypeNumeric
  173. }
  174. // Float returns the value of cell as a number.
  175. func (c *Cell) Float() (float64, error) {
  176. f, err := strconv.ParseFloat(c.Value, 64)
  177. if err != nil {
  178. return math.NaN(), err
  179. }
  180. return f, nil
  181. }
  182. // SetInt64 sets a cell's value to a 64-bit integer.
  183. func (c *Cell) SetInt64(n int64) {
  184. c.SetValue(n)
  185. }
  186. // Int64 returns the value of cell as 64-bit integer.
  187. func (c *Cell) Int64() (int64, error) {
  188. f, err := strconv.ParseInt(c.Value, 10, 64)
  189. if err != nil {
  190. return -1, err
  191. }
  192. return f, nil
  193. }
  194. // GeneralNumeric returns the value of the cell as a string. It is formatted very closely to the the XLSX spec for how
  195. // to display values when the storage type is Number and the format type is General. It is not 100% identical to the
  196. // spec but is as close as you can get using the built in Go formatting tools.
  197. func (c *Cell) GeneralNumeric() (string, error) {
  198. return generalNumericScientific(c.Value, true)
  199. }
  200. // GeneralNumericWithoutScientific returns numbers that are always formatted as numbers, but it does not follow
  201. // the rules for when XLSX should switch to scientific notation, since sometimes scientific notation is not desired,
  202. // even if that is how the document is supposed to be formatted.
  203. func (c *Cell) GeneralNumericWithoutScientific() (string, error) {
  204. return generalNumericScientific(c.Value, false)
  205. }
  206. // SetInt sets a cell's value to an integer.
  207. func (c *Cell) SetInt(n int) {
  208. c.SetValue(n)
  209. }
  210. // SetInt sets a cell's value to an integer.
  211. func (c *Cell) SetValue(n interface{}) {
  212. switch t := n.(type) {
  213. case time.Time:
  214. c.SetDateTime(t)
  215. return
  216. case int, int8, int16, int32, int64:
  217. c.setNumeric(fmt.Sprintf("%d", n))
  218. case float64:
  219. // When formatting floats, do not use fmt.Sprintf("%v", n), this will cause numbers below 1e-4 to be printed in
  220. // scientific notation. Scientific notation is not a valid way to store numbers in XML.
  221. // Also not not use fmt.Sprintf("%f", n), this will cause numbers to be stored as X.XXXXXX. Which means that
  222. // numbers will lose precision and numbers with fewer significant digits such as 0 will be stored as 0.000000
  223. // which causes tests to fail.
  224. c.setNumeric(strconv.FormatFloat(t, 'f', -1, 64))
  225. case float32:
  226. c.setNumeric(strconv.FormatFloat(float64(t), 'f', -1, 32))
  227. case string:
  228. c.SetString(t)
  229. case []byte:
  230. c.SetString(string(t))
  231. case nil:
  232. c.SetString("")
  233. default:
  234. c.SetString(fmt.Sprintf("%v", n))
  235. }
  236. }
  237. // setNumeric sets a cell's value to a number
  238. func (c *Cell) setNumeric(s string) {
  239. c.Value = s
  240. c.NumFmt = builtInNumFmt[builtInNumFmtIndex_GENERAL]
  241. c.formula = ""
  242. c.cellType = CellTypeNumeric
  243. }
  244. // Int returns the value of cell as integer.
  245. // Has max 53 bits of precision
  246. // See: float64(int64(math.MaxInt))
  247. func (c *Cell) Int() (int, error) {
  248. f, err := strconv.ParseFloat(c.Value, 64)
  249. if err != nil {
  250. return -1, err
  251. }
  252. return int(f), nil
  253. }
  254. // SetBool sets a cell's value to a boolean.
  255. func (c *Cell) SetBool(b bool) {
  256. if b {
  257. c.Value = "1"
  258. } else {
  259. c.Value = "0"
  260. }
  261. c.cellType = CellTypeBool
  262. }
  263. // Bool returns a boolean from a cell's value.
  264. // TODO: Determine if the current return value is
  265. // appropriate for types other than CellTypeBool.
  266. func (c *Cell) Bool() bool {
  267. // If bool, just return the value.
  268. if c.cellType == CellTypeBool {
  269. return c.Value == "1"
  270. }
  271. // If numeric, base it on a non-zero.
  272. if c.cellType == CellTypeNumeric {
  273. return c.Value != "0"
  274. }
  275. // Return whether there's an empty string.
  276. return c.Value != ""
  277. }
  278. // SetFormula sets the format string for a cell.
  279. func (c *Cell) SetFormula(formula string) {
  280. c.formula = formula
  281. c.cellType = CellTypeNumeric
  282. }
  283. func (c *Cell) SetStringFormula(formula string) {
  284. c.formula = formula
  285. c.cellType = CellTypeStringFormula
  286. }
  287. // Formula returns the formula string for the cell.
  288. func (c *Cell) Formula() string {
  289. return c.formula
  290. }
  291. // GetStyle returns the Style associated with a Cell
  292. func (c *Cell) GetStyle() *Style {
  293. if c.style == nil {
  294. c.style = NewStyle()
  295. }
  296. return c.style
  297. }
  298. // SetStyle sets the style of a cell.
  299. func (c *Cell) SetStyle(style *Style) {
  300. c.style = style
  301. }
  302. // GetNumberFormat returns the number format string for a cell.
  303. func (c *Cell) GetNumberFormat() string {
  304. return c.NumFmt
  305. }
  306. func (c *Cell) formatToFloat(format string) (string, error) {
  307. f, err := strconv.ParseFloat(c.Value, 64)
  308. if err != nil {
  309. return c.Value, err
  310. }
  311. return fmt.Sprintf(format, f), nil
  312. }
  313. func (c *Cell) formatToInt(format string) (string, error) {
  314. f, err := strconv.ParseFloat(c.Value, 64)
  315. if err != nil {
  316. return c.Value, err
  317. }
  318. return fmt.Sprintf(format, int(f)), nil
  319. }
  320. // getNumberFormat will update the parsedNumFmt struct if it has become out of date, since a cell's NumFmt string is a
  321. // public field that could be edited by clients.
  322. func (c *Cell) getNumberFormat() *parsedNumberFormat {
  323. if c.parsedNumFmt == nil || c.parsedNumFmt.numFmt != c.NumFmt {
  324. c.parsedNumFmt = parseFullNumberFormatString(c.NumFmt)
  325. }
  326. return c.parsedNumFmt
  327. }
  328. // FormattedValue returns a value, and possibly an error condition
  329. // from a Cell. If it is possible to apply a format to the cell
  330. // value, it will do so, if not then an error will be returned, along
  331. // with the raw value of the Cell.
  332. func (c *Cell) FormattedValue() (string, error) {
  333. fullFormat := c.getNumberFormat()
  334. returnVal, err := fullFormat.FormatValue(c)
  335. if fullFormat.parseEncounteredError != nil {
  336. return returnVal, *fullFormat.parseEncounteredError
  337. }
  338. return returnVal, err
  339. }
  340. // SetDataValidation set data validation
  341. func (c *Cell) SetDataValidation(dd *xlsxCellDataValidation) {
  342. c.DataValidation = dd
  343. }
  344. // CellMetadata represents anything attributable to a cell
  345. // except for the cell data itself. For example, it is used
  346. // in StreamFileBuilder.AddSheetWithDefaultColumnMetadata to
  347. // associate default attributes for cells in a particular column
  348. type CellMetadata struct {
  349. cellType CellType
  350. streamStyle StreamStyle
  351. }
  352. var (
  353. DefaultStringCellMetadata CellMetadata
  354. DefaultNumericCellMetadata CellMetadata
  355. DefaultDecimalCellMetadata CellMetadata
  356. DefaultIntegerCellMetadata CellMetadata
  357. DefaultDateCellMetadata CellMetadata
  358. )
  359. func MakeCellMetadata(cellType CellType, streamStyle StreamStyle) CellMetadata {
  360. return CellMetadata{cellType, streamStyle}
  361. }
  362. func (cm CellMetadata) Ptr() *CellMetadata {
  363. return &cm
  364. }