Timelines/slicers in pivot tables

[Amodio]

Hello,

I am trying to add a timeline to a pivot table generated by Excelize.
This cannot be done even in Excel (compatibility issue), you can run & try this code:

package main

import (
    "fmt"
    "github.com/360EntSecGroup-Skylar/excelize"
    "math/rand"
    "time"
)

// Write a random table to a given sheetName
func createTable(f *excelize.File, sheetName string) {
    // Write headers of the table
    headers := []string{ "Animal", "Date", "Number" }
    for i, str := range headers {
        cellName, _ := excelize.CoordinatesToCellName(1 + i, 1)
        f.SetCellValue(sheetName, cellName, str)
    }
    // Fill the lines of the table
    labels := []string{ "Elephant", "Pangolin", "Alligator", "Antelope", "Ape",
        "Armadillo", "Aye-Aye", "Baboon", "Badger", "Bandicoot", "Bat(s)" }
    // Set the date format (cell style)
    style, _ := f.NewStyle(`{
    "number_format": 15,
    "alignment":
    {
        "horizontal": "center",
        "shrink_to_fit": true,
        "vertical": "center"
    }
}`)
    for i, str := range labels {
        cellName, _ := excelize.CoordinatesToCellName(1, 2 + i)
        f.SetCellValue(sheetName, cellName, str)
        cellName, _ = excelize.CoordinatesToCellName(2, 2 + i)
        f.SetCellValue(sheetName, cellName, func() (date time.Time) {
            date = time.Date(2009, 1, 1, 12, 0, 0, 0, time.UTC)
            return date.Add(time.Hour * time.Duration(24 * rand.Intn(1337)))
        }())
        f.SetCellStyle(sheetName, cellName, cellName, style)
        cellName, _ = excelize.CoordinatesToCellName(3, 2 + i)
        f.SetCellValue(sheetName, cellName, rand.Intn(4))
    }
    // Create the table
    if err := f.AddTable(sheetName, "A1", "C12", `{
        "table_name": "animalsTable",
        "table_style": "TableStyleMedium2",
        "show_first_column": true,
        "show_last_column": true,
        "show_row_stripes": true,
        "show_column_stripes": false
    }`); err != nil {
        panic(err)
    }
}

func createPivot(f *excelize.File) {
    if err := f.AddPivotTable(&excelize.PivotTableOption{
        DataRange:       "Sheet1!$A$1:$C$12",
        PivotTableRange: "Sheet2!$A$3:$G$16",
        Rows: []excelize.PivotTableField{
            {Data: "Animal", DefaultSubtotal: true}},
        //Filter: []excelize.PivotTableField{{Data: "Date"}},
        Columns: []excelize.PivotTableField{
            {Data: "Number", DefaultSubtotal: true}},
        Data: []excelize.PivotTableField{
            {Data: "Date", Name: "Animals", Subtotal: "Count"}},
        RowGrandTotals: true,
        ColGrandTotals: true,
        ShowDrill:      true,
        ShowRowHeaders: true,
        ShowColHeaders: true,
        ShowLastColumn: true,
    }); err != nil {
        fmt.Println(err)
    }
}

func main() {
    rand.Seed(42)
    f := excelize.NewFile()
    // Write a random table in the 1st sheet.
    createTable(f, "Sheet1")
    // Create a new sheet.
    index := f.NewSheet("Sheet2")
    // Create a pivot table in the 2nd sheet.
    createPivot(f)
    // Set active sheet of the workbook.
    f.SetActiveSheet(index)
    // Save spreadsheet by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

Can you please add this feature? This is something I need along with pivot charts.

Thanks

[xuri]

Thanks for your issue, I have fixed it, please try to use the master branch code, and this patch will be released in the next version.

[Amodio]

Thank you for the quick fix! I am eager to generate pivot charts w/ timelines by coding :)