Post
Ask Question

Store XML Data to Excel Sheet using VBScript and HTML5

This article shows how to read a XML document and store the XML data to a Microsoft Excel spreadsheet using VBScript and HTML 5.

convert XML to Excel
Figure 1: XML to Excel

Step 1

  1. Start the Excel Application.

  2. Save the empty workbook with the extension “xlsx”.

  3. Close the workbook.

    Excel application
    Figure 2: Excel application

Step 2

  1. Open Notepad.

  2. Add the following code.
    1. <?xml version="1.0"?>  
    2. <catalog>  
    3.     <book id="bk101">  
    4.         <author>Gambardella, Matthew</author>  
    5.         <title>XML Developer's Guide</title>  
    6.         <genre>Computer</genre>  
    7.         <price>44.95</price>  
    8.         <publish_date>2000-10-01</publish_date>  
    9.         <description>An in-depth look at creating applications  
    10. with XML.</description>  
    11.     </book>  
    12.     <book id="bk102">  
    13.         <author>Ralls, Kim</author>  
    14.         <title>Midnight Rain</title>  
    15.         <genre>Fantasy</genre>  
    16.         <price>5.95</price>  
    17.         <publish_date>2000-12-16</publish_date>  
    18.         <description>A former architect battles corporate zombies,   
    19. an evil sorceress, and her own childhood to become queen   
    20. of the world.</description>  
    21.     </book>  
    22.     <book id="bk103">  
    23.         <author>Corets, Eva</author>  
    24.         <title>Maeve Ascendant</title>  
    25.         <genre>Fantasy</genre>  
    26.         <price>5.95</price>  
    27.         <publish_date>2000-11-17</publish_date>  
    28.         <description>After the collapse of a nanotechnology   
    29. society in England, the young survivors lay the   
    30. foundation for a new society.</description>  
    31.     </book>  
    32.     <book id="bk104">  
    33.         <author>Corets, Eva</author>  
    34.         <title>Oberon's Legacy</title>  
    35.         <genre>Fantasy</genre>  
    36.         <price>5.95</price>  
    37.         <publish_date>2001-03-10</publish_date>  
    38.         <description>In post-apocalypse England, the mysterious  
    39. agent known only as Oberon helps to create a new life  
    40. for the inhabitants of London. Sequel to Maeve  
    41. Ascendant.</description>  
    42.     </book>  
    43.     <book id="bk105">  
    44.         <author>Corets, Eva</author>  
    45.         <title>The Sundered Grail</title>  
    46.         <genre>Fantasy</genre>  
    47.         <price>5.95</price>  
    48.         <publish_date>2001-09-10</publish_date>  
    49.         <description>The two daughters of Maeve, half-sisters,  
    50. battle one another for control of England. Sequel to  
    51. Oberon's Legacy.</description>  
    52.     </book>  
    53.     <book id="bk106">  
    54.         <author>Randall, Cynthia</author>  
    55.         <title>Lover Birds</title>  
    56.         <genre>Romance</genre>  
    57.         <price>4.95</price>  
    58.         <publish_date>2000-09-02</publish_date>  
    59.         <description>When Carla meets Paul at an ornithology  
    60. conference, tempers fly as feathers get ruffled.</description>  
    61.     </book>  
    62.     <book id="bk107">  
    63.         <author>Thurman, Paula</author>  
    64.         <title>Splish Splash</title>  
    65.         <genre>Romance</genre>  
    66.         <price>4.95</price>  
    67.         <publish_date>2000-11-02</publish_date>  
    68.         <description>A deep sea diver finds true love twenty  
    69. thousand leagues beneath the sea.</description>  
    70.     </book>  
    71.     <book id="bk108">  
    72.         <author>Knorr, Stefan</author>  
    73.         <title>Creepy Crawlies</title>  
    74.         <genre>Horror</genre>  
    75.         <price>4.95</price>  
    76.         <publish_date>2000-12-06</publish_date>  
    77.         <description>An anthology of horror stories about roaches,  
    78. centipedes, scorpions and other insects.</description>  
    79.     </book>  
    80.     <book id="bk109">  
    81.         <author>Kress, Peter</author>  
    82.         <title>Paradox Lost</title>  
    83.         <genre>Science Fiction</genre>  
    84.         <price>6.95</price>  
    85.         <publish_date>2000-11-02</publish_date>  
    86.         <description>After an inadvertant trip through a Heisenberg  
    87. Uncertainty Device, James Salway discovers the problems  
    88. of being quantum.</description>  
    89.     </book>  
    90.     <book id="bk110">  
    91.         <author>O'Brien, Tim</author>  
    92.         <title>Microsoft .NET: The Programming Bible</title>  
    93.         <genre>Computer</genre>  
    94.         <price>36.95</price>  
    95.         <publish_date>2000-12-09</publish_date>  
    96.         <description>Microsoft's .NET initiative is explored in  
    97. detail in this deep programmer's reference.</description>  
    98.     </book>  
    99.     <book id="bk111">  
    100.         <author>O'Brien, Tim</author>  
    101.         <title>MSXML3: A Comprehensive Guide</title>  
    102.         <genre>Computer</genre>  
    103.         <price>36.95</price>  
    104.         <publish_date>2000-12-01</publish_date>  
    105.         <description>The Microsoft MSXML3 parser is covered in  
    106. detail, with attention to XML DOM interfaces, XSLT processing,  
    107. SAX and more.</description>  
    108.     </book>  
    109.     <book id="bk112">  
    110.         <author>Galos, Mike</author>  
    111.         <title>Visual Studio 7: A Comprehensive Guide</title>  
    112.         <genre>Computer</genre>  
    113.         <price>49.95</price>  
    114.         <publish_date>2001-04-16</publish_date>  
    115.         <description>Microsoft Visual Studio 7 is explored in depth,  
    116. looking at how Visual Basic, Visual C++, C#, and ASP+ are   
    117. integrated into a comprehensive development   
    118. environment.</description>  
    119.     </book>  
    120. </catalog>  
  3. Save the file with the extension “.xml”.

Step 3

  1. Open the new file in Notepad.

  2. Add the following code.
    1. <! DOCTYPE html>  
    2. <html>  
    3. <head>  
    4. <title>Page Title</title>  
    5. <style>  
    6. h1  
    7. {  
    8. color: #2C9EE6;  
    9. }  
    10. fieldset {  
    11.     font-family: sans-serif;  
    12.     border: 5px solid #2C9EE6;  
    13.     background: #ddd;  
    14.     border-radius: 5px;  
    15.     padding: 15px;  
    16. }  
    17.   
    18. fieldset legend {  
    19.     background: #2C9EE6;  
    20.     color: #fff;  
    21.     padding: 5px 10px ;  
    22.     font-size: 32px;  
    23.     border-radius: 5px;  
    24.     box-shadow: 0 0 0 5px #ddd;  
    25.     margin-left: 20px;  
    26. }  
    27. </style>  
    28. <script language="vbscript" type="text/vbscript">  
    29.   
    30.     Sub Handlechange()  
    31.     MsgBox "File Imported Successfully”, vbInformation  
    32. End Sub  
    33. Sub ExportXML  
    34.     Dim ObjXML  
    35.     Dim fileinput  
    36.     Dim PriceNode  
    37.     Dim NodeList  
    38.     Dim Title  
    39.     Dim Price  
    40.     Dim Nodes_Attribute  
    41.     Dim i  
    42.     Dim iRow  
    43.     Count=0  
    44.     Set ObjXML = CreateObject ("Microsoft.XMLDOM")   
    45.     ObjXML.async = False   
    46.     Set objExcel = CreateObject ("Excel. Application")  
    47.     Set objWorkbook =  
    48.     objExcel.Workbooks.Open ("C:\Users\Karthikeyan.K\Desktop\DataBook.xlsx")  
    49.     objExcel.Application.Visible = True  
    50.     objWorkbook.Windows(1).Visible = True  
    51.     Set XlSheet =objWorkbook.Sheets(1)  
    52.     XlSheet.Activate  
    53.     fileinput = Document.getElementById ("browse").Value  
    54.     ObjXML.load(fileinput)  
    55.     Set TitleNodes = ObjXML.SelectNodes("/catalog/book/title/text()")  
    56.     Set PriceNodes = ObjXML.SelectNodes("/catalog/book/price/text()")   
    57.     XlSheet.Range ("A1, B1, C1").Interior.ColorIndex =3  
    58.     XlSheet.Range ("A1, B1, C1").Borders. Value = 1  
    59.     XlSheet.Range ("A" & 1).Value = "Book ID"  
    60.     XlSheet.Range ("B" & 1).Value = "Book Titles"  
    61.     XlSheet.Range ("C" & 1).Value = "Price"  
    62.     For i = 0 To (TitleNodes.Length - 1)  
    63.         Title = TitleNodes(i).NodeValue  
    64.         Price = PriceNodes(i).NodeValue  
    65.         XlSheet.Range("B" & i + 2).Borders.Value = 1  
    66.         XlSheet.Range("C" & i + 2).Borders.Value = 1  
    67.         XlSheet.Range("B" & i + 2).Value = Title  
    68.         XlSheet.Range("C" & i + 2).Value = Price  
    69.     Next  
    70.     Set Nodes_Attribute = ObjXML.SelectNodes ("/catalog/book")  
    71.     For i = 0 To (Nodes_Attribute. Length - 1)  
    72.         Attributes = Nodes_Attribute(i).getAttribute("id")  
    73.         XlSheet.Range ("A" & i + 2).Borders.Value = 1  
    74.         XlSheet.Range ("A" & i + 2).Value = Attributes  
    75.     Next     
    76.     objWorkbook.save  
    77.     objWorkbook.close  
    78.     Set objWorkbook = Nothing  
    79.     Set objExcel = Nothing  
    80.     MsgBox "Data Read Successfully”, vbInformation  
    81. End Sub  
    82. </script>  
    83. </head>  
    84.   
    85. <body>  
    86.     <form>  
    87.         <section style="margin: 10px;">  
    88.             <fieldset style="min-height:100px;">  
    89.                 <legend>  
    90.                     <b>CsharpCorner </b>  
    91.                 </legend>  
    92.                 <center>  
    93.                     <h1>XML To Excel</h1>  
    94.                     <img src="C:\Users\Karthikeyan.K\Desktop\sync-circle-blue-256.png" alt="View" style="width: 256px; height: 256px ;">  
    95.                     <br>  
    96.                     <br>  
    97.                     <input type="file" id="browse" name="fileupload" onChange="Handlechange()" />  
    98.                     <br>  
    99.                     <br>  
    100.                     <input type="button" value="Export" id="Export" onclick="ExportXML ()" />  
    101.                 </center>  
    102.                 <label>  
    103.                     <br/>  
    104.                 </label>  
    105.                 <label>  
    106.                     <br/>  
    107.                 </label>  
    108.             </fieldset>  
    109.     </form>  
    110. </body>  
    111.   
    112. </html>  
  3. Save the file with the extension “htm”.

Output

  1. Open the HTML document.

    XML to Excel
    Figure 3: XML to Excel

  2. Click the Browse button then choose the XML document.

    Choose the XML document
    Figure 4: Choose the XML document

    File Imported successfully
    Figure 5: File Imported successfully

  3. Press the Export button.

    Data read successfully
    Figure 6: Data read successfully

  4. Open the Excel workbook to check the spreadsheet.

    Final result
    Figure 7: Final result

Conclusion

Thanks for reading. I hope you liked this article. Please provide your valuable suggestions.


Similar Articles