diff options
Diffstat (limited to 'excelize_test.go')
| -rw-r--r-- | excelize_test.go | 1097 |
1 files changed, 621 insertions, 476 deletions
diff --git a/excelize_test.go b/excelize_test.go index b1eb03a..1411371 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -7,7 +7,7 @@ import ( _ "image/jpeg" _ "image/png" "io/ioutil" - "reflect" + "os" "strconv" "strings" "testing" @@ -19,9 +19,10 @@ import ( func TestOpenFile(t *testing.T) { // Test update a XLSX file. xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + // Test get all the rows in a not exists worksheet. xlsx.GetRows("Sheet4") // Test get all the rows in a worksheet. @@ -104,10 +105,7 @@ func TestOpenFile(t *testing.T) { } for _, test := range booltest { xlsx.SetCellValue("Sheet2", "F16", test.value) - value := xlsx.GetCellValue("Sheet2", "F16") - if value != test.expected { - t.Errorf(`Expecting result of xlsx.SetCellValue("Sheet2", "F16", %v) to be %v (false), got: %s `, test.value, test.expected, value) - } + assert.Equal(t, test.expected, xlsx.GetCellValue("Sheet2", "F16")) } xlsx.SetCellValue("Sheet2", "G2", nil) xlsx.SetCellValue("Sheet2", "G4", time.Now()) @@ -128,93 +126,106 @@ func TestOpenFile(t *testing.T) { for i := 1; i <= 300; i++ { xlsx.SetCellStr("Sheet3", "c"+strconv.Itoa(i), strconv.Itoa(i)) } - err = xlsx.Save() - if err != nil { - t.Error(err) - } - // Test write file to not exist directory. - err = xlsx.SaveAs("") - if err != nil { - t.Log(err) + assert.NoError(t, xlsx.SaveAs("./test/TestOpenFile.xlsx")) +} + +func TestSaveAsWrongPath(t *testing.T) { + xlsx, err := OpenFile("./test/Book1.xlsx") + if assert.NoError(t, err) { + // Test write file to not exist directory. + err = xlsx.SaveAs("") + if assert.Error(t, err) { + assert.True(t, os.IsNotExist(err), "Error: %v: Expected os.IsNotExists(err) == true", err) + } } } func TestAddPicture(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + // Test add picture to worksheet with offset and location hyperlink. - err = xlsx.AddPicture("Sheet2", "I9", "./test/images/excel.jpg", `{"x_offset": 140, "y_offset": 120, "hyperlink": "#Sheet2!D8", "hyperlink_type": "Location"}`) - if err != nil { - t.Error(err) + err = xlsx.AddPicture("Sheet2", "I9", "./test/images/excel.jpg", + `{"x_offset": 140, "y_offset": 120, "hyperlink": "#Sheet2!D8", "hyperlink_type": "Location"}`) + if !assert.NoError(t, err) { + t.FailNow() } + // Test add picture to worksheet with offset, external hyperlink and positioning. - err = xlsx.AddPicture("Sheet1", "F21", "./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 { - t.Error(err) + err = xlsx.AddPicture("Sheet1", "F21", "./test/images/excel.png", + `{"x_offset": 10, "y_offset": 10, "hyperlink": "https://github.com/360EntSecGroup-Skylar/excelize", "hyperlink_type": "External", "positioning": "oneCell"}`) + if !assert.NoError(t, err) { + t.FailNow() } + + file, err := ioutil.ReadFile("./test/images/excel.jpg") + if !assert.NoError(t, err) { + t.FailNow() + } + + // Test add picture to worksheet from bytes. + err = xlsx.AddPictureFromBytes("Sheet1", "Q1", "", "Excel Logo", ".jpg", file) + assert.NoError(t, err) + + // Test write file to given path. + err = xlsx.SaveAs("./test/TestAddPicture.xlsx") + assert.NoError(t, err) +} + +func TestAddPictureErrors(t *testing.T) { + xlsx, err := OpenFile("./test/Book1.xlsx") + if !assert.NoError(t, err) { + t.FailNow() + } + // Test add picture to worksheet with invalid file path. - err = xlsx.AddPicture("Sheet1", "G21", "./test/images/excel.icon", "") - if err != nil { - t.Log(err) + err = xlsx.AddPicture("Sheet1", "G21", "./test/not_exists_dir/not_exists.icon", "") + if assert.Error(t, err) { + assert.True(t, os.IsNotExist(err), "Expected os.IsNotExist(err) == true") } + // Test add picture to worksheet with unsupport file type. err = xlsx.AddPicture("Sheet1", "G21", "./test/Book1.xlsx", "") - if err != nil { - t.Log(err) - } + assert.EqualError(t, err, "unsupported image extension") + err = xlsx.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", "jpg", make([]byte, 1)) - if err != nil { - t.Log(err) - } + assert.EqualError(t, err, "unsupported image extension") + // Test add picture to worksheet with invalid file data. err = xlsx.AddPictureFromBytes("Sheet1", "G21", "", "Excel Logo", ".jpg", make([]byte, 1)) - if err != nil { - t.Log(err) - } - file, err := ioutil.ReadFile("./test/images/excel.jpg") - if err != nil { - t.Error(err) - } - // Test add picture to worksheet from bytes. - err = xlsx.AddPictureFromBytes("Sheet1", "Q1", "", "Excel Logo", ".jpg", file) - if err != nil { - t.Log(err) - } - // Test write file to given path. - err = xlsx.SaveAs("./test/Book2.xlsx") - if err != nil { - t.Error(err) - } + assert.EqualError(t, err, "image: unknown format") } func TestBrokenFile(t *testing.T) { // Test write file with broken file struct. xlsx := File{} - err := xlsx.Save() - if err != nil { - t.Log(err) - } - // Test write file with broken file struct with given path. - err = xlsx.SaveAs("./test/Book3.xlsx") - if err != nil { - t.Log(err) - } - // Test set active sheet without BookViews and Sheets maps in xl/workbook.xml. - f3, err := OpenFile("./test/badWorkbook.xlsx") - f3.GetActiveSheetIndex() - f3.SetActiveSheet(2) - if err != nil { - t.Log(err) - } + t.Run("SaveWithoutName", func(t *testing.T) { + assert.EqualError(t, xlsx.Save(), "no path defined for file, consider File.WriteTo or File.Write") + }) - // Test open a XLSX file with given illegal path. - _, err = OpenFile("./test/Book.xlsx") - if err != nil { - t.Log(err) - } + t.Run("SaveAsEmptyStruct", func(t *testing.T) { + // Test write file with broken file struct with given path. + assert.NoError(t, xlsx.SaveAs("./test/TestBrokenFile.SaveAsEmptyStruct.xlsx")) + }) + + t.Run("OpenBadWorkbook", func(t *testing.T) { + // Test set active sheet without BookViews and Sheets maps in xl/workbook.xml. + f3, err := OpenFile("./test/BadWorkbook.xlsx") + f3.GetActiveSheetIndex() + f3.SetActiveSheet(2) + assert.NoError(t, err) + }) + + t.Run("OpenNotExistsFile", func(t *testing.T) { + // Test open a XLSX file with given illegal path. + _, err := OpenFile("./test/NotExistsFile.xlsx") + if assert.Error(t, err) { + assert.True(t, os.IsNotExist(err), "Expected os.IsNotExists(err) == true") + } + }) } func TestNewFile(t *testing.T) { @@ -226,25 +237,26 @@ func TestNewFile(t *testing.T) { xlsx.SetCellInt("XLSXSheet2", "A23", 56) xlsx.SetCellStr("Sheet1", "B20", "42") xlsx.SetActiveSheet(0) + // Test add picture to sheet with scaling and positioning. err := xlsx.AddPicture("Sheet1", "H2", "./test/images/excel.gif", `{"x_scale": 0.5, "y_scale": 0.5, "positioning": "absolute"}`) - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + // Test add picture to worksheet without formatset. err = xlsx.AddPicture("Sheet1", "C2", "./test/images/excel.png", "") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + // Test add picture to worksheet with invalid formatset. err = xlsx.AddPicture("Sheet1", "C2", "./test/images/excel.png", `{`) - if err != nil { - t.Log(err) - } - err = xlsx.SaveAs("./test/Book3.xlsx") - if err != nil { - t.Error(err) + if !assert.Error(t, err) { + t.FailNow() } + + assert.NoError(t, xlsx.SaveAs("./test/TestNewFile.xlsx")) } func TestColWidth(t *testing.T) { @@ -253,7 +265,7 @@ func TestColWidth(t *testing.T) { xlsx.SetColWidth("Sheet1", "A", "B", 12) xlsx.GetColWidth("Sheet1", "A") xlsx.GetColWidth("Sheet1", "C") - err := xlsx.SaveAs("./test/Book4.xlsx") + err := xlsx.SaveAs("./test/TestColWidth.xlsx") if err != nil { t.Error(err) } @@ -266,9 +278,9 @@ func TestRowHeight(t *testing.T) { xlsx.SetRowHeight("Sheet1", 4, 90) t.Log(xlsx.GetRowHeight("Sheet1", 1)) t.Log(xlsx.GetRowHeight("Sheet1", 0)) - err := xlsx.SaveAs("./test/Book5.xlsx") - if err != nil { - t.Error(err) + err := xlsx.SaveAs("./test/TestRowHeight.xlsx") + if !assert.NoError(t, err) { + t.FailNow() } convertColWidthToPixels(0) } @@ -286,17 +298,15 @@ func TestSetCellHyperLink(t *testing.T) { xlsx.SetCellHyperLink("Sheet2", "D6", "Sheet1!D8", "Location") xlsx.SetCellHyperLink("Sheet2", "C3", "Sheet1!D8", "") xlsx.SetCellHyperLink("Sheet2", "", "Sheet1!D60", "Location") - err = xlsx.Save() - if err != nil { - t.Error(err) - } + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellHyperLink.xlsx")) } func TestGetCellHyperLink(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + link, target := xlsx.GetCellHyperLink("Sheet1", "") t.Log(link, target) link, target = xlsx.GetCellHyperLink("Sheet1", "B19") @@ -309,51 +319,58 @@ func TestGetCellHyperLink(t *testing.T) { func TestSetCellFormula(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) + 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)") // Test set cell formula with illegal rows number. xlsx.SetCellFormula("Sheet1", "C", "SUM(Sheet2!D2,Sheet2!D9)") - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellFormula.xlsx")) } func TestSetSheetBackground(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) - } - err = xlsx.SetSheetBackground("Sheet2", "./test/images/background.png") - if err != nil { - t.Log(err) - } - err = xlsx.SetSheetBackground("Sheet2", "./test/Book1.xlsx") - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + err = xlsx.SetSheetBackground("Sheet2", "./test/images/background.jpg") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + err = xlsx.SetSheetBackground("Sheet2", "./test/images/background.jpg") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } - err = xlsx.Save() - if err != nil { - t.Error(err) + + assert.NoError(t, xlsx.SaveAs("./test/TestSetSheetBackground.xlsx")) +} + +func TestSetSheetBackgroundErrors(t *testing.T) { + xlsx, err := OpenFile("./test/Book1.xlsx") + if !assert.NoError(t, err) { + t.FailNow() + } + + err = xlsx.SetSheetBackground("Sheet2", "./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", "./test/Book1.xlsx") + assert.EqualError(t, err, "unsupported image extension") } func TestMergeCell(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.MergeCell("Sheet1", "D9", "D9") xlsx.MergeCell("Sheet1", "D9", "E9") xlsx.MergeCell("Sheet1", "H14", "G13") @@ -370,10 +387,8 @@ func TestMergeCell(t *testing.T) { xlsx.GetCellValue("Sheet1", "H11") xlsx.GetCellValue("Sheet2", "A6") // Merged cell ref is single coordinate. xlsx.GetCellFormula("Sheet1", "G12") - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestMergeCell.xlsx")) } func TestGetMergeCells(t *testing.T) { @@ -405,8 +420,8 @@ func TestGetMergeCells(t *testing.T) { } xlsx, err := OpenFile("./test/MergeCell.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } mergeCells := xlsx.GetMergeCells("Sheet1") @@ -430,84 +445,90 @@ func TestGetMergeCells(t *testing.T) { } func TestSetCellStyleAlignment(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, 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}}`) - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellStyle("Sheet1", "A22", "A22", style) // Test set cell style with given illegal rows number. xlsx.SetCellStyle("Sheet1", "A", "A22", style) xlsx.SetCellStyle("Sheet1", "A22", "A", style) // Test get cell style with given illegal rows number. xlsx.GetCellStyle("Sheet1", "A") - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleAlignment.xlsx")) } func TestSetCellStyleBorder(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) - } - var style int - // Test set border with invalid style parameter. - style, err = xlsx.NewStyle("") - if err != nil { - t.Log(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } - xlsx.SetCellStyle("Sheet1", "J21", "L25", style) - // Test set border with invalid style index number. - style, 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}]}`) - if err != nil { - t.Log(err) - } - xlsx.SetCellStyle("Sheet1", "J21", "L25", style) + 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}]}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.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}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.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}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.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}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.SetCellStyle("Sheet1", "O22", "O22", style) - err = xlsx.Save() - if err != nil { - t.Error(err) + + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleBorder.xlsx")) +} + +func TestSetCellStyleBorderErrors(t *testing.T) { + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() + } + + // Set border with invalid style parameter. + _, err = xlsx.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}]}`) + if !assert.NoError(t, err) { + t.FailNow() } } func TestSetCellStyleNumberFormat(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } + // Test only set fill and number format for a cell. col := []string{"L", "M", "N", "O", "P"} data := []int{0, 1, 2, 3, 4, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49} @@ -523,8 +544,8 @@ func TestSetCellStyleNumberFormat(t *testing.T) { xlsx.SetCellValue("Sheet2", c, val) } style, err := xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":5},"number_format": ` + strconv.Itoa(d) + `}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.SetCellStyle("Sheet2", c, c, style) t.Log(xlsx.GetCellValue("Sheet2", c)) @@ -532,72 +553,74 @@ func TestSetCellStyleNumberFormat(t *testing.T) { } var style int style, err = xlsx.NewStyle(`{"number_format":-1}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.SetCellStyle("Sheet2", "L33", "L33", style) - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleNumberFormat.xlsx")) } func TestSetCellStyleCurrencyNumberFormat(t *testing.T) { - xlsx, err := OpenFile("./test/Book3.xlsx") - if err != nil { - t.Error(err) - } - xlsx.SetCellValue("Sheet1", "A1", 56) - xlsx.SetCellValue("Sheet1", "A2", -32.3) - var style int - style, err = xlsx.NewStyle(`{"number_format": 188, "decimal_places": -1}`) - if err != nil { - t.Log(err) - } - xlsx.SetCellStyle("Sheet1", "A1", "A1", style) - style, err = xlsx.NewStyle(`{"number_format": 188, "decimal_places": 31, "negred": true}`) - if err != nil { - t.Log(err) - } - xlsx.SetCellStyle("Sheet1", "A2", "A2", style) + t.Run("TestBook3", func(t *testing.T) { + xlsx, err := prepareTestBook3() + if !assert.NoError(t, err) { + t.FailNow() + } - err = xlsx.Save() - if err != nil { - t.Log(err) - } + xlsx.SetCellValue("Sheet1", "A1", 56) + xlsx.SetCellValue("Sheet1", "A2", -32.3) + var style int + style, err = xlsx.NewStyle(`{"number_format": 188, "decimal_places": -1}`) + if !assert.NoError(t, err) { + t.FailNow() + } - xlsx, err = OpenFile("./test/Book4.xlsx") - if err != nil { - t.Log(err) - } - xlsx.SetCellValue("Sheet1", "A1", 42920.5) - xlsx.SetCellValue("Sheet1", "A2", 42920.5) + xlsx.SetCellStyle("Sheet1", "A1", "A1", style) + style, err = xlsx.NewStyle(`{"number_format": 188, "decimal_places": 31, "negred": true}`) + if !assert.NoError(t, err) { + t.FailNow() + } - _, err = xlsx.NewStyle(`{"number_format": 26, "lang": "zh-tw"}`) - if err != nil { - t.Log(err) - } - style, err = xlsx.NewStyle(`{"number_format": 27}`) - if err != nil { - t.Log(err) - } - xlsx.SetCellStyle("Sheet1", "A1", "A1", style) - style, err = xlsx.NewStyle(`{"number_format": 31, "lang": "ko-kr"}`) - if err != nil { - t.Log(err) - } - xlsx.SetCellStyle("Sheet1", "A2", "A2", style) + xlsx.SetCellStyle("Sheet1", "A2", "A2", style) - style, err = xlsx.NewStyle(`{"number_format": 71, "lang": "th-th"}`) - if err != nil { - t.Log(err) - } - xlsx.SetCellStyle("Sheet1", "A2", "A2", style) + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleCurrencyNumberFormat.TestBook3.xlsx")) + }) - err = xlsx.Save() - if err != nil { - t.Error(err) - } + t.Run("TestBook4", func(t *testing.T) { + xlsx, err := prepareTestBook4() + if !assert.NoError(t, err) { + t.FailNow() + } + xlsx.SetCellValue("Sheet1", "A1", 42920.5) + xlsx.SetCellValue("Sheet1", "A2", 42920.5) + + _, err = xlsx.NewStyle(`{"number_format": 26, "lang": "zh-tw"}`) + if !assert.NoError(t, err) { + t.FailNow() + } + + style, err := xlsx.NewStyle(`{"number_format": 27}`) + if !assert.NoError(t, err) { + t.FailNow() + } + + xlsx.SetCellStyle("Sheet1", "A1", "A1", style) + style, err = xlsx.NewStyle(`{"number_format": 31, "lang": "ko-kr"}`) + if !assert.NoError(t, err) { + t.FailNow() + } + + xlsx.SetCellStyle("Sheet1", "A2", "A2", style) + + style, err = xlsx.NewStyle(`{"number_format": 71, "lang": "th-th"}`) + if !assert.NoError(t, err) { + t.FailNow() + } + xlsx.SetCellStyle("Sheet1", "A2", "A2", style) + + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleCurrencyNumberFormat.TestBook4.xlsx")) + }) } func TestSetCellStyleCustomNumberFormat(t *testing.T) { @@ -614,148 +637,152 @@ func TestSetCellStyleCustomNumberFormat(t *testing.T) { t.Log(err) } xlsx.SetCellStyle("Sheet1", "A2", "A2", style) - err = xlsx.SaveAs("./test/Book_custom_number_format.xlsx") - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleCustomNumberFormat.xlsx")) } func TestSetCellStyleFill(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, 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}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.SetCellStyle("Sheet1", "O23", "O23", style) style, err = xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF"],"shading":1}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.SetCellStyle("Sheet1", "O23", "O23", style) style, err = xlsx.NewStyle(`{"fill":{"type":"pattern","color":[],"pattern":1}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.SetCellStyle("Sheet1", "O23", "O23", style) style, err = xlsx.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":19}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } xlsx.SetCellStyle("Sheet1", "O23", "O23", style) - err = xlsx.Save() - if err != nil { - t.Error(err) - } + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleFill.xlsx")) } func TestSetCellStyleFont(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, 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"}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellStyle("Sheet2", "A1", "A1", style) style, err = xlsx.NewStyle(`{"font":{"italic":true,"underline":"double"}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellStyle("Sheet2", "A2", "A2", style) style, err = xlsx.NewStyle(`{"font":{"bold":true}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellStyle("Sheet2", "A3", "A3", style) style, err = xlsx.NewStyle(`{"font":{"bold":true,"family":"","size":0,"color":"","underline":""}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellStyle("Sheet2", "A4", "A4", style) style, err = xlsx.NewStyle(`{"font":{"color":"#777777"}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellStyle("Sheet2", "A5", "A5", style) - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSetCellStyleFont.xlsx")) } func TestSetCellStyleProtection(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } + var style int style, err = xlsx.NewStyle(`{"protection":{"hidden":true, "locked":true}}`) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellStyle("Sheet2", "A6", "A6", style) - err = xlsx.Save() - if err != nil { - t.Error(err) + err = xlsx.SaveAs("./test/TestSetCellStyleProtection.xlsx") + if !assert.NoError(t, err) { + t.FailNow() } } func TestSetDeleteSheet(t *testing.T) { - xlsx, err := OpenFile("./test/Book3.xlsx") - if err != nil { - t.Error(err) - } - xlsx.DeleteSheet("XLSXSheet3") - err = xlsx.Save() - if err != nil { - t.Error(err) - } - xlsx, err = OpenFile("./test/Book4.xlsx") - if err != nil { - t.Error(err) - } - xlsx.DeleteSheet("Sheet1") - xlsx.AddComment("Sheet1", "A1", "") - xlsx.AddComment("Sheet1", "A1", `{"author":"Excelize: ","text":"This is a comment."}`) - err = xlsx.SaveAs("./test/Book_delete_sheet.xlsx") - if err != nil { - t.Error(err) - } + t.Run("TestBook3", func(t *testing.T) { + xlsx, err := prepareTestBook3() + if !assert.NoError(t, err) { + t.FailNow() + } + + xlsx.DeleteSheet("XLSXSheet3") + assert.NoError(t, xlsx.SaveAs("./test/TestSetDeleteSheet.TestBook3.xlsx")) + }) + + t.Run("TestBook4", func(t *testing.T) { + xlsx, 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("./test/TestSetDeleteSheet.TestBook4.xlsx")) + }) } func TestGetPicture(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Log(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } + file, raw := xlsx.GetPicture("Sheet1", "F21") if file == "" { err = ioutil.WriteFile(file, raw, 0644) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } } + // Try to get picture from a worksheet that doesn't contain any images. file, raw = xlsx.GetPicture("Sheet3", "I9") if file != "" { err = ioutil.WriteFile(file, raw, 0644) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } } // Try to get picture from a cell that doesn't contain an image. @@ -768,142 +795,149 @@ func TestGetPicture(t *testing.T) { } func TestSheetVisibility(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetSheetVisible("Sheet2", false) xlsx.SetSheetVisible("Sheet1", false) xlsx.SetSheetVisible("Sheet1", true) xlsx.GetSheetVisible("Sheet1") - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSheetVisibility.xlsx")) } func TestRowVisibility(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetRowVisible("Sheet3", 2, false) xlsx.SetRowVisible("Sheet3", 2, true) xlsx.GetRowVisible("Sheet3", 2) - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestRowVisibility.xlsx")) } func TestColumnVisibility(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) - } - xlsx.SetColVisible("Sheet1", "F", false) - xlsx.SetColVisible("Sheet1", "F", true) - xlsx.GetColVisible("Sheet1", "F") - xlsx.SetColVisible("Sheet3", "E", false) - err = xlsx.Save() - if err != nil { - t.Error(err) - } - xlsx, err = OpenFile("./test/Book3.xlsx") - if err != nil { - t.Error(err) - } - xlsx.GetColVisible("Sheet1", "B") + t.Run("TestBook1", func(t *testing.T) { + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() + } + + xlsx.SetColVisible("Sheet1", "F", false) + xlsx.SetColVisible("Sheet1", "F", true) + xlsx.GetColVisible("Sheet1", "F") + xlsx.SetColVisible("Sheet3", "E", false) + assert.NoError(t, xlsx.SaveAs("./test/TestColumnVisibility.xlsx")) + }) + + t.Run("TestBook3", func(t *testing.T) { + xlsx, err := prepareTestBook3() + if !assert.NoError(t, err) { + t.FailNow() + } + xlsx.GetColVisible("Sheet1", "B") + }) } func TestCopySheet(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) - } - err = xlsx.CopySheet(0, -1) - if err != nil { - t.Log(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } + idx := xlsx.NewSheet("CopySheet") err = xlsx.CopySheet(1, idx) - if err != nil { - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetCellValue("Sheet4", "F1", "Hello") - if xlsx.GetCellValue("Sheet1", "F1") == "Hello" { - t.Error("Invalid value \"Hello\" in Sheet1") + assert.NotEqual(t, "Hello", xlsx.GetCellValue("Sheet1", "F1")) + + assert.NoError(t, xlsx.SaveAs("./test/TestCopySheet.xlsx")) +} + +func TestCopySheetError(t *testing.T) { + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } - err = xlsx.Save() - if err != nil { - t.Error(err) + + err = xlsx.CopySheet(0, -1) + if !assert.EqualError(t, err, "invalid worksheet index") { + t.FailNow() } + + assert.NoError(t, xlsx.SaveAs("./test/TestCopySheetError.xlsx")) } func TestAddTable(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } + err = xlsx.AddTable("Sheet1", "B26", "A21", `{}`) - if err != nil { - t.Error(err) + 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}`) - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + err = xlsx.AddTable("Sheet2", "F1", "F1", `{"table_style":"TableStyleMedium8"}`) - if err != nil { - t.Error(err) - } - err = xlsx.Save() - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + + assert.NoError(t, xlsx.SaveAs("./test/TestAddTable.xlsx")) } func TestAddShape(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, 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", "") - err = xlsx.Save() - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestAddShape.xlsx")) } func TestAddComments(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, 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."}`) - err = xlsx.Save() - if err != nil { - t.Error(err) - } - allComments := xlsx.GetComments() - if len(allComments) != 2 { - t.Error("Expected 2 comment entry elements.") - } + if assert.NoError(t, xlsx.SaveAs("./test/TestAddComments.xlsx")) { + assert.Len(t, xlsx.GetComments(), 2) + } } func TestAutoFilter(t *testing.T) { - xlsx, err := OpenFile("./test/Book2.xlsx") - if err != nil { - t.Error(err) + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() } - formats := []string{``, + + formats := []string{ + ``, `{"column":"B","expression":"x != blanks"}`, `{"column":"B","expression":"x == blanks"}`, `{"column":"B","expression":"x != nonblanks"}`, @@ -911,6 +945,26 @@ func TestAutoFilter(t *testing.T) { `{"column":"B","expression":"x <= 1 and x >= 2"}`, `{"column":"B","expression":"x == 1 or x == 2"}`, `{"column":"B","expression":"x == 1 or x == 2*"}`, + } + + for i, format := range formats { + t.Run(fmt.Sprintf("Expression%d", i+1), func(t *testing.T) { + err = xlsx.AutoFilter("Sheet3", "D4", "B1", format) + if assert.NoError(t, err) { + assert.NoError(t, xlsx.SaveAs(fmt.Sprintf("./test/TestAutoFilter%d.xlsx", i+1))) + } + }) + } + +} + +func TestAutoFilterError(t *testing.T) { + xlsx, err := prepareTestBook1() + if !assert.NoError(t, err) { + t.FailNow() + } + + formats := []string{ `{"column":"B","expression":"x <= 1 and x >= blanks"}`, `{"column":"B","expression":"x -- y or x == *2*"}`, `{"column":"B","expression":"x != y or x ? *2"}`, @@ -918,21 +972,22 @@ func TestAutoFilter(t *testing.T) { `{"column":"B","expression":"x -- y"}`, `{"column":"A","expression":"x -- y"}`, } - for _, format := range formats { - err = xlsx.AutoFilter("Sheet3", "D4", "B1", format) - t.Log(err) - } - err = xlsx.Save() - if err != nil { - t.Error(err) + for i, format := range formats { + t.Run(fmt.Sprintf("Expression%d", i+1), func(t *testing.T) { + err = xlsx.AutoFilter("Sheet3", "D4", "B1", format) + if assert.Error(t, err) { + assert.NoError(t, xlsx.SaveAs(fmt.Sprintf("./test/TestAutoFilterError%d.xlsx", i+1))) + } + }) } } func TestAddChart(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + 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 { @@ -968,11 +1023,8 @@ func TestAddChart(t *testing.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"}`) 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"}`) 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"}`) - // Save xlsx file by the given path. - err = xlsx.SaveAs("./test/Book_addchart.xlsx") - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestAddChart.xlsx")) } func TestInsertCol(t *testing.T) { @@ -986,12 +1038,13 @@ func TestInsertCol(t *testing.T) { xlsx.SetCellHyperLink("Sheet1", "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External") xlsx.MergeCell("Sheet1", "A1", "C3") err := xlsx.AutoFilter("Sheet1", "A2", "B2", `{"column":"B","expression":"x != blanks"}`) - t.Log(err) - xlsx.InsertCol("Sheet1", "A") - err = xlsx.SaveAs("./test/Book_insertcol.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + + xlsx.InsertCol("Sheet1", "A") + + assert.NoError(t, xlsx.SaveAs("./test/TestInsertCol.xlsx")) } func TestRemoveCol(t *testing.T) { @@ -1008,10 +1061,8 @@ func TestRemoveCol(t *testing.T) { xlsx.MergeCell("Sheet1", "A2", "B2") xlsx.RemoveCol("Sheet1", "A") xlsx.RemoveCol("Sheet1", "A") - err := xlsx.SaveAs("./test/Book_removecol.xlsx") - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestRemoveCol.xlsx")) } func TestInsertRow(t *testing.T) { @@ -1025,15 +1076,14 @@ func TestInsertRow(t *testing.T) { xlsx.SetCellHyperLink("Sheet1", "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External") xlsx.InsertRow("Sheet1", -1) xlsx.InsertRow("Sheet1", 4) - err := xlsx.SaveAs("./test/Book_insertrow.xlsx") - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestInsertRow.xlsx")) } func TestDuplicateRow(t *testing.T) { const ( - file = "./test/Book_DuplicateRow_%s.xlsx" + file = "./test/TestDuplicateRow" + + ".%s.xlsx" sheet = "Sheet1" a1 = "A1" b1 = "B1" @@ -1053,11 +1103,10 @@ func TestDuplicateRow(t *testing.T) { xlsx.SetCellStr(sheet, b1, bnValue) t.Run("FromSingleRow", func(t *testing.T) { - xlsx.DuplicateRow(sheet, -1) xlsx.DuplicateRow(sheet, 1) xlsx.DuplicateRow(sheet, 2) - if assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(file, "SignleRow"))) { + if assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(file, "TestDuplicateRow.FromSingleRow"))) { assert.Equal(t, a1Value, xlsx.GetCellValue(sheet, a1)) assert.Equal(t, a1Value, xlsx.GetCellValue(sheet, a2)) assert.Equal(t, a1Value, xlsx.GetCellValue(sheet, a3)) @@ -1071,7 +1120,7 @@ func TestDuplicateRow(t *testing.T) { xlsx.SetCellStr(sheet, a2, a2Value) xlsx.SetCellStr(sheet, a3, a3Value) - if assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(file, "Updated"))) { + if assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(file, "TestDuplicateRow.UpdateDuplicatedRows"))) { assert.Equal(t, a1Value, xlsx.GetCellValue(sheet, a1)) assert.Equal(t, a2Value, xlsx.GetCellValue(sheet, a2)) assert.Equal(t, a3Value, xlsx.GetCellValue(sheet, a3)) @@ -1084,7 +1133,7 @@ func TestDuplicateRow(t *testing.T) { t.Run("FromFirstOfMultipleRows", func(t *testing.T) { xlsx.DuplicateRow(sheet, 1) - if assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(file, "FirstOfMultipleRows"))) { + if assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(file, "TestDuplicateRow.FromFirstOfMultipleRows"))) { assert.Equal(t, a1Value, xlsx.GetCellValue(sheet, a1)) assert.Equal(t, a1Value, xlsx.GetCellValue(sheet, a2)) assert.Equal(t, a2Value, xlsx.GetCellValue(sheet, a3)) @@ -1095,6 +1144,17 @@ func TestDuplicateRow(t *testing.T) { assert.Equal(t, bnValue, xlsx.GetCellValue(sheet, b4)) } }) + + t.Run("ZeroAndNegativeRowNum", func(t *testing.T) { + xlsx.DuplicateRow(sheet, -1) + xlsx.DuplicateRow(sheet, 0) + if assert.NoError(t, xlsx.SaveAs(fmt.Sprintf(file, "TestDuplicateRow.ZeroAndNegativeRowNum"))) { + assert.Equal(t, "", xlsx.GetCellValue(sheet, a1)) + assert.Equal(t, "", xlsx.GetCellValue(sheet, b1)) + assert.Equal(t, a1Value, xlsx.GetCellValue(sheet, a2)) + assert.Equal(t, bnValue, xlsx.GetCellValue(sheet, b2)) + } + }) } func TestSetPane(t *testing.T) { @@ -1107,10 +1167,8 @@ func TestSetPane(t *testing.T) { 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", "") - err := xlsx.SaveAs("./test/Book_set_panes.xlsx") - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSetPane.xlsx")) } func TestRemoveRow(t *testing.T) { @@ -1127,15 +1185,17 @@ func TestRemoveRow(t *testing.T) { xlsx.MergeCell("Sheet1", "B3", "B5") xlsx.RemoveRow("Sheet1", 2) xlsx.RemoveRow("Sheet1", 4) + err := xlsx.AutoFilter("Sheet1", "A2", "A2", `{"column":"A","expression":"x != blanks"}`) - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() + } + xlsx.RemoveRow("Sheet1", 0) xlsx.RemoveRow("Sheet1", 1) xlsx.RemoveRow("Sheet1", 0) - err = xlsx.SaveAs("./test/Book_removerow.xlsx") - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestRemoveRow.xlsx")) } func TestConditionalFormat(t *testing.T) { @@ -1149,13 +1209,22 @@ func TestConditionalFormat(t *testing.T) { var err error // Rose format for bad conditional. format1, err = xlsx.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`) - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() + } + // Light yellow format for neutral conditional. format2, err = xlsx.NewConditionalStyle(`{"fill":{"type":"pattern","color":["#FEEAA0"],"pattern":1}}`) - t.Log(err) + 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}}`) - t.Log(err) + 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"}]`) // Color scales: 3 color. @@ -1182,71 +1251,82 @@ func TestConditionalFormat(t *testing.T) { xlsx.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", "") - err = xlsx.SaveAs("./test/Book_conditional_format.xlsx") - if err != nil { - t.Log(err) + + err = xlsx.SaveAs("./test/TestConditionalFormat.xlsx") + if !assert.NoError(t, err) { + t.FailNow() } - // Set conditional format with illegal JSON string. - _, err = xlsx.NewConditionalStyle("") - t.Log(err) // Set conditional format with illegal valid type. xlsx.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"}]`) - // Set conditional format with file without dxfs element. + + // Set conditional format with file without dxfs element shold not return error. xlsx, err = OpenFile("./test/Book1.xlsx") - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() + } + _, err = xlsx.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`) - t.Log(err) + if !assert.NoError(t, err) { + t.FailNow() + } } -func TestTitleToNumber(t *testing.T) { - if TitleToNumber("AK") != 36 { - t.Error("Conver title to number failed") +func TestConditionalFormatError(t *testing.T) { + xlsx := NewFile() + for j := 1; j <= 10; j++ { + for i := 0; i <= 15; i++ { + xlsx.SetCellInt("Sheet1", ToAlphaString(i)+strconv.Itoa(j), j) + } } - if TitleToNumber("ak") != 36 { - t.Error("Conver title to number failed") + + // Set conditional format with illegal JSON string should return error + _, err := xlsx.NewConditionalStyle("") + if !assert.EqualError(t, err, "unexpected end of JSON input") { + t.FailNow() } } +func TestTitleToNumber(t *testing.T) { + assert.Equal(t, 36, TitleToNumber("AK")) + assert.Equal(t, 36, TitleToNumber("ak")) +} + func TestSharedStrings(t *testing.T) { xlsx, err := OpenFile("./test/SharedStrings.xlsx") - if err != nil { - t.Error(err) - return + if !assert.NoError(t, err) { + t.FailNow() } xlsx.GetRows("Sheet1") } func TestSetSheetRow(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) - return + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetSheetRow("Sheet1", "B27", &[]interface{}{"cell", nil, int32(42), float64(42), time.Now()}) xlsx.SetSheetRow("Sheet1", "", &[]interface{}{"cell", nil, 2}) xlsx.SetSheetRow("Sheet1", "B27", []interface{}{}) xlsx.SetSheetRow("Sheet1", "B27", &xlsx) - err = xlsx.Save() - if err != nil { - t.Error(err) - return - } + + assert.NoError(t, xlsx.SaveAs("./test/TestSetSheetRow.xlsx")) } func TestRows(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) - return + if !assert.NoError(t, err) { + t.FailNow() } + rows, err := xlsx.Rows("Sheet2") - if err != nil { - t.Error(err) - return + if !assert.NoError(t, err) { + t.FailNow() } + rowStrs := make([][]string, 0) var i = 0 for rows.Next() { @@ -1254,25 +1334,30 @@ func TestRows(t *testing.T) { columns := rows.Columns() rowStrs = append(rowStrs, columns) } - if rows.Error() != nil { - t.Error(rows.Error()) - return + + if !assert.NoError(t, rows.Error()) { + t.FailNow() } + dstRows := xlsx.GetRows("Sheet2") - if len(dstRows) != len(rowStrs) { - t.Error("values not equal") - return + if !assert.Equal(t, len(rowStrs), len(dstRows)) { + t.FailNow() } + for i := 0; i < len(rowStrs); i++ { - if !reflect.DeepEqual(trimSliceSpace(dstRows[i]), trimSliceSpace(rowStrs[i])) { - t.Error("values not equal") - return + if !assert.Equal(t, trimSliceSpace(dstRows[i]), trimSliceSpace(rowStrs[i])) { + t.FailNow() } } - rows, err = xlsx.Rows("SheetN") - if err != nil { - t.Log(err) +} + +func TestRowsError(t *testing.T) { + xlsx, err := OpenFile("./test/Book1.xlsx") + if !assert.NoError(t, err) { + t.FailNow() } + _, err = xlsx.Rows("SheetN") + assert.EqualError(t, err, "Sheet SheetN is not exist") } func TestOutlineLevel(t *testing.T) { @@ -1285,16 +1370,16 @@ func TestOutlineLevel(t *testing.T) { xlsx.SetColOutlineLevel("Sheet2", "B", 2) xlsx.SetRowOutlineLevel("Sheet1", 2, 1) xlsx.GetRowOutlineLevel("Sheet1", 2) - err := xlsx.SaveAs("./test/Book_outline_level.xlsx") - if err != nil { - t.Error(err) - return + err := xlsx.SaveAs("./test/TestOutlineLevel.xlsx") + if !assert.NoError(t, err) { + t.FailNow() } + xlsx, err = OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) - return + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.SetColOutlineLevel("Sheet2", "B", 2) } @@ -1325,10 +1410,10 @@ func TestHSL(t *testing.T) { func TestSearchSheet(t *testing.T) { xlsx, err := OpenFile("./test/SharedStrings.xlsx") - if err != nil { - t.Error(err) - return + if !assert.NoError(t, err) { + t.FailNow() } + // Test search in a not exists worksheet. t.Log(xlsx.SearchSheet("Sheet4", "")) // Test search a not exists value. @@ -1346,22 +1431,18 @@ func TestProtectSheet(t *testing.T) { Password: "password", EditScenarios: false, }) - err := xlsx.SaveAs("./test/Book_protect_sheet.xlsx") - if err != nil { - t.Error(err) - } + + assert.NoError(t, xlsx.SaveAs("./test/TestProtectSheet.xlsx")) } func TestUnprotectSheet(t *testing.T) { xlsx, err := OpenFile("./test/Book1.xlsx") - if err != nil { - t.Error(err) + if !assert.NoError(t, err) { + t.FailNow() } + xlsx.UnprotectSheet("Sheet1") - err = xlsx.Save() - if err != nil { - t.Error(err) - } + assert.NoError(t, xlsx.SaveAs("./test/TestUnprotectSheet.xlsx")) } func trimSliceSpace(s []string) []string { @@ -1374,3 +1455,67 @@ func trimSliceSpace(s []string) []string { } return s } + +func prepareTestBook1() (*File, error) { + xlsx, err := OpenFile("./test/Book1.xlsx") + if err != nil { + return nil, err + } + + err = xlsx.AddPicture("Sheet2", "I9", "./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", "./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 + } + + file, err := ioutil.ReadFile("./test/images/excel.jpg") + if err != nil { + return nil, err + } + + err = xlsx.AddPictureFromBytes("Sheet1", "Q1", "", "Excel Logo", ".jpg", file) + if err != nil { + return nil, err + } + + return xlsx, 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", "./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", "./test/images/excel.png", "") + if err != nil { + return nil, err + } + + return xlsx, 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") + + return xlsx, nil +} |