Is there a limit to the amount of data that can be held in a SpreadsheetGear WorkbookView?

Question

I have an application that takes a table of data from Excel, and imports it into my embedded SpreadsheetGear.WorkbookView. I am aware of the limitations to the size of a worksheet in Excel, but I was wondering if Excel is able to handle sets of data that is larger than SpreadsheetGear. Has anyone ever run into this before?

Jake

Answer 1

I just did a test with SpreadsheetGear 2012 for .NET running in a .NET 4.5 x64 application and Excel 2013 x64 RTM both running on Windows 8 x64 with an overclocked Core i7-980X.

I created a workbook with 10 million formulas (1,000,000 rows and 10 columns using the formula "=RAND()").

Excel 2013 created the workbook in 20.18 seconds, used 795MB of RAM and calculated in 0.39 seconds.

SpreadsheetGear 2012 created the workbook in 0.42 seconds, used 153MB of RAM and calculated in 0.09 seconds.

SpreadsheetGear is limited only by the amount of memory and is obviously more efficient than Excel 2013 when it comes to memory usage, not to mention the fact that SpreadsheetGear 2012 creates and calculates large workbooks faster than Excel 2013 - at least in this case.

You can download the free evaluation of SpreadsheetGear for .NET here.

Disclaimer: I own SpreadsheetGear LLC.

Here is the code I ran for SpreadsheetGear:

    using System;
    using SpreadsheetGear;

    namespace SpreadsheetGearMemTest
    {
        class Program
        {
            static void Test(IWorkbook workbook, int rows, int cols)
            {
                var worksheet = workbook.Worksheets[0];
                var cells = worksheet.Cells;
                var timer = System.Diagnostics.Stopwatch.StartNew();
                var startMem = System.GC.GetTotalMemory(true);
                cells[0, 0, rows - 1, cols - 1].Formula = "=RAND()";
                timer.Stop();
                var memUsed = System.GC.GetTotalMemory(true) - startMem;
                var calcTimer = System.Diagnostics.Stopwatch.StartNew();
                workbook.WorkbookSet.Calculate();
                Console.WriteLine("Creating took {0} seconds and {1}MB, calc took {2} seconds.",
                    timer.Elapsed.TotalSeconds, memUsed / (1024.0 * 1024.0), calcTimer.Elapsed.TotalSeconds);
                workbook.Close();
            }

            static void Main(string[] args)
            {
                // Get the code JITed.
                Test(Factory.GetWorkbook(), 100, 10);
                // Do the test.
                Test(Factory.GetWorkbook(), 1000000, 10);
            }
        }
    }