This project has moved. For the latest updates, please go here.

Insufficient system resources to complete view

May 15, 2012 at 9:31 AM

Hello,

I try create Excel Add-in with Excel DNA (xll library)  and NetOffice.  (Excel 2002, VS Studio 2008, .NET 3,5, Windows 7 64bit)

I test this code (VBA):

Sub Test()
Dim r As Integer
Dim s As Integer
Dim colorIndex As String

For r = 1 To 10000
    For s = 1 To 20
        colorIndex = Cells(r, s).Interior.colorIndex
    Next s
Next r

MsgBox "Finish"
End Sub
Call below code with only ExcelDNA is OK :

[ExcelCommand(MenuText = "TestExcelDna200t")]
        public static void TestExcelDna200t() {           
            string colorIndex = "";
            ExcelReference er;
            for (int r = 1; r <= 10000 ; r++) {
                for (int c = 1; c <= 20; c++) {
                    er = new ExcelReference(r + 1, c + 1);
                    colorIndex = XlCall.Excel(XlCall.xlfGetCell, 38, er).ToString();                    
                }
            }           
        }
 
But code below with Excel DNA and NetOffice isn't good:
[ExcelCommand(MenuText = "TestNetOffice200t")]
        public static void TestNetOffice200t() {
            LateBindingApi.Core.Factory.Initialize();
            NetOffice.ExcelApi.Application a = new NetOffice.ExcelApi.Application(null, ExcelDnaUtil.Application);
                       
            string colorIndex = "";
            for (int r = 1; r <= 10000; r++) {
                for (int c = 1; c <= 20; c++) {
                    colorIndex = a.Cells[r, c].Interior.ColorIndex.ToString();                      
                }
            }           
            MessageBox.Show("Finish");
        }
I get this error:
"Excel cannot complete this task with available resources that are available. Select less data or close other applications."

"Insufficient system resources to complete view"
And Excel in memory occupies cca 320 000kB.
For less cells code is OK, but occupies lot of memory and slower.
eg. same code, only for 20 000 cells duration 0,32s (VBA), 0,6s (ExcelDNA), 2,5s (ExcelDNA+NetOffice)
Please, can you help me?
Radek




Coordinator
May 15, 2012 at 9:30 PM
Edited May 15, 2012 at 9:36 PM

hey radek,

NetOffice is based on the COM Automation Interface. Excel DNA works different and much faster of course.
you have to free the created com objects. you create 10000 * 20 com objects without release in your code.(*heavy*)
you have serveral options:

For r = 1 To 10000

    For s = 1 To 20        
  
       Dim r as Excel.Range = myWorksheet.Cells(r, s)
       colorIndex = r.Interior.colorIndex       
       ' do more here         

       r.Dispose() ' free the object

    Next s

Next r

'ranother way to us is:  

For r = 1 To 10000

   For s = 1 To 20        
   
      colorIndex = myWorkSheet.Cells(r,s).Interior.colorIndex        
      ' do more here 

   Next s

    myWorkSheet.DisposeChildInstances() 
   '  20 range objects are childs from myWorksheet . 
   '  this method free all child objects from  
    ' myWorksheet. this is a general concept in NetOffice

Next r

' NetOffice.Factory.ProxyCount gives you the current count of 
' open com objects in your application' 

'in old 1.4 release it was LateBindingApi.Core.Factory.ProxyCount 
May 16, 2012 at 4:45 AM

Thanks Sebastian,

now it is OK. I use myWorkSheet.DisposeChildInstances()

But how I use this code:

LateBindingApi.Core.Factory.Initialize();
NetOffice.ExcelApi.Application a = new NetOffice.ExcelApi.Application(null, ExcelDnaUtil.Application);             
Excel.Range rng = ws.Range("A1:T1000");            
foreach (Excel.Range  cell in rng) {
   colorIndex = cell.Interior.ColorIndex.ToString();                
}

Thanks

Radek

Coordinator
May 16, 2012 at 12:45 PM
Edited May 16, 2012 at 12:46 PM
// the most efficent way is:

foreach
(Excel.Range cell in ws.Range("A1:T1000"))
{ colorIndex = cell.Interior.ColorIndex.ToString();
cell.Dispose(); }

dont forget the application.Dispose() while shutdown.
application is the root object and the Dispose() call release all open proxies.

NetOffice Documentation: COM Proxy Management
http://netoffice.codeplex.com/wikipage?title=Tec_Documentation_English_Management

Sebastian
May 17, 2012 at 9:42 AM

Thank you

I writed class with Dispose method and use "Using" block.

Radek