forked from p30928647/excelize
2781 lines
84 KiB
Go
2781 lines
84 KiB
Go
// Copyright 2016 - 2023 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.16 or later.
|
||
|
||
package excelize
|
||
|
||
import (
|
||
"bytes"
|
||
"encoding/xml"
|
||
"fmt"
|
||
"io"
|
||
"math"
|
||
"reflect"
|
||
"sort"
|
||
"strconv"
|
||
"strings"
|
||
)
|
||
|
||
// validType defined the list of valid validation types.
|
||
var validType = map[string]string{
|
||
"cell": "cellIs",
|
||
"date": "date", // Doesn't support currently
|
||
"time": "time", // Doesn't support currently
|
||
"average": "aboveAverage",
|
||
"duplicate": "duplicateValues",
|
||
"unique": "uniqueValues",
|
||
"top": "top10",
|
||
"bottom": "top10",
|
||
"text": "text", // Doesn't support currently
|
||
"time_period": "timePeriod", // Doesn't support currently
|
||
"blanks": "containsBlanks", // Doesn't support currently
|
||
"no_blanks": "notContainsBlanks", // Doesn't support currently
|
||
"errors": "containsErrors", // Doesn't support currently
|
||
"no_errors": "notContainsErrors", // Doesn't support currently
|
||
"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.
|
||
var criteriaType = map[string]string{
|
||
"between": "between",
|
||
"not between": "notBetween",
|
||
"equal to": "equal",
|
||
"=": "equal",
|
||
"==": "equal",
|
||
"not equal to": "notEqual",
|
||
"!=": "notEqual",
|
||
"<>": "notEqual",
|
||
"greater than": "greaterThan",
|
||
">": "greaterThan",
|
||
"less than": "lessThan",
|
||
"<": "lessThan",
|
||
"greater than or equal to": "greaterThanOrEqual",
|
||
">=": "greaterThanOrEqual",
|
||
"less than or equal to": "lessThanOrEqual",
|
||
"<=": "lessThanOrEqual",
|
||
"containing": "containsText",
|
||
"not containing": "notContains",
|
||
"begins with": "beginsWith",
|
||
"ends with": "endsWith",
|
||
"yesterday": "yesterday",
|
||
"today": "today",
|
||
"last 7 days": "last7Days",
|
||
"last week": "lastWeek",
|
||
"this week": "thisWeek",
|
||
"continue week": "continueWeek",
|
||
"last month": "lastMonth",
|
||
"this month": "thisMonth",
|
||
"continue month": "continueMonth",
|
||
}
|
||
|
||
// operatorType defined the list of valid operator types.
|
||
var operatorType = map[string]string{
|
||
"lastMonth": "last month",
|
||
"between": "between",
|
||
"notEqual": "not equal to",
|
||
"greaterThan": "greater than",
|
||
"lessThanOrEqual": "less than or equal to",
|
||
"today": "today",
|
||
"equal": "equal to",
|
||
"notContains": "not containing",
|
||
"thisWeek": "this week",
|
||
"endsWith": "ends with",
|
||
"yesterday": "yesterday",
|
||
"lessThan": "less than",
|
||
"beginsWith": "begins with",
|
||
"last7Days": "last 7 days",
|
||
"thisMonth": "this month",
|
||
"containsText": "containing",
|
||
"lastWeek": "last week",
|
||
"continueWeek": "continue week",
|
||
"continueMonth": "continue month",
|
||
"notBetween": "not between",
|
||
"greaterThanOrEqual": "greater than or equal to",
|
||
}
|
||
|
||
// 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() {
|
||
if f.Theme != nil {
|
||
output, _ := xml.Marshal(f.Theme)
|
||
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
|
||
//
|
||
// 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. 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 Excel Application: martes, 04 de Julio de 2017
|
||
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 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
|
||
},
|
||
}
|
||
|
||
// 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
|
||
}
|
||
dxf := dxf{
|
||
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)
|
||
}
|
||
dxfStr, _ := xml.Marshal(dxf)
|
||
if s.Dxfs == nil {
|
||
s.Dxfs = &xlsxDxfs{}
|
||
}
|
||
s.Dxfs.Count++
|
||
s.Dxfs.Dxfs = append(s.Dxfs.Dxfs, &xlsxDxf{
|
||
Dxf: string(dxfStr[5 : len(dxfStr)-6]),
|
||
})
|
||
return s.Dxfs.Count - 1, nil
|
||
}
|
||
|
||
// 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{FormatCode: *style.CustomNumFmt}
|
||
|
||
if styleSheet.NumFmts != nil {
|
||
nf.NumFmtID = styleSheet.NumFmts.NumFmt[len(styleSheet.NumFmts.NumFmt)-1].NumFmtID + 1
|
||
styleSheet.NumFmts.NumFmt = append(styleSheet.NumFmts.NumFmt, &nf)
|
||
styleSheet.NumFmts.Count++
|
||
} else {
|
||
nf.NumFmtID = 164
|
||
numFmts := xlsxNumFmts{
|
||
NumFmt: []*xlsxNumFmt{&nf},
|
||
Count: 1,
|
||
}
|
||
styleSheet.NumFmts = &numFmts
|
||
}
|
||
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
|
||
}
|
||
|
||
// setLangNumFmt provides a function to set number format code with language.
|
||
func setLangNumFmt(style *Style) int {
|
||
if (27 <= style.NumFmt && style.NumFmt <= 36) || (50 <= style.NumFmt && style.NumFmt <= 81) {
|
||
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 {
|
||
patterns := []string{
|
||
"none",
|
||
"solid",
|
||
"mediumGray",
|
||
"darkGray",
|
||
"lightGray",
|
||
"darkHorizontal",
|
||
"darkVertical",
|
||
"darkDown",
|
||
"darkUp",
|
||
"darkGrid",
|
||
"darkTrellis",
|
||
"lightHorizontal",
|
||
"lightVertical",
|
||
"lightDown",
|
||
"lightUp",
|
||
"lightGrid",
|
||
"lightTrellis",
|
||
"gray125",
|
||
"gray0625",
|
||
}
|
||
variants := []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},
|
||
}
|
||
|
||
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 := variants[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 = patterns[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 {
|
||
styles := []string{
|
||
"none",
|
||
"thin",
|
||
"medium",
|
||
"dashed",
|
||
"dotted",
|
||
"thick",
|
||
"double",
|
||
"hair",
|
||
"mediumDashed",
|
||
"dashDot",
|
||
"mediumDashDot",
|
||
"dashDotDot",
|
||
"mediumDashDotDot",
|
||
"slantDashDot",
|
||
}
|
||
|
||
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 = styles[v.Style]
|
||
border.Left.Color = &color
|
||
case "right":
|
||
border.Right.Style = styles[v.Style]
|
||
border.Right.Color = &color
|
||
case "top":
|
||
border.Top.Style = styles[v.Style]
|
||
border.Top.Color = &color
|
||
case "bottom":
|
||
border.Bottom.Style = styles[v.Style]
|
||
border.Bottom.Color = &color
|
||
case "diagonalUp":
|
||
border.Diagonal.Style = styles[v.Style]
|
||
border.Diagonal.Color = &color
|
||
border.DiagonalUp = true
|
||
case "diagonalDown":
|
||
border.Diagonal.Style = styles[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, hCell, vCell string, styleID int) error {
|
||
hCol, hRow, err := CellNameToCoordinates(hCell)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
|
||
vCol, vRow, err := CellNameToCoordinates(vCell)
|
||
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
|
||
// date | 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 {
|
||
drawContFmtFunc := map[string]func(p int, ct, GUID string, fmtCond *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule){
|
||
"cellIs": drawCondFmtCellIs,
|
||
"top10": drawCondFmtTop10,
|
||
"aboveAverage": drawCondFmtAboveAverage,
|
||
"duplicateValues": drawCondFmtDuplicateUniqueValues,
|
||
"uniqueValues": drawCondFmtDuplicateUniqueValues,
|
||
"2_color_scale": drawCondFmtColorScale,
|
||
"3_color_scale": drawCondFmtColorScale,
|
||
"dataBar": drawCondFmtDataBar,
|
||
"expression": drawCondFmtExp,
|
||
"iconSet": drawCondFmtIconSet,
|
||
}
|
||
|
||
ws, err := f.workSheetReader(sheet)
|
||
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)
|
||
}
|
||
GUID := fmt.Sprintf("{00000000-0000-0000-%04X-%012X}", f.getSheetID(sheet), rules)
|
||
var cfRule []*xlsxCfRule
|
||
for p, v := range opts {
|
||
var vt, ct string
|
||
var ok bool
|
||
// "type" is a required parameter, check for valid validation types.
|
||
vt, ok = validType[v.Type]
|
||
if ok {
|
||
// Check for valid criteria types.
|
||
ct, ok = criteriaType[v.Criteria]
|
||
if ok || vt == "expression" || vt == "iconSet" {
|
||
drawFunc, ok := drawContFmtFunc[vt]
|
||
if ok {
|
||
rule, x14rule := drawFunc(p, ct, GUID, &v)
|
||
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)
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
ws.ConditionalFormatting = append(ws.ConditionalFormatting, &xlsxConditionalFormatting{
|
||
SQRef: rangeRef,
|
||
CfRule: cfRule,
|
||
})
|
||
return err
|
||
}
|
||
|
||
// 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(decodeWorksheetExt)
|
||
condFmts *xlsxX14ConditionalFormattings
|
||
decodeCondFmts *decodeX14ConditionalFormattings
|
||
ext *xlsxWorksheetExt
|
||
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, &xlsxWorksheetExt{
|
||
URI: ExtURIConditionalFormattings, Content: string(condFmtsBytes),
|
||
})
|
||
}
|
||
sort.Slice(decodeExtLst.Ext, func(i, j int) bool {
|
||
return inStrSlice(extensionURIPriority, decodeExtLst.Ext[i].URI, false) <
|
||
inStrSlice(extensionURIPriority, 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 extractCondFmtCellIs(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
|
||
format := ConditionalFormatOptions{StopIfTrue: c.StopIfTrue, Type: "cell", Criteria: operatorType[c.Operator], Format: *c.DxfID}
|
||
if len(c.Formula) == 2 {
|
||
format.MinValue, format.MaxValue = c.Formula[0], c.Formula[1]
|
||
return format
|
||
}
|
||
format.Value = c.Formula[0]
|
||
return format
|
||
}
|
||
|
||
// extractCondFmtTop10 provides a function to extract conditional format
|
||
// settings for top N (default is top 10) by given conditional formatting
|
||
// rule.
|
||
func extractCondFmtTop10(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
|
||
format := ConditionalFormatOptions{
|
||
StopIfTrue: c.StopIfTrue,
|
||
Type: "top",
|
||
Criteria: "=",
|
||
Format: *c.DxfID,
|
||
Percent: c.Percent,
|
||
Value: strconv.Itoa(c.Rank),
|
||
}
|
||
if c.Bottom {
|
||
format.Type = "bottom"
|
||
}
|
||
return format
|
||
}
|
||
|
||
// extractCondFmtAboveAverage provides a function to extract conditional format
|
||
// settings for above average and below average by given conditional formatting
|
||
// rule.
|
||
func extractCondFmtAboveAverage(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
|
||
return ConditionalFormatOptions{
|
||
StopIfTrue: c.StopIfTrue,
|
||
Type: "average",
|
||
Criteria: "=",
|
||
Format: *c.DxfID,
|
||
AboveAverage: *c.AboveAverage,
|
||
}
|
||
}
|
||
|
||
// extractCondFmtDuplicateUniqueValues provides a function to extract
|
||
// conditional format settings for duplicate and unique values by given
|
||
// conditional formatting rule.
|
||
func extractCondFmtDuplicateUniqueValues(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
|
||
return ConditionalFormatOptions{
|
||
StopIfTrue: c.StopIfTrue,
|
||
Type: map[string]string{
|
||
"duplicateValues": "duplicate",
|
||
"uniqueValues": "unique",
|
||
}[c.Type],
|
||
Criteria: "=",
|
||
Format: *c.DxfID,
|
||
}
|
||
}
|
||
|
||
// extractCondFmtColorScale provides a function to extract conditional format
|
||
// settings for color scale (include 2 color scale and 3 color scale) by given
|
||
// conditional formatting rule.
|
||
func extractCondFmtColorScale(c *xlsxCfRule, 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 = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[0].RGB), "FF")
|
||
format.MaxType = c.ColorScale.Cfvo[1].Type
|
||
if c.ColorScale.Cfvo[1].Val != "0" {
|
||
format.MaxValue = c.ColorScale.Cfvo[1].Val
|
||
}
|
||
format.MaxColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[1].RGB), "FF")
|
||
}
|
||
if colors == 3 {
|
||
format.Type = "3_color_scale"
|
||
format.MidType = c.ColorScale.Cfvo[1].Type
|
||
if c.ColorScale.Cfvo[1].Val != "0" {
|
||
format.MidValue = c.ColorScale.Cfvo[1].Val
|
||
}
|
||
format.MidColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[1].RGB), "FF")
|
||
format.MaxType = c.ColorScale.Cfvo[2].Type
|
||
if c.ColorScale.Cfvo[2].Val != "0" {
|
||
format.MaxValue = c.ColorScale.Cfvo[2].Val
|
||
}
|
||
format.MaxColor = "#" + strings.TrimPrefix(strings.ToUpper(c.ColorScale.Color[2].RGB), "FF")
|
||
}
|
||
return format
|
||
}
|
||
|
||
// extractCondFmtDataBar provides a function to extract conditional format
|
||
// settings for data bar by given conditional formatting rule.
|
||
func extractCondFmtDataBar(c *xlsxCfRule, 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 = "#" + strings.TrimPrefix(strings.ToUpper(c.DataBar.Color[0].RGB), "FF")
|
||
if c.DataBar.ShowValue != nil {
|
||
format.BarOnly = !*c.DataBar.ShowValue
|
||
}
|
||
}
|
||
extractDataBarRule := func(condFmts []decodeX14ConditionalFormatting) {
|
||
for _, condFmt := range condFmts {
|
||
for _, rule := range condFmt.CfRule {
|
||
if rule.DataBar != nil {
|
||
format.BarSolid = !rule.DataBar.Gradient
|
||
format.BarDirection = rule.DataBar.Direction
|
||
if rule.DataBar.BorderColor != nil {
|
||
format.BarBorderColor = "#" + strings.TrimPrefix(strings.ToUpper(rule.DataBar.BorderColor.RGB), "FF")
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
extractExtLst := func(extLst *decodeWorksheetExt) {
|
||
for _, ext := range extLst.Ext {
|
||
if ext.URI == ExtURIConditionalFormattings {
|
||
decodeCondFmts := new(decodeX14ConditionalFormattings)
|
||
if err := xml.Unmarshal([]byte(ext.Content), &decodeCondFmts); err == nil {
|
||
var condFmts []decodeX14ConditionalFormatting
|
||
if err = xml.Unmarshal([]byte(decodeCondFmts.Content), &condFmts); err == nil {
|
||
extractDataBarRule(condFmts)
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
if c.ExtLst != nil {
|
||
ext := decodeX14ConditionalFormattingExt{}
|
||
if err := xml.Unmarshal([]byte(c.ExtLst.Ext), &ext); err == nil && extLst != nil {
|
||
decodeExtLst := new(decodeWorksheetExt)
|
||
if err = xml.Unmarshal([]byte("<extLst>"+extLst.Ext+"</extLst>"), decodeExtLst); err == nil {
|
||
extractExtLst(decodeExtLst)
|
||
}
|
||
}
|
||
}
|
||
return format
|
||
}
|
||
|
||
// extractCondFmtExp provides a function to extract conditional format settings
|
||
// for expression by given conditional formatting rule.
|
||
func extractCondFmtExp(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions {
|
||
format := ConditionalFormatOptions{StopIfTrue: c.StopIfTrue, Type: "formula", Format: *c.DxfID}
|
||
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 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) {
|
||
extractContFmtFunc := map[string]func(c *xlsxCfRule, extLst *xlsxExtLst) ConditionalFormatOptions{
|
||
"cellIs": extractCondFmtCellIs,
|
||
"top10": extractCondFmtTop10,
|
||
"aboveAverage": extractCondFmtAboveAverage,
|
||
"duplicateValues": extractCondFmtDuplicateUniqueValues,
|
||
"uniqueValues": extractCondFmtDuplicateUniqueValues,
|
||
"colorScale": extractCondFmtColorScale,
|
||
"dataBar": extractCondFmtDataBar,
|
||
"expression": extractCondFmtExp,
|
||
"iconSet": extractCondFmtIconSet,
|
||
}
|
||
|
||
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(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, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
|
||
c := &xlsxCfRule{
|
||
Priority: p + 1,
|
||
StopIfTrue: format.StopIfTrue,
|
||
Type: validType[format.Type],
|
||
Operator: ct,
|
||
DxfID: intPtr(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 idx := inStrSlice([]string{"equal", "notEqual", "greaterThan", "lessThan", "greaterThanOrEqual", "lessThanOrEqual", "containsText", "notContains", "beginsWith", "endsWith"}, ct, true); idx != -1 {
|
||
c.Formula = append(c.Formula, format.Value)
|
||
}
|
||
return c, 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, 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: intPtr(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, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
|
||
return &xlsxCfRule{
|
||
Priority: p + 1,
|
||
StopIfTrue: format.StopIfTrue,
|
||
Type: validType[format.Type],
|
||
AboveAverage: boolPtr(format.AboveAverage),
|
||
DxfID: intPtr(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, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
|
||
return &xlsxCfRule{
|
||
Priority: p + 1,
|
||
StopIfTrue: format.StopIfTrue,
|
||
Type: validType[format.Type],
|
||
DxfID: intPtr(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, 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, 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, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
|
||
return &xlsxCfRule{
|
||
Priority: p + 1,
|
||
StopIfTrue: format.StopIfTrue,
|
||
Type: validType[format.Type],
|
||
Formula: []string{format.Criteria},
|
||
DxfID: intPtr(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, GUID string, format *ConditionalFormatOptions) (*xlsxCfRule, *xlsxX14CfRule) {
|
||
cfvo3 := &xlsxCfRule{IconSet: &xlsxIconSet{Cfvo: []*xlsxCfvo{
|
||
{Type: "percent", Val: "0"},
|
||
{Type: "percent", Val: "33"},
|
||
{Type: "percent", Val: "67"},
|
||
}}}
|
||
cfvo4 := &xlsxCfRule{IconSet: &xlsxIconSet{Cfvo: []*xlsxCfvo{
|
||
{Type: "percent", Val: "0"},
|
||
{Type: "percent", Val: "25"},
|
||
{Type: "percent", Val: "50"},
|
||
{Type: "percent", Val: "75"},
|
||
}}}
|
||
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"},
|
||
}}}
|
||
presets := 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,
|
||
}
|
||
cfRule, ok := presets[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() (*xlsxTheme, error) {
|
||
if _, ok := f.Pkg.Load(defaultXMLPathTheme); !ok {
|
||
return nil, nil
|
||
}
|
||
theme := xlsxTheme{XMLNSa: NameSpaceDrawingML.Value, XMLNSr: SourceRelationship.Value}
|
||
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)
|
||
}
|