Javascript to Excel:由于缺少“范围"而导致性能不佳方法

问题描述

考虑到仅使用Internet Explorer(在大型企业中通常是这种情况)，您得出的结论是，您可以为用户提供将HTML Gridview转换为Excel文件的可能性:此外，您仅认为自己拥有在用户要求的范围内进行操作...

我最初从外部将表(或数组)写入Excel的经验是使用Visual Basic(.net的服务器端).所以我写了一个类来将VB 2D数组传播到Excel工作表中:

1)我使用的第一种方法乍看起来似乎不错，而且很简单:在行循环内的列上设置循环，然后将VB数组中的每个值写入Excel工作表.就这样:

  Dim j，k为整数Dim Valeur饰演String'让我们假设VB 2D数组是"Tab(10,20)"，包含11行21列字符串'让我们假设Excel范围的左上角是第7行，第3列:对于j = 0到10对于k = 0到20Valeur = Tab(j，k)单元格(j + 7，k + 3).值= valeur下一个下一个 

2)对上述方法进行了编程后，我最终意识到，尽管它简单且合乎逻辑，但它却是一个糟糕的方法，原因仅在于以下一个原因:它非常慢！因此，我找到了一种更好的方法，包括一次写入整个单元格范围:

  Feuil.Range("C7:W17").Value =制表符 

第一种方法和第二种方法有什么区别?好的，这两种方法都可以正常工作，但是如果您考虑一个由300行，20列(或6000个单元格)组成的表，则第一种方法花费的时间将约为1分钟，而第二种方法仅花费半秒！>

因此，谈到JavaScript时，我尝试了相同的方法:首先，打开一个Excel-ActiveX对象(当然，在MS-IE中)，该对象可以访问Excel对象模型参考(http://msdn.microsoft.com/en-us/library/bb149081(v = office.12).aspx)，我可以使用在称为"ap"的对象中设置的CSV堆栈，如下所述应用第一种方法:

 //'ap'是一个Javascript对象，其中包含CSV字符串表(自然索引:1..n)var valCell;var lim = ap.nbChamps;//'nbChamps'='ap'对象中的csv列数var curLig = 6;//将数据写入第7行及以下for(var j = 1; j< = ap.nbelem; j ++)//ap.nbelem ='ap'中的行数{//行循环curLig + = 1;for(var k = 1; k< = lim; k ++)//N.B:'ap'使用自然索引:1到n而不是0到n-1{//列循环valCell = ap.litEnLC(j，k);//'ap'方法从第j行读取列kclasseur.ActiveSheet.Cells(curLig，k + 2).value = valCell;}} 

使用该方法后，我再次发现了同样的慢度:因此，我想将第二种方法转换为javascript，如下所示:.首先，向"ap"对象添加一个方法，该方法将CSV堆栈转换为Javascript 2D数组.其次，就像在Visual Basic中一样，将此2D数组写入"range"对象.所以:

 //区域"是范围.例如:zone ="C7:W17"var biTab = ap.pcttEnTab();//将CSV堆栈转换为2D数组classeur.ActiveSheet.Range(zone).value = biTab; 

而且，正如预期的那样，这样做的速度要快得多，但问题是它实际上并不起作用，因为而不是将2D数组的每个值传输到Excel范围的对应单元格中，而是Excel范围内每个单元中的整个Javascript 2D数组！

问题是:如何一次将Javascript 2D数组写入Excel范围?

感谢任何能给我答案的人...

顺便说一句，我添加了pcttEnTab方法，以防有人可以用该方法更好地解释我的问题:

  this.pcttEnTab = function()//数组(表格à2维){//将PCTT转换为表格à二维var j，k，s;var biTab = new Array();for(k = 0; k< this.nbelem; k ++)//为每一行创建一个列数组{biTab [k] = new Array();}for(j = 1; j< = this.nbelem; j ++)//行循环(自然索引){for(k = 1; k< = this.nbChamps; k ++)//列循环{s = this.litEnLC(j，k);biTab [j-1] [k-1] = s;}}返回biTab} 

解决方案

您需要将JavaScript数组转换为本地数组.一种实现方法是使用Dictionary对象，请参见 http://cwestblog.com/2011/10/24/javascript-snippet-array-prototype-tovbarray :

  var dict = new ActiveXObject('Scripting.Dictionary');对于(var i = 0; i  

如果您要写一行，请省略 Transpose().

Considering that you use Internet Explorer only (which is often the case in large enterprises), you conclude that you may offer to your users the possibility to transform an HTML gridview into an Excel file: Moreover, you simply consider that you have to do it in so far as the users ask for it...

The first experience I had with writing tables(or arrays) to Excel from the outside was with Visual Basic (the server side of .net). So I wrote a class to spread a VB 2D-array into an Excel sheet:

1) The first method I used seemed good at first sight and, by the way, it was quite simple: set a loop on columns inside a loop on rows, and write each value from the VB array to the Excel sheet. So it goes:

        Dim j, k as Integer
        Dim Valeur as String
        'let us suppose the VB 2D-array is "Tab(10,20)", containing 11 rows of 21 columns of strings
        'let us suppose the upper left corner of the Excel range is line 7, column 3:
        For j = 0 to 10
           For k = 0 to 20
             Valeur = Tab(j, k)
             Cells(j + 7, k + 3).Value = valeur
           Next
        Next

2) Having programmed the above method , I realized in the end that, though it was simple and logical, it was a bad method, for just one reason: IT IS AWFULLY SLOW!So I found a better method, consisting of writing the whole range of cells at once:

                Feuil.Range("C7:W17").Value = Tab

What difference between first and second method? Well, both methods work correctly, but if you consider a table of 300 rows of 20 columns (or 6000 cells), the time taken by the first method will be around 1 minute, meanwhile the second one takes only half a second!

So, when it came to JavaScript, I tried the same methods: first, open an Excel-ActiveX object (in MS-IE, of course), that gives access to the Excel Object Model Reference (http://msdn.microsoft.com/en-us/library/bb149081(v=office.12).aspx) with which I can apply the first method as follows, using a CSV stack set in an object called 'ap':

   // 'ap' is a Javascript object holding a table of CSV strings (natural index: 1..n)
   var valCell;
   var lim = ap.nbChamps;  // 'nbChamps'= number of csv columns in 'ap' object
   var curLig = 6;         // the datas are written to lines 7 and under
   for (var j = 1; j <= ap.nbelem; j++) // ap.nbelem= number of lines in 'ap'
   {   // lines loop
      curLig += 1;
      for (var k = 1; k <= lim; k++)  // N.B: 'ap' uses natural index: 1 to n and not 0 to n-1
      {   // columns loop
        valCell = ap.litEnLC(j, k);  // 'ap' method reading column k from line j
        classeur.ActiveSheet.Cells(curLig, k + 2 ).value = valCell;
      }
    }

Having used that method, I found the same slowness again: So, I wanted to transpose the second method to javascript, as follows:. Firstly, add a method to the "ap" object, that converts the CSV stack into a Javascript 2D-array. Secondly, write this 2D-array into the "range" object, just as I did in Visual Basic. So:

    // 'zone' is the range. For example: zone= "C7:W17"
    var biTab = ap.pcttEnTab(); // converts CSV stack into a 2D-array
    classeur.ActiveSheet.Range(zone).value = biTab;

And, as expected, this goes much faster, but the problem is it doesn't really work, because instead of transferring each value of the 2D-Array to the corresponding cell of the Excel range, it writes the whole Javascript 2D-array in every cell of the Excel range!

So the problem is: How to write a Javascript 2D-Array into an Excel range at once?

Thanks to anyone who can give me the answer...

By the way, I'm adding the pcttEnTab method just in case someone could fine an explanation of my problem in that method:

this.pcttEnTab = function ()    // Array (tableau à 2 dimensions)
{   // convertit la pile PCTT en tableau à 2 dimensions
    var j, k, s;
    var biTab = new Array();
    for (k = 0; k < this.nbelem; k++) // create a columns Array for each line
    {
        biTab[k] = new Array();
    }
    for (j = 1; j <= this.nbelem; j++)  // lines loop (natural index)
    {
        for (k = 1; k <= this.nbChamps; k++) // columns loop
        {
            s = this.litEnLC(j, k);
            biTab[j - 1][k - 1] = s;
        }
    }
    return biTab
}

解决方案

You need to convert the JavaScript array to a native array.One way to do this is with the Dictionary object, see http://cwestblog.com/2011/10/24/javascript-snippet-array-prototype-tovbarray:

var dict = new ActiveXObject('Scripting.Dictionary');
for (var i=0;i < 5; i++ ) dict.add(i,i);
sheet.cells(1,1).resize(5, 1).value = sheet.application.Transpose(dict.items());

If you write to a row, ommit the Transpose().

这篇关于Javascript to Excel:由于缺少“范围"而导致性能不佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！