locked
How to handle large amount of data in Excel Web AddIn (task pane) RRS feed

  • Question

  • User-854857924 posted

    I've created an Excel Web AddIn (task pane)

    I have a dataset of 500,000 rows. When I use javascript it hangs. How can I quickly store the data and retrieve?

    function getValue(strA, strB){
    
    	if (strA == "aa" && strB == "bb") {return 82;}
    	if (strA == "bb" && strB == "aa") {return 82;}
    
    	if (strA == "cc" && strB == "dd") {return 83;}
    	if (strA == "dd" && strB == "cc") {return 83;}
    
    	if (strA == "ee" && strB == "dd") {return 85;}
    	if (strA == "dd" && strB == "ee") {return 85;}
    
    	if (strA == "ff" && strB == "bb") { return 90; }
    	if (strA == "bb" && strB == "ff") { return 90; }
    
    }
    Friday, August 12, 2016 1:47 PM

Answers

  • User269602965 posted

    Excel is not optimized for handling big data.

    Sure Excel XLSX can store 1,000,000 rows they say in documentation, but like MSACCESS office database, you can not practically reach the stated storage limits and expect it to be usable by you clients.

    So the more rows you add, the more likely performance will dramatically decline or even lock up the spreadsheet.

    I would use .NET Microsoft ACE.OLEDB 12.0 to query the database and insert directly into Excel XLSX sheet if you must transport data to Excel.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 13, 2016 11:10 PM

All replies

  • User-359936451 posted

    Well, why not load it to a database?

    Excel will handle it but it is limited by the amount of ram in the computer.

    time to handle it is based on the CPU and clock speed.

    javascript sucks for big data.

    Friday, August 12, 2016 3:57 PM
  • User-854857924 posted
    I can create a ms sql database and populate it with data .
    
    But how can I similarly retrieve the information from the database.
    
    Because I use an Excel Web AddIn is important that I can call the data from javascript.
    
    A concrete example would be fine.
    Friday, August 12, 2016 7:04 PM
  • User269602965 posted

    Excel is not optimized for handling big data.

    Sure Excel XLSX can store 1,000,000 rows they say in documentation, but like MSACCESS office database, you can not practically reach the stated storage limits and expect it to be usable by you clients.

    So the more rows you add, the more likely performance will dramatically decline or even lock up the spreadsheet.

    I would use .NET Microsoft ACE.OLEDB 12.0 to query the database and insert directly into Excel XLSX sheet if you must transport data to Excel.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 13, 2016 11:10 PM
  • User-854857924 posted

    On the basis of this article , I created a ms sql database, including the models and controllers.

    How can I populate the contents of the table Movie in Excel?

    This is the controller:

    namespace DatabaseWeb.Controllers
    {
        public class controllerMovie : Controller
        {
            private modelMovie db = new modelMovie();
    
            // GET: controllerMovie
            public ActionResult Index()
            {
                return View(db.Movies.ToList());
            }

    This is the javascript (not working)

    function loadSampleData() {
    
            var db = new Controllers.controllerMovie();
    
            var values = db.ActionResult;
    
            // Run a batch operation against the Excel object model
            Excel.run(function (ctx) {
                // Create a proxy object for the active sheet
                var sheet = ctx.workbook.worksheets.getActiveWorksheet();
                // Queue a command to write the sample data to the worksheet
                sheet.getRange("B3:D5").values = values;
    
                // Run the queued-up commands, and return a promise to indicate task completion
                return ctx.sync();
            })
            .catch(errorHandler);
        }

     

    Sunday, August 14, 2016 8:42 AM
  • User-359936451 posted

    You should post a new question to ask about getting data from the database. You question about Excel handling big data should be closed.

    There tons of examples on the web showing how to retrieve/ query data from a database, mySQL or others. Try and google for a few.

    Sunday, August 14, 2016 5:14 PM