CODEWITHSUNDEEP
htmlexcelvb.netvsto
Raghu
Raghu

Reputation: 114

Importing HTML to Excel using VSTO

I have some html files and I need to import the data to excel. I am using VSTO (VB.NET) to achieve the same. Unfortunately, I am getting some error. I am new to parsing html. Can somebody tell me where I am going wrong in the code below.

I tried to read header 2 text from the HTML collection, but its not working. Can some one let me know where am I going wrong?

        Dim doc As HtmlDocument, htmlTable As Object, htmlBody As 
        HtmlElementCollection
        Dim wbrowser As New WebBrowser
        Dim header2 as String
        wbrowser.Visible = False
        wbrowser.Navigate(InputHtmlFile)
        'Do
        '
        'Loop Until Not wbrowser.IsBusy

        doc = wbrowser.Document

        htmlBody = doc.GetElementsByTagName("BODY")
        htmlheadings = doc.GetElementsByTagName("H2")
        header2 = htmlheadings(0).Innertext

Below is the html code

    <HEAD>
    <TITLE> SOME TITLE </TITLE>
    </HEAD>
    <BODY>
    <H2> HEADER 2 TEXT </H2>
    <HR NOSHADE>
    <H3> <U>DATA</U> </H3>
    <BLOCKQUOTE>
    VERSION NUMBER X.X.XXX
    </BLOCKQUOTE>
    <HR SIZE = 5>
    <H3> <U>DATA SET 2</U></H3>
    <BLOCKQUOTE>
    <H4>DATA SET 3 </H4>
    <BLOCKQUOTE>

    <TABLE border=1>
    <TR> <TH> TableHeader1 </TH> <TH>TTableHeader2</TH> 
    <TH>TableHeader3</TH><TH>TableHeader4</TH><TH>TableHeader5</TH> 
    <TH>TableHeader6</TH></TR><TR ALIGN=CENTER><TD>1</TD><TD> 6.4</TD><TD> 
    StringData</TD><TD> No</TD><TD> </TD><TD> </TD> </TR></TABLE><BR>
    </BLOCKQUOTE>

I expect "HEADER 2 TEXT" is assigned to the variable header2 but I get the following error at the line 

    header2 = htmlheadings(0).Innertext

System.ArgumentOutOfRangeException: 'Value of '0' is not valid for 'index'. 'index' should be between 0 and -1. Parameter name: index'

htmlheadings(0).InnerHtml = 'htmlheadings(0).InnerHtml' threw an exception of type 'System.ArgumentOutOfRangeException'

Also the collections htmlBody and htmlheadings are empty.

Upvotes: 0

Views: 84

Answers (1)

Christoph
Christoph

Reputation: 3642

Your HTML code is not proper HTML. There are no <HTML> tags, no ending </BODY> tag, more opening <BLOCKQUOTE> tags than closing ones etc. I removed what was incorrect:

    <?xml version="1.0" encoding="utf-8" ?>
    <html>
      <head>
        <title>SOME TITLE</title>
      </head>
      <body>
        <h2>HEADER 2 TEXT </h2>
        <h3>
          <u>DATA</u>
        </h3>
        <blockquote>VERSION NUMBER X.X.XXX</blockquote>
        <h3><u>DATA SET 2</u></h3>
        <h4>DATA Set 3</h4>
        <table border="1">
          <tr><th>TableHeader1</th><th>TTableHeader2</th><th>TableHeader3</th><th>TableHeader4</th><th>TableHeader5</th><th>TableHeader6</th></tr>
          <tr align="CENTER"><td>1</td><td>6.4</td><td>StringData</td><td>No</td><td></td><td></td></tr>
        </table>
        <br />
      </body>
    </html>

The elements are working fine in the DocumentCompleted event handler of the browser control (double-click it to generate the event handler):

Public Class Form1

    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
        wbrowser.Visible = False
        wbrowser.Navigate("C:\temp\foo.html")
    End Sub

    Private Sub wbrowser_DocumentCompleted(sender As Object, e As Windows.Forms.WebBrowserDocumentCompletedEventArgs) Handles wbrowser.DocumentCompleted
        Dim doc = wbrowser.Document
        Dim htmlBody = doc.GetElementsByTagName("BODY")(0)
        Dim htmlheadings = htmlBody.GetElementsByTagName("H2")
        Dim header2 = htmlheadings(0).InnerText
        MsgBox(header2)
    End Sub

End Class

Upvotes: 1

Related Questions