Pivot Table Range

[robbogd76]

If I don't specify the pivot table range correctly I get an error when I open the spreadsheet. Unfortunately, I'm not sure how I can determine the range up front given I don't know how many rows will be in it. Any thought?

[xuri]

The range of the pivot table was not a strict limits, but requires the row of the upper-left cell in this range should be under the first row, and the bottom-right cell was different from upper left cell.

[robbogd76]

func addPivotTable(file *excelize.File, storyCount int) {

	file.NewSheet("Pivot Tables")

	if err := file.AddPivotTable(&excelize.PivotTableOption{
		DataRange:       "Data!$A$1:$K$" + strconv.Itoa(storyCount+1),
		PivotTableRange: "Pivot Tables!$A$2:$C$5000",
		Rows: []excelize.PivotTableField{
			{Data: "Project Name"}, {Data: "Category"}},
		Data: []excelize.PivotTableField{
			{Data: "Category", Name: "Count of Category", Subtotal: "Count"}},
		RowGrandTotals: true,
		ColGrandTotals: true,
		ShowDrill:      true,
		ShowRowHeaders: true,
		ShowColHeaders: true,
		ShowLastColumn: true,
	}); err != nil {
		fmt.Println(err)
	}
}

[robbogd76]

Actually, this may a red herring. I tried setting the range to what I knew to be correct for the data I have and I still get the error.

[robbogd76]

I've managed to reproduce this issue with the sample code that you have for AddPivotTable. It seems related to the Data property of the options.

package main

import (
	"fmt"
	"math/rand"

	"github.com/xuri/excelize/v2"
)

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)])
	}

	f.NewSheet("Pivot")

	if err := f.AddPivotTable(&excelize.PivotTableOption{
		DataRange:       "Sheet1!$A$1:$E$31",
		PivotTableRange: "Pivot!$A$1:$B$2",
		Rows:            []excelize.PivotTableField{{Data: "Month"}, {Data: "Type"}},
		Filter:          []excelize.PivotTableField{},
		Columns:         []excelize.PivotTableField{},
		Data:            []excelize.PivotTableField{{Data: "Type", Name: "Count of Type", Subtotal: "Count"}},
		RowGrandTotals:  true,
		ColGrandTotals:  true,
		ShowDrill:       true,
		ShowRowHeaders:  true,
		ShowColHeaders:  true,
		ShowLastColumn:  true,
	}); err != nil {
		fmt.Println(err)
	}
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

Running the above code will result in a spreadsheet with the same error I am seeing.

[robbogd76]

If I leave the Data property as it was in your original code, I do not see the error.

[xuri]

This issue duplicate with #1203, I have fixed it. Please upgrade to the master branch code, and this patch will be released in the next version.

[robbogd76]

Awesome, thanks for your help with this Xuri. Apologies for not finding that defect before bothering you.