457 lines
14 KiB
Go
457 lines
14 KiB
Go
// 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 (
|
|
"fmt"
|
|
"io"
|
|
"math"
|
|
"strings"
|
|
"unicode/utf16"
|
|
)
|
|
|
|
// DataValidationType defined the type of data validation.
|
|
type DataValidationType int
|
|
|
|
// Data validation types.
|
|
const (
|
|
_ DataValidationType = iota
|
|
DataValidationTypeNone
|
|
DataValidationTypeCustom
|
|
DataValidationTypeDate
|
|
DataValidationTypeDecimal
|
|
DataValidationTypeList
|
|
DataValidationTypeTextLength
|
|
DataValidationTypeTime
|
|
DataValidationTypeWhole
|
|
)
|
|
|
|
// DataValidationErrorStyle defined the style of data validation error alert.
|
|
type DataValidationErrorStyle int
|
|
|
|
// Data validation error styles.
|
|
const (
|
|
_ DataValidationErrorStyle = iota
|
|
DataValidationErrorStyleStop
|
|
DataValidationErrorStyleWarning
|
|
DataValidationErrorStyleInformation
|
|
)
|
|
|
|
// Data validation error styles.
|
|
const (
|
|
styleStop = "stop"
|
|
styleWarning = "warning"
|
|
styleInformation = "information"
|
|
)
|
|
|
|
// DataValidationOperator operator enum.
|
|
type DataValidationOperator int
|
|
|
|
// Data validation operators.
|
|
const (
|
|
_ DataValidationOperator = iota
|
|
DataValidationOperatorBetween
|
|
DataValidationOperatorEqual
|
|
DataValidationOperatorGreaterThan
|
|
DataValidationOperatorGreaterThanOrEqual
|
|
DataValidationOperatorLessThan
|
|
DataValidationOperatorLessThanOrEqual
|
|
DataValidationOperatorNotBetween
|
|
DataValidationOperatorNotEqual
|
|
)
|
|
|
|
var (
|
|
// formulaEscaper mimics the Excel escaping rules for data validation,
|
|
// which converts `"` to `""` instead of `"`.
|
|
formulaEscaper = strings.NewReplacer(
|
|
`&`, `&`,
|
|
`<`, `<`,
|
|
`>`, `>`,
|
|
)
|
|
formulaUnescaper = strings.NewReplacer(
|
|
`&`, `&`,
|
|
`<`, `<`,
|
|
`>`, `>`,
|
|
)
|
|
// dataValidationTypeMap defined supported data validation types.
|
|
dataValidationTypeMap = map[DataValidationType]string{
|
|
DataValidationTypeNone: "none",
|
|
DataValidationTypeCustom: "custom",
|
|
DataValidationTypeDate: "date",
|
|
DataValidationTypeDecimal: "decimal",
|
|
DataValidationTypeList: "list",
|
|
DataValidationTypeTextLength: "textLength",
|
|
DataValidationTypeTime: "time",
|
|
DataValidationTypeWhole: "whole",
|
|
}
|
|
// dataValidationOperatorMap defined supported data validation operators.
|
|
dataValidationOperatorMap = map[DataValidationOperator]string{
|
|
DataValidationOperatorBetween: "between",
|
|
DataValidationOperatorEqual: "equal",
|
|
DataValidationOperatorGreaterThan: "greaterThan",
|
|
DataValidationOperatorGreaterThanOrEqual: "greaterThanOrEqual",
|
|
DataValidationOperatorLessThan: "lessThan",
|
|
DataValidationOperatorLessThanOrEqual: "lessThanOrEqual",
|
|
DataValidationOperatorNotBetween: "notBetween",
|
|
DataValidationOperatorNotEqual: "notEqual",
|
|
}
|
|
)
|
|
|
|
// NewDataValidation return data validation struct.
|
|
func NewDataValidation(allowBlank bool) *DataValidation {
|
|
return &DataValidation{
|
|
AllowBlank: allowBlank,
|
|
ShowErrorMessage: false,
|
|
ShowInputMessage: false,
|
|
}
|
|
}
|
|
|
|
// SetError set error notice.
|
|
func (dv *DataValidation) SetError(style DataValidationErrorStyle, title, msg string) {
|
|
dv.Error = &msg
|
|
dv.ErrorTitle = &title
|
|
strStyle := styleStop
|
|
switch style {
|
|
case DataValidationErrorStyleStop:
|
|
strStyle = styleStop
|
|
case DataValidationErrorStyleWarning:
|
|
strStyle = styleWarning
|
|
case DataValidationErrorStyleInformation:
|
|
strStyle = styleInformation
|
|
|
|
}
|
|
dv.ShowErrorMessage = true
|
|
dv.ErrorStyle = &strStyle
|
|
}
|
|
|
|
// SetInput set prompt notice.
|
|
func (dv *DataValidation) SetInput(title, msg string) {
|
|
dv.ShowInputMessage = true
|
|
dv.PromptTitle = &title
|
|
dv.Prompt = &msg
|
|
}
|
|
|
|
// SetDropList data validation list. If you type the items into the data
|
|
// validation dialog box (a delimited list), the limit is 255 characters,
|
|
// including the separators. If your data validation list source formula is
|
|
// over the maximum length limit, please set the allowed values in the
|
|
// worksheet cells, and use the SetSqrefDropList function to set the reference
|
|
// for their cells.
|
|
func (dv *DataValidation) SetDropList(keys []string) error {
|
|
formula := strings.Join(keys, ",")
|
|
if MaxFieldLength < len(utf16.Encode([]rune(formula))) {
|
|
return ErrDataValidationFormulaLength
|
|
}
|
|
dv.Type = dataValidationTypeMap[DataValidationTypeList]
|
|
if strings.HasPrefix(formula, "=") {
|
|
dv.Formula1 = formulaEscaper.Replace(formula)
|
|
return nil
|
|
}
|
|
dv.Formula1 = fmt.Sprintf(`"%s"`, strings.NewReplacer(`"`, `""`).Replace(formulaEscaper.Replace(formula)))
|
|
return nil
|
|
}
|
|
|
|
// SetRange provides function to set data validation range in drop list, only
|
|
// accepts int, float64, string or []string data type formula argument.
|
|
func (dv *DataValidation) SetRange(f1, f2 interface{}, t DataValidationType, o DataValidationOperator) error {
|
|
genFormula := func(val interface{}) (string, error) {
|
|
var formula string
|
|
switch v := val.(type) {
|
|
case int:
|
|
formula = fmt.Sprintf("%d", v)
|
|
case float64:
|
|
if math.Abs(v) > math.MaxFloat32 {
|
|
return formula, ErrDataValidationRange
|
|
}
|
|
formula = fmt.Sprintf("%.17g", v)
|
|
case string:
|
|
formula = v
|
|
default:
|
|
return formula, ErrParameterInvalid
|
|
}
|
|
return formula, nil
|
|
}
|
|
formula1, err := genFormula(f1)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
formula2, err := genFormula(f2)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
dv.Formula1, dv.Formula2 = formula1, formula2
|
|
dv.Type = dataValidationTypeMap[t]
|
|
dv.Operator = dataValidationOperatorMap[o]
|
|
return err
|
|
}
|
|
|
|
// SetSqrefDropList provides set data validation on a range with source
|
|
// reference range of the worksheet by given data validation object and
|
|
// worksheet name. The data validation object can be created by
|
|
// NewDataValidation function. There are limits to the number of items that
|
|
// will show in a data validation drop down list: The list can show up to show
|
|
// 32768 items from a list on the worksheet. If you need more items than that,
|
|
// you could create a dependent drop down list, broken down by category. For
|
|
// example, set data validation on Sheet1!A7:B8 with validation criteria source
|
|
// Sheet1!E1:E3 settings, create in-cell dropdown by allowing list source:
|
|
//
|
|
// dv := excelize.NewDataValidation(true)
|
|
// dv.Sqref = "A7:B8"
|
|
// dv.SetSqrefDropList("$E$1:$E$3")
|
|
// err := f.AddDataValidation("Sheet1", dv)
|
|
func (dv *DataValidation) SetSqrefDropList(sqref string) {
|
|
dv.Formula1 = sqref
|
|
dv.Type = dataValidationTypeMap[DataValidationTypeList]
|
|
}
|
|
|
|
// SetSqref provides function to set data validation range in drop list.
|
|
func (dv *DataValidation) SetSqref(sqref string) {
|
|
if dv.Sqref == "" {
|
|
dv.Sqref = sqref
|
|
return
|
|
}
|
|
dv.Sqref = fmt.Sprintf("%s %s", dv.Sqref, sqref)
|
|
}
|
|
|
|
// AddDataValidation provides set data validation on a range of the worksheet
|
|
// by given data validation object and worksheet name. This function is
|
|
// concurrency safe. The data validation object can be created by
|
|
// NewDataValidation function.
|
|
//
|
|
// Example 1, set data validation on Sheet1!A1:B2 with validation criteria
|
|
// settings, show error alert after invalid data is entered with "Stop" style
|
|
// and custom title "error body":
|
|
//
|
|
// dv := excelize.NewDataValidation(true)
|
|
// dv.Sqref = "A1:B2"
|
|
// dv.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorBetween)
|
|
// dv.SetError(excelize.DataValidationErrorStyleStop, "error title", "error body")
|
|
// err := f.AddDataValidation("Sheet1", dv)
|
|
//
|
|
// Example 2, set data validation on Sheet1!A3:B4 with validation criteria
|
|
// settings, and show input message when cell is selected:
|
|
//
|
|
// dv = excelize.NewDataValidation(true)
|
|
// dv.Sqref = "A3:B4"
|
|
// dv.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorGreaterThan)
|
|
// dv.SetInput("input title", "input body")
|
|
// err = f.AddDataValidation("Sheet1", dv)
|
|
//
|
|
// Example 3, set data validation on Sheet1!A5:B6 with validation criteria
|
|
// settings, create in-cell dropdown by allowing list source:
|
|
//
|
|
// dv = excelize.NewDataValidation(true)
|
|
// dv.Sqref = "A5:B6"
|
|
// dv.SetDropList([]string{"1", "2", "3"})
|
|
// err = f.AddDataValidation("Sheet1", dv)
|
|
func (f *File) AddDataValidation(sheet string, dv *DataValidation) error {
|
|
ws, err := f.workSheetReader(sheet)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
ws.mu.Lock()
|
|
defer ws.mu.Unlock()
|
|
if nil == ws.DataValidations {
|
|
ws.DataValidations = new(xlsxDataValidations)
|
|
}
|
|
dataValidation := &xlsxDataValidation{
|
|
AllowBlank: dv.AllowBlank,
|
|
Error: dv.Error,
|
|
ErrorStyle: dv.ErrorStyle,
|
|
ErrorTitle: dv.ErrorTitle,
|
|
Operator: dv.Operator,
|
|
Prompt: dv.Prompt,
|
|
PromptTitle: dv.PromptTitle,
|
|
ShowDropDown: dv.ShowDropDown,
|
|
ShowErrorMessage: dv.ShowErrorMessage,
|
|
ShowInputMessage: dv.ShowInputMessage,
|
|
Sqref: dv.Sqref,
|
|
Type: dv.Type,
|
|
}
|
|
if dv.Formula1 != "" {
|
|
dataValidation.Formula1 = &xlsxInnerXML{Content: dv.Formula1}
|
|
}
|
|
if dv.Formula2 != "" {
|
|
dataValidation.Formula2 = &xlsxInnerXML{Content: dv.Formula2}
|
|
}
|
|
ws.DataValidations.DataValidation = append(ws.DataValidations.DataValidation, dataValidation)
|
|
ws.DataValidations.Count = len(ws.DataValidations.DataValidation)
|
|
return err
|
|
}
|
|
|
|
// GetDataValidations returns data validations list by given worksheet name.
|
|
func (f *File) GetDataValidations(sheet string) ([]*DataValidation, error) {
|
|
ws, err := f.workSheetReader(sheet)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
var (
|
|
dataValidations []*DataValidation
|
|
decodeExtLst = new(decodeExtLst)
|
|
decodeDataValidations *xlsxDataValidations
|
|
ext *xlsxExt
|
|
)
|
|
if ws.DataValidations != nil {
|
|
dataValidations = append(dataValidations, getDataValidations(ws.DataValidations)...)
|
|
}
|
|
if ws.ExtLst != nil {
|
|
if err = f.xmlNewDecoder(strings.NewReader("<extLst>" + ws.ExtLst.Ext + "</extLst>")).
|
|
Decode(decodeExtLst); err != nil && err != io.EOF {
|
|
return dataValidations, err
|
|
}
|
|
for _, ext = range decodeExtLst.Ext {
|
|
if ext.URI == ExtURIDataValidations {
|
|
decodeDataValidations = new(xlsxDataValidations)
|
|
_ = f.xmlNewDecoder(strings.NewReader(ext.Content)).Decode(decodeDataValidations)
|
|
dataValidations = append(dataValidations, getDataValidations(decodeDataValidations)...)
|
|
}
|
|
}
|
|
}
|
|
return dataValidations, err
|
|
}
|
|
|
|
// getDataValidations returns data validations list by given worksheet data
|
|
// validations.
|
|
func getDataValidations(dvs *xlsxDataValidations) []*DataValidation {
|
|
if dvs == nil {
|
|
return nil
|
|
}
|
|
var dataValidations []*DataValidation
|
|
for _, dv := range dvs.DataValidation {
|
|
if dv == nil {
|
|
continue
|
|
}
|
|
dataValidation := &DataValidation{
|
|
AllowBlank: dv.AllowBlank,
|
|
Error: dv.Error,
|
|
ErrorStyle: dv.ErrorStyle,
|
|
ErrorTitle: dv.ErrorTitle,
|
|
Operator: dv.Operator,
|
|
Prompt: dv.Prompt,
|
|
PromptTitle: dv.PromptTitle,
|
|
ShowDropDown: dv.ShowDropDown,
|
|
ShowErrorMessage: dv.ShowErrorMessage,
|
|
ShowInputMessage: dv.ShowInputMessage,
|
|
Sqref: dv.Sqref,
|
|
Type: dv.Type,
|
|
}
|
|
if dv.Formula1 != nil {
|
|
dataValidation.Formula1 = unescapeDataValidationFormula(dv.Formula1.Content)
|
|
}
|
|
if dv.Formula2 != nil {
|
|
dataValidation.Formula2 = unescapeDataValidationFormula(dv.Formula2.Content)
|
|
}
|
|
if dv.XMSqref != "" {
|
|
dataValidation.Sqref = dv.XMSqref
|
|
dataValidation.Formula1 = strings.TrimSuffix(strings.TrimPrefix(dataValidation.Formula1, "<xm:f>"), "</xm:f>")
|
|
dataValidation.Formula2 = strings.TrimSuffix(strings.TrimPrefix(dataValidation.Formula2, "<xm:f>"), "</xm:f>")
|
|
}
|
|
dataValidations = append(dataValidations, dataValidation)
|
|
}
|
|
return dataValidations
|
|
}
|
|
|
|
// DeleteDataValidation delete data validation by given worksheet name and
|
|
// reference sequence. This function is concurrency safe.
|
|
// All data validations in the worksheet will be deleted
|
|
// if not specify reference sequence parameter.
|
|
func (f *File) DeleteDataValidation(sheet string, sqref ...string) error {
|
|
ws, err := f.workSheetReader(sheet)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
ws.mu.Lock()
|
|
defer ws.mu.Unlock()
|
|
if ws.DataValidations == nil {
|
|
return nil
|
|
}
|
|
if sqref == nil {
|
|
ws.DataValidations = nil
|
|
return nil
|
|
}
|
|
delCells, err := flatSqref(sqref[0])
|
|
if err != nil {
|
|
return err
|
|
}
|
|
dv := ws.DataValidations
|
|
for i := 0; i < len(dv.DataValidation); i++ {
|
|
var applySqref []string
|
|
colCells, err := flatSqref(dv.DataValidation[i].Sqref)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for col, cells := range delCells {
|
|
for _, cell := range cells {
|
|
idx := inCoordinates(colCells[col], cell)
|
|
if idx != -1 {
|
|
colCells[col] = append(colCells[col][:idx], colCells[col][idx+1:]...)
|
|
}
|
|
}
|
|
}
|
|
for _, col := range colCells {
|
|
applySqref = append(applySqref, squashSqref(col)...)
|
|
}
|
|
dv.DataValidation[i].Sqref = strings.Join(applySqref, " ")
|
|
if len(applySqref) == 0 {
|
|
dv.DataValidation = append(dv.DataValidation[:i], dv.DataValidation[i+1:]...)
|
|
i--
|
|
}
|
|
}
|
|
dv.Count = len(dv.DataValidation)
|
|
if dv.Count == 0 {
|
|
ws.DataValidations = nil
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// squashSqref generates cell reference sequence by given cells coordinates list.
|
|
func squashSqref(cells [][]int) []string {
|
|
if len(cells) == 1 {
|
|
cell, _ := CoordinatesToCellName(cells[0][0], cells[0][1])
|
|
return []string{cell}
|
|
} else if len(cells) == 0 {
|
|
return []string{}
|
|
}
|
|
var refs []string
|
|
l, r := 0, 0
|
|
for i := 1; i < len(cells); i++ {
|
|
if cells[i][0] == cells[r][0] && cells[i][1]-cells[r][1] > 1 {
|
|
ref, _ := coordinatesToRangeRef(append(cells[l], cells[r]...))
|
|
if l == r {
|
|
ref, _ = CoordinatesToCellName(cells[l][0], cells[l][1])
|
|
}
|
|
refs = append(refs, ref)
|
|
l, r = i, i
|
|
} else {
|
|
r++
|
|
}
|
|
}
|
|
ref, _ := coordinatesToRangeRef(append(cells[l], cells[r]...))
|
|
if l == r {
|
|
ref, _ = CoordinatesToCellName(cells[l][0], cells[l][1])
|
|
}
|
|
return append(refs, ref)
|
|
}
|
|
|
|
// isFormulaDataValidation returns whether the data validation rule is a formula.
|
|
func (dv *xlsxInnerXML) isFormula() bool {
|
|
return dv != nil && !(strings.HasPrefix(dv.Content, """) && strings.HasSuffix(dv.Content, """))
|
|
}
|
|
|
|
// unescapeDataValidationFormula returns unescaped data validation formula.
|
|
func unescapeDataValidationFormula(val string) string {
|
|
if strings.HasPrefix(val, "\"") { // Text detection
|
|
return strings.NewReplacer(`""`, `"`).Replace(formulaUnescaper.Replace(val))
|
|
}
|
|
return formulaUnescaper.Replace(val)
|
|
}
|