none
csharp interop excel System.Runtime.InteropServices.COMException

    Frage

  • Hallo,

    ich habe ein Problem beim Schreiben von einer Formel in eine Excel Zelle. Ich möchte mehrere Zellen in einer For - Schleife aktualisieren und ich habe das Gefühl, dass er zu schnell aktualisiert. Durch das zu schnelle Aktualisieren kommt immer folgende Fehlermeldung:

    System.Runtime.InteropServices.COMException
      HResult=0x8001010A
      Message=The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))
      Source=mscorlib
      StackTrace:
       at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
       at System.Dynamic.ComRuntimeHelpers.CheckIfMissingTypeInfoIsExpected(Int32 hresult, Boolean throwIfMissingExpectedTypeInfo)
       at System.Dynamic.ComRuntimeHelpers.GetITypeInfoFromIDispatch(IDispatch dispatch, Boolean throwIfMissingExpectedTypeInfo)
       at System.Dynamic.IDispatchComObject.EnsureScanDefinedMethods()
       at ExcelManipulator.Program.Main(String[] args) in C:\Users\Test\Documents\git_projects\csharp\ExcelManipulator\ExcelManipulator\Program.cs:line 42
    


    hier noch mein momentaner Source Code Stand:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Threading.Tasks;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace ExcelManipulator
    {
        class Program
        {
            private static readonly string FILE_NAME = "online.xls";
            private static readonly string URL_TO_DATA_FILE = "http://test.de/download/";
    
            static void Main(string[] args)
            {
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\Test\Documents\git_projects\csharp\source.xls");
                xlApp.Calculation = Excel.XlCalculation.xlCalculationManual;
    
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets["data"];
                Excel.Range xlRange = xlWorksheet.UsedRange;
    
                xlApp.ScreenUpdating = true;
                xlApp.Visible = true;
                xlApp.Interactive = true;
                xlApp.IgnoreRemoteRequests = false;
    
                xlApp.CalculateBeforeSave = false;
    
                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;
    
                //iterate over the rows and columns and print to the console as it appears in the file
                //excel is not zero based!!
                for (int i = 1; i <= rowCount; i++)
                {
                    for (int j = 1; j <= colCount; j++)
                    {
                        //write the value to the console
                        if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Formula != null && xlRange.Cells[i, j].Formula.Contains(FILE_NAME))
                        {
                            string suffix = xlRange.Cells[i, j].Formula;
                            int positionOfBracket = suffix.IndexOf("]");
    
                            if (positionOfBracket < 0)
                            {
                                continue;
                            }
    
                            suffix = suffix.Substring(positionOfBracket + 1, suffix.Length - (positionOfBracket + 1));
                            string[] suffixes = null;
                            suffixes = suffix.Split('!');
    
                            for (int k = 0; k < suffixes.Length; k++)
                            {
                                suffixes[k] = suffixes[k].Replace("'", "");
                            }
    
                            string link = "='" + URL_TO_DATA_FILE + "[" + FILE_NAME + "]" + suffixes[0] + "'!" + suffixes[1];
                            xlRange.Cells[i, j].Value = link;
                        }
                    }
                }
    
                //Save data
                xlWorkbook.SaveAs(@"C:\Users\Test\Documents\git_projects\csharp\target.xls");
    
                //cleanup
                GC.Collect();
                GC.WaitForPendingFinalizers();
    
                //rule of thumb for releasing com objects:
                //  never use two dots, all COM objects must be referenced and released individually
                //  ex: [somthing].[something].[something] is bad
    
                //release com objects to fully kill excel process from running in the background
                Marshal.ReleaseComObject(xlRange);
                Marshal.ReleaseComObject(xlWorksheet);
    
                //close and release
                xlWorkbook.Close();
                Marshal.ReleaseComObject(xlWorkbook);
    
                //quit and release
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
            }
        }
    }
    

    Falls noch andere Infromationen benötigt werden, bitte kurz schreiben.

    Danke im voruas für eure Hilfe.

    Viele Grüße

    Julian

    Montag, 11. Juni 2018 20:11

Alle Antworten

  • Ich bin jetzt kein Excelexperte, aber wenn du der Meinung bist dass er nicht mehr hinterher kommt, hast du dann schonmal versucht einfache Verzögerungen einzubauen?

    Thread.Sleep(100);

    oder etwas in der Richtung.

    Btw.: Nimm bitte den GC.Collect raus wenn du ihn nicht unbedingt brauchst. Der kann bei einem manuellen Aufruf vieles durcheinander bringen. Die Automatik reicht im Normalfall immer aus.

    Und um den Fehler genauer einzugrenzen könntest du Logs schreiben. Also schreib eine kurze Logging Methode die mit using arbeitet und dann hinter jeder Zeile (oder jeder wo es sich lohnen könnte) einen Eintrag. Dann siehst du zumindest WO der Fehler exakt auftritt. Vielleicht kann er das Workbook nicht einmal öffnen weil das Öffnen der Applikation so lange dauert, etc. 

    Und du könntest dann einige dieser Aufrufe asynchron machen und auf deren Abschluss warten (siehe async await).

    Dienstag, 12. Juni 2018 08:53
  • Hallo Marcel,

    danke für deine Hilfe.

    Die Idee mit dem 

    Thread.Sleep(100);

    hatte ich schon getestet. Wenn ich dort 2000 Millisekunden einstelle, dann komm ich etwas weiter. Allerdings trotzdem nicht bis zum Ende des Dokuments. Und die Millisekunden einfach auf 5000 (oder noch höher) zu stellen macht meiner Meinung nach keinen Sinn, da eine Bearbeitung dann ca. 30 Minuten dauern würde.

    Den 

    GC.Collect();

    habe ich rausgenommen, leider hat das aber keine Veränderung gebracht.

    Laut den Debug Tools von Visual Studio 2017, kommt die Exception in der folgenden Zeile nach dem dirtten manchmal auch erst nach dem vierten Durchlauf der zweiten for-Schleife:

     if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Formula != null && xlRange.Cells[i, j].Formula.Contains(FILE_NAME))

    Durch die Befehle

    xlApp.ScreenUpdating = true;
    xlApp.Visible = true;
    xlApp.Interactive = true;
    xlApp.IgnoreRemoteRequests = false;

    macht die csharp Anwendung das Excel-File auf und man kann beobachten an welcher Stelle er nicht weitermachen kann. Wie gesagt, meistens fliegt die Exception beim dritten Durchlauf manchmal auch erst beim Vierten.

    Korrigiere mich, wenn ich falsch liege. Aber wenn ich einen asynchronen Aufruf mache und dann warte bis ich eine Antwort habe, ist das dann nicht eine sequenzielle Abarbeitung wie ich sie bereits habe? Ich denke auch, wenn ich das Excel File mit mehreren Threads bearbeite, dass Probleme auftreten werden. Den der zweite schreibende Thread wir eine Exception werfen, da dieser keinen schreibenden Zugriff auf die Excel Datei bekommt.

    Grüße

    Julian

    Dienstag, 12. Juni 2018 09:35
  • Hallo Julian,

    ein Vorschlag von mir ist erst einmal zu prüfen ob die Celle Formel beinhaltet, und dann nur den Formeltext zu verwenden.

    Der komplette Teile Deiner beiden verschachtelten Schleifen könnte so aussehen:

    Excel.Range xlRange = xlWorksheet.UsedRange;
    foreach (Excel.Range item in xlRange) {
    	if (item.HasFormula) {
    		String strFormula = item.FormulaLocal;
    		// hier kommt Deine Bearbeitung der Formel hin
    	}
    }
    

    Grüße

    Roland

    Dienstag, 12. Juni 2018 12:43
  • Hallo Roland,

    danke für deine Hilfe. Falls ich dich richtig verstanden habe, dann soll ich die Formel aus der bestehenden Zelle auslesen und diese nur modifizieren nicht komplett ersetzen oder?

    Bei mir sieht die foreach-Schleife jetzt so aus:

    Excel.Range xlRange = xlWorksheet.UsedRange;
    foreach (Excel.Range item in xlRange)
    {
    	if (item.HasFormula && item.Formula.Contains(FILE_NAME))
    	{
    		String strFormula = item.FormulaLocal;
    		// hier kommt Deine Bearbeitung der Formel hin
    
    		String link = "'http://test.de/download/[online.xls]value'";
    		item.FormulaLocal = Regex.Replace(strFormula,"'.+'", link);
    	}
    }


    Hier kommt jetzt eine andere Exception:

    System.Runtime.InteropServices.COMException
      HResult=0x800AC472
      Message=Exception from HRESULT: 0x800AC472
      Source=mscorlib
      StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.set_FormulaLocal(Object )
       at ExcelManipulator.Program.Main(String[] args) in C:\Users\Test\Documents\git_projects\csharp\ExcelManipulator\ExcelManipulator\Program.cs:line 78
    

    Wenn ich den Source Code wie folgt abändere, dann passiert gar nichts:

    Excel.Range xlRange = xlWorksheet.UsedRange;
    foreach (Excel.Range item in xlRange)
    {
    	if (item.HasFormula && item.Formula.Contains(FILE_NAME))
    	{
    		String strFormula = item.FormulaLocal;
    		// hier kommt Deine Bearbeitung der Formel hin
    
    		String link = "'http://test.de/download/[online.xls]value'";
    		//item.FormulaLocal = Regex.Replace(strFormula,"'.+'", link);
                    strFormula = Regex.Replace(strFormula, "'.+'", link);
    	}
    }

    also das Programm läuft ohne Exception durch aber  im Excel hat sich nichts geändert. Ist quasi eine 1:1 Kopie vom source File.

    Grüße

    Julian

    Dienstag, 12. Juni 2018 15:32
  • Hallo Julian,

    ich würde dir empfehlen dieses Problem mit OpenXML zu lösen. Interop ist natürlich schon wichtig wenn man Excel steuern will. Du bearbeitest das Dokument einfach nur deswegen würd OpenXML mehr sinn machen. Mit solchen Problemen müsstest Du dann auch nicht kämpfen


    Gruß Thomas
    13 Millionen Schweine landen jährlich im Müll
    Dev Apps von mir: UWP Segoe MDL2 Assets, UI Strings

    Dienstag, 12. Juni 2018 16:54
  • Hallo Julian,

    was ich nur in meinem Post mitteilen wollte, dass die anzahl der Zugriffe mit cells[x,y] minimiert werden sollte, daher mein Vorschlag mit der Schleife und der Bedingung mit HasFormula.

    Du musst natürlich deine Änderung dann schon wieder in die Zelle rausschreiben, das heißt item.Formula... = ... wird schon benötigt.

    Das aktuelle Problem liegt wahrscheinlich daran dass die Formel nicht in der richtigen Form wieder zusammengebaut wird.

    Kennst Du denn die Unterschiede zwischen .Formula, .FormulaLocal, FormulaR1C1 ?

    Ich würde zunächst mal die Formel wie in deinem zweiten Beispiel aufbereiten, mir dann im Debugger ansehen, und erst dann zurückschreiben.

    Poste doch mal den Formelinhalt zu dem Zeitpunkt wo die Exception geworfen wird.

    Grüße

    Roland

    Mittwoch, 13. Juni 2018 06:03
  • Hallo Thomas, Hallo Roland,

    danke für eure Vorschläge.

    Thomas, ich werde es bei Gelegenheit auch mit OpenXML versuchen. Vielleicht geht es damit ohne Probleme.

    Roland, ich kennen den Unterschied zwischen Formula, FormulaLocal und FormulaR1C1 leider nicht. Ich habe deshalb alle drei Varianten ausprobiert. Allerdings hat keine der drei Möglichkeiten funktioniert. Bei Allen kommt die gleiche Exception wie oben:

    System.Runtime.InteropServices.COMException
      HResult=0x800AC472
      Message=Exception from HRESULT: 0x800AC472
      Source=mscorlib
      StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.set_FormulaR1C1(Object )
       at ExcelManipulator.Program.Main(String[] args) in C:\Users\Test\Documents\git_projects\csharp\ExcelManipulator\ExcelManipulator\Program.cs:line 79
    

    Hier mein Source Code den ich jetzt getestet habe:

    Excel.Range xlRange = xlWorksheet.UsedRange;
    foreach (Excel.Range item in xlRange)
    {
    	if (item.HasFormula && item.Formula.Contains("online.xls"))
    	{
    		String link = "='http://test.de/download/[online.xls]value'!F31";
    		//item.Formula = link;
    		//item.FormulaLocal = link;
    		item.FormulaR1C1 = link;
    	}
    }

    Um das Problem auszuschließen, dass die Formel falsch zusammengebaut wird, habe ich einfach die Formel statisch in eine Variable geschrieben. Hier trotzdem noch der Wert der Variable der enthalten ist bei der Exception:

    "='http://test.de/download/[online.xls]value'!F31"

    Grüße

    Julian

    Mittwoch, 13. Juni 2018 09:45
  • Hallo Julian,

    klar das ist keine zulässige Formel.

    Was hast Du den eigentlich mit Deinem Programm vor?

    Wie sieht die Formel in der Zelle aus bevor Du diese mit link überschreibst?

    Um Deine ursprüngliche Frage zunächst einmal abzuschließen solltest Du erst mal die in der gefundenen Zelle  enthaltene Formel  unverändert zurückschreiben. Wenn das funktioniert, haben wir Deine ursprüngliche Frage beantwortet, nämlich das fehlerfreie schnelle aktualisieren.

    Als nächstes gehen wir dann an das Aufarbeiten Deiner Formel, was eigentlich ja ein eigenes Thema sein sollte.

    Grüße

    Roland

    Mittwoch, 13. Juni 2018 10:15
  • Hallo Roland,

    ich habe jetzt, wie du mir empfohlen hast, die ursprüngliche Formel wieder zurückkopiert. Und das hat ohne Probleme funktioniert.

    Mein eigentliches Anliegen ist, dass ich in einer Excel Datei (später sollen es sehr viel mehr werden) einen externen Link aktualisieren möchte. Im Moment verweist der Link noch auf eine lokal gespeicherte Datei. Das soll aber geändert werden. Die Datei soll ab jetzt im Internet liegen. Das hat den Vorteil, dass alle Benutzer auf die gleiche Datei im Internet zugreifen. Wenn sich dort etwas ändert, bekommen die Änderungen alle Benutzer direkt mit, ohne das diese manuell eine neue Version herunterladen müssen.

    Du hast gesagt, dass mein String keine gültige Formel ist, wenn ich diese aber manuell in eine Zelle kopiere, dann kann der Wert ohne Probleme aufgelöst werden. Was müsste ich den an meiner Formel anpassen, damit sie gültig wird?

    Grüße

    Julian

    Mittwoch, 13. Juni 2018 13:49
  • Hallo,

    also ich habe jetzt eine recht unschöne "Lösung" gefunden.

    Ich habe das Schreiben der neuen Formel in eine Methode ausgelagert. Diese rufe ich jetzt auf, um die Zelle mit dem neuen Wert zu befüllen. Innerhalb der Methode fange ich die Exception (COMException) und rufe die Methode rekursiv auf, bis keine Exception mehr kommt. Wie gesagt es funktioniert, aber es ist keine schöne "Lösung". Hier noch mein Source Code Stand:

    foreach-Schleife:

    Excel.Range xlRange = xlWorksheet.UsedRange;
    foreach (Excel.Range item in xlRange)
    {
    	if (item.HasFormula && item.Formula.Contains("online.xls"))
    	{
    		String link = "='http://test.de/download/[online.xls]value'!F31";
    		writeFormular(item, link);
    	}
    }

    Methode, die sich selbst rekursiv aufruft:

    static void writeFormular(Excel.Range item, String link)
    {
    	try
    	{
    		item.Formula = link;
    	}
    	catch (COMException e)
    	{
    		writeFormular(item, link);
    	}
    }

    Vielleicht hat einer von euch noch eine schönere Lösung für mich parat?

    Grüße

    Julian

    Mittwoch, 13. Juni 2018 15:57
  • Hallo Roland,

    ich habe jetzt, wie du mir empfohlen hast, die ursprüngliche Formel wieder zurückkopiert. Und das hat ohne Probleme funktioniert.

    Damit ist ja das ursprüngliche Problem mit dem schnellen aktualisieren  gelöst.

    Jetzt zum nächsten Problem:

    Du schreibst:

    "Du hast gesagt, dass mein String keine gültige Formel ist, wenn ich diese aber manuell in eine Zelle kopiere, dann kann der Wert ohne Probleme aufgelöst werden."

    Das heißt aber nicht das der String eine gültige Formel ist. Dieser String ist ein gültiger Wert für die Zelle, und das wird nicht über Formula sondern über Value zugewiesen.

    Wenn ich Deine letzte Aussage richtige verstehe geht es hier gar nicht um Formeln. Das heißt wir müssen auch nicht mit HasFormular und Formular hantieren.

    Die Schleife mit Bedingung müsste dann in etwa so aussehen:

    Excel.Range xlRange = xlWorksheet.UsedRange;
    foreach (Excel.Range item in xlRange) {
    	object objValue;
    	if (item.Value2 != null ) {
    		objValue = item.Value2;
    		if (objValue.GetType().Name == "String") {
    			String strText = item.Value2;
    			if (strText.Contains("online.xls")) {
    				// hier wird Dein Link verändert
    				// und zuletzt den veränderten Link wieder zurückschreiben:
    				item.Value2 = strText;
    			}
    		}
    	}
    }

    Grüße

    Roland


    Donnerstag, 14. Juni 2018 06:13
  • Hallo Roland,

    danke für deine Antwort,

    ich habe jetzt versucht anstelle der Formel den Value2 zu setzen. Ich denke er versucht meinen String auch direkt als Formel aufzulösen, da mein String mit einem "=" beginnt. Also kurz gesagt, wenn ich

    item.Value2 = link;

    schreibe, dann kommt folgende Exception:

    System.Runtime.InteropServices.COMException
      HResult=0x800AC472
      Message=Exception from HRESULT: 0x800AC472
      Source=mscorlib
      StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )
       at ExcelManipulator.Program.modifyFormula(String path) in C:\Users\Test\Documents\git_projects\csharp\ExcelManipulator\ExcelManipulator\Program.cs:line 87
       at ExcelManipulator.Program.Main(String[] args) in C:\Users\Test\Documents\git_projects\csharp\ExcelManipulator\ExcelManipulator\Program.cs:line 30
    

    der String, den ich dabei in Value2 schreiben lass, sieht so aus:

    link = "='http://test.de/download/[online.xls]value'!C2";
    Grüße

    Julian

    Montag, 18. Juni 2018 11:45
  • Hallo Julian,

    Du hast Dir die Antwort doch quasi schon selbst gegeben.

    Der String darf in Deinem Fall nicht mit "=" beginnen, da Excel, dass sonst wieder als Formel interpretiert. 

    Grüße

    Roland

    Montag, 18. Juni 2018 12:21