Populating FarPoint Spread with huge chunk of Data (64-bit Spreadsheet Issue)

Question

In C# 64-bit, i am trying to populate a FarPoint Spreadsheet with approx 70000 rows. The entire data gets loaded on spredsheet after taking 3-4 hours of time duration, which makes the entire process to have lot of performance issues.

Currently i am populating the data to spreadsheet by individual cells.Is there anything i can do in order to increase the performance of this issue i am facing??

Below is my code template to populate the spreadsheet by individual cells.

Public void PopulateSpreadsheet()
{
            FarPoint.Win.Spread.FpSpread SS;

            SS.SuspendLayout();
            int i = 0;
            int Rows = 70000;

            while( i < Rows)
                {
                  SS.ActiveSheet.ActiveCell.Text = Data to populate;
                }

            SS.ResumeLayout();
}

Please guide me how to improve the performance. Any Help is appreciated!! Thank You in Advance :)

Answer 1

Well initially, I was storing the data directly on the spreadsheet which would take lot of time. To overcome this issue, I first stored my data to a datatable and then make this datatable as data source to the spreadsheet.

Answer 2

Put the data into an object array and use the .SetArray property. This is extremely fast and also allows different data types in the array.

I use this often, especially when the data come from Sql.