Vilhelm-Ion Praisach: ImportFromXlsx 3.4

Improvements
- lcTableName has the same effect for cursors and tables
- check lcTableName for reserved characters (like :/\ and so on)
- small correction in error messages

(thanks to Koen Piller)

Download link
Import from xlsx 3.4

How to use it
1) The easiest way
Let's say you have a spreadsheet called MyXlsxFile.xlsx, and your goal is to import the data contained in this spreadsheet into a VFP table (the data from the first worksheet) .
All you have to do is:

             importfromxlsx('MyXlsxFile.xlsx')

The previous command creates a table (dbf) with the same name MyXlsxFile.dbf

2) Let's say you have a spreadsheet called MyXlsxFile.xlsx, and your goal is to import the data contained in this spreadsheet into a VFP table (the data from the first worksheet) .
But this time you want to skip the first three rows from the spreadsheet (and import the data starting with the fourth row of the XLSX document)
All you have to do is:

             importfromxlsx('MyXlsxFile.xlsx',4)

3) Let's say you have a spreadsheet called MyXlsxFile.xlsx, and your goal is to import the data contained in this spreadsheet into a VFP table.
But this time you want to import the data contained in the second worksheet.
All you have to do is:

             importfromxlsx('MyXlsxFile.xlsx',,2)

Note the two commas.

4) Let's say you have a spreadsheet called MyXlsxFile.xlsx, and your goal is to import the data contained in this spreadsheet into a VFP table.
But this time you want to import the data contained in the second worksheet and to skip the first three rows from the spreadsheet (and import the data starting with the fourth row of the XLSX document)
All you have to do is:

             importfromxlsx('MyXlsxFile.xlsx',4,2)

5) Let's say you have a spreadsheet called MyXlsxFile.xlsx, and your goal is to import the data contained in this spreadsheet into a VFP cursor (the data from the first worksheet) .
All you have to do is:

             ccur = importfromxlsx('MyXlsxFile.xlsx',,,.T.)

Note the three commas.

The name of the cursor is stored into the ccur variable

6) Let's say you have a spreadsheet called MyXlsxFile.xlsx, and your goal is to import the data contained in this spreadsheet into a VFP table (the data from the first worksheet).

But this spreadsheet contains empty cells (some of the cell are empty)
All you have to do is:

ccur = importfromxlsx('MyXlsxFile.xlsx',,,,.T.)

Note the four commas.

Obviously you can combine the parameters explained in examples (4), (5) and (6), e.g

ccur = importfromxlsx('MyXlsxFile.xlsx',,,.T.,.T.)

creates a cursor from a spreadsheet that contains empty cells, while
ccur = importfromxlsx('MyXlsxFile.xlsx',,3,,.T.)

creates a table from the third worksheet, that contains empty cells, a.s.o

7) Until now, the table / cursor that was generated, have all the fields with anonymous names, like mField1, mField2 a.s.o

If you intend to get the field name directly from the worksheet, you must provide the row number where these field names are.

This command

ccur = importfromxlsx('MyXlsxFile.xlsx',2,,,,1)

creates a table that get the data beginning with the second row and with the column names from the first row of the worksheet, while this one

ccur = importfromxlsx('MyXlsxFile.xlsx',8,,,,5)

creates a table that get the data beginning with the eight row and with the column names from the fifth row of the worksheet.

8) The last parameter gives you the possibility to chose the name of the generated table.

Instead of a table with the same name of the xlsx, you can say another name, or you can instruct VFP to display a "SaveAs" window that let you choose the name.

Proceed this way:

importfromxlsx('MyXlsxFile.xlsx',,,,,,'TheDesiredNameOfTheTable')

to create TheDesiredNameOfTheTable.dbf from MyXlsxFile.xlsx, respectively

importfromxlsx('MyXlsxFile.xlsx',,,,,,'?')

4 comentarii:

Unknown9 octombrie 2016 la 16:56
Acest comentariu a fost eliminat de autor.
RăspundețiȘtergere
Răspunsuri
Unknown9 octombrie 2016 la 17:01

Am fost în căutarea pentru o soluție similară și a ta este excelent. Pur și simplu nu știu cum să-l folosească în propria mea aplicație. Nu am multă experiență în VFP. Ai putea să mă ajute cu un pic mai multe informații despre modul de utilizare a codului te rog? Vă mulțumesc foarte mult în avans.
RăspundețiȘtergere
Răspunsuri
Dan Baker27 ianuarie 2021 la 12:23
Vilhelm, I remember your Excel-to-VFP classes several years ago when I was regularly watching the VFP site foxite.com.

Well, finally I had a chance to use them in production and they are nice and solid piece of work. Thank you for your efforts. Hope you in good health.

Dan Baker, now in Athens, GA USA
RăspundețiȘtergere
Răspunsuri

Adăugați un comentariu

Vilhelm-Ion Praisach

Faceți căutări pe acest blog

marți, 16 august 2016

ImportFromXlsx 3.4

4 comentarii:

Translate

Faceți căutări pe acest blog

marți, 16 august 2016

ImportFromXlsx 3.4

4 comentarii:

Translate

Abonați-vă la