Sort and sum on a massive spreadsheet

[Gordon Hooton]

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]

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];

pivotSheet.Activate();

//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]

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.

https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents

Please let me know if you have any questions.

Regards,
Vikas