cell_test.go

   1 package excelize
   2
   3 import (
   4         "fmt"
   5         _ "image/jpeg"
   6         "math"
   7         "os"
   8         "path/filepath"
   9         "reflect"
  10         "strconv"
  11         "strings"
  12         "sync"
  13         "testing"
  14         "time"
  15
  16         "github.com/stretchr/testify/assert"
  17 )
  18
  19 func TestConcurrency(t *testing.T) {
  20         f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
  21         assert.NoError(t, err)
  22         wg := new(sync.WaitGroup)
  23         for i := 1; i <= 5; i++ {
  24                 wg.Add(1)
  25                 go func(val int, t *testing.T) {
  26                         // Concurrency set cell value
  27                         assert.NoError(t, f.SetCellValue("Sheet1", fmt.Sprintf("A%d", val), val))
  28                         assert.NoError(t, f.SetCellValue("Sheet1", fmt.Sprintf("B%d", val), strconv.Itoa(val)))
  29                         // Concurrency get cell value
  30                         _, err := f.GetCellValue("Sheet1", fmt.Sprintf("A%d", val))
  31                         assert.NoError(t, err)
  32                         // Concurrency set rows
  33                         assert.NoError(t, f.SetSheetRow("Sheet1", "B6", &[]interface{}{
  34                                 " Hello",
  35                                 []byte("World"), 42, int8(1<<8/2 - 1), int16(1<<16/2 - 1), int32(1<<32/2 - 1),
  36                                 int64(1<<32/2 - 1), float32(42.65418), -42.65418, float32(42), float64(42),
  37                                 uint(1<<32 - 1), uint8(1<<8 - 1), uint16(1<<16 - 1), uint32(1<<32 - 1),
  38                                 uint64(1<<32 - 1), true, complex64(5 + 10i),
  39                         }))
  40                         // Concurrency create style
  41                         style, err := f.NewStyle(&Style{Font: &Font{Color: "1265BE", Underline: "single"}})
  42                         assert.NoError(t, err)
  43                         // Concurrency set cell style
  44                         assert.NoError(t, f.SetCellStyle("Sheet1", "A3", "A3", style))
  45                         // Concurrency add picture
  46                         assert.NoError(t, f.AddPicture("Sheet1", "F21", filepath.Join("test", "images", "excel.jpg"),
  47                                 &GraphicOptions{
  48                                         OffsetX:       10,
  49                                         OffsetY:       10,
  50                                         Hyperlink:     "https://github.com/xuri/excelize",
  51                                         HyperlinkType: "External",
  52                                         Positioning:   "oneCell",
  53                                 },
  54                         ))
  55                         // Concurrency get cell picture
  56                         pics, err := f.GetPictures("Sheet1", "A1")
  57                         assert.Len(t, pics, 0)
  58                         assert.NoError(t, err)
  59                         // Concurrency iterate rows
  60                         rows, err := f.Rows("Sheet1")
  61                         assert.NoError(t, err)
  62                         for rows.Next() {
  63                                 _, err := rows.Columns()
  64                                 assert.NoError(t, err)
  65                         }
  66                         // Concurrency iterate columns
  67                         cols, err := f.Cols("Sheet1")
  68                         assert.NoError(t, err)
  69                         for cols.Next() {
  70                                 _, err := cols.Rows()
  71                                 assert.NoError(t, err)
  72                         }
  73                         // Concurrency set columns style
  74                         assert.NoError(t, f.SetColStyle("Sheet1", "C:E", style))
  75                         // Concurrency get columns style
  76                         styleID, err := f.GetColStyle("Sheet1", "D")
  77                         assert.NoError(t, err)
  78                         assert.Equal(t, style, styleID)
  79                         // Concurrency set columns width
  80                         assert.NoError(t, f.SetColWidth("Sheet1", "A", "B", 10))
  81                         // Concurrency get columns width
  82                         width, err := f.GetColWidth("Sheet1", "A")
  83                         assert.NoError(t, err)
  84                         assert.Equal(t, 10.0, width)
  85                         // Concurrency set columns visible
  86                         assert.NoError(t, f.SetColVisible("Sheet1", "A:B", true))
  87                         // Concurrency get columns visible
  88                         visible, err := f.GetColVisible("Sheet1", "A")
  89                         assert.NoError(t, err)
  90                         assert.Equal(t, true, visible)
  91                         wg.Done()
  92                 }(i, t)
  93         }
  94         wg.Wait()
  95         val, err := f.GetCellValue("Sheet1", "A1")
  96         if err != nil {
  97                 t.Error(err)
  98         }
  99         assert.Equal(t, "1", val)
 100         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestConcurrency.xlsx")))
 101         assert.NoError(t, f.Close())
 102 }
 103
 104 func TestCheckCellInRangeRef(t *testing.T) {
 105         f := NewFile()
 106         expectedTrueCellInRangeRefList := [][2]string{
 107                 {"c2", "A1:AAZ32"},
 108                 {"B9", "A1:B9"},
 109                 {"C2", "C2:C2"},
 110         }
 111
 112         for _, expectedTrueCellInRangeRef := range expectedTrueCellInRangeRefList {
 113                 cell := expectedTrueCellInRangeRef[0]
 114                 reference := expectedTrueCellInRangeRef[1]
 115                 ok, err := f.checkCellInRangeRef(cell, reference)
 116                 assert.NoError(t, err)
 117                 assert.Truef(t, ok,
 118                         "Expected cell %v to be in range reference %v, got false\n", cell, reference)
 119         }
 120
 121         expectedFalseCellInRangeRefList := [][2]string{
 122                 {"c2", "A4:AAZ32"},
 123                 {"C4", "D6:A1"}, // weird case, but you never know
 124                 {"AEF42", "BZ40:AEF41"},
 125         }
 126
 127         for _, expectedFalseCellInRangeRef := range expectedFalseCellInRangeRefList {
 128                 cell := expectedFalseCellInRangeRef[0]
 129                 reference := expectedFalseCellInRangeRef[1]
 130                 ok, err := f.checkCellInRangeRef(cell, reference)
 131                 assert.NoError(t, err)
 132                 assert.Falsef(t, ok,
 133                         "Expected cell %v not to be inside of range reference %v, but got true\n", cell, reference)
 134         }
 135
 136         ok, err := f.checkCellInRangeRef("A1", "A:B")
 137         assert.EqualError(t, err, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 138         assert.False(t, ok)
 139
 140         ok, err = f.checkCellInRangeRef("AA0", "Z0:AB1")
 141         assert.EqualError(t, err, newCellNameToCoordinatesError("AA0", newInvalidCellNameError("AA0")).Error())
 142         assert.False(t, ok)
 143 }
 144
 145 func TestSetCellFloat(t *testing.T) {
 146         sheet := "Sheet1"
 147         t.Run("with no decimal", func(t *testing.T) {
 148                 f := NewFile()
 149                 assert.NoError(t, f.SetCellFloat(sheet, "A1", 123.0, -1, 64))
 150                 assert.NoError(t, f.SetCellFloat(sheet, "A2", 123.0, 1, 64))
 151                 val, err := f.GetCellValue(sheet, "A1")
 152                 assert.NoError(t, err)
 153                 assert.Equal(t, "123", val, "A1 should be 123")
 154                 val, err = f.GetCellValue(sheet, "A2")
 155                 assert.NoError(t, err)
 156                 assert.Equal(t, "123", val, "A2 should be 123")
 157         })
 158
 159         t.Run("with a decimal and precision limit", func(t *testing.T) {
 160                 f := NewFile()
 161                 assert.NoError(t, f.SetCellFloat(sheet, "A1", 123.42, 1, 64))
 162                 val, err := f.GetCellValue(sheet, "A1")
 163                 assert.NoError(t, err)
 164                 assert.Equal(t, "123.4", val, "A1 should be 123.4")
 165         })
 166
 167         t.Run("with a decimal and no limit", func(t *testing.T) {
 168                 f := NewFile()
 169                 assert.NoError(t, f.SetCellFloat(sheet, "A1", 123.42, -1, 64))
 170                 val, err := f.GetCellValue(sheet, "A1")
 171                 assert.NoError(t, err)
 172                 assert.Equal(t, "123.42", val, "A1 should be 123.42")
 173         })
 174         f := NewFile()
 175         assert.EqualError(t, f.SetCellFloat(sheet, "A", 123.42, -1, 64), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 176         // Test set cell float data type value with invalid sheet name
 177         assert.EqualError(t, f.SetCellFloat("Sheet:1", "A1", 123.42, -1, 64), ErrSheetNameInvalid.Error())
 178 }
 179
 180 func TestSetCellUint(t *testing.T) {
 181         f := NewFile()
 182         assert.NoError(t, f.SetCellValue("Sheet1", "A1", uint8(math.MaxUint8)))
 183         result, err := f.GetCellValue("Sheet1", "A1")
 184         assert.Equal(t, "255", result)
 185         assert.NoError(t, err)
 186         assert.NoError(t, f.SetCellValue("Sheet1", "A1", uint(math.MaxUint16)))
 187         result, err = f.GetCellValue("Sheet1", "A1")
 188         assert.Equal(t, "65535", result)
 189         assert.NoError(t, err)
 190         assert.NoError(t, f.SetCellValue("Sheet1", "A1", uint(math.MaxUint32)))
 191         result, err = f.GetCellValue("Sheet1", "A1")
 192         assert.Equal(t, "4294967295", result)
 193         assert.NoError(t, err)
 194         // Test uint cell value not exists worksheet
 195         assert.EqualError(t, f.SetCellUint("SheetN", "A1", 1), "sheet SheetN does not exist")
 196         // Test uint cell value with illegal cell reference
 197         assert.Equal(t, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")), f.SetCellUint("Sheet1", "A", 1))
 198 }
 199
 200 func TestSetCellValuesMultiByte(t *testing.T) {
 201         f := NewFile()
 202         row := []interface{}{
 203                 // Test set cell value with multi byte characters value
 204                 strings.Repeat("\u4E00", TotalCellChars+1),
 205                 // Test set cell value with XML escape characters
 206                 strings.Repeat("<>", TotalCellChars/2),
 207                 strings.Repeat(">", TotalCellChars-1),
 208                 strings.Repeat(">", TotalCellChars+1),
 209         }
 210         assert.NoError(t, f.SetSheetRow("Sheet1", "A1", &row))
 211         // Test set cell value with XML escape characters in stream writer
 212         _, err := f.NewSheet("Sheet2")
 213         assert.NoError(t, err)
 214         streamWriter, err := f.NewStreamWriter("Sheet2")
 215         assert.NoError(t, err)
 216         assert.NoError(t, streamWriter.SetRow("A1", row))
 217         assert.NoError(t, streamWriter.Flush())
 218         for _, sheetName := range []string{"Sheet1", "Sheet2"} {
 219                 for cell, expected := range map[string]int{
 220                         "A1": TotalCellChars,
 221                         "B1": TotalCellChars - 1,
 222                         "C1": TotalCellChars - 1,
 223                         "D1": TotalCellChars,
 224                 } {
 225                         result, err := f.GetCellValue(sheetName, cell)
 226                         assert.NoError(t, err)
 227                         assert.Len(t, []rune(result), expected)
 228                 }
 229         }
 230         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellValuesMultiByte.xlsx")))
 231 }
 232
 233 func TestSetCellValue(t *testing.T) {
 234         f := NewFile()
 235         assert.EqualError(t, f.SetCellValue("Sheet1", "A", time.Now().UTC()), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 236         assert.EqualError(t, f.SetCellValue("Sheet1", "A", time.Duration(1e13)), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 237         // Test set cell value with column and row style inherit
 238         style1, err := f.NewStyle(&Style{NumFmt: 2})
 239         assert.NoError(t, err)
 240         style2, err := f.NewStyle(&Style{NumFmt: 9})
 241         assert.NoError(t, err)
 242         assert.NoError(t, f.SetColStyle("Sheet1", "B", style1))
 243         assert.NoError(t, f.SetRowStyle("Sheet1", 1, 1, style2))
 244         assert.NoError(t, f.SetCellValue("Sheet1", "B1", 0.5))
 245         assert.NoError(t, f.SetCellValue("Sheet1", "B2", 0.5))
 246         B1, err := f.GetCellValue("Sheet1", "B1")
 247         assert.NoError(t, err)
 248         assert.Equal(t, "50%", B1)
 249         B2, err := f.GetCellValue("Sheet1", "B2")
 250         assert.NoError(t, err)
 251         assert.Equal(t, "0.50", B2)
 252
 253         // Test set cell value with invalid sheet name
 254         assert.EqualError(t, f.SetCellValue("Sheet:1", "A1", "A1"), ErrSheetNameInvalid.Error())
 255         // Test set cell value with unsupported charset shared strings table
 256         f.SharedStrings = nil
 257         f.Pkg.Store(defaultXMLPathSharedStrings, MacintoshCyrillicCharset)
 258         assert.EqualError(t, f.SetCellValue("Sheet1", "A1", "A1"), "XML syntax error on line 1: invalid UTF-8")
 259         // Test set cell value with unsupported charset workbook
 260         f.WorkBook = nil
 261         f.Pkg.Store(defaultXMLPathWorkbook, MacintoshCyrillicCharset)
 262         assert.EqualError(t, f.SetCellValue("Sheet1", "A1", time.Now().UTC()), "XML syntax error on line 1: invalid UTF-8")
 263         // Test set cell value with the shared string table's count not equal with unique count
 264         f = NewFile()
 265         f.SharedStrings = nil
 266         f.Pkg.Store(defaultXMLPathSharedStrings, []byte(fmt.Sprintf(`<sst xmlns="%s" count="2" uniqueCount="1"><si><t>a</t></si><si><t>a</t></si></sst>`, NameSpaceSpreadSheet.Value)))
 267         f.Sheet.Store("xl/worksheets/sheet1.xml", &xlsxWorksheet{
 268                 SheetData: xlsxSheetData{Row: []xlsxRow{
 269                         {R: intPtr(1), C: []xlsxC{{R: "A1", T: "str", V: "1"}}},
 270                 }},
 271         })
 272         assert.NoError(t, f.SetCellValue("Sheet1", "A1", "b"))
 273         val, err := f.GetCellValue("Sheet1", "A1")
 274         assert.NoError(t, err)
 275         assert.Equal(t, "b", val)
 276         assert.NoError(t, f.SetCellValue("Sheet1", "B1", "b"))
 277         val, err = f.GetCellValue("Sheet1", "B1")
 278         assert.NoError(t, err)
 279         assert.Equal(t, "b", val)
 280 }
 281
 282 func TestSetCellValues(t *testing.T) {
 283         f := NewFile()
 284         err := f.SetCellValue("Sheet1", "A1", time.Date(2010, time.December, 31, 0, 0, 0, 0, time.UTC))
 285         assert.NoError(t, err)
 286
 287         v, err := f.GetCellValue("Sheet1", "A1")
 288         assert.NoError(t, err)
 289         assert.Equal(t, v, "12/31/10 00:00")
 290
 291         // Test date value lower than min date supported by Excel
 292         err = f.SetCellValue("Sheet1", "A1", time.Date(1600, time.December, 31, 0, 0, 0, 0, time.UTC))
 293         assert.NoError(t, err)
 294
 295         v, err = f.GetCellValue("Sheet1", "A1")
 296         assert.NoError(t, err)
 297         assert.Equal(t, v, "1600-12-31T00:00:00Z")
 298 }
 299
 300 func TestSetCellBool(t *testing.T) {
 301         f := NewFile()
 302         assert.EqualError(t, f.SetCellBool("Sheet1", "A", true), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 303         // Test set cell boolean data type value with invalid sheet name
 304         assert.EqualError(t, f.SetCellBool("Sheet:1", "A1", true), ErrSheetNameInvalid.Error())
 305 }
 306
 307 func TestSetCellTime(t *testing.T) {
 308         date, err := time.Parse(time.RFC3339Nano, "2009-11-10T23:00:00Z")
 309         assert.NoError(t, err)
 310         for location, expected := range map[string]string{
 311                 "America/New_York": "40127.75",
 312                 "Asia/Shanghai":    "40128.291666666664",
 313                 "Europe/London":    "40127.958333333336",
 314                 "UTC":              "40127.958333333336",
 315         } {
 316                 timezone, err := time.LoadLocation(location)
 317                 assert.NoError(t, err)
 318                 c := &xlsxC{}
 319                 isNum, err := c.setCellTime(date.In(timezone), false)
 320                 assert.NoError(t, err)
 321                 assert.Equal(t, true, isNum)
 322                 assert.Equal(t, expected, c.V)
 323         }
 324 }
 325
 326 func TestGetCellValue(t *testing.T) {
 327         // Test get cell value without r attribute of the row
 328         f := NewFile()
 329         sheetData := `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>%s</sheetData></worksheet>`
 330
 331         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 332         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `<row r="3"><c t="inlineStr"><is><t>A3</t></is></c></row><row><c t="inlineStr"><is><t>A4</t></is></c><c t="inlineStr"><is><t>B4</t></is></c></row><row r="7"><c t="inlineStr"><is><t>A7</t></is></c><c t="inlineStr"><is><t>B7</t></is></c></row><row><c t="inlineStr"><is><t>A8</t></is></c><c t="inlineStr"><is><t>B8</t></is></c></row>`)))
 333         f.checked = sync.Map{}
 334         cells := []string{"A3", "A4", "B4", "A7", "B7"}
 335         rows, err := f.GetRows("Sheet1")
 336         assert.Equal(t, [][]string{nil, nil, {"A3"}, {"A4", "B4"}, nil, nil, {"A7", "B7"}, {"A8", "B8"}}, rows)
 337         assert.NoError(t, err)
 338         for _, cell := range cells {
 339                 value, err := f.GetCellValue("Sheet1", cell)
 340                 assert.Equal(t, cell, value)
 341                 assert.NoError(t, err)
 342         }
 343         cols, err := f.GetCols("Sheet1")
 344         assert.Equal(t, [][]string{{"", "", "A3", "A4", "", "", "A7", "A8"}, {"", "", "", "B4", "", "", "B7", "B8"}}, cols)
 345         assert.NoError(t, err)
 346
 347         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 348         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `<row r="2"><c r="A2" t="inlineStr"><is><t>A2</t></is></c></row><row r="2"><c r="B2" t="inlineStr"><is><t>B2</t></is></c></row>`)))
 349         f.checked = sync.Map{}
 350         cell, err := f.GetCellValue("Sheet1", "A2")
 351         assert.Equal(t, "A2", cell)
 352         assert.NoError(t, err)
 353
 354         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 355         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `<row r="2"><c r="A2" t="inlineStr"><is><t>A2</t></is></c></row><row r="2"><c r="B2" t="inlineStr"><is><t>B2</t></is></c></row>`)))
 356         f.checked = sync.Map{}
 357         rows, err = f.GetRows("Sheet1")
 358         assert.Equal(t, [][]string{nil, {"A2", "B2"}}, rows)
 359         assert.NoError(t, err)
 360
 361         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 362         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `<row r="1"><c r="A1" t="inlineStr"><is><t>A1</t></is></c></row><row r="1"><c r="B1" t="inlineStr"><is><t>B1</t></is></c></row>`)))
 363         f.checked = sync.Map{}
 364         rows, err = f.GetRows("Sheet1")
 365         assert.Equal(t, [][]string{{"A1", "B1"}}, rows)
 366         assert.NoError(t, err)
 367
 368         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 369         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `<row><c t="inlineStr"><is><t>A3</t></is></c></row><row><c t="inlineStr"><is><t>A4</t></is></c><c t="inlineStr"><is><t>B4</t></is></c></row><row r="7"><c t="inlineStr"><is><t>A7</t></is></c><c t="inlineStr"><is><t>B7</t></is></c></row><row><c t="inlineStr"><is><t>A8</t></is></c><c t="inlineStr"><is><t>B8</t></is></c></row>`)))
 370         f.checked = sync.Map{}
 371         rows, err = f.GetRows("Sheet1")
 372         assert.Equal(t, [][]string{{"A3"}, {"A4", "B4"}, nil, nil, nil, nil, {"A7", "B7"}, {"A8", "B8"}}, rows)
 373         assert.NoError(t, err)
 374
 375         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 376         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `<row r="0"><c r="H6" t="inlineStr"><is><t>H6</t></is></c><c r="A1" t="inlineStr"><is><t>r0A6</t></is></c><c r="F4" t="inlineStr"><is><t>F4</t></is></c></row><row><c r="A1" t="inlineStr"><is><t>A6</t></is></c><c r="B1" t="inlineStr"><is><t>B6</t></is></c><c r="C1" t="inlineStr"><is><t>C6</t></is></c></row><row r="3"><c r="A3"><v>100</v></c><c r="B3" t="inlineStr"><is><t>B3</t></is></c></row>`)))
 377         f.checked = sync.Map{}
 378         rows, err = f.GetRows("Sheet1")
 379         assert.Equal(t, [][]string{
 380                 {"A6", "B6", "C6"},
 381                 nil,
 382                 {"100", "B3"},
 383                 {"", "", "", "", "", "F4"},
 384                 nil,
 385                 {"", "", "", "", "", "", "", "H6"},
 386         }, rows)
 387         assert.NoError(t, err)
 388         cell, err = f.GetCellValue("Sheet1", "H6")
 389         assert.Equal(t, "H6", cell)
 390         assert.NoError(t, err)
 391
 392         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 393         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `<row><c r="A1" t="inlineStr"><is><t>A1</t></is></c></row><row></row><row><c r="A3" t="inlineStr"><is><t>A3</t></is></c></row>`)))
 394         f.checked = sync.Map{}
 395         rows, err = f.GetRows("Sheet1")
 396         assert.Equal(t, [][]string{{"A1"}, nil, {"A3"}}, rows)
 397         assert.NoError(t, err)
 398         cell, err = f.GetCellValue("Sheet1", "A3")
 399         assert.Equal(t, "A3", cell)
 400         assert.NoError(t, err)
 401
 402         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 403         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, `
 404         <row r="1"><c r="A1"><v>2422.3000000000002</v></c></row>
 405         <row r="2"><c r="A2"><v>2422.3000000000002</v></c></row>
 406         <row r="3"><c r="A3"><v>12.4</v></c></row>
 407         <row r="4"><c r="A4"><v>964</v></c></row>
 408         <row r="5"><c r="A5"><v>1101.5999999999999</v></c></row>
 409         <row r="6"><c r="A6"><v>275.39999999999998</v></c></row>
 410         <row r="7"><c r="A7"><v>68.900000000000006</v></c></row>
 411         <row r="8"><c r="A8"><v>44385.208333333336</v></c></row>
 412         <row r="9"><c r="A9"><v>5.0999999999999996</v></c></row>
 413         <row r="10"><c r="A10"><v>5.1100000000000003</v></c></row>
 414         <row r="11"><c r="A11"><v>5.0999999999999996</v></c></row>
 415         <row r="12"><c r="A12"><v>5.1109999999999998</v></c></row>
 416         <row r="13"><c r="A13"><v>5.1111000000000004</v></c></row>
 417         <row r="14"><c r="A14"><v>2422.012345678</v></c></row>
 418         <row r="15"><c r="A15"><v>2422.0123456789</v></c></row>
 419         <row r="16"><c r="A16"><v>12.012345678901</v></c></row>
 420         <row r="17"><c r="A17"><v>964</v></c></row>
 421         <row r="18"><c r="A18"><v>1101.5999999999999</v></c></row>
 422         <row r="19"><c r="A19"><v>275.39999999999998</v></c></row>
 423         <row r="20"><c r="A20"><v>68.900000000000006</v></c></row>
 424         <row r="21"><c r="A21"><v>8.8880000000000001E-2</v></c></row>
 425         <row r="22"><c r="A22"><v>4.0000000000000003e-5</v></c></row>
 426         <row r="23"><c r="A23"><v>2422.3000000000002</v></c></row>
 427         <row r="24"><c r="A24"><v>1101.5999999999999</v></c></row>
 428         <row r="25"><c r="A25"><v>275.39999999999998</v></c></row>
 429         <row r="26"><c r="A26"><v>68.900000000000006</v></c></row>
 430         <row r="27"><c r="A27"><v>1.1000000000000001</v></c></row>
 431         <row r="28"><c r="A28" t="inlineStr"><is><t>1234567890123_4</t></is></c></row>
 432         <row r="29"><c r="A29" t="inlineStr"><is><t>123456789_0123_4</t></is></c></row>
 433         <row r="30"><c r="A30"><v>+0.0000000000000000002399999999999992E-4</v></c></row>
 434         <row r="31"><c r="A31"><v>7.2399999999999992E-2</v></c></row>
 435         <row r="32"><c r="A32" t="d"><v>20200208T080910.123</v></c></row>
 436         <row r="33"><c r="A33" t="d"><v>20200208T080910,123</v></c></row>
 437         <row r="34"><c r="A34" t="d"><v>20221022T150529Z</v></c></row>
 438         <row r="35"><c r="A35" t="d"><v>2022-10-22T15:05:29Z</v></c></row>
 439         <row r="36"><c r="A36" t="d"><v>2020-07-10 15:00:00.000</v></c></row>`)))
 440         f.checked = sync.Map{}
 441         rows, err = f.GetCols("Sheet1")
 442         assert.Equal(t, []string{
 443                 "2422.3",
 444                 "2422.3",
 445                 "12.4",
 446                 "964",
 447                 "1101.6",
 448                 "275.4",
 449                 "68.9",
 450                 "44385.2083333333",
 451                 "5.1",
 452                 "5.11",
 453                 "5.1",
 454                 "5.111",
 455                 "5.1111",
 456                 "2422.012345678",
 457                 "2422.0123456789",
 458                 "12.012345678901",
 459                 "964",
 460                 "1101.6",
 461                 "275.4",
 462                 "68.9",
 463                 "0.08888",
 464                 "0.00004",
 465                 "2422.3",
 466                 "1101.6",
 467                 "275.4",
 468                 "68.9",
 469                 "1.1",
 470                 "1234567890123_4",
 471                 "123456789_0123_4",
 472                 "2.39999999999999E-23",
 473                 "0.0724",
 474                 "43869.3397004977",
 475                 "43869.3397004977",
 476                 "44856.6288078704",
 477                 "44856.6288078704",
 478                 "2020-07-10 15:00:00.000",
 479         }, rows[0])
 480         assert.NoError(t, err)
 481
 482         // Test get cell value with unsupported charset shared strings table
 483         f.SharedStrings = nil
 484         f.Pkg.Store(defaultXMLPathSharedStrings, MacintoshCyrillicCharset)
 485         _, value := f.GetCellValue("Sheet1", "A1")
 486         assert.EqualError(t, value, "XML syntax error on line 1: invalid UTF-8")
 487         // Test get cell value with invalid sheet name
 488         _, err = f.GetCellValue("Sheet:1", "A1")
 489         assert.EqualError(t, err, ErrSheetNameInvalid.Error())
 490 }
 491
 492 func TestGetCellType(t *testing.T) {
 493         f := NewFile()
 494         cellType, err := f.GetCellType("Sheet1", "A1")
 495         assert.NoError(t, err)
 496         assert.Equal(t, CellTypeUnset, cellType)
 497         assert.NoError(t, f.SetCellValue("Sheet1", "A1", "A1"))
 498         cellType, err = f.GetCellType("Sheet1", "A1")
 499         assert.NoError(t, err)
 500         assert.Equal(t, CellTypeSharedString, cellType)
 501         _, err = f.GetCellType("Sheet1", "A")
 502         assert.EqualError(t, err, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 503         // Test get cell type with invalid sheet name
 504         _, err = f.GetCellType("Sheet:1", "A1")
 505         assert.EqualError(t, err, ErrSheetNameInvalid.Error())
 506 }
 507
 508 func TestGetValueFrom(t *testing.T) {
 509         f := NewFile()
 510         c := xlsxC{T: "s"}
 511         sst, err := f.sharedStringsReader()
 512         assert.NoError(t, err)
 513         value, err := c.getValueFrom(f, sst, false)
 514         assert.NoError(t, err)
 515         assert.Equal(t, "", value)
 516
 517         c = xlsxC{T: "s", V: " 1 "}
 518         value, err = c.getValueFrom(f, &xlsxSST{Count: 1, SI: []xlsxSI{{}, {T: &xlsxT{Val: "s"}}}}, false)
 519         assert.NoError(t, err)
 520         assert.Equal(t, "s", value)
 521 }
 522
 523 func TestGetCellFormula(t *testing.T) {
 524         // Test get cell formula on not exist worksheet
 525         f := NewFile()
 526         _, err := f.GetCellFormula("SheetN", "A1")
 527         assert.EqualError(t, err, "sheet SheetN does not exist")
 528
 529         // Test get cell formula with invalid sheet name
 530         _, err = f.GetCellFormula("Sheet:1", "A1")
 531         assert.EqualError(t, err, ErrSheetNameInvalid.Error())
 532
 533         // Test get cell formula on no formula cell
 534         assert.NoError(t, f.SetCellValue("Sheet1", "A1", true))
 535         _, err = f.GetCellFormula("Sheet1", "A1")
 536         assert.NoError(t, err)
 537
 538         // Test get cell shared formula
 539         f = NewFile()
 540         sheetData := `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData><row r="1"><c r="A1"><v>1</v></c><c r="B1"><f>2*A1</f></c></row><row r="2"><c r="A2"><v>2</v></c><c r="B2"><f t="shared" ref="B2:B7" si="0">%s</f></c></row><row r="3"><c r="A3"><v>3</v></c><c r="B3"><f t="shared" si="0"/></c></row><row r="4"><c r="A4"><v>4</v></c><c r="B4"><f t="shared" si="0"/></c></row><row r="5"><c r="A5"><v>5</v></c><c r="B5"><f t="shared" si="0"/></c></row><row r="6"><c r="A6"><v>6</v></c><c r="B6"><f t="shared" si="0"/></c></row><row r="7"><c r="A7"><v>7</v></c><c r="B7"><f t="shared" si="0"/></c></row></sheetData></worksheet>`
 541
 542         for sharedFormula, expected := range map[string]string{
 543                 `2*A2`:           `2*A3`,
 544                 `2*A1A`:          `2*A2A`,
 545                 `2*$A$2+LEN("")`: `2*$A$2+LEN("")`,
 546         } {
 547                 f.Sheet.Delete("xl/worksheets/sheet1.xml")
 548                 f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, sharedFormula)))
 549                 formula, err := f.GetCellFormula("Sheet1", "B3")
 550                 assert.NoError(t, err)
 551                 assert.Equal(t, expected, formula)
 552         }
 553
 554         f.Sheet.Delete("xl/worksheets/sheet1.xml")
 555         f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData><row r="2"><c r="B2"><f t="shared" si="0"></f></c></row></sheetData></worksheet>`))
 556         formula, err := f.GetCellFormula("Sheet1", "B2")
 557         assert.NoError(t, err)
 558         assert.Equal(t, "", formula)
 559 }
 560
 561 func ExampleFile_SetCellFloat() {
 562         f := NewFile()
 563         defer func() {
 564                 if err := f.Close(); err != nil {
 565                         fmt.Println(err)
 566                 }
 567         }()
 568         x := 3.14159265
 569         if err := f.SetCellFloat("Sheet1", "A1", x, 2, 64); err != nil {
 570                 fmt.Println(err)
 571         }
 572         val, err := f.GetCellValue("Sheet1", "A1")
 573         if err != nil {
 574                 fmt.Println(err)
 575                 return
 576         }
 577         fmt.Println(val)
 578         // Output: 3.14
 579 }
 580
 581 func BenchmarkSetCellValue(b *testing.B) {
 582         values := []string{"First", "Second", "Third", "Fourth", "Fifth", "Sixth"}
 583         cols := []string{"A", "B", "C", "D", "E", "F"}
 584         f := NewFile()
 585         b.ResetTimer()
 586         for i := 1; i <= b.N; i++ {
 587                 for j := 0; j < len(values); j++ {
 588                         if err := f.SetCellValue("Sheet1", cols[j]+strconv.Itoa(i), values[j]); err != nil {
 589                                 b.Error(err)
 590                         }
 591                 }
 592         }
 593 }
 594
 595 func TestOverflowNumericCell(t *testing.T) {
 596         f, err := OpenFile(filepath.Join("test", "OverflowNumericCell.xlsx"))
 597         if !assert.NoError(t, err) {
 598                 t.FailNow()
 599         }
 600         val, err := f.GetCellValue("Sheet1", "A1")
 601         assert.NoError(t, err)
 602         // GOARCH=amd64 - all ok; GOARCH=386 - actual: "-2147483648"
 603         assert.Equal(t, "8595602512225", val, "A1 should be 8595602512225")
 604         assert.NoError(t, f.Close())
 605 }
 606
 607 func TestSetCellFormula(t *testing.T) {
 608         f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
 609         if !assert.NoError(t, err) {
 610                 t.FailNow()
 611         }
 612
 613         assert.NoError(t, f.SetCellFormula("Sheet1", "B19", "SUM(Sheet2!D2,Sheet2!D11)"))
 614         assert.NoError(t, f.SetCellFormula("Sheet1", "C19", "SUM(Sheet2!D2,Sheet2!D9)"))
 615
 616         // Test set cell formula with invalid sheet name
 617         assert.EqualError(t, f.SetCellFormula("Sheet:1", "A1", "SUM(1,2)"), ErrSheetNameInvalid.Error())
 618
 619         // Test set cell formula with illegal rows number
 620         assert.EqualError(t, f.SetCellFormula("Sheet1", "C", "SUM(Sheet2!D2,Sheet2!D9)"), newCellNameToCoordinatesError("C", newInvalidCellNameError("C")).Error())
 621
 622         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula1.xlsx")))
 623         assert.NoError(t, f.Close())
 624
 625         f, err = OpenFile(filepath.Join("test", "CalcChain.xlsx"))
 626         if !assert.NoError(t, err) {
 627                 t.FailNow()
 628         }
 629         // Test remove cell formula
 630         assert.NoError(t, f.SetCellFormula("Sheet1", "A1", ""))
 631         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula2.xlsx")))
 632         // Test remove all cell formula
 633         assert.NoError(t, f.SetCellFormula("Sheet1", "B1", ""))
 634         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula3.xlsx")))
 635         assert.NoError(t, f.Close())
 636
 637         // Test set shared formula for the cells
 638         f = NewFile()
 639         for r := 1; r <= 5; r++ {
 640                 assert.NoError(t, f.SetSheetRow("Sheet1", fmt.Sprintf("A%d", r), &[]interface{}{r, r + 1}))
 641         }
 642         formulaType, ref := STCellFormulaTypeShared, "C1:C5"
 643         assert.NoError(t, f.SetCellFormula("Sheet1", "C1", "=A1+B1", FormulaOpts{Ref: &ref, Type: &formulaType}))
 644         sharedFormulaSpreadsheet := filepath.Join("test", "TestSetCellFormula4.xlsx")
 645         assert.NoError(t, f.SaveAs(sharedFormulaSpreadsheet))
 646
 647         f, err = OpenFile(sharedFormulaSpreadsheet)
 648         assert.NoError(t, err)
 649         ref = "D1:D5"
 650         assert.NoError(t, f.SetCellFormula("Sheet1", "D1", "=A1+C1", FormulaOpts{Ref: &ref, Type: &formulaType}))
 651         ref = ""
 652         assert.EqualError(t, f.SetCellFormula("Sheet1", "D1", "=A1+C1", FormulaOpts{Ref: &ref, Type: &formulaType}), ErrParameterInvalid.Error())
 653         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula5.xlsx")))
 654
 655         // Test set table formula for the cells
 656         f = NewFile()
 657         for idx, row := range [][]interface{}{{"A", "B", "C"}, {1, 2}} {
 658                 assert.NoError(t, f.SetSheetRow("Sheet1", fmt.Sprintf("A%d", idx+1), &row))
 659         }
 660         assert.NoError(t, f.AddTable("Sheet1", &Table{Range: "A1:C2", Name: "Table1", StyleName: "TableStyleMedium2"}))
 661         formulaType = STCellFormulaTypeDataTable
 662         assert.NoError(t, f.SetCellFormula("Sheet1", "C2", "=SUM(Table1[[A]:[B]])", FormulaOpts{Type: &formulaType}))
 663         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula6.xlsx")))
 664 }
 665
 666 func TestGetCellRichText(t *testing.T) {
 667         f, theme := NewFile(), 1
 668
 669         runsSource := []RichTextRun{
 670                 {
 671                         Text: "a\n",
 672                 },
 673                 {
 674                         Text: "b",
 675                         Font: &Font{
 676                                 Underline:  "single",
 677                                 Color:      "ff0000",
 678                                 ColorTheme: &theme,
 679                                 ColorTint:  0.5,
 680                                 Bold:       true,
 681                                 Italic:     true,
 682                                 Family:     "Times New Roman",
 683                                 Size:       100,
 684                                 Strike:     true,
 685                         },
 686                 },
 687         }
 688         assert.NoError(t, f.SetCellRichText("Sheet1", "A1", runsSource))
 689         assert.NoError(t, f.SetCellValue("Sheet1", "A2", false))
 690
 691         runs, err := f.GetCellRichText("Sheet1", "A2")
 692         assert.NoError(t, err)
 693         assert.Equal(t, []RichTextRun(nil), runs)
 694
 695         runs, err = f.GetCellRichText("Sheet1", "A1")
 696         assert.NoError(t, err)
 697
 698         assert.Equal(t, runsSource[0].Text, runs[0].Text)
 699         assert.Nil(t, runs[0].Font)
 700         assert.NotNil(t, runs[1].Font)
 701
 702         runsSource[1].Font.Color = strings.ToUpper(runsSource[1].Font.Color)
 703         assert.True(t, reflect.DeepEqual(runsSource[1].Font, runs[1].Font), "should get the same font")
 704
 705         // Test get cell rich text with inlineStr
 706         ws, ok := f.Sheet.Load("xl/worksheets/sheet1.xml")
 707         assert.True(t, ok)
 708         ws.(*xlsxWorksheet).SheetData.Row[0].C[0] = xlsxC{
 709                 T: "inlineStr",
 710                 IS: &xlsxSI{
 711                         T: &xlsxT{Val: "A"},
 712                         R: []xlsxR{{T: &xlsxT{Val: "1"}}},
 713                 },
 714         }
 715         runs, err = f.GetCellRichText("Sheet1", "A1")
 716         assert.NoError(t, err)
 717         assert.Equal(t, []RichTextRun{{Text: "A"}, {Text: "1"}}, runs)
 718
 719         // Test get cell rich text when string item index overflow
 720         ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
 721         assert.True(t, ok)
 722         ws.(*xlsxWorksheet).SheetData.Row[0].C[0] = xlsxC{V: "2", IS: &xlsxSI{}}
 723         runs, err = f.GetCellRichText("Sheet1", "A1")
 724         assert.NoError(t, err)
 725         assert.Equal(t, 0, len(runs))
 726         // Test get cell rich text when string item index is negative
 727         ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
 728         assert.True(t, ok)
 729         ws.(*xlsxWorksheet).SheetData.Row[0].C[0] = xlsxC{T: "s", V: "-1", IS: &xlsxSI{}}
 730         runs, err = f.GetCellRichText("Sheet1", "A1")
 731         assert.NoError(t, err)
 732         assert.Equal(t, 0, len(runs))
 733         // Test get cell rich text on invalid string item index
 734         ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
 735         assert.True(t, ok)
 736         ws.(*xlsxWorksheet).SheetData.Row[0].C[0] = xlsxC{V: "x"}
 737         runs, err = f.GetCellRichText("Sheet1", "A1")
 738         assert.NoError(t, err)
 739         assert.Equal(t, 0, len(runs))
 740         // Test set cell rich text on not exists worksheet
 741         _, err = f.GetCellRichText("SheetN", "A1")
 742         assert.EqualError(t, err, "sheet SheetN does not exist")
 743         // Test set cell rich text with illegal cell reference
 744         _, err = f.GetCellRichText("Sheet1", "A")
 745         assert.EqualError(t, err, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 746         // Test set rich text color theme without tint
 747         assert.NoError(t, f.SetCellRichText("Sheet1", "A1", []RichTextRun{{Font: &Font{ColorTheme: &theme}}}))
 748         // Test set rich text color tint without theme
 749         assert.NoError(t, f.SetCellRichText("Sheet1", "A1", []RichTextRun{{Font: &Font{ColorTint: 0.5}}}))
 750
 751         // Test set cell rich text with unsupported charset shared strings table
 752         f.SharedStrings = nil
 753         f.Pkg.Store(defaultXMLPathSharedStrings, MacintoshCyrillicCharset)
 754         assert.EqualError(t, f.SetCellRichText("Sheet1", "A1", runsSource), "XML syntax error on line 1: invalid UTF-8")
 755         // Test get cell rich text with unsupported charset shared strings table
 756         f.SharedStrings = nil
 757         f.Pkg.Store(defaultXMLPathSharedStrings, MacintoshCyrillicCharset)
 758         _, err = f.GetCellRichText("Sheet1", "A1")
 759         assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
 760         // Test get cell rich text with invalid sheet name
 761         _, err = f.GetCellRichText("Sheet:1", "A1")
 762         assert.EqualError(t, err, ErrSheetNameInvalid.Error())
 763 }
 764
 765 func TestSetCellRichText(t *testing.T) {
 766         f := NewFile()
 767         assert.NoError(t, f.SetRowHeight("Sheet1", 1, 35))
 768         assert.NoError(t, f.SetColWidth("Sheet1", "A", "A", 44))
 769         richTextRun := []RichTextRun{
 770                 {
 771                         Text: "bold",
 772                         Font: &Font{
 773                                 Bold:         true,
 774                                 Color:        "2354e8",
 775                                 ColorIndexed: 0,
 776                                 Family:       "Times New Roman",
 777                         },
 778                 },
 779                 {
 780                         Text: " and ",
 781                         Font: &Font{
 782                                 Family: "Times New Roman",
 783                         },
 784                 },
 785                 {
 786                         Text: "italic ",
 787                         Font: &Font{
 788                                 Bold:   true,
 789                                 Color:  "e83723",
 790                                 Italic: true,
 791                                 Family: "Times New Roman",
 792                         },
 793                 },
 794                 {
 795                         Text: "text with color and font-family, ",
 796                         Font: &Font{
 797                                 Bold:   true,
 798                                 Color:  "2354e8",
 799                                 Family: "Times New Roman",
 800                         },
 801                 },
 802                 {
 803                         Text: "\r\nlarge text with ",
 804                         Font: &Font{
 805                                 Size:  14,
 806                                 Color: "ad23e8",
 807                         },
 808                 },
 809                 {
 810                         Text: "strike",
 811                         Font: &Font{
 812                                 Color:  "e89923",
 813                                 Strike: true,
 814                         },
 815                 },
 816                 {
 817                         Text: " superscript",
 818                         Font: &Font{
 819                                 Color:     "dbc21f",
 820                                 VertAlign: "superscript",
 821                         },
 822                 },
 823                 {
 824                         Text: " and ",
 825                         Font: &Font{
 826                                 Size:      14,
 827                                 Color:     "ad23e8",
 828                                 VertAlign: "BASELINE",
 829                         },
 830                 },
 831                 {
 832                         Text: "underline",
 833                         Font: &Font{
 834                                 Color:     "23e833",
 835                                 Underline: "single",
 836                         },
 837                 },
 838                 {
 839                         Text: " subscript.",
 840                         Font: &Font{
 841                                 Color:     "017505",
 842                                 VertAlign: "subscript",
 843                         },
 844                 },
 845         }
 846         assert.NoError(t, f.SetCellRichText("Sheet1", "A1", richTextRun))
 847         assert.NoError(t, f.SetCellRichText("Sheet1", "A2", richTextRun))
 848         style, err := f.NewStyle(&Style{
 849                 Alignment: &Alignment{
 850                         WrapText: true,
 851                 },
 852         })
 853         assert.NoError(t, err)
 854         assert.NoError(t, f.SetCellStyle("Sheet1", "A1", "A1", style))
 855         assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellRichText.xlsx")))
 856         // Test set cell rich text on not exists worksheet
 857         assert.EqualError(t, f.SetCellRichText("SheetN", "A1", richTextRun), "sheet SheetN does not exist")
 858         // Test set cell rich text with invalid sheet name
 859         assert.EqualError(t, f.SetCellRichText("Sheet:1", "A1", richTextRun), ErrSheetNameInvalid.Error())
 860         // Test set cell rich text with illegal cell reference
 861         assert.EqualError(t, f.SetCellRichText("Sheet1", "A", richTextRun), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
 862         richTextRun = []RichTextRun{{Text: strings.Repeat("s", TotalCellChars+1)}}
 863         // Test set cell rich text with characters over the maximum limit
 864         assert.EqualError(t, f.SetCellRichText("Sheet1", "A1", richTextRun), ErrCellCharsLength.Error())
 865 }
 866
 867 func TestFormattedValue(t *testing.T) {
 868         f := NewFile()
 869         result, err := f.formattedValue(&xlsxC{S: 0, V: "43528"}, false, CellTypeNumber)
 870         assert.NoError(t, err)
 871         assert.Equal(t, "43528", result)
 872
 873         // S is too large
 874         result, err = f.formattedValue(&xlsxC{S: 15, V: "43528"}, false, CellTypeNumber)
 875         assert.NoError(t, err)
 876         assert.Equal(t, "43528", result)
 877
 878         // S is too small
 879         result, err = f.formattedValue(&xlsxC{S: -15, V: "43528"}, false, CellTypeNumber)
 880         assert.NoError(t, err)
 881         assert.Equal(t, "43528", result)
 882
 883         result, err = f.formattedValue(&xlsxC{S: 1, V: "43528"}, false, CellTypeNumber)
 884         assert.NoError(t, err)
 885         assert.Equal(t, "43528", result)
 886         customNumFmt := "[$-409]MM/DD/YYYY"
 887         _, err = f.NewStyle(&Style{
 888                 CustomNumFmt: &customNumFmt,
 889         })
 890         assert.NoError(t, err)
 891         result, err = f.formattedValue(&xlsxC{S: 1, V: "43528"}, false, CellTypeNumber)
 892         assert.NoError(t, err)
 893         assert.Equal(t, "03/04/2019", result)
 894
 895         // Test format value with no built-in number format ID
 896         numFmtID := 5
 897         f.Styles.CellXfs.Xf = append(f.Styles.CellXfs.Xf, xlsxXf{
 898                 NumFmtID: &numFmtID,
 899         })
 900         result, err = f.formattedValue(&xlsxC{S: 2, V: "43528"}, false, CellTypeNumber)
 901         assert.NoError(t, err)
 902         assert.Equal(t, "43528", result)
 903
 904         // Test format value with invalid number format ID
 905         f.Styles.CellXfs.Xf = append(f.Styles.CellXfs.Xf, xlsxXf{
 906                 NumFmtID: nil,
 907         })
 908         result, err = f.formattedValue(&xlsxC{S: 3, V: "43528"}, false, CellTypeNumber)
 909         assert.NoError(t, err)
 910         assert.Equal(t, "43528", result)
 911
 912         // Test format value with empty number format
 913         f.Styles.NumFmts = nil
 914         f.Styles.CellXfs.Xf = append(f.Styles.CellXfs.Xf, xlsxXf{
 915                 NumFmtID: &numFmtID,
 916         })
 917         result, err = f.formattedValue(&xlsxC{S: 1, V: "43528"}, false, CellTypeNumber)
 918         assert.NoError(t, err)
 919         assert.Equal(t, "43528", result)
 920
 921         // Test format numeric value with shared string data type
 922         f.Styles.NumFmts, numFmtID = nil, 11
 923         f.Styles.CellXfs.Xf = append(f.Styles.CellXfs.Xf, xlsxXf{
 924                 NumFmtID: &numFmtID,
 925         })
 926         result, err = f.formattedValue(&xlsxC{S: 5, V: "43528"}, false, CellTypeSharedString)
 927         assert.NoError(t, err)
 928         assert.Equal(t, "43528", result)
 929
 930         // Test format decimal value with build-in number format ID
 931         styleID, err := f.NewStyle(&Style{
 932                 NumFmt: 1,
 933         })
 934         assert.NoError(t, err)
 935         result, err = f.formattedValue(&xlsxC{S: styleID, V: "310.56"}, false, CellTypeNumber)
 936         assert.NoError(t, err)
 937         assert.Equal(t, "311", result)
 938
 939         assert.Equal(t, "0_0", format("0_0", "", false, CellTypeNumber, nil))
 940
 941         // Test format value with unsupported charset workbook
 942         f.WorkBook = nil
 943         f.Pkg.Store(defaultXMLPathWorkbook, MacintoshCyrillicCharset)
 944         _, err = f.formattedValue(&xlsxC{S: 1, V: "43528"}, false, CellTypeNumber)
 945         assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
 946
 947         // Test format value with unsupported charset style sheet
 948         f.Styles = nil
 949         f.Pkg.Store(defaultXMLPathStyles, MacintoshCyrillicCharset)
 950         _, err = f.formattedValue(&xlsxC{S: 1, V: "43528"}, false, CellTypeNumber)
 951         assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
 952
 953         assert.Equal(t, "text", format("text", "0", false, CellTypeNumber, nil))
 954 }
 955
 956 func TestFormattedValueNilXfs(t *testing.T) {
 957         // Set the CellXfs to nil and verify that the formattedValue function does not crash
 958         f := NewFile()
 959         f.Styles.CellXfs = nil
 960         result, err := f.formattedValue(&xlsxC{S: 3, V: "43528"}, false, CellTypeNumber)
 961         assert.NoError(t, err)
 962         assert.Equal(t, "43528", result)
 963 }
 964
 965 func TestFormattedValueNilNumFmts(t *testing.T) {
 966         // Set the NumFmts value to nil and verify that the formattedValue function does not crash
 967         f := NewFile()
 968         f.Styles.NumFmts = nil
 969         result, err := f.formattedValue(&xlsxC{S: 3, V: "43528"}, false, CellTypeNumber)
 970         assert.NoError(t, err)
 971         assert.Equal(t, "43528", result)
 972 }
 973
 974 func TestFormattedValueNilWorkbook(t *testing.T) {
 975         // Set the Workbook value to nil and verify that the formattedValue function does not crash
 976         f := NewFile()
 977         f.WorkBook = nil
 978         result, err := f.formattedValue(&xlsxC{S: 3, V: "43528"}, false, CellTypeNumber)
 979         assert.NoError(t, err)
 980         assert.Equal(t, "43528", result)
 981 }
 982
 983 func TestFormattedValueNilWorkbookPr(t *testing.T) {
 984         // Set the WorkBook.WorkbookPr value to nil and verify that the formattedValue function does not
 985         // crash.
 986         f := NewFile()
 987         f.WorkBook.WorkbookPr = nil
 988         result, err := f.formattedValue(&xlsxC{S: 3, V: "43528"}, false, CellTypeNumber)
 989         assert.NoError(t, err)
 990         assert.Equal(t, "43528", result)
 991 }
 992
 993 func TestGetCustomNumFmtCode(t *testing.T) {
 994         expected := "[$-ja-JP-x-gannen,80]ggge\"年\"m\"月\"d\"日\";@"
 995         styleSheet := &xlsxStyleSheet{NumFmts: &xlsxNumFmts{NumFmt: []*xlsxNumFmt{
 996                 {NumFmtID: 164, FormatCode16: expected},
 997         }}}
 998         numFmtCode, ok := styleSheet.getCustomNumFmtCode(164)
 999         assert.Equal(t, expected, numFmtCode)
1000         assert.True(t, ok)
1001 }
1002
1003 func TestSharedStringsError(t *testing.T) {
1004         f, err := OpenFile(filepath.Join("test", "Book1.xlsx"), Options{UnzipXMLSizeLimit: 128})
1005         assert.NoError(t, err)
1006         tempFile, ok := f.tempFiles.Load(defaultXMLPathSharedStrings)
1007         assert.True(t, ok)
1008         f.tempFiles.Store(defaultXMLPathSharedStrings, "")
1009         assert.Equal(t, "1", f.getFromStringItem(1))
1010         // Cleanup undelete temporary files
1011         assert.NoError(t, os.Remove(tempFile.(string)))
1012         // Test reload the file error on set cell value and rich text. The error message was different between macOS and Windows
1013         err = f.SetCellValue("Sheet1", "A19", "A19")
1014         assert.Error(t, err)
1015
1016         f.tempFiles.Store(defaultXMLPathSharedStrings, "")
1017         err = f.SetCellRichText("Sheet1", "A19", []RichTextRun{})
1018         assert.Error(t, err)
1019         assert.NoError(t, f.Close())
1020
1021         f, err = OpenFile(filepath.Join("test", "Book1.xlsx"), Options{UnzipXMLSizeLimit: 128})
1022         assert.NoError(t, err)
1023         rows, err := f.Rows("Sheet1")
1024         assert.NoError(t, err)
1025         const maxUint16 = 1<<16 - 1
1026         currentRow := 0
1027         for rows.Next() {
1028                 currentRow++
1029                 if currentRow == 19 {
1030                         _, err := rows.Columns()
1031                         assert.NoError(t, err)
1032                         // Test get cell value from string item with invalid offset
1033                         f.sharedStringItem[1] = []uint{maxUint16 - 1, maxUint16}
1034                         assert.Equal(t, "1", f.getFromStringItem(1))
1035                         break
1036                 }
1037         }
1038         assert.NoError(t, rows.Close())
1039         // Test shared string item temporary files has been closed before close the workbook
1040         assert.NoError(t, f.sharedStringTemp.Close())
1041         assert.Error(t, f.Close())
1042         // Cleanup undelete temporary files
1043         f.tempFiles.Range(func(k, v interface{}) bool {
1044                 return assert.NoError(t, os.Remove(v.(string)))
1045         })
1046
1047         f, err = OpenFile(filepath.Join("test", "Book1.xlsx"), Options{UnzipXMLSizeLimit: 128})
1048         assert.NoError(t, err)
1049         rows, err = f.Rows("Sheet1")
1050         assert.NoError(t, err)
1051         currentRow = 0
1052         for rows.Next() {
1053                 currentRow++
1054                 if currentRow == 19 {
1055                         _, err := rows.Columns()
1056                         assert.NoError(t, err)
1057                         break
1058                 }
1059         }
1060         assert.NoError(t, rows.Close())
1061         assert.NoError(t, f.sharedStringTemp.Close())
1062         // Test shared string item temporary files has been closed before set the cell value
1063         assert.Error(t, f.SetCellValue("Sheet1", "A1", "A1"))
1064         assert.Error(t, f.Close())
1065         // Cleanup undelete temporary files
1066         f.tempFiles.Range(func(k, v interface{}) bool {
1067                 return assert.NoError(t, os.Remove(v.(string)))
1068         })
1069 }
1070
1071 func TestSIString(t *testing.T) {
1072         assert.Empty(t, xlsxSI{}.String())
1073 }