On 8/15/2016 11:18 AM, Dave Crozier wrote:
I have been forced to bite the bullet on a small project and use
embedded OLE Excel objects as general fields in a VFP table.
...
Ideally I want to query the embedded Excel and pull back a named range
of cells. Into VFP. Any ideas? Dave
Others have touched on pieces of what I'd do, but here is the overall
"design" concept:
1) Store the Excel file in a Memo field (I do "Memo binary"); you can
get a file to a string to store via filetostr()
2) When you need data out:
a) save the contents of the memo field to a file - aka strtofile()
(temp file, whatever, recommend making the extension correct - .xlsx)
b) use Excel automation to open the file and do the usual data
extraction via automation
c) close the automation object and delete the file
Notes about this:
1) storing the file (filetostr()) and pulling it out and saving it to
access is VERY fast (VFP functions)
2) Excel automation is slow (as you're probably aware) - creating the
excel object (aka oExcel = CREATEOBJECT('excel.application')), and
pulling data through multiple calls to get cells values
3) if you have to read, modify, and restore file data into the VFP table
you'll have to occasionally PACK because of bloat.
4) filesize usually isn't too much of a problem - Excel spreadsheets can
be huge, but you can do some quick calcs to figure that out. With the
2GB limit on Memo files (.FPT) I've rarely hit a problem (when I did I
simply split the data into multiple tables via a VFP storedproc based on
filename, etc - and had a VFP storedproc decipher which table to go get
data from - I had something like 40GB of stuff like this in one app).
If you have a LOT of data you need to pull out of the Excel file, it may
be better to grab it and save it in a separate table while your are
storing the file in the memo field. You could even use automation to
copy the cell-range to it's own SS tab, then save that tab as a .CSV,
and then import that into your own table (I've not had great success
with EXCEL XML being perfectly readable in VFP). So if you've got like
10,000 rows of data, that may be a better way. Then in the table that
has the data you can have the foreign key back to the table that has the
actual SS in a memo field.
I'm suggesting all this because it seems you need to be able to get to
that original spreadsheet - the real thing - at any given time. And if
you store the filename you can essentially recreate it whenever you
want. But it also seems you want data from that SS for your app or
whatever. So the automation approach or the data extract while saving
approach will work for that. Of course this assumes the SS isn't
constantly changing, or that your app is not changing the data in the
SS, etc; that would add a little more complexity, but still be quite
feasible.
HTH,
-Charlie
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
