diff options
| -rw-r--r-- | pivotTable.go | 119 | ||||
| -rw-r--r-- | pivotTable_test.go | 127 | ||||
| -rw-r--r-- | rows_test.go | 2 |
3 files changed, 144 insertions, 104 deletions
diff --git a/pivotTable.go b/pivotTable.go index 696dfe7..b7dc859 100644 --- a/pivotTable.go +++ b/pivotTable.go @@ -20,19 +20,15 @@ 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 - Data []string - Page []string + Page []PivotTableField + Rows []PivotTableField + Columns []PivotTableField + Data []PivotTableField } -// AddPivotTable provides the method to add pivot table by given pivot table -// options. -// -// DataSubtotal specifies the aggregation function that applies to this data +// PivotTableField directly maps the field settings of the pivot table. +// Subtotal specifies the aggregation function that applies to this data // field. The default value is sum. The possible values for this attribute // are: // @@ -48,8 +44,16 @@ type PivotTableOption struct { // Var // Varp // -// DataFieldName specifies the name of the data field. Maximum 255 characters +// Name specifies the name of the data field. Maximum 255 characters // are allowed in data field name, excess characters will be truncated. +type PivotTableField struct { + Data string + Name string + Subtotal string +} + +// 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: @@ -81,11 +85,9 @@ type PivotTableOption struct { // if err := f.AddPivotTable(&excelize.PivotTableOption{ // DataRange: "Sheet1!$A$1:$E$31", // PivotTableRange: "Sheet1!$G$2:$M$34", -// Rows: []string{"Month", "Year"}, -// Columns: []string{"Type"}, -// Data: []string{"Sales"}, -// DataSubtotal: "Sum", -// DataFieldName: "Summarize as Sum", +// Rows: []excelize.PivotTableField{{Data: "Month"}, {Data: "Year"}}, +// Columns: []excelize.PivotTableField{{Data: "Type"}}, +// Data: []excelize.PivotTableField{{Data: "Sales", Name: "Summarize", Subtotal: "Sum"}}, // }); err != nil { // fmt.Println(err) // } @@ -186,6 +188,8 @@ func (f *File) adjustRange(rangeStr string) (string, []int, error) { return rng[0], []int{x1, y1, x2, y2}, nil } +// getPivotFieldsOrder provides a function to get order list of pivot table +// fields. func (f *File) getPivotFieldsOrder(dataRange string) ([]string, error) { order := []string{} // data range has been checked @@ -321,15 +325,13 @@ 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 { + dataFieldsSubtotals := f.getPivotTableFieldsSubtotal(opt.Data) + dataFieldsName := f.getPivotTableFieldsName(opt.Data) + for idx, dataField := range dataFieldsIndex { pt.DataFields.DataField = append(pt.DataFields.DataField, &xlsxDataField{ - Name: dataFieldName, + Name: dataFieldsName[idx], Fld: dataField, - Subtotal: f.getFieldsSubtotal(opt), + Subtotal: dataFieldsSubtotals[idx], }) } @@ -352,6 +354,18 @@ func inStrSlice(a []string, x string) int { return -1 } +// 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, opt *PivotTableOption) error { @@ -385,9 +399,10 @@ func (f *File) addPivotFields(pt *xlsxPivotTableDefinition, opt *PivotTableOptio return err } for _, name := range order { - if inStrSlice(opt.Rows, name) != -1 { + if inPivotTableField(opt.Rows, name) != -1 { pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{ Axis: "axisRow", + Name: f.getPivotTableFieldName(name, opt.Rows), Items: &xlsxItems{ Count: 1, Item: []*xlsxItem{ @@ -397,9 +412,10 @@ func (f *File) addPivotFields(pt *xlsxPivotTableDefinition, opt *PivotTableOptio }) continue } - if inStrSlice(opt.Columns, name) != -1 { + if inPivotTableField(opt.Columns, name) != -1 { pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{ Axis: "axisCol", + Name: f.getPivotTableFieldName(name, opt.Columns), Items: &xlsxItems{ Count: 1, Item: []*xlsxItem{ @@ -409,7 +425,7 @@ func (f *File) addPivotFields(pt *xlsxPivotTableDefinition, opt *PivotTableOptio }) continue } - if inStrSlice(opt.Data, name) != -1 { + if inPivotTableField(opt.Data, name) != -1 { pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{ DataField: true, }) @@ -446,30 +462,61 @@ func (f *File) countPivotCache() int { // 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 []string, opt *PivotTableOption) ([]int, error) { +func (f *File) getPivotFieldsIndex(fields []PivotTableField, opt *PivotTableOption) ([]int, error) { pivotFieldsIndex := []int{} orders, err := f.getPivotFieldsOrder(opt.DataRange) if err != nil { return pivotFieldsIndex, err } for _, field := range fields { - if pos := inStrSlice(orders, field); pos != -1 { + if pos := inStrSlice(orders, field.Data); pos != -1 { pivotFieldsIndex = append(pivotFieldsIndex, pos) } } 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 +// 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.ToLower(enum) == strings.ToLower(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 len(fld.Name) > 255 { + field[idx] = fld.Name[0:255] + 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 + return "" } // addWorkbookPivotCache add the association ID of the pivot cache in xl/workbook.xml. diff --git a/pivotTable_test.go b/pivotTable_test.go index e40dbd6..4379538 100644 --- a/pivotTable_test.go +++ b/pivotTable_test.go @@ -28,67 +28,53 @@ func TestAddPivotTable(t *testing.T) { 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: "Sum", - DataFieldName: "Summarize by Sum", + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "Sum", Name: "Summarize by Sum"}}, })) // Use different order of coordinate tests assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, - DataSubtotal: "Average", - DataFieldName: "Summarize by Average", + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "Average", Name: "Summarize by Average"}}, })) assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "Sheet1!$W$2:$AC$34", - Rows: []string{"Month", "Year"}, - Columns: []string{"Region"}, - Data: []string{"Sales"}, - DataSubtotal: "Count", - DataFieldName: "Summarize by Count", + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Region"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "Count", Name: "Summarize by Count"}}, })) assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "Sheet1!$G$37:$W$50", - Rows: []string{"Month"}, - Columns: []string{"Region", "Year"}, - Data: []string{"Sales"}, - DataSubtotal: "CountNums", - DataFieldName: "Summarize by CountNums", + Rows: []PivotTableField{{Data: "Month"}}, + Columns: []PivotTableField{{Data: "Region"}, {Data: "Year"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "CountNums", Name: "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", + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "Max", Name: "Summarize by Max"}}, })) f.NewSheet("Sheet2") assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "Sheet2!$A$1:$AR$15", - Rows: []string{"Month"}, - Columns: []string{"Region", "Type", "Year"}, - Data: []string{"Sales"}, - DataSubtotal: "Min", - DataFieldName: "Summarize by Min", + Rows: []PivotTableField{{Data: "Month"}}, + Columns: []PivotTableField{{Data: "Region"}, {Data: "Type"}, {Data: "Year"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "Min", Name: "Summarize by Min"}}, })) assert.NoError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "Sheet2!$A$18:$AR$54", - Rows: []string{"Month", "Type"}, - Columns: []string{"Region", "Year"}, - Data: []string{"Sales"}, - DataSubtotal: "Product", - DataFieldName: "Summarize by Product", + Rows: []PivotTableField{{Data: "Month"}, {Data: "Type"}}, + Columns: []PivotTableField{{Data: "Region"}, {Data: "Year"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "Product", Name: "Summarize by Product"}}, })) // Test empty pivot table options @@ -97,68 +83,66 @@ func TestAddPivotTable(t *testing.T) { assert.EqualError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$A$1", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }), `parameter 'DataRange' parsing error: parameter is invalid`) // Test the data range of the worksheet that is not declared assert.EqualError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "$A$1:$E$31", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }), `parameter 'DataRange' parsing error: parameter is invalid`) // Test the worksheet declared in the data range does not exist assert.EqualError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "SheetN!$A$1:$E$31", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }), "sheet SheetN is not exist") // Test the pivot table range of the worksheet that is not declared assert.EqualError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }), `parameter 'PivotTableRange' parsing error: parameter is invalid`) // Test the worksheet declared in the pivot table range does not exist assert.EqualError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$1:$E$31", PivotTableRange: "SheetN!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }), "sheet SheetN is not exist") // Test not exists worksheet in data range assert.EqualError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "SheetN!$A$1:$E$31", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }), "sheet SheetN is not exist") // Test invalid row number in data range assert.EqualError(t, f.AddPivotTable(&PivotTableOption{ DataRange: "Sheet1!$A$0:$E$31", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "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), + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales", Subtotal: "-", Name: strings.Repeat("s", 256)}}, })) // Test adjust range with invalid range @@ -173,9 +157,9 @@ func TestAddPivotTable(t *testing.T) { assert.EqualError(t, f.addPivotCache(0, "", &PivotTableOption{ DataRange: "$A$1:$E$31", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }, nil), "parameter 'DataRange' parsing error: parameter is invalid") // Test add pivot table with empty options assert.EqualError(t, f.addPivotTable(0, 0, "", &PivotTableOption{}), "parameter 'PivotTableRange' parsing error: parameter is required") @@ -185,11 +169,20 @@ func TestAddPivotTable(t *testing.T) { assert.EqualError(t, f.addPivotFields(nil, &PivotTableOption{ DataRange: "$A$1:$E$31", PivotTableRange: "Sheet1!$U$34:$O$2", - Rows: []string{"Month", "Year"}, - Columns: []string{"Type"}, - Data: []string{"Sales"}, + Rows: []PivotTableField{{Data: "Month"}, {Data: "Year"}}, + Columns: []PivotTableField{{Data: "Type"}}, + Data: []PivotTableField{{Data: "Sales"}}, }), `parameter 'DataRange' parsing error: parameter is invalid`) // Test get pivot fields index with empty data range - _, err = f.getPivotFieldsIndex([]string{}, &PivotTableOption{}) + _, err = f.getPivotFieldsIndex([]PivotTableField{}, &PivotTableOption{}) assert.EqualError(t, err, `parameter 'DataRange' parsing error: parameter is required`) } + +func TestInStrSlice(t *testing.T) { + assert.EqualValues(t, -1, inStrSlice([]string{}, "")) +} + +func TestGetPivotTableFieldName(t *testing.T) { + f := NewFile() + f.getPivotTableFieldName("-", []PivotTableField{}) +} diff --git a/rows_test.go b/rows_test.go index a5ee428..a53b0a9 100644 --- a/rows_test.go +++ b/rows_test.go @@ -810,7 +810,7 @@ func TestDuplicateRowTo(t *testing.T) { func TestDuplicateMergeCells(t *testing.T) { f := File{} xlsx := &xlsxWorksheet{MergeCells: &xlsxMergeCells{ - Cells: []*xlsxMergeCell{&xlsxMergeCell{Ref: "A1:-"}}, + Cells: []*xlsxMergeCell{{Ref: "A1:-"}}, }} assert.EqualError(t, f.duplicateMergeCells("Sheet1", xlsx, 0, 0), `cannot convert cell "-" to coordinates: invalid cell name "-"`) xlsx.MergeCells.Cells[0].Ref = "A1:B1" |