Interessante foutmelding bij export naar Excel

Onlangs hadden we te maken met een interessant probleem. Onze software maakt een Excel-bestand aan via een .NET-library en dit bestand kon goed worden ingelezen in Excel. Het was een xlsx-bestand, dus OpenXML. Wanneer dit bestand echter werd geïmporteerd in een SQL-database via de Import and Export Data-tool, kwam er een foutmelding tevoorschijn: unexpected error from external database driver. Wat was er aan de hand? Na een speurtocht kwam ik erachter en vond ik de oplossing.

Het was in ieder geval vrij snel duidelijk dat het importeren wél werkte als het bestand in Excel opnieuw werd opgeslagen. Dat was een handig aanknopingspunt. Kennelijk maakt de .NET-library die wij gebruiken (DocumentFormat.OpenXml) een ander bestand dan Excel zelf, terwijl het toch dezelfde data bevat.

Als je het correcte en het incorrecte bestand vergeleek, dan was al zichtbaar dat de bestandsgrootte verschilde - de inhoud van het bestand was dus anders. Het is een xlsx-bestand en dat betekent dat het in feite een aantal XML-bestanden zijn in een aantal folders die zijn samengevoegd in een ZIP-bestand, met toevallig de extensie xlsx.

Om uit te zoeken wat er cruciaal verschilde tussen de twee bestanden, heb ik data uit het correcte bestand gehaald, zoals bepaalde XML-bestanden of XML-elementen in een bestand. Ik werd wel iets wijzer, maar toch kon ik het niet helemaal verklaren. Lag het aan SharedStrings? Een manier om gegevens niet per cel op te slaan, maar vanuit een cel te verwijzen naar gedeelde waardes. Nee, dat was niet het geval. Tegelijkertijd vulde ik het incorrecte bestand aan met elementen, nodes en bestanden. Bij elke toevoeging pakte ik het bestand in en probeerde ik het te openen en te importeren, totdat het lukte.

Wat bleek nu uiteindelijk het probleem te zijn? Ik zag dat in de correcte versie (die opgeslagen door Excel zelf), de XML-elementen in de worksheet een attribuut hadden dat de verkeerde niet had, namelijk: "r". R is CellReference: https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.celltype.cellreference?view=openxml-2.8.1. Het ontbreken ervan levert geen problemen op in Excel zelf, maar andere applicaties kunnen er niet altijd mee omgaan.

De oplossing bestond uit twee stappen:

1. Het toevoegen van CellReference aan cellen: cell.CellReference = ColumnKey + RowNumber //bijvoorbeeld "A1";

2. Oók CellReference toevoegen aan de rijen. Row heeft echter geen CellReference, dus daar moest ik het XML-attribuut op een andere manier toevoegen: row.SetAttribute(new OpenXmlAttribute("r", "", RowNumber));

Na deze kleine toevoegingen werd het bestand meteen op zo'n manier opgeslagen dat het kon worden geïmporteerd zonder het eerst opnieuw op te slaan in Excel.