Solved

VS2013: Windows Form: Load Excel xml into form with textboxes.

Posted on 2015-02-18
20
385 Views
Last Modified: 2015-02-27
Hi,
I am using VS2013 and I have an Excel xml file and I need to import it to a Windows Form I am developing.
This windows forms (vb.net) contains 1 textbox per each xml data.

How can I do it?

Attached it eh excel I need to imporrt
Computadores-de-Mesa.xml
0
Comment
Question by:José Perez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
20 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40617496
Hi Oscar;

The document you posted has 9 columns and about 36 rows, so you have 324 text boxes to fill? Or do you need it in some other format like a DataGridView.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40617637
oops you're right, thanks for asking :)
I would like to use a recordSet to go next, previous, begin and  last row.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40617668
This means 9 textboxes.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 2

Author Comment

by:José Perez
ID: 40617941
This is the Excel XML file:
<?xml version="1.0" ?>
<?mso-application progid="Excel.Sheet" ?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
	<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Author>xDMS Web Store</Author>
		<LastAuthor>xDMS Web Store</LastAuthor>
        <Created>2015-02-18T10:01:47Z</Created>
		<Version>12.00</Version>
    </DocumentProperties>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
		<WindowHeight>12585</WindowHeight>
		<WindowWidth>28515</WindowWidth>
		<WindowTopX>120</WindowTopX>
		<WindowTopY>90</WindowTopY>
		<ProtectStructure>False</ProtectStructure>
		<ProtectWindows>False</ProtectWindows>
	</ExcelWorkbook>
    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
			<Alignment ss:Vertical="Bottom"/>
			<Borders/>
			<Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000"/>
			<Interior/>
			<NumberFormat/>
			<Protection/>
		</Style>
		<Style ss:ID="BigHeader">
			<Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="14" ss:Color="#0000FF" ss:Bold="1"/>
		</Style>
		<Style ss:ID="Header">
			<Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="10" ss:Color="#0000FF" ss:Bold="1"/>
		</Style>
		<Style ss:ID="ShortDate">
			<NumberFormat ss:Format="Short Date"/>
		</Style>
		<Style ss:ID="LongDate">
			<NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>
		</Style>
        <Style ss:ID="Money">
            <NumberFormat ss:Format="#,##0.00"/>
        </Style>
    </Styles>
    <Worksheet ss:Name="Productos">
        <Table>

            <Row ss:StyleID="Header">
                <Cell><Data ss:Type="String">Tasa de cambio: $1 USD = $ 625</Data></Cell>
                <Cell><Data ss:Type="String">Tasa de cambio aplica para la fecha feb. 18, 2015</Data></Cell>
            </Row>

            <Row ss:StyleID="BigHeader">
                <Cell><Data ss:Type="String">Lista de Precios</Data></Cell>
            </Row>

           
            <Row ss:StyleID="Header">
                
                <Cell><Data ss:Type="String">Categoría</Data></Cell>
                <Cell><Data ss:Type="String">Subcategoría</Data></Cell>
                
                <Cell><Data ss:Type="String">Nombre</Data></Cell>
                <Cell><Data ss:Type="String">Marca</Data></Cell>
                <Cell><Data ss:Type="String">Precio</Data></Cell>
                <Cell><Data ss:Type="String">Disponibilidad</Data></Cell>
                <Cell><Data ss:Type="String">No. de Parte</Data></Cell>
                <Cell><Data ss:Type="String">SKU</Data></Cell>
                <Cell><Data ss:Type="String">Atributos</Data></Cell>
            </Row>
            
            <Row>
                
                <Cell><Data ss:Type="String">Videojuegos</Data></Cell>
                <Cell><Data ss:Type="String">Consolas</Data></Cell>
                
                <Cell><Data ss:Type="String">SONY PSVITA ESTUCHE PARA TARJETAS DE MEMORIAS</Data></Cell>
                <Cell><Data ss:Type="String">Sony</Data></Cell>
                
                <Cell><Data ss:Type="Number">0.90</Data></Cell>
                
                <Cell><Data ss:Type="String">18 en inventario</Data></Cell>
                <Cell><Data ss:Type="String">G1022089</Data></Cell>
                <Cell><Data ss:Type="String">MM950SON03</Data></Cell>
                <Cell><Data ss:Type="String"></Data></Cell>
            </Row>
            
            <Row>
                
                <Cell><Data ss:Type="String">Videojuegos</Data></Cell>
                <Cell><Data ss:Type="String">Consolas</Data></Cell>
                
                <Cell><Data ss:Type="String">SONY PSVITA + GOD OF WAR + MEMORIA 8 GB.</Data></Cell>
                <Cell><Data ss:Type="String">Sony</Data></Cell>
                
                <Cell><Data ss:Type="Number">219.00</Data></Cell>
                
                <Cell><Data ss:Type="String">Más de 20 en inventario</Data></Cell>
                <Cell><Data ss:Type="String">G3000354</Data></Cell>
                <Cell><Data ss:Type="String">MM220SON75</Data></Cell>
                <Cell><Data ss:Type="String"></Data></Cell>
            </Row>
            
            <Row>
                
                <Cell><Data ss:Type="String">Videojuegos</Data></Cell>
                <Cell><Data ss:Type="String">Consolas</Data></Cell>
                
                <Cell><Data ss:Type="String">SONY ESTADO PLAY CONSOLA PS3 500GB+2 CTR+GOF OF WAR3+UNCHART</Data></Cell>
                <Cell><Data ss:Type="String">Sony</Data></Cell>
                
                <Cell><Data ss:Type="Number">321.00</Data></Cell>
                
                <Cell><Data ss:Type="String">Más de 20 en inventario</Data></Cell>
                <Cell><Data ss:Type="String">CECH 4011C  kit2</Data></Cell>
                <Cell><Data ss:Type="String">MM220SON45</Data></Cell>
                <Cell><Data ss:Type="String"></Data></Cell>
            </Row>
            
            <Row>
                
                <Cell><Data ss:Type="String">Videojuegos</Data></Cell>
                <Cell><Data ss:Type="String">Consolas</Data></Cell>
                
                <Cell><Data ss:Type="String">SONY CONSOLA PS3 500GB + 2 CONTROL + FIFA 2015</Data></Cell>
                <Cell><Data ss:Type="String">Sony</Data></Cell>
                
                <Cell><Data ss:Type="Number">333.30</Data></Cell>
                
                <Cell><Data ss:Type="String">6 en inventario</Data></Cell>
                <Cell><Data ss:Type="String">27227</Data></Cell>
                <Cell><Data ss:Type="String">MM220SON60</Data></Cell>
                <Cell><Data ss:Type="String"></Data></Cell>
            </Row>
            
            <Row>
                
                <Cell><Data ss:Type="String">Videojuegos</Data></Cell>
                <Cell><Data ss:Type="String">Consolas</Data></Cell>
                
                <Cell><Data ss:Type="String">SONY CONSOLA PS4 500GB NEGRO + 1 CONTROL + 3 JUEGOS VIRT</Data></Cell>
                <Cell><Data ss:Type="String">Sony</Data></Cell>
                
                <Cell><Data ss:Type="Number">446.40</Data></Cell>
                
                <Cell><Data ss:Type="String">Más de 20 en inventario</Data></Cell>
                <Cell><Data ss:Type="String">27452247</Data></Cell>
                <Cell><Data ss:Type="String">MM220SON64</Data></Cell>
                <Cell><Data ss:Type="String"></Data></Cell>
            </Row>
            
            <Row>
                
                <Cell><Data ss:Type="String">Videojuegos</Data></Cell>
                <Cell><Data ss:Type="String">Consolas</Data></Cell>
                
                <Cell><Data ss:Type="String">SONY CONSOLA PS4 BLANCA 500 GB + JUEGO DESTINY + CONTROL</Data></Cell>
                <Cell><Data ss:Type="String">Sony</Data></Cell>
                
                <Cell><Data ss:Type="Number">464.00</Data></Cell>
                
                <Cell><Data ss:Type="String">1 en inventario</Data></Cell>
                <Cell><Data ss:Type="String">27129</Data></Cell>
                <Cell><Data ss:Type="String">MM220SON49</Data></Cell>
                <Cell><Data ss:Type="String"></Data></Cell>
            </Row>
            
        </Table>
    </Worksheet>
</Workbook>

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40618226
Hi Oscar;

I see that you have changed the XML document to be used. The sample code below will work with the new document format. For this code to work you need the following controls placed on the form, one BindingNavigator named BindingNavigator1, seven TextBox's named as follows txtCategoría, txtSubcategoría, txtNombre, txtMarca, txtPrecio, txtDisponibilidad, txtNodeParte, txtSKU, and txtAtributos. You can change these names as long as you change the name in the code as well.

Public Class Form1
    '' Holds the data from the XML document after parsing
    Dim results As New List(Of ExcelRowData)()
    '' Holds a copy of the data results setup for the BindingNavigator
    Dim bs As New BindingSource()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        '' Load the XML document to memory
        Dim xdoc As XDocument = XDocument.Load("Path and Filename to the XML File")
        '' Gets the XML Namespace for the data we will be working with
        Dim ss As XNamespace = xdoc.Root.GetNamespaceOfPrefix("ss")
        
        '' Parse the XML document and place each row in a class called ExcelRowData
        results = (From n In xdoc.Root.Descendants(ss + "Row") _
              Where n.Elements(ss + "Cell").Count() = 9 AndAlso n.Attributes().Count() = 0 _
              Select New ExcelRowData With _
              { _
                    .Categoría = n.Elements()(0).Value, _
                    .Subcategoría = n.Elements()(1).Value, _
                    .Nombre = n.Elements()(2).Value, _
                    .Marca = n.Elements()(3).Value, _
                    .Precio = n.Elements()(4).Value, _
                    .Disponibilidad = n.Elements()(5).Value, _
                    .No_de_Parte = n.Elements()(6).Value, _
                    .SKU = n.Elements()(7).Value, _
                    .Atributos = n.Elements()(8).Value _
              }).ToList()

        '' Assign the parsed XML data into the BindingSource
        bs.DataSource = results
        '' Connect the BindingSource to the BindingNavigator
        BindingNavigator1.BindingSource = bs

        '' Bind all the TextBox controls to the field they are to display
        txtCategoría.DataBindings.Add(New Binding("Text", bs, "Categoría"))
        txtSubcategoría.DataBindings.Add(New Binding("Text", bs, "Subcategoría"))
        txtNombre.DataBindings.Add(New Binding("Text", bs, "Nombre"))
        txtMarca.DataBindings.Add(New Binding("Text", bs, "Marca"))
        txtPrecio.DataBindings.Add(New Binding("Text", bs, "Precio"))
        txtDisponibilidad.DataBindings.Add(New Binding("Text", bs, "Disponibilidad"))
        txtNodeParte.DataBindings.Add(New Binding("Text", bs, "No_de_Parte"))
        txtSKU.DataBindings.Add(New Binding("Text", bs, "SKU"))
        txtAtributos.DataBindings.Add(New Binding("Text", bs, "Atributos"))

    End Sub

End Class

'' Custom class to hold one row of data from the XML document
Public Class ExcelRowData
    Public Property Categoría As String
    Public Property Subcategoría As String
    Public Property Nombre As String
    Public Property Marca As String
    Public Property Precio As String
    Public Property Disponibilidad As String
    Public Property No_de_Parte As String
    Public Property SKU As String
    Public Property Atributos As String
End Class

Open in new window

0
 
LVL 2

Author Comment

by:José Perez
ID: 40618567
Almost... it displys an error on "Dim xdoc As XDocument = XDocument.Load(txtRuta_eXML.Text)"

{"Declaración XML inesperada. La declaración XML debe ser el primer nodo del documento y no pueden aparecer espacios en blanco delante. línea 2, posición 3."}

Open in new window

0
 
LVL 2

Author Comment

by:José Perez
ID: 40618584
I put the xml path in "txtRuta_eXML.Text" textbox.

p.s. it does not work with straight path also: "C:\Users\Oscar\Downloads\Computadores de Mesa.xml"
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40618638
I used Google translator to translate the error message you posted, "Unexpected XML declaration . The XML declaration must be the first node of the document and may not appear in front blanks. Line". This error states that the XML document you are using has an error in ithe declaration statement. The declaration statement being the first statement of the file can NOT have any other lines before it. It also can not have any other characters before the first character of the declaration which is <.

By the way the code I posted works and was used to test the query in the code. If you still have issues please post the code you are using and attach the file you used to the post.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40618822
Attached.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40619085
Where, your XML document in this post I used and it worked. Please show your code as you implemented it and attach the XML file you used and which did not work for you by using the Attach File hot spot at the bottom of the Post a Comment window left side.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40619275
It does not allow me to upload the source files. I'll have to write it here...
Public Class Form1
    '' Holds the data from the XML document after parsing
    Dim results As New List(Of ExcelRowData)()
    '' Holds a copy of the data results setup for the BindingNavigator
    Dim bs As New BindingSource()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load


    End Sub

    Private Sub txtDlgBox_Click(sender As Object, e As EventArgs) Handles txtDlgBox.Click
        OpenFileDialog1.Filter = "Archivo Xml (*.xml)|*.xml"
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            txtRuta_eXML.Text = OpenFileDialog1.FileName
        End If
    End Sub

    Private Sub btnCargaXML_Click(sender As Object, e As EventArgs) Handles btnCargaXML.Click
        '' Load the XML document to memory
        'Dim xdoc As XDocument = XDocument.Load("Path and Filename to the XML File")
        'Dim xdoc As XDocument = XDocument.Load(txtRuta_eXML.Text)
        Dim xdoc As XDocument = XDocument.Load("C:\Users\Oscar\Downloads\Computadores de Mesa.xml")
        '' Gets the XML Namespace for the data we will be working with
        Dim ss As XNamespace = xdoc.Root.GetNamespaceOfPrefix("ss")

        '' Parse the XML document and place each row in a class called ExcelRowData
        results = (From n In xdoc.Root.Descendants(ss + "Row") _
              Where n.Elements(ss + "Cell").Count() = 9 AndAlso n.Attributes().Count() = 0 _
              Select New ExcelRowData With _
              { _
                    .Categoría = n.Elements()(0).Value, _
                    .Subcategoría = n.Elements()(1).Value, _
                    .Nombre = n.Elements()(2).Value, _
                    .Marca = n.Elements()(3).Value, _
                    .Precio = n.Elements()(4).Value, _
                    .Disponibilidad = n.Elements()(5).Value, _
                    .No_de_Parte = n.Elements()(6).Value, _
                    .SKU = n.Elements()(7).Value, _
                    .Atributos = n.Elements()(8).Value _
              }).ToList()

        '' Assign the parsed XML data into the BindingSource
        bs.DataSource = results
        '' Connect the BindingSource to the BindingNavigator
        BindingNavigator1.BindingSource = bs

        '' Bind all the TextBox controls to the field they are to display
        txtCategoria.DataBindings.Add(New Binding("Text", bs, "Categoría"))
        txtSubcategoria.DataBindings.Add(New Binding("Text", bs, "Subcategoría"))
        txtNombre.DataBindings.Add(New Binding("Text", bs, "Nombre"))
        txtMarca.DataBindings.Add(New Binding("Text", bs, "Marca"))
        txtPrecioCompra.DataBindings.Add(New Binding("Text", bs, "Precio"))
        txtDisponibilidad.DataBindings.Add(New Binding("Text", bs, "Disponibilidad"))
        txtNodeParte.DataBindings.Add(New Binding("Text", bs, "No_de_Parte"))
        txtSKU.DataBindings.Add(New Binding("Text", bs, "SKU"))
        txtAtributos.DataBindings.Add(New Binding("Text", bs, "Atributos"))
    End Sub
End Class

'' Custom class to hold one row of data from the XML document
Public Class ExcelRowData
    Public Property Categoría As String
    Public Property Subcategoría As String
    Public Property Nombre As String
    Public Property Marca As String
    Public Property Precio As String
    Public Property Disponibilidad As String
    Public Property No_de_Parte As String
    Public Property SKU As String
    Public Property Atributos As String
End Class

Open in new window

Computadores-de-Mesa.xml
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40619671
In one of my previous post I stated the following.

"Unexpected XML declaration . The XML declaration must be the first node of the document and may not appear in front blanks. Line". This error states that the XML document you are using has an error in the declaration statement. The declaration statement being the first statement of the file can NOT have any other lines before it. It also can not have any other characters before the first character of the declaration which is <.

The very first line in the XML document is a BLANK LINE. This makes the document invalid. If you were to remove the line so that statement in line 2 of that document becomes line 1 and making sure there is NO space character before the opening < character then it should work as long as you followed the instruction of setting up the form.

Malformed XML document
0
 
LVL 2

Author Comment

by:José Perez
ID: 40619701
oh it worked like a charm! by the way... if I wanted to capture the "DocumentProperties" tag what should I do, for example to capture "Created"?

	<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Author>Intcomex Web Store</Author>
		<LastAuthor>Intcomex Web Store</LastAuthor>
        <Created>2015-02-19T06:15:35Z</Created>
		<Version>12.00</Version>
    </DocumentProperties>

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40619912
This should get you what you need. Place this code in the same block of code as before so it has access to the Document.

'' XML Namespace to access the DocumentProperties node and its children
Dim o As XNamespace = xdoc.Root.GetNamespaceOfPrefix("o")

'' Query to get the DocumentProperties info.
Dim docProperties = (From dp In xdoc.Root.Descendants(o + "DocumentProperties") _
                    Select New With _
                        { _
                            .Author = dp.Element(o + "Author").Value, _
                            .LastAuthor = dp.Element(o + "LastAuthor").Value, _
                            .Created = dp.Element(o + "Created").Value, _
                            .Version = dp.Element(o + "Version").Value _
                        }).SingleOrDefault()

'' Accessing the results
Console.WriteLine("{0}  :  {1}  :  {2}  :  {3}", _
                  docProperties.Author, docProperties.LastAuthor, docProperties.Created, docProperties.Version)

Open in new window

0
 
LVL 2

Author Comment

by:José Perez
ID: 40620294
wow you're a blessing! can I ask one last question? If you decide not to answer, it is ok, no worries, points goes to you anyway :)

Before starting with the txt attributes, it displays the "Tasa de Cambio" exchange rate) and "Fecha" (date), is it possible to get those values (bold ones)?

   <Worksheet ss:Name="Productos">
        <Table>

            <Row ss:StyleID="Header">
                <Cell><Data ss:Type="String">Tasa de cambio: $1 USD = $ [b]625[/b]</Data></Cell>
                <Cell><Data ss:Type="String">Tasa de cambio aplica para la fecha [b]feb. 18, 2015[/b]</Data></Cell>
            </Row>

            <Row ss:StyleID="BigHeader">
                <Cell><Data ss:Type="String">Lista de Precios</Data></Cell>
            </Row>

           
            <Row ss:StyleID="Header">
                
                <Cell><Data ss:Type="String">Categoría</Data></Cell>
                <Cell><Data ss:Type="String">Subcategoría</Data></Cell>
                
                <Cell><Data ss:Type="String">Nombre</Data></Cell>
                <Cell><Data ss:Type="String">Marca</Data></Cell>
                <Cell><Data ss:Type="String">Precio</Data></Cell>
                <Cell><Data ss:Type="String">Disponibilidad</Data></Cell>
                <Cell><Data ss:Type="String">No. de Parte</Data></Cell>
                <Cell><Data ss:Type="String">SKU</Data></Cell>
                <Cell><Data ss:Type="String">Atributos</Data></Cell>
            </Row>

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40620369
Try this code snippet .

'' The variable exchangeInfo will contain a List(Of String)
'' which has the Text values of the two Data nodes.
'' All you will need to do is parse the string for the data you want
Dim exchangeInfo = (From ei In xdoc.Descendants(ss + "Row") _
                    Where ei.HasAttributes() AndAlso ei.Attribute(ss + "StyleID").Value = "Header" AndAlso _
                          ei.Descendants(ss + "Data").Count() = 2
                    From data In ei.Descendants(ss + "Data") _
                    Select data.Value).ToList()

For Each data As String In exchangeInfo
    Console.WriteLine(data)
Next

Open in new window

0
 
LVL 2

Author Comment

by:José Perez
ID: 40620847
Perfect! Really helpfull!
God bless you!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40621234
Not a problem Oscar, glad to help.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40623055
Thanks.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40636764
Hi friend. I am posting a new question related to this casa, can you please see it?

http://www.experts-exchange.com/Programming/Languages/.NET/Q_28626443.html

Thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question