Workbook.SaveAsXMLData Method return XlXmlExportResult.xlXmlExportValidationFailed

As part of our daily work we have an excel sheet that contains the entire layout of out deployment (for installation) and we use an automatic mechanism to create the installation package, the process of using excel is only for simplicity of viewing for our end users and the data is being exported to XML for usage of the process.

Yesterday i wasted almost 30 min trying to understand WHY the following code was failing

Excel.XlXmlExportResult result = oExcel.ActiveWorkbook.XmlMaps["Map1"].Export("export.xml", true);
if (result != Excel.XlXmlExportResult.xlXmlExportSuccess)
{
   throw new ApplicationException("failed writing file");
}

meaning why the hell did the method return xlXmlExportValidationFailed?, while the file was exported and looks “right”.

Well apparently one of our developers checked-in update version of the excel and one of the columns that was mapped to a numeric field was empty –> violation of the XSD! and that was the reason that the file was created and the method returned error (validation failed)

To mitigate the issue you can use the IsExportable method to validate XSD

Hope that helps you