diff options
Diffstat (limited to 'excelize_test.go')
| -rw-r--r-- | excelize_test.go | 894 |
1 files changed, 451 insertions, 443 deletions
diff --git a/excelize_test.go b/excelize_test.go index 6192110..87fd806 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -19,15 +19,15 @@ import ( func TestOpenFile(t *testing.T) { // Test update a XLSX file. - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } // Test get all the rows in a not exists worksheet. - xlsx.GetRows("Sheet4") + f.GetRows("Sheet4") // Test get all the rows in a worksheet. - rows, err := xlsx.GetRows("Sheet2") + rows, err := f.GetRows("Sheet2") assert.NoError(t, err) for _, row := range rows { for _, cell := range row { @@ -35,88 +35,88 @@ func TestOpenFile(t *testing.T) { } t.Log("\r\n") } - xlsx.UpdateLinkedValue() + f.UpdateLinkedValue() - xlsx.SetCellDefault("Sheet2", "A1", strconv.FormatFloat(float64(100.1588), 'f', -1, 32)) - xlsx.SetCellDefault("Sheet2", "A1", strconv.FormatFloat(float64(-100.1588), 'f', -1, 64)) + f.SetCellDefault("Sheet2", "A1", strconv.FormatFloat(float64(100.1588), 'f', -1, 32)) + f.SetCellDefault("Sheet2", "A1", strconv.FormatFloat(float64(-100.1588), 'f', -1, 64)) // Test set cell value with illegal row number. - assert.EqualError(t, xlsx.SetCellDefault("Sheet2", "A", strconv.FormatFloat(float64(-100.1588), 'f', -1, 64)), + assert.EqualError(t, f.SetCellDefault("Sheet2", "A", strconv.FormatFloat(float64(-100.1588), 'f', -1, 64)), `cannot convert cell "A" to coordinates: invalid cell name "A"`) - xlsx.SetCellInt("Sheet2", "A1", 100) + f.SetCellInt("Sheet2", "A1", 100) // Test set cell integer value with illegal row number. - assert.EqualError(t, xlsx.SetCellInt("Sheet2", "A", 100), `cannot convert cell "A" to coordinates: invalid cell name "A"`) + assert.EqualError(t, f.SetCellInt("Sheet2", "A", 100), `cannot convert cell "A" to coordinates: invalid cell name "A"`) - xlsx.SetCellStr("Sheet2", "C11", "Knowns") + f.SetCellStr("Sheet2", "C11", "Knowns") // Test max characters in a cell. - xlsx.SetCellStr("Sheet2", "D11", strings.Repeat("c", 32769)) - xlsx.NewSheet(":\\/?*[]Maximum 31 characters allowed in sheet title.") + f.SetCellStr("Sheet2", "D11", strings.Repeat("c", 32769)) + f.NewSheet(":\\/?*[]Maximum 31 characters allowed in sheet title.") // Test set worksheet name with illegal name. - xlsx.SetSheetName("Maximum 31 characters allowed i", "[Rename]:\\/?* Maximum 31 characters allowed in sheet title.") - xlsx.SetCellInt("Sheet3", "A23", 10) - xlsx.SetCellStr("Sheet3", "b230", "10") - assert.EqualError(t, xlsx.SetCellStr("Sheet10", "b230", "10"), "sheet Sheet10 is not exist") + f.SetSheetName("Maximum 31 characters allowed i", "[Rename]:\\/?* Maximum 31 characters allowed in sheet title.") + f.SetCellInt("Sheet3", "A23", 10) + f.SetCellStr("Sheet3", "b230", "10") + assert.EqualError(t, f.SetCellStr("Sheet10", "b230", "10"), "sheet Sheet10 is not exist") // Test set cell string value with illegal row number. - assert.EqualError(t, xlsx.SetCellStr("Sheet1", "A", "10"), `cannot convert cell "A" to coordinates: invalid cell name "A"`) + assert.EqualError(t, f.SetCellStr("Sheet1", "A", "10"), `cannot convert cell "A" to coordinates: invalid cell name "A"`) - xlsx.SetActiveSheet(2) + f.SetActiveSheet(2) // Test get cell formula with given rows number. - _, err = xlsx.GetCellFormula("Sheet1", "B19") + _, err = f.GetCellFormula("Sheet1", "B19") assert.NoError(t, err) // Test get cell formula with illegal worksheet name. - _, err = xlsx.GetCellFormula("Sheet2", "B20") + _, err = f.GetCellFormula("Sheet2", "B20") assert.NoError(t, err) - _, err = xlsx.GetCellFormula("Sheet1", "B20") + _, err = f.GetCellFormula("Sheet1", "B20") assert.NoError(t, err) // Test get cell formula with illegal rows number. - _, err = xlsx.GetCellFormula("Sheet1", "B") + _, err = f.GetCellFormula("Sheet1", "B") assert.EqualError(t, err, `cannot convert cell "B" to coordinates: invalid cell name "B"`) // Test get shared cell formula - xlsx.GetCellFormula("Sheet2", "H11") - xlsx.GetCellFormula("Sheet2", "I11") + f.GetCellFormula("Sheet2", "H11") + f.GetCellFormula("Sheet2", "I11") getSharedForumula(&xlsxWorksheet{}, "") // Test read cell value with given illegal rows number. - _, err = xlsx.GetCellValue("Sheet2", "a-1") + _, err = f.GetCellValue("Sheet2", "a-1") assert.EqualError(t, err, `cannot convert cell "A-1" to coordinates: invalid cell name "A-1"`) - _, err = xlsx.GetCellValue("Sheet2", "A") + _, err = f.GetCellValue("Sheet2", "A") assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`) // Test read cell value with given lowercase column number. - xlsx.GetCellValue("Sheet2", "a5") - xlsx.GetCellValue("Sheet2", "C11") - xlsx.GetCellValue("Sheet2", "D11") - xlsx.GetCellValue("Sheet2", "D12") + f.GetCellValue("Sheet2", "a5") + f.GetCellValue("Sheet2", "C11") + f.GetCellValue("Sheet2", "D11") + f.GetCellValue("Sheet2", "D12") // Test SetCellValue function. - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F1", " Hello")) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "G1", []byte("World"))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F2", 42)) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F3", int8(1<<8/2-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F4", int16(1<<16/2-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F5", int32(1<<32/2-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F6", int64(1<<32/2-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F7", float32(42.65418))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F8", float64(-42.65418))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F9", float32(42))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F10", float64(42))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F11", uint(1<<32-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F12", uint8(1<<8-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F13", uint16(1<<16-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F14", uint32(1<<32-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F15", uint64(1<<32-1))) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F16", true)) - assert.NoError(t, xlsx.SetCellValue("Sheet2", "F17", complex64(5+10i))) + assert.NoError(t, f.SetCellValue("Sheet2", "F1", " Hello")) + assert.NoError(t, f.SetCellValue("Sheet2", "G1", []byte("World"))) + assert.NoError(t, f.SetCellValue("Sheet2", "F2", 42)) + assert.NoError(t, f.SetCellValue("Sheet2", "F3", int8(1<<8/2-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F4", int16(1<<16/2-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F5", int32(1<<32/2-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F6", int64(1<<32/2-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F7", float32(42.65418))) + assert.NoError(t, f.SetCellValue("Sheet2", "F8", float64(-42.65418))) + assert.NoError(t, f.SetCellValue("Sheet2", "F9", float32(42))) + assert.NoError(t, f.SetCellValue("Sheet2", "F10", float64(42))) + assert.NoError(t, f.SetCellValue("Sheet2", "F11", uint(1<<32-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F12", uint8(1<<8-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F13", uint16(1<<16-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F14", uint32(1<<32-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F15", uint64(1<<32-1))) + assert.NoError(t, f.SetCellValue("Sheet2", "F16", true)) + assert.NoError(t, f.SetCellValue("Sheet2", "F17", complex64(5+10i))) // Test on not exists worksheet. - assert.EqualError(t, xlsx.SetCellDefault("SheetN", "A1", ""), "sheet SheetN is not exist") - assert.EqualError(t, xlsx.SetCellFloat("SheetN", "A1", 42.65418, 2, 32), "sheet SheetN is not exist") - assert.EqualError(t, xlsx.SetCellBool("SheetN", "A1", true), "sheet SheetN is not exist") - assert.EqualError(t, xlsx.SetCellFormula("SheetN", "A1", ""), "sheet SheetN is not exist") - assert.EqualError(t, xlsx.SetCellHyperLink("SheetN", "A1", "Sheet1!A40", "Location"), "sheet SheetN is not exist") + assert.EqualError(t, f.SetCellDefault("SheetN", "A1", ""), "sheet SheetN is not exist") + assert.EqualError(t, f.SetCellFloat("SheetN", "A1", 42.65418, 2, 32), "sheet SheetN is not exist") + assert.EqualError(t, f.SetCellBool("SheetN", "A1", true), "sheet SheetN is not exist") + assert.EqualError(t, f.SetCellFormula("SheetN", "A1", ""), "sheet SheetN is not exist") + assert.EqualError(t, f.SetCellHyperLink("SheetN", "A1", "Sheet1!A40", "Location"), "sheet SheetN is not exist") // Test boolean write booltest := []struct { @@ -127,55 +127,55 @@ func TestOpenFile(t *testing.T) { {true, "1"}, } for _, test := range booltest { - xlsx.SetCellValue("Sheet2", "F16", test.value) - val, err := xlsx.GetCellValue("Sheet2", "F16") + f.SetCellValue("Sheet2", "F16", test.value) + val, err := f.GetCellValue("Sheet2", "F16") assert.NoError(t, err) assert.Equal(t, test.expected, val) } - xlsx.SetCellValue("Sheet2", "G2", nil) + f.SetCellValue("Sheet2", "G2", nil) - assert.EqualError(t, xlsx.SetCellValue("Sheet2", "G4", time.Now()), "only UTC time expected") + assert.EqualError(t, f.SetCellValue("Sheet2", "G4", time.Now()), "only UTC time expected") - xlsx.SetCellValue("Sheet2", "G4", time.Now().UTC()) + f.SetCellValue("Sheet2", "G4", time.Now().UTC()) // 02:46:40 - xlsx.SetCellValue("Sheet2", "G5", time.Duration(1e13)) + f.SetCellValue("Sheet2", "G5", time.Duration(1e13)) // Test completion column. - xlsx.SetCellValue("Sheet2", "M2", nil) + f.SetCellValue("Sheet2", "M2", nil) // Test read cell value with given axis large than exists row. - xlsx.GetCellValue("Sheet2", "E231") + f.GetCellValue("Sheet2", "E231") // Test get active worksheet of XLSX and get worksheet name of XLSX by given worksheet index. - xlsx.GetSheetName(xlsx.GetActiveSheetIndex()) + f.GetSheetName(f.GetActiveSheetIndex()) // Test get worksheet index of XLSX by given worksheet name. - xlsx.GetSheetIndex("Sheet1") + f.GetSheetIndex("Sheet1") // Test get worksheet name of XLSX by given invalid worksheet index. - xlsx.GetSheetName(4) - // Test get worksheet map of XLSX. - xlsx.GetSheetMap() + f.GetSheetName(4) + // Test get worksheet map of f. + f.GetSheetMap() for i := 1; i <= 300; i++ { - xlsx.SetCellStr("Sheet3", "c"+strconv.Itoa(i), strconv.Itoa(i)) + f.SetCellStr("Sheet3", "c"+strconv.Itoa(i), strconv.Itoa(i)) } - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestOpenFile.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestOpenFile.xlsx"))) } func TestSaveFile(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSaveFile.xlsx"))) - xlsx, err = OpenFile(filepath.Join("test", "TestSaveFile.xlsx")) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSaveFile.xlsx"))) + f, err = OpenFile(filepath.Join("test", "TestSaveFile.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.Save()) + assert.NoError(t, f.Save()) } func TestSaveAsWrongPath(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if assert.NoError(t, err) { // Test write file to not exist directory. - err = xlsx.SaveAs("") + err = f.SaveAs("") if assert.Error(t, err) { assert.True(t, os.IsNotExist(err), "Error: %v: Expected os.IsNotExists(err) == true", err) } @@ -184,15 +184,15 @@ func TestSaveAsWrongPath(t *testing.T) { func TestBrokenFile(t *testing.T) { // Test write file with broken file struct. - xlsx := File{} + f := File{} t.Run("SaveWithoutName", func(t *testing.T) { - assert.EqualError(t, xlsx.Save(), "no path defined for file, consider File.WriteTo or File.Write") + assert.EqualError(t, f.Save(), "no path defined for file, consider File.WriteTo or File.Write") }) t.Run("SaveAsEmptyStruct", func(t *testing.T) { // Test write file with broken file struct with given path. - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestBrokenFile.SaveAsEmptyStruct.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestBrokenFile.SaveAsEmptyStruct.xlsx"))) }) t.Run("OpenBadWorkbook", func(t *testing.T) { @@ -214,34 +214,34 @@ func TestBrokenFile(t *testing.T) { func TestNewFile(t *testing.T) { // Test create a XLSX file. - xlsx := NewFile() - xlsx.NewSheet("Sheet1") - xlsx.NewSheet("XLSXSheet2") - xlsx.NewSheet("XLSXSheet3") - xlsx.SetCellInt("XLSXSheet2", "A23", 56) - xlsx.SetCellStr("Sheet1", "B20", "42") - xlsx.SetActiveSheet(0) + f := NewFile() + f.NewSheet("Sheet1") + f.NewSheet("XLSXSheet2") + f.NewSheet("XLSXSheet3") + f.SetCellInt("XLSXSheet2", "A23", 56) + f.SetCellStr("Sheet1", "B20", "42") + f.SetActiveSheet(0) // Test add picture to sheet with scaling and positioning. - err := xlsx.AddPicture("Sheet1", "H2", filepath.Join("test", "images", "excel.gif"), + err := f.AddPicture("Sheet1", "H2", filepath.Join("test", "images", "excel.gif"), `{"x_scale": 0.5, "y_scale": 0.5, "positioning": "absolute"}`) if !assert.NoError(t, err) { t.FailNow() } // Test add picture to worksheet without formatset. - err = xlsx.AddPicture("Sheet1", "C2", filepath.Join("test", "images", "excel.png"), "") + err = f.AddPicture("Sheet1", "C2", filepath.Join("test", "images", "excel.png"), "") if !assert.NoError(t, err) { t.FailNow() } // Test add picture to worksheet with invalid formatset. - err = xlsx.AddPicture("Sheet1", "C2", filepath.Join("test", "images", "excel.png"), `{`) + err = f.AddPicture("Sheet1", "C2", filepath.Join("test", "images", "excel.png"), `{`) if !assert.Error(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestNewFile.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestNewFile.xlsx"))) } func TestColWidth(t *testing.T) { @@ -275,128 +275,136 @@ func TestAddDrawingVML(t *testing.T) { } func TestSetCellHyperLink(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if err != nil { t.Log(err) } // Test set cell hyperlink in a work sheet already have hyperlinks. - assert.NoError(t, xlsx.SetCellHyperLink("Sheet1", "B19", "https://github.com/360EntSecGroup-Skylar/excelize", "External")) + assert.NoError(t, f.SetCellHyperLink("Sheet1", "B19", "https://github.com/360EntSecGroup-Skylar/excelize", "External")) // Test add first hyperlink in a work sheet. - assert.NoError(t, xlsx.SetCellHyperLink("Sheet2", "C1", "https://github.com/360EntSecGroup-Skylar/excelize", "External")) + assert.NoError(t, f.SetCellHyperLink("Sheet2", "C1", "https://github.com/360EntSecGroup-Skylar/excelize", "External")) // Test add Location hyperlink in a work sheet. - assert.NoError(t, xlsx.SetCellHyperLink("Sheet2", "D6", "Sheet1!D8", "Location")) + assert.NoError(t, f.SetCellHyperLink("Sheet2", "D6", "Sheet1!D8", "Location")) - assert.EqualError(t, xlsx.SetCellHyperLink("Sheet2", "C3", "Sheet1!D8", ""), `invalid link type ""`) + assert.EqualError(t, f.SetCellHyperLink("Sheet2", "C3", "Sheet1!D8", ""), `invalid link type ""`) - assert.EqualError(t, xlsx.SetCellHyperLink("Sheet2", "", "Sheet1!D60", "Location"), `invalid cell name ""`) + assert.EqualError(t, f.SetCellHyperLink("Sheet2", "", "Sheet1!D60", "Location"), `invalid cell name ""`) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellHyperLink.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellHyperLink.xlsx"))) + + file := NewFile() + for row := 1; row <= 65530; row++ { + cell, err := CoordinatesToCellName(1, row) + assert.NoError(t, err) + assert.NoError(t, file.SetCellHyperLink("Sheet1", cell, "https://github.com/360EntSecGroup-Skylar/excelize", "External")) + } + assert.EqualError(t, file.SetCellHyperLink("Sheet1", "A65531", "https://github.com/360EntSecGroup-Skylar/excelize", "External"), "over maximum limit hyperlinks in a worksheet") } func TestGetCellHyperLink(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - _, _, err = xlsx.GetCellHyperLink("Sheet1", "") + _, _, err = f.GetCellHyperLink("Sheet1", "") assert.EqualError(t, err, `invalid cell name ""`) - link, target, err := xlsx.GetCellHyperLink("Sheet1", "A22") + link, target, err := f.GetCellHyperLink("Sheet1", "A22") assert.NoError(t, err) t.Log(link, target) - link, target, err = xlsx.GetCellHyperLink("Sheet2", "D6") + link, target, err = f.GetCellHyperLink("Sheet2", "D6") assert.NoError(t, err) t.Log(link, target) - link, target, err = xlsx.GetCellHyperLink("Sheet3", "H3") + link, target, err = f.GetCellHyperLink("Sheet3", "H3") assert.EqualError(t, err, "sheet Sheet3 is not exist") t.Log(link, target) } func TestSetCellFormula(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - xlsx.SetCellFormula("Sheet1", "B19", "SUM(Sheet2!D2,Sheet2!D11)") - xlsx.SetCellFormula("Sheet1", "C19", "SUM(Sheet2!D2,Sheet2!D9)") + f.SetCellFormula("Sheet1", "B19", "SUM(Sheet2!D2,Sheet2!D11)") + f.SetCellFormula("Sheet1", "C19", "SUM(Sheet2!D2,Sheet2!D9)") // Test set cell formula with illegal rows number. - assert.EqualError(t, xlsx.SetCellFormula("Sheet1", "C", "SUM(Sheet2!D2,Sheet2!D9)"), `cannot convert cell "C" to coordinates: invalid cell name "C"`) + assert.EqualError(t, f.SetCellFormula("Sheet1", "C", "SUM(Sheet2!D2,Sheet2!D9)"), `cannot convert cell "C" to coordinates: invalid cell name "C"`) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellFormula1.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula1.xlsx"))) - xlsx, err = OpenFile(filepath.Join("test", "CalcChain.xlsx")) + f, err = OpenFile(filepath.Join("test", "CalcChain.xlsx")) if !assert.NoError(t, err) { t.FailNow() } // Test remove cell formula. - xlsx.SetCellFormula("Sheet1", "A1", "") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellFormula2.xlsx"))) + f.SetCellFormula("Sheet1", "A1", "") + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula2.xlsx"))) // Test remove all cell formula. - xlsx.SetCellFormula("Sheet1", "B1", "") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellFormula3.xlsx"))) + f.SetCellFormula("Sheet1", "B1", "") + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula3.xlsx"))) } func TestSetSheetBackground(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - err = xlsx.SetSheetBackground("Sheet2", filepath.Join("test", "images", "background.jpg")) + err = f.SetSheetBackground("Sheet2", filepath.Join("test", "images", "background.jpg")) if !assert.NoError(t, err) { t.FailNow() } - err = xlsx.SetSheetBackground("Sheet2", filepath.Join("test", "images", "background.jpg")) + err = f.SetSheetBackground("Sheet2", filepath.Join("test", "images", "background.jpg")) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetSheetBackground.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetSheetBackground.xlsx"))) } func TestSetSheetBackgroundErrors(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - err = xlsx.SetSheetBackground("Sheet2", filepath.Join("test", "not_exists", "not_exists.png")) + err = f.SetSheetBackground("Sheet2", filepath.Join("test", "not_exists", "not_exists.png")) if assert.Error(t, err) { assert.True(t, os.IsNotExist(err), "Expected os.IsNotExists(err) == true") } - err = xlsx.SetSheetBackground("Sheet2", filepath.Join("test", "Book1.xlsx")) + err = f.SetSheetBackground("Sheet2", filepath.Join("test", "Book1.xlsx")) assert.EqualError(t, err, "unsupported image extension") } func TestMergeCell(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - xlsx.MergeCell("Sheet1", "D9", "D9") - xlsx.MergeCell("Sheet1", "D9", "E9") - xlsx.MergeCell("Sheet1", "H14", "G13") - xlsx.MergeCell("Sheet1", "C9", "D8") - xlsx.MergeCell("Sheet1", "F11", "G13") - xlsx.MergeCell("Sheet1", "H7", "B15") - xlsx.MergeCell("Sheet1", "D11", "F13") - xlsx.MergeCell("Sheet1", "G10", "K12") - xlsx.SetCellValue("Sheet1", "G11", "set value in merged cell") - xlsx.SetCellInt("Sheet1", "H11", 100) - xlsx.SetCellValue("Sheet1", "I11", float64(0.5)) - xlsx.SetCellHyperLink("Sheet1", "J11", "https://github.com/360EntSecGroup-Skylar/excelize", "External") - xlsx.SetCellFormula("Sheet1", "G12", "SUM(Sheet1!B19,Sheet1!C19)") - xlsx.GetCellValue("Sheet1", "H11") - xlsx.GetCellValue("Sheet2", "A6") // Merged cell ref is single coordinate. - xlsx.GetCellFormula("Sheet1", "G12") + f.MergeCell("Sheet1", "D9", "D9") + f.MergeCell("Sheet1", "D9", "E9") + f.MergeCell("Sheet1", "H14", "G13") + f.MergeCell("Sheet1", "C9", "D8") + f.MergeCell("Sheet1", "F11", "G13") + f.MergeCell("Sheet1", "H7", "B15") + f.MergeCell("Sheet1", "D11", "F13") + f.MergeCell("Sheet1", "G10", "K12") + f.SetCellValue("Sheet1", "G11", "set value in merged cell") + f.SetCellInt("Sheet1", "H11", 100) + f.SetCellValue("Sheet1", "I11", float64(0.5)) + f.SetCellHyperLink("Sheet1", "J11", "https://github.com/360EntSecGroup-Skylar/excelize", "External") + f.SetCellFormula("Sheet1", "G12", "SUM(Sheet1!B19,Sheet1!C19)") + f.GetCellValue("Sheet1", "H11") + f.GetCellValue("Sheet2", "A6") // Merged cell ref is single coordinate. + f.GetCellFormula("Sheet1", "G12") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestMergeCell.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestMergeCell.xlsx"))) } func TestGetMergeCells(t *testing.T) { @@ -422,13 +430,13 @@ func TestGetMergeCells(t *testing.T) { end: "C10", }} - xlsx, err := OpenFile(filepath.Join("test", "MergeCell.xlsx")) + f, err := OpenFile(filepath.Join("test", "MergeCell.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - sheet1 := xlsx.GetSheetName(1) + sheet1 := f.GetSheetName(1) - mergeCells, err := xlsx.GetMergeCells(sheet1) + mergeCells, err := f.GetMergeCells(sheet1) if !assert.Len(t, mergeCells, len(wants)) { t.FailNow() } @@ -441,38 +449,38 @@ func TestGetMergeCells(t *testing.T) { } // Test get merged cells on not exists worksheet. - _, err = xlsx.GetMergeCells("SheetN") + _, err = f.GetMergeCells("SheetN") assert.EqualError(t, err, "sheet SheetN is not exist") } func TestSetCellStyleAlignment(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } var style int - style, err = xlsx.NewStyle(`{"alignment":{"horizontal":"center","ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1,"shrink_to_fit":true,"text_rotation":45,"vertical":"top","wrap_text":true}}`) + style, err = f.NewStyle(`{"alignment":{"horizontal":"center","ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1,"shrink_to_fit":true,"text_rotation":45,"vertical":"top","wrap_text":true}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A22", "A22", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "A22", "A22", style)) // Test set cell style with given illegal rows number. - assert.EqualError(t, xlsx.SetCellStyle("Sheet1", "A", "A22", style), `cannot convert cell "A" to coordinates: invalid cell name "A"`) - assert.EqualError(t, xlsx.SetCellStyle("Sheet1", "A22", "A", style), `cannot convert cell "A" to coordinates: invalid cell name "A"`) + assert.EqualError(t, f.SetCellStyle("Sheet1", "A", "A22", style), `cannot convert cell "A" to coordinates: invalid cell name "A"`) + assert.EqualError(t, f.SetCellStyle("Sheet1", "A22", "A", style), `cannot convert cell "A" to coordinates: invalid cell name "A"`) // Test get cell style with given illegal rows number. - index, err := xlsx.GetCellStyle("Sheet1", "A") + index, err := f.GetCellStyle("Sheet1", "A") assert.Equal(t, 0, index) assert.EqualError(t, err, `cannot convert cell "A" to coordinates: invalid cell name "A"`) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleAlignment.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleAlignment.xlsx"))) } func TestSetCellStyleBorder(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } @@ -480,56 +488,56 @@ func TestSetCellStyleBorder(t *testing.T) { var style int // Test set border on overlapping area with vertical variants shading styles gradient fill. - style, err = xlsx.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":2},{"type":"top","color":"00FF00","style":12},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":9},{"type":"diagonalUp","color":"A020F0","style":8}]}`) + style, err = f.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":2},{"type":"top","color":"00FF00","style":12},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":9},{"type":"diagonalUp","color":"A020F0","style":8}]}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "J21", "L25", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "J21", "L25", style)) - style, err = xlsx.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":2},{"type":"top","color":"00FF00","style":3},{"type":"bottom","color":"FFFF00","style":4},{"type":"right","color":"FF0000","style":5},{"type":"diagonalDown","color":"A020F0","style":6},{"type":"diagonalUp","color":"A020F0","style":7}],"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}}`) + style, err = f.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":2},{"type":"top","color":"00FF00","style":3},{"type":"bottom","color":"FFFF00","style":4},{"type":"right","color":"FF0000","style":5},{"type":"diagonalDown","color":"A020F0","style":6},{"type":"diagonalUp","color":"A020F0","style":7}],"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "M28", "K24", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "M28", "K24", style)) - style, err = xlsx.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":2},{"type":"top","color":"00FF00","style":3},{"type":"bottom","color":"FFFF00","style":4},{"type":"right","color":"FF0000","style":5},{"type":"diagonalDown","color":"A020F0","style":6},{"type":"diagonalUp","color":"A020F0","style":7}],"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":4}}`) + style, err = f.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":2},{"type":"top","color":"00FF00","style":3},{"type":"bottom","color":"FFFF00","style":4},{"type":"right","color":"FF0000","style":5},{"type":"diagonalDown","color":"A020F0","style":6},{"type":"diagonalUp","color":"A020F0","style":7}],"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":4}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "M28", "K24", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "M28", "K24", style)) // Test set border and solid style pattern fill for a single cell. - style, err = xlsx.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":8},{"type":"top","color":"00FF00","style":9},{"type":"bottom","color":"FFFF00","style":10},{"type":"right","color":"FF0000","style":11},{"type":"diagonalDown","color":"A020F0","style":12},{"type":"diagonalUp","color":"A020F0","style":13}],"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`) + style, err = f.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":8},{"type":"top","color":"00FF00","style":9},{"type":"bottom","color":"FFFF00","style":10},{"type":"right","color":"FF0000","style":11},{"type":"diagonalDown","color":"A020F0","style":12},{"type":"diagonalUp","color":"A020F0","style":13}],"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "O22", "O22", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "O22", "O22", style)) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleBorder.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleBorder.xlsx"))) } func TestSetCellStyleBorderErrors(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } // Set border with invalid style parameter. - _, err = xlsx.NewStyle("") + _, err = f.NewStyle("") if !assert.EqualError(t, err, "unexpected end of JSON input") { t.FailNow() } // Set border with invalid style index number. - _, err = xlsx.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":-1},{"type":"top","color":"00FF00","style":14},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":9},{"type":"diagonalUp","color":"A020F0","style":8}]}`) + _, err = f.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":-1},{"type":"top","color":"00FF00","style":14},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":9},{"type":"diagonalUp","color":"A020F0","style":8}]}`) if !assert.NoError(t, err) { t.FailNow() } } func TestSetCellStyleNumberFormat(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } @@ -544,202 +552,202 @@ func TestSetCellStyleNumberFormat(t *testing.T) { var val float64 val, err = strconv.ParseFloat(v, 64) if err != nil { - xlsx.SetCellValue("Sheet2", c, v) + f.SetCellValue("Sheet2", c, v) } else { - xlsx.SetCellValue("Sheet2", c, val) + f.SetCellValue("Sheet2", c, val) } - style, err := xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":5},"number_format": ` + strconv.Itoa(d) + `}`) + style, err := f.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":5},"number_format": ` + strconv.Itoa(d) + `}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", c, c, style)) - t.Log(xlsx.GetCellValue("Sheet2", c)) + assert.NoError(t, f.SetCellStyle("Sheet2", c, c, style)) + t.Log(f.GetCellValue("Sheet2", c)) } } var style int - style, err = xlsx.NewStyle(`{"number_format":-1}`) + style, err = f.NewStyle(`{"number_format":-1}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", "L33", "L33", style)) + assert.NoError(t, f.SetCellStyle("Sheet2", "L33", "L33", style)) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleNumberFormat.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleNumberFormat.xlsx"))) } func TestSetCellStyleCurrencyNumberFormat(t *testing.T) { t.Run("TestBook3", func(t *testing.T) { - xlsx, err := prepareTestBook3() + f, err := prepareTestBook3() if !assert.NoError(t, err) { t.FailNow() } - xlsx.SetCellValue("Sheet1", "A1", 56) - xlsx.SetCellValue("Sheet1", "A2", -32.3) + f.SetCellValue("Sheet1", "A1", 56) + f.SetCellValue("Sheet1", "A2", -32.3) var style int - style, err = xlsx.NewStyle(`{"number_format": 188, "decimal_places": -1}`) + style, err = f.NewStyle(`{"number_format": 188, "decimal_places": -1}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A1", "A1", style)) - style, err = xlsx.NewStyle(`{"number_format": 188, "decimal_places": 31, "negred": true}`) + assert.NoError(t, f.SetCellStyle("Sheet1", "A1", "A1", style)) + style, err = f.NewStyle(`{"number_format": 188, "decimal_places": 31, "negred": true}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A2", "A2", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "A2", "A2", style)) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleCurrencyNumberFormat.TestBook3.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleCurrencyNumberFormat.TestBook3.xlsx"))) }) t.Run("TestBook4", func(t *testing.T) { - xlsx, err := prepareTestBook4() + f, err := prepareTestBook4() if !assert.NoError(t, err) { t.FailNow() } - xlsx.SetCellValue("Sheet1", "A1", 42920.5) - xlsx.SetCellValue("Sheet1", "A2", 42920.5) + f.SetCellValue("Sheet1", "A1", 42920.5) + f.SetCellValue("Sheet1", "A2", 42920.5) - _, err = xlsx.NewStyle(`{"number_format": 26, "lang": "zh-tw"}`) + _, err = f.NewStyle(`{"number_format": 26, "lang": "zh-tw"}`) if !assert.NoError(t, err) { t.FailNow() } - style, err := xlsx.NewStyle(`{"number_format": 27}`) + style, err := f.NewStyle(`{"number_format": 27}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A1", "A1", style)) - style, err = xlsx.NewStyle(`{"number_format": 31, "lang": "ko-kr"}`) + assert.NoError(t, f.SetCellStyle("Sheet1", "A1", "A1", style)) + style, err = f.NewStyle(`{"number_format": 31, "lang": "ko-kr"}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A2", "A2", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "A2", "A2", style)) - style, err = xlsx.NewStyle(`{"number_format": 71, "lang": "th-th"}`) + style, err = f.NewStyle(`{"number_format": 71, "lang": "th-th"}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A2", "A2", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "A2", "A2", style)) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleCurrencyNumberFormat.TestBook4.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleCurrencyNumberFormat.TestBook4.xlsx"))) }) } func TestSetCellStyleCustomNumberFormat(t *testing.T) { - xlsx := NewFile() - xlsx.SetCellValue("Sheet1", "A1", 42920.5) - xlsx.SetCellValue("Sheet1", "A2", 42920.5) - style, err := xlsx.NewStyle(`{"custom_number_format": "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"}`) + f := NewFile() + f.SetCellValue("Sheet1", "A1", 42920.5) + f.SetCellValue("Sheet1", "A2", 42920.5) + style, err := f.NewStyle(`{"custom_number_format": "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"}`) if err != nil { t.Log(err) } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A1", "A1", style)) - style, err = xlsx.NewStyle(`{"custom_number_format": "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"}`) + assert.NoError(t, f.SetCellStyle("Sheet1", "A1", "A1", style)) + style, err = f.NewStyle(`{"custom_number_format": "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"}`) if err != nil { t.Log(err) } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "A2", "A2", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "A2", "A2", style)) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleCustomNumberFormat.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleCustomNumberFormat.xlsx"))) } func TestSetCellStyleFill(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } var style int // Test set fill for cell with invalid parameter. - style, err = xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":6}}`) + style, err = f.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":6}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "O23", "O23", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "O23", "O23", style)) - style, err = xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF"],"shading":1}}`) + style, err = f.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF"],"shading":1}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "O23", "O23", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "O23", "O23", style)) - style, err = xlsx.NewStyle(`{"fill":{"type":"pattern","color":[],"pattern":1}}`) + style, err = f.NewStyle(`{"fill":{"type":"pattern","color":[],"pattern":1}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "O23", "O23", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "O23", "O23", style)) - style, err = xlsx.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":19}}`) + style, err = f.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":19}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet1", "O23", "O23", style)) + assert.NoError(t, f.SetCellStyle("Sheet1", "O23", "O23", style)) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleFill.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleFill.xlsx"))) } func TestSetCellStyleFont(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } var style int - style, err = xlsx.NewStyle(`{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777","underline":"single"}}`) + style, err = f.NewStyle(`{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777","underline":"single"}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", "A1", "A1", style)) + assert.NoError(t, f.SetCellStyle("Sheet2", "A1", "A1", style)) - style, err = xlsx.NewStyle(`{"font":{"italic":true,"underline":"double"}}`) + style, err = f.NewStyle(`{"font":{"italic":true,"underline":"double"}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", "A2", "A2", style)) + assert.NoError(t, f.SetCellStyle("Sheet2", "A2", "A2", style)) - style, err = xlsx.NewStyle(`{"font":{"bold":true}}`) + style, err = f.NewStyle(`{"font":{"bold":true}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", "A3", "A3", style)) + assert.NoError(t, f.SetCellStyle("Sheet2", "A3", "A3", style)) - style, err = xlsx.NewStyle(`{"font":{"bold":true,"family":"","size":0,"color":"","underline":""}}`) + style, err = f.NewStyle(`{"font":{"bold":true,"family":"","size":0,"color":"","underline":""}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", "A4", "A4", style)) + assert.NoError(t, f.SetCellStyle("Sheet2", "A4", "A4", style)) - style, err = xlsx.NewStyle(`{"font":{"color":"#777777"}}`) + style, err = f.NewStyle(`{"font":{"color":"#777777"}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", "A5", "A5", style)) + assert.NoError(t, f.SetCellStyle("Sheet2", "A5", "A5", style)) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleFont.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellStyleFont.xlsx"))) } func TestSetCellStyleProtection(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } var style int - style, err = xlsx.NewStyle(`{"protection":{"hidden":true, "locked":true}}`) + style, err = f.NewStyle(`{"protection":{"hidden":true, "locked":true}}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetCellStyle("Sheet2", "A6", "A6", style)) - err = xlsx.SaveAs(filepath.Join("test", "TestSetCellStyleProtection.xlsx")) + assert.NoError(t, f.SetCellStyle("Sheet2", "A6", "A6", style)) + err = f.SaveAs(filepath.Join("test", "TestSetCellStyleProtection.xlsx")) if !assert.NoError(t, err) { t.FailNow() } @@ -747,172 +755,172 @@ func TestSetCellStyleProtection(t *testing.T) { func TestSetDeleteSheet(t *testing.T) { t.Run("TestBook3", func(t *testing.T) { - xlsx, err := prepareTestBook3() + f, err := prepareTestBook3() if !assert.NoError(t, err) { t.FailNow() } - xlsx.DeleteSheet("XLSXSheet3") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetDeleteSheet.TestBook3.xlsx"))) + f.DeleteSheet("XLSXSheet3") + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetDeleteSheet.TestBook3.xlsx"))) }) t.Run("TestBook4", func(t *testing.T) { - xlsx, err := prepareTestBook4() + f, err := prepareTestBook4() if !assert.NoError(t, err) { t.FailNow() } - xlsx.DeleteSheet("Sheet1") - xlsx.AddComment("Sheet1", "A1", "") - xlsx.AddComment("Sheet1", "A1", `{"author":"Excelize: ","text":"This is a comment."}`) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetDeleteSheet.TestBook4.xlsx"))) + f.DeleteSheet("Sheet1") + f.AddComment("Sheet1", "A1", "") + f.AddComment("Sheet1", "A1", `{"author":"Excelize: ","text":"This is a comment."}`) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetDeleteSheet.TestBook4.xlsx"))) }) } func TestSheetVisibility(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } - xlsx.SetSheetVisible("Sheet2", false) - xlsx.SetSheetVisible("Sheet1", false) - xlsx.SetSheetVisible("Sheet1", true) - xlsx.GetSheetVisible("Sheet1") + f.SetSheetVisible("Sheet2", false) + f.SetSheetVisible("Sheet1", false) + f.SetSheetVisible("Sheet1", true) + f.GetSheetVisible("Sheet1") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSheetVisibility.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSheetVisibility.xlsx"))) } func TestColumnVisibility(t *testing.T) { t.Run("TestBook1", func(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.SetColVisible("Sheet1", "F", false)) - assert.NoError(t, xlsx.SetColVisible("Sheet1", "F", true)) - visible, err := xlsx.GetColVisible("Sheet1", "F") + assert.NoError(t, f.SetColVisible("Sheet1", "F", false)) + assert.NoError(t, f.SetColVisible("Sheet1", "F", true)) + visible, err := f.GetColVisible("Sheet1", "F") assert.Equal(t, true, visible) assert.NoError(t, err) // Test get column visiable on not exists worksheet. - _, err = xlsx.GetColVisible("SheetN", "F") + _, err = f.GetColVisible("SheetN", "F") assert.EqualError(t, err, "sheet SheetN is not exist") // Test get column visiable with illegal cell coordinates. - _, err = xlsx.GetColVisible("Sheet1", "*") + _, err = f.GetColVisible("Sheet1", "*") assert.EqualError(t, err, `invalid column name "*"`) - assert.EqualError(t, xlsx.SetColVisible("Sheet1", "*", false), `invalid column name "*"`) + assert.EqualError(t, f.SetColVisible("Sheet1", "*", false), `invalid column name "*"`) - xlsx.NewSheet("Sheet3") - assert.NoError(t, xlsx.SetColVisible("Sheet3", "E", false)) + f.NewSheet("Sheet3") + assert.NoError(t, f.SetColVisible("Sheet3", "E", false)) - assert.EqualError(t, xlsx.SetColVisible("SheetN", "E", false), "sheet SheetN is not exist") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestColumnVisibility.xlsx"))) + assert.EqualError(t, f.SetColVisible("SheetN", "E", false), "sheet SheetN is not exist") + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestColumnVisibility.xlsx"))) }) t.Run("TestBook3", func(t *testing.T) { - xlsx, err := prepareTestBook3() + f, err := prepareTestBook3() if !assert.NoError(t, err) { t.FailNow() } - xlsx.GetColVisible("Sheet1", "B") + f.GetColVisible("Sheet1", "B") }) } func TestCopySheet(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } - idx := xlsx.NewSheet("CopySheet") - assert.EqualError(t, xlsx.CopySheet(1, idx), "sheet sheet1 is not exist") + idx := f.NewSheet("CopySheet") + assert.EqualError(t, f.CopySheet(1, idx), "sheet sheet1 is not exist") - xlsx.SetCellValue("Sheet4", "F1", "Hello") - val, err := xlsx.GetCellValue("Sheet1", "F1") + f.SetCellValue("Sheet4", "F1", "Hello") + val, err := f.GetCellValue("Sheet1", "F1") assert.NoError(t, err) assert.NotEqual(t, "Hello", val) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestCopySheet.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestCopySheet.xlsx"))) } func TestCopySheetError(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } - err = xlsx.CopySheet(0, -1) + err = f.CopySheet(0, -1) if !assert.EqualError(t, err, "invalid worksheet index") { t.FailNow() } - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestCopySheetError.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestCopySheetError.xlsx"))) } func TestAddTable(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } - err = xlsx.AddTable("Sheet1", "B26", "A21", `{}`) + err = f.AddTable("Sheet1", "B26", "A21", `{}`) if !assert.NoError(t, err) { t.FailNow() } - err = xlsx.AddTable("Sheet2", "A2", "B5", `{"table_name":"table","table_style":"TableStyleMedium2", "show_first_column":true,"show_last_column":true,"show_row_stripes":false,"show_column_stripes":true}`) + err = f.AddTable("Sheet2", "A2", "B5", `{"table_name":"table","table_style":"TableStyleMedium2", "show_first_column":true,"show_last_column":true,"show_row_stripes":false,"show_column_stripes":true}`) if !assert.NoError(t, err) { t.FailNow() } - err = xlsx.AddTable("Sheet2", "F1", "F1", `{"table_style":"TableStyleMedium8"}`) + err = f.AddTable("Sheet2", "F1", "F1", `{"table_style":"TableStyleMedium8"}`) if !assert.NoError(t, err) { t.FailNow() } // Test add table with illegal formatset. - assert.EqualError(t, xlsx.AddTable("Sheet1", "B26", "A21", `{x}`), "invalid character 'x' looking for beginning of object key string") + assert.EqualError(t, f.AddTable("Sheet1", "B26", "A21", `{x}`), "invalid character 'x' looking for beginning of object key string") // Test add table with illegal cell coordinates. - assert.EqualError(t, xlsx.AddTable("Sheet1", "A", "B1", `{}`), `cannot convert cell "A" to coordinates: invalid cell name "A"`) - assert.EqualError(t, xlsx.AddTable("Sheet1", "A1", "B", `{}`), `cannot convert cell "B" to coordinates: invalid cell name "B"`) + assert.EqualError(t, f.AddTable("Sheet1", "A", "B1", `{}`), `cannot convert cell "A" to coordinates: invalid cell name "A"`) + assert.EqualError(t, f.AddTable("Sheet1", "A1", "B", `{}`), `cannot convert cell "B" to coordinates: invalid cell name "B"`) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestAddTable.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddTable.xlsx"))) // Test addTable with illegal cell coordinates. - f := NewFile() + f = NewFile() assert.EqualError(t, f.addTable("sheet1", "", 0, 0, 0, 0, 0, nil), "invalid cell coordinates [0, 0]") assert.EqualError(t, f.addTable("sheet1", "", 1, 1, 0, 0, 0, nil), "invalid cell coordinates [0, 0]") } func TestAddShape(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } - xlsx.AddShape("Sheet1", "A30", `{"type":"rect","paragraph":[{"text":"Rectangle","font":{"color":"CD5C5C"}},{"text":"Shape","font":{"bold":true,"color":"2980B9"}}]}`) - xlsx.AddShape("Sheet1", "B30", `{"type":"rect","paragraph":[{"text":"Rectangle"},{}]}`) - xlsx.AddShape("Sheet1", "C30", `{"type":"rect","paragraph":[]}`) - xlsx.AddShape("Sheet3", "H1", `{"type":"ellipseRibbon", "color":{"line":"#4286f4","fill":"#8eb9ff"}, "paragraph":[{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777","underline":"single"}}], "height": 90}`) - xlsx.AddShape("Sheet3", "H1", "") + f.AddShape("Sheet1", "A30", `{"type":"rect","paragraph":[{"text":"Rectangle","font":{"color":"CD5C5C"}},{"text":"Shape","font":{"bold":true,"color":"2980B9"}}]}`) + f.AddShape("Sheet1", "B30", `{"type":"rect","paragraph":[{"text":"Rectangle"},{}]}`) + f.AddShape("Sheet1", "C30", `{"type":"rect","paragraph":[]}`) + f.AddShape("Sheet3", "H1", `{"type":"ellipseRibbon", "color":{"line":"#4286f4","fill":"#8eb9ff"}, "paragraph":[{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777","underline":"single"}}], "height": 90}`) + f.AddShape("Sheet3", "H1", "") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestAddShape.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddShape.xlsx"))) } func TestAddComments(t *testing.T) { - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } s := strings.Repeat("c", 32768) - xlsx.AddComment("Sheet1", "A30", `{"author":"`+s+`","text":"`+s+`"}`) - xlsx.AddComment("Sheet2", "B7", `{"author":"Excelize: ","text":"This is a comment."}`) + f.AddComment("Sheet1", "A30", `{"author":"`+s+`","text":"`+s+`"}`) + f.AddComment("Sheet2", "B7", `{"author":"Excelize: ","text":"This is a comment."}`) - if assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestAddComments.xlsx"))) { - assert.Len(t, xlsx.GetComments(), 2) + if assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddComments.xlsx"))) { + assert.Len(t, f.GetComments(), 2) } } @@ -924,7 +932,7 @@ func TestGetSheetComments(t *testing.T) { func TestAutoFilter(t *testing.T) { outFile := filepath.Join("test", "TestAutoFilter%d.xlsx") - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } @@ -942,21 +950,21 @@ func TestAutoFilter(t *testing.T) { for i, format := range formats { t.Run(fmt.Sprintf("Expression%d", i+1), func(t *testing.T) { - err = xlsx.AutoFilter("Sheet1", "D4", "B1", format) + err = f.AutoFilter("Sheet1", "D4", "B1", format) assert.NoError(t, err) - assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, i+1))) + assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, i+1))) }) } // testing AutoFilter with illegal cell coordinates. - assert.EqualError(t, xlsx.AutoFilter("Sheet1", "A", "B1", ""), `cannot convert cell "A" to coordinates: invalid cell name "A"`) - assert.EqualError(t, xlsx.AutoFilter("Sheet1", "A1", "B", ""), `cannot convert cell "B" to coordinates: invalid cell name "B"`) + assert.EqualError(t, f.AutoFilter("Sheet1", "A", "B1", ""), `cannot convert cell "A" to coordinates: invalid cell name "A"`) + assert.EqualError(t, f.AutoFilter("Sheet1", "A1", "B", ""), `cannot convert cell "B" to coordinates: invalid cell name "B"`) } func TestAutoFilterError(t *testing.T) { outFile := filepath.Join("test", "TestAutoFilterError%d.xlsx") - xlsx, err := prepareTestBook1() + f, err := prepareTestBook1() if !assert.NoError(t, err) { t.FailNow() } @@ -971,16 +979,16 @@ func TestAutoFilterError(t *testing.T) { } for i, format := range formats { t.Run(fmt.Sprintf("Expression%d", i+1), func(t *testing.T) { - err = xlsx.AutoFilter("Sheet3", "D4", "B1", format) + err = f.AutoFilter("Sheet3", "D4", "B1", format) if assert.Error(t, err) { - assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(outFile, i+1))) + assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, i+1))) } }) } } func TestAddChart(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } @@ -988,258 +996,258 @@ func TestAddChart(t *testing.T) { categories := map[string]string{"A30": "Small", "A31": "Normal", "A32": "Large", "B29": "Apple", "C29": "Orange", "D29": "Pear"} values := map[string]int{"B30": 2, "C30": 3, "D30": 3, "B31": 5, "C31": 2, "D31": 4, "B32": 6, "C32": 7, "D32": 8} for k, v := range categories { - assert.NoError(t, xlsx.SetCellValue("Sheet1", k, v)) + assert.NoError(t, f.SetCellValue("Sheet1", k, v)) } for k, v := range values { - assert.NoError(t, xlsx.SetCellValue("Sheet1", k, v)) + assert.NoError(t, f.SetCellValue("Sheet1", k, v)) } - assert.EqualError(t, xlsx.AddChart("Sheet1", "P1", ""), "unexpected end of JSON input") + assert.EqualError(t, f.AddChart("Sheet1", "P1", ""), "unexpected end of JSON input") // Test add chart on not exists worksheet. - assert.EqualError(t, xlsx.AddChart("SheetN", "P1", "{}"), "sheet SheetN is not exist") - - assert.NoError(t, xlsx.AddChart("Sheet1", "P1", `{"type":"col","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet1", "X1", `{"type":"colStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet1", "P16", `{"type":"colPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 100% Stacked Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet1", "X16", `{"type":"col3DClustered","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit 3D Clustered Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet1", "P30", `{"type":"col3DStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet1", "X30", `{"type":"col3DPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet1", "P45", `{"type":"col3D","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "P1", `{"type":"radar","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"top_right","show_legend_key":false},"title":{"name":"Fruit Radar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"span"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "X1", `{"type":"scatter","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit Scatter Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "P16", `{"type":"doughnut","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"right","show_legend_key":false},"title":{"name":"Fruit Doughnut Chart"},"plotarea":{"show_bubble_size":false,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "X16", `{"type":"line","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"top","show_legend_key":false},"title":{"name":"Fruit Line Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "P32", `{"type":"pie3D","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit 3D Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "X32", `{"type":"pie","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"gap"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "P48", `{"type":"bar","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Clustered Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "X48", `{"type":"barStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "P64", `{"type":"barPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked 100% Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "X64", `{"type":"bar3DClustered","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Clustered Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "P80", `{"type":"bar3DStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","y_axis":{"maximum":7.5,"minimum":0.5}}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "X80", `{"type":"bar3DPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"reverse_order":true,"maximum":0,"minimum":0},"y_axis":{"reverse_order":true,"maximum":0,"minimum":0}}`)) + assert.EqualError(t, f.AddChart("SheetN", "P1", "{}"), "sheet SheetN is not exist") + + assert.NoError(t, f.AddChart("Sheet1", "P1", `{"type":"col","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet1", "X1", `{"type":"colStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet1", "P16", `{"type":"colPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 100% Stacked Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet1", "X16", `{"type":"col3DClustered","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit 3D Clustered Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet1", "P30", `{"type":"col3DStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet1", "X30", `{"type":"col3DPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet1", "P45", `{"type":"col3D","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "P1", `{"type":"radar","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"top_right","show_legend_key":false},"title":{"name":"Fruit Radar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"span"}`)) + assert.NoError(t, f.AddChart("Sheet2", "X1", `{"type":"scatter","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit Scatter Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "P16", `{"type":"doughnut","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"right","show_legend_key":false},"title":{"name":"Fruit Doughnut Chart"},"plotarea":{"show_bubble_size":false,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "X16", `{"type":"line","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"top","show_legend_key":false},"title":{"name":"Fruit Line Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "P32", `{"type":"pie3D","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit 3D Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "X32", `{"type":"pie","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"gap"}`)) + assert.NoError(t, f.AddChart("Sheet2", "P48", `{"type":"bar","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Clustered Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "X48", `{"type":"barStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "P64", `{"type":"barPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked 100% Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "X64", `{"type":"bar3DClustered","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Clustered Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "P80", `{"type":"bar3DStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","y_axis":{"maximum":7.5,"minimum":0.5}}`)) + assert.NoError(t, f.AddChart("Sheet2", "X80", `{"type":"bar3DPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"reverse_order":true,"maximum":0,"minimum":0},"y_axis":{"reverse_order":true,"maximum":0,"minimum":0}}`)) // area series charts - assert.NoError(t, xlsx.AddChart("Sheet2", "AF1", `{"type":"area","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "AN1", `{"type":"areaStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "AF16", `{"type":"areaPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D 100% Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "AN16", `{"type":"area3D","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "AF32", `{"type":"area3DStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - assert.NoError(t, xlsx.AddChart("Sheet2", "AN32", `{"type":"area3DPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) - - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestAddChart.xlsx"))) + assert.NoError(t, f.AddChart("Sheet2", "AF1", `{"type":"area","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "AN1", `{"type":"areaStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "AF16", `{"type":"areaPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 2D 100% Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "AN16", `{"type":"area3D","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "AF32", `{"type":"area3DStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + assert.NoError(t, f.AddChart("Sheet2", "AN32", `{"type":"area3DPercentStacked","series":[{"name":"Sheet1!$A$30","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$30:$D$30"},{"name":"Sheet1!$A$31","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$31:$D$31"},{"name":"Sheet1!$A$32","categories":"Sheet1!$B$29:$D$29","values":"Sheet1!$B$32:$D$32"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Fruit 3D 100% Stacked Area Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`)) + + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddChart.xlsx"))) } func TestInsertCol(t *testing.T) { - xlsx := NewFile() - sheet1 := xlsx.GetSheetName(1) + f := NewFile() + sheet1 := f.GetSheetName(1) - fillCells(xlsx, sheet1, 10, 10) + fillCells(f, sheet1, 10, 10) - xlsx.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External") - xlsx.MergeCell(sheet1, "A1", "C3") + f.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External") + f.MergeCell(sheet1, "A1", "C3") - err := xlsx.AutoFilter(sheet1, "A2", "B2", `{"column":"B","expression":"x != blanks"}`) + err := f.AutoFilter(sheet1, "A2", "B2", `{"column":"B","expression":"x != blanks"}`) if !assert.NoError(t, err) { t.FailNow() } - assert.NoError(t, xlsx.InsertCol(sheet1, "A")) + assert.NoError(t, f.InsertCol(sheet1, "A")) // Test insert column with illegal cell coordinates. - assert.EqualError(t, xlsx.InsertCol("Sheet1", "*"), `invalid column name "*"`) + assert.EqualError(t, f.InsertCol("Sheet1", "*"), `invalid column name "*"`) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestInsertCol.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestInsertCol.xlsx"))) } func TestRemoveCol(t *testing.T) { - xlsx := NewFile() - sheet1 := xlsx.GetSheetName(1) + f := NewFile() + sheet1 := f.GetSheetName(1) - fillCells(xlsx, sheet1, 10, 15) + fillCells(f, sheet1, 10, 15) - xlsx.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External") - xlsx.SetCellHyperLink(sheet1, "C5", "https://github.com", "External") + f.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External") + f.SetCellHyperLink(sheet1, "C5", "https://github.com", "External") - xlsx.MergeCell(sheet1, "A1", "B1") - xlsx.MergeCell(sheet1, "A2", "B2") + f.MergeCell(sheet1, "A1", "B1") + f.MergeCell(sheet1, "A2", "B2") - assert.NoError(t, xlsx.RemoveCol(sheet1, "A")) - assert.NoError(t, xlsx.RemoveCol(sheet1, "A")) + assert.NoError(t, f.RemoveCol(sheet1, "A")) + assert.NoError(t, f.RemoveCol(sheet1, "A")) // Test remove column with illegal cell coordinates. - assert.EqualError(t, xlsx.RemoveCol("Sheet1", "*"), `invalid column name "*"`) + assert.EqualError(t, f.RemoveCol("Sheet1", "*"), `invalid column name "*"`) // Test remove column on not exists worksheet. - assert.EqualError(t, xlsx.RemoveCol("SheetN", "B"), "sheet SheetN is not exist") + assert.EqualError(t, f.RemoveCol("SheetN", "B"), "sheet SheetN is not exist") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestRemoveCol.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestRemoveCol.xlsx"))) } func TestSetPane(t *testing.T) { - xlsx := NewFile() - xlsx.SetPanes("Sheet1", `{"freeze":false,"split":false}`) - xlsx.NewSheet("Panes 2") - xlsx.SetPanes("Panes 2", `{"freeze":true,"split":false,"x_split":1,"y_split":0,"top_left_cell":"B1","active_pane":"topRight","panes":[{"sqref":"K16","active_cell":"K16","pane":"topRight"}]}`) - xlsx.NewSheet("Panes 3") - xlsx.SetPanes("Panes 3", `{"freeze":false,"split":true,"x_split":3270,"y_split":1800,"top_left_cell":"N57","active_pane":"bottomLeft","panes":[{"sqref":"I36","active_cell":"I36"},{"sqref":"G33","active_cell":"G33","pane":"topRight"},{"sqref":"J60","active_cell":"J60","pane":"bottomLeft"},{"sqref":"O60","active_cell":"O60","pane":"bottomRight"}]}`) - xlsx.NewSheet("Panes 4") - xlsx.SetPanes("Panes 4", `{"freeze":true,"split":false,"x_split":0,"y_split":9,"top_left_cell":"A34","active_pane":"bottomLeft","panes":[{"sqref":"A11:XFD11","active_cell":"A11","pane":"bottomLeft"}]}`) - xlsx.SetPanes("Panes 4", "") - - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetPane.xlsx"))) + f := NewFile() + f.SetPanes("Sheet1", `{"freeze":false,"split":false}`) + f.NewSheet("Panes 2") + f.SetPanes("Panes 2", `{"freeze":true,"split":false,"x_split":1,"y_split":0,"top_left_cell":"B1","active_pane":"topRight","panes":[{"sqref":"K16","active_cell":"K16","pane":"topRight"}]}`) + f.NewSheet("Panes 3") + f.SetPanes("Panes 3", `{"freeze":false,"split":true,"x_split":3270,"y_split":1800,"top_left_cell":"N57","active_pane":"bottomLeft","panes":[{"sqref":"I36","active_cell":"I36"},{"sqref":"G33","active_cell":"G33","pane":"topRight"},{"sqref":"J60","active_cell":"J60","pane":"bottomLeft"},{"sqref":"O60","active_cell":"O60","pane":"bottomRight"}]}`) + f.NewSheet("Panes 4") + f.SetPanes("Panes 4", `{"freeze":true,"split":false,"x_split":0,"y_split":9,"top_left_cell":"A34","active_pane":"bottomLeft","panes":[{"sqref":"A11:XFD11","active_cell":"A11","pane":"bottomLeft"}]}`) + f.SetPanes("Panes 4", "") + + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetPane.xlsx"))) } func TestConditionalFormat(t *testing.T) { - xlsx := NewFile() - sheet1 := xlsx.GetSheetName(1) + f := NewFile() + sheet1 := f.GetSheetName(1) - fillCells(xlsx, sheet1, 10, 15) + fillCells(f, sheet1, 10, 15) var format1, format2, format3 int var err error // Rose format for bad conditional. - format1, err = xlsx.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`) + format1, err = f.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`) if !assert.NoError(t, err) { t.FailNow() } // Light yellow format for neutral conditional. - format2, err = xlsx.NewConditionalStyle(`{"fill":{"type":"pattern","color":["#FEEAA0"],"pattern":1}}`) + format2, err = f.NewConditionalStyle(`{"fill":{"type":"pattern","color":["#FEEAA0"],"pattern":1}}`) if !assert.NoError(t, err) { t.FailNow() } // Light green format for good conditional. - format3, err = xlsx.NewConditionalStyle(`{"font":{"color":"#09600B"},"fill":{"type":"pattern","color":["#C7EECF"],"pattern":1}}`) + format3, err = f.NewConditionalStyle(`{"font":{"color":"#09600B"},"fill":{"type":"pattern","color":["#C7EECF"],"pattern":1}}`) if !assert.NoError(t, err) { t.FailNow() } // Color scales: 2 color. - xlsx.SetConditionalFormat(sheet1, "A1:A10", `[{"type":"2_color_scale","criteria":"=","min_type":"min","max_type":"max","min_color":"#F8696B","max_color":"#63BE7B"}]`) + f.SetConditionalFormat(sheet1, "A1:A10", `[{"type":"2_color_scale","criteria":"=","min_type":"min","max_type":"max","min_color":"#F8696B","max_color":"#63BE7B"}]`) // Color scales: 3 color. - xlsx.SetConditionalFormat(sheet1, "B1:B10", `[{"type":"3_color_scale","criteria":"=","min_type":"min","mid_type":"percentile","max_type":"max","min_color":"#F8696B","mid_color":"#FFEB84","max_color":"#63BE7B"}]`) + f.SetConditionalFormat(sheet1, "B1:B10", `[{"type":"3_color_scale","criteria":"=","min_type":"min","mid_type":"percentile","max_type":"max","min_color":"#F8696B","mid_color":"#FFEB84","max_color":"#63BE7B"}]`) // Hightlight cells rules: between... - xlsx.SetConditionalFormat(sheet1, "C1:C10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format1)) + f.SetConditionalFormat(sheet1, "C1:C10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format1)) // Hightlight cells rules: Greater Than... - xlsx.SetConditionalFormat(sheet1, "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format3)) + f.SetConditionalFormat(sheet1, "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format3)) // Hightlight cells rules: Equal To... - xlsx.SetConditionalFormat(sheet1, "E1:E10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d}]`, format3)) + f.SetConditionalFormat(sheet1, "E1:E10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d}]`, format3)) // Hightlight cells rules: Not Equal To... - xlsx.SetConditionalFormat(sheet1, "F1:F10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, format2)) + f.SetConditionalFormat(sheet1, "F1:F10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, format2)) // Hightlight cells rules: Duplicate Values... - xlsx.SetConditionalFormat(sheet1, "G1:G10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format2)) + f.SetConditionalFormat(sheet1, "G1:G10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format2)) // Top/Bottom rules: Top 10%. - xlsx.SetConditionalFormat(sheet1, "H1:H10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6","percent":true}]`, format1)) + f.SetConditionalFormat(sheet1, "H1:H10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6","percent":true}]`, format1)) // Top/Bottom rules: Above Average... - xlsx.SetConditionalFormat(sheet1, "I1:I10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": true}]`, format3)) + f.SetConditionalFormat(sheet1, "I1:I10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": true}]`, format3)) // Top/Bottom rules: Below Average... - xlsx.SetConditionalFormat(sheet1, "J1:J10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": false}]`, format1)) + f.SetConditionalFormat(sheet1, "J1:J10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": false}]`, format1)) // Data Bars: Gradient Fill. - xlsx.SetConditionalFormat(sheet1, "K1:K10", `[{"type":"data_bar", "criteria":"=", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`) + f.SetConditionalFormat(sheet1, "K1:K10", `[{"type":"data_bar", "criteria":"=", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`) // Use a formula to determine which cells to format. - xlsx.SetConditionalFormat(sheet1, "L1:L10", fmt.Sprintf(`[{"type":"formula", "criteria":"L2<3", "format":%d}]`, format1)) + f.SetConditionalFormat(sheet1, "L1:L10", fmt.Sprintf(`[{"type":"formula", "criteria":"L2<3", "format":%d}]`, format1)) // Test set invalid format set in conditional format - xlsx.SetConditionalFormat(sheet1, "L1:L10", "") + f.SetConditionalFormat(sheet1, "L1:L10", "") - err = xlsx.SaveAs(filepath.Join("test", "TestConditionalFormat.xlsx")) + err = f.SaveAs(filepath.Join("test", "TestConditionalFormat.xlsx")) if !assert.NoError(t, err) { t.FailNow() } // Set conditional format with illegal valid type. - xlsx.SetConditionalFormat(sheet1, "K1:K10", `[{"type":"", "criteria":"=", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`) + f.SetConditionalFormat(sheet1, "K1:K10", `[{"type":"", "criteria":"=", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`) // Set conditional format with illegal criteria type. - xlsx.SetConditionalFormat(sheet1, "K1:K10", `[{"type":"data_bar", "criteria":"", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`) + f.SetConditionalFormat(sheet1, "K1:K10", `[{"type":"data_bar", "criteria":"", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`) // Set conditional format with file without dxfs element shold not return error. - xlsx, err = OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err = OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - _, err = xlsx.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`) + _, err = f.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`) if !assert.NoError(t, err) { t.FailNow() } } func TestConditionalFormatError(t *testing.T) { - xlsx := NewFile() - sheet1 := xlsx.GetSheetName(1) + f := NewFile() + sheet1 := f.GetSheetName(1) - fillCells(xlsx, sheet1, 10, 15) + fillCells(f, sheet1, 10, 15) // Set conditional format with illegal JSON string should return error - _, err := xlsx.NewConditionalStyle("") + _, err := f.NewConditionalStyle("") if !assert.EqualError(t, err, "unexpected end of JSON input") { t.FailNow() } } func TestSharedStrings(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "SharedStrings.xlsx")) + f, err := OpenFile(filepath.Join("test", "SharedStrings.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - xlsx.GetRows("Sheet1") + f.GetRows("Sheet1") } func TestSetSheetRow(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - xlsx.SetSheetRow("Sheet1", "B27", &[]interface{}{"cell", nil, int32(42), float64(42), time.Now().UTC()}) + f.SetSheetRow("Sheet1", "B27", &[]interface{}{"cell", nil, int32(42), float64(42), time.Now().UTC()}) - assert.EqualError(t, xlsx.SetSheetRow("Sheet1", "", &[]interface{}{"cell", nil, 2}), + assert.EqualError(t, f.SetSheetRow("Sheet1", "", &[]interface{}{"cell", nil, 2}), `cannot convert cell "" to coordinates: invalid cell name ""`) - assert.EqualError(t, xlsx.SetSheetRow("Sheet1", "B27", []interface{}{}), `pointer to slice expected`) - assert.EqualError(t, xlsx.SetSheetRow("Sheet1", "B27", &xlsx), `pointer to slice expected`) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestSetSheetRow.xlsx"))) + assert.EqualError(t, f.SetSheetRow("Sheet1", "B27", []interface{}{}), `pointer to slice expected`) + assert.EqualError(t, f.SetSheetRow("Sheet1", "B27", &f), `pointer to slice expected`) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetSheetRow.xlsx"))) } func TestOutlineLevel(t *testing.T) { - xlsx := NewFile() - xlsx.NewSheet("Sheet2") - xlsx.SetColOutlineLevel("Sheet1", "D", 4) - xlsx.GetColOutlineLevel("Sheet1", "D") - xlsx.GetColOutlineLevel("Shee2", "A") - xlsx.SetColWidth("Sheet2", "A", "D", 13) - xlsx.SetColOutlineLevel("Sheet2", "B", 2) - xlsx.SetRowOutlineLevel("Sheet1", 2, 250) + f := NewFile() + f.NewSheet("Sheet2") + f.SetColOutlineLevel("Sheet1", "D", 4) + f.GetColOutlineLevel("Sheet1", "D") + f.GetColOutlineLevel("Shee2", "A") + f.SetColWidth("Sheet2", "A", "D", 13) + f.SetColOutlineLevel("Sheet2", "B", 2) + f.SetRowOutlineLevel("Sheet1", 2, 250) // Test set and get column outline level with illegal cell coordinates. - assert.EqualError(t, xlsx.SetColOutlineLevel("Sheet1", "*", 1), `invalid column name "*"`) - _, err := xlsx.GetColOutlineLevel("Sheet1", "*") + assert.EqualError(t, f.SetColOutlineLevel("Sheet1", "*", 1), `invalid column name "*"`) + _, err := f.GetColOutlineLevel("Sheet1", "*") assert.EqualError(t, err, `invalid column name "*"`) // Test set column outline level on not exists worksheet. - assert.EqualError(t, xlsx.SetColOutlineLevel("SheetN", "E", 2), "sheet SheetN is not exist") + assert.EqualError(t, f.SetColOutlineLevel("SheetN", "E", 2), "sheet SheetN is not exist") - assert.EqualError(t, xlsx.SetRowOutlineLevel("Sheet1", 0, 1), "invalid row number 0") - level, err := xlsx.GetRowOutlineLevel("Sheet1", 2) + assert.EqualError(t, f.SetRowOutlineLevel("Sheet1", 0, 1), "invalid row number 0") + level, err := f.GetRowOutlineLevel("Sheet1", 2) assert.NoError(t, err) assert.Equal(t, uint8(250), level) - _, err = xlsx.GetRowOutlineLevel("Sheet1", 0) + _, err = f.GetRowOutlineLevel("Sheet1", 0) assert.EqualError(t, err, `invalid row number 0`) - level, err = xlsx.GetRowOutlineLevel("Sheet1", 10) + level, err = f.GetRowOutlineLevel("Sheet1", 10) assert.NoError(t, err) assert.Equal(t, uint8(0), level) - err = xlsx.SaveAs(filepath.Join("test", "TestOutlineLevel.xlsx")) + err = f.SaveAs(filepath.Join("test", "TestOutlineLevel.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - xlsx, err = OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err = OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } - xlsx.SetColOutlineLevel("Sheet2", "B", 2) + f.SetColOutlineLevel("Sheet2", "B", 2) } func TestThemeColor(t *testing.T) { @@ -1268,44 +1276,44 @@ func TestHSL(t *testing.T) { } func TestSearchSheet(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "SharedStrings.xlsx")) + f, err := OpenFile(filepath.Join("test", "SharedStrings.xlsx")) if !assert.NoError(t, err) { t.FailNow() } // Test search in a not exists worksheet. - t.Log(xlsx.SearchSheet("Sheet4", "")) + t.Log(f.SearchSheet("Sheet4", "")) // Test search a not exists value. - t.Log(xlsx.SearchSheet("Sheet1", "X")) - t.Log(xlsx.SearchSheet("Sheet1", "A")) + t.Log(f.SearchSheet("Sheet1", "X")) + t.Log(f.SearchSheet("Sheet1", "A")) // Test search the coordinates where the numerical value in the range of // "0-9" of Sheet1 is described by regular expression: - t.Log(xlsx.SearchSheet("Sheet1", "[0-9]", true)) + t.Log(f.SearchSheet("Sheet1", "[0-9]", true)) } func TestProtectSheet(t *testing.T) { - xlsx := NewFile() - xlsx.ProtectSheet("Sheet1", nil) - xlsx.ProtectSheet("Sheet1", &FormatSheetProtection{ + f := NewFile() + f.ProtectSheet("Sheet1", nil) + f.ProtectSheet("Sheet1", &FormatSheetProtection{ Password: "password", EditScenarios: false, }) - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestProtectSheet.xlsx"))) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestProtectSheet.xlsx"))) // Test protect not exists worksheet. - assert.EqualError(t, xlsx.ProtectSheet("SheetN", nil), "sheet SheetN is not exist") + assert.EqualError(t, f.ProtectSheet("SheetN", nil), "sheet SheetN is not exist") } func TestUnprotectSheet(t *testing.T) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if !assert.NoError(t, err) { t.FailNow() } // Test unprotect not exists worksheet. - assert.EqualError(t, xlsx.UnprotectSheet("SheetN"), "sheet SheetN is not exist") + assert.EqualError(t, f.UnprotectSheet("SheetN"), "sheet SheetN is not exist") - xlsx.UnprotectSheet("Sheet1") - assert.NoError(t, xlsx.SaveAs(filepath.Join("test", "TestUnprotectSheet.xlsx"))) + f.UnprotectSheet("Sheet1") + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestUnprotectSheet.xlsx"))) } func TestSetDefaultTimeStyle(t *testing.T) { @@ -1315,19 +1323,19 @@ func TestSetDefaultTimeStyle(t *testing.T) { } func prepareTestBook1() (*File, error) { - xlsx, err := OpenFile(filepath.Join("test", "Book1.xlsx")) + f, err := OpenFile(filepath.Join("test", "Book1.xlsx")) if err != nil { return nil, err } - err = xlsx.AddPicture("Sheet2", "I9", filepath.Join("test", "images", "excel.jpg"), + err = f.AddPicture("Sheet2", "I9", filepath.Join("test", "images", "excel.jpg"), `{"x_offset": 140, "y_offset": 120, "hyperlink": "#Sheet2!D8", "hyperlink_type": "Location"}`) if err != nil { return nil, err } // Test add picture to worksheet with offset, external hyperlink and positioning. - err = xlsx.AddPicture("Sheet1", "F21", filepath.Join("test", "images", "excel.png"), + err = f.AddPicture("Sheet1", "F21", filepath.Join("test", "images", "excel.png"), `{"x_offset": 10, "y_offset": 10, "hyperlink": "https://github.com/360EntSecGroup-Skylar/excelize", "hyperlink_type": "External", "positioning": "oneCell"}`) if err != nil { return nil, err @@ -1338,52 +1346,52 @@ func prepareTestBook1() (*File, error) { return nil, err } - err = xlsx.AddPictureFromBytes("Sheet1", "Q1", "", "Excel Logo", ".jpg", file) + err = f.AddPictureFromBytes("Sheet1", "Q1", "", "Excel Logo", ".jpg", file) if err != nil { return nil, err } - return xlsx, nil + return f, nil } func prepareTestBook3() (*File, error) { - xlsx := NewFile() - xlsx.NewSheet("Sheet1") - xlsx.NewSheet("XLSXSheet2") - xlsx.NewSheet("XLSXSheet3") - xlsx.SetCellInt("XLSXSheet2", "A23", 56) - xlsx.SetCellStr("Sheet1", "B20", "42") - xlsx.SetActiveSheet(0) - - err := xlsx.AddPicture("Sheet1", "H2", filepath.Join("test", "images", "excel.gif"), + f := NewFile() + f.NewSheet("Sheet1") + f.NewSheet("XLSXSheet2") + f.NewSheet("XLSXSheet3") + f.SetCellInt("XLSXSheet2", "A23", 56) + f.SetCellStr("Sheet1", "B20", "42") + f.SetActiveSheet(0) + + err := f.AddPicture("Sheet1", "H2", filepath.Join("test", "images", "excel.gif"), `{"x_scale": 0.5, "y_scale": 0.5, "positioning": "absolute"}`) if err != nil { return nil, err } - err = xlsx.AddPicture("Sheet1", "C2", filepath.Join("test", "images", "excel.png"), "") + err = f.AddPicture("Sheet1", "C2", filepath.Join("test", "images", "excel.png"), "") if err != nil { return nil, err } - return xlsx, nil + return f, nil } func prepareTestBook4() (*File, error) { - xlsx := NewFile() - xlsx.SetColWidth("Sheet1", "B", "A", 12) - xlsx.SetColWidth("Sheet1", "A", "B", 12) - xlsx.GetColWidth("Sheet1", "A") - xlsx.GetColWidth("Sheet1", "C") + f := NewFile() + f.SetColWidth("Sheet1", "B", "A", 12) + f.SetColWidth("Sheet1", "A", "B", 12) + f.GetColWidth("Sheet1", "A") + f.GetColWidth("Sheet1", "C") - return xlsx, nil + return f, nil } -func fillCells(xlsx *File, sheet string, colCount, rowCount int) { +func fillCells(f *File, sheet string, colCount, rowCount int) { for col := 1; col <= colCount; col++ { for row := 1; row <= rowCount; row++ { cell, _ := CoordinatesToCellName(col, row) - xlsx.SetCellStr(sheet, cell, cell) + f.SetCellStr(sheet, cell, cell) } } } |