Het Excel bestandsformaat

Pagina's in dit artikel

1. Werkblad gegevens
2. Besturingselementen

Werken met werkblad gegevens in een Excel xlsx/xlsm bestand

In dit hoofdstuk wordt uitgelegd hoe de inhoud van een cel op een werkblad kan worden gelezen en gewijzigd door rechtstreeks met de XML van het bronbestand te werken.

Het juiste werkblad vinden

Allereerst een tip hoe de inhoud van een Open XML bestand kan worden bekeken. Een Excel bestand (met de extensies .xlsx en .xlsm) is niets anders dan een zip bestand met daarin een serie mappen en bestanden. Je kan deze inhoud bekijken, door achter de bestandsnaam de extensie .zip in te voeren. Vervolgens kan je rechtsklikken op het bestand en verkennen kiezen.

Het XML pakket in een Excel xlsx/xlsm bestand kent de onderstaande struktuur:

Inhoud van een Excel xlsm bestand.

In deze struktuur duiken we in de "xl" map, welke (in het voorbeeld bestand) bevat:

Inhoud van de "xl" map

In deze map bevindt zich het bestand Workbook.xml. Hierin bevindt zich op zijn beurt een gedeelte dat voor dit hoofdstuk relevant is:

Deel van de XML in "Workbook.xml"

Stel dat je iets wilt toevoegen aan het werkblad "Comments". Dan moet je dus weten welk XML bestand de data van dit werkblad bevat. Het sleutel element waar het om gaat is  het r:id gedeelte in de hierboven getoonde xml, de r:id van het werkblad "Comments" is gelijk aan "rId7".

In de map xl\_rels staat een bestandje genaamd "Workbook.xml.rels". In dit bestand vind je het volgende stukje xml:

Sectie in "Workbook.xml.rels" welke laat zien welk bestand hoort bij ons werkblad

Zoals je kan zien, wordt de inhoud van werkblad "Comments" (rId7) weergegeven in het bestand genaamd "sheet7.xml". Je vindt dit bestand in de map "worksheets":

De map xl\worksheets en haar inhoud.

Een celwaarde lezen

Het werkblad "Comments" bevat dit:

De inhoud van het werkblad "Comments" (cell B2 heeft commentaar toegevoegd)

Deze inhoud ziet er in XML formaat (het bestand sheet7.xml) als volgt uit:

XML in sheet7.xml (niet relevante deel is ingeklapt) met in beeld de informatie van cellen A1, A2 en A3

Stel dat je geinteresseerd bent in de inhoud van cel A1. Hoe vind je die informatie?

Omdat er dit staat: <c r="A1" t="s">

Weet je dat cel A1 tekst bevat (t="s"). De volgende regel: <v>28</v> vertelt je vervolgens dat we deze tekst kunnen vinden als het element met index 28 in het bestand genaamd "xl/SharedStrings.xml"

Het bovenste deel van dat bestand ziet er als volgt uit:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="41" uniqueCount="46">

Je ziet hier dat er 46 unieke teksten in dit bestand voorkomen. Omdat de index bij nul begint, en de xml van sheet7.xml wijst naar index # 28, moet je dus het 29ste "<si>" element uit het XML bestand hebben:

Niet eens erg lastig!

Tekst aan een cel toevoegen

Stel dat je een nieuw stuk tekst wilt toevoegen aan een rij op het werkblad "Comments", hoe moet dat dan?

Neem aan, dat deze tekst nog niet voorkomt in het bestand SharedStrings.xml (teksten mogen hier wel dubbel in voorkomen, maar het is uiteraard minder efficient).

Allereerst moet het aantal teksten bovenaan in het bestand worden gewijzigd. Verander:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="41" uniqueCount="46">

naar:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="42" uniqueCount="47">

Nu kan je je nieuwe tekst toevoegen aan het bestand. Op positie 29 in het xml bestand voeg je dan toe:

Toevoegen van een tekst aan het bestand sharedStrings.xml

Bestand sharedStrings.xml is hiermee klaar. Nu de wijzigingen aan sheet7.xml.

Hier is nogmaals het relevante deel van sheet7.xml:

<row r="1" spans="1:2">
  <c r="A1" s="33" t="s">
    <v>28</v>
  </c>
</row>
<row r="2" spans="1:2">
  <c r="A2">
    <v>12</v>
  </c>
</row>
<row r="3" spans="1:2">
  <c r="A3" s="34">
    <v>39218</v>
  </c>
</row>

Om een rij toe te voegen voeg je het vetgemaakte gedeelte toe:

<row r="1" spans="1:2">
  <c r="A1" s="33" t="s">
    <v>28</v>
  </c>
</row>
<row r="2" spans="1:2">
  <c r="A2">
    <v>12</v>
  </c>
</row>
<row r="3" spans="1:2">
  <c r="A3" s="34">
    <v>39218</v>
  </c>
</row>
<row r="4" spans="1:2">
  <c r="A4" s="33" t="s">
    <v>29</v>
  </c>
</row>

Klaar!! Hier zie je hoe het werkblad eruit ziet nadat het bestand in Excel is geopend:

Het resultaat van het bewerken van de xml weergegeven in Excel

Een getal toevoegen aan een cel

Het toevoegen van getallen aan cellen werkt op vergelijkbare manier als het toevoegen van tekst, met dat verschil dat de getallen rechtstreeks in het Sheet?.xml bestand zelf staan (geen verwijzing naar sharedStrings.xml) en dat je een getalsformaat kunt toevoegen.

Een cel met getalsformaat "general" en verder geen specifieke formattering wordt in sheet7.xml als volgt weergegeven:

<row r="5" spans="1:2">
  <c r="A5">
    <v>12</v>
  </c>
</row>

Cel formaten (stijlen) worden middels een index getal verwezen, als volgt (je ziet hier de info van cel A3):

<row r="3" spans="1:2">
  <c r="A3" s="34">
    <v>39218</v>
  </c>
</row>

Dit is toevallig een datum formaat.

De index van 34 verwijst naar een ander bestand binnen het pakket: Styles.xml. Dit bestand begint met een lijst van aangepaste getalsformaten. Verder naar beneden vind je een reeks verwijzingen met cel formaten genaams cellXfs, welke indien van toepassing terug verwijzen naar de lijst van aangepaste getals formaten bovenaan in het bestand.

De s="34" in Sheet?.xml verwijst naar één van de elementen in deze node en omdat ook dit een lijst is waarvan de telling met nul begint, moeten we het 35ste element uit deze lijst hebben:

Meer informatie over impliciete stijlen vind je hier: Standard ECMA-376 Office Open XML Formats: 2nd Edition Part 2.

Deze kruisverwijzing van het cel formaat wijst op zijn beurt naar de lijst met numFormatId’s, de lijst met FontId’s, de lijst met fillId’s en de lijst met borderId’s, welke allemaal terug te vinden zijn binnen Styles.xml. Denk wederom aan het feit dat alle lijsten met index nul beginnen.

Zoals je kunt zien is het toevoegen van formattering aan een cel op een werkblad tamelijk complex. Je moet eigenlijk nagaan, of de formatteringscompbinatie die je wilt hebben al bestaat als "stijl" combinatie, wat inhoudt dat alle individuele formatterings verwijzingen in het bestand styles.xml moeten worden nagelopen.

Indien je formatteringscombinatie nieuw is, dan zal je alle formatterings lijsten bij moeten werken met de eventueel nieuwe elementen en de stijl kruisverwijzingen tabel moeten bijwerken. Uiteraard zullen ook alle aantallen van deze lijsten moeten worden bijgewerkt.

Download

Je kan het bestand uit dit artikel hieronder downloaden:

Excel2007FileFormatDemo.zip