summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go13
-rw-r--r--col.go4
-rw-r--r--pivotTable.go38
-rw-r--r--pivotTable_test.go16
4 files changed, 51 insertions, 20 deletions
diff --git a/calc.go b/calc.go
index 8ceceec..934ae43 100644
--- a/calc.go
+++ b/calc.go
@@ -955,16 +955,23 @@ func isOperatorPrefixToken(token efp.Token) bool {
// getDefinedNameRefTo convert defined name to reference range.
func (f *File) getDefinedNameRefTo(definedNameName string, currentSheet string) (refTo string) {
+ var workbookRefTo, worksheetRefTo string
for _, definedName := range f.GetDefinedName() {
if definedName.Name == definedNameName {
- refTo = definedName.RefersTo
// worksheet scope takes precedence over scope workbook when both definedNames exist
+ if definedName.Scope == "Workbook" {
+ workbookRefTo = definedName.RefersTo
+ }
if definedName.Scope == currentSheet {
- break
+ worksheetRefTo = definedName.RefersTo
}
}
}
- return refTo
+ refTo = workbookRefTo
+ if worksheetRefTo != "" {
+ refTo = worksheetRefTo
+ }
+ return
}
// parseToken parse basic arithmetic operator priority and evaluate based on
diff --git a/col.go b/col.go
index 2e3190c..2fd90b2 100644
--- a/col.go
+++ b/col.go
@@ -604,7 +604,7 @@ func (f *File) positionObjectPixels(sheet string, col, row, x1, y1, width, heigh
}
// getColWidth provides a function to get column width in pixels by given
-// sheet name and column index.
+// sheet name and column number.
func (f *File) getColWidth(sheet string, col int) int {
xlsx, _ := f.workSheetReader(sheet)
if xlsx.Cols != nil {
@@ -623,7 +623,7 @@ func (f *File) getColWidth(sheet string, col int) int {
}
// GetColWidth provides a function to get column width by given worksheet name
-// and column index.
+// and column name.
func (f *File) GetColWidth(sheet, col string) (float64, error) {
colNum, err := ColumnNameToNumber(col)
if err != nil {
diff --git a/pivotTable.go b/pivotTable.go
index 11c2b31..3d93260 100644
--- a/pivotTable.go
+++ b/pivotTable.go
@@ -21,6 +21,7 @@ import (
// PivotTableOption directly maps the format settings of the pivot table.
type PivotTableOption struct {
+ pivotTableSheetName string
DataRange string
PivotTableRange string
Rows []PivotTableField
@@ -164,14 +165,19 @@ func (f *File) parseFormatPivotTableSet(opt *PivotTableOption) (*xlsxWorksheet,
if opt == nil {
return nil, "", errors.New("parameter is required")
}
- dataSheetName, _, err := f.adjustRange(opt.DataRange)
- if err != nil {
- return nil, "", fmt.Errorf("parameter 'DataRange' parsing error: %s", err.Error())
- }
pivotTableSheetName, _, err := f.adjustRange(opt.PivotTableRange)
if err != nil {
return nil, "", fmt.Errorf("parameter 'PivotTableRange' parsing error: %s", err.Error())
}
+ opt.pivotTableSheetName = pivotTableSheetName
+ dataRange := f.getDefinedNameRefTo(opt.DataRange, pivotTableSheetName)
+ if dataRange == "" {
+ dataRange = opt.DataRange
+ }
+ dataSheetName, _, err := f.adjustRange(dataRange)
+ if err != nil {
+ return nil, "", fmt.Errorf("parameter 'DataRange' parsing error: %s", err.Error())
+ }
dataSheet, err := f.workSheetReader(dataSheetName)
if err != nil {
return dataSheet, "", err
@@ -215,8 +221,12 @@ func (f *File) adjustRange(rangeStr string) (string, []int, error) {
// getPivotFieldsOrder provides a function to get order list of pivot table
// fields.
-func (f *File) getPivotFieldsOrder(dataRange string) ([]string, error) {
+func (f *File) getPivotFieldsOrder(opt *PivotTableOption) ([]string, error) {
order := []string{}
+ dataRange := f.getDefinedNameRefTo(opt.DataRange, opt.pivotTableSheetName)
+ if dataRange == "" {
+ dataRange = opt.DataRange
+ }
dataSheet, coordinates, err := f.adjustRange(dataRange)
if err != nil {
return order, fmt.Errorf("parameter 'DataRange' parsing error: %s", err.Error())
@@ -235,12 +245,18 @@ func (f *File) getPivotFieldsOrder(dataRange string) ([]string, error) {
// addPivotCache provides a function to create a pivot cache by given properties.
func (f *File) addPivotCache(pivotCacheID int, pivotCacheXML string, opt *PivotTableOption, ws *xlsxWorksheet) error {
// validate data range
- dataSheet, coordinates, err := f.adjustRange(opt.DataRange)
+ definedNameRef := true
+ dataRange := f.getDefinedNameRefTo(opt.DataRange, opt.pivotTableSheetName)
+ if dataRange == "" {
+ definedNameRef = false
+ dataRange = opt.DataRange
+ }
+ dataSheet, coordinates, err := f.adjustRange(dataRange)
if err != nil {
return fmt.Errorf("parameter 'DataRange' parsing error: %s", err.Error())
}
// data range has been checked
- order, _ := f.getPivotFieldsOrder(opt.DataRange)
+ order, _ := f.getPivotFieldsOrder(opt)
hcell, _ := CoordinatesToCellName(coordinates[0], coordinates[1])
vcell, _ := CoordinatesToCellName(coordinates[2], coordinates[3])
pc := xlsxPivotCacheDefinition{
@@ -258,7 +274,9 @@ func (f *File) addPivotCache(pivotCacheID int, pivotCacheXML string, opt *PivotT
},
CacheFields: &xlsxCacheFields{},
}
-
+ if definedNameRef {
+ pc.CacheSource.WorksheetSource = &xlsxWorksheetSource{Name: opt.DataRange}
+ }
for _, name := range order {
defaultRowsSubtotal, rowOk := f.getPivotTableFieldNameDefaultSubtotal(name, opt.Rows)
defaultColumnsSubtotal, colOk := f.getPivotTableFieldNameDefaultSubtotal(name, opt.Columns)
@@ -509,7 +527,7 @@ func (f *File) addPivotColFields(pt *xlsxPivotTableDefinition, opt *PivotTableOp
// 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, opt *PivotTableOption) error {
- order, err := f.getPivotFieldsOrder(opt.DataRange)
+ order, err := f.getPivotFieldsOrder(opt)
if err != nil {
return err
}
@@ -606,7 +624,7 @@ func (f *File) countPivotCache() int {
// to a sequential index by given fields and pivot option.
func (f *File) getPivotFieldsIndex(fields []PivotTableField, opt *PivotTableOption) ([]int, error) {
pivotFieldsIndex := []int{}
- orders, err := f.getPivotFieldsOrder(opt.DataRange)
+ orders, err := f.getPivotFieldsOrder(opt)
if err != nil {
return pivotFieldsIndex, err
}
diff --git a/pivotTable_test.go b/pivotTable_test.go
index 40d58d4..7098b3a 100644
--- a/pivotTable_test.go
+++ b/pivotTable_test.go
@@ -136,10 +136,16 @@ func TestAddPivotTable(t *testing.T) {
ShowColHeaders: true,
ShowLastColumn: true,
}))
- //Test Pivot table with many data, many rows, many cols
+ // Create pivot table with many data, many rows, many cols and defined name
+ f.SetDefinedName(&DefinedName{
+ Name: "dataRange",
+ RefersTo: "Sheet1!$A$1:$E$31",
+ Comment: "Pivot Table Data Range",
+ Scope: "Sheet2",
+ })
assert.NoError(t, f.AddPivotTable(&PivotTableOption{
- DataRange: "Sheet1!$A$1:$E$31",
- PivotTableRange: "Sheet2!$A$56:$AG$90",
+ DataRange: "dataRange",
+ PivotTableRange: "Sheet2!$A$57:$AJ$91",
Rows: []PivotTableField{{Data: "Month", DefaultSubtotal: true}, {Data: "Year"}},
Columns: []PivotTableField{{Data: "Region", DefaultSubtotal: true}, {Data: "Type"}},
Data: []PivotTableField{{Data: "Sales", Subtotal: "Sum", Name: "Sum of Sales"}, {Data: "Sales", Subtotal: "Average", Name: "Average of Sales"}},
@@ -223,7 +229,7 @@ func TestAddPivotTable(t *testing.T) {
_, _, err := f.adjustRange("")
assert.EqualError(t, err, "parameter is required")
// Test get pivot fields order with empty data range
- _, err = f.getPivotFieldsOrder("")
+ _, err = f.getPivotFieldsOrder(&PivotTableOption{})
assert.EqualError(t, err, `parameter 'DataRange' parsing error: parameter is required`)
// Test add pivot cache with empty data range
assert.EqualError(t, f.addPivotCache(0, "", &PivotTableOption{}, nil), "parameter 'DataRange' parsing error: parameter is required")
@@ -288,7 +294,7 @@ func TestAddPivotColFields(t *testing.T) {
func TestGetPivotFieldsOrder(t *testing.T) {
f := NewFile()
// Test get pivot fields order with not exist worksheet
- _, err := f.getPivotFieldsOrder("SheetN!$A$1:$E$31")
+ _, err := f.getPivotFieldsOrder(&PivotTableOption{DataRange: "SheetN!$A$1:$E$31"})
assert.EqualError(t, err, "sheet SheetN is not exist")
}
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 10:19:22 +0000