W programie Microsoft Excel można łatwo importować dane w formacie XML (Extensible Markup Language) utworzone w innych bazach danych lub aplikacjach, mapować elementy XML ze schematu XML na komórki arkusza oraz eksportować poprawione dane XML w celu umożliwienia interakcji z innymi bazami danych i aplikacjami. Dzięki tym funkcjom XML program Office Excel staje się generatorem plików danych XML o znajomym interfejsie użytkownika.
W tym artykule
Dlaczego warto używać danych XML w programie Excel?
XML to technologia przeznaczona do udostępniania danych strukturalnych i zarządzania nimi w pliku tekstowym czytelnym dla człowieka. Dane XML są zgodne ze wskazówkami standardu branżowego i mogą być przetwarzane przez wiele różnych baz danych i aplikacji. Używając języka XML, projektanci aplikacji mogą tworzyć własne dostosowane tagi, struktury danych i schematy. Podsumowując, stosowanie języka XML znacznie ułatwia definiowanie, przesyłanie, sprawdzanie poprawności i interpretowanie danych podczas współpracy baz danych, aplikacji i organizacji.
Pliki schematów i danych XML
Program Excel współpracuje głównie z dwoma typami plików XML:
-
z plikami danych XML (xml), które zawierają tagi niestandardowe i dane strukturalne, oraz
-
Pliki schematów (xsd), które zawierają tagi schematu wymuszające reguły, takie jak typ danych i sprawdzanie poprawności.
W standardzie języka XML zdefiniowano również pliki przekształceń języka rozszerzonego arkusza stylów (XSLT), które są używane do stosowania stylów i przekształcania danych XML na inne formaty prezentacji. Przekształcenia można stosować przed zaimportowaniem plików XML do programu Excel i po ich wyeksportowaniu z programu Excel. Jeśli pliki XSLT są połączone z plikami danych XML importowanymi do programu Excel, można zastosować formatowanie lub nie stosować go przed dodaniem danych do arkusza, ale tylko w przypadku otwierania pliku XML za pomocą polecenia Otwórz w programie Excel. Aby wyświetlić pliki XML w folderze, wybierz typ pliku Pliki XML (*.xml), zanim klikniesz przycisk Otwórz.
Kluczowe scenariusze programu Excel i języka XML
Używając języka XML i programu Excel, można zarządzać skoroszytami i danymi w sposób, który wcześniej był niemożliwy lub bardzo trudny. Dzięki mapom XML można łatwo dodawać, identyfikować i wyodrębniać określone fragmenty danych biznesowych w dokumentach programu Excel. Na przykład faktura zawierająca nazwę i adres klienta lub raport z kwartalnymi wynikami finansowymi nie muszą już być raportami statycznymi. Te informacje można łatwo zaimportować z baz danych i aplikacji, wprowadzić do nich zmiany i wyeksportować je do tych samych lub innych baz danych i aplikacji.
Poniżej przedstawiono kluczowe scenariusze, w których są używane funkcje języka XML:
-
Rozszerzanie funkcjonalności istniejących szablonów programu Excel dzięki mapowaniu elementów XML na istniejące komórki. To ułatwia pobieranie danych XML z szablonu i umieszczanie danych w szablonie bez konieczności jego przebudowy.
-
Używanie danych XML jako danych wejściowych dla istniejących modeli obliczeniowych dzięki mapowaniu elementów XML na istniejące arkusze.
-
Importowanie plików danych XML do nowego skoroszytu.
-
Importowanie danych XML z usługi sieci Web do arkusza programu Excel.
-
Eksportowanie danych z zamapowanych komórek do plików danych XML niezależnie od innych danych w skoroszycie.
Podstawowy proces używania danych XML w programie Excel
Na poniższym diagramie przedstawiono współpracę różnych plików i operacji podczas używania danych XML w programie Excel. Ten proces składa się z pięciu etapów:
Praca z mapami XML
Możesz utworzyć lub otworzyć skoroszyt w programie Excel, dołączyć plik schematu XML (xsd) do skoroszytu, a następnie za pomocą okienka zadań Źródło XML zamapować elementy XML schematu na pojedyncze komórki lub tabele. Po zamapowaniu elementów XML na arkusz można importować dane XML do zamapowanych komórek i eksportować je z nich.
Dodanie pliku schematu XML (xsd) do skoroszytu powoduje utworzenie mapy XML. Mapy XML służą do tworzenia zamapowanych komórek i do zarządzania relacjami między zamapowanymi komórkami a poszczególnymi elementami w schemacie XML. Ponadto są one używane do powiązywania zawartości zamapowanych komórek z elementami w schemacie podczas importowania i eksportowania plików danych XML (xml).
Można tworzyć dwa typy zamapowanych komórek: komórki zamapowane jednokrotnie i komórki powtarzające się, które są widoczne jako tabele XML. Zamapowane komórki można przeciągać do dowolnego miejsca w arkuszu, a także organizować w dowolnym porządku — nawet w innym niż w schemacie XML, co czyni projektowanie arkusza jeszcze bardziej elastycznym. Można też wybierać elementy do mapowania oraz te, które nie będą mapowane.
Poniżej przedstawiono ważne reguły dotyczące używania map XML:
-
Skoroszyt może zawierać jedną lub więcej map XML.
-
Można mapować tylko jeden element na jedną lokalizację w skoroszycie jednocześnie.
-
Każda mapa XML jest niezależną jednostką, nawet jeśli wiele map XML w tym samym skoroszycie odwołuje się do tego samego schematu.
-
Mapa XML może zawierać tylko jeden element główny. W przypadku dodania schematu definiującego więcej niż jeden element główny pojawi się monit o wybranie elementu głównego do użycia w nowej mapie XML.
Używanie okienka zadań Źródło XML
Okienka zadań Źródło XML można używać do zarządzania mapami XML. Aby je otworzyć, należy na karcie Deweloper w grupie XML kliknąć przycisk Źródło. Na poniższym diagramie pokazano główne elementy tego okienka zadań.
1. Lista map XML dodanych do skoroszytu
2. Zawiera hierarchiczną listę elementów XML w bieżącej mapie XML
3. Umożliwia ustawianie opcji podczas pracy z okienkiem zadań Źródło XML i danymi XML, dotyczących na przykład sposobu wyświetlania podglądu danych i kontrolowania nagłówków
4. Otwiera okno dialogowe Mapy XML, w którym można dodawać i usuwać mapy XML oraz zmieniać ich nazwy
5. Umożliwia sprawdzanie, czy można wyeksportować dane XML za pośrednictwem bieżącej mapy XML
Typy elementów i ich ikony
W poniższej tabeli przedstawiono zestawienie typów elementów XML, z którymi program Excel może współpracować, wraz z reprezentującymi je ikonami.
|
Typ elementu |
Ikona |
|---|---|
|
Element nadrzędny |
|
|
Wymagany element nadrzędny |
|
|
Powtarzający się element nadrzędny |
|
|
Wymagany powtarzający się element nadrzędny |
|
|
Element podrzędny |
|
|
Wymagany element podrzędny |
|
|
Powtarzający się element podrzędny |
|
|
Wymagany powtarzający się element podrzędny |
|
|
Atrybut |
|
|
Wymagany atrybut |
|
|
Prosta zawartość w złożonej strukturze |
|
|
Wymagana prosta zawartość w złożonej strukturze |
|
Praca z komórkami zamapowanymi jednokrotnie
Komórka zamapowana jednokrotnie to komórka, która została zamapowana na niepowtarzający się element XML. Można ją utworzyć, przeciągając niepowtarzający się element XML z okienka zadań Źródło XML do pojedynczej komórki w arkuszu.
W przypadku przeciągania niepowtarzającego się elementu XML do arkusza można za pomocą tagu inteligentnego wybrać opcję dołączenia nazwy elementu XML jako nagłówka powyżej lub z lewej strony komórki zamapowanej jednokrotnie. Jako nagłówka można także użyć istniejącej wartości komórki.
W komórce zamapowanej jednokrotnie można też używać formuły, o ile komórka jest zamapowana na element XML mający typ danych definicji schematu XML (XML Schema Definition, XSD), który jest interpretowany przez program Excel jako liczba, data lub godzina.
Praca z komórkami powtarzającymi się w tabelach XML
Tabele XML pod względem wyglądu i działania przypominają tabele programu Excel. Tabela XML to tabela programu Excel zamapowana na jeden lub wiele powtarzających się elementów XML. Każda kolumna w tabeli XML reprezentuje jeden element XML.
Tabela XML jest tworzona, gdy:
-
zaimportowano plik danych XML za pomocą polecenia Importuj (w grupie XML na karcie Deweloper);
-
Użyj polecenia Otwórz z poziomu programu Excel, aby otworzyć plik danych XML , a następnie wybierz pozycję Jako tabela XML w oknie dialogowym Otwieranie pliku XML .
-
użyto polecenia Z importu danych XML (z przycisku polecenia Z innych źródeł w grupie Pobieranie danych zewnętrznych na karcie Dane) w celu zaimportowania pliku danych XML, a następnie wybrano pozycję Tabela XML w istniejącym arkuszu lub Nowy arkusz w oknie dialogowym Importowanie danych;
-
przeciągnięto jeden lub wiele powtarzających się elementów z okienka zadań Źródło XML do arkusza.
Podczas tworzenia tabeli XML nazwy elementów XML są automatycznie używane jako nagłówki kolumn. Te nagłówki można dowolnie zmieniać. Podczas eksportowania danych z zamapowanych komórek zawsze są jednak używane oryginalne nazwy elementów XML.
Podczas pracy z tabelami XML można korzystać z dwóch przydatnych opcji widocznych po kliknięciu przycisku Opcje w okienku zadań Źródło XML:
-
Automatycznie scal elementy podczas mapowania Wybranie tej opcji powoduje, że program Excel tworzy jedną tabelę XML z wielu pól upuszczanych na arkusz. Ta opcja działa, o ile pola są upuszczane w tym samym wierszu i jedno obok drugiego. Jeśli ta opcja jest wyczyszczona, każdy element jest widoczny jako osobna tabela XML.
-
Dane z nagłówkami Wybranie tej opcji sprawia, że jako nagłówki kolumn dla elementów powtarzających się, które są mapowane na arkusz, są używane istniejące dane nagłówków. Wyczyszczenie tej opcji sprawia, że jako nagłówki kolumn są używane nazwy elementów XML.
Tabele XML umożliwiają łatwe importowanie, eksportowanie, sortowanie, filtrowanie i drukowanie danych opartych na źródle danych XML. Jednak tabele XML podlegają pewnym ograniczeniom dotyczącym ich rozmieszczenia w arkuszu.
-
Tabele XML są oparte na wierszach, czyli rozrastają się od wiersza nagłówka w dół. Nie można dodawać nowych pozycji powyżej istniejących wierszy.
-
Nie można transponować tabeli XML w taki sposób, aby nowe pozycje były dodawane po prawej stronie.
Istnieje możliwość używania formuł w kolumnach zamapowanych na elementy XML z typem danych XSD (XML Schema Definition), który jest interpretowany w programie Excel jako liczba, data lub godzina. Podobnie jak w tabeli programu Excel, formuły w tabeli XML są wpisywane w całej kolumnie w miarę dodawania do tabeli nowych wierszy.
Zagadnienia dotyczące zabezpieczeń map XML
Mapa XML i informacje o jej źródle danych są zapisywane razem ze skoroszytem programu Excel, a nie z określonym arkuszem. Złośliwy użytkownik może podejrzeć te informacje, używając makra języka VBA (Microsoft Visual Basic for Applications) firmy Microsoft. Jeśli ponadto skoroszyt zostanie zapisany jako plik w formacie Office Open XML z obsługą makr programu Excel, te informacje mapy będzie można wyświetlać w Notatniku firmy Microsoft lub w innym programie do edycji tekstu.
Aby nadal używać informacji mapy, ale usunąć potencjalnie poufne informacje o źródle danych, można usunąć ze skoroszytu definicję źródła danych schematu XML, zachowując możliwość eksportowania danych XML, przez wyczyszczenie pola wyboru Zapisz definicję źródła danych w skoroszycie w oknie dialogowym Właściwości mapy XML, które jest dostępne po wybraniu polecenia Właściwości mapy w grupie XML na karcie Deweloper.
W przypadku usunięcia arkusza przed usunięciem mapy jej informacje dotyczące źródeł danych (i ewentualne inne poufne informacje) będą w dalszym ciągu zapisane w skoroszycie. Jeśli skoroszyt jest aktualizowany w celu usunięcia poufnych informacji, przed usunięciem arkusza należy usunąć mapę XML, aby informacje mapy zostały zupełnie usunięte ze skoroszytu.
Praca ze schematem wywnioskowanym na podstawie danych
W przypadku importowania danych XML bez dodawania odpowiadającego im schematu XML w celu utworzenia mapy XML program Excel podejmuje próbę wywnioskowania schematu na podstawie tagów zdefiniowanych w pliku danych XML. Taki schemat jest przechowywany w skoroszycie i pozwala pracować z danymi XML, gdy ze skoroszytem nie powiązano schematu XML.
Po zaimportowaniu danych XML o schemacie wywnioskowanym na ich podstawie można dostosować okienko zadań Źródło XML. Wybierz opcję Podgląd danych w okienku zadań z przycisku Opcje , aby wyświetlić pierwszy wiersz danych jako przykładowe dane na liście elementów, jeśli zaimportowano dane XML skojarzone z mapą XML w bieżącej sesji programu Excel.
Wywnioskowanego w programie Excel schematu nie można wyeksportować jako osobnego pliku danych schematu XML (xsd). Mimo że istnieją edytory schematów XML i są dostępne inne metody tworzenia pliku schematu XML, użytkownik może nie mieć do nich dostępu lub wiedzy, jak z nich korzystać.
Eksportowanie danych XML
Dane XML można wyeksportować, eksportując zawartość zamapowanych komórek w arkuszu. Podczas tej operacji w programie Excel są stosowane następujące reguły ustalania, które dane należy zapisać i w jaki sposób:
-
Jeśli dla elementu opcjonalnego istnieją puste komórki, nie są tworzone puste elementy. Są jednak one tworzone, jeśli puste komórki istnieją dla elementu wymaganego.
-
Do zapisywania danych jest używane kodowanie UTF-8 (Unicode Transformation Format-8).
-
Wszystkie przestrzenie nazw są definiowane w elemencie głównym XML.
-
Program Excel zastępuje istniejące prefiksy przestrzeni nazw. Domyślna przestrzeń nazw ma przypisany prefiks ns0. Kolejne przestrzenie nazw mają oznaczenie ns1, ns2–ns<> gdzie <liczba> jest liczbą przestrzeni nazw zapisanych w pliku XML.
-
Węzły komentarzy nie są zachowywane.
Możesz wyświetlić okno dialogowe Właściwości mapy XML (kliknij pozycję Właściwości mapy w grupie XML na karcie Deweloper ). a następnie użyj opcji Sprawdź poprawność danych względem schematu na potrzeby importowania i eksportowania (domyślnie aktywnego), aby określić, czy program Excel sprawdza poprawność danych na mapie XML podczas eksportowania danych. Kliknij tę opcję, aby upewnić się, że eksportowane dane XML są zgodne ze schematem XML.
Używanie pliku w formacie Office Open XML z obsługą makr programu Excel
Skoroszyt programu Excel można zapisać w różnych formatach plików, w tym w formacie Office Open XML (xlsm) z obsługą makr programu Excel. Program Excel ma zdefiniowany schemat XML definiujący zawartość skoroszytu programu Excel, w tym tagi XML przechowujące wszystkie informacje o skoroszycie, takie jak dane i właściwości, oraz definiujący ogólną strukturę skoroszytu. Aplikacje niestandardowe mogą używać tego pliku formatu Office XML z obsługą makr programu Excel. Na przykład deweloperzy mogą chcieć utworzyć aplikację niestandardową w celu wyszukiwania danych w wielu skoroszytach zapisanych w tym formacie i utworzenia systemu raportowania na podstawie znalezionych danych.
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w Społecznościach.
