Results 1 to 9 of 9

Thread: [SOLVED] Export to Excel, Then View Excel File

  1. #1
    Junior Member
    Join Date
    Jun 2012
    Posts
    23

    [SOLVED] Export to Excel, Then View Excel File

    Hi,

    Is there a recommended way in Lianja to export a cursor or table to Excel? I am currently using a routine called VFP2Excel, written by Cetin Basoz, which uses an ADOdb.RecordSet, via "Provider=VFPOLEDB;Data Source=" + Sys(2023) in my Visual Foxpro system. If I use this in Lianja, it kicks out with an error message about an "expected variable". Before I start debugging this, I thought I would ask, in case the "Lianja Way" is still inscrutable to me <g>.

    Related to this, can I use "Lianja.showdocument() to present this Excel file on the screen or form? If so, are there any requirements for this, like utilizing a particular section, or initializing code?

    Thanks,

    Bill

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,954
    Its quite straightforward.

    Code:
    set xmlformat to ado
    use yourtablename
    copy to yourxmlfile type xml
    // you now have your data in Microsoft ADO XML format
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,954
    Lianja.showDocument( "filename.ext" ) will run the program that is associated with ".ext" so it can be used to open word documents, excel spreadsheets etc.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,954
    Additionally, Lianja SQL has built-in support for XML. Here is a one line command:

    Code:
    select * from yourtablename save as xml myxmlfilename format ado
    Which can be used with views and virtual tables as well.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  5. #5
    Junior Member
    Join Date
    Jun 2012
    Posts
    23
    Barry,

    Saving the table as xml ado per your instructions worked, except for one problem. I used my customer table as a test case. Some of my customer names include the "ampersand" character, as in "Tom Smith & Sons". Excel 2010 threw a "whitespace" error when parsing the XML file. I then opened the file in DreamWeaver, and replaced these ampersands with "&amp;". The file then opened correctly in Excel.

    As to opening the XML file in Excel 2010, is there a way to have it open without Excel asking whether to 1)open as XML, 2)open as readonly Workbook, or 3)open and use the XML Source Pane? Further, is there a way to bypass the writing of the schema to the worksheet? Users would be opening these files.

    Thanks,

    Bill

  6. #6
    Lianja Support Team lianjasupport's Avatar
    Join Date
    Feb 2012
    Location
    Boston USA, Wokingham UK
    Posts
    1,268
    Yes I noticed that, we will take a look at escaping these. This was a recent "fix" that broke the character escaping.

    Older versions of excel handled the schema, it seems 2010 does not.

    we have added a new command in Beta10R1:

    set xmlschema on|off

    Which cam be set to "off" to prevent the schema from being generated.

    wrt excel asking about the XML file, I agree it's rather lame. See if there us a command line switch for excel that prevents this dialog from being displayed.
    Last edited by barrymavin; 2012-11-11 at 20:43.

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,954
    The XML generated is now properly escaped in Beta10R1.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  8. #8
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,954
    In fact we have simplified the whole process now in Beta10R1 and added native export support for Excel.

    The NoSQL way:

    Code:
    set xmlformat to excel
    use yourtablename
    copy to myexcelfilename.xml type xml
    Lianja.showDocument("myexcelfilename.xml")
    and the SQL way:

    Code:
    select * from yourtablename save as xml myexcelfilename.xml format excel
    Lianja.showDocument("myexcelfilename.xml")
    Last edited by barrymavin; 2012-11-12 at 02:47.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  9. #9
    Member
    Join Date
    Oct 2012
    Location
    México city
    Posts
    34
    Hello I need to export my table to excel , but I need the image in my record I have A blob field with the image , and other fields with prices, description, etc.

    How can I do this?

    Thanks for the help

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us