pivotTable.go

// Copyright 2016 - 2026 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.25.0 or later.

package excelize

import (
	"bytes"
	"encoding/xml"
	"fmt"
	"io"
	"path/filepath"
	"reflect"
	"strconv"
	"strings"

	"golang.org/x/text/cases"
	"golang.org/x/text/language"
)

// PivotTableOptions directly maps the format settings of the pivot table.
//
// PivotTableStyleName: The built-in pivot table style names
//
//	PivotStyleLight1 - PivotStyleLight28
//	PivotStyleMedium1 - PivotStyleMedium28
//	PivotStyleDark1 - PivotStyleDark28
type PivotTableOptions struct {
	items               map[string][]*xlsxItem
	sharedItems         map[string]xlsxSharedItems
	pivotTableXML       string
	pivotCacheXML       string
	pivotSheetName      string
	pivotDataRange      string
	namedDataRange      bool
	DataRange           string
	PivotTableRange     string
	Name                string
	Rows                []PivotTableField
	Columns             []PivotTableField
	Data                []PivotTableField
	Filter              []PivotTableField
	RowGrandTotals      bool
	ColGrandTotals      bool
	ShowDrill           bool
	UseAutoFormatting   bool
	PageOverThenDown    bool
	MergeItem           bool
	ClassicLayout       bool
	CompactData         bool
	ShowError           bool
	ShowRowHeaders      bool
	ShowColHeaders      bool
	ShowRowStripes      bool
	ShowColStripes      bool
	ShowLastColumn      bool
	FieldPrintTitles    bool
	ItemPrintTitles     bool
	PivotTableStyleName string
}

// PivotTableShowValuesAsType is the type of calculation for showing values in a
// pivot table.
type PivotTableShowValuesAsType byte

// PivotTableShowValuesAsType defines the calculation type enumeration.
const (
	PivotTableShowValuesAsNoCalculation PivotTableShowValuesAsType = iota
	PivotTableShowValuesAsPercentOfGrandTotal
	PivotTableShowValuesAsPercentOfColumnTotal
	PivotTableShowValuesAsPercentOfRowTotal
	PivotTableShowValuesAsPercentOf
	PivotTableShowValuesAsPercentOfParentRowTotal
	PivotTableShowValuesAsPercentOfParentColumnTotal
	PivotTableShowValuesAsPercentOfParentTotal
	PivotTableShowValuesAsDifferenceFrom
	PivotTableShowValuesAsPercentDifferenceFrom
	PivotTableShowValuesAsRunningTotalIn
	PivotTableShowValuesAsPercentRunningTotalIn
	PivotTableShowValuesAsRankSmallestToLargest
	PivotTableShowValuesAsRankLargestToSmallest
	PivotTableShowValuesAsIndex
)

// PivotTableShowValuesAs directly maps the show values as settings of the pivot
// table.
type PivotTableShowValuesAs struct {
	Type      PivotTableShowValuesAsType
	BaseField string
	BaseItem  string
}

// PivotTableField directly maps the field settings of the pivot table.
//
// Name specifies the name of the data field. Maximum 255 characters
// are allowed in data field name, excess characters will be truncated.
//
// Subtotal specifies the aggregation function that applies to this data
// field. The default value is sum. The possible values for this attribute
// are:
//
//	Average
//	Count
//	CountNums
//	Max
//	Min
//	Product
//	StdDev
//	StdDevp
//	Sum
//	Var
//	Varp
//
// NumFmt specifies the number format ID of the data field, this field only
// accepts built-in number format ID and does not support custom number format
// expression currently.
//
// SelectedItems specifies the default selected items in a pivot table field.
// The selected items must be values within the cell range referenced by that
// field.
//
// ShowValuesAs specifies the calculation type for showing values in a pivot
// table values fields. The possible values for the Type field of ShowValuesAs
// are:
//
//	PivotTableShowValuesAsPercentOfGrandTotal
//	PivotTableShowValuesAsPercentOfColumnTotal
//	PivotTableShowValuesAsPercentOfRowTotal
//	PivotTableShowValuesAsPercentOf
//	PivotTableShowValuesAsPercentOfParentRowTotal
//	PivotTableShowValuesAsPercentOfParentColumnTotal
//	PivotTableShowValuesAsPercentOfParentTotal
//	PivotTableShowValuesAsDifferenceFrom
//	PivotTableShowValuesAsPercentDifferenceFrom
//	PivotTableShowValuesAsRunningTotalIn
//	PivotTableShowValuesAsPercentRunningTotalIn
//	PivotTableShowValuesAsRankSmallestToLargest
//	PivotTableShowValuesAsRankLargestToSmallest
//	PivotTableShowValuesAsIndex
//
// Note that the base field and base item settings of ShowValuesAs are only
// required for some calculation types, the calculation types requires base
// field settings are:
//
//	PivotTableShowValuesAsPercentOf
//	PivotTableShowValuesAsPercentOfParentTotal
//	PivotTableShowValuesAsDifferenceFrom
//	PivotTableShowValuesAsPercentDifferenceFrom
//	PivotTableShowValuesAsRunningTotalIn
//	PivotTableShowValuesAsPercentRunningTotalIn
//	PivotTableShowValuesAsRankSmallestToLargest
//	PivotTableShowValuesAsRankLargestToSmallest
//
// The supported calculation types requires base item settings are:
//
//	PivotTableShowValuesAsPercentOf
//	PivotTableShowValuesAsDifferenceFrom
//	PivotTableShowValuesAsPercentDifferenceFrom
type PivotTableField struct {
	Compact         bool
	Data            string
	Name            string
	Outline         bool
	ShowAll         bool
	InsertBlankRow  bool
	Subtotal        string
	DefaultSubtotal bool
	NumFmt          int
	SelectedItems   []string
	ShowValuesAs    PivotTableShowValuesAs
}

var (
	pivotTableShowValuesAsMap = map[PivotTableShowValuesAsType]string{
		PivotTableShowValuesAsPercentOfGrandTotal:        "percentOfTotal",
		PivotTableShowValuesAsPercentOfColumnTotal:       "percentOfCol",
		PivotTableShowValuesAsPercentOfRowTotal:          "percentOfRow",
		PivotTableShowValuesAsPercentOf:                  "percent",
		PivotTableShowValuesAsPercentOfParentRowTotal:    "percentOfParentRow",
		PivotTableShowValuesAsPercentOfParentColumnTotal: "percentOfParentCol",
		PivotTableShowValuesAsPercentOfParentTotal:       "percentOfParent",
		PivotTableShowValuesAsDifferenceFrom:             "difference",
		PivotTableShowValuesAsPercentDifferenceFrom:      "percentDiff",
		PivotTableShowValuesAsRunningTotalIn:             "runTotal",
		PivotTableShowValuesAsPercentRunningTotalIn:      "percentOfRunningTotal",
		PivotTableShowValuesAsRankSmallestToLargest:      "rankAscending",
		PivotTableShowValuesAsRankLargestToSmallest:      "rankDescending",
		PivotTableShowValuesAsIndex:                      "index",
	}
	pivotTableShowValuesAsX14Map = map[PivotTableShowValuesAsType]bool{
		PivotTableShowValuesAsPercentOfParentRowTotal:    true,
		PivotTableShowValuesAsPercentOfParentColumnTotal: true,
		PivotTableShowValuesAsPercentOfParentTotal:       true,
		PivotTableShowValuesAsPercentRunningTotalIn:      true,
		PivotTableShowValuesAsRankSmallestToLargest:      true,
		PivotTableShowValuesAsRankLargestToSmallest:      true,
		PivotTableShowValuesAsIndex:                      true,
	}
	pivotTableShowValuesAsBaseFieldRequiredMap = map[PivotTableShowValuesAsType]bool{
		PivotTableShowValuesAsPercentOf:             true,
		PivotTableShowValuesAsPercentOfParentTotal:  true,
		PivotTableShowValuesAsDifferenceFrom:        true,
		PivotTableShowValuesAsPercentDifferenceFrom: true,
		PivotTableShowValuesAsRunningTotalIn:        true,
		PivotTableShowValuesAsPercentRunningTotalIn: true,
		PivotTableShowValuesAsRankSmallestToLargest: true,
		PivotTableShowValuesAsRankLargestToSmallest: true,
	}
	pivotTableShowValuesAsBaseItemRequiredMap = map[PivotTableShowValuesAsType]bool{
		PivotTableShowValuesAsPercentOf:             true,
		PivotTableShowValuesAsDifferenceFrom:        true,
		PivotTableShowValuesAsPercentDifferenceFrom: true,
	}
)

// AddPivotTable provides the method to add pivot table by given pivot table
// options. Note that the same fields can not in Columns, Rows and Filter
// fields at the same time.
//
// For example, create a pivot table on the range reference Sheet1!G4:M30 with
// the range reference Sheet1!A1:E31 as the data source, summarize by sum for
// revenue:
//
//	package main
//
//	import (
//	    "fmt"
//
//	    "github.com/xuri/excelize/v2"
//	)
//
//	func main() {
//	    f := excelize.NewFile()
//	    defer func() {
//	        if err := f.Close(); err != nil {
//	            fmt.Println(err)
//	        }
//	    }()
//	    // Create some data in a sheet
//	    month := []string{"Jan", "Feb", "Mar", "Apr", "May",
//	        "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
//	    year := []int{2017, 2018, 2019}
//	    types := []string{"Meat", "Dairy", "Beverages", "Produce"}
//	    revenue := []int{3217, 4512, 3891, 4738, 3054, 4265, 3643, 4901, 3378, 4126}
//	    region := []string{"East", "West", "North", "South"}
//	    if err := f.SetSheetRow(
//	        "Sheet1", "A1", &[]string{"Month", "Year", "Type", "Revenue", "Region"},
//	    ); err != nil {
//	        fmt.Println(err)
//	        return
//	    }
//	    for row := 2; row < 32; row++ {
//	        f.SetCellValue("Sheet1", fmt.Sprintf("A%d", row), month[(row-2)%len(month)])
//	        f.SetCellValue("Sheet1", fmt.Sprintf("B%d", row), year[(row-2)%len(year)])
//	        f.SetCellValue("Sheet1", fmt.Sprintf("C%d", row), types[(row-2)%len(types)])
//	        f.SetCellValue("Sheet1", fmt.Sprintf("D%d", row), revenue[(row-2)%len(revenue)])
//	        f.SetCellValue("Sheet1", fmt.Sprintf("E%d", row), region[(row-2)%len(region)])
//	    }
//	    if err := f.AddPivotTable(&excelize.PivotTableOptions{
//	        DataRange:       "Sheet1!A1:E31",
//	        PivotTableRange: "Sheet1!G4:M30",
//	        Rows: []excelize.PivotTableField{
//	            {Data: "Month", DefaultSubtotal: true}, {Data: "Year"},
//	        },
//	        Filter: []excelize.PivotTableField{
//	            {Data: "Region"}},
//	        Columns: []excelize.PivotTableField{
//	            {Data: "Type", DefaultSubtotal: true},
//	        },
//	        Data: []excelize.PivotTableField{
//	            {Data: "Revenue", Name: "Summarize", Subtotal: "Sum"},
//	        },
//	        RowGrandTotals: true,
//	        ColGrandTotals: true,
//	        ShowDrill:      true,
//	        ShowRowHeaders: true,
//	        ShowColHeaders: true,
//	        ShowLastColumn: true,
//	    }); err != nil {
//	        fmt.Println(err)
//	        return
//	    }
//	    if err := f.SaveAs("Book1.xlsx"); err != nil {
//	        fmt.Println(err)
//	    }
//	}
func (f *File) AddPivotTable(opts *PivotTableOptions) error {
	// parameter validation
	_, pivotTableSheetPath, err := f.parseFormatPivotTableSet(opts)
	if err != nil {
		return err
	}
	f.clearCalcCache()
	pivotTableID := f.countPivotTables() + 1
	pivotCacheID := f.countPivotCache() + 1

	sheetRelationshipsPivotTableXML := "../pivotTables/pivotTable" + strconv.Itoa(pivotTableID) + ".xml"
	opts.pivotTableXML = strings.ReplaceAll(sheetRelationshipsPivotTableXML, "..", "xl")
	opts.pivotCacheXML = "xl/pivotCache/pivotCacheDefinition" + strconv.Itoa(pivotCacheID) + ".xml"
	if err = f.addPivotCache(opts); err != nil {
		return err
	}

	// workbook pivot cache
	workBookPivotCacheRID := f.addRels(f.getWorkbookRelsPath(), SourceRelationshipPivotCache, strings.TrimPrefix(opts.pivotCacheXML, "xl/"), "")
	cacheID := f.addWorkbookPivotCache(workBookPivotCacheRID)

	pivotCacheRels := "xl/pivotTables/_rels/pivotTable" + strconv.Itoa(pivotTableID) + ".xml.rels"
	// rId not used
	_ = f.addRels(pivotCacheRels, SourceRelationshipPivotCache, fmt.Sprintf("../pivotCache/pivotCacheDefinition%d.xml", pivotCacheID), "")
	if err = f.addPivotTable(cacheID, pivotTableID, opts); err != nil {
		return err
	}
	pivotTableSheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(pivotTableSheetPath, "xl/worksheets/") + ".rels"
	f.addRels(pivotTableSheetRels, SourceRelationshipPivotTable, sheetRelationshipsPivotTableXML, "")
	if err = f.addContentTypePart(pivotTableID, "pivotTable"); err != nil {
		return err
	}
	return f.addContentTypePart(pivotCacheID, "pivotCache")
}

// parseFormatPivotTableSet provides a function to validate pivot table
// properties.
func (f *File) parseFormatPivotTableSet(opts *PivotTableOptions) (*xlsxWorksheet, string, error) {
	if opts == nil {
		return nil, "", ErrParameterRequired
	}
	pivotTableSheetName, _, err := f.adjustRange(opts.PivotTableRange)
	if err != nil {
		return nil, "", newPivotTableRangeError(err.Error())
	}
	if countUTF16String(opts.Name) > MaxFieldLength {
		return nil, "", ErrNameLength
	}
	opts.pivotSheetName = pivotTableSheetName
	if err = f.getPivotTableDataRange(opts); err != nil {
		return nil, "", err
	}
	dataSheetName, _, err := f.adjustRange(opts.pivotDataRange)
	if err != nil {
		return nil, "", newPivotTableDataRangeError(err.Error())
	}
	dataSheet, err := f.workSheetReader(dataSheetName)
	if err != nil {
		return dataSheet, "", err
	}
	pivotTableSheetPath, ok := f.getSheetXMLPath(pivotTableSheetName)
	if !ok {
		return dataSheet, pivotTableSheetPath, ErrSheetNotExist{pivotTableSheetName}
	}
	if opts.CompactData && opts.ClassicLayout {
		return nil, "", ErrPivotTableClassicLayout
	}
	var colDataFields, rowDataFields []string
	for _, f := range opts.Filter {
		if inPivotTableField(opts.Columns, f.Data) != -1 {
			colDataFields = append(colDataFields, f.Data)
		}
		if inPivotTableField(opts.Rows, f.Data) != -1 {
			rowDataFields = append(rowDataFields, f.Data)
		}
	}
	if len(colDataFields) > 0 {
		return dataSheet, pivotTableSheetPath, newPivotTableColFieldsError(colDataFields)
	}
	if len(rowDataFields) > 0 {
		return dataSheet, pivotTableSheetPath, newPivotTableRowFieldsError(rowDataFields)
	}
	return dataSheet, pivotTableSheetPath, err
}

// adjustRange adjust range, for example: adjust Sheet1!$E$31:$A$1 to Sheet1!$A$1:$E$31
func (f *File) adjustRange(rangeStr string) (string, []int, error) {
	if len(rangeStr) < 1 {
		return "", []int{}, ErrParameterRequired
	}
	rng := strings.Split(rangeStr, "!")
	if len(rng) != 2 {
		return "", []int{}, ErrParameterInvalid
	}
	trimRng := strings.ReplaceAll(rng[1], "$", "")
	coordinates, err := rangeRefToCoordinates(trimRng)
	if err != nil {
		return rng[0], []int{}, err
	}
	x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3]
	if x1 == x2 && y1 == y2 {
		return rng[0], []int{}, ErrParameterInvalid
	}

	// Correct the range, such correct C1:B3 to B1:C3.
	if x2 < x1 {
		x1, x2 = x2, x1
	}

	if y2 < y1 {
		y1, y2 = y2, y1
	}
	return rng[0], []int{x1, y1, x2, y2}, nil
}

// getTableFieldsOrder provides a function to get order list of pivot table
// fields.
func (f *File) getTableFieldsOrder(opts *PivotTableOptions) ([]string, error) {
	var order []string
	if err := f.getPivotTableDataRange(opts); err != nil {
		return order, err
	}
	dataSheet, coordinates, err := f.adjustRange(opts.pivotDataRange)
	if err != nil {
		return order, newPivotTableDataRangeError(err.Error())
	}
	for col := coordinates[0]; col <= coordinates[2]; col++ {
		coordinate, _ := CoordinatesToCellName(col, coordinates[1])
		name, err := f.GetCellValue(dataSheet, coordinate)
		if err != nil {
			return order, err
		}
		if name == "" {
			return order, ErrParameterInvalid
		}
		order = append(order, name)
	}
	return order, nil
}

// MarshalXML encodes a shared item element without default namespace.
func (si xlsxSharedItem) MarshalXML(e *xml.Encoder, start xml.StartElement) error {
	start.Name = xml.Name{Local: si.XMLName.Local}
	type sharedItem xlsxSharedItem
	return e.EncodeElement(sharedItem(si), start)
}

// addMissingItem adds a missing shared item for pivot table field if it does
// not exist.
func (si *xlsxSharedItems) addMissingItem() {
	for _, item := range si.Items {
		if item.XMLName.Local == "m" {
			return
		}
	}
	si.Items = append(si.Items, xlsxSharedItem{XMLName: xml.Name{Local: "m"}})
	si.ContainsBlank = true
}

// addNumberItem adds a number shared item for pivot table field if it does not
// exist.
func (si *xlsxSharedItems) addNumberItem(val string) {
	for _, item := range si.Items {
		if item.XMLName.Local == "n" && item.V == val {
			return
		}
	}
	si.Items = append(si.Items, xlsxSharedItem{XMLName: xml.Name{Local: "n"}, V: val})
	si.ContainsNumber = true
}

// addBooleanItem adds a boolean shared item for pivot table field if it does
// not exist.
func (si *xlsxSharedItems) addBooleanItem(val string) {
	v := strconv.FormatBool(strings.ToUpper(val) == "TRUE")
	for _, item := range si.Items {
		if item.XMLName.Local == "b" && item.V == v {
			return
		}
	}
	si.Items = append(si.Items, xlsxSharedItem{XMLName: xml.Name{Local: "b"}, V: v})
}

// addErrorItem adds an error shared item for pivot table field if it does not
// exist.
func (si *xlsxSharedItems) addErrorItem(val string) {
	for _, item := range si.Items {
		if item.XMLName.Local == "e" && item.V == val {
			return
		}
	}
	si.Items = append(si.Items, xlsxSharedItem{XMLName: xml.Name{Local: "e"}, V: val})
}

// addStringItem adds a string shared item for pivot table field if it does not
// exist.
func (si *xlsxSharedItems) addStringItem(val string) {
	for _, item := range si.Items {
		if item.XMLName.Local == "s" && item.V == val {
			return
		}
	}
	si.Items = append(si.Items, xlsxSharedItem{XMLName: xml.Name{Local: "s"}, V: val})
	si.ContainsString = boolPtr(true)
}

// checkSelectedItems check whether the selected items exist in shared items.
func (si *xlsxSharedItems) checkSelectedItems(field string, selectedItems []string) error {
	for _, sharedItem := range selectedItems {
		found := false
		for _, item := range si.Items {
			switch item.XMLName.Local {
			case "m":
				if sharedItem == "" {
					found = true
				}
			case "b":
				if strings.EqualFold(item.V, sharedItem) {
					found = true
				}
			case "n", "e", "s", "d":
				if item.V == sharedItem {
					found = true
				}
			}
			if found {
				break
			}
		}
		if !found {
			return newPivotTableSelectedItemError(sharedItem, field)
		}
	}
	return nil
}

// addSharedItems generates shared items for pivot table for a column cells.
func (f *File) addSharedItems(sheet string, col, fromRow, toRow int) (xlsxSharedItems, error) {
	var si xlsxSharedItems
	for row := fromRow; row <= toRow; row++ {
		cell, err := CoordinatesToCellName(col, row)
		if err != nil {
			return si, err
		}
		val, err := f.CalcCellValue(sheet, cell)
		if err != nil && inStrSlice([]string{
			formulaErrorDIV,
			formulaErrorNAME,
			formulaErrorNA,
			formulaErrorNUM,
			formulaErrorVALUE,
			formulaErrorREF,
			formulaErrorNULL,
			formulaErrorSPILL,
			formulaErrorCALC,
			formulaErrorGETTINGDATA,
		}, err.Error(), true) != -1 {
			si.addErrorItem(err.Error())
			continue
		}
		if val == "" {
			si.addMissingItem()
			continue
		}
		cellType, _ := f.GetCellType(sheet, cell)
		switch cellType {
		case CellTypeUnset, CellTypeNumber:
			si.addNumberItem(val)
		case CellTypeBool:
			si.addBooleanItem(val)
		case CellTypeError:
			si.addErrorItem(val)
		case CellTypeFormula:
			if isNum, _, _ := isNumeric(val); isNum {
				si.addNumberItem(val)
				continue
			}
			si.addStringItem(val)
		case CellTypeInlineString, CellTypeSharedString:
			si.addStringItem(val)
		}
	}
	return si, nil
}

// buildPivotSharedItems builds shared items for pivot table.
func (f *File) buildPivotSharedItems(opts *PivotTableOptions, idx int, coordinates []int, field PivotTableField) error {
	var items []*xlsxItem
	sharedItems, err := f.addSharedItems(opts.pivotSheetName, coordinates[0]+idx, coordinates[1]+1, coordinates[3])
	if err != nil {
		return err
	}
	i := 0
	for _, item := range sharedItems.Items {
		if len(field.SelectedItems) == 0 {
			items = append(items, &xlsxItem{H: false, X: intPtr(i)})
			i++
			continue
		}
		switch item.XMLName.Local {
		case "m":
			items = append(items, &xlsxItem{H: inStrSlice(field.SelectedItems, "", true) == -1, X: intPtr(i)})
		case "b":
			items = append(items, &xlsxItem{H: inStrSlice(field.SelectedItems, item.V, false) == -1, X: intPtr(i)})
		case "n", "e", "s", "d":
			items = append(items, &xlsxItem{H: inStrSlice(field.SelectedItems, item.V, true) == -1, X: intPtr(i)})
		}
		i++
	}
	if err := sharedItems.checkSelectedItems(field.Data, field.SelectedItems); err != nil {
		return err
	}
	typesSet := map[string]bool{}
	numCount := 0
	for _, item := range sharedItems.Items {
		typesSet[item.XMLName.Local] = true
		if item.XMLName.Local == "n" {
			numCount++
		}
	}
	sharedItems.ContainsMixedTypes = len(typesSet) > 1
	if numCount == i {
		sharedItems.ContainsInteger = true
		sharedItems.ContainsString = boolPtr(false)
		sharedItems.ContainsSemiMixedTypes = boolPtr(false)
	}
	sharedItems.Count = i

	opts.items[field.Data] = items
	opts.sharedItems[field.Data] = sharedItems
	return nil
}

// addPivotSharedItems provides a function to prepare shared items for pivot
// table by given pivot table options, fields and coordinates.
func (f *File) addPivotSharedItems(opts *PivotTableOptions, coordinates []int, fieldsType string) error {
	var (
		fields                        []PivotTableField
		showValuesAsBaseFieldRequired bool
	)
	switch fieldsType {
	case "filters":
		fields = opts.Filter
	case "cols":
		fields = opts.Columns
	case "rows":
		fields = opts.Rows
	}
	fieldsIndex, err := f.getPivotFieldsIndex(fields, opts)
	if err != nil {
		return err
	}
	for _, field := range opts.Data {
		if _, ok := pivotTableShowValuesAsMap[field.ShowValuesAs.Type]; ok && pivotTableShowValuesAsBaseFieldRequiredMap[field.ShowValuesAs.Type] {
			showValuesAsBaseFieldRequired = true
		}
	}
	for i, field := range fields {
		if len(field.SelectedItems) > 0 || showValuesAsBaseFieldRequired {
			if opts.items == nil {
				opts.items = make(map[string][]*xlsxItem)
			}
			if opts.sharedItems == nil {
				opts.sharedItems = make(map[string]xlsxSharedItems)
			}
			if err = f.buildPivotSharedItems(opts, fieldsIndex[i], coordinates, field); err != nil {
				return err
			}
		}
	}
	return err
}

// addPivotCache provides a function to create a pivot cache by given properties.
func (f *File) addPivotCache(opts *PivotTableOptions) error {
	// validate data range
	dataSheet, coordinates, err := f.adjustRange(opts.pivotDataRange)
	if err != nil {
		return newPivotTableDataRangeError(err.Error())
	}
	order, err := f.getTableFieldsOrder(opts)
	if err != nil {
		return newPivotTableDataRangeError(err.Error())
	}
	topLeftCell, _ := CoordinatesToCellName(coordinates[0], coordinates[1])
	bottomRightCell, _ := CoordinatesToCellName(coordinates[2], coordinates[3])
	pc := xlsxPivotCacheDefinition{
		SaveData:              false,
		RefreshOnLoad:         true,
		CreatedVersion:        pivotTableVersion,
		RefreshedVersion:      pivotTableRefreshedVersion,
		MinRefreshableVersion: pivotTableVersion,
		CacheSource: &xlsxCacheSource{
			Type: "worksheet",
			WorksheetSource: &xlsxWorksheetSource{
				Ref:   topLeftCell + ":" + bottomRightCell,
				Sheet: dataSheet,
			},
		},
		CacheFields: &xlsxCacheFields{},
	}
	if opts.namedDataRange {
		pc.CacheSource.WorksheetSource = &xlsxWorksheetSource{Name: opts.DataRange}
	}
	if err = f.addPivotSharedItems(opts, coordinates, "filters"); err != nil {
		return err
	}
	if err = f.addPivotSharedItems(opts, coordinates, "cols"); err != nil {
		return err
	}
	if err = f.addPivotSharedItems(opts, coordinates, "rows"); err != nil {
		return err
	}
	for _, name := range order {
		si, ok := opts.sharedItems[name]
		if !ok {
			si = xlsxSharedItems{ContainsBlank: true, Items: []xlsxSharedItem{{XMLName: xml.Name{Local: "m"}}}}
		}
		pc.CacheFields.CacheField = append(pc.CacheFields.CacheField, &xlsxCacheField{
			Name:        name,
			SharedItems: &si,
		})
	}
	pc.CacheFields.Count = len(pc.CacheFields.CacheField)
	pivotCache, err := xml.Marshal(pc)
	f.saveFileList(opts.pivotCacheXML, pivotCache)
	return err
}

// addPivotTable provides a function to create a pivot table by given pivot
// table ID and properties.
func (f *File) addPivotTable(cacheID, pivotTableID int, opts *PivotTableOptions) error {
	// validate pivot table range
	_, coordinates, err := f.adjustRange(opts.PivotTableRange)
	if err != nil {
		return newPivotTableRangeError(err.Error())
	}

	topLeftCell, _ := CoordinatesToCellName(coordinates[0], coordinates[1])
	bottomRightCell, _ := CoordinatesToCellName(coordinates[2], coordinates[3])

	pivotTableStyle := func() string {
		if opts.PivotTableStyleName == "" {
			return "PivotStyleLight16"
		}
		return opts.PivotTableStyleName
	}
	pt := xlsxPivotTableDefinition{
		Name:                  opts.Name,
		CacheID:               cacheID,
		RowGrandTotals:        &opts.RowGrandTotals,
		ColGrandTotals:        &opts.ColGrandTotals,
		UpdatedVersion:        pivotTableRefreshedVersion,
		MinRefreshableVersion: pivotTableVersion,
		ShowDrill:             &opts.ShowDrill,
		UseAutoFormatting:     &opts.UseAutoFormatting,
		PageOverThenDown:      &opts.PageOverThenDown,
		MergeItem:             &opts.MergeItem,
		CreatedVersion:        pivotTableVersion,
		CompactData:           &opts.CompactData,
		GridDropZones:         opts.ClassicLayout,
		ShowError:             &opts.ShowError,
		FieldPrintTitles:      opts.FieldPrintTitles,
		ItemPrintTitles:       opts.ItemPrintTitles,
		DataCaption:           "Values",
		Location: &xlsxLocation{
			Ref:            topLeftCell + ":" + bottomRightCell,
			FirstDataCol:   1,
			FirstDataRow:   1,
			FirstHeaderRow: 1,
		},
		PivotFields: &xlsxPivotFields{},
		RowItems: &xlsxRowItems{
			Count: 1,
			I: []*xlsxI{
				{
					[]*xlsxX{{}},
				},
			},
		},
		ColItems: &xlsxColItems{
			Count: 1,
			I:     []*xlsxI{{}},
		},
		PivotTableStyleInfo: &xlsxPivotTableStyleInfo{
			Name:           pivotTableStyle(),
			ShowRowHeaders: opts.ShowRowHeaders,
			ShowColHeaders: opts.ShowColHeaders,
			ShowRowStripes: opts.ShowRowStripes,
			ShowColStripes: opts.ShowColStripes,
			ShowLastColumn: opts.ShowLastColumn,
		},
	}
	if pt.Name == "" {
		pt.Name = fmt.Sprintf("PivotTable%d", pivotTableID)
	}

	// set classic layout
	if opts.ClassicLayout {
		pt.Compact, pt.CompactData = boolPtr(false), boolPtr(false)
	}

	// pivot fields
	_ = f.addPivotFields(&pt, opts)

	// count pivot fields
	pt.PivotFields.Count = len(pt.PivotFields.PivotField)

	// data range has been checked
	_ = f.addPivotRowFields(&pt, opts)
	_ = f.addPivotColFields(&pt, opts)
	_ = f.addPivotPageFields(&pt, opts)
	if err = f.addPivotDataFields(&pt, opts); err != nil {
		return err
	}

	pivotTable, err := xml.Marshal(pt)
	f.saveFileList(opts.pivotTableXML, pivotTable)
	return err
}

// addPivotRowFields provides a method to add row fields for pivot table by
// given pivot table options.
func (f *File) addPivotRowFields(pt *xlsxPivotTableDefinition, opts *PivotTableOptions) error {
	// row fields
	rowFieldsIndex, err := f.getPivotFieldsIndex(opts.Rows, opts)
	if err != nil {
		return err
	}
	for _, fieldIdx := range rowFieldsIndex {
		if pt.RowFields == nil {
			pt.RowFields = &xlsxRowFields{}
		}
		pt.RowFields.Field = append(pt.RowFields.Field, &xlsxField{
			X: fieldIdx,
		})
	}

	// count row fields
	if pt.RowFields != nil {
		pt.RowFields.Count = len(pt.RowFields.Field)
	}
	return err
}

// addPivotPageFields provides a method to add page fields for pivot table by
// given pivot table options.
func (f *File) addPivotPageFields(pt *xlsxPivotTableDefinition, opts *PivotTableOptions) error {
	// page fields
	pageFieldsIndex, err := f.getPivotFieldsIndex(opts.Filter, opts)
	if err != nil {
		return err
	}
	pageFieldsName := f.getPivotTableFieldsName(opts.Filter)
	for idx, pageField := range pageFieldsIndex {
		if pt.PageFields == nil {
			pt.PageFields = &xlsxPageFields{}
		}
		pt.PageFields.PageField = append(pt.PageFields.PageField, &xlsxPageField{
			Name: pageFieldsName[idx],
			Fld:  pageField,
		})
	}

	// count page fields
	if pt.PageFields != nil {
		pt.PageFields.Count = len(pt.PageFields.PageField)
	}
	return err
}

// addPivotDataFields provides a method to add data fields for pivot table by
// given pivot table options.
func (f *File) addPivotDataFields(pt *xlsxPivotTableDefinition, opts *PivotTableOptions) error {
	// data fields
	dataFieldsIndex, err := f.getPivotFieldsIndex(opts.Data, opts)
	if err != nil {
		return err
	}
	order, _ := f.getTableFieldsOrder(opts)
	dataFieldsSubtotals := f.getPivotTableFieldsSubtotal(opts.Data)
	dataFieldsName := f.getPivotTableFieldsName(opts.Data)
	dataFieldsNumFmtID := f.getPivotTableFieldsNumFmtID(opts.Data)
	for idx, dataField := range dataFieldsIndex {
		if pt.DataFields == nil {
			pt.DataFields = &xlsxDataFields{}
		}
		dataField := &xlsxDataField{
			Name:     dataFieldsName[idx],
			Fld:      dataField,
			Subtotal: dataFieldsSubtotals[idx],
			NumFmtID: dataFieldsNumFmtID[idx],
		}
		if err = dataField.setPivotTableShowValuesAs(idx, order, opts); err != nil {
			return err
		}
		pt.DataFields.DataField = append(pt.DataFields.DataField, dataField)
	}
	// count data fields
	if pt.DataFields != nil {
		pt.DataFields.Count = len(pt.DataFields.DataField)
	}
	return err
}

// setPivotTableShowValuesAs provides a method to set show values as for pivot
// table data field by given pivot table options and data field index.
func (df *xlsxDataField) setPivotTableShowValuesAs(idx int, order []string, opts *PivotTableOptions) error {
	showValuesAsType := opts.Data[idx].ShowValuesAs.Type
	if showValuesAsType == PivotTableShowValuesAsNoCalculation {
		return nil
	}
	if _, ok := pivotTableShowValuesAsMap[showValuesAsType]; !ok {
		return ErrUnsupportedPivotTableShowValuesAsType
	}
	df.ShowDataAs = pivotTableShowValuesAsMap[showValuesAsType]
	if pivotTableShowValuesAsX14Map[showValuesAsType] {
		df.ShowDataAs = ""
		dataFieldBytes, _ := xml.Marshal(xlsxX14DataField{PivotShowAs: pivotTableShowValuesAsMap[showValuesAsType]})
		var decodeExtLst decodeExtLst
		ext := &xlsxExt{
			xmlns: []xml.Attr{{Name: xml.Name{Local: "xmlns:" + NameSpaceSpreadSheetX14.Name.Local}, Value: NameSpaceSpreadSheetX14.Value}},
			URI:   ExtURIPivotDataField, Content: string(dataFieldBytes),
		}
		decodeExtLst.Ext = append(decodeExtLst.Ext, ext)
		extLstBytes, _ := xml.Marshal(decodeExtLst)
		df.ExtLst = &xlsxExtLst{Ext: strings.TrimSuffix(strings.TrimPrefix(string(extLstBytes), "<extLst>"), "</extLst>")}
	}
	if pivotTableShowValuesAsBaseFieldRequiredMap[showValuesAsType] {
		baseField := opts.Data[idx].ShowValuesAs.BaseField
		if baseField == "" {
			return ErrPivotTableShowValuesAsBaseField
		}
		sharedItems, ok := opts.sharedItems[baseField]
		if !ok {
			return newPivotTableShowValuesAsBaseFieldError(baseField)
		}
		baseFieldIndex := inStrSlice(order, baseField, true)
		df.BaseField = intPtr(baseFieldIndex)
		if pivotTableShowValuesAsBaseItemRequiredMap[showValuesAsType] {
			if err := df.setPivotTableShowValuesAsBaseItem(baseField, opts.Data[idx].ShowValuesAs.BaseItem, &sharedItems); err != nil {
				return err
			}
		}
	}
	return nil
}

// setPivotTableShowValuesAsBaseItem provides a method to set base item for show
// value as in pivot table data field by given base field, base item and shared
// items.
func (df *xlsxDataField) setPivotTableShowValuesAsBaseItem(baseField, baseItem string, sharedItems *xlsxSharedItems) error {
	if baseItem == "" {
		return ErrPivotTableShowValuesAsBaseItem
	}
	if err := sharedItems.checkSelectedItems(baseField, []string{baseItem}); err != nil {
		return err
	}
	for i, item := range sharedItems.Items {
		switch item.XMLName.Local {
		case "b":
			if strings.EqualFold(item.V, baseItem) {
				df.BaseItem = intPtr(i)
			}
		case "n", "e", "s", "d":
			if item.V == baseItem {
				df.BaseItem = intPtr(i)
			}
		}
	}
	return nil
}

// inPivotTableField provides a method to check if an element is present in
// pivot table fields list, and return the index of its location, otherwise
// return -1.
func inPivotTableField(a []PivotTableField, x string) int {
	for idx, n := range a {
		if x == n.Data {
			return idx
		}
	}
	return -1
}

// addPivotColFields create pivot column fields by given pivot table
// definition and option.
func (f *File) addPivotColFields(pt *xlsxPivotTableDefinition, opts *PivotTableOptions) error {
	if len(opts.Columns) == 0 {
		if len(opts.Data) <= 1 {
			return nil
		}
		pt.ColFields = &xlsxColFields{}
		// in order to create pivot table in case there is no input from Columns
		pt.ColFields.Count = 1
		pt.ColFields.Field = append(pt.ColFields.Field, &xlsxField{
			X: -2,
		})
		return nil
	}

	pt.ColFields = &xlsxColFields{}

	// col fields
	colFieldsIndex, err := f.getPivotFieldsIndex(opts.Columns, opts)
	if err != nil {
		return err
	}
	for _, fieldIdx := range colFieldsIndex {
		pt.ColFields.Field = append(pt.ColFields.Field, &xlsxField{
			X: fieldIdx,
		})
	}

	// in order to create pivot in case there is many Columns and Data
	if len(opts.Data) > 1 {
		pt.ColFields.Field = append(pt.ColFields.Field, &xlsxField{
			X: -2,
		})
	}

	// count col fields
	pt.ColFields.Count = len(pt.ColFields.Field)
	return err
}

// setClassicLayout provides a method to set classic layout for pivot table by
// setting Compact and Outline to false.
func (fld *xlsxPivotField) setClassicLayout(classicLayout bool) {
	if classicLayout {
		fld.Compact, fld.Outline = boolPtr(false), boolPtr(false)
	}
}

// addPivotFields create pivot fields based on the column order of the first
// row in the data region by given pivot table definition and option.
func (f *File) addPivotFields(pt *xlsxPivotTableDefinition, opts *PivotTableOptions) error {
	order, err := f.getTableFieldsOrder(opts)
	if err != nil {
		return err
	}
	x := 0
	for _, name := range order {
		if inPivotTableField(opts.Rows, name) != -1 {
			rowOptions, ok := f.getPivotTableFieldOptions(name, opts.Rows)
			items := opts.items[name]
			if ok && rowOptions.DefaultSubtotal {
				items = append(items, &xlsxItem{T: "default"})
			}
			if len(items) == 0 {
				items = append(items, &xlsxItem{X: &x})
			}
			fld := &xlsxPivotField{
				Name:                         f.getPivotTableFieldName(name, opts.Rows),
				Axis:                         "axisRow",
				DataField:                    inPivotTableField(opts.Data, name) != -1,
				Compact:                      &rowOptions.Compact,
				Outline:                      &rowOptions.Outline,
				MultipleItemSelectionAllowed: len(opts.items[name]) != 0,
				ShowAll:                      rowOptions.ShowAll,
				InsertBlankRow:               rowOptions.InsertBlankRow,
				DefaultSubtotal:              &rowOptions.DefaultSubtotal,
				Items: &xlsxItems{
					Count: len(items),
					Item:  items,
				},
			}
			fld.setClassicLayout(opts.ClassicLayout)
			pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, fld)
			continue
		}
		if inPivotTableField(opts.Filter, name) != -1 {
			items := append(opts.items[name], &xlsxItem{T: "default"})
			fld := &xlsxPivotField{
				Axis:                         "axisPage",
				DataField:                    inPivotTableField(opts.Data, name) != -1,
				MultipleItemSelectionAllowed: len(opts.items[name]) != 0,
				Name:                         f.getPivotTableFieldName(name, opts.Columns),
				Items: &xlsxItems{
					Count: len(items),
					Item:  items,
				},
			}
			fld.setClassicLayout(opts.ClassicLayout)
			pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, fld)
			continue
		}
		if inPivotTableField(opts.Columns, name) != -1 {
			columnOptions, ok := f.getPivotTableFieldOptions(name, opts.Columns)
			items := opts.items[name]
			if ok && columnOptions.DefaultSubtotal {
				items = append(items, &xlsxItem{T: "default"})
			}
			if len(items) == 0 {
				items = append(items, &xlsxItem{X: &x})
			}
			fld := &xlsxPivotField{
				Name:                         f.getPivotTableFieldName(name, opts.Columns),
				Axis:                         "axisCol",
				DataField:                    inPivotTableField(opts.Data, name) != -1,
				Compact:                      &columnOptions.Compact,
				MultipleItemSelectionAllowed: len(opts.items[name]) != 0,
				Outline:                      &columnOptions.Outline,
				ShowAll:                      columnOptions.ShowAll,
				InsertBlankRow:               columnOptions.InsertBlankRow,
				DefaultSubtotal:              &columnOptions.DefaultSubtotal,
				Items: &xlsxItems{
					Count: len(items),
					Item:  items,
				},
			}
			fld.setClassicLayout(opts.ClassicLayout)
			pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, fld)
			continue
		}
		if inPivotTableField(opts.Data, name) != -1 {
			fld := &xlsxPivotField{
				DataField: true,
			}
			fld.setClassicLayout(opts.ClassicLayout)
			pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, fld)
			continue
		}
		fld := &xlsxPivotField{}
		fld.setClassicLayout(opts.ClassicLayout)
		pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, fld)
	}
	return err
}

// countPivotTables provides a function to get pivot table files count storage
// in the folder xl/pivotTables.
func (f *File) countPivotTables() int {
	count := 0
	f.Pkg.Range(func(k, v interface{}) bool {
		if strings.Contains(k.(string), "xl/pivotTables/pivotTable") {
			count++
		}
		return true
	})
	return count
}

// countPivotCache provides a function to get pivot table cache definition files
// count storage in the folder xl/pivotCache.
func (f *File) countPivotCache() int {
	count := 0
	f.Pkg.Range(func(k, v interface{}) bool {
		if strings.Contains(k.(string), "xl/pivotCache/pivotCacheDefinition") {
			count++
		}
		return true
	})
	return count
}

// getPivotFieldsIndex convert the column of the first row in the data region
// to a sequential index by given fields and pivot option.
func (f *File) getPivotFieldsIndex(fields []PivotTableField, opts *PivotTableOptions) ([]int, error) {
	var pivotFieldsIndex []int
	orders, err := f.getTableFieldsOrder(opts)
	if err != nil {
		return pivotFieldsIndex, err
	}
	for _, field := range fields {
		if pos := inStrSlice(orders, field.Data, true); pos != -1 {
			pivotFieldsIndex = append(pivotFieldsIndex, pos)
		}
	}
	return pivotFieldsIndex, nil
}

// getPivotTableFieldsSubtotal prepare fields subtotal by given pivot table fields.
func (f *File) getPivotTableFieldsSubtotal(fields []PivotTableField) []string {
	field := make([]string, len(fields))
	enums := []string{"average", "count", "countNums", "max", "min", "product", "stdDev", "stdDevp", "sum", "var", "varp"}
	inEnums := func(enums []string, val string) string {
		for _, enum := range enums {
			if strings.EqualFold(enum, val) {
				return enum
			}
		}
		return "sum"
	}
	for idx, fld := range fields {
		field[idx] = inEnums(enums, fld.Subtotal)
	}
	return field
}

// getPivotTableFieldsName prepare fields name list by given pivot table
// fields.
func (f *File) getPivotTableFieldsName(fields []PivotTableField) []string {
	field := make([]string, len(fields))
	for idx, fld := range fields {
		if countUTF16String(fld.Name) > MaxFieldLength {
			field[idx] = truncateUTF16Units(fld.Name, MaxFieldLength)
			continue
		}
		field[idx] = fld.Name
	}
	return field
}

// getPivotTableFieldName prepare field name by given pivot table fields.
func (f *File) getPivotTableFieldName(name string, fields []PivotTableField) string {
	fieldsName := f.getPivotTableFieldsName(fields)
	for idx, field := range fields {
		if field.Data == name {
			return fieldsName[idx]
		}
	}
	return ""
}

// getPivotTableFieldsNumFmtID prepare fields number format ID by given pivot
// table fields.
func (f *File) getPivotTableFieldsNumFmtID(fields []PivotTableField) []int {
	field := make([]int, len(fields))
	for idx, fld := range fields {
		if _, ok := builtInNumFmt[fld.NumFmt]; ok {
			field[idx] = fld.NumFmt
			continue
		}
		if (27 <= fld.NumFmt && fld.NumFmt <= 36) || (50 <= fld.NumFmt && fld.NumFmt <= 81) {
			field[idx] = fld.NumFmt
		}
	}
	return field
}

// getPivotTableFieldOptions return options for specific field by given field name.
func (f *File) getPivotTableFieldOptions(name string, fields []PivotTableField) (options PivotTableField, ok bool) {
	for _, field := range fields {
		if field.Data == name {
			options, ok = field, true
			return
		}
	}
	return
}

// addWorkbookPivotCache add the association ID of the pivot cache in workbook.xml.
func (f *File) addWorkbookPivotCache(RID int) int {
	wb, _ := f.workbookReader()
	if wb.PivotCaches == nil {
		wb.PivotCaches = &xlsxPivotCaches{}
	}
	cacheID := 1
	for _, pivotCache := range wb.PivotCaches.PivotCache {
		if pivotCache.CacheID > cacheID {
			cacheID = pivotCache.CacheID
		}
	}
	cacheID++
	wb.PivotCaches.PivotCache = append(wb.PivotCaches.PivotCache, xlsxPivotCache{
		CacheID: cacheID,
		RID:     fmt.Sprintf("rId%d", RID),
	})
	return cacheID
}

// GetPivotTables returns all pivot table definitions in a worksheet by given
// worksheet name. Currently only support get pivot table cache with worksheet
// source type, and doesn't support source types: external, consolidation
// and scenario.
func (f *File) GetPivotTables(sheet string) ([]PivotTableOptions, error) {
	var pivotTables []PivotTableOptions
	name, ok := f.getSheetXMLPath(sheet)
	if !ok {
		return pivotTables, ErrSheetNotExist{sheet}
	}
	rels := "xl/worksheets/_rels/" + strings.TrimPrefix(name, "xl/worksheets/") + ".rels"
	sheetRels, err := f.relsReader(rels)
	if err != nil {
		return pivotTables, err
	}
	if sheetRels == nil {
		sheetRels = &xlsxRelationships{}
	}
	for _, v := range sheetRels.Relationships {
		if v.Type == SourceRelationshipPivotTable {
			pivotTableXML := strings.ReplaceAll(v.Target, "..", "xl")
			pivotCacheRels := "xl/pivotTables/_rels/" + filepath.Base(v.Target) + ".rels"
			pivotTable, err := f.getPivotTable(sheet, pivotTableXML, pivotCacheRels)
			if err != nil {
				return pivotTables, err
			}
			pivotTables = append(pivotTables, pivotTable)
		}
	}
	return pivotTables, nil
}

// getPivotTableDataRange checking given if data range is a cell reference or
// named reference (defined name or table name), and set pivot table data range.
func (f *File) getPivotTableDataRange(opts *PivotTableOptions) error {
	if opts.DataRange == "" {
		return newPivotTableDataRangeError(ErrParameterRequired.Error())
	}
	if opts.pivotDataRange != "" {
		return nil
	}
	if strings.Contains(opts.DataRange, "!") {
		opts.pivotDataRange = opts.DataRange
		return nil
	}
	tbls, err := f.getTables()
	if err != nil {
		return err
	}
	for sheetName, tables := range tbls {
		for _, table := range tables {
			if table.Name == opts.DataRange {
				opts.pivotDataRange, opts.namedDataRange = fmt.Sprintf("%s!%s", sheetName, table.Range), true
				return err
			}
		}
	}
	if !opts.namedDataRange {
		opts.pivotDataRange = f.getDefinedNameRefTo(opts.DataRange, opts.pivotSheetName)
		if opts.pivotDataRange != "" {
			opts.namedDataRange = true
			return nil
		}
	}
	return newPivotTableDataRangeError(ErrParameterInvalid.Error())
}

// getPivotTable provides a function to get a pivot table definition by given
// worksheet name, pivot table XML path and pivot cache relationship XML path.
func (f *File) getPivotTable(sheet, pivotTableXML, pivotCacheRels string) (PivotTableOptions, error) {
	var opts PivotTableOptions
	rels, err := f.relsReader(pivotCacheRels)
	if err != nil {
		return opts, err
	}
	var pivotCacheXML string
	for _, v := range rels.Relationships {
		if v.Type == SourceRelationshipPivotCache {
			pivotCacheXML = strings.ReplaceAll(v.Target, "..", "xl")
			break
		}
	}
	pc, err := f.pivotCacheReader(pivotCacheXML)
	if err != nil {
		return opts, err
	}
	pt, err := f.pivotTableReader(pivotTableXML)
	if err != nil {
		return opts, err
	}
	if pc.CacheSource.WorksheetSource == nil {
		return opts, newUnsupportedPivotCacheSourceType(pc.CacheSource.Type)
	}
	opts = PivotTableOptions{
		pivotTableXML:    pivotTableXML,
		pivotCacheXML:    pivotCacheXML,
		pivotSheetName:   sheet,
		DataRange:        fmt.Sprintf("%s!%s", pc.CacheSource.WorksheetSource.Sheet, pc.CacheSource.WorksheetSource.Ref),
		PivotTableRange:  fmt.Sprintf("%s!%s", sheet, pt.Location.Ref),
		Name:             pt.Name,
		ClassicLayout:    pt.GridDropZones,
		FieldPrintTitles: pt.FieldPrintTitles,
		ItemPrintTitles:  pt.ItemPrintTitles,
	}
	if pc.CacheSource.WorksheetSource.Name != "" {
		opts.DataRange = pc.CacheSource.WorksheetSource.Name
		_ = f.getPivotTableDataRange(&opts)
	}
	setPtrFieldsVal([]string{
		"RowGrandTotals", "ColGrandTotals", "ShowDrill",
		"UseAutoFormatting", "PageOverThenDown", "MergeItem", "CompactData", "ShowError",
	}, reflect.ValueOf(*pt), reflect.ValueOf(&opts).Elem())
	if si := pt.PivotTableStyleInfo; si != nil {
		opts.ShowRowHeaders = si.ShowRowHeaders
		opts.ShowColHeaders = si.ShowColHeaders
		opts.ShowRowStripes = si.ShowRowStripes
		opts.ShowColStripes = si.ShowColStripes
		opts.ShowLastColumn = si.ShowLastColumn
		opts.PivotTableStyleName = si.Name
	}
	if err = f.getPivotTableDataRange(&opts); err != nil {
		return opts, err
	}
	f.extractPivotTableFields(pt, pc, &opts)
	return opts, err
}

// getPivotCacheFieldsName returns pivot table fields name list by order from
// pivot cache fields.
func (pc *xlsxPivotCacheDefinition) getPivotCacheFieldsName() []string {
	var order []string
	if pc.CacheFields != nil {
		for _, cf := range pc.CacheFields.CacheField {
			if cf != nil {
				order = append(order, cf.Name)
			}
		}
	}
	return order
}

// pivotTableReader provides a function to get the pointer to the structure
// after deserialization of xl/pivotTables/pivotTable%d.xml.
func (f *File) pivotTableReader(path string) (*xlsxPivotTableDefinition, error) {
	content, ok := f.Pkg.Load(path)
	pivotTable := &xlsxPivotTableDefinition{}
	if ok && content != nil {
		if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
			Decode(pivotTable); err != nil && err != io.EOF {
			return nil, err
		}
	}
	return pivotTable, nil
}

// pivotCacheReader provides a function to get the pointer to the structure
// after deserialization of xl/pivotCache/pivotCacheDefinition%d.xml.
func (f *File) pivotCacheReader(path string) (*xlsxPivotCacheDefinition, error) {
	content, ok := f.Pkg.Load(path)
	pivotCache := &xlsxPivotCacheDefinition{}
	if ok && content != nil {
		if err := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(content.([]byte)))).
			Decode(pivotCache); err != nil && err != io.EOF {
			return nil, err
		}
	}
	return pivotCache, nil
}

// extractPivotTableFields provides a function to extract all pivot table fields
// settings by given pivot table fields.
func (f *File) extractPivotTableFields(pt *xlsxPivotTableDefinition, pc *xlsxPivotCacheDefinition, opts *PivotTableOptions) {
	order := pc.getPivotCacheFieldsName()
	for fieldIdx, field := range pt.PivotFields.PivotField {
		if field.Axis == "axisRow" {
			opts.Rows = append(opts.Rows, pc.extractPivotTableField(order[fieldIdx], field))
		}
		if field.Axis == "axisCol" {
			opts.Columns = append(opts.Columns, pc.extractPivotTableField(order[fieldIdx], field))
		}
		if field.Axis == "axisPage" {
			opts.Filter = append(opts.Filter, pc.extractPivotTableField(order[fieldIdx], field))
		}
	}
	if pt.DataFields != nil {
		for _, field := range pt.DataFields.DataField {
			dataField := PivotTableField{
				Data:     order[field.Fld],
				Name:     field.Name,
				Subtotal: cases.Title(language.English, cases.NoLower).String(field.Subtotal),
				NumFmt:   field.NumFmtID,
			}
			if field.ShowDataAs != "" || field.ExtLst != nil {
				f.extractPivotTableShowValuesAs(pc, field, &dataField)
			}
			opts.Data = append(opts.Data, dataField)
		}
	}
}

// extractPivotTableShowValuesAs provides a function to extract show values as
// settings for pivot table data field.
func (f *File) extractPivotTableShowValuesAs(pc *xlsxPivotCacheDefinition, df *xlsxDataField, dataField *PivotTableField) {
	order := pc.getPivotCacheFieldsName()
	showDataAs := df.ShowDataAs
	if df.ExtLst != nil {
		ext := new(xlsxExt)
		_ = f.xmlNewDecoder(strings.NewReader(df.ExtLst.Ext)).Decode(ext)
		field := new(decodeX14DataField)
		_ = f.xmlNewDecoder(strings.NewReader(ext.Content)).Decode(field)
		showDataAs = field.PivotShowAs
	}
	for k, v := range pivotTableShowValuesAsMap {
		if v == showDataAs {
			dataField.ShowValuesAs.Type = k
			break
		}
	}
	if df.BaseField != nil {
		baseFieldIdx := *df.BaseField
		if baseFieldIdx < len(order) {
			dataField.ShowValuesAs.BaseField = order[baseFieldIdx]
		}
	}
	if df.BaseItem == nil {
		return
	}
	for _, cacheField := range pc.CacheFields.CacheField {
		if cacheField.Name == dataField.ShowValuesAs.BaseField {
			if cacheField.SharedItems != nil {
				if *df.BaseItem < len(cacheField.SharedItems.Items) {
					dataField.ShowValuesAs.BaseItem = cacheField.SharedItems.Items[*df.BaseItem].V
				}
			}
		}
	}
}

// extractPivotTableField provides a function to extract pivot table field
// settings by given pivot table fields.
func (pc *xlsxPivotCacheDefinition) extractPivotTableField(data string, fld *xlsxPivotField) PivotTableField {
	pivotTableField := PivotTableField{
		Data:           data,
		ShowAll:        fld.ShowAll,
		InsertBlankRow: fld.InsertBlankRow,
	}
	if pc.CacheFields != nil && fld.Items != nil {
		for _, item := range fld.Items.Item {
			if !item.H && item.X != nil {
				idx := *item.X
				for _, field := range pc.CacheFields.CacheField {
					if field != nil && field.Name == data && field.SharedItems != nil && field.SharedItems.Count > 0 {
						if idx < len(field.SharedItems.Items) {
							pivotTableField.SelectedItems = append(pivotTableField.SelectedItems, field.SharedItems.Items[idx].V)
						}
					}
				}
			}
		}
	}
	setPtrFieldsVal([]string{"Compact", "Outline", "DefaultSubtotal"},
		reflect.ValueOf(*fld), reflect.ValueOf(&pivotTableField).Elem())
	return pivotTableField
}

// genPivotCacheDefinitionID generates a unique pivot table cache definition ID.
func (f *File) genPivotCacheDefinitionID() int {
	var (
		ID                            int
		decodeExtLst                  = new(decodeExtLst)
		decodeX14PivotCacheDefinition = new(decodeX14PivotCacheDefinition)
	)
	f.Pkg.Range(func(k, v interface{}) bool {
		if strings.Contains(k.(string), "xl/pivotCache/pivotCacheDefinition") {
			pc, err := f.pivotCacheReader(k.(string))
			if err != nil {
				return true
			}
			if pc.ExtLst != nil {
				_ = f.xmlNewDecoder(strings.NewReader("<extLst>" + pc.ExtLst.Ext + "</extLst>")).Decode(decodeExtLst)
				for _, ext := range decodeExtLst.Ext {
					if ext.URI == ExtURIPivotCacheDefinition {
						_ = f.xmlNewDecoder(strings.NewReader(ext.Content)).Decode(decodeX14PivotCacheDefinition)
						if ID < decodeX14PivotCacheDefinition.PivotCacheID {
							ID = decodeX14PivotCacheDefinition.PivotCacheID
						}
					}
				}
			}
		}
		return true
	})
	return ID + 1
}

// deleteWorkbookPivotCache remove workbook pivot cache and pivot cache
// relationships.
func (f *File) deleteWorkbookPivotCache(opt PivotTableOptions) error {
	rID, err := f.deleteWorkbookRels(SourceRelationshipPivotCache, strings.TrimPrefix(strings.TrimPrefix(opt.pivotCacheXML, "/"), "xl/"))
	if err != nil {
		return err
	}
	wb, err := f.workbookReader()
	if err != nil {
		return err
	}
	if wb.PivotCaches != nil {
		for i, pivotCache := range wb.PivotCaches.PivotCache {
			if pivotCache.RID == rID {
				wb.PivotCaches.PivotCache = append(wb.PivotCaches.PivotCache[:i], wb.PivotCaches.PivotCache[i+1:]...)
			}
		}
		if len(wb.PivotCaches.PivotCache) == 0 {
			wb.PivotCaches = nil
		}
	}
	return err
}

// DeletePivotTable delete a pivot table by giving the worksheet name and pivot
// table name. Note that this function does not clean cell values in the pivot
// table range.
func (f *File) DeletePivotTable(sheet, name string) error {
	sheetXML, ok := f.getSheetXMLPath(sheet)
	if !ok {
		return ErrSheetNotExist{sheet}
	}
	rels := "xl/worksheets/_rels/" + strings.TrimPrefix(sheetXML, "xl/worksheets/") + ".rels"
	sheetRels, err := f.relsReader(rels)
	if err != nil {
		return err
	}
	if sheetRels == nil {
		sheetRels = &xlsxRelationships{}
	}
	opts, err := f.GetPivotTables(sheet)
	if err != nil {
		return err
	}
	f.clearCalcCache()
	pivotTableCaches := map[string]int{}
	pivotTables, _ := f.getPivotTables()
	for _, sheetPivotTables := range pivotTables {
		for _, sheetPivotTable := range sheetPivotTables {
			pivotTableCaches[sheetPivotTable.pivotCacheXML]++
		}
	}
	for _, v := range sheetRels.Relationships {
		for _, opt := range opts {
			if v.Type == SourceRelationshipPivotTable {
				pivotTableXML := strings.ReplaceAll(v.Target, "..", "xl")
				if opt.Name == name && opt.pivotTableXML == pivotTableXML {
					if pivotTableCaches[opt.pivotCacheXML] == 1 {
						err = f.deleteWorkbookPivotCache(opt)
					}
					f.deleteSheetRelationships(sheet, v.ID)
					return err
				}
			}
		}
	}
	return newNoExistTableError(name)
}

// getPivotTables provides a function to get all pivot tables in a workbook.
func (f *File) getPivotTables() (map[string][]PivotTableOptions, error) {
	pivotTables := map[string][]PivotTableOptions{}
	for _, sheetName := range f.GetSheetList() {
		pts, err := f.GetPivotTables(sheetName)
		e := ErrSheetNotExist{sheetName}
		if err != nil && err.Error() != newNotWorksheetError(sheetName).Error() && err.Error() != e.Error() {
			return pivotTables, err
		}
		pivotTables[sheetName] = append(pivotTables[sheetName], pts...)
	}
	return pivotTables, nil
}