[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

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

---

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.

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.

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

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.

The XML generated is now properly escaped in Beta10R1.

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")

---

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