styles.go

// Copyright 2016 - 2025 The excelize Authors. All rights reserved. Use of
// this source code is governed by a BSD-style license that can be found in
// the LICENSE file.
//
// Package excelize providing a set of functions that allow you to write to and
// read from XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and
// writing spreadsheet documents generated by Microsoft Excel™ 2007 and later.
// Supports complex components by high compatibility, and provided streaming
// API for generating or reading data from a worksheet with huge amounts of
// data. This library needs Go version 1.23 or later.

package excelize

import (
	"bytes"
	"encoding/xml"
	"fmt"
	"io"
	"math"
	"reflect"
	"sort"
	"strconv"
	"strings"
)

// stylesReader provides a function to get the pointer to the structure after
// deserialization of xl/styles.xml.
func (f *File) stylesReader() (*xlsxStyleSheet, error) {
	if f.Styles == nil {
		f.Styles = new(xlsxStyleSheet)
		if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(defaultXMLPathStyles)))).
			Decode(f.Styles); err != nil && err != io.EOF {
			return f.Styles, err
		}
	}
	return f.Styles, nil
}

// styleSheetWriter provides a function to save xl/styles.xml after serialize
// structure.
func (f *File) styleSheetWriter() {
	if f.Styles != nil {
		output, _ := xml.Marshal(f.Styles)
		f.saveFileList(defaultXMLPathStyles, f.replaceNameSpaceBytes(defaultXMLPathStyles, output))
	}
}

// themeWriter provides a function to save xl/theme/theme1.xml after serialize
// structure.
func (f *File) themeWriter() {
	newColor := func(c *decodeCTColor) xlsxCTColor {
		return xlsxCTColor{
			ScrgbClr:  c.ScrgbClr,
			SrgbClr:   c.SrgbClr,
			HslClr:    c.HslClr,
			SysClr:    c.SysClr,
			SchemeClr: c.SchemeClr,
			PrstClr:   c.PrstClr,
		}
	}
	newFontScheme := func(c *decodeFontCollection) xlsxFontCollection {
		return xlsxFontCollection{
			Latin:  c.Latin,
			Ea:     c.Ea,
			Cs:     c.Cs,
			Font:   c.Font,
			ExtLst: c.ExtLst,
		}
	}
	if f.Theme != nil {
		output, _ := xml.Marshal(xlsxTheme{
			XMLNSa: NameSpaceDrawingML.Value,
			XMLNSr: SourceRelationship.Value,
			Name:   f.Theme.Name,
			ThemeElements: xlsxBaseStyles{
				ClrScheme: xlsxColorScheme{
					Name:     f.Theme.ThemeElements.ClrScheme.Name,
					Dk1:      newColor(&f.Theme.ThemeElements.ClrScheme.Dk1),
					Lt1:      newColor(&f.Theme.ThemeElements.ClrScheme.Lt1),
					Dk2:      newColor(&f.Theme.ThemeElements.ClrScheme.Dk2),
					Lt2:      newColor(&f.Theme.ThemeElements.ClrScheme.Lt2),
					Accent1:  newColor(&f.Theme.ThemeElements.ClrScheme.Accent1),
					Accent2:  newColor(&f.Theme.ThemeElements.ClrScheme.Accent2),
					Accent3:  newColor(&f.Theme.ThemeElements.ClrScheme.Accent3),
					Accent4:  newColor(&f.Theme.ThemeElements.ClrScheme.Accent4),
					Accent5:  newColor(&f.Theme.ThemeElements.ClrScheme.Accent5),
					Accent6:  newColor(&f.Theme.ThemeElements.ClrScheme.Accent6),
					Hlink:    newColor(&f.Theme.ThemeElements.ClrScheme.Hlink),
					FolHlink: newColor(&f.Theme.ThemeElements.ClrScheme.FolHlink),
					ExtLst:   f.Theme.ThemeElements.ClrScheme.ExtLst,
				},
				FontScheme: xlsxFontScheme{
					Name:      f.Theme.ThemeElements.FontScheme.Name,
					MajorFont: newFontScheme(&f.Theme.ThemeElements.FontScheme.MajorFont),
					MinorFont: newFontScheme(&f.Theme.ThemeElements.FontScheme.MinorFont),
					ExtLst:    f.Theme.ThemeElements.FontScheme.ExtLst,
				},
				FmtScheme: xlsxStyleMatrix{
					Name:           f.Theme.ThemeElements.FmtScheme.Name,
					FillStyleLst:   f.Theme.ThemeElements.FmtScheme.FillStyleLst,
					LnStyleLst:     f.Theme.ThemeElements.FmtScheme.LnStyleLst,
					EffectStyleLst: f.Theme.ThemeElements.FmtScheme.EffectStyleLst,
					BgFillStyleLst: f.Theme.ThemeElements.FmtScheme.BgFillStyleLst,
				},
				ExtLst: f.Theme.ThemeElements.ExtLst,
			},
			ObjectDefaults:    f.Theme.ObjectDefaults,
			ExtraClrSchemeLst: f.Theme.ExtraClrSchemeLst,
			CustClrLst:        f.Theme.CustClrLst,
			ExtLst:            f.Theme.ExtLst,
		})
		f.saveFileList(defaultXMLPathTheme, f.replaceNameSpaceBytes(defaultXMLPathTheme, output))
	}
}

// sharedStringsWriter provides a function to save xl/sharedStrings.xml after
// serialize structure.
func (f *File) sharedStringsWriter() {
	if f.SharedStrings != nil {
		output, _ := xml.Marshal(f.SharedStrings)
		f.saveFileList(defaultXMLPathSharedStrings, f.replaceNameSpaceBytes(defaultXMLPathSharedStrings, output))
	}
}

// parseFormatStyleSet provides a function to parse the format settings of the
// cells and conditional formats.
func parseFormatStyleSet(style *Style) (*Style, error) {
	var err error
	if style.Font != nil {
		if len(style.Font.Family) > MaxFontFamilyLength {
			return style, ErrFontLength
		}
		if style.Font.Size > MaxFontSize {
			return style, ErrFontSize
		}
	}
	if style.CustomNumFmt != nil && len(*style.CustomNumFmt) == 0 {
		err = ErrCustomNumFmt
	}
	return style, err
}

// NewStyle provides a function to create the style for cells by a given style
// options, and returns style index. The same style index can not be used
// across different workbook. This function is concurrency safe. Note that
// the 'Font.Color' field uses an RGB color represented in 'RRGGBB' hexadecimal
// notation.
//
// The following table shows the border types used in 'Border.Type' supported by
// excelize:
//
//	 Type         | Description
//	--------------+------------------
//	 left         | Left border
//	 top          | Top border
//	 right        | Right border
//	 bottom       | Bottom border
//	 diagonalDown | Diagonal down border
//	 diagonalUp   | Diagonal up border
//
// The following table shows the border styles used in 'Border.Style' supported
// by excelize index number:
//
//	 Index | Name          | Weight | Style
//	-------+---------------+--------+-------------
//	 0     | None          | 0      |
//	 1     | Continuous    | 1      | -----------
//	 2     | Continuous    | 2      | -----------
//	 3     | Dash          | 1      | - - - - - -
//	 4     | Dot           | 1      | . . . . . .
//	 5     | Continuous    | 3      | -----------
//	 6     | Double        | 3      | ===========
//	 7     | Continuous    | 0      | -----------
//	 8     | Dash          | 2      | - - - - - -
//	 9     | Dash Dot      | 1      | - . - . - .
//	 10    | Dash Dot      | 2      | - . - . - .
//	 11    | Dash Dot Dot  | 1      | - . . - . .
//	 12    | Dash Dot Dot  | 2      | - . . - . .
//	 13    | SlantDash Dot | 2      | / - . / - .
//
// The following table shows the border styles used in 'Border.Style' in the
// order shown in the Excel dialog:
//
//	 Index | Style       | Index | Style
//	-------+-------------+-------+-------------
//	 0     | None        | 12    | - . . - . .
//	 7     | ----------- | 13    | / - . / - .
//	 4     | . . . . . . | 10    | - . - . - .
//	 11    | - . . - . . | 8     | - - - - - -
//	 9     | - . - . - . | 2     | -----------
//	 3     | - - - - - - | 5     | -----------
//	 1     | ----------- | 6     | ===========
//
// The following table shows the shading styles used in 'Fill.Shading' supported
// by excelize index number:
//
//	 Index | Style           | Index | Style
//	-------+-----------------+-------+-----------------
//	 0-2   | Horizontal      | 9-11  | Diagonal down
//	 3-5   | Vertical        | 12-15 | From corner
//	 6-8   | Diagonal Up     | 16    | From center
//
// The following table shows the pattern styles used in 'Fill.Pattern' supported
// by excelize index number:
//
//	 Index | Style           | Index | Style
//	-------+-----------------+-------+-----------------
//	 0     | None            | 10    | darkTrellis
//	 1     | solid           | 11    | lightHorizontal
//	 2     | mediumGray      | 12    | lightVertical
//	 3     | darkGray        | 13    | lightDown
//	 4     | lightGray       | 14    | lightUp
//	 5     | darkHorizontal  | 15    | lightGrid
//	 6     | darkVertical    | 16    | lightTrellis
//	 7     | darkDown        | 17    | gray125
//	 8     | darkUp          | 18    | gray0625
//	 9     | darkGrid        |       |
//
// The 'Alignment.Indent' is an integer value, where an increment of 1
// represents 3 spaces. Indicates the number of spaces (of the normal style
// font) of indentation for text in a cell. The number of spaces to indent is
// calculated as following:
//
//	Number of spaces to indent = indent value * 3
//
// For example, an indent value of 1 means that the text begins 3 space widths
// (of the normal style font) from the edge of the cell. Note: The width of one
// space character is defined by the font. Only left, right, and distributed
// horizontal alignments are supported.
//
// The following table shows the type of cells' horizontal alignment used
// in 'Alignment.Horizontal':
//
//	 Style
//	------------------
//	 left
//	 center
//	 right
//	 fill
//	 justify
//	 centerContinuous
//	 distributed
//
// The following table shows the type of cells' vertical alignment used in
// 'Alignment.Vertical':
//
//	 Style
//	------------------
//	 top
//	 center
//	 justify
//	 distributed
//
// The 'Alignment.ReadingOrder' is an uint64 value indicating whether the
// reading order of the cell is left-to-right, right-to-left, or context
// dependent. the valid value of this field was:
//
//	 Value | Description
//	-------+----------------------------------------------------
//	 0     | Context Dependent - reading order is determined by scanning the
//	       | text for the first non-whitespace character: if it is a strong
//	       | right-to-left character, the reading order is right-to-left;
//	       | otherwise, the reading order left-to-right.
//	 1     | Left-to-Right: reading order is left-to-right in the cell, as in
//	       | English.
//	 2     | Right-to-Left: reading order is right-to-left in the cell, as in
//	       | Hebrew.
//
// The 'Alignment.RelativeIndent' is an integer value to indicate the additional
// number of spaces of indentation to adjust for text in a cell.
//
// The following table shows the type of font underline style used in
// 'Font.Underline':
//
//	 Style
//	------------------
//	 none
//	 single
//	 double
//
// NumFmt is used to set the built-in all languages formats index, built-in
// language formats index, or built-in currency formats index, it doesn't work
// when you specify the custom number format by CustomNumFmt. When you get
// style definition by the GetStyle or GetConditionalStyle function, the NumFmt
// only works if the number format code is exactly equal with any built-in all
// languages format code, built-in language formats code, or built-in currency
// format code.
//
// Excel's built-in all languages formats are shown in the following table:
//
//	 Index | Format String
//	-------+----------------------------------------------------
//	 0     | General
//	 1     | 0
//	 2     | 0.00
//	 3     | #,##0
//	 4     | #,##0.00
//	 5     | ($#,##0_);($#,##0)
//	 6     | ($#,##0_);[Red]($#,##0)
//	 7     | ($#,##0.00_);($#,##0.00)
//	 8     | ($#,##0.00_);[Red]($#,##0.00)
//	 9     | 0%
//	 10    | 0.00%
//	 11    | 0.00E+00
//	 12    | # ?/?
//	 13    | # ??/??
//	 14    | m/d/yy
//	 15    | d-mmm-yy
//	 16    | d-mmm
//	 17    | mmm-yy
//	 18    | h:mm AM/PM
//	 19    | h:mm:ss AM/PM
//	 20    | h:mm
//	 21    | h:mm:ss
//	 22    | m/d/yy h:mm
//	 ...   | ...
//	 37    | (#,##0_);(#,##0)
//	 38    | (#,##0_);[Red](#,##0)
//	 39    | (#,##0.00_);(#,##0.00)
//	 40    | (#,##0.00_);[Red](#,##0.00)
//	 41    | _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
//	 42    | _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
//	 43    | _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
//	 44    | _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
//	 45    | mm:ss
//	 46    | [h]:mm:ss
//	 47    | mm:ss.0
//	 48    | ##0.0E+0
//	 49    | @
//
// Number format code in zh-tw language:
//
//	 Index | Symbol
//	-------+-------------------------------------------
//	 27    | [$-404]e/m/d
//	 28    | [$-404]e"年"m"月"d"日"
//	 29    | [$-404]e"年"m"月"d"日"
//	 30    | m/d/yy
//	 31    | yyyy"年"m"月"d"日"
//	 32    | hh"時"mm"分"
//	 33    | hh"時"mm"分"ss"秒"
//	 34    | 上午/下午 hh"時"mm"分"
//	 35    | 上午/下午 hh"時"mm"分"ss"秒"
//	 36    | [$-404]e/m/d
//	 50    | [$-404]e/m/d
//	 51    | [$-404]e"年"m"月"d"日"
//	 52    | 上午/下午 hh"時"mm"分"
//	 53    | 上午/下午 hh"時"mm"分"ss"秒"
//	 54    | [$-404]e"年"m"月"d"日"
//	 55    | 上午/下午 hh"時"mm"分"
//	 56    | 上午/下午 hh"時"mm"分"ss"秒"
//	 57    | [$-404]e/m/d
//	 58    | [$-404]e"年"m"月"d"日"
//
// Number format code in zh-cn language:
//
//	 Index | Symbol
//	-------+-------------------------------------------
//	 27    | yyyy"年"m"月"
//	 28    | m"月"d"日"
//	 29    | m"月"d"日"
//	 30    | m-d-yy
//	 31    | yyyy"年"m"月"d"日"
//	 32    | h"时"mm"分"
//	 33    | h"时"mm"分"ss"秒"
//	 34    | 上午/下午 h"时"mm"分"
//	 35    | 上午/下午 h"时"mm"分"ss"秒
//	 36    | yyyy"年"m"月
//	 50    | yyyy"年"m"月
//	 51    | m"月"d"日
//	 52    | yyyy"年"m"月
//	 53    | m"月"d"日
//	 54    | m"月"d"日
//	 55    | 上午/下午 h"时"mm"分
//	 56    | 上午/下午 h"时"mm"分"ss"秒
//	 57    | yyyy"年"m"月
//	 58    | m"月"d"日"
//
// Number format code in ja-jp language:
//
//	 Index | Symbol
//	-------+-------------------------------------------
//	 27    | [$-411]ge.m.d
//	 28    | [$-411]ggge"年"m"月"d"日
//	 29    | [$-411]ggge"年"m"月"d"日
//	 30    | m/d/y
//	 31    | yyyy"年"m"月"d"日
//	 32    | h"時"mm"分
//	 33    | h"時"mm"分"ss"秒
//	 34    | yyyy"年"m"月
//	 35    | m"月"d"日
//	 36    | [$-411]ge.m.d
//	 50    | [$-411]ge.m.d
//	 51    | [$-411]ggge"年"m"月"d"日
//	 52    | yyyy"年"m"月
//	 53    | m"月"d"日
//	 54    | [$-411]ggge"年"m"月"d"日
//	 55    | yyyy"年"m"月
//	 56    | m"月"d"日
//	 57    | [$-411]ge.m.d
//	 58    | [$-411]ggge"年"m"月"d"日"
//
// Number format code in ko-kr language:
//
//	 Index | Symbol
//	-------+-------------------------------------------
//	 27    | yyyy"年" mm"月" dd"日
//	 28    | mm-d
//	 29    | mm-d
//	 30    | mm-dd-y
//	 31    | yyyy"년" mm"월" dd"일
//	 32    | h"시" mm"분
//	 33    | h"시" mm"분" ss"초
//	 34    | yyyy-mm-d
//	 35    | yyyy-mm-d
//	 36    | yyyy"年" mm"月" dd"日
//	 50    | yyyy"年" mm"月" dd"日
//	 51    | mm-d
//	 52    | yyyy-mm-d
//	 53    | yyyy-mm-d
//	 54    | mm-d
//	 55    | yyyy-mm-d
//	 56    | yyyy-mm-d
//	 57    | yyyy"年" mm"月" dd"日
//	 58    | mm-dd
//
// Number format code in th-th language:
//
//	 Index | Symbol
//	-------+-------------------------------------------
//	 59    | t
//	 60    | t0.0
//	 61    | t#,##
//	 62    | t#,##0.0
//	 67    | t0
//	 68    | t0.00
//	 69    | t# ?/
//	 70    | t# ??/?
//	 71    | ว/ด/ปปป
//	 72    | ว-ดดด-ป
//	 73    | ว-ดด
//	 74    | ดดด-ป
//	 75    | ช:น
//	 76    | ช:นน:ท
//	 77    | ว/ด/ปปปป ช:น
//	 78    | นน:ท
//	 79    | [ช]:นน:ท
//	 80    | นน:ทท.
//	 81    | d/m/bb
//
// Excelize built-in currency formats are shown in the following table, only
// support these types in the following table (Index number is used only for
// markup and is not used inside an Excel file and you can't get formatted value
// by the function GetCellValue) currently:
//
//	 Index | Symbol
//	-------+---------------------------------------------------------------
//	 164   | ¥
//	 165   | $ English (United States)
//	 166   | $ Cherokee (United States)
//	 167   | $ Chinese (Singapore)
//	 168   | $ Chinese (Taiwan)
//	 169   | $ English (Australia)
//	 170   | $ English (Belize)
//	 171   | $ English (Canada)
//	 172   | $ English (Jamaica)
//	 173   | $ English (New Zealand)
//	 174   | $ English (Singapore)
//	 175   | $ English (Trinidad & Tobago)
//	 176   | $ English (U.S. Virgin Islands)
//	 177   | $ English (United States)
//	 178   | $ French (Canada)
//	 179   | $ Hawaiian (United States)
//	 180   | $ Malay (Brunei)
//	 181   | $ Quechua (Ecuador)
//	 182   | $ Spanish (Chile)
//	 183   | $ Spanish (Colombia)
//	 184   | $ Spanish (Ecuador)
//	 185   | $ Spanish (El Salvador)
//	 186   | $ Spanish (Mexico)
//	 187   | $ Spanish (Puerto Rico)
//	 188   | $ Spanish (United States)
//	 189   | $ Spanish (Uruguay)
//	 190   | £ English (United Kingdom)
//	 191   | £ Scottish Gaelic (United Kingdom)
//	 192   | £ Welsh (United Kindom)
//	 193   | ¥ Chinese (China)
//	 194   | ¥ Japanese (Japan)
//	 195   | ¥ Sichuan Yi (China)
//	 196   | ¥ Tibetan (China)
//	 197   | ¥ Uyghur (China)
//	 198   | ֏ Armenian (Armenia)
//	 199   | ؋ Pashto (Afghanistan)
//	 200   | ؋ Persian (Afghanistan)
//	 201   | ৳ Bengali (Bangladesh)
//	 202   | ៛ Khmer (Cambodia)
//	 203   | ₡ Spanish (Costa Rica)
//	 204   | ₦ Hausa (Nigeria)
//	 205   | ₦ Igbo (Nigeria)
//	 206   | ₩ Korean (South Korea)
//	 207   | ₪ Hebrew (Israel)
//	 208   | ₫ Vietnamese (Vietnam)
//	 209   | € Basque (Spain)
//	 210   | € Breton (France)
//	 211   | € Catalan (Spain)
//	 212   | € Corsican (France)
//	 213   | € Dutch (Belgium)
//	 214   | € Dutch (Netherlands)
//	 215   | € English (Ireland)
//	 216   | € Estonian (Estonia)
//	 217   | € Euro (€ 123)
//	 218   | € Euro (123 €)
//	 219   | € Finnish (Finland)
//	 220   | € French (Belgium)
//	 221   | € French (France)
//	 222   | € French (Luxembourg)
//	 223   | € French (Monaco)
//	 224   | € French (Réunion)
//	 225   | € Galician (Spain)
//	 226   | € German (Austria)
//	 227   | € German (German)
//	 228   | € German (Luxembourg)
//	 229   | € Greek (Greece)
//	 230   | € Inari Sami (Finland)
//	 231   | € Irish (Ireland)
//	 232   | € Italian (Italy)
//	 233   | € Latin (Italy)
//	 234   | € Latin, Serbian (Montenegro)
//	 235   | € Larvian (Latvia)
//	 236   | € Lithuanian (Lithuania)
//	 237   | € Lower Sorbian (Germany)
//	 238   | € Luxembourgish (Luxembourg)
//	 239   | € Maltese (Malta)
//	 240   | € Northern Sami (Finland)
//	 241   | € Occitan (France)
//	 242   | € Portuguese (Portugal)
//	 243   | € Serbian (Montenegro)
//	 244   | € Skolt Sami (Finland)
//	 245   | € Slovak (Slovakia)
//	 246   | € Slovenian (Slovenia)
//	 247   | € Spanish (Spain)
//	 248   | € Swedish (Finland)
//	 249   | € Swiss German (France)
//	 250   | € Upper Sorbian (Germany)
//	 251   | € Western Frisian (Netherlands)
//	 252   | ₭ Lao (Laos)
//	 253   | ₮ Mongolian (Mongolia)
//	 254   | ₮ Mongolian, Mongolian (Mongolia)
//	 255   | ₱ English (Philippines)
//	 256   | ₱ Filipino (Philippines)
//	 257   | ₴ Ukrainian (Ukraine)
//	 258   | ₸ Kazakh (Kazakhstan)
//	 259   | ₹ Arabic, Kashmiri (India)
//	 260   | ₹ English (India)
//	 261   | ₹ Gujarati (India)
//	 262   | ₹ Hindi (India)
//	 263   | ₹ Kannada (India)
//	 264   | ₹ Kashmiri (India)
//	 265   | ₹ Konkani (India)
//	 266   | ₹ Manipuri (India)
//	 267   | ₹ Marathi (India)
//	 268   | ₹ Nepali (India)
//	 269   | ₹ Oriya (India)
//	 270   | ₹ Punjabi (India)
//	 271   | ₹ Sanskrit (India)
//	 272   | ₹ Sindhi (India)
//	 273   | ₹ Tamil (India)
//	 274   | ₹ Urdu (India)
//	 275   | ₺ Turkish (Turkey)
//	 276   | ₼ Azerbaijani (Azerbaijan)
//	 277   | ₼ Cyrillic, Azerbaijani (Azerbaijan)
//	 278   | ₽ Russian (Russia)
//	 279   | ₽ Sakha (Russia)
//	 280   | ₾ Georgian (Georgia)
//	 281   | B/. Spanish (Panama)
//	 282   | Br Oromo (Ethiopia)
//	 283   | Br Somali (Ethiopia)
//	 284   | Br Tigrinya (Ethiopia)
//	 285   | Bs Quechua (Bolivia)
//	 286   | Bs Spanish (Bolivia)
//	 287   | BS. Spanish (Venezuela)
//	 288   | BWP Tswana (Botswana)
//	 289   | C$ Spanish (Nicaragua)
//	 290   | CA$ Latin, Inuktitut (Canada)
//	 291   | CA$ Mohawk (Canada)
//	 292   | CA$ Unified Canadian Aboriginal Syllabics, Inuktitut (Canada)
//	 293   | CFA French (Mali)
//	 294   | CFA French (Senegal)
//	 295   | CFA Fulah (Senegal)
//	 296   | CFA Wolof (Senegal)
//	 297   | CHF French (Switzerland)
//	 298   | CHF German (Liechtenstein)
//	 299   | CHF German (Switzerland)
//	 300   | CHF Italian (Switzerland)
//	 301   | CHF Romansh (Switzerland)
//	 302   | CLP Mapuche (Chile)
//	 303   | CN¥ Mongolian, Mongolian (China)
//	 304   | DZD Central Atlas Tamazight (Algeria)
//	 305   | FCFA French (Cameroon)
//	 306   | Ft Hungarian (Hungary)
//	 307   | G French (Haiti)
//	 308   | Gs. Spanish (Paraguay)
//	 309   | GTQ K'iche' (Guatemala)
//	 310   | HK$ Chinese (Hong Kong (China))
//	 311   | HK$ English (Hong Kong (China))
//	 312   | HRK Croatian (Croatia)
//	 313   | IDR English (Indonesia)
//	 314   | IQD Arbic, Central Kurdish (Iraq)
//	 315   | ISK Icelandic (Iceland)
//	 316   | K Burmese (Myanmar (Burma))
//	 317   | Kč Czech (Czech Republic)
//	 318   | KM Bosnian (Bosnia & Herzegovina)
//	 319   | KM Croatian (Bosnia & Herzegovina)
//	 320   | KM Latin, Serbian (Bosnia & Herzegovina)
//	 321   | kr Faroese (Faroe Islands)
//	 322   | kr Northern Sami (Norway)
//	 323   | kr Northern Sami (Sweden)
//	 324   | kr Norwegian Bokmål (Norway)
//	 325   | kr Norwegian Nynorsk (Norway)
//	 326   | kr Swedish (Sweden)
//	 327   | kr. Danish (Denmark)
//	 328   | kr. Kalaallisut (Greenland)
//	 329   | Ksh Swahili (kenya)
//	 330   | L Romanian (Moldova)
//	 331   | L Russian (Moldova)
//	 332   | L Spanish (Honduras)
//	 333   | Lekë Albanian (Albania)
//	 334   | MAD Arabic, Central Atlas Tamazight (Morocco)
//	 335   | MAD French (Morocco)
//	 336   | MAD Tifinagh, Central Atlas Tamazight (Morocco)
//	 337   | MOP$ Chinese (Macau (China))
//	 338   | MVR Divehi (Maldives)
//	 339   | Nfk Tigrinya (Eritrea)
//	 340   | NGN Bini (Nigeria)
//	 341   | NGN Fulah (Nigeria)
//	 342   | NGN Ibibio (Nigeria)
//	 343   | NGN Kanuri (Nigeria)
//	 344   | NOK Lule Sami (Norway)
//	 345   | NOK Southern Sami (Norway)
//	 346   | NZ$ Maori (New Zealand)
//	 347   | PKR Sindhi (Pakistan)
//	 348   | PYG Guarani (Paraguay)
//	 349   | Q Spanish (Guatemala)
//	 350   | R Afrikaans (South Africa)
//	 351   | R English (South Africa)
//	 352   | R Zulu (South Africa)
//	 353   | R$ Portuguese (Brazil)
//	 354   | RD$ Spanish (Dominican Republic)
//	 355   | RF Kinyarwanda (Rwanda)
//	 356   | RM English (Malaysia)
//	 357   | RM Malay (Malaysia)
//	 358   | RON Romanian (Romania)
//	 359   | Rp Indonesoan (Indonesia)
//	 360   | Rs Urdu (Pakistan)
//	 361   | Rs. Tamil (Sri Lanka)
//	 362   | RSD Latin, Serbian (Serbia)
//	 363   | RSD Serbian (Serbia)
//	 364   | RUB Bashkir (Russia)
//	 365   | RUB Tatar (Russia)
//	 366   | S/. Quechua (Peru)
//	 367   | S/. Spanish (Peru)
//	 368   | SEK Lule Sami (Sweden)
//	 369   | SEK Southern Sami (Sweden)
//	 370   | soʻm Latin, Uzbek (Uzbekistan)
//	 371   | soʻm Uzbek (Uzbekistan)
//	 372   | SYP Syriac (Syria)
//	 373   | THB Thai (Thailand)
//	 374   | TMT Turkmen (Turkmenistan)
//	 375   | US$ English (Zimbabwe)
//	 376   | ZAR Northern Sotho (South Africa)
//	 377   | ZAR Southern Sotho (South Africa)
//	 378   | ZAR Tsonga (South Africa)
//	 379   | ZAR Tswana (south Africa)
//	 380   | ZAR Venda (South Africa)
//	 381   | ZAR Xhosa (South Africa)
//	 382   | zł Polish (Poland)
//	 383   | ден Macedonian (Macedonia)
//	 384   | KM Cyrillic, Bosnian (Bosnia & Herzegovina)
//	 385   | KM Serbian (Bosnia & Herzegovina)
//	 386   | лв. Bulgarian (Bulgaria)
//	 387   | p. Belarusian (Belarus)
//	 388   | сом Kyrgyz (Kyrgyzstan)
//	 389   | сом Tajik (Tajikistan)
//	 390   | ج.م. Arabic (Egypt)
//	 391   | د.أ. Arabic (Jordan)
//	 392   | د.أ. Arabic (United Arab Emirates)
//	 393   | د.ب. Arabic (Bahrain)
//	 394   | د.ت. Arabic (Tunisia)
//	 395   | د.ج. Arabic (Algeria)
//	 396   | د.ع. Arabic (Iraq)
//	 397   | د.ك. Arabic (Kuwait)
//	 398   | د.ل. Arabic (Libya)
//	 399   | د.م. Arabic (Morocco)
//	 400   | ر Punjabi (Pakistan)
//	 401   | ر.س. Arabic (Saudi Arabia)
//	 402   | ر.ع. Arabic (Oman)
//	 403   | ر.ق. Arabic (Qatar)
//	 404   | ر.ي. Arabic (Yemen)
//	 405   | ریال Persian (Iran)
//	 406   | ل.س. Arabic (Syria)
//	 407   | ل.ل. Arabic (Lebanon)
//	 408   | ብር Amharic (Ethiopia)
//	 409   | रू Nepaol (Nepal)
//	 410   | රු. Sinhala (Sri Lanka)
//	 411   | ADP
//	 412   | AED
//	 413   | AFA
//	 414   | AFN
//	 415   | ALL
//	 416   | AMD
//	 417   | ANG
//	 418   | AOA
//	 419   | ARS
//	 420   | ATS
//	 421   | AUD
//	 422   | AWG
//	 423   | AZM
//	 424   | AZN
//	 425   | BAM
//	 426   | BBD
//	 427   | BDT
//	 428   | BEF
//	 429   | BGL
//	 430   | BGN
//	 431   | BHD
//	 432   | BIF
//	 433   | BMD
//	 434   | BND
//	 435   | BOB
//	 436   | BOV
//	 437   | BRL
//	 438   | BSD
//	 439   | BTN
//	 440   | BWP
//	 441   | BYR
//	 442   | BZD
//	 443   | CAD
//	 444   | CDF
//	 445   | CHE
//	 446   | CHF
//	 447   | CHW
//	 448   | CLF
//	 449   | CLP
//	 450   | CNY
//	 451   | COP
//	 452   | COU
//	 453   | CRC
//	 454   | CSD
//	 455   | CUC
//	 456   | CVE
//	 457   | CYP
//	 458   | CZK
//	 459   | DEM
//	 460   | DJF
//	 461   | DKK
//	 462   | DOP
//	 463   | DZD
//	 464   | ECS
//	 465   | ECV
//	 466   | EEK
//	 467   | EGP
//	 468   | ERN
//	 469   | ESP
//	 470   | ETB
//	 471   | EUR
//	 472   | FIM
//	 473   | FJD
//	 474   | FKP
//	 475   | FRF
//	 476   | GBP
//	 477   | GEL
//	 478   | GHC
//	 479   | GHS
//	 480   | GIP
//	 481   | GMD
//	 482   | GNF
//	 483   | GRD
//	 484   | GTQ
//	 485   | GYD
//	 486   | HKD
//	 487   | HNL
//	 488   | HRK
//	 489   | HTG
//	 490   | HUF
//	 491   | IDR
//	 492   | IEP
//	 493   | ILS
//	 494   | INR
//	 495   | IQD
//	 496   | IRR
//	 497   | ISK
//	 498   | ITL
//	 499   | JMD
//	 500   | JOD
//	 501   | JPY
//	 502   | KAF
//	 503   | KES
//	 504   | KGS
//	 505   | KHR
//	 506   | KMF
//	 507   | KPW
//	 508   | KRW
//	 509   | KWD
//	 510   | KYD
//	 511   | KZT
//	 512   | LAK
//	 513   | LBP
//	 514   | LKR
//	 515   | LRD
//	 516   | LSL
//	 517   | LTL
//	 518   | LUF
//	 519   | LVL
//	 520   | LYD
//	 521   | MAD
//	 522   | MDL
//	 523   | MGA
//	 524   | MGF
//	 525   | MKD
//	 526   | MMK
//	 527   | MNT
//	 528   | MOP
//	 529   | MRO
//	 530   | MTL
//	 531   | MUR
//	 532   | MVR
//	 533   | MWK
//	 534   | MXN
//	 535   | MXV
//	 536   | MYR
//	 537   | MZM
//	 538   | MZN
//	 539   | NAD
//	 540   | NGN
//	 541   | NIO
//	 542   | NLG
//	 543   | NOK
//	 544   | NPR
//	 545   | NTD
//	 546   | NZD
//	 547   | OMR
//	 548   | PAB
//	 549   | PEN
//	 550   | PGK
//	 551   | PHP
//	 552   | PKR
//	 553   | PLN
//	 554   | PTE
//	 555   | PYG
//	 556   | QAR
//	 557   | ROL
//	 558   | RON
//	 559   | RSD
//	 560   | RUB
//	 561   | RUR
//	 562   | RWF
//	 563   | SAR
//	 564   | SBD
//	 565   | SCR
//	 566   | SDD
//	 567   | SDG
//	 568   | SDP
//	 569   | SEK
//	 570   | SGD
//	 571   | SHP
//	 572   | SIT
//	 573   | SKK
//	 574   | SLL
//	 575   | SOS
//	 576   | SPL
//	 577   | SRD
//	 578   | SRG
//	 579   | STD
//	 580   | SVC
//	 581   | SYP
//	 582   | SZL
//	 583   | THB
//	 584   | TJR
//	 585   | TJS
//	 586   | TMM
//	 587   | TMT
//	 588   | TND
//	 589   | TOP
//	 590   | TRL
//	 591   | TRY
//	 592   | TTD
//	 593   | TWD
//	 594   | TZS
//	 595   | UAH
//	 596   | UGX
//	 597   | USD
//	 598   | USN
//	 599   | USS
//	 600   | UYI
//	 601   | UYU
//	 602   | UZS
//	 603   | VEB
//	 604   | VEF
//	 605   | VND
//	 606   | VUV
//	 607   | WST
//	 608   | XAF
//	 609   | XAG
//	 610   | XAU
//	 611   | XB5
//	 612   | XBA
//	 613   | XBB
//	 614   | XBC
//	 615   | XBD
//	 616   | XCD
//	 617   | XDR
//	 618   | XFO
//	 619   | XFU
//	 620   | XOF
//	 621   | XPD
//	 622   | XPF
//	 623   | XPT
//	 624   | XTS
//	 625   | XXX
//	 626   | YER
//	 627   | YUM
//	 628   | ZAR
//	 629   | ZMK
//	 630   | ZMW
//	 631   | ZWD
//	 632   | ZWL
//	 633   | ZWN
//	 634   | ZWR
//
// Excelize support set custom number format for cell by CustomNumFmt field. For
// example, set number as date type in Uruguay (Spanish) format for Sheet1!A6:
//
//	f := excelize.NewFile()
//	defer func() {
//	    if err := f.Close(); err != nil {
//	        fmt.Println(err)
//	    }
//	}()
//	if err := f.SetCellValue("Sheet1", "A6", 42920.5); err != nil {
//	    fmt.Println(err)
//	    return
//	}
//	exp := "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"
//	style, err := f.NewStyle(&excelize.Style{CustomNumFmt: &exp})
//	if err != nil {
//	    fmt.Println(err)
//	    return
//	}
//	err = f.SetCellStyle("Sheet1", "A6", "A6", style)
//
// Cell Sheet1!A6 in the spreadsheet application: martes, 04 de Julio de 2017
//
// DecimalPlaces is used to set the decimal places for built-in currency
// formats, it doesn't work if you have specified the built-in all languages
// formats or built-in language formats by NumFmt field, or specify the custom
// number format by CustomNumFmt. When you get style definition by the GetStyle
// or GetConditionalStyle function, the DecimalPlaces only doesn't nil if a
// number format code has the same decimal places in the positive part negative
// part, or only the positive part.
func (f *File) NewStyle(style *Style) (int, error) {
	var (
		fs                                  *Style
		font                                *xlsxFont
		err                                 error
		cellXfsID, fontID, borderID, fillID int
	)
	if style == nil {
		return cellXfsID, err
	}
	fs, err = parseFormatStyleSet(style)
	if err != nil {
		return cellXfsID, err
	}
	if fs.DecimalPlaces != nil && (*fs.DecimalPlaces < 0 || *fs.DecimalPlaces > 30) {
		fs.DecimalPlaces = intPtr(2)
	}
	f.mu.Lock()
	s, err := f.stylesReader()
	if err != nil {
		f.mu.Unlock()
		return cellXfsID, err
	}
	f.mu.Unlock()
	s.mu.Lock()
	defer s.mu.Unlock()
	// check given style already exist.
	if cellXfsID, err = f.getStyleID(s, fs); err != nil || cellXfsID != -1 {
		return cellXfsID, err
	}

	numFmtID := newNumFmt(s, fs)

	if fs.Font != nil {
		fontID, _ = f.getFontID(s, fs)
		if fontID == -1 {
			s.Fonts.Count++
			font, _ = f.newFont(fs)
			s.Fonts.Font = append(s.Fonts.Font, font)
			fontID = s.Fonts.Count - 1
		}
	}

	borderID = getBorderID(s, fs)
	if borderID == -1 {
		if len(fs.Border) == 0 {
			borderID = 0
		} else {
			s.Borders.Count++
			s.Borders.Border = append(s.Borders.Border, newBorders(fs))
			borderID = s.Borders.Count - 1
		}
	}

	if fillID = getFillID(s, fs); fillID == -1 {
		if fill := newFills(fs, true); fill != nil {
			s.Fills.Count++
			s.Fills.Fill = append(s.Fills.Fill, fill)
			fillID = s.Fills.Count - 1
		} else {
			fillID = 0
		}
	}

	applyAlignment, alignment := fs.Alignment != nil, newAlignment(fs)
	applyProtection, protection := fs.Protection != nil, newProtection(fs)
	return setCellXfs(s, fontID, numFmtID, fillID, borderID, applyAlignment, applyProtection, alignment, protection)
}

var (
	// styleBorders list all types of the cell border style.
	styleBorders = []string{
		"none",
		"thin",
		"medium",
		"dashed",
		"dotted",
		"thick",
		"double",
		"hair",
		"mediumDashed",
		"dashDot",
		"mediumDashDot",
		"dashDotDot",
		"mediumDashDotDot",
		"slantDashDot",
	}
	// styleBorderTypes list all types of the cell border.
	styleBorderTypes = []string{
		"left", "right", "top", "bottom", "diagonalUp", "diagonalDown",
	}
	// styleFillPatterns list all types of the cell fill style.
	styleFillPatterns = []string{
		"none",
		"solid",
		"mediumGray",
		"darkGray",
		"lightGray",
		"darkHorizontal",
		"darkVertical",
		"darkDown",
		"darkUp",
		"darkGrid",
		"darkTrellis",
		"lightHorizontal",
		"lightVertical",
		"lightDown",
		"lightUp",
		"lightGrid",
		"lightTrellis",
		"gray125",
		"gray0625",
	}
	// styleFillVariants list all preset variants of the fill style.
	styleFillVariants = func() []xlsxGradientFill {
		return []xlsxGradientFill{
			{Degree: 90, Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Degree: 270, Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Degree: 90, Stop: []*xlsxGradientFillStop{{}, {Position: 0.5}, {Position: 1}}},
			{Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Degree: 180, Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Stop: []*xlsxGradientFillStop{{}, {Position: 0.5}, {Position: 1}}},
			{Degree: 45, Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Degree: 255, Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Degree: 45, Stop: []*xlsxGradientFillStop{{}, {Position: 0.5}, {Position: 1}}},
			{Degree: 135, Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Degree: 315, Stop: []*xlsxGradientFillStop{{}, {Position: 1}}},
			{Degree: 135, Stop: []*xlsxGradientFillStop{{}, {Position: 0.5}, {Position: 1}}},
			{Stop: []*xlsxGradientFillStop{{}, {Position: 1}}, Type: "path"},
			{Stop: []*xlsxGradientFillStop{{}, {Position: 1}}, Type: "path", Left: 1, Right: 1},
			{Stop: []*xlsxGradientFillStop{{}, {Position: 1}}, Type: "path", Bottom: 1, Top: 1},
			{Stop: []*xlsxGradientFillStop{{}, {Position: 1}}, Type: "path", Bottom: 1, Left: 1, Right: 1, Top: 1},
			{Stop: []*xlsxGradientFillStop{{}, {Position: 1}}, Type: "path", Bottom: 0.5, Left: 0.5, Right: 0.5, Top: 0.5},
		}
	}

	// getXfIDFuncs provides a function to get xfID by given style.
	getXfIDFuncs = map[string]func(int, xlsxXf, *Style) bool{
		"numFmt": func(numFmtID int, xf xlsxXf, style *Style) bool {
			if style.CustomNumFmt == nil && numFmtID == -1 {
				return xf.NumFmtID != nil && *xf.NumFmtID == 0
			}
			if style.NegRed || (style.DecimalPlaces != nil && *style.DecimalPlaces != 2) {
				return false
			}
			return xf.NumFmtID != nil && *xf.NumFmtID == numFmtID
		},
		"font": func(fontID int, xf xlsxXf, style *Style) bool {
			if style.Font == nil {
				return (xf.FontID == nil || *xf.FontID == 0) && (xf.ApplyFont == nil || !*xf.ApplyFont)
			}
			return xf.FontID != nil && *xf.FontID == fontID && xf.ApplyFont != nil && *xf.ApplyFont
		},
		"fill": func(fillID int, xf xlsxXf, style *Style) bool {
			if style.Fill.Type == "" {
				return (xf.FillID == nil || *xf.FillID == 0) && (xf.ApplyFill == nil || !*xf.ApplyFill)
			}
			return xf.FillID != nil && *xf.FillID == fillID && xf.ApplyFill != nil && *xf.ApplyFill
		},
		"border": func(borderID int, xf xlsxXf, style *Style) bool {
			if len(style.Border) == 0 {
				return (xf.BorderID == nil || *xf.BorderID == 0) && (xf.ApplyBorder == nil || !*xf.ApplyBorder)
			}
			return xf.BorderID != nil && *xf.BorderID == borderID && xf.ApplyBorder != nil && *xf.ApplyBorder
		},
		"alignment": func(ID int, xf xlsxXf, style *Style) bool {
			if style.Alignment == nil {
				return xf.ApplyAlignment == nil || !*xf.ApplyAlignment
			}
			return reflect.DeepEqual(xf.Alignment, newAlignment(style))
		},
		"protection": func(ID int, xf xlsxXf, style *Style) bool {
			if style.Protection == nil {
				return xf.ApplyProtection == nil || !*xf.ApplyProtection
			}
			return reflect.DeepEqual(xf.Protection, newProtection(style)) && xf.ApplyProtection != nil && *xf.ApplyProtection
		},
	}

	// extractStyleCondFuncs provides a function set to returns if should be
	// extract style definition by given style.
	extractStyleCondFuncs = map[string]func(xlsxXf, *xlsxStyleSheet) bool{
		"fill": func(xf xlsxXf, s *xlsxStyleSheet) bool {
			return (xf.ApplyFill == nil || (xf.ApplyFill != nil && *xf.ApplyFill)) &&
				xf.FillID != nil && s.Fills != nil &&
				*xf.FillID < len(s.Fills.Fill)
		},
		"border": func(xf xlsxXf, s *xlsxStyleSheet) bool {
			return (xf.ApplyBorder == nil || (xf.ApplyBorder != nil && *xf.ApplyBorder)) &&
				xf.BorderID != nil && s.Borders != nil &&
				*xf.BorderID < len(s.Borders.Border)
		},
		"font": func(xf xlsxXf, s *xlsxStyleSheet) bool {
			return (xf.ApplyFont == nil || (xf.ApplyFont != nil && *xf.ApplyFont)) &&
				xf.FontID != nil && s.Fonts != nil &&
				*xf.FontID < len(s.Fonts.Font)
		},
		"alignment": func(xf xlsxXf, s *xlsxStyleSheet) bool {
			return xf.ApplyAlignment == nil || (xf.ApplyAlignment != nil && *xf.ApplyAlignment)
		},
		"protection": func(xf xlsxXf, s *xlsxStyleSheet) bool {
			return xf.ApplyProtection == nil || (xf.ApplyProtection != nil && *xf.ApplyProtection)
		},
	}

	// drawContFmtFunc defines functions to create conditional formats.
	drawContFmtFunc = map[string]func(p int, ct, ref, GUID string, fmtCond *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule){
		"cellIs":            drawCondFmtCellIs,
		"timePeriod":        drawCondFmtTimePeriod,
		"text":              drawCondFmtText,
		"top10":             drawCondFmtTop10,
		"aboveAverage":      drawCondFmtAboveAverage,
		"duplicateValues":   drawCondFmtDuplicateUniqueValues,
		"uniqueValues":      drawCondFmtDuplicateUniqueValues,
		"containsBlanks":    drawCondFmtBlanks,
		"notContainsBlanks": drawCondFmtNoBlanks,
		"containsErrors":    drawCondFmtErrors,
		"notContainsErrors": drawCondFmtNoErrors,
		"2_color_scale":     drawCondFmtColorScale,
		"3_color_scale":     drawCondFmtColorScale,
		"dataBar":           drawCondFmtDataBar,
		"expression":        drawCondFmtExp,
		"iconSet":           drawCondFmtIconSet,
	}

	// extractContFmtFunc defines functions to get conditional formats.
	extractContFmtFunc = map[string]func(*File, *xlsxCfRule, *xlsxExtLst) ConditionalFormatOptions{
		"cellIs": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtCellIs(c, extLst)
		},
		"timePeriod": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtTimePeriod(c, extLst)
		},
		"containsText": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtText(c, extLst)
		},
		"notContainsText": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtText(c, extLst)
		},
		"beginsWith": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtText(c, extLst)
		},
		"endsWith": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtText(c, extLst)
		},
		"top10": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtTop10(c, extLst)
		},
		"aboveAverage": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtAboveAverage(c, extLst)
		},
		"duplicateValues": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtDuplicateUniqueValues(c, extLst)
		},
		"uniqueValues": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtDuplicateUniqueValues(c, extLst)
		},
		"containsBlanks": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtBlanks(c, extLst)
		},
		"notContainsBlanks": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtNoBlanks(c, extLst)
		},
		"containsErrors": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtErrors(c, extLst)
		},
		"notContainsErrors": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtNoErrors(c, extLst)
		},
		"colorScale": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtColorScale(c, extLst)
		},
		"dataBar": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtDataBar(c, extLst)
		},
		"expression": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtExp(c, extLst)
		},
		"iconSet": func(f *File, c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
			return f.extractCondFmtIconSet(c, extLst)
		},
	}

	// validType defined the list of valid validation types.
	validType = map[string]string{
		"cell":          "cellIs",
		"average":       "aboveAverage",
		"duplicate":     "duplicateValues",
		"unique":        "uniqueValues",
		"top":           "top10",
		"bottom":        "top10",
		"text":          "text",
		"time_period":   "timePeriod",
		"blanks":        "containsBlanks",
		"no_blanks":     "notContainsBlanks",
		"errors":        "containsErrors",
		"no_errors":     "notContainsErrors",
		"2_color_scale": "2_color_scale",
		"3_color_scale": "3_color_scale",
		"data_bar":      "dataBar",
		"formula":       "expression",
		"icon_set":      "iconSet",
	}
	// criteriaType defined the list of valid criteria types.
	criteriaType = map[string]string{
		"!=":                       "notEqual",
		"<":                        "lessThan",
		"<=":                       "lessThanOrEqual",
		"<>":                       "notEqual",
		"=":                        "equal",
		"==":                       "equal",
		">":                        "greaterThan",
		">=":                       "greaterThanOrEqual",
		"begins with":              "beginsWith",
		"between":                  "between",
		"containing":               "containsText",
		"continue month":           "nextMonth",
		"continue week":            "nextWeek",
		"ends with":                "endsWith",
		"equal to":                 "equal",
		"greater than or equal to": "greaterThanOrEqual",
		"greater than":             "greaterThan",
		"last 7 days":              "last7Days",
		"last month":               "lastMonth",
		"last week":                "lastWeek",
		"less than or equal to":    "lessThanOrEqual",
		"less than":                "lessThan",
		"not between":              "notBetween",
		"not containing":           "notContains",
		"not equal to":             "notEqual",
		"this month":               "thisMonth",
		"this week":                "thisWeek",
		"today":                    "today",
		"tomorrow":                 "tomorrow",
		"yesterday":                "yesterday",
	}
	// operatorType defined the list of valid operator types.
	operatorType = map[string]string{
		"beginsWith":         "begins with",
		"between":            "between",
		"containsText":       "containing",
		"endsWith":           "ends with",
		"equal":              "equal to",
		"greaterThan":        "greater than",
		"greaterThanOrEqual": "greater than or equal to",
		"last7Days":          "last 7 days",
		"lastMonth":          "last month",
		"lastWeek":           "last week",
		"lessThan":           "less than",
		"lessThanOrEqual":    "less than or equal to",
		"nextMonth":          "continue month",
		"nextWeek":           "continue week",
		"notBetween":         "not between",
		"notContains":        "not containing",
		"notEqual":           "not equal to",
		"thisMonth":          "this month",
		"thisWeek":           "this week",
		"today":              "today",
		"tomorrow":           "tomorrow",
		"yesterday":          "yesterday",
	}
	// cellIsCriteriaType defined the list of valid criteria types used for
	// cellIs conditional formats.
	cellIsCriteriaType = []string{
		"equal",
		"notEqual",
		"greaterThan",
		"lessThan",
		"greaterThanOrEqual",
		"lessThanOrEqual",
		"containsText",
		"notContains",
		"beginsWith",
		"endsWith",
	}
	// cfvo3 defined the icon set conditional formatting rules.
	cfvo3 = &xlsxCfRule{IconSet: &xlsxIconSet{Cfvo: []*xlsxCfvo{
		{Type: "percent", Val: "0"},
		{Type: "percent", Val: "33"},
		{Type: "percent", Val: "67"},
	}}}
	// cfvo4 defined the icon set conditional formatting rules.
	cfvo4 = &xlsxCfRule{IconSet: &xlsxIconSet{Cfvo: []*xlsxCfvo{
		{Type: "percent", Val: "0"},
		{Type: "percent", Val: "25"},
		{Type: "percent", Val: "50"},
		{Type: "percent", Val: "75"},
	}}}
	// cfvo5 defined the icon set conditional formatting rules.
	cfvo5 = &xlsxCfRule{IconSet: &xlsxIconSet{Cfvo: []*xlsxCfvo{
		{Type: "percent", Val: "0"},
		{Type: "percent", Val: "20"},
		{Type: "percent", Val: "40"},
		{Type: "percent", Val: "60"},
		{Type: "percent", Val: "80"},
	}}}
	// condFmtIconSetPresets defined the list of icon set conditional formatting
	// rules.
	condFmtIconSetPresets = map[string]*xlsxCfRule{
		"3Arrows":         cfvo3,
		"3ArrowsGray":     cfvo3,
		"3Flags":          cfvo3,
		"3Signs":          cfvo3,
		"3Symbols":        cfvo3,
		"3Symbols2":       cfvo3,
		"3TrafficLights1": cfvo3,
		"3TrafficLights2": cfvo3,
		"4Arrows":         cfvo4,
		"4ArrowsGray":     cfvo4,
		"4Rating":         cfvo4,
		"4RedToBlack":     cfvo4,
		"4TrafficLights":  cfvo4,
		"5Arrows":         cfvo5,
		"5ArrowsGray":     cfvo5,
		"5Quarters":       cfvo5,
		"5Rating":         cfvo5,
	}
)

// colorChoice returns a hex color code from the actual color values.
func (clr *decodeCTColor) colorChoice() *string {
	if clr.SrgbClr != nil {
		return clr.SrgbClr.Val
	}
	if clr.SysClr != nil {
		return &clr.SysClr.LastClr
	}
	return nil
}

// GetBaseColor returns the preferred hex color code by giving hex color code,
// indexed color, and theme color.
func (f *File) GetBaseColor(hexColor string, indexedColor int, themeColor *int) string {
	if f.Theme != nil && themeColor != nil {
		clrScheme := f.Theme.ThemeElements.ClrScheme
		if val, ok := map[int]*string{
			0: clrScheme.Lt1.colorChoice(),
			1: clrScheme.Dk1.colorChoice(),
			2: clrScheme.Lt2.colorChoice(),
			3: clrScheme.Dk2.colorChoice(),
			4: clrScheme.Accent1.colorChoice(),
			5: clrScheme.Accent2.colorChoice(),
			6: clrScheme.Accent3.colorChoice(),
			7: clrScheme.Accent4.colorChoice(),
			8: clrScheme.Accent5.colorChoice(),
			9: clrScheme.Accent6.colorChoice(),
		}[*themeColor]; ok && val != nil {
			return *val
		}
	}
	if len(hexColor) == 6 {
		return hexColor
	}
	if len(hexColor) == 8 {
		return strings.TrimPrefix(hexColor, "FF")
	}
	if f.Styles != nil && f.Styles.Colors != nil && f.Styles.Colors.IndexedColors != nil &&
		indexedColor < len(f.Styles.Colors.IndexedColors.RgbColor) {
		return strings.TrimPrefix(f.Styles.Colors.IndexedColors.RgbColor[indexedColor].RGB, "FF")
	}
	if indexedColor < len(IndexedColorMapping) {
		return IndexedColorMapping[indexedColor]
	}
	return hexColor
}

// getThemeColor provides a function to convert theme color or index color to
// RGB color.
func (f *File) getThemeColor(clr *xlsxColor) string {
	var RGB string
	if clr == nil || f.Theme == nil {
		return RGB
	}
	if RGB = f.GetBaseColor(clr.RGB, clr.Indexed, clr.Theme); RGB != "" {
		RGB = strings.TrimPrefix(ThemeColor(RGB, clr.Tint), "FF")
	}
	return RGB
}

// extractBorders provides a function to extract borders styles settings by
// given border styles definition.
func (f *File) extractBorders(bdr *xlsxBorder, s *xlsxStyleSheet, style *Style) {
	if bdr != nil {
		var borders []Border
		extractBorder := func(lineType string, line *xlsxLine) {
			if line != nil && line.Style != "" {
				borders = append(borders, Border{
					Type:  lineType,
					Color: f.getThemeColor(line.Color),
					Style: inStrSlice(styleBorders, line.Style, false),
				})
			}
		}
		for i, line := range []*xlsxLine{
			bdr.Left, bdr.Right, bdr.Top, bdr.Bottom, bdr.Diagonal, bdr.Diagonal,
		} {
			if i < 4 {
				extractBorder(styleBorderTypes[i], line)
			}
			if i == 4 && bdr.DiagonalUp {
				extractBorder(styleBorderTypes[i], line)
			}
			if i == 5 && bdr.DiagonalDown {
				extractBorder(styleBorderTypes[i], line)
			}
		}
		style.Border = borders
	}
}

// extractFills provides a function to extract fill styles settings by
// given fill styles definition.
func (f *File) extractFills(fl *xlsxFill, s *xlsxStyleSheet, style *Style) {
	if fl != nil {
		var fill Fill
		if fl.GradientFill != nil {
			fill.Type = "gradient"
			for shading, variants := range styleFillVariants() {
				if fl.GradientFill.Bottom == variants.Bottom &&
					fl.GradientFill.Degree == variants.Degree &&
					fl.GradientFill.Left == variants.Left &&
					fl.GradientFill.Right == variants.Right &&
					fl.GradientFill.Top == variants.Top &&
					fl.GradientFill.Type == variants.Type {
					fill.Shading = shading
					break
				}
			}
			for _, stop := range fl.GradientFill.Stop {
				fill.Color = append(fill.Color, f.getThemeColor(&stop.Color))
			}
		}
		if fl.PatternFill != nil {
			fill.Type = "pattern"
			fill.Pattern = inStrSlice(styleFillPatterns, fl.PatternFill.PatternType, false)
			if fl.PatternFill.BgColor != nil {
				fill.Color = []string{f.getThemeColor(fl.PatternFill.BgColor)}
			}
			if fl.PatternFill.FgColor != nil {
				fill.Color = []string{f.getThemeColor(fl.PatternFill.FgColor)}
			}
		}
		style.Fill = fill
	}
}

// extractFont provides a function to extract font styles settings by given
// font styles definition.
func (f *File) extractFont(fnt *xlsxFont, s *xlsxStyleSheet, style *Style) {
	if fnt != nil {
		var font Font
		if fnt.B != nil {
			font.Bold = fnt.B.Value()
		}
		if fnt.I != nil {
			font.Italic = fnt.I.Value()
		}
		if fnt.U != nil {
			if font.Underline = fnt.U.Value(); font.Underline == "" {
				font.Underline = "single"
			}
		}
		if fnt.Name != nil {
			font.Family = fnt.Name.Value()
		}
		if fnt.Sz != nil {
			font.Size = fnt.Sz.Value()
		}
		if fnt.Strike != nil {
			font.Strike = fnt.Strike.Value()
		}
		if fnt.Color != nil {
			font.Color = strings.TrimPrefix(fnt.Color.RGB, "FF")
			font.ColorIndexed = fnt.Color.Indexed
			font.ColorTheme = fnt.Color.Theme
			font.ColorTint = fnt.Color.Tint
		}
		style.Font = &font
	}
}

// extractNumFmt provides a function to extract number format by given styles
// definition.
func (f *File) extractNumFmt(n *int, s *xlsxStyleSheet, style *Style) {
	if n != nil {
		numFmtID := *n
		if builtInFmtCode, ok := builtInNumFmt[numFmtID]; ok || isLangNumFmt(numFmtID) {
			style.NumFmt = numFmtID
			if decimalPlaces := f.extractNumFmtDecimal(builtInFmtCode); decimalPlaces != -1 {
				style.DecimalPlaces = &decimalPlaces
			}
			return
		}
		if s.NumFmts != nil {
			for _, numFmt := range s.NumFmts.NumFmt {
				if numFmt.NumFmtID != numFmtID {
					continue
				}
				if decimalPlaces := f.extractNumFmtDecimal(numFmt.FormatCode); decimalPlaces != -1 {
					style.DecimalPlaces = &decimalPlaces
				}
				style.CustomNumFmt = &numFmt.FormatCode
				if strings.Contains(numFmt.FormatCode, ";[Red]") {
					style.NegRed = true
				}
				for numFmtID, fmtCode := range currencyNumFmt {
					if style.NegRed {
						fmtCode += ";[Red]" + fmtCode
					}
					if numFmt.FormatCode == fmtCode {
						style.NumFmt = numFmtID
					}
				}
			}
		}
	}
}

// extractAlignment provides a function to extract alignment format by
// given style definition.
func (f *File) extractAlignment(a *xlsxAlignment, s *xlsxStyleSheet, style *Style) {
	if a != nil {
		style.Alignment = &Alignment{
			Horizontal:      a.Horizontal,
			Indent:          a.Indent,
			JustifyLastLine: a.JustifyLastLine,
			ReadingOrder:    a.ReadingOrder,
			RelativeIndent:  a.RelativeIndent,
			ShrinkToFit:     a.ShrinkToFit,
			TextRotation:    a.TextRotation,
			Vertical:        a.Vertical,
			WrapText:        a.WrapText,
		}
	}
}

// extractProtection provides a function to extract protection settings by
// given format definition.
func (f *File) extractProtection(p *xlsxProtection, s *xlsxStyleSheet, style *Style) {
	if p != nil {
		style.Protection = &Protection{}
		if p.Hidden != nil {
			style.Protection.Hidden = *p.Hidden
		}
		if p.Locked != nil {
			style.Protection.Locked = *p.Locked
		}
	}
}

// GetStyle provides a function to get style definition by given style index.
func (f *File) GetStyle(idx int) (*Style, error) {
	var style *Style
	f.mu.Lock()
	s, err := f.stylesReader()
	if err != nil {
		return style, err
	}
	f.mu.Unlock()
	if idx < 0 || s.CellXfs == nil || len(s.CellXfs.Xf) <= idx {
		return style, newInvalidStyleID(idx)
	}
	style = &Style{}
	xf := s.CellXfs.Xf[idx]
	if extractStyleCondFuncs["fill"](xf, s) {
		f.extractFills(s.Fills.Fill[*xf.FillID], s, style)
	}
	if extractStyleCondFuncs["border"](xf, s) {
		f.extractBorders(s.Borders.Border[*xf.BorderID], s, style)
	}
	if extractStyleCondFuncs["font"](xf, s) {
		f.extractFont(s.Fonts.Font[*xf.FontID], s, style)
	}
	if extractStyleCondFuncs["alignment"](xf, s) {
		f.extractAlignment(xf.Alignment, s, style)
	}
	if extractStyleCondFuncs["protection"](xf, s) {
		f.extractProtection(xf.Protection, s, style)
	}
	f.extractNumFmt(xf.NumFmtID, s, style)
	return style, nil
}

// getStyleID provides a function to get styleID by given style. If given
// style does not exist, will return -1.
func (f *File) getStyleID(ss *xlsxStyleSheet, style *Style) (int, error) {
	var (
		err     error
		fontID  int
		styleID = -1
	)
	if ss.CellXfs == nil {
		return styleID, err
	}
	numFmtID, borderID, fillID := getNumFmtID(ss, style), getBorderID(ss, style), getFillID(ss, style)
	if fontID, err = f.getFontID(ss, style); err != nil {
		return styleID, err
	}
	if style.CustomNumFmt != nil {
		numFmtID = getCustomNumFmtID(ss, style)
	}
	for xfID, xf := range ss.CellXfs.Xf {
		if getXfIDFuncs["numFmt"](numFmtID, xf, style) &&
			getXfIDFuncs["font"](fontID, xf, style) &&
			getXfIDFuncs["fill"](fillID, xf, style) &&
			getXfIDFuncs["border"](borderID, xf, style) &&
			getXfIDFuncs["alignment"](0, xf, style) &&
			getXfIDFuncs["protection"](0, xf, style) {
			styleID = xfID
			return styleID, err
		}
	}
	return styleID, err
}

// NewConditionalStyle provides a function to create style for conditional
// format by given style format. The parameters are the same with the NewStyle
// function.
func (f *File) NewConditionalStyle(style *Style) (int, error) {
	f.mu.Lock()
	s, err := f.stylesReader()
	if err != nil {
		f.mu.Unlock()
		return 0, err
	}
	f.mu.Unlock()
	fs, err := parseFormatStyleSet(style)
	if err != nil {
		return 0, err
	}
	if fs.DecimalPlaces != nil && (*fs.DecimalPlaces < 0 || *fs.DecimalPlaces > 30) {
		fs.DecimalPlaces = intPtr(2)
	}
	dxf := xlsxDxf{
		Fill: newFills(fs, false),
	}
	if fs.Alignment != nil {
		dxf.Alignment = newAlignment(fs)
	}
	if len(fs.Border) > 0 {
		dxf.Border = newBorders(fs)
	}
	if fs.Font != nil {
		dxf.Font, _ = f.newFont(fs)
	}
	if fs.Protection != nil {
		dxf.Protection = newProtection(fs)
	}
	dxf.NumFmt = newDxfNumFmt(s, style, &dxf)
	if s.Dxfs == nil {
		s.Dxfs = &xlsxDxfs{}
	}
	s.Dxfs.Count++
	s.Dxfs.Dxfs = append(s.Dxfs.Dxfs, &dxf)
	return s.Dxfs.Count - 1, nil
}

// GetConditionalStyle returns conditional format style definition by specified
// style index.
func (f *File) GetConditionalStyle(idx int) (*Style, error) {
	var style *Style
	f.mu.Lock()
	s, err := f.stylesReader()
	if err != nil {
		return style, err
	}
	f.mu.Unlock()
	if idx < 0 || s.Dxfs == nil || len(s.Dxfs.Dxfs) <= idx {
		return style, newInvalidStyleID(idx)
	}
	style = &Style{}
	xf := s.Dxfs.Dxfs[idx]
	// The default pattern fill type of conditional format style is solid
	if xf.Fill != nil && xf.Fill.PatternFill != nil && xf.Fill.PatternFill.PatternType == "" {
		xf.Fill.PatternFill.PatternType = "solid"
	}
	f.extractFills(xf.Fill, s, style)
	f.extractBorders(xf.Border, s, style)
	f.extractFont(xf.Font, s, style)
	f.extractAlignment(xf.Alignment, s, style)
	f.extractProtection(xf.Protection, s, style)
	if xf.NumFmt != nil {
		f.extractNumFmt(&xf.NumFmt.NumFmtID, s, style)
	}
	return style, nil
}

// newDxfNumFmt provides a function to create number format for conditional
// format styles.
func newDxfNumFmt(styleSheet *xlsxStyleSheet, style *Style, dxf *xlsxDxf) *xlsxNumFmt {
	dp, numFmtID := "0", 164 // Default custom number format code from 164.
	if style.DecimalPlaces != nil && *style.DecimalPlaces > 0 {
		dp += "."
		for i := 0; i < *style.DecimalPlaces; i++ {
			dp += "0"
		}
	}
	if style.CustomNumFmt != nil {
		if styleSheet.Dxfs != nil {
			for _, d := range styleSheet.Dxfs.Dxfs {
				if d != nil && d.NumFmt != nil && d.NumFmt.NumFmtID > numFmtID {
					numFmtID = d.NumFmt.NumFmtID
				}
			}
		}
		return &xlsxNumFmt{NumFmtID: numFmtID + 1, FormatCode: *style.CustomNumFmt}
	}
	numFmtCode, ok := builtInNumFmt[style.NumFmt]
	if style.NumFmt > 0 && ok {
		return &xlsxNumFmt{NumFmtID: style.NumFmt, FormatCode: numFmtCode}
	}
	fc, currency := currencyNumFmt[style.NumFmt]
	if !currency {
		return nil
	}
	if style.DecimalPlaces != nil {
		fc = strings.ReplaceAll(fc, "0.00", dp)
	}
	if style.NegRed {
		fc = fc + ";[Red]" + fc
	}
	return &xlsxNumFmt{NumFmtID: numFmtID, FormatCode: fc}
}

// GetDefaultFont provides the default font name currently set in the
// workbook. The spreadsheet generated by excelize default font is Calibri.
func (f *File) GetDefaultFont() (string, error) {
	font, err := f.readDefaultFont()
	if err != nil {
		return "", err
	}
	return *font.Name.Val, err
}

// SetDefaultFont changes the default font in the workbook.
func (f *File) SetDefaultFont(fontName string) error {
	font, err := f.readDefaultFont()
	if err != nil {
		return err
	}
	font.Name.Val = stringPtr(fontName)
	f.mu.Lock()
	s, _ := f.stylesReader()
	f.mu.Unlock()
	s.Fonts.Font[0] = font
	custom := true
	s.CellStyles.CellStyle[0].CustomBuiltIn = &custom
	return err
}

// readDefaultFont provides an un-marshalled font value.
func (f *File) readDefaultFont() (*xlsxFont, error) {
	f.mu.Lock()
	defer f.mu.Unlock()
	s, err := f.stylesReader()
	if err != nil {
		return nil, err
	}
	return s.Fonts.Font[0], err
}

// getFontID provides a function to get font ID.
// If given font does not exist, will return -1.
func (f *File) getFontID(styleSheet *xlsxStyleSheet, style *Style) (int, error) {
	var err error
	fontID := -1
	if styleSheet.Fonts == nil || style.Font == nil {
		return fontID, err
	}
	for idx, fnt := range styleSheet.Fonts.Font {
		font, err := f.newFont(style)
		if err != nil {
			return fontID, err
		}
		if reflect.DeepEqual(*fnt, *font) {
			fontID = idx
			return fontID, err
		}
	}
	return fontID, err
}

// newFontColor set font color by given styles.
func newFontColor(font *Font) *xlsxColor {
	var fontColor *xlsxColor
	prepareFontColor := func() {
		if fontColor != nil {
			return
		}
		fontColor = &xlsxColor{}
	}
	if font.Color != "" {
		prepareFontColor()
		fontColor.RGB = getPaletteColor(font.Color)
	}
	if font.ColorIndexed >= 0 && font.ColorIndexed <= len(IndexedColorMapping)+1 {
		prepareFontColor()
		fontColor.Indexed = font.ColorIndexed
	}
	if font.ColorTheme != nil {
		prepareFontColor()
		fontColor.Theme = font.ColorTheme
	}
	if font.ColorTint != 0 {
		prepareFontColor()
		fontColor.Tint = font.ColorTint
	}
	return fontColor
}

// newFont provides a function to add font style by given cell format
// settings.
func (f *File) newFont(style *Style) (*xlsxFont, error) {
	var err error
	if style.Font.Size < MinFontSize {
		style.Font.Size = 11
	}
	fnt := xlsxFont{
		Sz:     &attrValFloat{Val: float64Ptr(style.Font.Size)},
		Name:   &attrValString{Val: stringPtr(style.Font.Family)},
		Family: &attrValInt{Val: intPtr(2)},
	}
	fnt.Color = newFontColor(style.Font)
	if style.Font.Bold {
		fnt.B = &attrValBool{Val: &style.Font.Bold}
	}
	if style.Font.Italic {
		fnt.I = &attrValBool{Val: &style.Font.Italic}
	}
	if *fnt.Name.Val == "" {
		if *fnt.Name.Val, err = f.GetDefaultFont(); err != nil {
			return &fnt, err
		}
	}
	if style.Font.Strike {
		fnt.Strike = &attrValBool{Val: &style.Font.Strike}
	}
	if idx := inStrSlice(supportedUnderlineTypes, style.Font.Underline, true); idx != -1 {
		fnt.U = &attrValString{Val: stringPtr(supportedUnderlineTypes[idx])}
	}
	return &fnt, err
}

// getNumFmtID provides a function to get number format code ID.
// If given number format code does not exist, will return -1.
func getNumFmtID(styleSheet *xlsxStyleSheet, style *Style) int {
	numFmtID := -1
	if _, ok := builtInNumFmt[style.NumFmt]; ok {
		return style.NumFmt
	}
	if (27 <= style.NumFmt && style.NumFmt <= 36) || (50 <= style.NumFmt && style.NumFmt <= 81) {
		return style.NumFmt
	}
	if fmtCode, ok := currencyNumFmt[style.NumFmt]; ok {
		numFmtID = style.NumFmt
		if styleSheet.NumFmts != nil {
			for _, numFmt := range styleSheet.NumFmts.NumFmt {
				if numFmt.FormatCode == fmtCode {
					return numFmt.NumFmtID
				}
			}
		}
	}
	return numFmtID
}

// newNumFmt provides a function to check if number format code in the range
// of built-in values.
func newNumFmt(styleSheet *xlsxStyleSheet, style *Style) int {
	dp, numFmtID := "0", 164 // Default custom number format code from 164.
	if style.DecimalPlaces != nil && *style.DecimalPlaces > 0 {
		dp += "."
		for i := 0; i < *style.DecimalPlaces; i++ {
			dp += "0"
		}
	}
	if style.CustomNumFmt != nil {
		if customNumFmtID := getCustomNumFmtID(styleSheet, style); customNumFmtID != -1 {
			return customNumFmtID
		}
		return setCustomNumFmt(styleSheet, style)
	}
	if _, ok := builtInNumFmt[style.NumFmt]; !ok {
		fc, currency := currencyNumFmt[style.NumFmt]
		if !currency {
			return setLangNumFmt(style)
		}
		if style.DecimalPlaces != nil {
			fc = strings.ReplaceAll(fc, "0.00", dp)
		}
		if style.NegRed {
			fc = fc + ";[Red]" + fc
		}
		if styleSheet.NumFmts == nil {
			styleSheet.NumFmts = &xlsxNumFmts{NumFmt: []*xlsxNumFmt{}}
		} else {
			numFmtID = styleSheet.NumFmts.NumFmt[len(styleSheet.NumFmts.NumFmt)-1].NumFmtID + 1
		}
		styleSheet.NumFmts.NumFmt = append(styleSheet.NumFmts.NumFmt, &xlsxNumFmt{
			FormatCode: fc, NumFmtID: numFmtID,
		})
		styleSheet.NumFmts.Count++
		return numFmtID
	}
	return style.NumFmt
}

// setCustomNumFmt provides a function to set custom number format code.
func setCustomNumFmt(styleSheet *xlsxStyleSheet, style *Style) int {
	nf := xlsxNumFmt{NumFmtID: 163, FormatCode: *style.CustomNumFmt}
	if styleSheet.NumFmts == nil {
		styleSheet.NumFmts = &xlsxNumFmts{}
	}
	for _, numFmt := range styleSheet.NumFmts.NumFmt {
		if numFmt != nil && nf.NumFmtID < numFmt.NumFmtID {
			nf.NumFmtID = numFmt.NumFmtID
		}
	}
	nf.NumFmtID++
	styleSheet.NumFmts.NumFmt = append(styleSheet.NumFmts.NumFmt, &nf)
	styleSheet.NumFmts.Count = len(styleSheet.NumFmts.NumFmt)
	return nf.NumFmtID
}

// getCustomNumFmtID provides a function to get custom number format code ID.
// If given custom number format code does not exist, will return -1.
func getCustomNumFmtID(styleSheet *xlsxStyleSheet, style *Style) (customNumFmtID int) {
	customNumFmtID = -1
	if styleSheet.NumFmts == nil {
		return
	}
	for _, numFmt := range styleSheet.NumFmts.NumFmt {
		if style.CustomNumFmt != nil && numFmt.FormatCode == *style.CustomNumFmt {
			customNumFmtID = numFmt.NumFmtID
			return
		}
	}
	return
}

// isLangNumFmt provides a function to returns if a given number format ID is a
// built-in language glyphs number format code.
func isLangNumFmt(ID int) bool {
	return (27 <= ID && ID <= 36) || (50 <= ID && ID <= 62) || (67 <= ID && ID <= 81)
}

// setLangNumFmt provides a function to set number format code with language.
func setLangNumFmt(style *Style) int {
	if isLangNumFmt(style.NumFmt) {
		return style.NumFmt
	}
	return 0
}

// getFillID provides a function to get fill ID. If given fill is not
// exist, will return -1.
func getFillID(styleSheet *xlsxStyleSheet, style *Style) (fillID int) {
	fillID = -1
	if styleSheet.Fills == nil || style.Fill.Type == "" {
		return
	}
	fills := newFills(style, true)
	if fills == nil {
		return
	}
	for idx, fill := range styleSheet.Fills.Fill {
		if reflect.DeepEqual(fill, fills) {
			fillID = idx
			return
		}
	}
	return
}

// newFills provides a function to add fill elements in the styles.xml by
// given cell format settings.
func newFills(style *Style, fg bool) *xlsxFill {
	var fill xlsxFill
	switch style.Fill.Type {
	case "gradient":
		if len(style.Fill.Color) != 2 || style.Fill.Shading < 0 || style.Fill.Shading > 16 {
			break
		}
		gradient := styleFillVariants()[style.Fill.Shading]
		gradient.Stop[0].Color.RGB = getPaletteColor(style.Fill.Color[0])
		gradient.Stop[1].Color.RGB = getPaletteColor(style.Fill.Color[1])
		if len(gradient.Stop) == 3 {
			gradient.Stop[2].Color.RGB = getPaletteColor(style.Fill.Color[0])
		}
		fill.GradientFill = &gradient
	case "pattern":
		if style.Fill.Pattern > 18 || style.Fill.Pattern < 0 {
			break
		}
		var pattern xlsxPatternFill
		pattern.PatternType = styleFillPatterns[style.Fill.Pattern]
		if len(style.Fill.Color) < 1 {
			fill.PatternFill = &pattern
			break
		}
		if fg {
			if pattern.FgColor == nil {
				pattern.FgColor = new(xlsxColor)
			}
			pattern.FgColor.RGB = getPaletteColor(style.Fill.Color[0])
		} else {
			if pattern.BgColor == nil {
				pattern.BgColor = new(xlsxColor)
			}
			pattern.BgColor.RGB = getPaletteColor(style.Fill.Color[0])
		}
		fill.PatternFill = &pattern
	default:
		return nil
	}
	return &fill
}

// newAlignment provides a function to formatting information pertaining to
// text alignment in cells. There are a variety of choices for how text is
// aligned both horizontally and vertically, as well as indentation settings,
// and so on.
func newAlignment(style *Style) *xlsxAlignment {
	var alignment xlsxAlignment
	if style.Alignment != nil {
		alignment.Horizontal = style.Alignment.Horizontal
		alignment.Indent = style.Alignment.Indent
		alignment.JustifyLastLine = style.Alignment.JustifyLastLine
		alignment.ReadingOrder = style.Alignment.ReadingOrder
		alignment.RelativeIndent = style.Alignment.RelativeIndent
		alignment.ShrinkToFit = style.Alignment.ShrinkToFit
		alignment.TextRotation = style.Alignment.TextRotation
		alignment.Vertical = style.Alignment.Vertical
		alignment.WrapText = style.Alignment.WrapText
	}
	return &alignment
}

// newProtection provides a function to set protection properties associated
// with the cell.
func newProtection(style *Style) *xlsxProtection {
	var protection xlsxProtection
	if style.Protection != nil {
		protection.Hidden = &style.Protection.Hidden
		protection.Locked = &style.Protection.Locked
	}
	return &protection
}

// getBorderID provides a function to get border ID. If given border is not
// exist, will return -1.
func getBorderID(styleSheet *xlsxStyleSheet, style *Style) (borderID int) {
	borderID = -1
	if styleSheet.Borders == nil || len(style.Border) == 0 {
		return
	}
	for idx, border := range styleSheet.Borders.Border {
		if reflect.DeepEqual(*border, *newBorders(style)) {
			borderID = idx
			return
		}
	}
	return
}

// newBorders provides a function to add border elements in the styles.xml by
// given borders format settings.
func newBorders(style *Style) *xlsxBorder {
	var border xlsxBorder
	for _, v := range style.Border {
		if 0 <= v.Style && v.Style < 14 {
			line := &xlsxLine{Style: styleBorders[v.Style], Color: &xlsxColor{RGB: getPaletteColor(v.Color)}}
			switch v.Type {
			case "left":
				border.Left = line
			case "right":
				border.Right = line
			case "top":
				border.Top = line
			case "bottom":
				border.Bottom = line
			case "diagonalUp":
				border.Diagonal, border.DiagonalUp = line, true
			case "diagonalDown":
				border.Diagonal, border.DiagonalDown = line, true
			}
		}
	}
	return &border
}

// setCellXfs provides a function to set describes all the formatting for a
// cell.
func setCellXfs(style *xlsxStyleSheet, fontID, numFmtID, fillID, borderID int, applyAlignment, applyProtection bool, alignment *xlsxAlignment, protection *xlsxProtection) (int, error) {
	var xf xlsxXf
	xf.FontID = intPtr(fontID)
	if fontID != 0 {
		xf.ApplyFont = boolPtr(true)
	}
	xf.NumFmtID = intPtr(numFmtID)
	if numFmtID != 0 {
		xf.ApplyNumberFormat = boolPtr(true)
	}
	xf.FillID = intPtr(fillID)
	if fillID != 0 {
		xf.ApplyFill = boolPtr(true)
	}
	xf.BorderID = intPtr(borderID)
	if borderID != 0 {
		xf.ApplyBorder = boolPtr(true)
	}
	if len(style.CellXfs.Xf) == MaxCellStyles {
		return 0, ErrCellStyles
	}
	style.CellXfs.Count = len(style.CellXfs.Xf) + 1
	xf.Alignment = alignment
	if alignment != nil {
		xf.ApplyAlignment = boolPtr(applyAlignment)
	}
	if applyProtection {
		xf.ApplyProtection = boolPtr(applyProtection)
		xf.Protection = protection
	}
	xfID := 0
	xf.XfID = &xfID
	style.CellXfs.Xf = append(style.CellXfs.Xf, xf)
	return style.CellXfs.Count - 1, nil
}

// GetCellStyle provides a function to get cell style index by given worksheet
// name and cell reference. This function is concurrency safe.
func (f *File) GetCellStyle(sheet, cell string) (int, error) {
	f.mu.Lock()
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		f.mu.Unlock()
		return 0, err
	}
	f.mu.Unlock()
	ws.mu.Lock()
	defer ws.mu.Unlock()
	col, row, err := CellNameToCoordinates(cell)
	if err != nil {
		return 0, err
	}
	ws.prepareSheetXML(col, row)
	return ws.prepareCellStyle(col, row, ws.SheetData.Row[row-1].C[col-1].S), err
}

// SetCellStyle provides a function to add style attribute for cells by given
// worksheet name, range reference and style ID. This function is concurrency
// safe. Note that diagonalDown and diagonalUp type border should be use same
// color in the same range. SetCellStyle will overwrite the existing
// styles for the cell, it won't append or merge style with existing styles.
//
// For example create a borders of cell H9 on Sheet1:
//
//	style, err := f.NewStyle(&excelize.Style{
//	    Border: []excelize.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)
//	}
//	err = f.SetCellStyle("Sheet1", "H9", "H9", style)
//
// Set gradient fill with vertical variants shading styles for cell H9 on
// Sheet1:
//
//	style, err := f.NewStyle(&excelize.Style{
//	    Fill: excelize.Fill{Type: "gradient", Color: []string{"FFFFFF", "E0EBF5"}, Shading: 1},
//	})
//	if err != nil {
//	    fmt.Println(err)
//	}
//	err = f.SetCellStyle("Sheet1", "H9", "H9", style)
//
// Set solid style pattern fill for cell H9 on Sheet1:
//
//	style, err := f.NewStyle(&excelize.Style{
//	    Fill: excelize.Fill{Type: "pattern", Color: []string{"E0EBF5"}, Pattern: 1},
//	})
//	if err != nil {
//	    fmt.Println(err)
//	}
//	err = f.SetCellStyle("Sheet1", "H9", "H9", style)
//
// Set alignment style for cell H9 on Sheet1:
//
//	style, err := f.NewStyle(&excelize.Style{
//	    Alignment: &excelize.Alignment{
//	        Horizontal:      "center",
//	        Indent:          1,
//	        JustifyLastLine: true,
//	        ReadingOrder:    0,
//	        RelativeIndent:  1,
//	        ShrinkToFit:     true,
//	        TextRotation:    45,
//	        Vertical:        "",
//	        WrapText:        true,
//	    },
//	})
//	if err != nil {
//	    fmt.Println(err)
//	}
//	err = f.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:
//
//	f.SetCellValue("Sheet1", "H9", 42920.5)
//	style, err := f.NewStyle(&excelize.Style{NumFmt: 22})
//	if err != nil {
//	    fmt.Println(err)
//	}
//	err = f.SetCellStyle("Sheet1", "H9", "H9", style)
//
// Set font style for cell H9 on Sheet1:
//
//	style, err := f.NewStyle(&excelize.Style{
//	    Font: &excelize.Font{
//	        Bold:   true,
//	        Italic: true,
//	        Family: "Times New Roman",
//	        Size:   36,
//	        Color:  "777777",
//	    },
//	})
//	if err != nil {
//	    fmt.Println(err)
//	}
//	err = f.SetCellStyle("Sheet1", "H9", "H9", style)
//
// Hide and lock for cell H9 on Sheet1:
//
//	style, err := f.NewStyle(&excelize.Style{
//	    Protection: &excelize.Protection{
//	        Hidden: true,
//	        Locked: true,
//	    },
//	})
//	if err != nil {
//	    fmt.Println(err)
//	}
//	err = f.SetCellStyle("Sheet1", "H9", "H9", style)
func (f *File) SetCellStyle(sheet, topLeftCell, bottomRightCell string, styleID int) error {
	hCol, hRow, err := CellNameToCoordinates(topLeftCell)
	if err != nil {
		return err
	}

	vCol, vRow, err := CellNameToCoordinates(bottomRightCell)
	if err != nil {
		return err
	}

	// Normalize the range, such correct C1:B3 to B1:C3.
	if vCol < hCol {
		vCol, hCol = hCol, vCol
	}

	if vRow < hRow {
		vRow, hRow = hRow, vRow
	}

	hColIdx := hCol - 1
	hRowIdx := hRow - 1

	vColIdx := vCol - 1
	vRowIdx := vRow - 1
	f.mu.Lock()
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		f.mu.Unlock()
		return err
	}
	s, err := f.stylesReader()
	if err != nil {
		f.mu.Unlock()
		return err
	}
	f.mu.Unlock()

	ws.mu.Lock()
	defer ws.mu.Unlock()

	ws.prepareSheetXML(vCol, vRow)
	ws.makeContiguousColumns(hRow, vRow, vCol)

	if styleID < 0 || s.CellXfs == nil || len(s.CellXfs.Xf) <= styleID {
		return newInvalidStyleID(styleID)
	}

	for r := hRowIdx; r <= vRowIdx; r++ {
		for k := hColIdx; k <= vColIdx; k++ {
			ws.SheetData.Row[r].C[k].S = styleID
		}
	}
	return err
}

// SetConditionalFormat provides a function to create conditional formatting
// rule for cell value. Conditional formatting is a feature of Excel which
// allows you to apply a format to a cell or a range of cells based on certain
// criteria.
//
// The type option is a required parameter and it has no default value.
// Allowable type values and their associated parameters are:
//
//	 Type          | Parameters
//	---------------+------------------------------------
//	 cell          | Criteria
//	               | Value
//	               | MinValue
//	               | MaxValue
//	 time_period   | Criteria
//	 text          | Criteria
//	               | Value
//	 average       | Criteria
//	 duplicate     | (none)
//	 unique        | (none)
//	 top           | Criteria
//	               | Value
//	 bottom        | Criteria
//	               | Value
//	 blanks        | (none)
//	 no_blanks     | (none)
//	 errors        | (none)
//	 no_errors     | (none)
//	 2_color_scale | MinType
//	               | MaxType
//	               | MinValue
//	               | MaxValue
//	               | MinColor
//	               | MaxColor
//	 3_color_scale | MinType
//	               | MidType
//	               | MaxType
//	               | MinValue
//	               | MidValue
//	               | MaxValue
//	               | MinColor
//	               | MidColor
//	               | MaxColor
//	 data_bar      | MinType
//	               | MaxType
//	               | MinValue
//	               | MaxValue
//	               | BarBorderColor
//	               | BarColor
//	               | BarDirection
//	               | BarOnly
//	               | BarSolid
//	 icon_set      | IconStyle
//	               | ReverseIcons
//	               | IconsOnly
//	 formula       | Criteria
//
// The 'Criteria' parameter is used to set the criteria by which the cell data
// will be evaluated. It has no default value. The most common criteria as
// applied to {Type: "cell"} are:
//
//	between                  |
//	not between              |
//	equal to                 | ==
//	not equal to             | !=
//	greater than             | >
//	less than                | <
//	greater than or equal to | >=
//	less than or equal to    | <=
//
// You can either use Excel's textual description strings, in the first column
// above, or the more common symbolic alternatives.
//
// Additional criteria which are specific to other conditional format types are
// shown in the relevant sections below.
//
// value: The value is generally used along with the criteria parameter to set
// the rule by which the cell data will be evaluated:
//
//	err := f.SetConditionalFormat("Sheet1", "D1:D10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "cell",
//	            Criteria: ">",
//	            Format:   &format,
//	            Value:    "6",
//	        },
//	    },
//	)
//
// The value property can also be an cell reference:
//
//	err := f.SetConditionalFormat("Sheet1", "D1:D10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "cell",
//	            Criteria: ">",
//	            Format:   &format,
//	            Value:    "$C$1",
//	        },
//	    },
//	)
//
// type: format - The format parameter is used to specify the format that will
// be applied to the cell when the conditional formatting criterion is met. The
// format is created using the NewConditionalStyle function in the same way as
// cell formats:
//
//	format, err := f.NewConditionalStyle(
//	    &excelize.Style{
//	        Font: &excelize.Font{Color: "9A0511"},
//	        Fill: excelize.Fill{
//	            Type: "pattern", Color: []string{"FEC7CE"}, Pattern: 1,
//	        },
//	    },
//	)
//	if err != nil {
//	    fmt.Println(err)
//	}
//	err = f.SetConditionalFormat("Sheet1", "D1:D10",
//	    []excelize.ConditionalFormatOptions{
//	        {Type: "cell", Criteria: ">", Format: &format, Value: "6"},
//	    },
//	)
//
// Note: In Excel, a conditional format is superimposed over the existing cell
// format and not all cell format properties can be modified. Properties that
// cannot be modified in a conditional format are font name, font size,
// superscript and subscript, diagonal borders, all alignment properties and all
// protection properties.
//
// Excel specifies some default formats to be used with conditional formatting.
// These can be replicated using the following excelize formats:
//
//	// Rose format for bad conditional.
//	format1, err := f.NewConditionalStyle(
//	    &excelize.Style{
//	        Font: &excelize.Font{Color: "9A0511"},
//	        Fill: excelize.Fill{
//	            Type: "pattern", Color: []string{"#FEC7CE"}, Pattern: 1,
//	        },
//	    },
//	)
//
//	// Light yellow format for neutral conditional.
//	format2, err := f.NewConditionalStyle(
//	    &excelize.Style{
//	        Font: &excelize.Font{Color: "9B5713"},
//	        Fill: excelize.Fill{
//	            Type: "pattern", Color: []string{"FEEAA0"}, Pattern: 1,
//	        },
//	    },
//	)
//
//	// Light green format for good conditional.
//	format3, err := f.NewConditionalStyle(
//	    &excelize.Style{
//	        Font: &excelize.Font{Color: "09600B"},
//	        Fill: excelize.Fill{
//	            Type: "pattern", Color: []string{"C7EECF"}, Pattern: 1,
//	        },
//	    },
//	)
//
// type: MinValue - The 'MinValue' parameter is used to set the lower limiting
// value when the criteria is either "between" or "not between".
//
//	// Highlight cells rules: between...
//	err := f.SetConditionalFormat("Sheet1", "A1:A10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "cell",
//	            Criteria: "between",
//	            Format:   &format,
//	            MinValue: 6",
//	            MaxValue: 8",
//	        },
//	    },
//	)
//
// type: MaxValue - The 'MaxValue' parameter is used to set the upper limiting
// value when the criteria is either "between" or "not between". See the
// previous example.
//
// type: average - The average type is used to specify Excel's "Average" style
// conditional format:
//
//	// Top/Bottom rules: Above Average...
//	err := f.SetConditionalFormat("Sheet1", "A1:A10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:         "average",
//	            Criteria:     "=",
//	            Format:       &format1,
//	            AboveAverage: true,
//	        },
//	    },
//	)
//
//	// Top/Bottom rules: Below Average...
//	err := f.SetConditionalFormat("Sheet1", "B1:B10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:         "average",
//	            Criteria:     "=",
//	            Format:       &format2,
//	            AboveAverage: false,
//	        },
//	    },
//	)
//
// type: duplicate - The duplicate type is used to highlight duplicate cells in
// a range:
//
//	// Highlight cells rules: Duplicate Values...
//	err := f.SetConditionalFormat("Sheet1", "A1:A10",
//	    []excelize.ConditionalFormatOptions{
//	        {Type: "duplicate", Criteria: "=", Format: &format},
//	    },
//	)
//
// type: unique - The unique type is used to highlight unique cells in a range:
//
//	// Highlight cells rules: Not Equal To...
//	err := f.SetConditionalFormat("Sheet1", "A1:A10",
//	    []excelize.ConditionalFormatOptions{
//	        {Type: "unique", Criteria: "=", Format: &format},
//	    },
//	)
//
// type: top - The top type is used to specify the top n values by number or
// percentage in a range:
//
//	// Top/Bottom rules: Top 10.
//	err := f.SetConditionalFormat("Sheet1", "H1:H10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "top",
//	            Criteria: "=",
//	            Format:   &format,
//	            Value:    "6",
//	        },
//	    },
//	)
//
// The criteria can be used to indicate that a percentage condition is required:
//
//	err := f.SetConditionalFormat("Sheet1", "A1:A10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "top",
//	            Criteria: "=",
//	            Format:   &format,
//	            Value:    "6",
//	            Percent:  true,
//	        },
//	    },
//	)
//
// type: 2_color_scale - The 2_color_scale type is used to specify Excel's "2
// Color Scale" style conditional format:
//
//	// Color scales: 2 color.
//	err := f.SetConditionalFormat("Sheet1", "A1:A10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "2_color_scale",
//	            Criteria: "=",
//	            MinType:  "min",
//	            MaxType:  "max",
//	            MinColor: "#F8696B",
//	            MaxColor: "#63BE7B",
//	        },
//	    },
//	)
//
// This conditional type can be modified with MinType, MaxType, MinValue,
// MaxValue, MinColor and MaxColor, see below.
//
// type: 3_color_scale - The 3_color_scale type is used to specify Excel's "3
// Color Scale" style conditional format:
//
//	// Color scales: 3 color.
//	err := f.SetConditionalFormat("Sheet1", "A1:A10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "3_color_scale",
//	            Criteria: "=",
//	            MinType:  "min",
//	            MidType:  "percentile",
//	            MaxType:  "max",
//	            MinColor: "#F8696B",
//	            MidColor: "#FFEB84",
//	            MaxColor: "#63BE7B",
//	        },
//	    },
//	)
//
// This conditional type can be modified with MinType, MidType, MaxType,
// MinValue, MidValue, MaxValue, MinColor, MidColor and MaxColor, see
// below.
//
// type: data_bar - The data_bar type is used to specify Excel's "Data Bar"
// style conditional format.
//
// MinType - The MinType and MaxType properties are available when the
// conditional formatting type is 2_color_scale, 3_color_scale or data_bar.
// The MidType is available for 3_color_scale. The properties are used as
// follows:
//
//	// Data Bars: Gradient Fill.
//	err := f.SetConditionalFormat("Sheet1", "K1:K10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "data_bar",
//	            Criteria: "=",
//	            MinType:  "min",
//	            MaxType:  "max",
//	            BarColor: "#638EC6",
//	        },
//	    },
//	)
//
// The available min/mid/max types are:
//
//	min        (for MinType only)
//	num
//	percent
//	percentile
//	formula
//	max        (for MaxType only)
//
// MidType - Used for 3_color_scale. Same as MinType, see above.
//
// MaxType - Same as MinType, see above.
//
// MinValue - The MinValue and MaxValue properties are available when the
// conditional formatting type is 2_color_scale, 3_color_scale or data_bar.
//
// MidValue - The MidValue is available for 3_color_scale. Same as MinValue,
// see above.
//
// MaxValue - Same as MinValue, see above.
//
// MinColor - The MinColor and MaxColor properties are available when the
// conditional formatting type is 2_color_scale, 3_color_scale or data_bar.
//
// MidColor - The MidColor is available for 3_color_scale. The properties
// are used as follows:
//
//	// Color scales: 3 color.
//	err := f.SetConditionalFormat("Sheet1", "B1:B10",
//	    []excelize.ConditionalFormatOptions{
//	        {
//	            Type:     "3_color_scale",
//	            Criteria: "=",
//	            MinType:  "min",
//	            MidType:  "percentile",
//	            MaxType:  "max",
//	            MinColor: "#F8696B",
//	            MidColor: "#FFEB84",
//	            MaxColor: "#63BE7B",
//	        },
//	    },
//	)
//
// MaxColor - Same as MinColor, see above.
//
// BarColor - Used for data_bar. Same as MinColor, see above.
//
// BarBorderColor - Used for sets the color for the border line of a data bar,
// this is only visible in Excel 2010 and later.
//
// BarDirection - sets the direction for data bars. The available options are:
//
//	context - Data bar direction is set by spreadsheet application based on the context of the data displayed.
//	leftToRight - Data bar direction is from right to left.
//	rightToLeft - Data bar direction is from left to right.
//
// BarOnly - Used for set displays a bar data but not the data in the cells.
//
// BarSolid - Used for turns on a solid (non-gradient) fill for data bars, this
// is only visible in Excel 2010 and later.
//
// IconStyle - The available options are:
//
//	3Arrows
//	3ArrowsGray
//	3Flags
//	3Signs
//	3Symbols
//	3Symbols2
//	3TrafficLights1
//	3TrafficLights2
//	4Arrows
//	4ArrowsGray
//	4Rating
//	4RedToBlack
//	4TrafficLights
//	5Arrows
//	5ArrowsGray
//	5Quarters
//	5Rating
//
// ReverseIcons - Used for set reversed icons sets.
//
// IconsOnly - Used for set displayed without the cell value.
//
// StopIfTrue - used to set the "stop if true" feature of a conditional
// formatting rule when more than one rule is applied to a cell or a range of
// cells. When this parameter is set then subsequent rules are not evaluated
// if the current rule is true.
func (f *File) SetConditionalFormat(sheet, rangeRef string, opts []ConditionalFormatOptions) error {
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	SQRef, mastCell, err := prepareConditionalFormatRange(rangeRef)
	if err != nil {
		return err
	}
	// Create a pseudo GUID for each unique rule.
	var rules int
	for _, cf := range ws.ConditionalFormatting {
		rules += len(cf.CfRule)
	}
	var (
		cfRule          []*xlsxCfRule
		noCriteriaTypes = []string{
			"containsBlanks",
			"notContainsBlanks",
			"containsErrors",
			"notContainsErrors",
			"expression",
			"iconSet",
		}
	)
	for i, opt := range opts {
		var vt, ct string
		var ok bool
		// "type" is a required parameter, check for valid validation types.
		vt, ok = validType[opt.Type]
		if ok {
			// Check for valid criteria types.
			ct, ok = criteriaType[opt.Criteria]
			if ok || inStrSlice(noCriteriaTypes, vt, true) != -1 {
				drawFunc, ok := drawContFmtFunc[vt]
				if ok {
					priority := rules + i
					rule, x14rule := drawFunc(priority, ct, mastCell,
						fmt.Sprintf("{00000000-0000-0000-%04X-%012X}", f.getSheetID(sheet), priority), &opt)
					if rule == nil {
						return ErrParameterInvalid
					}
					if x14rule != nil {
						if err = f.appendCfRule(ws, x14rule); err != nil {
							return err
						}
						f.addSheetNameSpace(sheet, NameSpaceSpreadSheetX14)
					}
					cfRule = append(cfRule, rule)
					continue
				}
			}
			return ErrParameterInvalid
		}
		return ErrParameterInvalid
	}

	ws.ConditionalFormatting = append(ws.ConditionalFormatting, &xlsxConditionalFormatting{
		SQRef:  SQRef,
		CfRule: cfRule,
	})
	return err
}

// prepareConditionalFormatRange returns checked cell range and master cell
// reference by giving conditional formatting range reference.
func prepareConditionalFormatRange(rangeRef string) (string, string, error) {
	var SQRef, mastCell string
	if rangeRef == "" {
		return SQRef, mastCell, ErrParameterRequired
	}
	rangeRef = strings.ReplaceAll(rangeRef, ",", " ")
	for i, cellRange := range strings.Split(rangeRef, " ") {
		var cellNames []string
		for j, ref := range strings.Split(cellRange, ":") {
			if j > 1 {
				return SQRef, mastCell, ErrParameterInvalid
			}
			cellRef, col, row, err := parseRef(ref)
			if err != nil {
				return SQRef, mastCell, err
			}
			var c, r int
			if col {
				if cellRef.Row = TotalRows; j == 0 {
					cellRef.Row = 1
				}
			}
			if row {
				if cellRef.Col = MaxColumns; j == 0 {
					cellRef.Col = 1
				}
			}
			c, r = cellRef.Col, cellRef.Row
			cellName, _ := CoordinatesToCellName(c, r)
			cellNames = append(cellNames, cellName)
			if i == 0 && j == 0 {
				mastCell = cellName
			}
		}
		SQRef += strings.Join(cellNames, ":") + " "
	}
	return strings.TrimSuffix(SQRef, " "), mastCell, nil
}

// appendCfRule provides a function to append rules to conditional formatting.
func (f *File) appendCfRule(ws *xlsxWorksheet, rule *xlsxX14CfRule) error {
	var (
		err                                      error
		idx                                      int
		appendMode                               bool
		decodeExtLst                             = new(decodeExtLst)
		condFmts                                 *xlsxX14ConditionalFormattings
		decodeCondFmts                           *decodeX14ConditionalFormattings
		ext                                      *xlsxExt
		condFmtBytes, condFmtsBytes, extLstBytes []byte
	)
	condFmtBytes, _ = xml.Marshal([]*xlsxX14ConditionalFormatting{
		{XMLNSXM: NameSpaceSpreadSheetExcel2006Main.Value, CfRule: []*xlsxX14CfRule{rule}},
	})
	if ws.ExtLst != nil { // append mode ext
		if err = f.xmlNewDecoder(strings.NewReader("<extLst>" + ws.ExtLst.Ext + "</extLst>")).
			Decode(decodeExtLst); err != nil && err != io.EOF {
			return err
		}
		for idx, ext = range decodeExtLst.Ext {
			if ext.URI == ExtURIConditionalFormattings {
				decodeCondFmts = new(decodeX14ConditionalFormattings)
				_ = f.xmlNewDecoder(strings.NewReader(ext.Content)).Decode(decodeCondFmts)
				if condFmts == nil {
					condFmts = &xlsxX14ConditionalFormattings{}
				}
				condFmts.Content = decodeCondFmts.Content + string(condFmtBytes)
				condFmtsBytes, _ = xml.Marshal(condFmts)
				decodeExtLst.Ext[idx].Content = string(condFmtsBytes)
				appendMode = true
			}
		}
	}
	if !appendMode {
		condFmtsBytes, _ = xml.Marshal(&xlsxX14ConditionalFormattings{Content: string(condFmtBytes)})
		decodeExtLst.Ext = append(decodeExtLst.Ext, &xlsxExt{
			URI: ExtURIConditionalFormattings, Content: string(condFmtsBytes),
		})
	}
	sort.Slice(decodeExtLst.Ext, func(i, j int) bool {
		return inStrSlice(worksheetExtURIPriority, decodeExtLst.Ext[i].URI, false) <
			inStrSlice(worksheetExtURIPriority, decodeExtLst.Ext[j].URI, false)
	})
	extLstBytes, err = xml.Marshal(decodeExtLst)
	ws.ExtLst = &xlsxExtLst{Ext: strings.TrimSuffix(strings.TrimPrefix(string(extLstBytes), "<extLst>"), "</extLst>")}
	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 (f *File) extractCondFmtCellIs(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	format := ConditionalFormatOptions{Format: c.DxfID, StopIfTrue: c.StopIfTrue, Type: "cell", Criteria: operatorType[c.Operator]}
	if len(c.Formula) == 2 {
		format.MinValue, format.MaxValue = c.Formula[0], c.Formula[1]
		return format
	}
	format.Value = c.Formula[0]
	return format
}

// extractCondFmtTimePeriod provides a function to extract conditional format
// settings for time period by given conditional formatting rule.
func (f *File) extractCondFmtTimePeriod(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	return ConditionalFormatOptions{Format: c.DxfID, StopIfTrue: c.StopIfTrue, Type: "time_period", Criteria: operatorType[c.Operator]}
}

// extractCondFmtText provides a function to extract conditional format
// settings for text cell values by given conditional formatting rule.
func (f *File) extractCondFmtText(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	return ConditionalFormatOptions{Format: c.DxfID, StopIfTrue: c.StopIfTrue, Type: "text", Criteria: operatorType[c.Operator], Value: c.Text}
}

// extractCondFmtTop10 provides a function to extract conditional format
// settings for top N (default is top 10) by given conditional formatting
// rule.
func (f *File) extractCondFmtTop10(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	format := ConditionalFormatOptions{
		Format:     c.DxfID,
		StopIfTrue: c.StopIfTrue,
		Type:       "top",
		Criteria:   "=",
		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 (f *File) extractCondFmtAboveAverage(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	format := ConditionalFormatOptions{
		Format:     c.DxfID,
		StopIfTrue: c.StopIfTrue,
		Type:       "average",
		Criteria:   "=",
	}
	if c.AboveAverage != nil {
		format.AboveAverage = *c.AboveAverage
	}
	return format
}

// extractCondFmtDuplicateUniqueValues provides a function to extract
// conditional format settings for duplicate and unique values by given
// conditional formatting rule.
func (f *File) extractCondFmtDuplicateUniqueValues(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	return ConditionalFormatOptions{
		Format:     c.DxfID,
		StopIfTrue: c.StopIfTrue,
		Type: map[string]string{
			"duplicateValues": "duplicate",
			"uniqueValues":    "unique",
		}[c.Type],
		Criteria: "=",
	}
}

// extractCondFmtBlanks provides a function to extract conditional format
// settings for blank cells by given conditional formatting rule.
func (f *File) extractCondFmtBlanks(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	return ConditionalFormatOptions{
		Format:     c.DxfID,
		StopIfTrue: c.StopIfTrue,
		Type:       "blanks",
	}
}

// extractCondFmtNoBlanks provides a function to extract conditional format
// settings for no blank cells by given conditional formatting rule.
func (f *File) extractCondFmtNoBlanks(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	return ConditionalFormatOptions{
		Format:     c.DxfID,
		StopIfTrue: c.StopIfTrue,
		Type:       "no_blanks",
	}
}

// extractCondFmtErrors provides a function to extract conditional format
// settings for cells with errors by given conditional formatting rule.
func (f *File) extractCondFmtErrors(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	return ConditionalFormatOptions{
		Format:     c.DxfID,
		StopIfTrue: c.StopIfTrue,
		Type:       "errors",
	}
}

// extractCondFmtNoErrors provides a function to extract conditional format
// settings for cells without errors by given conditional formatting rule.
func (f *File) extractCondFmtNoErrors(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	return ConditionalFormatOptions{
		Format:     c.DxfID,
		StopIfTrue: c.StopIfTrue,
		Type:       "no_errors",
	}
}

// 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 (f *File) extractCondFmtColorScale(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	format := ConditionalFormatOptions{StopIfTrue: c.StopIfTrue}
	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 = "#" + f.getThemeColor(c.ColorScale.Color[0])
		format.MaxType = c.ColorScale.Cfvo[1].Type
		if c.ColorScale.Cfvo[1].Val != "0" {
			format.MaxValue = c.ColorScale.Cfvo[1].Val
		}
		format.MaxColor = "#" + f.getThemeColor(c.ColorScale.Color[1])
	}
	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 = "#" + f.getThemeColor(c.ColorScale.Color[1])
		format.MaxType = c.ColorScale.Cfvo[2].Type
		if c.ColorScale.Cfvo[2].Val != "0" {
			format.MaxValue = c.ColorScale.Cfvo[2].Val
		}
		format.MaxColor = "#" + f.getThemeColor(c.ColorScale.Color[2])
	}
	return format
}

// extractCondFmtDataBarRule provides a function to extract conditional format
// settings for data bar by given conditional formatting rule extension list.
func (f *File) extractCondFmtDataBarRule(ID string, format *ConditionalFormatOptions, condFmts []decodeX14ConditionalFormatting) {
	for _, condFmt := range condFmts {
		for _, rule := range condFmt.CfRule {
			if rule.DataBar != nil && rule.ID == ID {
				format.BarDirection = rule.DataBar.Direction
				if rule.DataBar.Gradient != nil && !*rule.DataBar.Gradient {
					format.BarSolid = true
				}
				if rule.DataBar.BorderColor != nil {
					format.BarBorderColor = "#" + f.getThemeColor(rule.DataBar.BorderColor)
				}
			}
		}
	}
}

// extractCondFmtDataBar provides a function to extract conditional format
// settings for data bar by given conditional formatting rule.
func (f *File) extractCondFmtDataBar(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	format := ConditionalFormatOptions{Type: "data_bar", Criteria: "="}
	if c.DataBar != nil {
		format.StopIfTrue = c.StopIfTrue
		format.MinType = c.DataBar.Cfvo[0].Type
		format.MinValue = c.DataBar.Cfvo[0].Val
		format.MaxType = c.DataBar.Cfvo[1].Type
		format.MaxValue = c.DataBar.Cfvo[1].Val
		format.BarColor = "#" + f.getThemeColor(c.DataBar.Color[0])
		if c.DataBar.ShowValue != nil {
			format.BarOnly = !*c.DataBar.ShowValue
		}
	}
	extractExtLst := func(ID string, extLst *decodeExtLst) {
		for _, ext := range extLst.Ext {
			if ext.URI == ExtURIConditionalFormattings {
				decodeCondFmts := new(decodeX14ConditionalFormattingRules)
				if err := xml.Unmarshal([]byte(ext.Content), &decodeCondFmts); err == nil {
					f.extractCondFmtDataBarRule(ID, &format, decodeCondFmts.CondFmt)
				}
			}
		}
	}
	if c.ExtLst != nil {
		ext := decodeX14ConditionalFormattingExt{}
		if err := xml.Unmarshal([]byte(c.ExtLst.Ext), &ext); err == nil && extLst != nil {
			decodeExtLst := new(decodeExtLst)
			if err = xml.Unmarshal([]byte("<extLst>"+extLst.Ext+"</extLst>"), decodeExtLst); err == nil {
				extractExtLst(ext.ID, decodeExtLst)
			}
		}
	}
	return format
}

// extractCondFmtExp provides a function to extract conditional format settings
// for expression by given conditional formatting rule.
func (f *File) extractCondFmtExp(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	format := ConditionalFormatOptions{Format: c.DxfID, StopIfTrue: c.StopIfTrue, Type: "formula"}
	if len(c.Formula) > 0 {
		format.Criteria = c.Formula[0]
	}
	return format
}

// extractCondFmtIconSet provides a function to extract conditional format
// settings for icon sets by given conditional formatting rule.
func (f *File) extractCondFmtIconSet(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
	format := ConditionalFormatOptions{Type: "icon_set"}
	if c.IconSet != nil {
		if c.IconSet.ShowValue != nil {
			format.IconsOnly = !*c.IconSet.ShowValue
		}
		format.IconStyle = c.IconSet.IconSet
		format.ReverseIcons = c.IconSet.Reverse
	}
	return format
}

// GetConditionalFormats returns conditional format settings by given worksheet
// name.
func (f *File) GetConditionalFormats(sheet string) (map[string][]ConditionalFormatOptions, error) {
	conditionalFormats := make(map[string][]ConditionalFormatOptions)
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return conditionalFormats, err
	}
	for _, cf := range ws.ConditionalFormatting {
		var opts []ConditionalFormatOptions
		for _, cr := range cf.CfRule {
			if extractFunc, ok := extractContFmtFunc[cr.Type]; ok {
				opts = append(opts, extractFunc(f, cr, ws.ExtLst))
			}
		}
		conditionalFormats[cf.SQRef] = opts
	}
	return conditionalFormats, err
}

// UnsetConditionalFormat provides a function to unset the conditional format
// by given worksheet name and range reference.
func (f *File) UnsetConditionalFormat(sheet, rangeRef string) error {
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	for i, cf := range ws.ConditionalFormatting {
		if cf.SQRef == rangeRef {
			ws.ConditionalFormatting = append(ws.ConditionalFormatting[:i], ws.ConditionalFormatting[i+1:]...)
			return nil
		}
	}
	return nil
}

// drawCondFmtCellIs provides a function to create conditional formatting rule
// for cell value (include between, not between, equal, not equal, greater
// than and less than) by given priority, criteria type and format settings.
func drawCondFmtCellIs(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	c := &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		Operator:   ct,
		DxfID:      format.Format,
	}
	// "between" and "not between" criteria require 2 values.
	if ct == "between" || ct == "notBetween" {
		c.Formula = append(c.Formula, []string{format.MinValue, format.MaxValue}...)
	}
	if inStrSlice(cellIsCriteriaType, ct, true) != -1 {
		c.Formula = append(c.Formula, format.Value)
	}
	return c, nil
}

// drawCondFmtTimePeriod provides a function to create conditional formatting
// rule for time period by given priority, criteria type and format settings.
func drawCondFmtTimePeriod(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       "timePeriod",
		Operator:   ct,
		Formula: []string{
			map[string]string{
				"yesterday":      fmt.Sprintf("FLOOR(%s,1)=TODAY()-1", ref),
				"today":          fmt.Sprintf("FLOOR(%s,1)=TODAY()", ref),
				"tomorrow":       fmt.Sprintf("FLOOR(%s,1)=TODAY()+1", ref),
				"last 7 days":    fmt.Sprintf("AND(TODAY()-FLOOR(%[1]s,1)<=6,FLOOR(%[1]s,1)<=TODAY())", ref),
				"last week":      fmt.Sprintf("AND(TODAY()-ROUNDDOWN(%[1]s,0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(%[1]s,0)<(WEEKDAY(TODAY())+7))", ref),
				"this week":      fmt.Sprintf("AND(TODAY()-ROUNDDOWN(%[1]s,0)<=WEEKDAY(TODAY())-1,ROUNDDOWN(%[1]s,0)-TODAY()>=7-WEEKDAY(TODAY()))", ref),
				"continue week":  fmt.Sprintf("AND(ROUNDDOWN(%[1]s,0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(%[1]s,0)-TODAY()<(15-WEEKDAY(TODAY())))", ref),
				"last month":     fmt.Sprintf("AND(MONTH(%[1]s)=MONTH(TODAY())-1,OR(YEAR(%[1]s)=YEAR(TODAY()),AND(MONTH(%[1]s)=1,YEAR(%[1]s)=YEAR(TODAY())-1)))", ref),
				"this month":     fmt.Sprintf("AND(MONTH(%[1]s)=MONTH(TODAY()),YEAR(%[1]s)=YEAR(TODAY()))", ref),
				"continue month": fmt.Sprintf("AND(MONTH(%[1]s)=MONTH(TODAY())+1,OR(YEAR(%[1]s)=YEAR(TODAY()),AND(MONTH(%[1]s)=12,YEAR(%[1]s)=YEAR(TODAY())+1)))", ref),
			}[ct],
		},
		DxfID: format.Format,
	}, nil
}

// drawCondFmtText provides a function to create conditional formatting rule for
// text cell values by given priority, criteria type and format settings.
func drawCondFmtText(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type: map[string]string{
			"containsText": "containsText",
			"notContains":  "notContainsText",
			"beginsWith":   "beginsWith",
			"endsWith":     "endsWith",
		}[ct],
		Text:     format.Value,
		Operator: ct,
		Formula: []string{
			map[string]string{
				"containsText": fmt.Sprintf("NOT(ISERROR(SEARCH(\"%s\",%s)))",
					strings.NewReplacer(`"`, `""`).Replace(format.Value), ref),
				"notContains": fmt.Sprintf("ISERROR(SEARCH(\"%s\",%s))",
					strings.NewReplacer(`"`, `""`).Replace(format.Value), ref),
				"beginsWith": fmt.Sprintf("LEFT(%[2]s,LEN(\"%[1]s\"))=\"%[1]s\"",
					strings.NewReplacer(`"`, `""`).Replace(format.Value), ref),
				"endsWith": fmt.Sprintf("RIGHT(%[2]s,LEN(\"%[1]s\"))=\"%[1]s\"",
					strings.NewReplacer(`"`, `""`).Replace(format.Value), ref),
			}[ct],
		},
		DxfID: format.Format,
	}, nil
}

// drawCondFmtTop10 provides a function to create conditional formatting rule
// for top N (default is top 10) by given priority, criteria type and format
// settings.
func drawCondFmtTop10(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	c := &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Bottom:     format.Type == "bottom",
		Type:       validType[format.Type],
		Rank:       10,
		DxfID:      format.Format,
		Percent:    format.Percent,
	}
	if rank, err := strconv.Atoi(format.Value); err == nil {
		c.Rank = rank
	}
	return c, nil
}

// drawCondFmtAboveAverage provides a function to create conditional
// formatting rule for above average and below average by given priority,
// criteria type and format settings.
func drawCondFmtAboveAverage(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:     p + 1,
		StopIfTrue:   format.StopIfTrue,
		Type:         validType[format.Type],
		AboveAverage: boolPtr(format.AboveAverage),
		DxfID:        format.Format,
	}, nil
}

// drawCondFmtDuplicateUniqueValues provides a function to create conditional
// formatting rule for duplicate and unique values by given priority, criteria
// type and format settings.
func drawCondFmtDuplicateUniqueValues(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		DxfID:      format.Format,
	}, nil
}

// drawCondFmtColorScale provides a function to create conditional formatting
// rule for color scale (include 2 color scale and 3 color scale) by given
// priority, criteria type and format settings.
func drawCondFmtColorScale(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	minValue := format.MinValue
	if minValue == "" {
		minValue = "0"
	}
	maxValue := format.MaxValue
	if maxValue == "" {
		maxValue = "0"
	}
	midValue := format.MidValue
	if midValue == "" {
		midValue = "50"
	}

	c := &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       "colorScale",
		ColorScale: &xlsxColorScale{
			Cfvo: []*xlsxCfvo{
				{Type: format.MinType, Val: minValue},
			},
			Color: []*xlsxColor{
				{RGB: getPaletteColor(format.MinColor)},
			},
		},
	}
	if validType[format.Type] == "3_color_scale" {
		c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MidType, Val: midValue})
		c.ColorScale.Color = append(c.ColorScale.Color, &xlsxColor{RGB: getPaletteColor(format.MidColor)})
	}
	c.ColorScale.Cfvo = append(c.ColorScale.Cfvo, &xlsxCfvo{Type: format.MaxType, Val: maxValue})
	c.ColorScale.Color = append(c.ColorScale.Color, &xlsxColor{RGB: getPaletteColor(format.MaxColor)})
	return c, nil
}

// drawCondFmtDataBar provides a function to create conditional formatting
// rule for data bar by given priority, criteria type and format settings.
func drawCondFmtDataBar(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	var x14CfRule *xlsxX14CfRule
	var extLst *xlsxExtLst
	if format.BarSolid || format.BarDirection == "leftToRight" || format.BarDirection == "rightToLeft" || format.BarBorderColor != "" {
		extLst = &xlsxExtLst{Ext: fmt.Sprintf(`<ext uri="%s" xmlns:x14="%s"><x14:id>%s</x14:id></ext>`, ExtURIConditionalFormattingRuleID, NameSpaceSpreadSheetX14.Value, GUID)}
		x14CfRule = &xlsxX14CfRule{
			Type: validType[format.Type],
			ID:   GUID,
			DataBar: &xlsx14DataBar{
				MaxLength:         100,
				Border:            format.BarBorderColor != "",
				Gradient:          !format.BarSolid,
				Direction:         format.BarDirection,
				Cfvo:              []*xlsxCfvo{{Type: "autoMin"}, {Type: "autoMax"}},
				NegativeFillColor: &xlsxColor{RGB: "FFFF0000"},
				AxisColor:         &xlsxColor{RGB: "FFFF0000"},
			},
		}
		if x14CfRule.DataBar.Border {
			x14CfRule.DataBar.BorderColor = &xlsxColor{RGB: getPaletteColor(format.BarBorderColor)}
		}
	}
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		DataBar: &xlsxDataBar{
			ShowValue: boolPtr(!format.BarOnly),
			Cfvo:      []*xlsxCfvo{{Type: format.MinType, Val: format.MinValue}, {Type: format.MaxType, Val: format.MaxValue}},
			Color:     []*xlsxColor{{RGB: getPaletteColor(format.BarColor)}},
		},
		ExtLst: extLst,
	}, x14CfRule
}

// drawCondFmtExp provides a function to create conditional formatting rule
// for expression by given priority, criteria type and format settings.
func drawCondFmtExp(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		Formula:    []string{format.Criteria},
		DxfID:      format.Format,
	}, nil
}

// drawCondFmtErrors provides a function to create conditional formatting rule
// for cells with errors by given priority, criteria type and format settings.
func drawCondFmtErrors(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		Formula:    []string{fmt.Sprintf("ISERROR(%s)", ref)},
		DxfID:      format.Format,
	}, nil
}

// drawCondFmtNoErrors provides a function to create conditional formatting rule
// for cells without errors by given priority, criteria type and format settings.
func drawCondFmtNoErrors(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		Formula:    []string{fmt.Sprintf("NOT(ISERROR(%s))", ref)},
		DxfID:      format.Format,
	}, nil
}

// drawCondFmtBlanks provides a function to create conditional formatting rule
// for blank cells by given priority, criteria type and format settings.
func drawCondFmtBlanks(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		Formula:    []string{fmt.Sprintf("LEN(TRIM(%s))=0", ref)},
		DxfID:      format.Format,
	}, nil
}

// drawCondFmtNoBlanks provides a function to create conditional formatting rule
// for no blanks cells by given priority, criteria type and format settings.
func drawCondFmtNoBlanks(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	return &xlsxCfRule{
		Priority:   p + 1,
		StopIfTrue: format.StopIfTrue,
		Type:       validType[format.Type],
		Formula:    []string{fmt.Sprintf("LEN(TRIM(%s))>0", ref)},
		DxfID:      format.Format,
	}, nil
}

// drawCondFmtIconSet provides a function to create conditional formatting rule
// for icon set by given priority, criteria type and format settings.
func drawCondFmtIconSet(p int, ct, ref, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
	cfRule, ok := condFmtIconSetPresets[format.IconStyle]
	if !ok {
		return nil, nil
	}
	cfRule.Priority = p + 1
	cfRule.IconSet.IconSet = format.IconStyle
	cfRule.IconSet.Reverse = format.ReverseIcons
	cfRule.IconSet.ShowValue = boolPtr(!format.IconsOnly)
	cfRule.Type = validType[format.Type]
	return cfRule, nil
}

// getPaletteColor provides a function to convert the RBG color by given
// string.
func getPaletteColor(color string) string {
	return "FF" + strings.ReplaceAll(strings.ToUpper(color), "#", "")
}

// themeReader provides a function to get the pointer to the xl/theme/theme1.xml
// structure after deserialization.
func (f *File) themeReader() (*decodeTheme, error) {
	if _, ok := f.Pkg.Load(defaultXMLPathTheme); !ok {
		return nil, nil
	}
	theme := decodeTheme{}
	if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(defaultXMLPathTheme)))).
		Decode(&theme); err != nil && err != io.EOF {
		return &theme, err
	}
	return &theme, nil
}

// ThemeColor applied the color with tint value.
func ThemeColor(baseColor string, tint float64) string {
	if tint == 0 {
		return "FF" + baseColor
	}
	r, _ := strconv.ParseUint(baseColor[:2], 16, 64)
	g, _ := strconv.ParseUint(baseColor[2:4], 16, 64)
	b, _ := strconv.ParseUint(baseColor[4:6], 16, 64)
	var h, s, l float64
	if r <= math.MaxUint8 && g <= math.MaxUint8 && b <= math.MaxUint8 {
		h, s, l = RGBToHSL(uint8(r), uint8(g), uint8(b))
	}
	if tint < 0 {
		l *= 1 + tint
	} else {
		l = l*(1-tint) + (1 - (1 - tint))
	}
	br, bg, bb := HSLToRGB(h, s, l)
	return fmt.Sprintf("FF%02X%02X%02X", br, bg, bb)
}