Answered How to get pivot table range using Closedxml worksheet?

[Sachin Patil]

I am writing code for get pivot table range using ClosedXML library. Using below code I am getting source data range but not getting table range. Eg.

IXLPivotTable xLPTable = this.CurrentWorksheet.PivotTable(tblName);

return xLPTable.SourceRange.ToString();

Is there other way to get pivot table range using ClosedXML?

 

[JohnH]

Do you mean TargetCell ?

 

[Sachin Patil]

Yes , I want Pivot table targeted cell range.

 

[JohnH]

The TargetCell property is type IXLCell, you would probably get Address or CurrentRegion from that.

 

[Sachin Patil]

If source data on Sheet1!A1:F12 and pivot table on Sheet2!A1:B8. The give code returning Sheet1!A1:F12 but I want Sheet2!A1:B8 this result. The Pivot table targeted range . I have attached file. PFA.

 

[JohnH]

Then look into the TargetCell property.

 

[Sachin Patil]

TargetCell property return only first cell address of table only not a table range

 

[JohnH]

I can't see another member that gives more information.

 

[JohnH]

In Office object model the PivotTable has a TableRange2 property with this information, the ClosedXml library doesn't have any such property.

I also looked into LastCellUsed and RangeUsed of sheet, but for a sheet with only a pivot table they return null. (they do work for sheet with regular content)

 

[hexo]

Any answer to this? I'm looking for the same functionality
Thanks