We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Sort and sum on a massive spreadsheet

I have a spreadsheet that is massive, over 600,000 records and am looking at the best way to handle this with XLSIO (in ASP.NET).
One approach is to export the sheet into a datatable and use that, but I wonder if a better way is to use some "sort and sum" approach to consolidate the data.
The data consists of phone numbers, and all the cost of all the calls made by those numbers, then all the other data (duration, type etc).  So what I am trying to do is get the totals for each number (essentially a pivot table)
Can anyone advise the best approach to this task in XlsIO

Thanks and Regards

2 Replies

GH Gordon Hooton June 10, 2015 10:41 PM UTC

Here is the sort of code I am using (mainly using the example)

IWorkbook sourcebook = application.Workbooks.Open("C:\\Vo1\\Vo1.xls", ExcelOpenType.Automatic);

IWorksheet sourcesheet = sourcebook.Worksheets[0];

int rowrange = sourcesheet.Columns.Length;

// only interested in three columns

IRange sourcerange = sourcesheet[1, 8, sourcesheet.Rows.Length, 10];

//add the pivot sheet

IWorksheet pivotSheet = sourcebook.Worksheets[1];


//Select the data to add in cache

IPivotCache cache = sourcebook.PivotCaches.Add(sourcesheet[1, 8, sourcesheet.Rows.Length, 10]);

//Insert the pivot table.

IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);

// Here is where I start having issues with what to do

pivotTable.Fields[1].Axis = PivotAxisTypes.Row;

IPivotField field = pivotSheet.PivotTables[0].Fields[2];

pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);

Then copy the pivot table as values to a new workbook and close this one

Any snippets that would help with this - thanks 





VS Vikas Sekar Syncfusion Team June 11, 2015 12:58 PM UTC

Hi Gordon,

Thanks for using Syncfusion products.

Currently we don’t have support to copy values from pivot tables in XlsIO. We have logged a feature report regarding this. A support incident to track the status of this feature has been created under your account. Please log on to our support website to check for further updates.


Please let me know if you have any questions.


Live Chat Icon For mobile
Up arrow icon