OSDN -- 开发和下载开源软件
简体中文翻译状态 translation status for zh
  • R/O
  • HTTP
  • SSH
  • HTTPS
Fork

excelize: 提交

Go language library for reading and writing Microsoft Excel™ (XLAM / XLSM / XLSX / XLTM / XLTX) spreadsheets


Commit MetaInfo

修订版f8667386dcde788d8232b652ac85a138c0d20bf3 (tree)
时间2022-08-27 01:45:46
作者chenliu1993 <13630583107@163....>
CommiterGitHub

Log Message

This closes #827, add new functions GetDataValidations and GetConditionalFormats (#1315)

Signed-off-by: chenliu1993 <13630583107@163.com>

更改概述

差异

--- a/datavalidation.go
+++ b/datavalidation.go
@@ -259,6 +259,18 @@ func (f *File) AddDataValidation(sheet string, dv *DataValidation) error {
259259 return err
260260 }
261261
262+// GetDataValidations returns data validations list by given worksheet name.
263+func (f *File) GetDataValidations(sheet string) ([]*DataValidation, error) {
264+ ws, err := f.workSheetReader(sheet)
265+ if err != nil {
266+ return nil, err
267+ }
268+ if ws.DataValidations == nil || len(ws.DataValidations.DataValidation) == 0 {
269+ return nil, err
270+ }
271+ return ws.DataValidations.DataValidation, err
272+}
273+
262274 // DeleteDataValidation delete data validation by given worksheet name and
263275 // reference sequence. All data validations in the worksheet will be deleted
264276 // if not specify reference sequence parameter.
--- a/datavalidation_test.go
+++ b/datavalidation_test.go
@@ -32,6 +32,11 @@ func TestDataValidation(t *testing.T) {
3232 dvRange.SetError(DataValidationErrorStyleWarning, "error title", "error body")
3333 dvRange.SetError(DataValidationErrorStyleInformation, "error title", "error body")
3434 assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
35+
36+ dataValidations, err := f.GetDataValidations("Sheet1")
37+ assert.NoError(t, err)
38+ assert.Equal(t, len(dataValidations), 1)
39+
3540 assert.NoError(t, f.SaveAs(resultFile))
3641
3742 dvRange = NewDataValidation(true)
@@ -39,6 +44,11 @@ func TestDataValidation(t *testing.T) {
3944 assert.NoError(t, dvRange.SetRange(10, 20, DataValidationTypeWhole, DataValidationOperatorGreaterThan))
4045 dvRange.SetInput("input title", "input body")
4146 assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
47+
48+ dataValidations, err = f.GetDataValidations("Sheet1")
49+ assert.NoError(t, err)
50+ assert.Equal(t, len(dataValidations), 2)
51+
4252 assert.NoError(t, f.SaveAs(resultFile))
4353
4454 f.NewSheet("Sheet2")
@@ -49,6 +59,12 @@ func TestDataValidation(t *testing.T) {
4959 assert.NoError(t, dvRange.SetRange("INDIRECT($A$2)", "INDIRECT($A$3)", DataValidationTypeWhole, DataValidationOperatorBetween))
5060 dvRange.SetError(DataValidationErrorStyleStop, "error title", "error body")
5161 assert.NoError(t, f.AddDataValidation("Sheet2", dvRange))
62+ dataValidations, err = f.GetDataValidations("Sheet1")
63+ assert.NoError(t, err)
64+ assert.Equal(t, len(dataValidations), 2)
65+ dataValidations, err = f.GetDataValidations("Sheet2")
66+ assert.NoError(t, err)
67+ assert.Equal(t, len(dataValidations), 1)
5268
5369 dvRange = NewDataValidation(true)
5470 dvRange.Sqref = "A5:B6"
@@ -67,7 +83,22 @@ func TestDataValidation(t *testing.T) {
6783 }
6884 assert.Equal(t, `<formula1>"A&lt;,B&gt;,C"",D ,E',F"</formula1>`, dvRange.Formula1)
6985 assert.NoError(t, f.AddDataValidation("Sheet1", dvRange))
86+
87+ dataValidations, err = f.GetDataValidations("Sheet1")
88+ assert.NoError(t, err)
89+ assert.Equal(t, len(dataValidations), 3)
90+
91+ // Test get data validation on no exists worksheet
92+ _, err = f.GetDataValidations("SheetN")
93+ assert.EqualError(t, err, "sheet SheetN is not exist")
94+
7095 assert.NoError(t, f.SaveAs(resultFile))
96+
97+ // Test get data validation on a worksheet without data validation settings
98+ f = NewFile()
99+ dataValidations, err = f.GetDataValidations("Sheet1")
100+ assert.NoError(t, err)
101+ assert.Equal(t, []*DataValidation(nil), dataValidations)
71102 }
72103
73104 func TestDataValidationError(t *testing.T) {
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -1041,15 +1041,15 @@ func TestConditionalFormat(t *testing.T) {
10411041 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"}]`))
10421042 // Color scales: 3 color.
10431043 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"}]`))
1044- // Hightlight cells rules: between...
1044+ // Highlight cells rules: between...
10451045 assert.NoError(t, f.SetConditionalFormat(sheet1, "C1:C10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format1)))
1046- // Hightlight cells rules: Greater Than...
1046+ // Highlight cells rules: Greater Than...
10471047 assert.NoError(t, f.SetConditionalFormat(sheet1, "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format3)))
1048- // Hightlight cells rules: Equal To...
1048+ // Highlight cells rules: Equal To...
10491049 assert.NoError(t, f.SetConditionalFormat(sheet1, "E1:E10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d}]`, format3)))
1050- // Hightlight cells rules: Not Equal To...
1050+ // Highlight cells rules: Not Equal To...
10511051 assert.NoError(t, f.SetConditionalFormat(sheet1, "F1:F10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, format2)))
1052- // Hightlight cells rules: Duplicate Values...
1052+ // Highlight cells rules: Duplicate Values...
10531053 assert.NoError(t, f.SetConditionalFormat(sheet1, "G1:G10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format2)))
10541054 // Top/Bottom rules: Top 10%.
10551055 assert.NoError(t, f.SetConditionalFormat(sheet1, "H1:H10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6","percent":true}]`, format1)))
--- a/styles.go
+++ b/styles.go
@@ -844,6 +844,31 @@ var criteriaType = map[string]string{
844844 "continue month": "continueMonth",
845845 }
846846
847+// operatorType defined the list of valid operator types.
848+var operatorType = map[string]string{
849+ "lastMonth": "last month",
850+ "between": "between",
851+ "notEqual": "not equal to",
852+ "greaterThan": "greater than",
853+ "lessThanOrEqual": "less than or equal to",
854+ "today": "today",
855+ "equal": "equal to",
856+ "notContains": "not containing",
857+ "thisWeek": "this week",
858+ "endsWith": "ends with",
859+ "yesterday": "yesterday",
860+ "lessThan": "less than",
861+ "beginsWith": "begins with",
862+ "last7Days": "last 7 days",
863+ "thisMonth": "this month",
864+ "containsText": "containing",
865+ "lastWeek": "last week",
866+ "continueWeek": "continue week",
867+ "continueMonth": "continue month",
868+ "notBetween": "not between",
869+ "greaterThanOrEqual": "greater than or equal to",
870+}
871+
847872 // formatToInt provides a function to convert original string to integer
848873 // format as string type by given built-in number formats code and cell
849874 // string.
@@ -2726,7 +2751,7 @@ func (f *File) SetCellStyle(sheet, hCell, vCell string, styleID int) error {
27262751 // type: minimum - The minimum parameter is used to set the lower limiting value
27272752 // when the criteria is either "between" or "not between".
27282753 //
2729-// // Hightlight cells rules: between...
2754+// // Highlight cells rules: between...
27302755 // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format))
27312756 //
27322757 // 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 {
27442769 //
27452770 // type: duplicate - The duplicate type is used to highlight duplicate cells in a range:
27462771 //
2747-// // Hightlight cells rules: Duplicate Values...
2772+// // Highlight cells rules: Duplicate Values...
27482773 // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format))
27492774 //
27502775 // type: unique - The unique type is used to highlight unique cells in a range:
27512776 //
2752-// // Hightlight cells rules: Not Equal To...
2777+// // Highlight cells rules: Not Equal To...
27532778 // f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, format))
27542779 //
27552780 // 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 {
28372862 "2_color_scale": drawCondFmtColorScale,
28382863 "3_color_scale": drawCondFmtColorScale,
28392864 "dataBar": drawCondFmtDataBar,
2840- "expression": drawConfFmtExp,
2865+ "expression": drawCondFmtExp,
28412866 }
28422867
28432868 ws, err := f.workSheetReader(sheet)
@@ -2854,9 +2879,9 @@ func (f *File) SetConditionalFormat(sheet, area, formatSet string) error {
28542879 // Check for valid criteria types.
28552880 ct, ok = criteriaType[v.Criteria]
28562881 if ok || vt == "expression" {
2857- drawfunc, ok := drawContFmtFunc[vt]
2882+ drawFunc, ok := drawContFmtFunc[vt]
28582883 if ok {
2859- cfRule = append(cfRule, drawfunc(p, ct, v))
2884+ cfRule = append(cfRule, drawFunc(p, ct, v))
28602885 }
28612886 }
28622887 }
@@ -2869,6 +2894,152 @@ func (f *File) SetConditionalFormat(sheet, area, formatSet string) error {
28692894 return err
28702895 }
28712896
2897+// extractCondFmtCellIs provides a function to extract conditional format
2898+// settings for cell value (include between, not between, equal, not equal,
2899+// greater than and less than) by given conditional formatting rule.
2900+func extractCondFmtCellIs(c *xlsxCfRule) *formatConditional {
2901+ format := formatConditional{Type: "cell", Criteria: operatorType[c.Operator], Format: *c.DxfID}
2902+ if len(c.Formula) == 2 {
2903+ format.Minimum, format.Maximum = c.Formula[0], c.Formula[1]
2904+ return &format
2905+ }
2906+ format.Value = c.Formula[0]
2907+ return &format
2908+}
2909+
2910+// extractCondFmtTop10 provides a function to extract conditional format
2911+// settings for top N (default is top 10) by given conditional formatting
2912+// rule.
2913+func extractCondFmtTop10(c *xlsxCfRule) *formatConditional {
2914+ format := formatConditional{
2915+ Type: "top",
2916+ Criteria: "=",
2917+ Format: *c.DxfID,
2918+ Percent: c.Percent,
2919+ Value: strconv.Itoa(c.Rank),
2920+ }
2921+ if c.Bottom {
2922+ format.Type = "bottom"
2923+ }
2924+ return &format
2925+}
2926+
2927+// extractCondFmtAboveAverage provides a function to extract conditional format
2928+// settings for above average and below average by given conditional formatting
2929+// rule.
2930+func extractCondFmtAboveAverage(c *xlsxCfRule) *formatConditional {
2931+ return &formatConditional{
2932+ Type: "average",
2933+ Criteria: "=",
2934+ Format: *c.DxfID,
2935+ AboveAverage: *c.AboveAverage,
2936+ }
2937+}
2938+
2939+// extractCondFmtDuplicateUniqueValues provides a function to extract
2940+// conditional format settings for duplicate and unique values by given
2941+// conditional formatting rule.
2942+func extractCondFmtDuplicateUniqueValues(c *xlsxCfRule) *formatConditional {
2943+ return &formatConditional{
2944+ Type: map[string]string{
2945+ "duplicateValues": "duplicate",
2946+ "uniqueValues": "unique",
2947+ }[c.Type],
2948+ Criteria: "=",
2949+ Format: *c.DxfID,
2950+ }
2951+}
2952+
2953+// extractCondFmtColorScale provides a function to extract conditional format
2954+// settings for color scale (include 2 color scale and 3 color scale) by given
2955+// conditional formatting rule.
2956+func extractCondFmtColorScale(c *xlsxCfRule) *formatConditional {
2957+ var format formatConditional
2958+ format.Type, format.Criteria = "2_color_scale", "="
2959+ values := len(c.ColorScale.Cfvo)
2960+ colors := len(c.ColorScale.Color)
2961+ if colors > 1 && values > 1 {
2962+ format.MinType = c.ColorScale.Cfvo[0].Type
2963+ if c.ColorScale.Cfvo[0].Val != "0" {
2964+ format.MinValue = c.ColorScale.Cfvo[0].Val
2965+ }
2966+ format.MinColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[0].RGB), "FF")
2967+ format.MaxType = c.ColorScale.Cfvo[1].Type
2968+ if c.ColorScale.Cfvo[1].Val != "0" {
2969+ format.MaxValue = c.ColorScale.Cfvo[1].Val
2970+ }
2971+ format.MaxColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[1].RGB), "FF")
2972+ }
2973+ if colors == 3 {
2974+ format.Type = "3_color_scale"
2975+ format.MidType = c.ColorScale.Cfvo[1].Type
2976+ if c.ColorScale.Cfvo[1].Val != "0" {
2977+ format.MidValue = c.ColorScale.Cfvo[1].Val
2978+ }
2979+ format.MidColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[1].RGB), "FF")
2980+ format.MaxType = c.ColorScale.Cfvo[2].Type
2981+ if c.ColorScale.Cfvo[2].Val != "0" {
2982+ format.MaxValue = c.ColorScale.Cfvo[2].Val
2983+ }
2984+ format.MaxColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[2].RGB), "FF")
2985+ }
2986+ return &format
2987+}
2988+
2989+// extractCondFmtDataBar provides a function to extract conditional format
2990+// settings for data bar by given conditional formatting rule.
2991+func extractCondFmtDataBar(c *xlsxCfRule) *formatConditional {
2992+ format := formatConditional{Type: "data_bar", Criteria: "="}
2993+ if c.DataBar != nil {
2994+ format.MinType = c.DataBar.Cfvo[0].Type
2995+ format.MaxType = c.DataBar.Cfvo[1].Type
2996+ format.BarColor = "#" + strings.TrimPrefix(strings.ToUpper(c.DataBar.Color[0].RGB), "FF")
2997+ }
2998+ return &format
2999+}
3000+
3001+// extractCondFmtExp provides a function to extract conditional format settings
3002+// for expression by given conditional formatting rule.
3003+func extractCondFmtExp(c *xlsxCfRule) *formatConditional {
3004+ format := formatConditional{Type: "formula", Format: *c.DxfID}
3005+ if len(c.Formula) > 0 {
3006+ format.Criteria = c.Formula[0]
3007+ }
3008+ return &format
3009+}
3010+
3011+// GetConditionalFormats returns conditional format settings by given worksheet
3012+// name.
3013+func (f *File) GetConditionalFormats(sheet string) (map[string]string, error) {
3014+ extractContFmtFunc := map[string]func(c *xlsxCfRule) *formatConditional{
3015+ "cellIs": extractCondFmtCellIs,
3016+ "top10": extractCondFmtTop10,
3017+ "aboveAverage": extractCondFmtAboveAverage,
3018+ "duplicateValues": extractCondFmtDuplicateUniqueValues,
3019+ "uniqueValues": extractCondFmtDuplicateUniqueValues,
3020+ "colorScale": extractCondFmtColorScale,
3021+ "dataBar": extractCondFmtDataBar,
3022+ "expression": extractCondFmtExp,
3023+ }
3024+
3025+ conditionalFormats := make(map[string]string)
3026+ ws, err := f.workSheetReader(sheet)
3027+ if err != nil {
3028+ return conditionalFormats, err
3029+ }
3030+ for _, cf := range ws.ConditionalFormatting {
3031+ var format []*formatConditional
3032+ for _, cr := range cf.CfRule {
3033+ if extractFunc, ok := extractContFmtFunc[cr.Type]; ok {
3034+ format = append(format, extractFunc(cr))
3035+ }
3036+ }
3037+ formatSet, _ := json.Marshal(format)
3038+ conditionalFormats[cf.SQRef] = string(formatSet)
3039+ }
3040+ return conditionalFormats, err
3041+}
3042+
28723043 // UnsetConditionalFormat provides a function to unset the conditional format
28733044 // by given worksheet name and range.
28743045 func (f *File) UnsetConditionalFormat(sheet, area string) error {
@@ -3001,9 +3172,9 @@ func drawCondFmtDataBar(p int, ct string, format *formatConditional) *xlsxCfRule
30013172 }
30023173 }
30033174
3004-// drawConfFmtExp provides a function to create conditional formatting rule
3175+// drawCondFmtExp provides a function to create conditional formatting rule
30053176 // for expression by given priority, criteria type and format settings.
3006-func drawConfFmtExp(p int, ct string, format *formatConditional) *xlsxCfRule {
3177+func drawCondFmtExp(p int, ct string, format *formatConditional) *xlsxCfRule {
30073178 return &xlsxCfRule{
30083179 Priority: p + 1,
30093180 Type: validType[format.Type],
--- a/styles_test.go
+++ b/styles_test.go
@@ -175,6 +175,33 @@ func TestSetConditionalFormat(t *testing.T) {
175175 }
176176 }
177177
178+func TestGetConditionalFormats(t *testing.T) {
179+ for _, format := range []string{
180+ `[{"type":"cell","format":1,"criteria":"greater than","value":"6"}]`,
181+ `[{"type":"cell","format":1,"criteria":"between","minimum":"6","maximum":"8"}]`,
182+ `[{"type":"top","format":1,"criteria":"=","value":"6"}]`,
183+ `[{"type":"bottom","format":1,"criteria":"=","value":"6"}]`,
184+ `[{"type":"average","above_average":true,"format":1,"criteria":"="}]`,
185+ `[{"type":"duplicate","format":1,"criteria":"="}]`,
186+ `[{"type":"unique","format":1,"criteria":"="}]`,
187+ `[{"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"}]`,
188+ `[{"type":"2_color_scale","criteria":"=","min_type":"num","max_type":"num","min_color":"#FF0000","max_color":"#0000FF"}]`,
189+ `[{"type":"data_bar","criteria":"=","min_type":"min","max_type":"max","bar_color":"#638EC6"}]`,
190+ `[{"type":"formula","format":1,"criteria":"="}]`,
191+ } {
192+ f := NewFile()
193+ err := f.SetConditionalFormat("Sheet1", "A1:A2", format)
194+ assert.NoError(t, err)
195+ formatSet, err := f.GetConditionalFormats("Sheet1")
196+ assert.NoError(t, err)
197+ assert.Equal(t, format, formatSet["A1:A2"])
198+ }
199+ // Test get conditional formats on no exists worksheet
200+ f := NewFile()
201+ _, err := f.GetConditionalFormats("SheetN")
202+ assert.EqualError(t, err, "sheet SheetN is not exist")
203+}
204+
178205 func TestUnsetConditionalFormat(t *testing.T) {
179206 f := NewFile()
180207 assert.NoError(t, f.SetCellValue("Sheet1", "A1", 7))
--- a/xmlWorksheet.go
+++ b/xmlWorksheet.go
@@ -826,10 +826,10 @@ type formatPanes struct {
826826 // formatConditional directly maps the conditional format settings of the cells.
827827 type formatConditional struct {
828828 Type string `json:"type"`
829- AboveAverage bool `json:"above_average"`
830- Percent bool `json:"percent"`
831- Format int `json:"format"`
832- Criteria string `json:"criteria"`
829+ AboveAverage bool `json:"above_average,omitempty"`
830+ Percent bool `json:"percent,omitempty"`
831+ Format int `json:"format,omitempty"`
832+ Criteria string `json:"criteria,omitempty"`
833833 Value string `json:"value,omitempty"`
834834 Minimum string `json:"minimum,omitempty"`
835835 Maximum string `json:"maximum,omitempty"`
Show on old repository browser