Microsoft Excel lihtsustab laiendatava märgistuskeele (XML) andmete importimist muudest andmebaasidest ja rakendustest, XML-elementide vastendamist XML-skeemist töölehe lahtritega ning redigeeritud XML-andmete eksportimist teiste andmebaaside ja rakendustega suhtlemiseks. Võite neid XML-i funktsioone võtta nagu Exceli muutmist tuttava kasutajaliidesega XML-andmefaili koosturiks.
Selle artikli teemad
-
- XML-allika tööpaani kasutamine
- Elemenditüübid ja nende ikoonid
- Ühe vastendusega lahtritega töötamine
- Korduvate lahtritega töötamine XML-tabelites
- XML-vastenduse turvakaalutlused
- XML-andmete importimine
- Tuletatud skeemiga töötamine
- XML-andmete eksportimine
- Exceli makrotoega Office Open XML-i vormingu kasutamine
Miks kasutada XML-i Excelis?
XML on inimloetavas tekstifailis struktureeritud andmete haldamiseks ja ühiskasutuseks mõeldud tehnoloogia. XML järgib tööstusliku standardi põhimõtteid ning seda saab töödelda mitmete andmebaaside ja rakenduste abil. XML-i kasutades saavad rakenduste arendajad luua enda kohandatud silte, andmestruktuure ja skeeme. Lühidalt: XML lihtsustab oluliselt andmete määratlemist, edastamist, valideerimist ja tõlgendamist andmebaaside, rakenduste ja asutuste vahel.
XML-andme- ja -skeemifailid
Excel töötab põhiliselt kahte tüüpi XML-failidega:
- XML-andmefailid (.xml), mis sisaldavad kohandatud silte ja struktureeritud andmeid;
- Skeemifailid (.xsd), mis sisaldavad reegleid jõustavaid skeemisilte (nt andmetüüp ja valideerimine).
XML-standard määratleb ka laiendatavas laadilehekeeles (.xslt) faile, mida kasutatakse laadide rakendamiseks ja XML-andmete teisendamiseks erinevateks esitusvorminguteks. Neid teisendusi saate rakendada enne XML-failide importimist Exceli ja pärast XML-failide eksportimist Excelist. Kui XSLT-failid on lingitud Exceli imporditavate XML-andmefailidega, on teil võimalus enne andmete töölehele lisamist vormindamist rakendada või mitte rakendada, kuid ainult siis, kui avate XML-faili Exceli käsuga Ava. Kaustas asuvate XML-failide vaatamiseks valige enne nupu Ava klõpsamist failitüüp XML-failid (*.xml).
Tähtsaimad XML-i ja Exceli stsenaariumid
XML-i ja Exceli abil saate hallata töövihikuid ja andmeid nii, nagu varem oli võimatu või väga keeruline. XML-vastenduste abil saate Exceli dokumentides kergesti lisada, tuvastada ja ekstraktida äriandmete kindlaid üksusi. Näiteks arve, mis sisaldab kliendi nime ja aadressi, või aruanne, mis sisaldab viimase kvartali majandustulemusi, ei ole enam pelgalt staatiline aruanne. Seda teavet saab hõlpsalt andmebaasidest ja rakendustest importida, muuta ja eksportida samasse või mõnda muusse andmebaasi või rakendusse.
Järgnevalt on üles loetletud tähtsaimad stsenaariumid, millega XML-i funktsioonid on loodud tegelema.
- Olemasolevate Exceli mallide funktsionaalsust saab laiendada, vastendades XML-elemendid olemasolevatele lahtritele. See lihtsustab XML-andmete sisestamist malli ja nende sealt kättesaamist mallide ümberkujundamiseta.
- XML-andmeid saab kasutada sisendina teie olemasolevatesse arvutusmudelitesse, vastendades XML-elemendid olemasolevatele töölehtedele.
- XML-andmefailide saab importida uude töövihikusse.
- XML-andmete saab importida veebiteenusest teie Exceli töölehele.
- Vastendatud lahtrite andmeid saab eksportida XML-andmefailidesse muudest töövihiku andmetest sõltumata.
XML-andmete Excelis kasutamise põhiprotsess
Järgmine diagramm näitab, kuidas erinevad failid ja toimingud XML-i Exceliga kasutamisel koos töötavad. Üldjoontes on protsessil viis faasi.
XML-skeemifaili (.xsd) lisamine töövihikusse
XML-skeemi elementide vastendamine üksikute lahtrite või XML-tabelitega
XML-andmefaili (.xml) importimine ja XML-elementide sidumine vastendatud lahtritele
Andmete sisestamine, vastendatud lahtrite teisaldamine ja Exceli funktsioonide kasutamine, säilitades samal ajal XML-struktuuri ja definitsioonid
Parandatud andmete eksportimine vastendatud lahtritest XML-andmefaili
XML-vastendustega töötamine
Saate luua või avada töövihiku Excelis, manustada töövihikule XML-skeemifaili (.xsd) ja seejärel kasutada tööpaani XML-allikas skeemi XML-elementide vastendamiseks üksikute lahtrite või tabelitega. Pärast XML-elementide vastendamist töölehele saate importida ja eksportida XML-andmeid vastendatud lahtritele ja lahtritelt välja.
Töövihikule XML-skeemifaili (.xsd) lisades loote XML-vastenduse. Üldiselt kasutatakse XML-vastendusi vastendatud lahtrite loomiseks ning XML-skeemil asuvate vastendatud lahtrite ja üksikute elementide vahelise suhte haldamiseks. Lisaks kasutatakse neid XML-vastendusi vastendatud lahtri sisu sidumiseks elementidega skeemil, kui te impordite või ekspordite XML-andmefaile (.xml).
Luua saab kahte liiki vastendatud lahtreid: ühe vastendusega lahtrid ja korduvad lahtrid (mis kuvatakse XML-tabelitena). Töölehe kujundamise paindlikumaks muutmiseks saab vastendatud lahtrid lohistada kuhu iganes töölehel ja ükskõik, millisesse järjestusse panna – isegi XML-skeemist erinevasse. Lisaks saate valida, millist elementi vastendada ja millist mitte.
XML-vastenduste kasutamise kohta on oluline teada järgmisi reegleid.
- Töövihik võib sisaldada ühte või mitut XML-vastendust.
- Korraga saab vastendada ühe elemendi ühele asukohale töövihikus.
- Iga XML-vastendus on iseseisev olem, isegi kui mitu XML-vastendust ühes töövihikus samale skeemile viitavad.
- XML-vastendus saab sisaldada ainult ühte juurelementi. Kui lisate skeemi, mis määratleb rohkem kui ühe juurelemendi, palutakse teil valida juurelement, mida uue XML-vastenduse jaoks kasutada.
XML-allika tööpaani kasutamine
XML-vastenduste haldamiseks kasutatakse tööpaani XML-allikas. Selle avamiseks klõpsake menüü Arendaja jaotises XML nuppu Allikas. Järgmine diagramm näitab selle tööpaani peamisi funktsioone.
1. Loetleb töövihikusse lisatud XML-vastendused
2. 2. Kuvab praegu loetletud XML-vastenduse XML-elementide hierarhilise loendi
3. 3. Seab suvandid tööpaanil XML-allikas ja XML-andmetega töötamisel (nt kuidas vaadata andmete eelvaadet ja juhtpäiseid).
4. 4. Avab dialoogiboksi XML-vastendused , mille abil saate XML-vastendusi lisada, kustutada või ümber nimetada.
5. 5. Kontrollib, kas xml-andmeid saab praeguse XML-vastenduse kaudu eksportida.
Elemenditüübid ja nende ikoonid
Järgmine tabel teeb kokkuvõtte igat tüüpi XML-elementidest, millega Excel töötada saab, ja ikoonidest, mida igat tüüpi elementide väljendamiseks kasutatakse.
| Elemendi tüüp | Ikoon |
|---|---|
| Emaelement |
|
| Vajalik emaelement |
|
| Korduv emaelement |
|
| Vajalik korduv emaelement |
|
| Tütarelement |
|
| Vajalik tütarelement |
|
| Korduv tütarelement |
|
| Vajalik korduv tütarelement |
|
| Atribuut |
|
| Vajalik atribuut |
|
| Lihtne sisu keerukas struktuuris |
|
| Vajalik lihtne sisu keerukas struktuuris |
|
Ühe vastendusega lahtritega töötamine
Ühe vastendusega lahter on lahter, mida on vastendatud mittekorduvale XML-elemendile. Ühe vastendusega lahtri saate luua, kui lohistate mittekorduva XML-elemendi tööpaanilt XML-allikas üksikusse lahtrisse töölehel.
Kui lohistate mittekorduva XML-elemendi töölehele, saate nutika sildi abil valida, kas lisada XML-elemendi nimi päisena ühe vastendusega lahtrist üles või vasakule või kasutada päisena olemasolevat lahtriväärtust.
Ühe vastendusega lahtris saab kasutada ka valemit, juhul kui lahter on vastendatud XML-elemendile XML-skeemi määratluse andmetüübiga (XSD), mida Excel tõlgendab arvu, kuupäeva või kellaajana.
Korduvate lahtritega töötamine XML-tabelites
XML-tabelid on välimuselt ja funktsionaalsuselt sarnased Exceli tabelitega. XML-tabel on Exceli tabel, mis on vastendatud ühe või rohkema korduva XML-elemendiga. Iga XML-tabeli veerg tähistab XML-elementi.
XML-tabel luuakse järgmistel juhtudel:
- XML-andmefaili importimiseks kasutage käsku Impordi (menüü Arendaja jaotises XML).
- Kasutage XML-andmefaili avamiseks Exceli käsku Ava ja seejärel valige dialoogiboksis XML-i avamine suvand XML-tabelina.
- XML-andmefaili importimiseks kasutage käsku XML-andmete impordist (käsunupu Muudest allikatest kaudu, jaotises Välisandmete toomine vahekaardil Andmed) ja valige siis dialoogiboksis Andmete importiminesuvand XML-tabel olemasoleval töölehel või Uus tööleht.
- kui lohistate ühe või mitu korduvat elementi tööpaanilt XML-allikas töölehele.
Kui loote XML-tabeli, kasutatakse veerupäistena automaatselt XML-elemendi nimesid. Neid saate muuta vastavalt oma soovile. Algseid XML-elemendi nimesid kasutatakse siiski alati, kui te ekspordite andmeid vastendatud lahtritest.
XML-tabelitega töötamisel on tööpaanil XML-allikas nupu Suvandid all kaks järgmist kasulikku valikut.
- Ühenda elemendid vastendamisel automaatselt Kui see on valitud, loob Excel ühe XML-tabeli mitmelt väljalt töölehele kukutamisel. See suvand töötab seni, kuni mitu välja kukutatakse samale reale üksteisega külgnevalt. Kui see suvand ei ole valitud, kuvatakse iga element omaette XML-tabelina.
- Minu andmetel on pealkirjad Kui see on valitud, kasutatakse olemasolevaid päiseandmeid veerupäistena korduvate elementide jaoks, mida vastendate oma töölehega. Kui see suvand ei ole valitud, kasutatakse veerupäistena XML-elemendi nimesid.
XML-tabelite abil saate lihtsalt importida, eksportida, sortida, filtreerida ja printida XML-andmeallikal põhinevaid andmeid. XML-tabelitel on siiski ka mõned piirangud selles osas, kuidas neid töölehel korraldada saab.
- XML-tabelid on reapõhised, mis tähendab, et need kasvavad päisereast allapoole. Uusi kirjeid ei saa lihtsalt olemasolevate ridade peale lisada.
- XML-tabelit ei saa transponeerida nii, et uued kirjed lisataks paremale.
Valemeid saab kasutada veergudes, mis on vastendatud XML-elementidele XML-skeemi määratluse (XSD) andmetüübiga, mida Excel tõlgendab arvu, kuupäeva või kellaajana. Just nagu Exceli tabelis täidetakse XML-tabelis valemid uute ridade lisamisel mööda veergu allapoole.
XML-vastenduse turvakaalutlused
XML-vastendus ja selle andmeallikat käsitlev teave on salvestatud koos Exceli töövihikuga, mitte konkreetse töölehega. Pahatahtlik kasutaja saab tundlikku vastendusteavet vaadata programmi Microsoft Visual Basic for Applications (VBA) makro abil. Lisaks, kui salvestate oma töövihiku makrotoega Exceli Office Open XML-i vormingus, saab vastendusteavet vaadata Microsoft Notepadi või muu tekstiredaktoriga.
Kui soovite vastendusteabe kasutamist jätkata, kuid eemaldada tundliku andmeallikateabe, saate töövihikust XML-skeemi andmeallika määratluse kustutada, kuid siiski eksportida XML-andmeid, tühjendades märkeruudu Salvesta andmeallika määratlus töövihikus dialoogiboksis XML-vastenduse atribuudid, mis on saadaval menüü Arendaja jaotise XML nupu Vastenduse atribuudid abil.
Kui kustutate töölehe enne vastenduse kustutamist, siis vastendusteave andmeallikate kohta ja ka muu tundlik teave jääb ikka töövihikusse. Kui värskendate töövihikut tundliku teabe eemaldamiseks, vaadake kindlasti, et kustutate XML-vastenduse enne töölehe kustutamist, et vastendusteave oleks jäädavalt töövihikust eemaldatud.
Tuletatud skeemiga töötamine
Kui te impordite XML-andmeid, ilma et oleksite kõigepealt lisanud vastava XML-skeemi XML-vastenduse loomiseks, üritab Excel tuletada skeemi, tuginedes siltidele, mis on määratletud XML-andmefailis. Tuletatud skeem säilitatakse töövihikus ja selle abil saate töötada XML-andmetega, kui XML-skeem ei ole töövihikuga seotud.
Kui töötate imporditud XML-andmetega, millel on tuletatud skeem, saate tööpaani XML-allikas kohandada. Kui importisite XML-vastendusega seotud XML-andmeid praegusel Exceli seansil, valige nupust Suvandid suvand Andmete eelvaade tööpaanil, et kuvada esimene andmerida elemendiloendis näidisandmetena.
Exceli tuletatud skeemi ei saa eraldi XML-skeemi andmefailina (.xsd) eksportida. Kuigi XML-skeemifaili loomiseks on XML-skeemi redaktorid ja muud meetodid, ei pruugi teil olla neile mugavat juurdepääsu või te ei pruugi teada, kuidas neid kasutada.
XML-andmete eksportimine
XML-andmete eksportimisel ekspordite vastendatud lahtrite sisu töölehel. Kui ekspordite andmeid, rakendab Excel järgmised reegleid, et määrata kindlaks, millised andmed salvestada ja kuidas.
- Tühje üksusi ei looda, kui tühjad lahtrid on fakultatiivsete elementide jaoks, aga luuakse juhul, kui tühjad lahtrid on vajaliku elemendi jaoks.
- Andmete kirjutamiseks kasutatakse kodeeringut Unicode Transformation Format-8 (UTF-8).
- Kõik nimeruumid määratletakse XML-juurelemendis.
- Excel kirjutab olemasolevad nimeruumi eesliited üle. Vaikenimeruumile omistatakse eesliide ns0. Järjestikustele nimeruumidele määratakse ns1, ns2 kuni ns<arv> , kus <arv> on XML-faili kirjutatud nimeruumide arv.
- Kommentaarisõlmi ei säilitata.
Saate kuvada dialoogiboksi XML-vastenduse atribuudid (klõpsake menüü Arendaja jaotises XML nuppu Vastenduse atribuudid) ja seejärel kasutada suvandit Valideeri andmed impordi ja ekspordi puhul skeemi suhtes (vaikimisi aktiivne), et määrata, kas Excel valideerib andmed xml-vastenduse suhtes andmete eksportimisel. Klõpsake seda suvandit, kui soovite tagada, et eksporditavad XML-andmed vastavad XML-skeemile.
Exceli makrotoega Office Open XML-i vormingu kasutamine
Exceli töövihiku saab salvestada paljudes failivormingutes, kaasa arvatud Exceli makrotoega Office Open XML-i vormingus (.xlsm). Excelil on määratletud XML-skeem, mis määratleb Exceli töövihiku sisu, sealhulgas XML-sildid, mis säilitavad töövihiku üldise struktuuri ja kogu teabe töövihikus (näiteks andmed ja atribuudid). Seda Exceli makrotoega Office XML-i vormingut saavad kasutada kohandatud rakendused. Näiteks võivad arendajad soovida luua kohandatud rakenduse andmete otsimiseks mitmest selles vormingus salvestatud töövihikust ja süsteemi aruannete koostamiseks, mis põhineb leitud andmetel.
Kas vajate rohkem abi?
Võite alati küsida Exceli tehnikakogukonna eksperdilt või kogukonnafoorumites tuge.