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

修订版3e2406096f89158dc0e46ee40aad29d42352a32d (tree)
时间2023-02-07 01:08:11
作者xuri <xuri.me@gmai...>
Commiterxuri

Log Message

This closes #1462 and closes #1464

- Support creating a conditional format with a "stop if true" rule
- Support set border color and create solid color for the color data bar
- Fix incorrect cell type when modifying string cell with the time number
- Update unit test for the add pivot table to avoid pivot table range overlap

更改概述

差异

--- a/cell.go
+++ b/cell.go
@@ -527,7 +527,7 @@ func (c *xlsxC) setCellDefault(value string) {
527527 c.T, c.V, c.IS = value, value, nil
528528 return
529529 }
530- c.V = value
530+ c.T, c.V = "", value
531531 }
532532
533533 // getCellDate parse cell value which contains a date in the ISO 8601 format.
--- a/numfmt.go
+++ b/numfmt.go
@@ -305,7 +305,7 @@ var (
305305 monthNamesYiSuffix = []string{"\ua2cd\ua1aa", "\ua44d\ua1aa", "\ua315\ua1aa", "\ua1d6\ua1aa", "\ua26c\ua1aa", "\ua0d8\ua1aa", "\ua3c3\ua1aa", "\ua246\ua1aa", "\ua22c\ua1aa", "\ua2b0\ua1aa", "\ua2b0\ua2aa\ua1aa", "\ua2b0\ua44b\ua1aa"}
306306 // monthNamesZulu list the month names in the Zulu.
307307 monthNamesZulu = []string{"Januwari", "Febhuwari", "Mashi", "Ephreli", "Meyi", "Juni", "Julayi", "Agasti", "Septemba", "Okthoba", "Novemba", "Disemba"}
308- // monthNamesZuluAbbr list teh month name abbreviations in Zulu
308+ // monthNamesZuluAbbr list the month name abbreviations in Zulu
309309 monthNamesZuluAbbr = []string{"Jan", "Feb", "Mas", "Eph", "Mey", "Jun", "Jul", "Agas", "Sep", "Okt", "Nov", "Dis"}
310310 // apFmtAfrikaans defined the AM/PM name in the Afrikaans.
311311 apFmtAfrikaans = "vm./nm."
--- a/pivotTable_test.go
+++ b/pivotTable_test.go
@@ -70,7 +70,7 @@ func TestAddPivotTable(t *testing.T) {
7070 }))
7171 assert.NoError(t, f.AddPivotTable(&PivotTableOptions{
7272 DataRange: "Sheet1!$A$1:$E$31",
73- PivotTableRange: "Sheet1!$G$37:$W$50",
73+ PivotTableRange: "Sheet1!$G$39:$W$52",
7474 Rows: []PivotTableField{{Data: "Month"}},
7575 Columns: []PivotTableField{{Data: "Region", DefaultSubtotal: true}, {Data: "Year"}},
7676 Data: []PivotTableField{{Data: "Sales", Subtotal: "CountNums", Name: "Summarize by CountNums"}},
--- a/styles.go
+++ b/styles.go
@@ -3190,8 +3190,21 @@ func (f *File) SetCellStyle(sheet, hCell, vCell string, styleID int) error {
31903190 // MaxColor - Same as MinColor, see above.
31913191 //
31923192 // BarColor - Used for data_bar. Same as MinColor, see above.
3193+//
3194+// BarBorderColor - Used for sets the color for the border line of a data bar,
3195+// this is only visible in Excel 2010 and later.
3196+//
3197+// BarOnly - Used for displays a bar data but not the data in the cells.
3198+//
3199+// BarSolid - Used for turns on a solid (non-gradient) fill for data bars, this
3200+// is only visible in Excel 2010 and later.
3201+//
3202+// StopIfTrue - used to set the "stop if true" feature of a conditional
3203+// formatting rule when more than one rule is applied to a cell or a range of
3204+// cells. When this parameter is set then subsequent rules are not evaluated
3205+// if the current rule is true.
31933206 func (f *File) SetConditionalFormat(sheet, rangeRef string, opts []ConditionalFormatOptions) error {
3194- drawContFmtFunc := map[string]func(p int, ct string, fmtCond *ConditionalFormatOptions) *xlsxCfRule{
3207+ drawContFmtFunc := map[string]func(p int, ct, GUID string, fmtCond *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule){
31953208 "cellIs": drawCondFmtCellIs,
31963209 "top10": drawCondFmtTop10,
31973210 "aboveAverage": drawCondFmtAboveAverage,
@@ -3207,6 +3220,12 @@ func (f *File) SetConditionalFormat(sheet, rangeRef string, opts []ConditionalFo
32073220 if err != nil {
32083221 return err
32093222 }
3223+ // Create a pseudo GUID for each unique rule.
3224+ var rules int
3225+ for _, cf := range ws.ConditionalFormatting {
3226+ rules += len(cf.CfRule)
3227+ }
3228+ GUID := fmt.Sprintf("{00000000-0000-0000-%04X-%012X}", f.getSheetID(sheet), rules)
32103229 var cfRule []*xlsxCfRule
32113230 for p, v := range opts {
32123231 var vt, ct string
@@ -3219,7 +3238,14 @@ func (f *File) SetConditionalFormat(sheet, rangeRef string, opts []ConditionalFo
32193238 if ok || vt == "expression" {
32203239 drawFunc, ok := drawContFmtFunc[vt]
32213240 if ok {
3222- cfRule = append(cfRule, drawFunc(p, ct, &v))
3241+ rule, x14rule := drawFunc(p, ct, GUID, &v)
3242+ if x14rule != nil {
3243+ if err = f.appendCfRule(ws, x14rule); err != nil {
3244+ return err
3245+ }
3246+ f.addSheetNameSpace(sheet, NameSpaceSpreadSheetX14)
3247+ }
3248+ cfRule = append(cfRule, rule)
32233249 }
32243250 }
32253251 }
@@ -3232,11 +3258,64 @@ func (f *File) SetConditionalFormat(sheet, rangeRef string, opts []ConditionalFo
32323258 return err
32333259 }
32343260
3261+// appendCfRule provides a function to append rules to conditional formatting.
3262+func (f *File) appendCfRule(ws *xlsxWorksheet, rule *xlsxX14CfRule) error {
3263+ var (
3264+ err error
3265+ idx int
3266+ decodeExtLst *decodeWorksheetExt
3267+ condFmts *xlsxX14ConditionalFormattings
3268+ decodeCondFmts *decodeX14ConditionalFormattings
3269+ ext *xlsxWorksheetExt
3270+ condFmtBytes, condFmtsBytes, extLstBytes, extBytes []byte
3271+ )
3272+ if ws.ExtLst != nil { // append mode ext
3273+ decodeExtLst = new(decodeWorksheetExt)
3274+ if err = f.xmlNewDecoder(strings.NewReader("<extLst>" + ws.ExtLst.Ext + "</extLst>")).
3275+ Decode(decodeExtLst); err != nil && err != io.EOF {
3276+ return err
3277+ }
3278+ for idx, ext = range decodeExtLst.Ext {
3279+ if ext.URI == ExtURIConditionalFormattings {
3280+ decodeCondFmts = new(decodeX14ConditionalFormattings)
3281+ _ = f.xmlNewDecoder(strings.NewReader(ext.Content)).Decode(decodeCondFmts)
3282+ condFmtBytes, _ = xml.Marshal([]*xlsxX14ConditionalFormatting{
3283+ {
3284+ XMLNSXM: NameSpaceSpreadSheetExcel2006Main.Value,
3285+ CfRule: []*xlsxX14CfRule{rule},
3286+ },
3287+ })
3288+ if condFmts == nil {
3289+ condFmts = &xlsxX14ConditionalFormattings{}
3290+ }
3291+ condFmts.Content = decodeCondFmts.Content + string(condFmtBytes)
3292+ condFmtsBytes, _ = xml.Marshal(condFmts)
3293+ decodeExtLst.Ext[idx].Content = string(condFmtsBytes)
3294+ }
3295+ }
3296+ extLstBytes, _ = xml.Marshal(decodeExtLst)
3297+ ws.ExtLst = &xlsxExtLst{
3298+ Ext: strings.TrimSuffix(strings.TrimPrefix(string(extLstBytes), "<extLst>"), "</extLst>"),
3299+ }
3300+ return err
3301+ }
3302+ condFmtBytes, _ = xml.Marshal([]*xlsxX14ConditionalFormatting{
3303+ {XMLNSXM: NameSpaceSpreadSheetExcel2006Main.Value, CfRule: []*xlsxX14CfRule{rule}},
3304+ })
3305+ condFmtsBytes, _ = xml.Marshal(&xlsxX14ConditionalFormattings{Content: string(condFmtBytes)})
3306+ extBytes, err = xml.Marshal(&xlsxWorksheetExt{
3307+ URI: ExtURIConditionalFormattings,
3308+ Content: string(condFmtsBytes),
3309+ })
3310+ ws.ExtLst = &xlsxExtLst{Ext: strings.TrimSuffix(strings.TrimPrefix(string(extBytes), "<extLst>"), "</extLst>")}
3311+ return err
3312+}
3313+
32353314 // extractCondFmtCellIs provides a function to extract conditional format
32363315 // settings for cell value (include between, not between, equal, not equal,
32373316 // greater than and less than) by given conditional formatting rule.
3238-func extractCondFmtCellIs(c *xlsxCfRule) ConditionalFormatOptions {
3239- format := ConditionalFormatOptions{Type: "cell", Criteria: operatorType[c.Operator], Format: *c.DxfID}
3317+func extractCondFmtCellIs(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
3318+ format := ConditionalFormatOptions{StopIfTrue: c.StopIfTrue, Type: "cell", Criteria: operatorType[c.Operator], Format: *c.DxfID}
32403319 if len(c.Formula) == 2 {
32413320 format.Minimum, format.Maximum = c.Formula[0], c.Formula[1]
32423321 return format
@@ -3248,13 +3327,14 @@ func extractCondFmtCellIs(c *xlsxCfRule) ConditionalFormatOptions {
32483327 // extractCondFmtTop10 provides a function to extract conditional format
32493328 // settings for top N (default is top 10) by given conditional formatting
32503329 // rule.
3251-func extractCondFmtTop10(c *xlsxCfRule) ConditionalFormatOptions {
3330+func extractCondFmtTop10(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
32523331 format := ConditionalFormatOptions{
3253- Type: "top",
3254- Criteria: "=",
3255- Format: *c.DxfID,
3256- Percent: c.Percent,
3257- Value: strconv.Itoa(c.Rank),
3332+ StopIfTrue: c.StopIfTrue,
3333+ Type: "top",
3334+ Criteria: "=",
3335+ Format: *c.DxfID,
3336+ Percent: c.Percent,
3337+ Value: strconv.Itoa(c.Rank),
32583338 }
32593339 if c.Bottom {
32603340 format.Type = "bottom"
@@ -3265,8 +3345,9 @@ func extractCondFmtTop10(c *xlsxCfRule) ConditionalFormatOptions {
32653345 // extractCondFmtAboveAverage provides a function to extract conditional format
32663346 // settings for above average and below average by given conditional formatting
32673347 // rule.
3268-func extractCondFmtAboveAverage(c *xlsxCfRule) ConditionalFormatOptions {
3348+func extractCondFmtAboveAverage(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
32693349 return ConditionalFormatOptions{
3350+ StopIfTrue: c.StopIfTrue,
32703351 Type: "average",
32713352 Criteria: "=",
32723353 Format: *c.DxfID,
@@ -3277,8 +3358,9 @@ func extractCondFmtAboveAverage(c *xlsxCfRule) ConditionalFormatOptions {
32773358 // extractCondFmtDuplicateUniqueValues provides a function to extract
32783359 // conditional format settings for duplicate and unique values by given
32793360 // conditional formatting rule.
3280-func extractCondFmtDuplicateUniqueValues(c *xlsxCfRule) ConditionalFormatOptions {
3361+func extractCondFmtDuplicateUniqueValues(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
32813362 return ConditionalFormatOptions{
3363+ StopIfTrue: c.StopIfTrue,
32823364 Type: map[string]string{
32833365 "duplicateValues": "duplicate",
32843366 "uniqueValues": "unique",
@@ -3291,8 +3373,8 @@ func extractCondFmtDuplicateUniqueValues(c *xlsxCfRule) ConditionalFormatOptions
32913373 // extractCondFmtColorScale provides a function to extract conditional format
32923374 // settings for color scale (include 2 color scale and 3 color scale) by given
32933375 // conditional formatting rule.
3294-func extractCondFmtColorScale(c *xlsxCfRule) ConditionalFormatOptions {
3295- var format ConditionalFormatOptions
3376+func extractCondFmtColorScale(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
3377+ format := ConditionalFormatOptions{StopIfTrue: c.StopIfTrue}
32963378 format.Type, format.Criteria = "2_color_scale", "="
32973379 values := len(c.ColorScale.Cfvo)
32983380 colors := len(c.ColorScale.Color)
@@ -3326,20 +3408,58 @@ func extractCondFmtColorScale(c *xlsxCfRule) ConditionalFormatOptions {
33263408
33273409 // extractCondFmtDataBar provides a function to extract conditional format
33283410 // settings for data bar by given conditional formatting rule.
3329-func extractCondFmtDataBar(c *xlsxCfRule) ConditionalFormatOptions {
3411+func extractCondFmtDataBar(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
33303412 format := ConditionalFormatOptions{Type: "data_bar", Criteria: "="}
33313413 if c.DataBar != nil {
3414+ format.StopIfTrue = c.StopIfTrue
33323415 format.MinType = c.DataBar.Cfvo[0].Type
33333416 format.MaxType = c.DataBar.Cfvo[1].Type
33343417 format.BarColor = "#" + strings.TrimPrefix(strings.ToUpper(c.DataBar.Color[0].RGB), "FF")
3418+ if c.DataBar.ShowValue != nil {
3419+ format.BarOnly = !*c.DataBar.ShowValue
3420+ }
3421+ }
3422+ extractDataBarRule := func(condFmts []decodeX14ConditionalFormatting) {
3423+ for _, condFmt := range condFmts {
3424+ for _, rule := range condFmt.CfRule {
3425+ if rule.DataBar != nil {
3426+ format.BarSolid = !rule.DataBar.Gradient
3427+ if rule.DataBar.BorderColor != nil {
3428+ format.BarBorderColor = "#" + strings.TrimPrefix(strings.ToUpper(rule.DataBar.BorderColor.RGB), "FF")
3429+ }
3430+ }
3431+ }
3432+ }
3433+ }
3434+ extractExtLst := func(extLst *decodeWorksheetExt) {
3435+ for _, ext := range extLst.Ext {
3436+ if ext.URI == ExtURIConditionalFormattings {
3437+ decodeCondFmts := new(decodeX14ConditionalFormattings)
3438+ if err := xml.Unmarshal([]byte(ext.Content), &decodeCondFmts); err == nil {
3439+ condFmts := []decodeX14ConditionalFormatting{}
3440+ if err = xml.Unmarshal([]byte(decodeCondFmts.Content), &condFmts); err == nil {
3441+ extractDataBarRule(condFmts)
3442+ }
3443+ }
3444+ }
3445+ }
3446+ }
3447+ if c.ExtLst != nil {
3448+ ext := decodeX14ConditionalFormattingExt{}
3449+ if err := xml.Unmarshal([]byte(c.ExtLst.Ext), &ext); err == nil && extLst != nil {
3450+ decodeExtLst := new(decodeWorksheetExt)
3451+ if err = xml.Unmarshal([]byte("<extLst>"+extLst.Ext+"</extLst>"), decodeExtLst); err == nil {
3452+ extractExtLst(decodeExtLst)
3453+ }
3454+ }
33353455 }
33363456 return format
33373457 }
33383458
33393459 // extractCondFmtExp provides a function to extract conditional format settings
33403460 // for expression by given conditional formatting rule.
3341-func extractCondFmtExp(c *xlsxCfRule) ConditionalFormatOptions {
3342- format := ConditionalFormatOptions{Type: "formula", Format: *c.DxfID}
3461+func extractCondFmtExp(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
3462+ format := ConditionalFormatOptions{StopIfTrue: c.StopIfTrue, Type: "formula", Format: *c.DxfID}
33433463 if len(c.Formula) > 0 {
33443464 format.Criteria = c.Formula[0]
33453465 }
@@ -3349,7 +3469,7 @@ func extractCondFmtExp(c *xlsxCfRule) ConditionalFormatOptions {
33493469 // GetConditionalFormats returns conditional format settings by given worksheet
33503470 // name.
33513471 func (f *File) GetConditionalFormats(sheet string) (map[string][]ConditionalFormatOptions, error) {
3352- extractContFmtFunc := map[string]func(c *xlsxCfRule) ConditionalFormatOptions{
3472+ extractContFmtFunc := map[string]func(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions{
33533473 "cellIs": extractCondFmtCellIs,
33543474 "top10": extractCondFmtTop10,
33553475 "aboveAverage": extractCondFmtAboveAverage,
@@ -3369,7 +3489,7 @@ func (f *File) GetConditionalFormats(sheet string) (map[string][]ConditionalForm
33693489 var opts []ConditionalFormatOptions
33703490 for _, cr := range cf.CfRule {
33713491 if extractFunc, ok := extractContFmtFunc[cr.Type]; ok {
3372- opts = append(opts, extractFunc(cr))
3492+ opts = append(opts, extractFunc(cr, ws.ExtLst))
33733493 }
33743494 }
33753495 conditionalFormats[cf.SQRef] = opts
@@ -3396,12 +3516,13 @@ func (f *File) UnsetConditionalFormat(sheet, rangeRef string) error {
33963516 // drawCondFmtCellIs provides a function to create conditional formatting rule
33973517 // for cell value (include between, not between, equal, not equal, greater
33983518 // than and less than) by given priority, criteria type and format settings.
3399-func drawCondFmtCellIs(p int, ct string, format *ConditionalFormatOptions) *xlsxCfRule {
3519+func drawCondFmtCellIs(p int, ct, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
34003520 c := &xlsxCfRule{
3401- Priority: p + 1,
3402- Type: validType[format.Type],
3403- Operator: ct,
3404- DxfID: &format.Format,
3521+ Priority: p + 1,
3522+ StopIfTrue: format.StopIfTrue,
3523+ Type: validType[format.Type],
3524+ Operator: ct,
3525+ DxfID: &format.Format,
34053526 }
34063527 // "between" and "not between" criteria require 2 values.
34073528 if ct == "between" || ct == "notBetween" {
@@ -3410,54 +3531,57 @@ func drawCondFmtCellIs(p int, ct string, format *ConditionalFormatOptions) *xlsx
34103531 if idx := inStrSlice([]string{"equal", "notEqual", "greaterThan", "lessThan", "greaterThanOrEqual", "lessThanOrEqual", "containsText", "notContains", "beginsWith", "endsWith"}, ct, true); idx != -1 {
34113532 c.Formula = append(c.Formula, format.Value)
34123533 }
3413- return c
3534+ return c, nil
34143535 }
34153536
34163537 // drawCondFmtTop10 provides a function to create conditional formatting rule
34173538 // for top N (default is top 10) by given priority, criteria type and format
34183539 // settings.
3419-func drawCondFmtTop10(p int, ct string, format *ConditionalFormatOptions) *xlsxCfRule {
3540+func drawCondFmtTop10(p int, ct, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
34203541 c := &xlsxCfRule{
3421- Priority: p + 1,
3422- Bottom: format.Type == "bottom",
3423- Type: validType[format.Type],
3424- Rank: 10,
3425- DxfID: &format.Format,
3426- Percent: format.Percent,
3542+ Priority: p + 1,
3543+ StopIfTrue: format.StopIfTrue,
3544+ Bottom: format.Type == "bottom",
3545+ Type: validType[format.Type],
3546+ Rank: 10,
3547+ DxfID: &format.Format,
3548+ Percent: format.Percent,
34273549 }
34283550 if rank, err := strconv.Atoi(format.Value); err == nil {
34293551 c.Rank = rank
34303552 }
3431- return c
3553+ return c, nil
34323554 }
34333555
34343556 // drawCondFmtAboveAverage provides a function to create conditional
34353557 // formatting rule for above average and below average by given priority,
34363558 // criteria type and format settings.
3437-func drawCondFmtAboveAverage(p int, ct string, format *ConditionalFormatOptions) *xlsxCfRule {
3559+func drawCondFmtAboveAverage(p int, ct, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
34383560 return &xlsxCfRule{
34393561 Priority: p + 1,
3562+ StopIfTrue: format.StopIfTrue,
34403563 Type: validType[format.Type],
34413564 AboveAverage: &format.AboveAverage,
34423565 DxfID: &format.Format,
3443- }
3566+ }, nil
34443567 }
34453568
34463569 // drawCondFmtDuplicateUniqueValues provides a function to create conditional
34473570 // formatting rule for duplicate and unique values by given priority, criteria
34483571 // type and format settings.
3449-func drawCondFmtDuplicateUniqueValues(p int, ct string, format *ConditionalFormatOptions) *xlsxCfRule {
3572+func drawCondFmtDuplicateUniqueValues(p int, ct, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
34503573 return &xlsxCfRule{
3451- Priority: p + 1,
3452- Type: validType[format.Type],
3453- DxfID: &format.Format,
3454- }
3574+ Priority: p + 1,
3575+ StopIfTrue: format.StopIfTrue,
3576+ Type: validType[format.Type],
3577+ DxfID: &format.Format,
3578+ }, nil
34553579 }
34563580
34573581 // drawCondFmtColorScale provides a function to create conditional formatting
34583582 // rule for color scale (include 2 color scale and 3 color scale) by given
34593583 // priority, criteria type and format settings.
3460-func drawCondFmtColorScale(p int, ct string, format *ConditionalFormatOptions) *xlsxCfRule {
3584+func drawCondFmtColorScale(p int, ct, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
34613585 minValue := format.MinValue
34623586 if minValue == "" {
34633587 minValue = "0"
@@ -3472,8 +3596,9 @@ func drawCondFmtColorScale(p int, ct string, format *ConditionalFormatOptions) *
34723596 }
34733597
34743598 c := &xlsxCfRule{
3475- Priority: p + 1,
3476- Type: "colorScale",
3599+ Priority: p + 1,
3600+ StopIfTrue: format.StopIfTrue,
3601+ Type: "colorScale",
34773602 ColorScale: &xlsxColorScale{
34783603 Cfvo: []*xlsxCfvo{
34793604 {Type: format.MinType, Val: minValue},
@@ -3489,31 +3614,53 @@ func drawCondFmtColorScale(p int, ct string, format *ConditionalFormatOptions) *
34893614 }
34903615 c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MaxType, Val: maxValue})
34913616 c.ColorScale.Color = append(c.ColorScale.Color, &xlsxColor{RGB: getPaletteColor(format.MaxColor)})
3492- return c
3617+ return c, nil
34933618 }
34943619
34953620 // drawCondFmtDataBar provides a function to create conditional formatting
34963621 // rule for data bar by given priority, criteria type and format settings.
3497-func drawCondFmtDataBar(p int, ct string, format *ConditionalFormatOptions) *xlsxCfRule {
3622+func drawCondFmtDataBar(p int, ct, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
3623+ var x14CfRule *xlsxX14CfRule
3624+ var extLst *xlsxExtLst
3625+ if format.BarSolid {
3626+ extLst = &xlsxExtLst{Ext: fmt.Sprintf(`<ext uri="%s" xmlns:x14="%s"><x14:id>%s</x14:id></ext>`, ExtURIConditionalFormattingRuleID, NameSpaceSpreadSheetX14.Value, GUID)}
3627+ x14CfRule = &xlsxX14CfRule{
3628+ Type: validType[format.Type],
3629+ ID: GUID,
3630+ DataBar: &xlsx14DataBar{
3631+ MaxLength: 100,
3632+ Cfvo: []*xlsxCfvo{{Type: "autoMin"}, {Type: "autoMax"}},
3633+ NegativeFillColor: &xlsxColor{RGB: "FFFF0000"},
3634+ AxisColor: &xlsxColor{RGB: "FFFF0000"},
3635+ },
3636+ }
3637+ if format.BarBorderColor != "" {
3638+ x14CfRule.DataBar.BorderColor = &xlsxColor{RGB: getPaletteColor(format.BarBorderColor)}
3639+ }
3640+ }
34983641 return &xlsxCfRule{
3499- Priority: p + 1,
3500- Type: validType[format.Type],
3642+ Priority: p + 1,
3643+ StopIfTrue: format.StopIfTrue,
3644+ Type: validType[format.Type],
35013645 DataBar: &xlsxDataBar{
3502- Cfvo: []*xlsxCfvo{{Type: format.MinType}, {Type: format.MaxType}},
3503- Color: []*xlsxColor{{RGB: getPaletteColor(format.BarColor)}},
3646+ ShowValue: boolPtr(!format.BarOnly),
3647+ Cfvo: []*xlsxCfvo{{Type: format.MinType}, {Type: format.MaxType}},
3648+ Color: []*xlsxColor{{RGB: getPaletteColor(format.BarColor)}},
35043649 },
3505- }
3650+ ExtLst: extLst,
3651+ }, x14CfRule
35063652 }
35073653
35083654 // drawCondFmtExp provides a function to create conditional formatting rule
35093655 // for expression by given priority, criteria type and format settings.
3510-func drawCondFmtExp(p int, ct string, format *ConditionalFormatOptions) *xlsxCfRule {
3656+func drawCondFmtExp(p int, ct, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
35113657 return &xlsxCfRule{
3512- Priority: p + 1,
3513- Type: validType[format.Type],
3514- Formula: []string{format.Criteria},
3515- DxfID: &format.Format,
3516- }
3658+ Priority: p + 1,
3659+ StopIfTrue: format.StopIfTrue,
3660+ Type: validType[format.Type],
3661+ Formula: []string{format.Criteria},
3662+ DxfID: &format.Format,
3663+ }, nil
35173664 }
35183665
35193666 // getPaletteColor provides a function to convert the RBG color by given
--- a/styles_test.go
+++ b/styles_test.go
@@ -159,12 +159,7 @@ func TestSetConditionalFormat(t *testing.T) {
159159 f := NewFile()
160160 const sheet = "Sheet1"
161161 const rangeRef = "A1:A1"
162-
163- err := f.SetConditionalFormat(sheet, rangeRef, testCase.format)
164- if err != nil {
165- t.Fatalf("%s", err)
166- }
167-
162+ assert.NoError(t, f.SetConditionalFormat(sheet, rangeRef, testCase.format))
168163 ws, err := f.workSheetReader(sheet)
169164 assert.NoError(t, err)
170165 cf := ws.ConditionalFormatting
@@ -173,6 +168,19 @@ func TestSetConditionalFormat(t *testing.T) {
173168 assert.Equal(t, rangeRef, cf[0].SQRef, testCase.label)
174169 assert.EqualValues(t, testCase.rules, cf[0].CfRule, testCase.label)
175170 }
171+ // Test creating a conditional format with a solid color data bar style
172+ f := NewFile()
173+ condFmts := []ConditionalFormatOptions{
174+ {Type: "data_bar", BarColor: "#A9D08E", BarSolid: true, Format: 0, Criteria: "=", MinType: "min", MaxType: "max"},
175+ }
176+ for _, ref := range []string{"A1:A2", "B1:B2"} {
177+ assert.NoError(t, f.SetConditionalFormat("Sheet1", ref, condFmts))
178+ }
179+ // Test creating a conditional format with invalid extension list characters
180+ ws, ok := f.Sheet.Load("xl/worksheets/sheet1.xml")
181+ assert.True(t, ok)
182+ ws.(*xlsxWorksheet).ExtLst.Ext = "<extLst><ext><x14:conditionalFormattings></x14:conditionalFormatting></x14:conditionalFormattings></ext></extLst>"
183+ assert.EqualError(t, f.SetConditionalFormat("Sheet1", "A1:A2", condFmts), "XML syntax error on line 1: element <conditionalFormattings> closed by </conditionalFormatting>")
176184 }
177185
178186 func TestGetConditionalFormats(t *testing.T) {
@@ -186,7 +194,7 @@ func TestGetConditionalFormats(t *testing.T) {
186194 {{Type: "unique", Format: 1, Criteria: "="}},
187195 {{Type: "3_color_scale", Criteria: "=", MinType: "num", MidType: "num", MaxType: "num", MinValue: "-10", MidValue: "50", MaxValue: "10", MinColor: "#FF0000", MidColor: "#00FF00", MaxColor: "#0000FF"}},
188196 {{Type: "2_color_scale", Criteria: "=", MinType: "num", MaxType: "num", MinColor: "#FF0000", MaxColor: "#0000FF"}},
189- {{Type: "data_bar", Criteria: "=", MinType: "min", MaxType: "max", BarColor: "#638EC6"}},
197+ {{Type: "data_bar", Criteria: "=", MinType: "min", MaxType: "max", BarColor: "#638EC6", BarBorderColor: "#0000FF", BarOnly: true, BarSolid: true, StopIfTrue: true}},
190198 {{Type: "formula", Format: 1, Criteria: "="}},
191199 } {
192200 f := NewFile()
--- a/xmlDrawing.go
+++ b/xmlDrawing.go
@@ -91,19 +91,20 @@ const (
9191 // ([ISO/IEC29500-1:2016] section 18.2.10) of the worksheet element
9292 // ([ISO/IEC29500-1:2016] section 18.3.1.99) is extended by the addition of
9393 // new child ext elements ([ISO/IEC29500-1:2016] section 18.2.7)
94- ExtURIConditionalFormattings = "{78C0D931-6437-407D-A8EE-F0AAD7539E65}"
95- ExtURIDataValidations = "{CCE6A557-97BC-4B89-ADB6-D9C93CAAB3DF}"
96- ExtURIDrawingBlip = "{28A0092B-C50C-407E-A947-70E740481C1C}"
97- ExtURIIgnoredErrors = "{01252117-D84E-4E92-8308-4BE1C098FCBB}"
98- ExtURIMacExcelMX = "{64002731-A6B0-56B0-2670-7721B7C09600}"
99- ExtURIProtectedRanges = "{FC87AEE6-9EDD-4A0A-B7FB-166176984837}"
100- ExtURISlicerCachesListX14 = "{BBE1A952-AA13-448e-AADC-164F8A28A991}"
101- ExtURISlicerListX14 = "{A8765BA9-456A-4DAB-B4F3-ACF838C121DE}"
102- ExtURISlicerListX15 = "{3A4CF648-6AED-40f4-86FF-DC5316D8AED3}"
103- ExtURISparklineGroups = "{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"
104- ExtURISVG = "{96DAC541-7B7A-43D3-8B79-37D633B846F1}"
105- ExtURITimelineRefs = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}"
106- ExtURIWebExtensions = "{F7C9EE02-42E1-4005-9D12-6889AFFD525C}"
94+ ExtURIConditionalFormattingRuleID = "{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"
95+ ExtURIConditionalFormattings = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}"
96+ ExtURIDataValidations = "{CCE6A557-97BC-4B89-ADB6-D9C93CAAB3DF}"
97+ ExtURIDrawingBlip = "{28A0092B-C50C-407E-A947-70E740481C1C}"
98+ ExtURIIgnoredErrors = "{01252117-D84E-4E92-8308-4BE1C098FCBB}"
99+ ExtURIMacExcelMX = "{64002731-A6B0-56B0-2670-7721B7C09600}"
100+ ExtURIProtectedRanges = "{FC87AEE6-9EDD-4A0A-B7FB-166176984837}"
101+ ExtURISlicerCachesListX14 = "{BBE1A952-AA13-448e-AADC-164F8A28A991}"
102+ ExtURISlicerListX14 = "{A8765BA9-456A-4DAB-B4F3-ACF838C121DE}"
103+ ExtURISlicerListX15 = "{3A4CF648-6AED-40f4-86FF-DC5316D8AED3}"
104+ ExtURISparklineGroups = "{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"
105+ ExtURISVG = "{96DAC541-7B7A-43D3-8B79-37D633B846F1}"
106+ ExtURITimelineRefs = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}"
107+ ExtURIWebExtensions = "{F7C9EE02-42E1-4005-9D12-6889AFFD525C}"
107108 )
108109
109110 // Excel specifications and limits
--- a/xmlWorksheet.go
+++ b/xmlWorksheet.go
@@ -592,7 +592,7 @@ type xlsxColorScale struct {
592592 type xlsxDataBar struct {
593593 MaxLength int `xml:"maxLength,attr,omitempty"`
594594 MinLength int `xml:"minLength,attr,omitempty"`
595- ShowValue bool `xml:"showValue,attr,omitempty"`
595+ ShowValue *bool `xml:"showValue,attr"`
596596 Cfvo []*xlsxCfvo `xml:"cfvo"`
597597 Color []*xlsxColor `xml:"color"`
598598 }
@@ -601,7 +601,7 @@ type xlsxDataBar struct {
601601 type xlsxIconSet struct {
602602 Cfvo []*xlsxCfvo `xml:"cfvo"`
603603 IconSet string `xml:"iconSet,attr,omitempty"`
604- ShowValue bool `xml:"showValue,attr,omitempty"`
604+ ShowValue *bool `xml:"showValue,attr"`
605605 Percent bool `xml:"percent,attr,omitempty"`
606606 Reverse bool `xml:"reverse,attr,omitempty"`
607607 }
@@ -742,6 +742,84 @@ type decodeX14SparklineGroups struct {
742742 Content string `xml:",innerxml"`
743743 }
744744
745+// decodeX14ConditionalFormattingExt directly maps the ext
746+// element.
747+type decodeX14ConditionalFormattingExt struct {
748+ XMLName xml.Name `xml:"ext"`
749+ ID string `xml:"id"`
750+}
751+
752+// decodeX14ConditionalFormattings directly maps the conditionalFormattings
753+// element.
754+type decodeX14ConditionalFormattings struct {
755+ XMLName xml.Name `xml:"conditionalFormattings"`
756+ XMLNSXM string `xml:"xmlns:xm,attr"`
757+ Content string `xml:",innerxml"`
758+}
759+
760+// decodeX14ConditionalFormatting directly maps the conditionalFormatting
761+// element.
762+type decodeX14ConditionalFormatting struct {
763+ XMLName xml.Name `xml:"conditionalFormatting"`
764+ CfRule []*decodeX14CfRule `xml:"cfRule"`
765+}
766+
767+// decodeX14CfRule directly maps the cfRule element.
768+type decodeX14CfRule struct {
769+ XNLName xml.Name `xml:"cfRule"`
770+ Type string `xml:"type,attr,omitempty"`
771+ ID string `xml:"id,attr,omitempty"`
772+ DataBar *decodeX14DataBar `xml:"dataBar"`
773+}
774+
775+// decodeX14DataBar directly maps the dataBar element.
776+type decodeX14DataBar struct {
777+ XNLName xml.Name `xml:"dataBar"`
778+ MaxLength int `xml:"maxLength,attr"`
779+ MinLength int `xml:"minLength,attr"`
780+ Gradient bool `xml:"gradient,attr"`
781+ ShowValue bool `xml:"showValue,attr,omitempty"`
782+ Cfvo []*xlsxCfvo `xml:"cfvo"`
783+ BorderColor *xlsxColor `xml:"borderColor"`
784+ NegativeFillColor *xlsxColor `xml:"negativeFillColor"`
785+ AxisColor *xlsxColor `xml:"axisColor"`
786+}
787+
788+// xlsxX14ConditionalFormattings directly maps the conditionalFormattings
789+// element.
790+type xlsxX14ConditionalFormattings struct {
791+ XMLName xml.Name `xml:"x14:conditionalFormattings"`
792+ Content string `xml:",innerxml"`
793+}
794+
795+// xlsxX14ConditionalFormatting directly maps the conditionalFormatting element.
796+type xlsxX14ConditionalFormatting struct {
797+ XMLName xml.Name `xml:"x14:conditionalFormatting"`
798+ XMLNSXM string `xml:"xmlns:xm,attr"`
799+ CfRule []*xlsxX14CfRule `xml:"x14:cfRule"`
800+}
801+
802+// xlsxX14CfRule directly maps the cfRule element.
803+type xlsxX14CfRule struct {
804+ XNLName xml.Name `xml:"x14:cfRule"`
805+ Type string `xml:"type,attr,omitempty"`
806+ ID string `xml:"id,attr,omitempty"`
807+ DataBar *xlsx14DataBar `xml:"x14:dataBar"`
808+}
809+
810+// xlsx14DataBar directly maps the dataBar element.
811+type xlsx14DataBar struct {
812+ XNLName xml.Name `xml:"x14:dataBar"`
813+ MaxLength int `xml:"maxLength,attr"`
814+ MinLength int `xml:"minLength,attr"`
815+ Gradient bool `xml:"gradient,attr"`
816+ ShowValue bool `xml:"showValue,attr,omitempty"`
817+ Cfvo []*xlsxCfvo `xml:"x14:cfvo"`
818+ BorderColor *xlsxColor `xml:"x14:borderColor"`
819+ NegativeFillColor *xlsxColor `xml:"x14:negativeFillColor"`
820+ AxisColor *xlsxColor `xml:"x14:axisColor"`
821+}
822+
745823 // xlsxX14SparklineGroups directly maps the sparklineGroups element.
746824 type xlsxX14SparklineGroups struct {
747825 XMLName xml.Name `xml:"x14:sparklineGroups"`
@@ -843,26 +921,30 @@ type Panes struct {
843921
844922 // ConditionalFormatOptions directly maps the conditional format settings of the cells.
845923 type ConditionalFormatOptions struct {
846- Type string
847- AboveAverage bool
848- Percent bool
849- Format int
850- Criteria string
851- Value string
852- Minimum string
853- Maximum string
854- MinType string
855- MidType string
856- MaxType string
857- MinValue string
858- MidValue string
859- MaxValue string
860- MinColor string
861- MidColor string
862- MaxColor string
863- MinLength string
864- MaxLength string
865- BarColor string
924+ Type string
925+ AboveAverage bool
926+ Percent bool
927+ Format int
928+ Criteria string
929+ Value string
930+ Minimum string
931+ Maximum string
932+ MinType string
933+ MidType string
934+ MaxType string
935+ MinValue string
936+ MidValue string
937+ MaxValue string
938+ MinColor string
939+ MidColor string
940+ MaxColor string
941+ MinLength string
942+ MaxLength string
943+ BarColor string
944+ BarBorderColor string
945+ BarOnly bool
946+ BarSolid bool
947+ StopIfTrue bool
866948 }
867949
868950 // SheetProtectionOptions directly maps the settings of worksheet protection.
Show on old repository browser