locked
C# ReoGrid performance issue when binding datatable with formula RRS feed

  • Question


  • Here i am asking about 3rd party component but please do not avoid to answer. if possible see my code and tell me is there anything in my code which causing slow loading of data in ReoGrid. please read my explanation.

    I have populated my data table with random values and formula like =SUM(A1:A2) when there is 150 columns and 4000 rows in datatable then ReoGrid taking approx 20 minute to be populated.

    when i am populating ReoGrid without formula then it takes few second to populate ReoGrid but the moment many SUM formulas are being assigned to cell dynamically then ReoGrid take long time to populated.

    Here i am sharing my code. so my request please guys see my code logic and tell me is there any flaw when assigning formula to many cell of ReoGrid.

    please tell me how could i simplify the logic to populate grid with formula. Is there way to increase the performance of my code with formula as a result ReoGrid can be populated with in few sec or few minute.

    Looking for suggestion and guide line. ReoGrid is free component for winform application https://reogrid.net/.


    Here is my code
    private void btnBindTable_Click(object sender, EventArgs e)
    {
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
    
        string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
        int startsum = 0;
        int currow = 0;
        bool firstTimeSum = true;
    
        int NumRows = 4000;
        int NumColumns = 150;
    
        var sheet = reoGrd.CurrentWorksheet;
        sheet.Resize(NumRows, NumColumns);  // resize 
    
        DataTable dt = new DataTable();
    
        for (int col = 0; col < NumColumns; col++)
        {
            strColName = GenerateColumnText(col);
            DataColumn datacol = new DataColumn(strColName, typeof(string));
            dt.Columns.Add(datacol);
        }
    
    
        for (int row = 0; row < NumRows; row++)
        {
            dt.Rows.Add();
    
            for (int col = 0; col < NumColumns; col++)
            {
                if (row < 2)
                {
                    dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2"); 
                }
                else
                {
                    if (firstTimeSum)
                    {
                        if (row - currow == 2)
                        {
                            currow = row;
                            startsum = 0;
                            firstTimeSum = false;
                        }
                        else
                        {
                            startsum = 1;
                        }
                    }
                    else
                    {
                        if (row - currow == 3)
                        {
                            currow = row;
                            startsum = 0;
                        }
                    }
    
    
                    if (startsum == 0)
                    {
                        strColName = GenerateColumnText(col);
                        strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                        strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                        dt.Rows[row][col] = strSum; 
    
                        string cellname = GenerateColumnText(col) + (row + 1).ToString();
                        var cell = sheet.Cells[cellname];
                        cell.Style.BackColor = Color.LightGoldenrodYellow;
                    }
                    else
                    {
                        dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2"); 
                    }
                }
    
            }
    
            startsum = 1;
        }
    
        sheet["A1"] = dt;
    
        stopwatch.Stop();
        TimeSpan timeSpan = stopwatch.Elapsed;
    
        MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
    
    }
    

    Thanks

    Tuesday, November 20, 2018 3:11 PM

Answers

  • Now i did this way and now data loading becomes much faster.

    Reogrid1        
            
            // Cell populate in loop with sheet.SuspendFormulaReferenceUpdates();
            private void button1_Click(object sender, EventArgs e)
            {
                string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            sheet[row, col] = new Random().Next(1, NumRows).ToString();
                            //var xval = GenerateUniqueNumber(1, NumRows);
    
                            //sheet[row, col] = new Random().Next(1, 100).ToString("D2");
                            //sheet[row, col] = GenerateUniqueNumber(1, NumRows);
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                sheet[row, col] = strSum;
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                //var xval = GenerateUniqueNumber(1, NumRows);
                                sheet[row, col] = new Random().Next(1, NumRows).ToString("D2");
                                //string  uniqueVal= GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = uniqueVal;
    
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                sheet.ResumeFormulaReferenceUpdates();
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
            }
    
            //table bind with sheet.SuspendFormulaReferenceUpdates();
            private void button2_Click(object sender, System.EventArgs e)
            {
    
                string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
                DataTable dt = new DataTable();
    
                for (int col = 0; col < NumColumns; col++)
                {
                    strColName = GenerateColumnText(col);
                    DataColumn datacol = new DataColumn(strColName, typeof(string));
                    dt.Columns.Add(datacol);
                }
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    dt.Rows.Add();
    
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2");
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                dt.Rows[row][col] = strSum;
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2");
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
    
                sheet["A1"] = dt;
                sheet.ResumeFormulaReferenceUpdates();
                //sheet.Recalculate();
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
    
            }
    
            //range bind with sheet.SuspendFormulaReferenceUpdates();
            private void button3_Click(object sender, System.EventArgs e)
            {
                string strSum = "", strColName="", strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
                var data = new object[NumRows, NumColumns];
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            data[row, col] = new Random().Next(1, NumRows).ToString();
    
                            //sheet[row, col] = new Random().Next(1, NumRows).ToString();
                            //var xval = GenerateUniqueNumber(1, NumRows);
    
                            //sheet[row, col] = new Random().Next(1, 100).ToString("D2");
                            //sheet[row, col] = GenerateUniqueNumber(1, NumRows);
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                //sheet[row, col] = strSum;
                                data[row, col] = strSum;
    
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                data[row, col] = new Random().Next(1, NumRows).ToString();
                                //var xval = GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = new Random().Next(1, NumRows).ToString("D2");
                                //string  uniqueVal= GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = uniqueVal;
    
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                //var range = new unvell.ReoGrid.RangePosition(0, 0, NumRows, NumColumns);
                var rangename="A1:"+strColName+NumRows.ToString();
                var range = sheet.Ranges[rangename];
                range.Data = data;
                sheet.ResumeFormulaReferenceUpdates();
    
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
            }
    
            private string GenerateColumnText(int num)
            {
                string str = "";
                char achar;
                int mod;
                while (true)
                {
                    mod = (num % 26) + 65;
                    num = (int)(num / 26);
                    achar = (char)mod;
                    str = achar + str;
                    if (num > 0) num--;
                    else if (num == 0) break;
                }
                return str;
            }
    • Marked as answer by Sudip_inn Sunday, November 25, 2018 6:19 PM
    Thursday, November 22, 2018 1:49 PM

All replies

  • Hello,

    Rather than time the entire operation, time things like in the for statement how much time does it take to do say 50 percent of the operation then the next 50 percent. Does the last 50 percent take long than the first 50 percent?

    Look through the grid's properties, is there a property to turn off screen updates during the processing shown? A DataGridView does so perhaps this grid does.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, November 21, 2018 1:19 AM
  • Sorry not very clear what you said.

    Reo grid is getting slow when i am inserting formula into many cell dynamically. if there is not formula then huge data population is also not taking long time.

    so looking for advise.

    Wednesday, November 21, 2018 7:51 PM
  • Now i did this way and now data loading becomes much faster.

    Reogrid1        
            
            // Cell populate in loop with sheet.SuspendFormulaReferenceUpdates();
            private void button1_Click(object sender, EventArgs e)
            {
                string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            sheet[row, col] = new Random().Next(1, NumRows).ToString();
                            //var xval = GenerateUniqueNumber(1, NumRows);
    
                            //sheet[row, col] = new Random().Next(1, 100).ToString("D2");
                            //sheet[row, col] = GenerateUniqueNumber(1, NumRows);
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                sheet[row, col] = strSum;
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                //var xval = GenerateUniqueNumber(1, NumRows);
                                sheet[row, col] = new Random().Next(1, NumRows).ToString("D2");
                                //string  uniqueVal= GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = uniqueVal;
    
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                sheet.ResumeFormulaReferenceUpdates();
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
            }
    
            //table bind with sheet.SuspendFormulaReferenceUpdates();
            private void button2_Click(object sender, System.EventArgs e)
            {
    
                string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
                DataTable dt = new DataTable();
    
                for (int col = 0; col < NumColumns; col++)
                {
                    strColName = GenerateColumnText(col);
                    DataColumn datacol = new DataColumn(strColName, typeof(string));
                    dt.Columns.Add(datacol);
                }
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    dt.Rows.Add();
    
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2");
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                dt.Rows[row][col] = strSum;
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2");
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
    
                sheet["A1"] = dt;
                sheet.ResumeFormulaReferenceUpdates();
                //sheet.Recalculate();
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
    
            }
    
            //range bind with sheet.SuspendFormulaReferenceUpdates();
            private void button3_Click(object sender, System.EventArgs e)
            {
                string strSum = "", strColName="", strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
                var data = new object[NumRows, NumColumns];
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            data[row, col] = new Random().Next(1, NumRows).ToString();
    
                            //sheet[row, col] = new Random().Next(1, NumRows).ToString();
                            //var xval = GenerateUniqueNumber(1, NumRows);
    
                            //sheet[row, col] = new Random().Next(1, 100).ToString("D2");
                            //sheet[row, col] = GenerateUniqueNumber(1, NumRows);
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                //sheet[row, col] = strSum;
                                data[row, col] = strSum;
    
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                data[row, col] = new Random().Next(1, NumRows).ToString();
                                //var xval = GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = new Random().Next(1, NumRows).ToString("D2");
                                //string  uniqueVal= GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = uniqueVal;
    
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                //var range = new unvell.ReoGrid.RangePosition(0, 0, NumRows, NumColumns);
                var rangename="A1:"+strColName+NumRows.ToString();
                var range = sheet.Ranges[rangename];
                range.Data = data;
                sheet.ResumeFormulaReferenceUpdates();
    
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
            }
    
            private string GenerateColumnText(int num)
            {
                string str = "";
                char achar;
                int mod;
                while (true)
                {
                    mod = (num % 26) + 65;
                    num = (int)(num / 26);
                    achar = (char)mod;
                    str = achar + str;
                    if (num > 0) num--;
                    else if (num == 0) break;
                }
                return str;
            }
    Thursday, November 22, 2018 1:49 PM
  • Now i did this way and now data loading becomes much faster.

    Reogrid1        
            
            // Cell populate in loop with sheet.SuspendFormulaReferenceUpdates();
            private void button1_Click(object sender, EventArgs e)
            {
                string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            sheet[row, col] = new Random().Next(1, NumRows).ToString();
                            //var xval = GenerateUniqueNumber(1, NumRows);
    
                            //sheet[row, col] = new Random().Next(1, 100).ToString("D2");
                            //sheet[row, col] = GenerateUniqueNumber(1, NumRows);
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                sheet[row, col] = strSum;
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                //var xval = GenerateUniqueNumber(1, NumRows);
                                sheet[row, col] = new Random().Next(1, NumRows).ToString("D2");
                                //string  uniqueVal= GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = uniqueVal;
    
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                sheet.ResumeFormulaReferenceUpdates();
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
            }
    
            //table bind with sheet.SuspendFormulaReferenceUpdates();
            private void button2_Click(object sender, System.EventArgs e)
            {
    
                string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
                DataTable dt = new DataTable();
    
                for (int col = 0; col < NumColumns; col++)
                {
                    strColName = GenerateColumnText(col);
                    DataColumn datacol = new DataColumn(strColName, typeof(string));
                    dt.Columns.Add(datacol);
                }
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    dt.Rows.Add();
    
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2");
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                dt.Rows[row][col] = strSum;
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                dt.Rows[row][col] = new Random().Next(1, NumRows).ToString("D2");
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
    
                sheet["A1"] = dt;
                sheet.ResumeFormulaReferenceUpdates();
                //sheet.Recalculate();
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
    
            }
    
            //range bind with sheet.SuspendFormulaReferenceUpdates();
            private void button3_Click(object sender, System.EventArgs e)
            {
                string strSum = "", strColName="", strImmediateOneUp = "", strImmediateTwoUp = "";
    
                int NumRows = 4000;
                int NumColumns = 150;
    
                int startsum = 0;
                int currow = 0;
                bool firstTimeSum = true;
                var data = new object[NumRows, NumColumns];
    
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
    
                var sheet = reoGrd.CurrentWorksheet;
                sheet.SuspendFormulaReferenceUpdates();
                sheet.Resize(NumRows, NumColumns);  // resize 
    
    
                for (int row = 0; row < NumRows; row++)
                {
                    for (int col = 0; col < NumColumns; col++)
                    {
                        if (row < 2)
                        {
                            data[row, col] = new Random().Next(1, NumRows).ToString();
    
                            //sheet[row, col] = new Random().Next(1, NumRows).ToString();
                            //var xval = GenerateUniqueNumber(1, NumRows);
    
                            //sheet[row, col] = new Random().Next(1, 100).ToString("D2");
                            //sheet[row, col] = GenerateUniqueNumber(1, NumRows);
                        }
                        else
                        {
                            if (firstTimeSum)
                            {
                                if (row - currow == 2)
                                {
                                    currow = row;
                                    startsum = 0;
                                    firstTimeSum = false;
                                }
                                else
                                {
                                    startsum = 1;
                                }
                            }
                            else
                            {
                                if (row - currow == 3)
                                {
                                    currow = row;
                                    startsum = 0;
                                }
                            }
    
    
                            if (startsum == 0)
                            {
                                strColName = GenerateColumnText(col);
                                strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                                strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                                strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                                //sheet[row, col] = strSum;
                                data[row, col] = strSum;
    
    
                                string cellname = GenerateColumnText(col) + (row + 1).ToString();
                                var cell = sheet.Cells[cellname];
                                cell.Style.BackColor = Color.LightGoldenrodYellow;
                            }
                            else
                            {
                                data[row, col] = new Random().Next(1, NumRows).ToString();
                                //var xval = GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = new Random().Next(1, NumRows).ToString("D2");
                                //string  uniqueVal= GenerateUniqueNumber(1, NumRows);
                                //sheet[row, col] = uniqueVal;
    
                            }
                        }
    
                    }
    
                    startsum = 1;
                }
    
                //var range = new unvell.ReoGrid.RangePosition(0, 0, NumRows, NumColumns);
                var rangename="A1:"+strColName+NumRows.ToString();
                var range = sheet.Ranges[rangename];
                range.Data = data;
                sheet.ResumeFormulaReferenceUpdates();
    
    
                stopwatch.Stop();
                TimeSpan timeSpan = stopwatch.Elapsed;
    
                MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
            }
    
            private string GenerateColumnText(int num)
            {
                string str = "";
                char achar;
                int mod;
                while (true)
                {
                    mod = (num % 26) + 65;
                    num = (int)(num / 26);
                    achar = (char)mod;
                    str = achar + str;
                    if (num > 0) num--;
                    else if (num == 0) break;
                }
                return str;
            }
    • Marked as answer by Sudip_inn Sunday, November 25, 2018 6:19 PM
    Thursday, November 22, 2018 1:49 PM