Vilhelm-Ion Praisach: august 2016

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',,,,,,'?')

Vilhelm-Ion Praisach

Faceți căutări pe acest blog

marți, 16 august 2016

ImportFromXlsx 3.4

luni, 15 august 2016

ImportFromOoCalc

ImportFromXlsx 3.3

Translate