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.

Wed Aug 02, 2017 4:48 pm

Hi,

I have a pivot table data in sheet1. I would like to read the value of particular cell. But I couldn't. Cell values of the pivot table always returning null. How can I get the cell value from pivot table?

Thanks

soloman
 
Posts: 3
Joined: Mon Jul 31, 2017 9:34 pm

Thu Aug 03, 2017 2:10 am

Hello,

Thanks for your inquiry. Please refer the following method to get the cell value from pivot table.
Code: Select all
foreach (Worksheet ptSheet in workbook.Worksheets)
        {
            foreach (XlsPivotTable pt in ptSheet.PivotTables)
            {
                IXLSRange loc = pt.Location;
                for (int i = loc.Row; i <= loc.LastRow; i++)
                {
                    for (int j = loc.Column; j <= loc.LastColumn; j++)
                    {
                        IXLSRange cell = ptSheet.Range[i, j];
                        string value = cell.Value;
                    }
                }
            }
        }

Please contact us if you have any questions.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Thu Aug 03, 2017 1:47 pm

Sample.zip
Sample excel file
(424.55 KiB) Downloaded 284 times
Gary,

Thanks for your reply. I used 7.9 version of free spire.xls. I used the following code to access the cell value in a pivot table. Still I am getting blank. But when I open the sheet the values are there.

Dim workbook As New Workbook()
workbook.LoadFromFile("C:\Excel\RCCF Performance Data For Week 29.xlsx")

Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = "Data Source"
Dim sheet2 As Worksheet = workbook.CreateEmptySheet()
sheet2.Name = "Pivot Table"
'
Dim dataRange As CellRange = sheet.Range("A7:U2523")
Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)
Dim pt As PivotTable = sheet2.PivotTables.Add("Pivot Table", sheet.Range("A1"), cache)
'
'pt.ManualUpdate = True
Dim r1 = pt.PivotFields("REQ_DIV_DESC")
r1.Axis = AxisTypes.Row
pt.Options.RowHeaderCaption = "Division"


Dim r2 = pt.PivotFields("REQ_SUB_DIV_DESC")
r2.Axis = AxisTypes.Row

Dim r3 = pt.PivotFields("CRE_ROLE_ID")
r3.Axis = AxisTypes.Column
'
pt.DataFields.Add(pt.PivotFields("CRE_ROLE_ID"), "Sum of Sep", SubtotalTypes.Count)

workbook.Save()

For Each ptSheet As Worksheet In workbook.Worksheets
For Each point As XlsPivotTable In ptSheet.PivotTables
Dim loc As IXLSRange = point.Location
Dim i As Integer
For i = loc.Row To loc.LastRow Step i + 1
Dim j As Integer
For j = loc.Column To loc.LastColumn Step j + 1
Dim cell As IXLSRange = ptSheet.Range(i, j)
Dim value As String = cell.Value
Next
Next
Next

Next

soloman
 
Posts: 3
Joined: Mon Jul 31, 2017 9:34 pm

Thu Aug 03, 2017 3:11 pm

Gary,

I created the pivot table. I need to access the cell value once the workbook got saved in run time. The data is blank when you access the pivot table cell after creating the pivot table.(before open manually in excel)

After I opened the workbook and saved it using excel. Now when I access the pivot table cell, the data is available.

It means excel is slicing the pivot table data when the file is opened. Is it not available once the pivot table created?
Attachments
Test.zip
(496.84 KiB) Downloaded 277 times

soloman
 
Posts: 3
Joined: Mon Jul 31, 2017 9:34 pm

Fri Aug 04, 2017 1:59 am

Hello,

Thanks for your response. When you save it using excel, the pivot table has been calculated, so you can access the pivot table cell, but when you create the pivot table in runtime, it is not calculated, for this case, our product provides a method as following to calculate it in runtime.
Code: Select all
XlsPivotTable.CalculateData();

Nevertheless, all the fixes and new features/enhancements are implemented in commercial verison, so if you would like to use this method, you have to upgrade to our commercial version, which download link is as below.
https://www.e-iceblue.com/Download/down ... t-now.html

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Return to Spire.XLS