Solved

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

Posted on 2015-02-18
20
305 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 62

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
 
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 62

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 62

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 62

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 62

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 62

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 62

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 62

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to go to Web page 2 52
Expando 4 36
C# Error - Add Failed 12 53
Copy/Clone an object. 9 18
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now