Solved

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

Posted on 2015-02-18
20
355 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

790 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