asked on

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

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

Fernando Soto

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.

José Perez

ASKER

oops you're right, thanks for asking :)
I would like to use a recordSet to go next, previous, begin and last row.

José Perez

ASKER

This means 9 textboxes.

José Perez

ASKER

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

Fernando Soto

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

José Perez

ASKER

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

José Perez

ASKER

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"

Fernando Soto

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.

José Perez

ASKER

Attached.

Fernando Soto

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.

José Perez

ASKER

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

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

José Perez

ASKER

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

Fernando Soto

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

José Perez

ASKER

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

Fernando Soto

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

José Perez

ASKER

Perfect! Really helpfull!
God bless you!

Fernando Soto

Not a problem Oscar, glad to help.

José Perez

ASKER

Thanks.

José Perez

ASKER

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.