From cbe919fdf6c00733513494680b89171b8b1b41a1 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 4 Aug 2019 20:24:59 +0800 Subject: New feature: sparkline supported --- sparkline_test.go | 297 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 297 insertions(+) create mode 100644 sparkline_test.go (limited to 'sparkline_test.go') diff --git a/sparkline_test.go b/sparkline_test.go new file mode 100644 index 0000000..d52929b --- /dev/null +++ b/sparkline_test.go @@ -0,0 +1,297 @@ +package excelize + +import ( + "fmt" + "path/filepath" + "testing" + + "github.com/stretchr/testify/assert" +) + +func TestAddSparkline(t *testing.T) { + f := prepareSparklineDataset() + + // Set the columns widths to make the output clearer + style, err := f.NewStyle(`{"font":{"bold":true}}`) + assert.NoError(t, err) + assert.NoError(t, f.SetCellStyle("Sheet1", "A1", "B1", style)) + assert.NoError(t, f.SetSheetViewOptions("Sheet1", 0, ZoomScale(150))) + + f.SetColWidth("Sheet1", "A", "A", 14) + f.SetColWidth("Sheet1", "B", "B", 50) + // Headings + f.SetCellValue("Sheet1", "A1", "Sparkline") + f.SetCellValue("Sheet1", "B1", "Description") + + f.SetCellValue("Sheet1", "B2", `A default "line" sparkline.`) + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A2"}, + Range: []string{"Sheet3!A1:J1"}, + })) + + f.SetCellValue("Sheet1", "B3", `A default "column" sparkline.`) + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A3"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + })) + + f.SetCellValue("Sheet1", "B4", `A default "win/loss" sparkline.`) + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A4"}, + Range: []string{"Sheet3!A3:J3"}, + Type: "win_loss", + })) + + f.SetCellValue("Sheet1", "B6", "Line with markers.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A6"}, + Range: []string{"Sheet3!A1:J1"}, + Markers: true, + })) + + f.SetCellValue("Sheet1", "B7", "Line with high and low points.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A7"}, + Range: []string{"Sheet3!A1:J1"}, + High: true, + Low: true, + })) + + f.SetCellValue("Sheet1", "B8", "Line with first and last point markers.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A8"}, + Range: []string{"Sheet3!A1:J1"}, + First: true, + Last: true, + })) + + f.SetCellValue("Sheet1", "B9", "Line with negative point markers.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A9"}, + Range: []string{"Sheet3!A1:J1"}, + Negative: true, + })) + + f.SetCellValue("Sheet1", "B10", "Line with axis.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A10"}, + Range: []string{"Sheet3!A1:J1"}, + Axis: true, + })) + + f.SetCellValue("Sheet1", "B12", "Column with default style (1).") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A12"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + })) + + f.SetCellValue("Sheet1", "B13", "Column with style 2.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A13"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + Style: 2, + })) + + f.SetCellValue("Sheet1", "B14", "Column with style 3.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A14"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + Style: 3, + })) + + f.SetCellValue("Sheet1", "B15", "Column with style 4.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A15"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + Style: 4, + })) + + f.SetCellValue("Sheet1", "B16", "Column with style 5.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A16"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + Style: 5, + })) + + f.SetCellValue("Sheet1", "B17", "Column with style 6.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A17"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + Style: 6, + })) + + f.SetCellValue("Sheet1", "B18", "Column with a user defined color.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A18"}, + Range: []string{"Sheet3!A2:J2"}, + Type: "column", + SeriesColor: "#E965E0", + })) + + f.SetCellValue("Sheet1", "B20", "A win/loss sparkline.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A20"}, + Range: []string{"Sheet3!A3:J3"}, + Type: "win_loss", + })) + + f.SetCellValue("Sheet1", "B21", "A win/loss sparkline with negative points highlighted.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A21"}, + Range: []string{"Sheet3!A3:J3"}, + Type: "win_loss", + Negative: true, + })) + + f.SetCellValue("Sheet1", "B23", "A left to right column (the default).") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A23"}, + Range: []string{"Sheet3!A4:J4"}, + Type: "column", + Style: 20, + })) + + f.SetCellValue("Sheet1", "B24", "A right to left column.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A24"}, + Range: []string{"Sheet3!A4:J4"}, + Type: "column", + Style: 20, + Reverse: true, + })) + + f.SetCellValue("Sheet1", "B25", "Sparkline and text in one cell.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A25"}, + Range: []string{"Sheet3!A4:J4"}, + Type: "column", + Style: 20, + })) + f.SetCellValue("Sheet1", "A25", "Growth") + + f.SetCellValue("Sheet1", "B27", "A grouped sparkline. Changes are applied to all three.") + assert.NoError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A27", "A28", "A29"}, + Range: []string{"Sheet3!A5:J5", "Sheet3!A6:J6", "Sheet3!A7:J7"}, + Markers: true, + })) + + // Sheet2 sections + assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{ + Location: []string{"F3"}, + Range: []string{"Sheet2!A3:E3"}, + Type: "win_loss", + Negative: true, + })) + + assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{ + Location: []string{"F1"}, + Range: []string{"Sheet2!A1:E1"}, + Markers: true, + })) + + assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{ + Location: []string{"F2"}, + Range: []string{"Sheet2!A2:E2"}, + Type: "column", + Style: 12, + })) + + assert.NoError(t, f.AddSparkline("Sheet2", &SparklineOption{ + Location: []string{"F3"}, + Range: []string{"Sheet2!A3:E3"}, + Type: "win_loss", + Negative: true, + })) + + // Save xlsx file by the given path. + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddSparkline.xlsx"))) + + // Test error exceptions + assert.EqualError(t, f.AddSparkline("SheetN", &SparklineOption{ + Location: []string{"F3"}, + Range: []string{"Sheet2!A3:E3"}, + }), "sheet SheetN is not exist") + + assert.EqualError(t, f.AddSparkline("Sheet1", nil), "parameter is required") + + assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Range: []string{"Sheet2!A3:E3"}, + }), `parameter 'Location' is required`) + + assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"F3"}, + }), `parameter 'Range' is required`) + + assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"F2", "F3"}, + Range: []string{"Sheet2!A3:E3"}, + }), `must have the same number of 'Location' and 'Range' parameters`) + + assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"F3"}, + Range: []string{"Sheet2!A3:E3"}, + Type: "unknown_type", + }), `parameter 'Type' must be 'line', 'column' or 'win_loss'`) + + assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"F3"}, + Range: []string{"Sheet2!A3:E3"}, + Style: -1, + }), `parameter 'Style' must betweent 0-35`) + + assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"F3"}, + Range: []string{"Sheet2!A3:E3"}, + Style: -1, + }), `parameter 'Style' must betweent 0-35`) + + f.Sheet["xl/worksheets/sheet1.xml"].ExtLst.Ext = ` + + + + + + + + ` + assert.EqualError(t, f.AddSparkline("Sheet1", &SparklineOption{ + Location: []string{"A2"}, + Range: []string{"Sheet3!A1:J1"}, + }), "XML syntax error on line 6: element closed by ") +} + +func prepareSparklineDataset() *File { + f := NewFile() + sheet2 := [][]int{ + {-2, 2, 3, -1, 0}, + {30, 20, 33, 20, 15}, + {1, -1, -1, 1, -1}, + } + sheet3 := [][]int{ + {-2, 2, 3, -1, 0, -2, 3, 2, 1, 0}, + {30, 20, 33, 20, 15, 5, 5, 15, 10, 15}, + {1, 1, -1, -1, 1, -1, 1, 1, 1, -1}, + {5, 6, 7, 10, 15, 20, 30, 50, 70, 100}, + {-2, 2, 3, -1, 0, -2, 3, 2, 1, 0}, + {3, -1, 0, -2, 3, 2, 1, 0, 2, 1}, + {0, -2, 3, 2, 1, 0, 1, 2, 3, 1}, + } + f.NewSheet("Sheet2") + f.NewSheet("Sheet3") + for row, data := range sheet2 { + f.SetSheetRow("Sheet2", fmt.Sprintf("A%d", row+1), &data) + } + for row, data := range sheet3 { + f.SetSheetRow("Sheet3", fmt.Sprintf("A%d", row+1), &data) + } + return f +} -- cgit v1.2.1