Pivot Table : System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.

Question

We are using Syncfusion.XlsIO and Syncfusion.XlsIO.Implementation.PivotTables for Generating reports.

When i tried to create the pivot table I received the following error, and this only appears when the sheet of my book only had headers, I just want to know if there is any way to avoid creating the pivot table if the workbook has only one row (headers ), or create the pivot table only with the sheet headers ?

Abirami Varadharajan · Answer

Hi Jorge, 

Thank you for contacting Syncfusion support. 

As per the Microsoft Excel behavior, it is not possible to create pivot table with only one row of data and hence the exception is thrown. So, it is recommended to have two or more rows of data to create pivot table. Please add below code as condition check to create pivot table when the rows length of used range is greater than 1. 

Code Sample: 




if (worksheet.UsedRange.Rows.Length > 1) 
{ 
    var cache = workbook.PivotCaches.Add(worksheet.UsedRange); 
} 

Regards, 
Abirami.