none
C# InterOp.Excel: Reading & Updating to a large CSV/XLSX file in chunks RRS feed

  • Question

  • Hello, 

    (VS2017, C#, Interop.Excel (15.0.0.0))

    I'm trying to go over a very large CSV / Excel updating it live while the user can also view it. 

    Initially i tried the cell by cell approach, it worked, but it's way too slow. 

    So, i've done some digging in the web and saw it is possible to speed things up via working with arrays of dynamic object[,]

    but i cannot get it to work for me, either it works for the first one (line 1...x) and then not the next chunk x+1...y or it fails the first chunk. 

    what is the correct way of doing it ? 

    how do i update the array back in one chunk ?

     private string[,] GetCellBlockFromExcel(UInt32 fromX, UInt32 fromY, UInt32 toX, UInt32 toY)
    {
     Excel.Worksheet ws = _xlsxApp.ActiveSheet as Excel.Worksheet;
    
     //Get the range using number index
     Excel.Range newRng = ws.Range[ws.Cells[fromX, fromY], ws.Cells[toX, toY]];
    
     object[,] holder = newRng.Value2;
     string[,] returnString = new string[holder.GetLength(0), holder.GetLength(1)];
               
               
                int returnString_x = 0, returnString_y = 0;
                int numHops = newRng.Count;
                for (int i = 1; i < numHops; i++)
                {
                   
                    Excel.Range r = Excel.Range)newRng.Item[i];
                    if (r != null && r.Value2 != null)
                    {
                        
                        returnString[returnString_x, returnString_y] = r.Value2;
                    }
                }
                return returnString;
            }


    • Edited by OferP Monday, November 19, 2018 11:42 AM
    • Moved by CoolDadTx Monday, November 19, 2018 3:26 PM Office related
    Monday, November 19, 2018 8:07 AM

All replies

  • In order to speed up the function, you should eliminate ‘newRng.Item[i]’ since all of the values are already in holder. And it is not clear how returnString_x and returnString_y are calculated.

    Consider an approach like this:

    int max_i = holder.GetLength(0);

    int max_j = holder.GetLength(1);

    for( int i = 0; i < max_i; ++i)

    {

       for( int j = 0; j < max_j; ++j)

       {

          object v = holder[i, j];

          returnString[i,j] = v?.ToString();

       }

    }

     

    If strings are not needed, you can also return the holder array without conversion to strings (to reduce the memory usage in case of large ranges).


    • Edited by Viorel_MVP Tuesday, November 20, 2018 6:43 AM
    Monday, November 19, 2018 8:55 AM
  • Hi OferP,

    Could Viorel's solution solve your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Tuesday, November 20, 2018 6:35 AM
    Moderator
  • Hey @Viorel_,

    Thank you for your reply, i'll try to explain better:

     the holder object[,] is a matrix of dynamic values since numHops represents X*Y of the matrix i'v read, i needed 

    access indexes for returnString[returnString_x, returnString_y] 

    so x is our lines progresses when y reaches Rank[1] and up to Rank[0] of the object[,] array. 

    it was written this way because i encountered a problem traversing like you wrote it: for( int i = 0; i < max_i; ++i). 

    i kept getting crushes while accessing holder[i, j]; of chunks above zero. 

    the i,j are not the same for those. 

    conversion to string is because of excel formatting issues. a part of the fields are numbers, strings, some are huge numbers (Excel convers to stuff like 1.95605E+13) etc'. this way everything is a string and is easier to handle in SW. 

    if you have other suggestions for me i'm open, but please try them first, since it's not as trivial as it appears when the indexes are not beginning in zero. 

    from my poor and tiny experience:

       accessing holder[0, 0]  == exception since indexes begin in 1,1

       indexes of the next chunk are expected not to be [1,1] but the [x,y] you selected. 

    But as you gather, i'm new to this, and have a habit of finding all the pitfalls in the way :)

    Wednesday, November 21, 2018 2:12 PM