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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Sort and sum on a massive spreadsheet

Thread ID:





119354 Jun 10,2015 07:44 AM UTC Jun 11,2015 12:58 PM UTC ASP.NET Web Forms (Classic) 2
Tags: XlsIO
Gordon Hooton
Asked On June 10, 2015 07:44 AM UTC

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

Gordon Hooton
Replied On 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 





Vikas Sekar [Syncfusion]
Replied On 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.



This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon