From 6dcb7013eeeb8902be97c564c7a5a05dddcb06b8 Mon Sep 17 00:00:00 2001 From: xuri Date: Fri, 21 Feb 2020 23:07:43 +0800 Subject: Resolve #582, support to set date field subtotal and names for pivot table - typo fixed and update do.dev badge in the README. --- README.md | 2 +- README_zh.md | 2 +- pivotTable.go | 58 ++++++++++++++++++++++++++++++++++++++++++++++-------- pivotTable_test.go | 25 +++++++++++++++++++++++ 4 files changed, 77 insertions(+), 10 deletions(-) diff --git a/README.md b/README.md index c81efd1..fa1dda9 100644 --- a/README.md +++ b/README.md @@ -4,7 +4,7 @@ Build Status Code Coverage Go Report Card - GoDoc + go.dev Licenses Donate

diff --git a/README_zh.md b/README_zh.md index f75eec5..44ab9b5 100644 --- a/README_zh.md +++ b/README_zh.md @@ -4,7 +4,7 @@ Build Status Code Coverage Go Report Card - GoDoc + go.dev Licenses Donate

diff --git a/pivotTable.go b/pivotTable.go index ee0d94e..696dfe7 100644 --- a/pivotTable.go +++ b/pivotTable.go @@ -20,6 +20,8 @@ import ( // PivotTableOption directly maps the format settings of the pivot table. type PivotTableOption struct { DataRange string + DataSubtotal string + DataFieldName string PivotTableRange string Rows []string Columns []string @@ -28,9 +30,29 @@ type PivotTableOption struct { } // AddPivotTable provides the method to add pivot table by given pivot table -// options. For example, create a pivot table on the Sheet1!$G$2:$M$34 area -// with the region Sheet1!$A$1:$E$31 as the data source, summarize by sum for -// sales: +// options. +// +// DataSubtotal 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 +// +// DataFieldName specifies the name of the data field. Maximum 255 characters +// are allowed in data field name, excess characters will be truncated. +// +// For example, create a pivot table on the Sheet1!$G$2:$M$34 area with the +// region Sheet1!$A$1:$E$31 as the data source, summarize by sum for sales: // // package main // @@ -62,6 +84,8 @@ type PivotTableOption struct { // Rows: []string{"Month", "Year"}, // Columns: []string{"Type"}, // Data: []string{"Sales"}, +// DataSubtotal: "Sum", +// DataFieldName: "Summarize as Sum", // }); err != nil { // fmt.Println(err) // } @@ -278,9 +302,9 @@ func (f *File) addPivotTable(cacheID, pivotTableID int, pivotTableXML string, op if err != nil { return err } - for _, filedIdx := range rowFieldsIndex { + for _, fieldIdx := range rowFieldsIndex { pt.RowFields.Field = append(pt.RowFields.Field, &xlsxField{ - X: filedIdx, + X: fieldIdx, }) } @@ -297,9 +321,15 @@ func (f *File) addPivotTable(cacheID, pivotTableID int, pivotTableXML string, op if err != nil { return err } + dataFieldName := opt.DataFieldName + if len(dataFieldName) > 255 { + dataFieldName = dataFieldName[0:255] + } for _, dataField := range dataFieldsIndex { pt.DataFields.DataField = append(pt.DataFields.DataField, &xlsxDataField{ - Fld: dataField, + Name: dataFieldName, + Fld: dataField, + Subtotal: f.getFieldsSubtotal(opt), }) } @@ -336,9 +366,9 @@ func (f *File) addPivotColFields(pt *xlsxPivotTableDefinition, opt *PivotTableOp if err != nil { return err } - for _, filedIdx := range colFieldsIndex { + for _, fieldIdx := range colFieldsIndex { pt.ColFields.Field = append(pt.ColFields.Field, &xlsxField{ - X: filedIdx, + X: fieldIdx, }) } @@ -430,6 +460,18 @@ func (f *File) getPivotFieldsIndex(fields []string, opt *PivotTableOption) ([]in return pivotFieldsIndex, nil } +// getFieldsSubtotal prepare data subtotal by given fields and pivot option. +func (f *File) getFieldsSubtotal(opt *PivotTableOption) (subtotal string) { + subtotal = "sum" + for _, enum := range []string{"average", "count", "countNums", "max", "min", "product", "stdDev", "stdDevp", "sum", "var", "varp"} { + if strings.ToLower(enum) == strings.ToLower(opt.DataSubtotal) { + subtotal = enum + return + } + } + return +} + // addWorkbookPivotCache add the association ID of the pivot cache in xl/workbook.xml. func (f *File) addWorkbookPivotCache(RID int) int { wb := f.workbookReader() diff --git a/pivotTable_test.go b/pivotTable_test.go index 5d841d8..e40dbd6 100644 --- a/pivotTable_test.go +++ b/pivotTable_test.go @@ -4,6 +4,7 @@ import ( "fmt" "math/rand" "path/filepath" + "strings" "testing" "github.com/stretchr/testify/assert" @@ -30,6 +31,8 @@ func TestAddPivotTable(t *testing.T) { Rows: []string{"Month", "Year"}, Columns: []string{"Type"}, Data: []string{"Sales"}, + DataSubtotal: "Sum", + DataFieldName: "Summarize by Sum", })) // Use different order of coordinate tests assert.NoError(t, f.AddPivotTable(&PivotTableOption{ @@ -38,6 +41,8 @@ func TestAddPivotTable(t *testing.T) { Rows: []string{"Month", "Year"}, Columns: []string{"Type"}, Data: []string{"Sales"}, + DataSubtotal: "Average", + DataFieldName: "Summarize by Average", })) assert.NoError(t, f.AddPivotTable(&PivotTableOption{ @@ -46,6 +51,8 @@ func TestAddPivotTable(t *testing.T) { Rows: []string{"Month", "Year"}, Columns: []string{"Region"}, Data: []string{"Sales"}, + DataSubtotal: "Count", + DataFieldName: "Summarize by Count", })) assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", @@ -53,12 +60,16 @@ func TestAddPivotTable(t *testing.T) { Rows: []string{"Month"}, Columns: []string{"Region", "Year"}, Data: []string{"Sales"}, + DataSubtotal: "CountNums", + DataFieldName: "Summarize by CountNums", })) assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "Sheet1!$AE$2:$AG$33", Rows: []string{"Month", "Year"}, Data: []string{"Sales"}, + DataSubtotal: "Max", + DataFieldName: "Summarize by Max", })) f.NewSheet("Sheet2") assert.NoError(t, f.AddPivotTable(&PivotTableOption{ @@ -67,6 +78,8 @@ func TestAddPivotTable(t *testing.T) { Rows: []string{"Month"}, Columns: []string{"Region", "Type", "Year"}, Data: []string{"Sales"}, + DataSubtotal: "Min", + DataFieldName: "Summarize by Min", })) assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", @@ -74,6 +87,8 @@ func TestAddPivotTable(t *testing.T) { Rows: []string{"Month", "Type"}, Columns: []string{"Region", "Year"}, Data: []string{"Sales"}, + DataSubtotal: "Product", + DataFieldName: "Summarize by Product", })) // Test empty pivot table options @@ -135,6 +150,16 @@ func TestAddPivotTable(t *testing.T) { Data: []string{"Sales"}, }), `parameter 'DataRange' parsing error: cannot convert cell "A0" to coordinates: invalid cell name "A0"`) assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddPivotTable1.xlsx"))) + // Test with field names that exceed the length limit and invalid subtotal + assert.NoError(t, f.AddPivotTable(&PivotTableOption{ + DataRange: "Sheet1!$A$1:$E$31", + PivotTableRange: "Sheet1!$G$2:$M$34", + Rows: []string{"Month", "Year"}, + Columns: []string{"Type"}, + Data: []string{"Sales"}, + DataSubtotal: "-", + DataFieldName: strings.Repeat("s", 256), + })) // Test adjust range with invalid range _, _, err := f.adjustRange("") -- cgit v1.2.1