diff options
| -rw-r--r-- | excelize.go | 9 | ||||
| -rw-r--r-- | excelize_test.go | 79 | ||||
| -rw-r--r-- | styles.go | 123 |
3 files changed, 133 insertions, 78 deletions
diff --git a/excelize.go b/excelize.go index 8e91fea..4fdfc72 100644 --- a/excelize.go +++ b/excelize.go @@ -114,9 +114,9 @@ func (f *File) SetCellValue(sheet, axis string, value interface{}) { } } -// getCellStyle provides function to get cell style index by given worksheet +// GetCellStyle provides function to get cell style index by given worksheet // name and cell coordinates. -func (f *File) getCellStyle(sheet, axis string) int { +func (f *File) GetCellStyle(sheet, axis string) int { xlsx := f.workSheetReader(sheet) axis = strings.ToUpper(axis) f.mergeCellsParser(xlsx, axis) @@ -137,8 +137,9 @@ func (f *File) getCellStyle(sheet, axis string) int { // setDefaultTimeStyle provides function to set default numbers format for // time.Time type cell value by given worksheet name and cell coordinates. func (f *File) setDefaultTimeStyle(sheet, axis string) { - if f.getCellStyle(sheet, axis) == 0 { - f.SetCellStyle(sheet, axis, axis, `{"number_format": 22}`) + if f.GetCellStyle(sheet, axis) == 0 { + style, _ := f.NewStyle(`{"number_format": 22}`) + f.SetCellStyle(sheet, axis, axis, style) } } diff --git a/excelize_test.go b/excelize_test.go index 738af92..534538d 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -289,7 +289,12 @@ func TestSetCellStyleAlignment(t *testing.T) { if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet1", "A22", "A22", `{"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}}`) + 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.Log(err) + } + xlsx.SetCellStyle("Sheet1", "A22", "A22", style) err = xlsx.Save() if err != nil { t.Log(err) @@ -301,30 +306,47 @@ func TestSetCellStyleBorder(t *testing.T) { if err != nil { t.Log(err) } + var style int // Test set border with invalid style parameter. - err = xlsx.SetCellStyle("Sheet1", "J21", "L25", "") + style, err = xlsx.NewStyle("") if err != nil { t.Log(err) } + xlsx.SetCellStyle("Sheet1", "J21", "L25", style) + // Test set border with invalid style index number. - err = xlsx.SetCellStyle("Sheet1", "J21", "L25", `{"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}]}`) + 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) + // Test set border on overlapping area with vertical variants shading styles gradient fill. - err = xlsx.SetCellStyle("Sheet1", "J21", "L25", `{"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 = 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) } - err = xlsx.SetCellStyle("Sheet1", "M28", "K24", `{"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}}`) + 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) } + 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) + } + xlsx.SetCellStyle("Sheet1", "M28", "K24", style) + // Test set border and solid style pattern fill for a single cell. - err = xlsx.SetCellStyle("Sheet1", "O22", "O22", `{"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 = 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) } + + xlsx.SetCellStyle("Sheet1", "O22", "O22", style) err = xlsx.Save() if err != nil { t.Log(err) @@ -350,13 +372,20 @@ func TestSetCellStyleNumberFormat(t *testing.T) { } else { xlsx.SetCellValue("Sheet2", c, val) } - err := xlsx.SetCellStyle("Sheet2", c, c, `{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":5},"number_format": `+strconv.Itoa(d)+`}`) + style, err := xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":5},"number_format": ` + strconv.Itoa(d) + `}`) if err != nil { t.Log(err) } + xlsx.SetCellStyle("Sheet2", c, c, style) t.Log(xlsx.GetCellValue("Sheet2", c)) } } + var style int + style, err = xlsx.NewStyle(`{"number_format":-1}`) + if err != nil { + t.Log(err) + } + xlsx.SetCellStyle("Sheet2", "L33", "L33", style) err = xlsx.Save() if err != nil { t.Log(err) @@ -368,24 +397,32 @@ func TestSetCellStyleFill(t *testing.T) { if err != nil { t.Log(err) } + var style int // Test set fill for cell with invalid parameter. - err = xlsx.SetCellStyle("Sheet1", "O23", "O23", `{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":6}}`) + style, err = xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":6}}`) if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet1", "O23", "O23", `{"fill":{"type":"gradient","color":["#FFFFFF"],"shading":1}}`) + xlsx.SetCellStyle("Sheet1", "O23", "O23", style) + + style, err = xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF"],"shading":1}}`) if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet1", "O23", "O23", `{"fill":{"type":"pattern","color":[],"pattern":1}}`) + xlsx.SetCellStyle("Sheet1", "O23", "O23", style) + + style, err = xlsx.NewStyle(`{"fill":{"type":"pattern","color":[],"pattern":1}}`) if err != nil { t.Log(err) } + xlsx.SetCellStyle("Sheet1", "O23", "O23", style) - err = xlsx.SetCellStyle("Sheet1", "O23", "O23", `{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":19}}`) + style, err = xlsx.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":19}}`) if err != nil { t.Log(err) } + xlsx.SetCellStyle("Sheet1", "O23", "O23", style) + err = xlsx.Save() if err != nil { t.Log(err) @@ -397,26 +434,36 @@ func TestSetCellStyleFont(t *testing.T) { if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet2", "A1", "A1", `{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777","underline":"single"}}`) + 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) } - err = xlsx.SetCellStyle("Sheet2", "A2", "A2", `{"font":{"italic":true,"underline":"double"}}`) + xlsx.SetCellStyle("Sheet2", "A1", "A1", style) + + style, err = xlsx.NewStyle(`{"font":{"italic":true,"underline":"double"}}`) if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet2", "A3", "A3", `{"font":{"bold":true}}`) + xlsx.SetCellStyle("Sheet2", "A2", "A2", style) + + style, err = xlsx.NewStyle(`{"font":{"bold":true}}`) if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet2", "A4", "A4", `{"font":{"bold":true,"family":"","size":0,"color":"","underline":""}}`) + xlsx.SetCellStyle("Sheet2", "A3", "A3", style) + + style, err = xlsx.NewStyle(`{"font":{"bold":true,"family":"","size":0,"color":"","underline":""}}`) if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet2", "A5", "A5", `{"font":{"color":"#777777"}}`) + xlsx.SetCellStyle("Sheet2", "A4", "A4", style) + + style, err = xlsx.NewStyle(`{"font":{"color":"#777777"}}`) if err != nil { t.Log(err) } + xlsx.SetCellStyle("Sheet2", "A5", "A5", style) err = xlsx.Save() if err != nil { t.Log(err) @@ -252,56 +252,8 @@ func parseFormatStyleSet(style string) (*formatCellStyle, error) { return &format, err } -// SetCellStyle provides function to set style for cells by given sheet index -// and coordinate area in XLSX file. Note that the color field uses RGB color -// code and diagonalDown and diagonalUp type border should be use same color in -// the same coordinate area. -// -// For example create a borders of cell H9 on Sheet1: -// -// err := xlsx.SetCellStyle("Sheet1", "H9", "H9", `{"border":[{"type":"left","color":"0000FF","style":3},{"type":"top","color":"00FF00","style":4},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":7},{"type":"diagonalUp","color":"A020F0","style":8}]}`) -// if err != nil { -// fmt.Println(err) -// } -// -// Set gradient fill with vertical variants shading styles for cell H9 on -// Sheet1: -// -// err := xlsx.SetCellStyle("Sheet1", "H9", "H9", `{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}}`) -// if err != nil { -// fmt.Println(err) -// } -// -// Set solid style pattern fill for cell H9 on Sheet1: -// -// err := xlsx.SetCellStyle("Sheet1", "H9", "H9", `{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`) -// if err != nil { -// fmt.Println(err) -// } -// -// Set alignment style for cell H9 on Sheet1: -// -// err = xlsx.SetCellStyle("Sheet1", "H9", "H9", `{"alignment":{"horizontal":"center","ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1,"shrink_to_fit":true,"text_rotation":45,"vertical":"","wrap_text":true}}`) -// if err != nil { -// fmt.Println(err) -// } -// -// Dates and times in Excel are represented by real numbers, for example "Apr 7 -// 2017 12:00 PM" is represented by the number 42920.5. Set date and time format -// for cell H9 on Sheet1: -// -// xlsx.SetCellValue("Sheet2", "H9", 42920.5) -// err = xlsx.SetCellStyle("Sheet1", "H9", "H9", `{"number_format": 22}`) -// if err != nil { -// fmt.Println(err) -// } -// -// Set font style for cell H9 on Sheet1: -// -// err = xlsx.SetCellStyle("Sheet1", "H9", "H9", `{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777"}}`) -// if err != nil { -// fmt.Println(err) -// } +// NewStyle provides function to create style for cells by given style format. +// Note that the color field uses RGB color code. // // The following shows the border styles sorted by excelize index number: // @@ -519,20 +471,20 @@ func parseFormatStyleSet(style string) (*formatCellStyle, error) { // | 49 | @ | // +-------+----------------------------------------------------+ // -func (f *File) SetCellStyle(sheet, hcell, vcell, style string) error { +func (f *File) NewStyle(style string) (int, error) { + var cellXfsID int styleSheet := f.stylesReader() formatCellStyle, err := parseFormatStyleSet(style) if err != nil { - return err + return cellXfsID, err } numFmtID := setNumFmt(styleSheet, formatCellStyle) fontID := setFont(styleSheet, formatCellStyle) borderID := setBorders(styleSheet, formatCellStyle) fillID := setFills(styleSheet, formatCellStyle) applyAlignment, alignment := setAlignment(styleSheet, formatCellStyle) - cellXfsID := setCellXfs(styleSheet, fontID, numFmtID, fillID, borderID, applyAlignment, alignment) - f.setCellStyle(sheet, hcell, vcell, cellXfsID) - return err + cellXfsID = setCellXfs(styleSheet, fontID, numFmtID, fillID, borderID, applyAlignment, alignment) + return cellXfsID, nil } // setFont provides function to add font style by given cell format settings. @@ -757,9 +709,64 @@ func setCellXfs(style *xlsxStyleSheet, fontID, numFmtID, fillID, borderID int, a return style.CellXfs.Count - 1 } -// setCellStyle provides function to add style attribute for cells by given -// sheet index, coordinate area and style ID. -func (f *File) setCellStyle(sheet, hcell, vcell string, styleID int) { +// SetCellStyle provides function to add style attribute for cells by given +// worksheet sheet index, coordinate area and style ID. Note that diagonalDown +// and diagonalUp type border should be use same color in the same coordinate +// area. +// +// For example create a borders of cell H9 on Sheet1: +// +// style, err := xlsx.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":3},{"type":"top","color":"00FF00","style":4},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":7},{"type":"diagonalUp","color":"A020F0","style":8}]}`) +// if err != nil { +// fmt.Println(err) +// } +// xlsx.SetCellStyle("Sheet1", "H9", "H9", style) +// +// Set gradient fill with vertical variants shading styles for cell H9 on +// Sheet1: +// +// style, err := xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}}`) +// if err != nil { +// fmt.Println(err) +// } +// xlsx.SetCellStyle("Sheet1", "H9", "H9", style) +// +// Set solid style pattern fill for cell H9 on Sheet1: +// +// style, err := xlsx.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`) +// if err != nil { +// fmt.Println(err) +// } +// xlsx.SetCellStyle("Sheet1", "H9", "H9", style) +// +// Set alignment style for cell H9 on Sheet1: +// +// 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":"","wrap_text":true}}`) +// if err != nil { +// fmt.Println(err) +// } +// xlsx.SetCellStyle("Sheet1", "H9", "H9", style) +// +// Dates and times in Excel are represented by real numbers, for example "Apr 7 +// 2017 12:00 PM" is represented by the number 42920.5. Set date and time format +// for cell H9 on Sheet1: +// +// xlsx.SetCellValue("Sheet1", "H9", 42920.5) +// style, err := xlsx.NewStyle(`{"number_format": 22}`) +// if err != nil { +// fmt.Println(err) +// } +// xlsx.SetCellStyle("Sheet1", "H9", "H9", style) +// +// Set font style for cell H9 on Sheet1: +// +// style, err := xlsx.NewStyle(`{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777"}}`) +// if err != nil { +// fmt.Println(err) +// } +// xlsx.SetCellStyle("Sheet1", "H9", "H9", style) +// +func (f *File) SetCellStyle(sheet, hcell, vcell string, styleID int) { hcell = strings.ToUpper(hcell) vcell = strings.ToUpper(vcell) |