Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Sun Sep 15, 2019 1:30 pm

hi,

when i create a pivot, save the file and open it again in the spire.spreadsheet
the pivot table is not shown.
If I open the file with excel, the pivot data are shown.
if I save the excelfile and open the file with spire.spreadsheet, then the pivot is shown.

could you please help me?
GH

sub CreateData
Dim workbook As New Workbook()
workbook.CreateEmptySheets(1)

Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = "Data"

'Set value of specified cell
sheet.Range("A1").Value = "KW"
sheet.Range("A2").NumberValue = 3
sheet.Range("A3").NumberValue = 2
sheet.Range("A4").NumberValue = 4
sheet.Range("A5").NumberValue = 4

sheet.Range("B1").Value = "Wert"
sheet.Range("B2").NumberValue = 3300
sheet.Range("B3").NumberValue = 2300
sheet.Range("B4").NumberValue = 4500
sheet.Range("B5").NumberValue = 6700


Dim DataRange As CellRange = sheet.Range("A1:B17")
Dim cache As PivotCache = workbook.PivotCaches.Add(DataRange)
Dim Tempsheet As Worksheet = workbook.CreateEmptySheet
Tempsheet.Name = "Pivot"

Dim pt As PivotTable = Tempsheet.PivotTables.Add("Pivot Table", Tempsheet.Range("A1"), cache)
Dim r1 = pt.PivotFields("KW")
r1.Axis = AxisTypes.Row
pt.Options.RowHeaderCaption = "KW"

pt.DataFields.Add(pt.PivotFields("Wert"), "Wert", SubtotalTypes.Sum)
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

workbook.CalculateAllValue()
workbook.SaveToFile(FieName, ExcelVersion.Version2010)
Spreadsheet1.LoadFromFile(FieName)

End Sub

GHWels
 
Posts: 90
Joined: Sun Nov 23, 2014 7:22 pm

Mon Sep 16, 2019 9:27 am

Hi,

I have solved the problem,

pt.CalculateData()

GH

GHWels
 
Posts: 90
Joined: Sun Nov 23, 2014 7:22 pm

Mon Sep 16, 2019 9:41 am

Hi,

Thanks for your reply and glad to hear that you have managed to solve the issue by yourself.
Any question, welcome to contact us.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Fri Dec 06, 2019 5:55 pm

I am able to create the Pivot Table in excel without any issues. But I am not able to add report filters into PivotTable .

Code:
//Add Pivot Table
PivotTable ptPercentage = sheet2.PivotTables.Add("PivotTable2", sheet2.Range["A1"], pivotCache);

//filters
PivotReportFilter f1 = new PivotReportFilter("Terminated Within Eval Month", true);
ptPercentage.ReportFilters.Add(f1);
PivotReportFilter f2 = new PivotReportFilter("Terminated Outside Eval Month Not Submitted", true);
ptPercentage.ReportFilters.Add(f2);

//Even after adding two filters, the ptPercentage.ReportFilters.Count shows 0.

I am using the Nuget Package "FreeSpire.XLS" version 9.10.11.
<package id="FreeSpire.XLS" version="9.10.11" targetFramework="net40" />

sthakuri
 
Posts: 2
Joined: Fri Dec 06, 2019 5:31 pm

Mon Dec 09, 2019 2:28 am

Hi,

Thanks for your inquiry. Sorry to reply late for weekend.
I firstly suggest you try to use the latest commercial version of Spire.XLS Pack(Hotfix) Version:9.11.14 which has more improvements than free version.

If the issue still exists after trying, to help us investigate it accurately, please offer us the following information.
1. Your input Excel file(if any).
2. The complete code you were using which could reproduce your issue directly.
3. Your desired result file.

You could upload them here or send us(support@e-iceblue.com) via email.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Return to Spire.XLS