[SOLVED] Export to Excel, Then View Excel File

[mastb]

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

[barrymavin]

Its quite straightforward.

set xmlformat to ado
use yourtablename
copy to yourxmlfile type xml
// you now have your data in Microsoft ADO XML format

[barrymavin]

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.

[barrymavin]

Additionally, Lianja SQL has built-in support for XML. Here is a one line command:

select * from yourtablename save as xml myxmlfilename format ado

Which can be used with views and virtual tables as well.

[mastb]

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

[lianjasupport]

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.

[barrymavin]

The XML generated is now properly escaped in Beta10R1.

[barrymavin]

In fact we have simplified the whole process now in Beta10R1 and added native export support for Excel.

The NoSQL way:

set xmlformat to excel
use yourtablename
copy to myexcelfilename.xml type xml
Lianja.showDocument("myexcelfilename.xml")

and the SQL way:

select * from yourtablename save as xml myexcelfilename.xml format excel
Lianja.showDocument("myexcelfilename.xml")

[gontran]

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