set_Value with xlRangeValueXMLSpreadsheet

[slawek-pelka-3301]

Hi!
I am new here, I stumbled upon the netoffice library and I am amazed by what it offers, it perfectly suits my need.
However.
I encounter the roadblock when I try to my old Microsoft.Interop code to netoffice.
Example code below throws exception:

var xml = activeSheet.get_Range("A1", Type.Missing).get_Value(rangeValueDataType: XlRangeValueDataType.xlRangeValueXMLSpreadsheet);
activeSheet.get_Range("B1", Type.Missing).set_Value(rangeValueDataType: XlRangeValueDataType.xlRangeValueXMLSpreadsheet, xml);

The exception is as follows:

> NetOffice.Exceptions.PropertySetCOMException
>   HResult=0x80004005
>   Message=Failed to proceed PropertySet on Excel.Range=>Value.
>   Source=NetOffice
>   StackTrace:
>    at NetOffice.Invoker.PropertySet(ICOMObject comObject, String name, Object[] value)
>    at NetOffice.CorePropertySetExtensions.ExecutePropertySet(Core value, ICOMObject caller, String name, Object newValue, Object argument)
>    at NetOffice.ExcelApi.Range_.set_Value(Object rangeValueDataType, Object value)
>    at AddInForConnect.ExcelController.DownloadTest(Int32 number) in C:\Repos\excel_addin_for_connect\AddIn\AddInForConnect\ExcelController.cs:line 111
>    at AddInForConnect.ExcelController.ShowWizard() in C:\Repos\excel_addin_for_connect\AddIn\AddInForConnect\ExcelController.cs:line 46
>    at AddInForConnect.CustomRibbon.OnShowWizard(IRibbonControl control) in C:\Repos\excel_addin_for_connect\AddIn\AddInForConnect\CustomRibbon.cs:line 79

Inner Exception 1:
TargetInvocationException: Exception has been thrown by the target of an invocation.

Inner Exception 2:
COMException: Type mismatch. (Exception from HRESULT: 0x80020005 (**DISP_E_TYPEMISMATCH**))

This roadblock is on my critical path, please give me a hope I don't have to return to Microsoft.Interop ;-)

If anybody asks why I am using this technique: XmlSpreadsheet is very efficient way of putting large amount of data to live excel spreadsheet. This minimizes number of calls to interop especially if you want add formatting (and I use e.g. setting colors of text etc).

[jozefizso]

Hi, do you have sample project which reproduces the issue?

[slawek-pelka-3301]

Thanks for looking into this!

It's reproducible 100% of times.

Just as reminder:
Reading value of cells in xmlSpreadsheet format works as expected.
Only attempt to assign cell's value using this format throws an exception.

Below I modified standard netoffice excel Example01, by removing creating shapes (not necessary for reproduce the issue) and added two lines to simulate putting A1 cell value "We have 2 simple shapes created." with xmlSpreadsheet equivalent, making it as explicit as possible:

    public void RunExample()
    {
        // start excel and turn off msg boxes
        Excel.Application excelApplication = new Excel.Application();
        excelApplication.DisplayAlerts = false;

        // create a utils instance, no need for but helpful to keep the lines of code low
        CommonUtils utils = new CommonUtils(excelApplication);

        // add a new workbook
        Excel.Workbook workBook = excelApplication.Workbooks.Add();
        Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

       //workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";

        //var xml = workSheet.Cells[1, 1].get_Value(rangeValueDataType: XlRangeValueDataType.xlRangeValueXMLSpreadsheet);

       // putting hardcoded string instead of reading excel value to make it more explicit
       var xml = "<?xml version=\"1.0\"?>\r\n<?mso-application progid=\"Excel.Sheet\"?>\r\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n <Styles>\r\n  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n   <Alignment ss:Vertical=\"Bottom\"/>\r\n   <Borders/>\r\n   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>\r\n   <Interior/>\r\n   <NumberFormat/>\r\n   <Protection/>\r\n  </Style>\r\n </Styles>\r\n <Worksheet ss:Name=\"Sheet1\">\r\n  <Table ss:ExpandedColumnCount=\"1\" ss:ExpandedRowCount=\"1\"\r\n   ss:DefaultRowHeight=\"14.4\">\r\n   <Row>\r\n    <Cell><Data ss:Type=\"String\">We have 2 simple shapes created.</Data></Cell>\r\n   </Row>\r\n  </Table>\r\n </Worksheet>\r\n</Workbook>\r\n";
       workSheet.Cells[1, 1].set_Value(rangeValueDataType: XlRangeValueDataType.xlRangeValueXMLSpreadsheet, xml);

        // save the book - utils want build the filename for us
        string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example01", DocumentFormat.Normal);
        workBook.SaveAs(workbookFile);

        // close excel and dispose reference
        excelApplication.Quit();
        excelApplication.Dispose();

        // show end dialog
        HostApplication.ShowFinishDialog(null, workbookFile);
    }

[slawek-pelka-3301]

Let me know if I can help further,
Best Regards!

[JD-Robbs]

Same here - calling set_Value throws the same Exception.

Unfortunately, only downgrading to 1.7.3 fixed it for me.

[slawek-pelka-3301]

Thank You JD_Robbs for sharing the info.
Could be helpful when I will get back to this project, will consider downgrading.
On the other hand, I am curious what has changed to introduce the issue.
Unfortunately I have not enough time atm to compare branches :)