Resolve #40, init pivot table support

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"`
 }