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