csharp interop excel System.Runtime.InteropServices.COMException

Question

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

Answer 1

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).

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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