Von Excel zu XML (und zurück) 9

Wie man aus einer Excel-Tabelle eine XML-Datei erstellt
 

Auch wenn wir Excel-Fetischisten das nicht so gerne hören:
Es gibt noch andere Systeme da draußen in dieser bösen Welt, mit denen man hin und wieder Daten austauschen muss. Insbesondere in Zeiten des allgegenwärtigen Internets erfreut sich hier ein Format besonderer Beliebtheit:

Das XML-Format.

Wie man seine Excel-Tabelle als XML-Datei speichert oder eine vorliegende XML-Datei in eine Excel-Tabelle importiert, zeige ich im heutigen Artikel.

Und so geht’s:

Was ist eine XML-Datei?

XML ist eine sogenannte Auszeichnungssprache, mit deren Hilfe man hierarchisch strukturierte Daten in einer Textdatei abspeichern kann (siehe dazu auch Wikipedia).

Eine solche Struktur besteht in einer XML-Datei aus einzelnen Elementen. Jedes Element kann entweder einen Text oder ein weiteres untergeordnetes Element enthalten. Dadurch kann also eine Verschachtelung von Elementen entstehen. Jedes Element ist dabei von einem Anfangs- und einem End-Auszeichner umschlossen, den sogenannten Tags. Dies sieht z.B. so aus:
<element>Name</element>

Und eine Verschachtelung könnte so aussehen:
<adresse>
<strasse>Musterstrasse</strasse>
<ort>Entenhausen</ort>
</adresse>

Das soll uns als Hintergrundwissen erst einmal reichen.

Die Ausgangstabelle

Ausgangsbasis für unsere Übung soll eine sehr einfache Adressenliste sein, bestehend aus folgenden Spalten:

Die Ausgangstabelle: Eine einfache Adressenliste

Die Ausgangstabelle: Eine einfache Adressenliste

(Diese Adressenliste kannst du bei Bedarf hier herunterladen)

Diese simple Liste soll nun in eine XML-Datei umgewandelt werden. Damit Excel weiß, wie die Struktur der XML-Datei aussehen soll und welche Datentypen für die einzelnen Elemente verwendet werden, brauchen wir eine sogenannte Schema-Datei.

Eine Schema-Datei erstellen

Wer sich etwas in die Materie einlesen möchte, sei auf diesen Wikipedia-Artikel verwiesen. Dort werden auch die verschiedenen Datentypen beschrieben.

Um eine Schema-Datei zu erzeugen, brauchst du einen einfachen Texteditor. Der Windows-Editor reicht für unsere Zwecke aus, etwas mehr Komfort und Übersicht bietet Notepad++ (einfach in die Suchmaschine deiner Wahl eingeben und kostenlos herunterladen)

In den Editor gibst du folgende Daten ein:

Die XML-Schema-Datei

Die XML-Schema-Datei

Achte bei der Eingabe unbedingt darauf, dass jedes geöffnete Element auch wieder geschlossen werden muss (Notepad++ unterstützt dich dabei).

Die allererste Zeile ist nicht kriegsentscheidend, gehört aber der Vollständigkeit halber zu einer “sauberen” XML-Datei dazu:
<?xml version="1.0" encoding="UTF-8"?>

Damit legen wir eine Struktur fest, die aus dem Element “Adressen” besteht, welches untergeordnete Elemente namens “Adresszeile” enthalten kann. Dabei kann es keine oder beliebig viele Adresszeilen geben (Parameter minOccurs und maxOccurs).

Jedes Element “Adresszeile” besteht wiederum aus den Unterelementen “Vorname”, “Name”, “Strasse”, “PLZ” und “Ort”. Ich habe hier exakt die gleichen Bezeichnungen wie in der Excel-Tabelle verwendet, ebenso sollte die Reihenfolge Elemente genau mit der in der Tabelle übereinstimmen, das macht die anschließende Zuordnung einfacher.

Die Einrückungen sind nicht zwingend erforderlich, aus Gründen der Übersicht aber dringend zu empfehlen. So ist leichter zu erkennen, welche Elemente zusammengehören und ob jedes Element auch wieder geschlossen wurde.

Die Datei speicherst du anschließend unter dem Namen schema.xsd ab (wer sich die Tipperei ersparen möchte, kann sich hier die fertige Datei herunterladen)

Damit sind die Vorarbeiten abgeschlossen und wir wechseln wieder zu Excel.

Entwicklertools einblenden

Für den nächsten Schritt brauchen wir die Registerkarte mit den Entwicklertools, die standardmäßig in Excel nicht angezeigt wird. In diesem Artikel habe ich für die verschiedenen Excel-Versionen beschrieben, wie man dieses Menü einblendet.

Öffne nun die Entwicklertools und klicke dort auf die Schaltfläche “Quelle”:

Entwicklertools: Quelle

Entwicklertools: Quelle

Dadurch wird rechts der Arbeitsbereich “XML-Quelle” eingeblendet, in dem du dann unten auf die Schaltfläche “XML-Zuordnungen…” klickst:

XML-Zuordnungen verwalten

XML-Zuordnungen verwalten

In dem sich nun öffnenden Fenster wählst du über die Schaltfläche “Hinzufügen…” die zuvor erstellte Datei “schema.xsd”.

Die Schema-Datei hinzufügen

Die Schema-Datei hinzufügen

Nur kommt ein Fenster, in dem man den sogenannten Wurzelknoten auswählen muss, also die oberste Ebene in unserer Struktur. In unserem Beispiel ist das der Eintrag “Adressen”:

Das Wurzelelement auswählen

Das Wurzelelement auswählen

Wenn du danach die beiden Fenster mit OK geschlossen hast, sollte der Arbeitsbereich “XML-Quelle” wie folgt aussehen:

Die vorhandene XML-Zuordnung...

Die vorhandene XML-Zuordnung.

Klicke jetzt auf das oberste Element “Adressen” und ziehe es mit der Maus nach links über die Excel-Tabelle und lasse es es ganz links oben über der Überschrift “Vorname” fallen.

... muss der Tabelle zugewiesen werden

… muss der Tabelle zugewiesen werden

Die Tabelle verwandelt sich jetzt in eine formatierte Tabelle:

Formatierte Tabelle mit XML-Zuordnung

Formatierte Tabelle mit XML-Zuordnung

Solltest du die XML-Zuordnung an der falschen Stelle fallengelassen haben, drücke einfach die Tastenkombination Strg+Z, um die Aktion rückgängig zu machen, und versuche es anschließend nochmal.

Dass es sich dabei aber nicht um eine “normale” formatierte Tabelle handelt, erkennt man an verschiedenen Stellen Wenn man rechts im Fenster “XML-Quelle” auf eines der Adressenelement klickt, wird die entsprechende Spalte in der Tabelle markiert. Daran sieht man auch, ob die Zuordnung richtig funktioniert hat:

Ausgewählte Felder werden markiert

Ausgewählte Felder werden markiert

Außerdem enthält das Kontextmenü der Tabelle einen neuen Eintrag. Führe einen Rechtsklick mit der Maus irgendwo innerhalb der Tabelle aus und du findest jetzt den Menüpunkt “XML”:

Das neue Kontextmenü XML

Das neue Kontextmenü XML

Der letzte Schritt: Die XML-Datei erstellen

Wenn du bis hierher folgen konntest, hast du alles richtig gemacht und kannst jetzt endlich die XML-Datei erstellen. Wähle dazu entweder in dem oben gezeigten Kontextmenü den Punkt “XML | Exportieren…” oder die gleichlautende Schaltfläche in den Entwicklertools.

Du brauchst nur noch den gewünschten Speicherort auszuwählen und einen Dateinamen einzugeben. Der Dateityp ist automatisch auf “XML-Dateien (*.xlm) voreingestellt:

Die Tabelle in eine XML-Datei exportieren

Die Tabelle in eine XML-Datei exportieren

Zur Kontrolle des Ergebnisses kannst du die Datei in deinem Texteditor oder auch in einem Webbrowser öffnen:

Die XML-Datei im Webbrowser

Die XML-Datei im Webbrowser

…und anders herum geht natürlich auch!

Selbstverständlich kannst du diese XML-Definition jetzt auch dazu nutzen, die XML-Datei wieder in eine Excel-Tabelle umzuwandeln. Dazu löschen wir erst alle vorhandenen Datensätze aus der Tabelle, die formatierte Tabelle an sich muss aber erhalten bleiben:

Tabelle leeren

Tabelle leeren

Du ahnst es vermutlich schon: Ein Rechtsklick in die Tabelle, dann die Option “XML | Importieren…” (oder der gleiche Pfad über die Entwicklertools) bringen dich zur Auswahl der zu importierenden XML-Datei:

XML-Datei für den Import auswählen

XML-Datei für den Import auswählen

Und schon ist die Adressenliste in Excel wiederhergestellt.

Ein kleiner Tipp zum Schluss

Es könnte ja sein, dass du regelmäßig deine Adressentabelle durch einen XML-Import erweitern musst. Standardmäßig wird die vorhandene Tabelle aber immer überschrieben. In den Entwicklertools gibt es aber die Schaltfläche “Eigenschaften zuordnen”. In dem sich nun öffnenden Fenster aktivierst du einfach die Option “Vorhandene XML-Tabellen um neue Daten erweitern” und schon werden alle neuen Datensätze unten an die Tabelle angehängt:

Neue Datensätze anfügen

Neue Datensätze anfügen

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

9 Gedanken zu “Von Excel zu XML (und zurück)

  • Géza

    Hallo Martin,
    ich bin von deinen Beiträgen immer wieder begeistert. Oft kenne ich bereits einen Weg, du öffnest aber auch oft Kapitel auf, die ich noch nicht gelesen oder erlebt habe. Zudem gibt es wirklich immer mehrere Wege zum Ziel.
    Vielen Dank, weiter so!

    • Martin Weiß Autor des Beitrags

      Hallo Géza,

      vielen Dank für dein schönes Feedback und ich bin genau Deiner Meinung: Viele Wege führen nach Rom!

      Schöne Grüße,
      Martin

  • BoRado

    Hallo Martin,
    bei meinem Excel (Office 2016) taucht nach dem Zuweisen der Zuordnung “Adressen” zu Zelle “Vorname” kein Eintrag “XML” im Kontextmenü auf. Auch in den Entwicklertools enthält der Eintrag XML in den Ribbons lediglich den Button “Quelle”. Der öffnet/schließt aber lediglich das schon bekannte XML-Quelle-Dialog-Fenster. Muss die XML-Funktionalität womöglich noch irgendwo aktiviert werden?
    Beste Grüße und danke für die vielen tollen Tipps,
    BoRado

    • Martin Weiß Autor des Beitrags

      Hallo Bo,

      wurde die Adressenliste durch die Zuordnung tatsächlich in eine formatierte Tabelle umgewandelt? Wenn nicht, wäre das ein Indiz dafür, dass die Zuordnung nicht geklappt hat.
      Beim Öffnen der schema.xsd wird man gefragt, welcher Eintrag der Wurzelknoten sein soll: Adressen oder Adresszeile. Hattest Du hier vielleicht fälschlicherweise “Adresszeile” ausgewählt?
      Du könntest noch versuchen, jedes Element einzeln zuzuordnen. Also rechts aus der XML-Quelle das Feld Vorname nach links in die Tabelle auf das Feld Vorname ziehen usw. Dabei sollte jedesmal das betreffende Feld als formatierte Tabelle angezeigt werden.

      Ansonsten fällt mir spontan auch nichts anderes ein. Die XML-Funktionalität muss meines Wissens nach nirgendwo explizit aktiviert werden.

      Schöne Grüße,
      Martin

      • BoRado

        Hallo Martin,

        auch die wiederholte Zuordnung unter genauer Beachtung Deiner Hinweise – danke auch dafür – hat leider keine Veränderung herbeigeführt. Nach wie vor keine XML-Funktionalität in Kontextmenü oder Ribbons. Mal sehen, was die Suchmaschine mit den vielen “G’s” und “O’s” dazu sagt.

        Beste Grüße,
        BoRado

        Excel-Version: Microsoft Office Professional Plus 2016

      • BoRado

        Hallo Martin,

        mit Unterstützung von Luschi (Herber-Forum) hat zu guter letzt nur die komplette De- und Neuinstallation des Office Pakets geholfen, die XML-Funktionalität wieder herzustellen. Die Office-eigene Reparatur-Funktion hatte auch keine Wirkung. Anscheinend war das entsprechende Office-Modul dauerhaft abgeraucht.
        Vielen Dank für Support und Tipps. Oft hilft das schon, um nicht zu früh genervt das Handtuch zu werfen… 😉

        Beste Grüße,
        Bo Rado

        MS-Office Professional Plus 2016

        • Martin Weiß Autor des Beitrags

          Hallo Bo,

          dass die Office-Installation zerschossen war, damit konnte natürlich keiner rechnen. Aber schön, dass Du noch Unterstützung gefunden hast und danke für die Rückmeldung.

          Schöne Grüße,
          Martin

  • Henrich

    Danke bin immer wieder begeistert über die informativen Beiträge!
    Ich hätte eine Frage: Wenn man auf eine strukturierte Tabelle mit Namen ‘Adressen’ zugreifen will, wie die obige mit der Spalte ‘Vorname’.
    Gibt es eine einfache Möglichkeit z.B. auf die 5. Zeile des Datenbereiches von Vorname zuzugreifen. Dabei würde ich gerne den Verweis mittels der strukturierten Methoden durchführen. Also in etwa so ‘=Adressen[[Vorname][#Daten]];Zeile5’ (so geht es natürlich nicht!)
    Gibt es so was?
    Danke
    Heinrich

    • Martin Weiß Autor des Beitrags

      Hallo Heinrich,

      freut mich, wenn die Artikel nützlich sind.
      Was Deine Frage zu den strukturierten Verweisen angeht, muss ich Dich leider enttäuschen. Die von Dir angedachte Möglichkeit gibt es leider nicht. Nur für Verweise auf Elemente in der gleichen Zeile gibt es einen strukturierten Verweis mit dem @-Symbol, z.B. [@Vorname].
      Sobald ein Element in einer andere Zeile angesprochen werden soll, muss man leider auf normale Bezüge zurückgreifen.

      Schöne Grüße,
      Martin