xuri/excelize
xuri/excelize
1
4
Fork 0
Code Issues Pull Requests Projects Releases 22 Wiki Activity
excelize/styles.go

3507 lines
110 KiB
Go
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

// Copyright 2016 - 2024 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.18 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,
}
)
// 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.SysClr.LastClr,
1: &clrScheme.Dk1.SysClr.LastClr,
2: clrScheme.Lt2.SrgbClr.Val,
3: clrScheme.Dk2.SrgbClr.Val,
4: clrScheme.Accent1.SrgbClr.Val,
5: clrScheme.Accent2.SrgbClr.Val,
6: clrScheme.Accent3.SrgbClr.Val,
7: clrScheme.Accent4.SrgbClr.Val,
8: clrScheme.Accent5.SrgbClr.Val,
9: clrScheme.Accent6.SrgbClr.Val,
}[*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.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) (numFmtID 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) {
numFmtID = style.NumFmt
return
}
if fmtCode, ok := currencyNumFmt[style.NumFmt]; ok {
numFmtID = style.NumFmt
if styleSheet.NumFmts != nil {
for _, numFmt := range styleSheet.NumFmts.NumFmt {
if numFmt.FormatCode == fmtCode {
numFmtID = numFmt.NumFmtID
return
}
}
}
}
return
}
// 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
}
if len(style.Fill.Color) < 1 {
break
}
var pattern xlsxPatternFill
pattern.PatternType = styleFillPatterns[style.Fill.Pattern]
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 {
var color xlsxColor
color.RGB = getPaletteColor(v.Color)
switch v.Type {
case "left":
border.Left.Style = styleBorders[v.Style]
border.Left.Color = &color
case "right":
border.Right.Style = styleBorders[v.Style]
border.Right.Color = &color
case "top":
border.Top.Style = styleBorders[v.Style]
border.Top.Color = &color
case "bottom":
border.Bottom.Style = styleBorders[v.Style]
border.Bottom.Color = &color
case "diagonalUp":
border.Diagonal.Style = styleBorders[v.Style]
border.Diagonal.Color = &color
border.DiagonalUp = true
case "diagonalDown":
border.Diagonal.Style = styleBorders[v.Style]
border.Diagonal.Color = &color
border.DiagonalDown = 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.
func (f *File) GetCellStyle(sheet, cell string) (int, error) {
ws, err := f.workSheetReader(sheet)
if err != nil {
return 0, err
}
col, row, err := CellNameToCoordinates(cell)
if err != nil {
return 0, err
}
ws.prepareSheetXML(col, row)
ws.mu.Lock()
defer ws.mu.Unlock()
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)
}
View Git Blame Copy Permalink