Pivot Table - Save first and Enable Show Details

[ppdevs]

I tried to create a worksheet with the data I need for a pivot table on another sheet in the same workbook. The pivot table does not show. But if I save the wb to memory stream after I create the data and then load it again to then create the pivot table, it works. Is this a bug or by design?

Secondly, I cannot find the setting to Enable Show Details in the pivot table options data. Does anyone know the setting?

[Gary.zhang]

Hello,

Thanks for your inquiry. After an initial test with the latest Spire.Office hotfix(Spire.Office Platinum (Hot Fix) Version:2.15.5) along with the following codes, I have not managed to reproduce the issue you mentioned on my end, so please attach the codes you were trying for investigation further.

Code: Select all

Workbook book = new Workbook();
book.Worksheets.Clear();
 //add data
Worksheet dataSheet = book.Worksheets.Add("Data");
dataSheet.Range["A1"].Value = "Product";
dataSheet.Range["A2"].Value = "Spire";
dataSheet.Range["B1"].Value = "Month";
dataSheet.Range["B2"].Value = "January";
dataSheet.Range["C1"].Value = "Count";
dataSheet.Range["C2"].Value2 = 10;
//create pt
Worksheet ptSheet = book.Worksheets.Add("Pt");
PivotCache pc = book.PivotCaches.Add(dataSheet["A1:C3"]);
PivotTable pt = ptSheet.PivotTables.Add("PivotTable", ptSheet.Range["B2"], pc);
PivotField field1= pt.PivotFields["Product"] as PivotField;
field1.Axis = AxisTypes.Row;
PivotField field2 = pt.PivotFields["Month"] as PivotField;
field2.Axis = AxisTypes.Row;
IPivotField fieldItemsTotal = pt.PivotFields["Count"];
pt.DataFields.Add(fieldItemsTotal, "Sum Of Count", SubtotalTypes.Sum);
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark14;
//Enable Show Details in the pivot table options data
pt.EnableDrilldown = true;
book.SaveToFile("CreatePT.xlsx",ExcelVersion.Version2010);


Secondly, the EnableDrilldown property for PivotTable represents the setting to Enable Show Details in the pivot table options data.
Sincerely,
Gary
E-iceblue support team

[ppdevs]

This code (with some of the data-fill taken out) does not work

Code: Select all

                Workbook wb = new Workbook();
                Worksheet pivotWs = wb.Worksheets[0];
                pivotWs.Name = "Pivot";
                Worksheet rosterWS = wb.Worksheets[1];
                rosterWS.Name = "Roster";
                wb.Worksheets.Remove(2);
                rosterWS.Range[1, 1].Value = "Emp No";
                rosterWS.Range[1, 2].Value = "Last Name";
                rosterWS.Range[1, 3].Value = "First Name";
                rosterWS.Range[1, 4].Value = "Department";
                rosterWS.Range[1, 5].Value = "Job Title";
                rosterWS.Range[1, 6].Value = "Date Completed";
                rosterWS.Range[1, 7].Value = "Complete";
                rosterWS.Range[1, 8].Value = "LOA";
                rosterWS.Range[1, 9].Value = "Non-Patient Care";
                rosterWS.Range[1, 10].Value = "Needs Training";
                rosterWS.Range[1, 11].Value = "Trained";
                int rownumber = 2;
                foreach (FacilityStaff staff in staffs)
                {
                    //fill in the column values from data - left out for posting in forum
                    rownumber++;
                }
                CellRange rosterRange = rosterWS.Range[1, 1, rownumber - 1, 7];
                PivotCache cache = wb.PivotCaches.Add(rosterRange);
                XlsPivotTable pt = pivotWs.PivotTables.Add(facilityName + " Pivot Table", pivotWs.Range["B5"], cache);
                var r1 = pt.PivotFields["Department"];
                r1.Axis = AxisTypes.Row;
                pt.Options.RowHeaderCaption = "Department";
                pt.DataFields.Add(pt.PivotFields["Last Name"], "Total", SubtotalTypes.Count);

                pt.EnableDrilldown = true;
                wb.SaveToFile(@"test.xlsx", FileFormat.Version2013);


The identical code but saving to the memory stream first before the pivot table works

Code: Select all

                Workbook wb = new Workbook();
                Worksheet pivotWs = wb.Worksheets[0];
                pivotWs.Name = "Pivot";
                Worksheet rosterWS = wb.Worksheets[1];
                rosterWS.Name = "Roster";
                wb.Worksheets.Remove(2);
                rosterWS.Range[1, 1].Value = "Emp No";
                rosterWS.Range[1, 2].Value = "Last Name";
                rosterWS.Range[1, 3].Value = "First Name";
                rosterWS.Range[1, 4].Value = "Department";
                rosterWS.Range[1, 5].Value = "Job Title";
                rosterWS.Range[1, 6].Value = "Date Completed";
                rosterWS.Range[1, 7].Value = "Complete";
                rosterWS.Range[1, 8].Value = "LOA";
                rosterWS.Range[1, 9].Value = "Non-Patient Care";
                rosterWS.Range[1, 10].Value = "Needs Training";
                rosterWS.Range[1, 11].Value = "Trained";
                int rownumber = 2;
                foreach (FacilityStaff staff in staffs)
                {
                    //fill in the column values from data - left out for posting in forum
                    rownumber++;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.SaveToStream(ms, FileFormat.Version2013);
                    wb.LoadFromStream(ms);
                    pivotWs = wb.Worksheets[0];
                    rosterWS = wb.Worksheets[1];
                }
                CellRange rosterRange = rosterWS.Range[1, 1, rownumber - 1, 7];
                PivotCache cache = wb.PivotCaches.Add(rosterRange);
                XlsPivotTable pt = pivotWs.PivotTables.Add(facilityName + " Pivot Table", pivotWs.Range["B5"], cache);
                var r1 = pt.PivotFields["Department"];
                r1.Axis = AxisTypes.Row;
                pt.Options.RowHeaderCaption = "Department";
                pt.DataFields.Add(pt.PivotFields["Last Name"], "Total", SubtotalTypes.Count);

                pt.EnableDrilldown = true;
                wb.SaveToFile(@"test.xlsx", FileFormat.Version2013);


I am on the latest version of Spire Office.NET from Nuget

[Gary.zhang]

Hello,

Thanks for the detailed information. I have managed to reproduce the issue on my side and posted it to our Dev team for investigation further, you will be notified once it is resolved or there are some other update.
As a temporary workaround, you could specify the version to avoid the case when initializing the workbook object like as the following code.

Code: Select all

Workbook wb = new Workbook();
wb.Version = ExcelVersion.Version2013;


Sincerely,
Gary
E-iceblue support team

[Gary.zhang]

Hello,

Thanks for your waiting. Glad to inform you that the issue has get resolved in the latest hotfix(Spire.Office Platinum (Hot Fix) Version:2.15.8).

Sincerely,
Gary
E-iceblue support team