How to convert XML to Excel file OR Xbrowser load it?

[richard-service]

Dear Antonio,

Harbour have any function XML convert to Excel file?
I try XBrowser load XML file not work. No show any data that just only empty.

Code: Select all | Expand

XBROWSER  MEMOREAD("WANTMON.XML")

[cnavarro]

Dear Richard, try with this

"test.xml" is in the samples folder of the fivewin installation

Code: Select all | Expand

//--------------------------------------------------------------------------------------//
//
//--------------------------------------------------------------------------------------//

#include "FiveWin.ch"

Static aXml

function Main()

  aXml   := {}

  XmlTree( "test.xml" )
  XBrowse( aXml )

return nil

//--------------------------------------------------------------------------------------//
//
//--------------------------------------------------------------------------------------//

static function XmlTree( cXml )

   local oXmlDoc
   local oRoot
   local cPrompt
   local cData
   local oTree
  
   TRY
      oXmlDoc  := TXmlDocument():New( cXml )
   CATCH
      ? "TXmlDocument class can not read " + cXml
      return nil
   END
   oRoot    := oXmlDoc:oRoot
   cPrompt  := If( Empty( oRoot:cName ), cFileNoPath( cXml ), oRoot:cName )
   cData    := if( Empty( oRoot:cData ), "Empty Value", oRoot:cData )
   AAdd( aXml, { cPrompt, cData, oRoot:oChild, oRoot:oNext, oRoot:oPrev, oRoot:oParent, oRoot:Path() } )
   if !Hb_IsNil( oRoot:oChild )
      SubTree( oRoot:oChild )
   endif

return nil

//--------------------------------------------------------------------------------------//
//
//--------------------------------------------------------------------------------------//

static function SubTree( oNode )

   local cPrompt
   local cData
   
   do while oNode != nil
      cPrompt  := If( Empty( oNode:cName ), "", oNode:cName )
      cData    := if( Empty( oNode:cData ), "Empty Value", oNode:cData )
      AAdd( aXml, { cPrompt, cData, oNode:oChild, oNode:oNext, oNode:oPrev, oNode:oParent, oNode:Path() } )
      if !Hb_IsNil( oNode:oChild )
         SubTree( oNode:oChild )
      endif
      oNode    := oNode:oNext
   enddo

return nil

//--------------------------------------------------------------------------------------//
//
//--------------------------------------------------------------------------------------//

or use TXmltoHash

[Marc Venken]

Maybe this can help.

It will open a xml file, reads it, show it and save it to dbf.
In the loop you could also use exel class to build.
This way you have full controle off data. I suppose the code explain inself or ask what you need...

XmlView will show the file. Take the Tags you need to create data.
If you want i can send the 2 files (or upload)

The second function : makedroponfILE(oBrw)

Will make a exel from a dbf file. If you combine these 2 function you should get it working.

Regards

Code: Select all | Expand

function Xmlcolorreader()
   local aMail:={},nTel:= 1, i
   local hFile    := FOpen( "c:\programmas\fotoselect\kleuren.xml"  )
   Local aStruct := {}
   Local oXmlDoc  := TXmlDocument():New( hFile )
   Local oXmlIter := TXmlIterator():New( oXmlDoc:oRoot ), oTagActual
   Local cKleur, cHex, cId

   FW_XmlView( hFile )

   nTel = 1
   while .T.
      oTagActual = oXmlIter:Next()
      If oTagActual != nil
         //if oTagActual:cName = "item"
         //   aadd(aMail,oTagActual:cData)
         //endif

         if oTagActual:cName = "id"
            cId = oTagActual:cData
         endif

         if oTagActual:cName = "colorHex"
           cHex = oTagActual:cData
           do while .t.
             oTagActual = oXmlIter:Next()
             If oTagActual != nil
               if oTagActual:cName = "dutch"
                  cdd = oTagActual:cData //+ " : "+cHex
//                  aadd(aMail ,{cdd,cHex} )
//                  aadd(aMail ,{if(cdd==NIL,"",cdd),if(cHex==NIL,"",cHex)} )
                  aadd(aMail ,{if(cdd==NIL,"",cdd),if(cHex==NIL,"",cHex),if(cId==NIL,"",cId)} )
                  nTel++
                  exit
               endif
             endif
           enddo
         endif
      Else
         Exit
      Endif
   End
   netopen("shopcolor",NIL,NIL,.F.)
   select shopcolor
   zap
   //shopcolor->(dbzap())

   /*
   AADD(aStruct, { "code"   , "C",  6, 0 }) // Id
   AADD(aStruct, { "kleuren", "C", 70, 0 }) // Id
   AADD(aStruct, { "hexcode", "C", 10, 0 }) // Id

   DbCreate( "shopcolor.dbf", aStruct)
   xbrowser("shopcolor")
   netopen("shopcolor")
   */
   xbrowser(aMail)


    */


  for i = 1 to len(aMail)
     ckleur = amail[i,1]
     cHex = aMail[i,2]
     shopcolor->(dbappend())
     shopcolor->code    := aMail[i,3]
     shopcolor->kleuren := cKleur
     shopcolor->hexcode := Chex

  next

   xbrowser("shopcolor")

   FClose( hFile )
   close all

return nil

Code: Select all | Expand

function makedroponfILE(oBrw)

oExcel := CreateObject( "Excel.Application" )
oExcel:WorkBooks:Add()


oAs := oExcel:Activesheet()

oAs:Cells:Font:Name := "Calibri"
oAs:Cells:Font:Size := 10

oAs:Columns(  1 ):ColumnWidth := 10
oAs:Columns(  2 ):ColumnWidth := 60
oAs:Columns(  3 ):ColumnWidth := 60
oAs:Columns(  4 ):ColumnWidth := 5
oAs:Columns(  5 ):ColumnWidth := 8
oAs:Columns(  6 ):ColumnWidth := 30
oAs:Columns(  7 ):ColumnWidth := 40
oAs:Columns(  8 ):ColumnWidth := 20
oAs:Columns(  9 ):ColumnWidth := 10
oAs:Columns( 10 ):ColumnWidth := 10
oAs:Columns( 11 ):ColumnWidth := 15
oAs:Columns( 12 ):ColumnWidth := 50
oAs:Columns( 13 ):ColumnWidth := 8
oAs:Columns( 14 ):ColumnWidth := 10
oAs:Columns( 15 ):ColumnWidth := 10

oAs:Cells( 1,  1 ):Value := "Handle"
oAs:Cells( 1,  2 ):Value := "Recipient"
oAs:Cells( 1,  3 ):Value := "Street and number"
oAs:Cells( 1,  4 ):Value := "Bus"
oAs:Cells( 1,  5 ):Value := "Postal code"
oAs:Cells( 1,  6 ):Value := "City"
oAs:Cells( 1,  7 ):Value := "Email"
oAs:Cells( 1,  8 ):Value := "Phone"
oAs:Cells( 1,  9 ):Value := "Product"
oAs:Cells( 1, 10 ):Value := "Quantity"
oAs:Cells( 1, 11 ):Value := "Capacity (kg)"
oAs:Cells( 1, 12 ):Value := "Comment"
oAs:Cells( 1, 13 ):Value := "Language"
oAs:Cells( 1, 14 ):Value := "Planning date"
oAs:Cells( 1, 15 ):Value := "Reference"

factinfo->(dbclearfilter())
factinfo->(dbgotop())
n = 2
do while !factinfo->(eof())
    if empty(factinfo->tr_handle) .or. alltrim(factinfo->tr_handle) = "SEND"
       factinfo->(dbskip())
       loop
    endif
    klant->(dbseek(factinfo->klant))
    sysrefresh()
    oAs:Cells( n, 1 ):Value := factinfo->TR_handle     // Type (Pickup,Drop,Retour)
    oAs:Cells( n, 2 ):Value := factinfo->TR_Name    // Naam
    oAs:Cells( n, 3 ):Value := factinfo->TR_straat       // Straat
    oAs:Cells( n, 4 ):Value := "" // Bus Nr.
    oAs:Cells( n, 5 ):Value := factinfo->TR_post
    oAs:Cells( n, 6 ):Value := factinfo->TR_plaats
    oAs:Cells( n, 7 ):Value := factinfo->TR_email
    oAs:Cells( n, 8 ):Value := factinfo->TR_tel
    oAs:Cells( n, 9 ):Value := "box"
    oAs:Cells( n,10 ):Value := factinfo->tr_aantal
    oAs:Cells( n,11 ):Value := "25"
    oAs:Cells( n,12 ):Value := factinfo->tr_comment
    oAs:Cells( n,13 ):Value := "NL"
    oAs:Cells( n,14 ):Value := dtoc(factinfo->tr_datum)
    oAs:Cells( n,15 ):Value := factinfo->document
    n++
    if factinfo->(rLock())
       factinfo->TR_handle ="SEND"
    endif
    factinfo->(dbskip())
enddo

cFile:="\\carolien-pc\marc\Dropon\Dropon_"+str(year(date()),4)+str(month(date()),2)+str(day(date()),2)
Cfile = StrTran( cFile, " ", "0" )
oAs:SaveAs( cFile )
oExcel:visible := .T.
msginfo("Exelbestand aangemaakt. op SERVER CAROLIEN-PC\MARC\DROPON\Dropon_xxx.  U kan het online verwerken")
oExcel:quit()
return

[FiveWiDi]

Dear Antonio,

Harbour have any function XML convert to Excel file?
I try XBrowser load XML file not work. No show any data that just only empty.

Code: Select all | Expand

XBROWSER  MEMOREAD("WANTMON.XML")

Try it:

XBROWSER "WANTMON.XML"

Regards,

[richard-service]

Dear All,

Thanks for your wonderful help me.
I will try it again when I come back my office.