diff options
| -rw-r--r-- | datavalidation.go | 12 | ||||
| -rw-r--r-- | datavalidation_test.go | 31 | ||||
| -rw-r--r-- | excelize_test.go | 10 | ||||
| -rw-r--r-- | styles.go | 187 | ||||
| -rw-r--r-- | styles_test.go | 27 | ||||
| -rw-r--r-- | xmlWorksheet.go | 8 |
6 files changed, 258 insertions, 17 deletions
diff --git a/datavalidation.go b/datavalidation.go index 0cad1b8..3d82f7c 100644 --- a/datavalidation.go +++ b/datavalidation.go @@ -259,6 +259,18 @@ func (f *File) AddDataValidation(sheet string, dv *DataValidation) error { return err } +// GetDataValidations returns data validations list by given worksheet name. +func (f *File) GetDataValidations(sheet string) ([]*DataValidation, error) { + ws, err := f.workSheetReader(sheet) + if err != nil { + return nil, err + } + if ws.DataValidations == nil || len(ws.DataValidations.DataValidation) == 0 { + return nil, err + } + return ws.DataValidations.DataValidation, err +} + // DeleteDataValidation delete data validation by given worksheet name and // reference sequence. All data validations in the worksheet will be deleted // if not specify reference sequence parameter. diff --git a/datavalidation_test.go b/datavalidation_test.go index d9e060a..88625d1 100644 --- a/datavalidation_test.go +++ b/datavalidation_test.go @@ -32,6 +32,11 @@ func TestDataValidation(t *testing.T) { dvRange.SetError(DataValidationErrorStyleWarning, "error title", "error body") dvRange.SetError(DataValidationErrorStyleInformation, "error title", "error body") assert.NoError(t, f.AddDataValidation("Sheet1", dvRange)) + + dataValidations, err := f.GetDataValidations("Sheet1") + assert.NoError(t, err) + assert.Equal(t, len(dataValidations), 1) + assert.NoError(t, f.SaveAs(resultFile)) dvRange = NewDataValidation(true) @@ -39,6 +44,11 @@ func TestDataValidation(t *testing.T) { assert.NoError(t, dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorGreaterThan)) dvRange.SetInput("input title", "input body") assert.NoError(t, f.AddDataValidation("Sheet1", dvRange)) + + dataValidations, err = f.GetDataValidations("Sheet1") + assert.NoError(t, err) + assert.Equal(t, len(dataValidations), 2) + assert.NoError(t, f.SaveAs(resultFile)) f.NewSheet("Sheet2") @@ -49,6 +59,12 @@ func TestDataValidation(t *testing.T) { assert.NoError(t, dvRange.SetRange("INDIRECT($A$2)", "INDIRECT($A$3)", DataValidationTypeWhole, DataValidationOperatorBetween)) dvRange.SetError(DataValidationErrorStyleStop, "error title", "error body") assert.NoError(t, f.AddDataValidation("Sheet2", dvRange)) + dataValidations, err = f.GetDataValidations("Sheet1") + assert.NoError(t, err) + assert.Equal(t, len(dataValidations), 2) + dataValidations, err = f.GetDataValidations("Sheet2") + assert.NoError(t, err) + assert.Equal(t, len(dataValidations), 1) dvRange = NewDataValidation(true) dvRange.Sqref = "A5:B6" @@ -67,7 +83,22 @@ func TestDataValidation(t *testing.T) { } assert.Equal(t, `<formula1>"A<,B>,C"",D ,E',F"</formula1>`, dvRange.Formula1) assert.NoError(t, f.AddDataValidation("Sheet1", dvRange)) + + dataValidations, err = f.GetDataValidations("Sheet1") + assert.NoError(t, err) + assert.Equal(t, len(dataValidations), 3) + + // Test get data validation on no exists worksheet + _, err = f.GetDataValidations("SheetN") + assert.EqualError(t, err, "sheet SheetN is not exist") + assert.NoError(t, f.SaveAs(resultFile)) + + // Test get data validation on a worksheet without data validation settings + f = NewFile() + dataValidations, err = f.GetDataValidations("Sheet1") + assert.NoError(t, err) + assert.Equal(t, []*DataValidation(nil), dataValidations) } func TestDataValidationError(t *testing.T) { diff --git a/excelize_test.go b/excelize_test.go index 5db658a..eac218f 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -1041,15 +1041,15 @@ func TestConditionalFormat(t *testing.T) { assert.NoError(t, 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. assert.NoError(t, 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... + // Highlight cells rules: between... assert.NoError(t, f.SetConditionalFormat(sheet1, "C1:C10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format1))) - // Hightlight cells rules: Greater Than... + // Highlight cells rules: Greater Than... assert.NoError(t, f.SetConditionalFormat(sheet1, "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format3))) - // Hightlight cells rules: Equal To... + // Highlight cells rules: Equal To... assert.NoError(t, f.SetConditionalFormat(sheet1, "E1:E10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d}]`, format3))) - // Hightlight cells rules: Not Equal To... + // Highlight cells rules: Not Equal To... assert.NoError(t, f.SetConditionalFormat(sheet1, "F1:F10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, format2))) - // Hightlight cells rules: Duplicate Values... + // Highlight cells rules: Duplicate Values... assert.NoError(t, f.SetConditionalFormat(sheet1, "G1:G10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format2))) // Top/Bottom rules: Top 10%. assert.NoError(t, f.SetConditionalFormat(sheet1, "H1:H10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6","percent":true}]`, format1))) @@ -844,6 +844,31 @@ var criteriaType = map[string]string{ "continue month": "continueMonth", } +// operatorType defined the list of valid operator types. +var operatorType = map[string]string{ + "lastMonth": "last month", + "between": "between", + "notEqual": "not equal to", + "greaterThan": "greater than", + "lessThanOrEqual": "less than or equal to", + "today": "today", + "equal": "equal to", + "notContains": "not containing", + "thisWeek": "this week", + "endsWith": "ends with", + "yesterday": "yesterday", + "lessThan": "less than", + "beginsWith": "begins with", + "last7Days": "last 7 days", + "thisMonth": "this month", + "containsText": "containing", + "lastWeek": "last week", + "continueWeek": "continue week", + "continueMonth": "continue month", + "notBetween": "not between", + "greaterThanOrEqual": "greater than or equal to", +} + // formatToInt provides a function to convert original string to integer // format as string type by given built-in number formats code and cell // string. @@ -2726,7 +2751,7 @@ func (f *File) SetCellStyle(sheet, hCell, vCell string, styleID int) error { // type: minimum - The minimum parameter is used to set the lower limiting value // when the criteria is either "between" or "not between". // -// // Hightlight cells rules: between... +// // Highlight cells rules: between... // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format)) // // type: maximum - The maximum parameter is used to set the upper limiting value @@ -2744,12 +2769,12 @@ func (f *File) SetCellStyle(sheet, hCell, vCell string, styleID int) error { // // type: duplicate - The duplicate type is used to highlight duplicate cells in a range: // -// // Hightlight cells rules: Duplicate Values... +// // Highlight cells rules: Duplicate Values... // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format)) // // type: unique - The unique type is used to highlight unique cells in a range: // -// // Hightlight cells rules: Not Equal To... +// // Highlight cells rules: Not Equal To... // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, format)) // // type: top - The top type is used to specify the top n values by number or percentage in a range: @@ -2837,7 +2862,7 @@ func (f *File) SetConditionalFormat(sheet, area, formatSet string) error { "2_color_scale": drawCondFmtColorScale, "3_color_scale": drawCondFmtColorScale, "dataBar": drawCondFmtDataBar, - "expression": drawConfFmtExp, + "expression": drawCondFmtExp, } ws, err := f.workSheetReader(sheet) @@ -2854,9 +2879,9 @@ func (f *File) SetConditionalFormat(sheet, area, formatSet string) error { // Check for valid criteria types. ct, ok = criteriaType[v.Criteria] if ok || vt == "expression" { - drawfunc, ok := drawContFmtFunc[vt] + drawFunc, ok := drawContFmtFunc[vt] if ok { - cfRule = append(cfRule, drawfunc(p, ct, v)) + cfRule = append(cfRule, drawFunc(p, ct, v)) } } } @@ -2869,6 +2894,152 @@ func (f *File) SetConditionalFormat(sheet, area, formatSet string) error { return err } +// extractCondFmtCellIs provides a function to extract conditional format +// settings for cell value (include between, not between, equal, not equal, +// greater than and less than) by given conditional formatting rule. +func extractCondFmtCellIs(c *xlsxCfRule) *formatConditional { + format := formatConditional{Type: "cell", Criteria: operatorType[c.Operator], Format: *c.DxfID} + if len(c.Formula) == 2 { + format.Minimum, format.Maximum = c.Formula[0], c.Formula[1] + return &format + } + format.Value = c.Formula[0] + return &format +} + +// extractCondFmtTop10 provides a function to extract conditional format +// settings for top N (default is top 10) by given conditional formatting +// rule. +func extractCondFmtTop10(c *xlsxCfRule) *formatConditional { + format := formatConditional{ + Type: "top", + Criteria: "=", + Format: *c.DxfID, + Percent: c.Percent, + Value: strconv.Itoa(c.Rank), + } + if c.Bottom { + format.Type = "bottom" + } + return &format +} + +// extractCondFmtAboveAverage provides a function to extract conditional format +// settings for above average and below average by given conditional formatting +// rule. +func extractCondFmtAboveAverage(c *xlsxCfRule) *formatConditional { + return &formatConditional{ + Type: "average", + Criteria: "=", + Format: *c.DxfID, + AboveAverage: *c.AboveAverage, + } +} + +// extractCondFmtDuplicateUniqueValues provides a function to extract +// conditional format settings for duplicate and unique values by given +// conditional formatting rule. +func extractCondFmtDuplicateUniqueValues(c *xlsxCfRule) *formatConditional { + return &formatConditional{ + Type: map[string]string{ + "duplicateValues": "duplicate", + "uniqueValues": "unique", + }[c.Type], + Criteria: "=", + Format: *c.DxfID, + } +} + +// extractCondFmtColorScale provides a function to extract conditional format +// settings for color scale (include 2 color scale and 3 color scale) by given +// conditional formatting rule. +func extractCondFmtColorScale(c *xlsxCfRule) *formatConditional { + var format formatConditional + format.Type, format.Criteria = "2_color_scale", "=" + values := len(c.ColorScale.Cfvo) + colors := len(c.ColorScale.Color) + if colors > 1 && values > 1 { + format.MinType = c.ColorScale.Cfvo[0].Type + if c.ColorScale.Cfvo[0].Val != "0" { + format.MinValue = c.ColorScale.Cfvo[0].Val + } + format.MinColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[0].RGB), "FF") + format.MaxType = c.ColorScale.Cfvo[1].Type + if c.ColorScale.Cfvo[1].Val != "0" { + format.MaxValue = c.ColorScale.Cfvo[1].Val + } + format.MaxColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[1].RGB), "FF") + } + if colors == 3 { + format.Type = "3_color_scale" + format.MidType = c.ColorScale.Cfvo[1].Type + if c.ColorScale.Cfvo[1].Val != "0" { + format.MidValue = c.ColorScale.Cfvo[1].Val + } + format.MidColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[1].RGB), "FF") + format.MaxType = c.ColorScale.Cfvo[2].Type + if c.ColorScale.Cfvo[2].Val != "0" { + format.MaxValue = c.ColorScale.Cfvo[2].Val + } + format.MaxColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[2].RGB), "FF") + } + return &format +} + +// extractCondFmtDataBar provides a function to extract conditional format +// settings for data bar by given conditional formatting rule. +func extractCondFmtDataBar(c *xlsxCfRule) *formatConditional { + format := formatConditional{Type: "data_bar", Criteria: "="} + if c.DataBar != nil { + format.MinType = c.DataBar.Cfvo[0].Type + format.MaxType = c.DataBar.Cfvo[1].Type + format.BarColor = "#" + strings.TrimPrefix(strings.ToUpper(c.DataBar.Color[0].RGB), "FF") + } + return &format +} + +// extractCondFmtExp provides a function to extract conditional format settings +// for expression by given conditional formatting rule. +func extractCondFmtExp(c *xlsxCfRule) *formatConditional { + format := formatConditional{Type: "formula", Format: *c.DxfID} + if len(c.Formula) > 0 { + format.Criteria = c.Formula[0] + } + return &format +} + +// GetConditionalFormats returns conditional format settings by given worksheet +// name. +func (f *File) GetConditionalFormats(sheet string) (map[string]string, error) { + extractContFmtFunc := map[string]func(c *xlsxCfRule) *formatConditional{ + "cellIs": extractCondFmtCellIs, + "top10": extractCondFmtTop10, + "aboveAverage": extractCondFmtAboveAverage, + "duplicateValues": extractCondFmtDuplicateUniqueValues, + "uniqueValues": extractCondFmtDuplicateUniqueValues, + "colorScale": extractCondFmtColorScale, + "dataBar": extractCondFmtDataBar, + "expression": extractCondFmtExp, + } + + conditionalFormats := make(map[string]string) + ws, err := f.workSheetReader(sheet) + if err != nil { + return conditionalFormats, err + } + for _, cf := range ws.ConditionalFormatting { + var format []*formatConditional + for _, cr := range cf.CfRule { + if extractFunc, ok := extractContFmtFunc[cr.Type]; ok { + format = append(format, extractFunc(cr)) + } + } + formatSet, _ := json.Marshal(format) + conditionalFormats[cf.SQRef] = string(formatSet) + } + return conditionalFormats, err +} + // UnsetConditionalFormat provides a function to unset the conditional format // by given worksheet name and range. func (f *File) UnsetConditionalFormat(sheet, area string) error { @@ -3001,9 +3172,9 @@ func drawCondFmtDataBar(p int, ct string, format *formatConditional) *xlsxCfRule } } -// drawConfFmtExp provides a function to create conditional formatting rule +// drawCondFmtExp provides a function to create conditional formatting rule // for expression by given priority, criteria type and format settings. -func drawConfFmtExp(p int, ct string, format *formatConditional) *xlsxCfRule { +func drawCondFmtExp(p int, ct string, format *formatConditional) *xlsxCfRule { return &xlsxCfRule{ Priority: p + 1, Type: validType[format.Type], diff --git a/styles_test.go b/styles_test.go index 156b4e3..96cc2ad 100644 --- a/styles_test.go +++ b/styles_test.go @@ -175,6 +175,33 @@ func TestSetConditionalFormat(t *testing.T) { } } +func TestGetConditionalFormats(t *testing.T) { + for _, format := range []string{ + `[{"type":"cell","format":1,"criteria":"greater than","value":"6"}]`, + `[{"type":"cell","format":1,"criteria":"between","minimum":"6","maximum":"8"}]`, + `[{"type":"top","format":1,"criteria":"=","value":"6"}]`, + `[{"type":"bottom","format":1,"criteria":"=","value":"6"}]`, + `[{"type":"average","above_average":true,"format":1,"criteria":"="}]`, + `[{"type":"duplicate","format":1,"criteria":"="}]`, + `[{"type":"unique","format":1,"criteria":"="}]`, + `[{"type":"3_color_scale","criteria":"=","min_type":"num","mid_type":"num","max_type":"num","min_value":"-10","mid_value":"50","max_value":"10","min_color":"#FF0000","mid_color":"#00FF00","max_color":"#0000FF"}]`, + `[{"type":"2_color_scale","criteria":"=","min_type":"num","max_type":"num","min_color":"#FF0000","max_color":"#0000FF"}]`, + `[{"type":"data_bar","criteria":"=","min_type":"min","max_type":"max","bar_color":"#638EC6"}]`, + `[{"type":"formula","format":1,"criteria":"="}]`, + } { + f := NewFile() + err := f.SetConditionalFormat("Sheet1", "A1:A2", format) + assert.NoError(t, err) + formatSet, err := f.GetConditionalFormats("Sheet1") + assert.NoError(t, err) + assert.Equal(t, format, formatSet["A1:A2"]) + } + // Test get conditional formats on no exists worksheet + f := NewFile() + _, err := f.GetConditionalFormats("SheetN") + assert.EqualError(t, err, "sheet SheetN is not exist") +} + func TestUnsetConditionalFormat(t *testing.T) { f := NewFile() assert.NoError(t, f.SetCellValue("Sheet1", "A1", 7)) diff --git a/xmlWorksheet.go b/xmlWorksheet.go index 3b9caac..af7c4f3 100644 --- a/xmlWorksheet.go +++ b/xmlWorksheet.go @@ -826,10 +826,10 @@ type formatPanes struct { // formatConditional directly maps the conditional format settings of the cells. type formatConditional struct { Type string `json:"type"` - AboveAverage bool `json:"above_average"` - Percent bool `json:"percent"` - Format int `json:"format"` - Criteria string `json:"criteria"` + AboveAverage bool `json:"above_average,omitempty"` + Percent bool `json:"percent,omitempty"` + Format int `json:"format,omitempty"` + Criteria string `json:"criteria,omitempty"` Value string `json:"value,omitempty"` Minimum string `json:"minimum,omitempty"` Maximum string `json:"maximum,omitempty"` |