none
Open Interop created xlsx file with interop RRS feed

  • Question

  • Hi and thx in advance,

    I have an issue reopening ".xlsx"-files with the Office Interop in .NET C#.

    No matter how i create the file (with all the options set or none at all), the file wont be opened again by the Workbooks.Open-Method (again, with al options set or none at all).

    I already tried to set the Current Culture of the Thread the Office-Application is running in, also set the UICulture (both to "en-US" of cause, cause there are the fewest problems with formats) but it still won't open. I also tried different Formats... but no... same Exception. When I open the created file via the real thing (MS Office Excel) it opens with no errors or warnings at all.

    Using:

    • "Microsoft.Office.Interop.Excel" in Office14-Folder under VS Tools for Office (Version 14.0.0.0)
    • Runtime Version of Interop v2.0.50727
    • VisualStudio 2012
    • .NET 4.5
    • Code is Executed in a Thread (System.Threading.Thread, tried STA-mode too)
    • Data is provided by TFS (TFS API, i dont know the CodePage it works with)
    • Marshall.ReleaseComObject when finished with creation and after saving the file (on workbook- and application-interface)

    I am really lost since this code has worked before and after changing some cell values (retrieving other values from the TFS and writing them to the document) I get Errors al along

    The famous Culture-Error (HRESULT 0x800A03EC) is in there sometimes, most times though I get the "file-format mismatch to file extension"-Exception (but the same HRESULT 0x800a03ec).

    Error:

    "Exception from HRESULT: 0x800A03EC"

    or FileFormat-Error:

    "Excel cannot open the file [filename] because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

    It seems that the Open method actaully just does a count of non Missing.Value parameters and determines which Exceptioon to throw.

    Here is the code in full:

    using MAS.Tools.TfsExport.Common;
    using MAS.Tools.TfsExport.Common.Interfaces;
    using MAS.Tools.TfsExport.Common.Models;
    using Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;
    using System.Windows;
    
    namespace MAS.Tools.TfsExport.Excel
    {
    	public class Creator : IExcelCreator
    	{
    		private string ExtractFromHtml(string text)
    		{
    			if(text == null)
    			{
    				text = "#missing#";
    			}
    			text = text.Replace("</P><P>", System.Environment.NewLine);
    			text = Regex.Replace(text, "<.*?>", "");
    			text = text.Replace("&#160;", "");
    
    			return text;
    		}
    
    		private string GetSuiteTitle(int id)
    		{
    			string name = "";
    			name = "(" + _exportDate.Date.ToString("dd.MM.yyyy") + ")Suite-" + id.ToString();
    			name = Regex.Replace(name, @"[/\\\:\?\[\]\*]*", "");
    			if(name.Length > 30)
    				return name.Substring(0, 30);
    			return name;
    		}
    
    		private string GetSuiteTitle(int id, int level)
    		{
    			string name = "";
    			name = "(" + _exportDate.Date.ToString("dd.MM.yyyy") + ")" + string.Join("", Enumerable.Repeat("Sub", level).ToArray()) + "Suite-" + id;
    			name = Regex.Replace(name, @"[/\\\:\?\[\]\*]*", "");
    			if(name.Length > 30)
    				return name.Substring(0, 15) + name.Substring(name.Length - 16, 15);
    			return name;
    		}
    
    		private DateTime _exportDate;
    		private Microsoft.Office.Interop.Excel.Application _excel;
    		private ExportSettings _settings;
    		private Suite _exportSuite;
    
    		public Creator()
    		{
    		}
    
    		private Workbook SetUpDocument(string path)
    		{
    			if(File.Exists(path))
    			{
    				Workbook w = _excel.Workbooks.Open(path,
    					System.Reflection.Missing.Value, 
    					false,
    					XlFileFormat.xlOpenXMLWorkbook,
    					System.Reflection.Missing.Value,
    					System.Reflection.Missing.Value, 
    					true, 
    					System.Reflection.Missing.Value, 
    					System.Reflection.Missing.Value,
    					true, 
    					System.Reflection.Missing.Value, 
    					System.Reflection.Missing.Value,
    					System.Reflection.Missing.Value, 
    					System.Reflection.Missing.Value, 
    					System.Reflection.Missing.Value);
    				return w;
    			}
    			else
    			{
    				var tmp = _excel.Workbooks.Add();
    				while(tmp.Sheets.Count > 1)
    				{
    					tmp.Sheets.get_Item(1).Delete();
    				}
    				return tmp;
    			}
    		}
    
    		private void Create()
    		{
    			_exportDate = DateTime.Now;
    			MakeSheet(_exportSuite);
    			if(_settings.NoSubsuites)
    			{
    				return;
    			}
    			else if(_settings.MultipleSheets)
    			{
    				foreach(Suite s in _exportSuite.Subsuites)
    				{
    					MakeSheet(s);
    				}
    			}
    		}
    
    		private void MakeSheet(Suite suite, int caseStartRow = 3)
    		{
    			Worksheet sheet = GetWorksheet(GetSuiteTitle(suite.Id));
    			if(sheet == null)
    			{
    				return;
    			}
    			FillHeaders(sheet, GetSuiteTitle(suite.Id));
    			foreach(Case test in suite.Cases)
    			{
    				caseStartRow += MakeCaseArea(sheet, test, caseStartRow);
    			}
    			if(!_settings.NoSubsuites && !_settings.MultipleSheets && suite.Subsuites != null && suite.Subsuites.Count > 0)
    			{
    				AddSubsuites(sheet, suite.Subsuites, caseStartRow);
    			}
    		}
    
    		private Worksheet GetWorksheet(string title)
    		{
    			Worksheet tmp = null;
    			try
    			{
    				tmp = _excel.Workbooks[1].Worksheets.get_Item(title);
    				return tmp;
    			}
    			catch(System.Runtime.InteropServices.COMException e)
    			{
    				try
    				{
    					if(_excel.Workbooks[1].Worksheets.Count == 1 && _excel.Workbooks[1].Worksheets.get_Item(1).Name == null)
    					{
    						tmp = _excel.Workbooks[1].Worksheets.get_Item(1);
    					}
    					else
    					{
    						tmp = _excel.Workbooks[1].Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, XlSheetType.xlWorksheet);
    					}
    					tmp.Name = title;
    					return tmp;
    				}
    				catch(System.Runtime.InteropServices.COMException e2)
    				{
    					return null;
    				}
    			}
    		}
    
    		private void FillHeaders(Worksheet sheet, string title)
    		{
    			sheet.Cells.Clear();
    			_settings.Formatter.ApplayInitialDocumentFormat(sheet);
    
    			Range r = sheet.get_Range("a1", "j1");
    			r.Value = title;
    			_settings.Formatter.FormatHead(r);
    
    			r = sheet.Cells[3, 7].EntireColumn;
    			_settings.Formatter.ApplyResultCondition(r);
    
    			r = sheet.Cells[3, 8].EntireColumn;
    			_settings.Formatter.ApplyResultCondition(r);
    
    			_settings.Formatter.RemoveResultCondition(sheet.Cells[1, 7]);
    			_settings.Formatter.RemoveResultCondition(sheet.Cells[2, 7]);
    			_settings.Formatter.RemoveResultCondition(sheet.Cells[1, 8]);
    			_settings.Formatter.RemoveResultCondition(sheet.Cells[2, 8]);
    
    			sheet.Cells[2, 1] = "WI-Id";
    			sheet.Cells[2, 2] = "RQ-Id";
    			sheet.Cells[2, 3] = "TC-Id";
    			sheet.Cells[2, 4] = "#";
    			sheet.Cells[2, 5] = "Test Case Title\nTest Step Action";
    			sheet.Cells[2, 6] = "Summary\nTest Step Expected Result";
    			sheet.Cells[2, 7] = "TC Verdict";
    			sheet.Cells[2, 8] = "TS Verdict";
    			sheet.Cells[2, 9] = "Tested By\nTest Step Comments";
    			sheet.Cells[2, 10] = "Tested Date\nBug-Ids";
    
    			_settings.Formatter.SetColumnsWidths(sheet);
    
    			r = sheet.get_Range("a2", "j2");
    			_settings.Formatter.ApplyDocumentDefaultFormat(r);
    		}
    
    		private int MakeCaseArea(Worksheet sheet, Case test, int caseStartRow)
    		{
    			int currentRow = caseStartRow;
    
    			foreach(Step step in test.Steps)
    			{
    				currentRow++;
    				MakeStepRow(sheet, step, currentRow, currentRow - caseStartRow);
    			}
    
    			_settings.Formatter.SetTestCaseBorders(sheet, caseStartRow);
    
    			sheet.Cells[caseStartRow, 1] = ExtractFromHtml(test.Id.ToString());
    			sheet.Cells[caseStartRow, 2] = ExtractFromHtml(test.RefExtId);
    			sheet.Cells[caseStartRow, 3] = ExtractFromHtml(test.ExtId);
    			sheet.Cells[caseStartRow, 5] = ExtractFromHtml(test.Title);
    			sheet.Cells[caseStartRow, 6] = ExtractFromHtml(test.Description);
    			_settings.Formatter.RemoveResultCondition(sheet.Cells[caseStartRow, 8]);
    			if(_settings.WithTestResults)
    			{
    				sheet.Cells[caseStartRow, 7].Value = (_settings.WithTestResults && test.Result == null ? "NotExecuted" : ExtractFromHtml(test.Result.Outcome));
    				sheet.Cells[caseStartRow, 9] = (_settings.WithTestResults && test.Result == null ? "" : ExtractFromHtml(test.Result.RunByName));
    				sheet.Cells[caseStartRow, 10] = (_settings.WithTestResults && test.Result == null ? "NotExecuted" : ExtractFromHtml(test.Result.DateStarted));
    			}
    
    			if(currentRow != caseStartRow)
    			{
    				sheet.get_Range("j" + (caseStartRow + 1), "j" + currentRow).Merge();
    				sheet.get_Range("a" + (caseStartRow + 1), "j" + currentRow).Rows.Group();
    			}
    
    			return currentRow - caseStartRow + 1;
    		}
    
    		private void MakeStepRow(Worksheet sheet, Step step, int rowsIndex, int stepCount)
    		{
    			if(step != null)
    			{
    				_settings.Formatter.SetTestStepBorders(sheet, rowsIndex);
    				sheet.Cells[rowsIndex, 4] = stepCount;
    				sheet.Cells[rowsIndex, 5] = ExtractFromHtml((step.Title != null && step.Title.Length > 0) ? step.Title.ToString() : null);
    				sheet.Cells[rowsIndex, 6] = (step.ExpectedResult == null || step.ExpectedResult.Length == 0) ? "k.A." : ExtractFromHtml(step.ExpectedResult.ToString());
    			}
    			if(_settings.WithTestResults)
    			{
    				sheet.Cells[rowsIndex, 7].Validation.Delete();
    				sheet.Cells[rowsIndex, 8].Value = step.Result == null ? "n/A" : step.Result.Outcome.ToString();
    				sheet.Cells[rowsIndex, 9] = step.Result == null ? "n/A" : step.Result.ErrorMessage.ToString();
    			}
    		}
    
    		private void AddSubsuites(Worksheet sheet, List<Suite> suites, int currentRow)
    		{
    			foreach(Suite suite in suites)
    			{
    				foreach(Case test in suite.Cases)
    				{
    					currentRow += MakeCaseArea(sheet, test, currentRow);
    				}
    				if(suite.Subsuites != null && suite.Subsuites.Count > 0)
    				{
    					AddSubsuites(sheet, suite.Subsuites, currentRow);
    				}
    			}
    		}
    
    		public void Export(ExportSettings settings, Suite suite)
    		{
    			_settings = settings;
    			_exportSuite = suite;
    			_excel = new Microsoft.Office.Interop.Excel.Application();
    			//_excel.DisplayAlerts = false;
    			Workbook book = null;
    			try
    			{
    				book = SetUpDocument(_settings.FilePath);
    				Create();
    				book.SaveAs(settings.FilePath,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value,
    				XlSaveAsAccessMode.xlExclusive,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value,
    				System.Reflection.Missing.Value);
    			}
    			catch(Exception ex)
    			{
    				System.Windows.MessageBox.Show("Error while creating the File\n\n\nException:\n" + ex.Message + "\n\nStackTrace:\n" + ex.StackTrace, "Export failed", MessageBoxButton.OK, MessageBoxImage.Error);
    			}
    			finally
    			{
    				if(book != null)
    				{
    					book.Close(false, book, false);
    				}
    				if(_excel != null)
    				{
    					_excel.Quit();
    				}
    				if(book != null)
    				{
    					Marshal.ReleaseComObject(book);
    				}
    				if(_excel != null)
    				{
    					Marshal.ReleaseComObject(_excel);
    				}
    				book = null;
    				_excel = null;
    				GC.Collect();
    				GC.WaitForFullGCComplete();
    			}
    		}
    	}
    }

    Not all of it is relevant i know... but this is my first try using Excel Interop and TFS API and maybe there is just something you have to know about it.

    Oh and i also tried to use the options in the Wrokbooks.Add-Method with no success.

    Thx for reading and trying...

    help is greately appreciated

    Sincerely

    MDeero


    • Moved by Cindy Meister MVP Thursday, June 18, 2015 5:35 PM Excel-specific, not using VSTO technology
    Thursday, June 18, 2015 6:58 AM

Answers

  • For anyone having a similar Problem:

    The Excel Application Object (with which we create a File) needs to be disposed, as do all the "Worksheets" etc., so that the File can be freed.

    Cheers


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.

    • Marked as answer by MDeero Wednesday, March 15, 2017 10:30 PM
    Wednesday, March 15, 2017 10:30 PM

All replies

  • Hi Mdeero,

    >> Exception from HRESULT: 0x800A03EC

    >> Excel cannot open the file [filename] because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

    Based on your code, I found you did not set fileformat in your "book.SaveAs", and you set the file format as "XlFileFormat.xlOpenXMLWorkbook" in your "_excel.Workbooks.Open". In my option, if you want to open a file, you need to set the XlFileFormat in the open event the same as you saved. In addition, if you save a file with a specific format, you need to modify the file extension with the same, otherwise you will get the FileFormat error.

    For your issue, if you want to open the xlsx file, save the file as xlsx, and then open it again, I will suggest you save the file with System.Reflection.Missing.Value in the format parameter, and then open the file with System.Reflection.Missing.Value in the format parameter. If you want to save the file with different file format like csv file, here is a simple demo.

      private void ExcelSave_Click(object sender, EventArgs e)
            {
                Excel.Application oExcel = new Excel.Application();
                oExcel.Visible = true;
                Excel.Workbook oWorkbook = oExcel.Workbooks.Open(@"D:\Backup\Desktop\Thread.xlsx", System.Reflection.Missing.Value,
                        false,
                    //XlFileFormat.xlWorkbookDefault,
                        System.Reflection.Missing.Value,//replace XlFileFormat.xlOpenXMLWorkbook with System.Reflection.Missing.Value
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        true,
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        true,
                        System.Reflection.Missing.Value,
    
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value);
                oWorkbook.SaveAs(@"D:\Backup\Desktop\Thread1.csv",
                   XlFileFormat.xlCSV,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value,
                    XlSaveAsAccessMode.xlExclusive,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value);
                oWorkbook.Close();
            } 
           private void ExcelOpen_Click(object sender, EventArgs e)
            {
                Excel.Application oExcel = new Excel.Application();
                oExcel.Visible = true;
                Excel.Workbook oWorkbook = oExcel.Workbooks.Open(@"D:\Backup\Desktop\Thread1.csv", System.Reflection.Missing.Value,
                        false,
                        XlFileFormat.xlCSV,
                        //System.Reflection.Missing.Value,//replace XlFileFormat.xlOpenXMLWorkbook with System.Reflection.Missing.Value
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        true,
                        System.Reflection.Missing.Value,
    
                        System.Reflection.Missing.Value,
                        true,
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value);
            }  
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 22, 2015 5:22 AM
  • Thanks forthe reply

    unfortunally, I already tried that too. In the above code I was just trying to make it work and tried to force it to opne with a specific format (OpenWorkbook should be the default on "SaveAs" with Office 2010).

    I now also tried to just use "Close" with the first parameter set to true, wich did work... once... then it changed back to the old behavior.

    To me it seems that the ExcelApplication is somehow not finalizng the save or the Quit is making something weird happen. But just like before: "I am totally lost".

    I am at the point where I would like a sample of someone "Saving AND Opening" the same file multiple times in a row without Exceptions... maybe I can figure it out from there.

    Thank you again for the reply... not to many there.

    PS: if someone has an Idea of how to do a step by step debugging on that Interop... please let me know

    Monday, June 22, 2015 6:36 AM
  • ​Hi MDeero,

    >> I already tried that too. In the above code I was just trying to make it work and tried to force it to opne with a specific format

    What do you mean by “a specific format”? Could you share us the file name with extension or share us the file through OneDrive?
    >> I am at the point where I would like a sample of someone "Saving AND Opening" the same file multiple times in a row without Exceptions

    In my first reply, I shared how to open the xlsx file, save it as csv file and then open the csv file again. Could you share us how you tried and where it did not work? Keep in mind, when you open the file or save the file with the specific format, you need to keep the extension of the file consistent with the XlFileFormat in your open and saveas methods.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, June 23, 2015 9:13 AM
  • >>What do you mean by “a specific format”? Could you share us the file name with extension or share us the file through OneDrive? 

    SpecificFromat -> XlFileFormat.XXXX. Extension is xlsx (as stated in the Threads Header), its also the extension for XlFileFormat.Default... when using Office 2010 or higher (which i am)

    >> In my first reply, I shared how to open the xlsx file, save it as csv file and then open the csv file again.

    As CSV yeah... i dont want to rename or move the file... i want to open, edit and overwrite it, i thought it was clear

    >> Could you share us how you tried and where it did not work?

    Its all in the code i provided... it always fails on Open when its a file created by the provided Code... its a COM exception so i cannot go into any details.

    >> Keep in mind, when you open the file or save the file with the specific format, you need to keep the extension of the file consistent with the XlFileFormat in your open and saveas methods

    Yeah... i did that from the beginning... but it didnt work so i tried other combinations too... which (in this case like i expected) also threw the same error

    This is the process regarding Excel:

    First run

    Open EXCEL-Instance (Interop) -> Create Workbook -> Write to it -> Save it (again: xslx format with many combinations, "XlFileFormat.XmlOpenWorkbook" is desired) -> no Exception

    Second run

    Open EXCEL-Instance (Interop) -> Open Workbook (since it already exitsts) -> Exception -> WTF (try with different XlFileFormats but still the same result -> Exception)

    ...and just out of curiosity:

    Open EXCEL-Instance (the real thing... not Interop) -> open the created file that raised Exceptions in the Interop.Open-Method -> WTF!!! it opens with no Exceptions or complains

    ALSO: There are no Permissions used anywhere! so its not that

    I dont have the file here right now... different location and PC
    • Edited by MDeero Sunday, July 5, 2015 7:47 AM
    Sunday, July 5, 2015 7:47 AM
  • Hi MDeero,

    I have checked the Workbooks.Open method, and found you do not need to set the XlFileFormat.xlOpenXMLWorkbook as the format when you want to open it. The format parameter  is used when you want to open a text file.

    # Workbooks.Open method
    https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open.aspx

    I suggest you open it with replacing XlFileFormat.xlOpenXMLWorkbook with System.Reflection.Missing.Value.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 7, 2015 9:58 AM
  • Thanks for sticking with this post Edward...

    I just tried leaving the Format-parameter of the Open method empty, it didn't work either...

    I am really greatful for your help, I just have to give up on this... cause it just won't work.

    I may return to this question in the far future though, so if anyone stumbles on the solution to the problem at hand, please reply...

    Until then... happy coding (wich I will now have, since I will abandon this project ;-) )

    Wednesday, July 8, 2015 11:03 AM
  • For anyone having a similar Problem:

    The Excel Application Object (with which we create a File) needs to be disposed, as do all the "Worksheets" etc., so that the File can be freed.

    Cheers


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.

    • Marked as answer by MDeero Wednesday, March 15, 2017 10:30 PM
    Wednesday, March 15, 2017 10:30 PM