diff options
| -rw-r--r-- | pivotTable.go | 434 | ||||
| -rw-r--r-- | pivotTable_test.go | 164 | ||||
| -rw-r--r-- | xmlPivotCache.go | 34 | ||||
| -rw-r--r-- | xmlPivotTable.go | 141 |
4 files changed, 696 insertions, 77 deletions
diff --git a/pivotTable.go b/pivotTable.go new file mode 100644 index 0000000..881d774 --- /dev/null +++ b/pivotTable.go @@ -0,0 +1,434 @@ +// Copyright 2016 - 2019 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 XLSX files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.10 or later. + +package excelize + +import ( + "encoding/xml" + "errors" + "fmt" + "strconv" + "strings" +) + +// PivotTableOption directly maps the format settings of the pivot table. +type PivotTableOption struct { + DataRange string + PivotTableRange string + Rows []string + Columns []string + Data []string + Page []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: +// +// package main +// +// import ( +// "fmt" +// "math/rand" +// +// "github.com/360EntSecGroup-Skylar/excelize" +// ) +// +// func main() { +// f := excelize.NewFile() +// // 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"} +// region := []string{"East", "West", "North", "South"} +// f.SetSheetRow("Sheet1", "A1", &[]string{"Month", "Year", "Type", "Sales", "Region"}) +// for i := 0; i < 30; i++ { +// f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), month[rand.Intn(12)]) +// f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), year[rand.Intn(3)]) +// f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), types[rand.Intn(4)]) +// f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), rand.Intn(5000)) +// f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), region[rand.Intn(4)]) +// } +// 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"}, +// }) +// if err != nil { +// fmt.Println(err) +// } +// err = f.SaveAs("Book1.xlsx") +// if err != nil { +// fmt.Println(err) +// } +// } +// +func (f *File) AddPivotTable(opt *PivotTableOption) error { + // parameter validation + dataSheet, pivotTableSheetPath, err := f.parseFormatPivotTableSet(opt) + if err != nil { + return err + } + + pivotTableID := f.countPivotTables() + 1 + pivotCacheID := f.countPivotCache() + 1 + + sheetRelationshipsPivotTableXML := "../pivotTables/pivotTable" + strconv.Itoa(pivotTableID) + ".xml" + pivotTableXML := strings.Replace(sheetRelationshipsPivotTableXML, "..", "xl", -1) + pivotCacheXML := "xl/pivotCache/pivotCacheDefinition" + strconv.Itoa(pivotCacheID) + ".xml" + err = f.addPivotCache(pivotCacheID, pivotCacheXML, opt, dataSheet) + if err != nil { + return err + } + + // workbook pivot cache + workBookPivotCacheRID := f.addRels("xl/_rels/workbook.xml.rels", SourceRelationshipPivotCache, fmt.Sprintf("pivotCache/pivotCacheDefinition%d.xml", pivotCacheID), "") + 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), "") + err = f.addPivotTable(cacheID, pivotTableID, pivotTableXML, opt) + if err != nil { + return err + } + pivotTableSheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(pivotTableSheetPath, "xl/worksheets/") + ".rels" + f.addRels(pivotTableSheetRels, SourceRelationshipPivotTable, sheetRelationshipsPivotTableXML, "") + f.addContentTypePart(pivotTableID, "pivotTable") + f.addContentTypePart(pivotCacheID, "pivotCache") + + return nil +} + +// parseFormatPivotTableSet provides a function to validate pivot table +// properties. +func (f *File) parseFormatPivotTableSet(opt *PivotTableOption) (*xlsxWorksheet, string, error) { + 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()) + } + dataSheet, err := f.workSheetReader(dataSheetName) + if err != nil { + return dataSheet, "", err + } + pivotTableSheetPath, ok := f.sheetMap[trimSheetName(pivotTableSheetName)] + if !ok { + return dataSheet, pivotTableSheetPath, fmt.Errorf("sheet %s is not exist", pivotTableSheetName) + } + 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{}, errors.New("parameter is required") + } + rng := strings.Split(rangeStr, "!") + if len(rng) != 2 { + return "", []int{}, errors.New("parameter is invalid") + } + trimRng := strings.Replace(rng[1], "$", "", -1) + coordinates, err := f.areaRefToCoordinates(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{}, errors.New("parameter is invalid") + } + + // Correct the coordinate area, 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 +} + +func (f *File) getPivotFieldsOrder(dataRange string) ([]string, error) { + order := []string{} + // data range has been checked + dataSheet, coordinates, err := f.adjustRange(dataRange) + if err != nil { + return order, fmt.Errorf("parameter 'DataRange' parsing error: %s", 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 + } + order = append(order, name) + } + return order, nil +} + +// 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) + if err != nil { + return fmt.Errorf("parameter 'DataRange' parsing error: %s", err.Error()) + } + order, err := f.getPivotFieldsOrder(opt.DataRange) + if err != nil { + return err + } + hcell, _ := CoordinatesToCellName(coordinates[0], coordinates[1]) + vcell, _ := CoordinatesToCellName(coordinates[2], coordinates[3]) + pc := xlsxPivotCacheDefinition{ + SaveData: false, + RefreshOnLoad: true, + CacheSource: &xlsxCacheSource{ + Type: "worksheet", + WorksheetSource: &xlsxWorksheetSource{ + Ref: hcell + ":" + vcell, + Sheet: dataSheet, + }, + }, + CacheFields: &xlsxCacheFields{}, + } + for _, name := range order { + pc.CacheFields.CacheField = append(pc.CacheFields.CacheField, &xlsxCacheField{ + Name: name, + SharedItems: &xlsxSharedItems{ + Count: 0, + }, + }) + } + pc.CacheFields.Count = len(pc.CacheFields.CacheField) + pivotCache, err := xml.Marshal(pc) + f.saveFileList(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, pivotTableXML string, opt *PivotTableOption) error { + // validate pivot table range + _, coordinates, err := f.adjustRange(opt.PivotTableRange) + if err != nil { + return fmt.Errorf("parameter 'PivotTableRange' parsing error: %s", err.Error()) + } + + hcell, _ := CoordinatesToCellName(coordinates[0], coordinates[1]) + vcell, _ := CoordinatesToCellName(coordinates[2], coordinates[3]) + + pt := xlsxPivotTableDefinition{ + Name: fmt.Sprintf("Pivot Table%d", pivotTableID), + CacheID: cacheID, + DataCaption: "Values", + Location: &xlsxLocation{ + Ref: hcell + ":" + vcell, + FirstDataCol: 1, + FirstDataRow: 1, + FirstHeaderRow: 1, + }, + PivotFields: &xlsxPivotFields{}, + RowFields: &xlsxRowFields{}, + RowItems: &xlsxRowItems{ + Count: 1, + I: []*xlsxI{ + { + []*xlsxX{{}, {}}, + }, + }, + }, + ColFields: &xlsxColFields{}, + DataFields: &xlsxDataFields{}, + PivotTableStyleInfo: &xlsxPivotTableStyleInfo{ + Name: "PivotStyleLight16", + ShowRowHeaders: true, + ShowColHeaders: true, + ShowLastColumn: true, + }, + } + + // pivot fields + err = f.addPivotFields(&pt, opt) + if err != nil { + return err + } + + // count pivot fields + pt.PivotFields.Count = len(pt.PivotFields.PivotField) + + // row fields + rowFieldsIndex, err := f.getPivotFieldsIndex(opt.Rows, opt) + if err != nil { + return err + } + for _, filedIdx := range rowFieldsIndex { + pt.RowFields.Field = append(pt.RowFields.Field, &xlsxField{ + X: filedIdx, + }) + } + + // count row fields + pt.RowFields.Count = len(pt.RowFields.Field) + + // col fields + colFieldsIndex, err := f.getPivotFieldsIndex(opt.Columns, opt) + if err != nil { + return err + } + for _, filedIdx := range colFieldsIndex { + pt.ColFields.Field = append(pt.ColFields.Field, &xlsxField{ + X: filedIdx, + }) + } + + // count col fields + pt.ColFields.Count = len(pt.ColFields.Field) + + // data fields + dataFieldsIndex, err := f.getPivotFieldsIndex(opt.Data, opt) + if err != nil { + return err + } + for _, dataField := range dataFieldsIndex { + pt.DataFields.DataField = append(pt.DataFields.DataField, &xlsxDataField{ + Fld: dataField, + }) + } + + // count data fields + pt.DataFields.Count = len(pt.DataFields.DataField) + + pivotTable, err := xml.Marshal(pt) + f.saveFileList(pivotTableXML, pivotTable) + return err +} + +// inStrSlice provides a method to check if an element is present in an array, +// and return the index of its location, otherwise return -1. +func inStrSlice(a []string, x string) int { + for idx, n := range a { + if x == n { + return idx + } + } + return -1 +} + +// 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) + if err != nil { + return err + } + for _, name := range order { + if inStrSlice(opt.Rows, name) != -1 { + pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{ + Axis: "axisRow", + Items: &xlsxItems{ + Count: 1, + Item: []*xlsxItem{ + {T: "default"}, + }, + }, + }) + continue + } + if inStrSlice(opt.Columns, name) != -1 { + pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{ + Axis: "axisCol", + Items: &xlsxItems{ + Count: 1, + Item: []*xlsxItem{ + {T: "default"}, + }, + }, + }) + continue + } + if inStrSlice(opt.Data, name) != -1 { + pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{ + DataField: true, + }) + continue + } + pt.PivotFields.PivotField = append(pt.PivotFields.PivotField, &xlsxPivotField{}) + } + return err +} + +// countPivotTables provides a function to get drawing files count storage in +// the folder xl/pivotTables. +func (f *File) countPivotTables() int { + count := 0 + for k := range f.XLSX { + if strings.Contains(k, "xl/pivotTables/pivotTable") { + count++ + } + } + return count +} + +// countPivotCache provides a function to get drawing files count storage in +// the folder xl/pivotCache. +func (f *File) countPivotCache() int { + count := 0 + for k := range f.XLSX { + if strings.Contains(k, "xl/pivotCache/pivotCacheDefinition") { + count++ + } + } + 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 []string, 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 { + pivotFieldsIndex = append(pivotFieldsIndex, pos) + } + } + return pivotFieldsIndex, nil +} + +// addWorkbookPivotCache add the association ID of the pivot cache in xl/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 +} diff --git a/pivotTable_test.go b/pivotTable_test.go new file mode 100644 index 0000000..27e5914 --- /dev/null +++ b/pivotTable_test.go @@ -0,0 +1,164 @@ +package excelize + +import ( + "fmt" + "math/rand" + "path/filepath" + "testing" + + "github.com/stretchr/testify/assert" +) + +func TestAddPivotTable(t *testing.T) { + f := NewFile() + // 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"} + region := []string{"East", "West", "North", "South"} + f.SetSheetRow("Sheet1", "A1", &[]string{"Month", "Year", "Type", "Sales", "Region"}) + for i := 0; i < 30; i++ { + f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), month[rand.Intn(12)]) + f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), year[rand.Intn(3)]) + f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), types[rand.Intn(4)]) + f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), rand.Intn(5000)) + f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), region[rand.Intn(4)]) + } + 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"}, + })) + // 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"}, + })) + + 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"}, + })) + 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"}, + })) + 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"}, + })) + 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"}, + })) + + // Test empty pivot table options + assert.EqualError(t, f.AddPivotTable(nil), "parameter is required") + // Test invalid data range + 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"}, + }), `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"}, + }), `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"}, + }), "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"}, + }), `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"}, + }), "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"}, + }), "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"}, + }), `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 adjust range with invalid range + _, _, err := f.adjustRange("") + assert.EqualError(t, err, "parameter is required") + // Test get pivot fields order with empty data range + _, err = f.getPivotFieldsOrder("") + 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") + // Test add pivot cache with invalid data range + 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"}, + }, 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") + // Test add pivot table with invalid data range + assert.EqualError(t, f.addPivotTable(0, 0, "", &PivotTableOption{}), "parameter 'PivotTableRange' parsing error: parameter is required") + // Test add pivot fields with empty data range + 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"}, + }), `parameter 'DataRange' parsing error: parameter is invalid`) + // Test get pivot fields index with empty data range + _, err = f.getPivotFieldsIndex([]string{}, &PivotTableOption{}) + assert.EqualError(t, err, `parameter 'DataRange' parsing error: parameter is required`) +} diff --git a/xmlPivotCache.go b/xmlPivotCache.go index 0c00832..a4b0711 100644 --- a/xmlPivotCache.go +++ b/xmlPivotCache.go @@ -10,7 +10,7 @@ type xlsxPivotCacheDefinition struct { XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main pivotCacheDefinition"` RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"` Invalid bool `xml:"invalid,attr,omitempty"` - SaveData bool `xml:"saveData,attr,omitempty"` + SaveData bool `xml:"saveData,attr"` RefreshOnLoad bool `xml:"refreshOnLoad,attr,omitempty"` OptimizeMemory bool `xml:"optimizeMemory,attr,omitempty"` EnableRefresh bool `xml:"enableRefresh,attr,omitempty"` @@ -47,6 +47,28 @@ type xlsxPivotCacheDefinition struct { // (including OLAP cubes), multiple SpreadsheetML worksheets, or another // PivotTable. type xlsxCacheSource struct { + Type string `xml:"type,attr"` + ConnectionId int `xml:"connectionId,attr,omitempty"` + WorksheetSource *xlsxWorksheetSource `xml:"worksheetSource"` + Consolidation *xlsxConsolidation `xml:"consolidation"` + ExtLst *xlsxExtLst `xml:"extLst"` +} + +// xlsxWorksheetSource represents the location of the source of the data that +// is stored in the cache. +type xlsxWorksheetSource struct { + RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"` + Ref string `xml:"ref,attr,omitempty"` + Name string `xml:"name,attr,omitempty"` + Sheet string `xml:"sheet,attr,omitempty"` +} + +// xlsxConsolidation represents the description of the PivotCache source using +// multiple consolidation ranges. This element is used when the source of the +// PivotTable is a collection of ranges in the workbook. The ranges are +// specified in the rangeSets collection. The logic for how the application +// consolidates the data in the ranges is application- defined. +type xlsxConsolidation struct { } // xlsxCacheFields represents the collection of field definitions in the @@ -67,18 +89,18 @@ type xlsxCacheField struct { PropertyName string `xml:"propertyName,attr,omitempty"` ServerField bool `xml:"serverField,attr,omitempty"` UniqueList bool `xml:"uniqueList,attr,omitempty"` - NumFmtId string `xml:"numFmtId,attr,omitempty"` + NumFmtId int `xml:"numFmtId,attr"` Formula string `xml:"formula,attr,omitempty"` SQLType int `xml:"sqlType,attr,omitempty"` Hierarchy int `xml:"hierarchy,attr,omitempty"` Level int `xml:"level,attr,omitempty"` - DatabaseField bool `xml:"databaseField,attr"` + DatabaseField bool `xml:"databaseField,attr,omitempty"` MappingCount int `xml:"mappingCount,attr,omitempty"` MemberPropertyField bool `xml:"memberPropertyField,attr,omitempty"` SharedItems *xlsxSharedItems `xml:"sharedItems"` FieldGroup *xlsxFieldGroup `xml:"fieldGroup"` - MpMap *xlsxX `xml:"map"` - ExtLst *xlsxExtLst `xml:"exrLst"` + MpMap *xlsxX `xml:"mpMap"` + ExtLst *xlsxExtLst `xml:"extLst"` } // xlsxSharedItems represents the collection of unique items for a field in @@ -100,7 +122,7 @@ type xlsxSharedItems struct { MaxValue float64 `xml:"maxValue,attr,omitempty"` MinDate string `xml:"minDate,attr,omitempty"` MaxDate string `xml:"maxDate,attr,omitempty"` - Count int `xml:"count,attr,omitempty"` + Count int `xml:"count,attr"` LongText bool `xml:"longText,attr,omitempty"` M *xlsxMissing `xml:"m"` N *xlsxNumber `xml:"n"` diff --git a/xmlPivotTable.go b/xmlPivotTable.go index 6f2a8e7..3738ed8 100644 --- a/xmlPivotTable.go +++ b/xmlPivotTable.go @@ -36,8 +36,8 @@ type xlsxPivotTableDefinition struct { PivotTableStyle string `xml:"pivotTableStyle,attr,omitempty"` VacatedStyle string `xml:"vacatedStyle,attr,omitempty"` Tag string `xml:"tag,attr,omitempty"` - UpdatedVersion int `xml:"updatedVersion,attr"` - MinRefreshableVersion int `xml:"minRefreshableVersion,attr"` + UpdatedVersion int `xml:"updatedVersion,attr,omitempty"` + MinRefreshableVersion int `xml:"minRefreshableVersion,attr,omitempty"` AsteriskTotals bool `xml:"asteriskTotals,attr,omitempty"` ShowItems bool `xml:"showItems,attr,omitempty"` EditData bool `xml:"editData,attr,omitempty"` @@ -54,27 +54,27 @@ type xlsxPivotTableDefinition struct { EnableDrill bool `xml:"enableDrill,attr,omitempty"` EnableFieldProperties bool `xml:"enableFieldProperties,attr,omitempty"` PreserveFormatting bool `xml:"preserveFormatting,attr,omitempty"` - UseAutoFormatting bool `xml:"useAutoFormatting,attr"` + UseAutoFormatting bool `xml:"useAutoFormatting,attr,omitempty"` PageWrap int `xml:"pageWrap,attr,omitempty"` PageOverThenDown bool `xml:"pageOverThenDown,attr,omitempty"` SubtotalHiddenItems bool `xml:"subtotalHiddenItems,attr,omitempty"` RowGrandTotals bool `xml:"rowGrandTotals,attr,omitempty"` ColGrandTotals bool `xml:"colGrandTotals,attr,omitempty"` FieldPrintTitles bool `xml:"fieldPrintTitles,attr,omitempty"` - ItemPrintTitles bool `xml:"itemPrintTitles,attr"` + ItemPrintTitles bool `xml:"itemPrintTitles,attr,omitempty"` MergeItem bool `xml:"mergeItem,attr,omitempty"` ShowDropZones bool `xml:"showDropZones,attr,omitempty"` - CreatedVersion int `xml:"createdVersion,attr"` + CreatedVersion int `xml:"createdVersion,attr,omitempty"` Indent int `xml:"indent,attr,omitempty"` ShowEmptyRow bool `xml:"showEmptyRow,attr,omitempty"` ShowEmptyCol bool `xml:"showEmptyCol,attr,omitempty"` ShowHeaders bool `xml:"showHeaders,attr,omitempty"` - Compact bool `xml:"compact,attr,omitempty"` - Outline bool `xml:"outline,attr,omitempty"` + Compact bool `xml:"compact,attr"` + Outline bool `xml:"outline,attr"` OutlineData bool `xml:"outlineData,attr,omitempty"` CompactData bool `xml:"compactData,attr,omitempty"` Published bool `xml:"published,attr,omitempty"` - GridDropZones bool `xml:"gridDropZones,attr"` + GridDropZones bool `xml:"gridDropZones,attr,omitempty"` Immersive bool `xml:"immersive,attr,omitempty"` MultipleFieldFilters bool `xml:"multipleFieldFilters,attr,omitempty"` ChartFormat int `xml:"chartFormat,attr,omitempty"` @@ -101,8 +101,8 @@ type xlsxLocation struct { FirstHeaderRow int `xml:"firstHeaderRow,attr"` FirstDataRow int `xml:"firstDataRow,attr"` FirstDataCol int `xml:"firstDataCol,attr"` - RowPageCount int `xml:"rowPageCount,attr"` - ColPageCount int `xml:"colPageCount,attr"` + RowPageCount int `xml:"rowPageCount,attr,omitempty"` + ColPageCount int `xml:"colPageCount,attr,omitempty"` } // xlsxPivotFields represents the collection of fields that appear on the @@ -116,50 +116,50 @@ type xlsxPivotFields struct { // contains information about the field, including the collection of items in // the field. type xlsxPivotField struct { - Name string `xml:"name,attr"` + Name string `xml:"name,attr,omitempty"` Axis string `xml:"axis,attr,omitempty"` - DataField bool `xml:"dataField,attr"` - SubtotalCaption string `xml:"subtotalCaption,attr"` - ShowDropDowns bool `xml:"showDropDowns,attr"` - HiddenLevel bool `xml:"hiddenLevel,attr"` - UniqueMemberProperty string `xml:"uniqueMemberProperty,attr"` + DataField bool `xml:"dataField,attr,omitempty"` + SubtotalCaption string `xml:"subtotalCaption,attr,omitempty"` + ShowDropDowns bool `xml:"showDropDowns,attr,omitempty"` + HiddenLevel bool `xml:"hiddenLevel,attr,omitempty"` + UniqueMemberProperty string `xml:"uniqueMemberProperty,attr,omitempty"` Compact bool `xml:"compact,attr"` - AllDrilled bool `xml:"allDrilled,attr"` + AllDrilled bool `xml:"allDrilled,attr,omitempty"` NumFmtId string `xml:"numFmtId,attr,omitempty"` Outline bool `xml:"outline,attr"` - SubtotalTop bool `xml:"subtotalTop,attr"` - DragToRow bool `xml:"dragToRow,attr"` - DragToCol bool `xml:"dragToCol,attr"` - MultipleItemSelectionAllowed bool `xml:"multipleItemSelectionAllowed,attr"` - DragToPage bool `xml:"dragToPage,attr"` - DragToData bool `xml:"dragToData,attr"` - DragOff bool `xml:"dragOff,attr"` + SubtotalTop bool `xml:"subtotalTop,attr,omitempty"` + DragToRow bool `xml:"dragToRow,attr,omitempty"` + DragToCol bool `xml:"dragToCol,attr,omitempty"` + MultipleItemSelectionAllowed bool `xml:"multipleItemSelectionAllowed,attr,omitempty"` + DragToPage bool `xml:"dragToPage,attr,omitempty"` + DragToData bool `xml:"dragToData,attr,omitempty"` + DragOff bool `xml:"dragOff,attr,omitempty"` ShowAll bool `xml:"showAll,attr"` - InsertBlankRow bool `xml:"insertBlankRow,attr"` - ServerField bool `xml:"serverField,attr"` - InsertPageBreak bool `xml:"insertPageBreak,attr"` - AutoShow bool `xml:"autoShow,attr"` - TopAutoShow bool `xml:"topAutoShow,attr"` - HideNewItems bool `xml:"hideNewItems,attr"` - MeasureFilter bool `xml:"measureFilter,attr"` - IncludeNewItemsInFilter bool `xml:"includeNewItemsInFilter,attr"` - ItemPageCount int `xml:"itemPageCount,attr"` - SortType string `xml:"sortType,attr"` + InsertBlankRow bool `xml:"insertBlankRow,attr,omitempty"` + ServerField bool `xml:"serverField,attr,omitempty"` + InsertPageBreak bool `xml:"insertPageBreak,attr,omitempty"` + AutoShow bool `xml:"autoShow,attr,omitempty"` + TopAutoShow bool `xml:"topAutoShow,attr,omitempty"` + HideNewItems bool `xml:"hideNewItems,attr,omitempty"` + MeasureFilter bool `xml:"measureFilter,attr,omitempty"` + IncludeNewItemsInFilter bool `xml:"includeNewItemsInFilter,attr,omitempty"` + ItemPageCount int `xml:"itemPageCount,attr,omitempty"` + SortType string `xml:"sortType,attr,omitempty"` DataSourceSort bool `xml:"dataSourceSort,attr,omitempty"` - NonAutoSortDefault bool `xml:"nonAutoSortDefault,attr"` + NonAutoSortDefault bool `xml:"nonAutoSortDefault,attr,omitempty"` RankBy int `xml:"rankBy,attr,omitempty"` - DefaultSubtotal bool `xml:"defaultSubtotal,attr"` - SumSubtotal bool `xml:"sumSubtotal,attr"` - CountASubtotal bool `xml:"countASubtotal,attr"` - AvgSubtotal bool `xml:"avgSubtotal,attr"` - MaxSubtotal bool `xml:"maxSubtotal,attr"` - MinSubtotal bool `xml:"minSubtotal,attr"` - ProductSubtotal bool `xml:"productSubtotal,attr"` - CountSubtotal bool `xml:"countSubtotal,attr"` - StdDevSubtotal bool `xml:"stdDevSubtotal,attr"` - StdDevPSubtotal bool `xml:"stdDevPSubtotal,attr"` - VarSubtotal bool `xml:"varSubtotal,attr"` - VarPSubtotal bool `xml:"varPSubtotal,attr"` + DefaultSubtotal bool `xml:"defaultSubtotal,attr,omitempty"` + SumSubtotal bool `xml:"sumSubtotal,attr,omitempty"` + CountASubtotal bool `xml:"countASubtotal,attr,omitempty"` + AvgSubtotal bool `xml:"avgSubtotal,attr,omitempty"` + MaxSubtotal bool `xml:"maxSubtotal,attr,omitempty"` + MinSubtotal bool `xml:"minSubtotal,attr,omitempty"` + ProductSubtotal bool `xml:"productSubtotal,attr,omitempty"` + CountSubtotal bool `xml:"countSubtotal,attr,omitempty"` + StdDevSubtotal bool `xml:"stdDevSubtotal,attr,omitempty"` + StdDevPSubtotal bool `xml:"stdDevPSubtotal,attr,omitempty"` + VarSubtotal bool `xml:"varSubtotal,attr,omitempty"` + VarPSubtotal bool `xml:"varPSubtotal,attr,omitempty"` ShowPropCell bool `xml:"showPropCell,attr,omitempty"` ShowPropTip bool `xml:"showPropTip,attr,omitempty"` ShowPropAsCaption bool `xml:"showPropAsCaption,attr,omitempty"` @@ -179,17 +179,17 @@ type xlsxItems struct { // xlsxItem represents a single item in PivotTable field. type xlsxItem struct { - N string `xml:"n,attr"` - T string `xml:"t,attr"` - H bool `xml:"h,attr"` - S bool `xml:"s,attr"` - SD bool `xml:"sd,attr"` - F bool `xml:"f,attr"` - M bool `xml:"m,attr"` - C bool `xml:"c,attr"` - X int `xml:"x,attr,omitempty"` - D bool `xml:"d,attr"` - E bool `xml:"e,attr"` + N string `xml:"n,attr,omitempty"` + T string `xml:"t,attr,omitempty"` + H bool `xml:"h,attr,omitempty"` + S bool `xml:"s,attr,omitempty"` + SD bool `xml:"sd,attr,omitempty"` + F bool `xml:"f,attr,omitempty"` + M bool `xml:"m,attr,omitempty"` + C bool `xml:"c,attr,omitempty"` + X int `xml:"x,attr,omitempty,omitempty"` + D bool `xml:"d,attr,omitempty"` + E bool `xml:"e,attr,omitempty"` } // xlsxAutoSortScope represents the sorting scope for the PivotTable. @@ -198,8 +198,8 @@ type xlsxAutoSortScope struct { // xlsxRowFields represents the collection of row fields for the PivotTable. type xlsxRowFields struct { - Count int `xml:"count,attr"` - Fields []*xlsxField `xml:"fields"` + Count int `xml:"count,attr"` + Field []*xlsxField `xml:"field"` } // xlsxField represents a generic field that can appear either on the column @@ -224,14 +224,13 @@ type xlsxI struct { // xlsxX represents an array of indexes to cached shared item values. type xlsxX struct { - XMLName xml.Name `xml:"x"` } // xlsxColFields represents the collection of fields that are on the column // axis of the PivotTable. type xlsxColFields struct { - Count int `xml:"count,attr"` - Fields []*xlsxField `xml:"fields"` + Count int `xml:"count,attr"` + Field []*xlsxField `xml:"field"` } // xlsxColItems represents the collection of column items of the PivotTable. @@ -261,8 +260,8 @@ type xlsxPageField struct { // xlsxDataFields represents the collection of items in the data region of the // PivotTable. type xlsxDataFields struct { - Count int `xml:"count,attr"` - DataField *xlsxDataField `xml:"dataField"` + Count int `xml:"count,attr"` + DataField []*xlsxDataField `xml:"dataField"` } // xlsxDataField represents a field from a source list, table, or database @@ -270,10 +269,10 @@ type xlsxDataFields struct { type xlsxDataField struct { Name string `xml:"name,attr,omitempty"` Fld int `xml:"fld,attr"` - Subtotal string `xml:"subtotal,attr"` - ShowDataAs string `xml:"showDataAs,attr"` - BaseField int `xml:"baseField,attr"` - BaseItem int64 `xml:"baseItem,attr"` + Subtotal string `xml:"subtotal,attr,omitempty"` + ShowDataAs string `xml:"showDataAs,attr,omitempty"` + BaseField int `xml:"baseField,attr,omitempty"` + BaseItem int64 `xml:"baseItem,attr,omitempty"` NumFmtId string `xml:"numFmtId,attr,omitempty"` ExtLst *xlsxExtLst `xml:"extLst"` } @@ -289,7 +288,7 @@ type xlsxPivotTableStyleInfo struct { Name string `xml:"name,attr"` ShowRowHeaders bool `xml:"showRowHeaders,attr"` ShowColHeaders bool `xml:"showColHeaders,attr"` - ShowRowStripes bool `xml:"showRowStripes,attr"` - ShowColStripes bool `xml:"showColStripes,attr"` + ShowRowStripes bool `xml:"showRowStripes,attr,omitempty"` + ShowColStripes bool `xml:"showColStripes,attr,omitempty"` ShowLastColumn bool `xml:"showLastColumn,attr,omitempty"` } |