Faceți căutări pe acest blog

marți, 16 august 2016

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

to display a "SaveAs" dialog window

Related posts
http://praisachion.blogspot.com/2016/06/inspectpptx-10.html
http://praisachion.blogspot.com/2016/06/inspectdocx-10.html

MSOffice -> DBF
http://praisachion.blogspot.com/2017/08/importfromxlsx-40.html
http://praisachion.blogspot.com/2017/06/append-from-xlsx-30.html
http://praisachion.blogspot.com/2016/08/importfromxlsx-34.html
http://praisachion.blogspot.com/2016/08/importfromxlsx-33.html
http://praisachion.blogspot.com/2016/06/importfromxlsx-32-appendfromxlsx-21.html
http://praisachion.blogspot.com/2016/06/appendfrompptx-10.html
http://praisachion.blogspot.com/2016/06/appendfromdocx-21.html
http://praisachion.blogspot.com/2016/06/importfromdocx-31.html
http://praisachion.blogspot.com/2016/06/importfrompptx-11.html
http://praisachion.blogspot.com/2016/06/importfromxlsx-31.html
http://praisachion.blogspot.com/2016/06/importfrompptx-10.html
http://praisachion.blogspot.com/2016/06/importfromdocx-30.html
http://praisachion.blogspot.com/2016/05/importfromxlsx-22.html
http://praisachion.blogspot.com/2016/02/import-dbf-from-msoffice-2007-xlsx-docx.html
http://praisachion.blogspot.com/2016/01/importfromdocx-20.html
http://praisachion.blogspot.com/2016/01/appendfromdocx-20.html
http://praisachion.blogspot.com/2016/01/importfromxlsx-13.html
http://praisachion.blogspot.com/2016/01/appendfromxlsx-20.html
http://praisachion.blogspot.com/2015/12/appendfromxlsx-15.html
http://praisachion.blogspot.com/2015/11/importfromxlsx-13.html
http://praisachion.blogspot.com/2015/09/importfromxlsx-12.html
http://praisachion.blogspot.com/2015/09/appendfromxlsx-14.html
http://praisachion.blogspot.com/2015/09/appendfromnxlsx-13.html
http://praisachion.blogspot.com/2015/08/importfromxlsx-11.html
http://praisachion.blogspot.com/2015/07/import-from-xlsx.html
http://praisachion.blogspot.com/2015/07/insert-from-focx.html
http://praisachion.blogspot.com/2015/06/append-from-docx.html

DBF -> MSOffice
http://praisachion.blogspot.com/2017/04/export-dbf-to-excel-2007.html
http://praisachion.blogspot.com/2017/01/export-dbf-to-msexcel-xlsx.html
http://praisachion.blogspot.com/2017/01/export-dbf-to-mspowerpoin-pptx.html
http://praisachion.blogspot.com/2017/01/export-dbf-to-msword-docx.html
http://praisachion.blogspot.com/2016/11/export-to-xlsx.html
http://praisachion.blogspot.com/2016/11/export-to-pptx.html
http://praisachion.blogspot.com/2016/11/export-to-docx.html
http://praisachion.blogspot.com/2016/11/export-from-vfp6-to-msoffice-docx-pptx.html
http://praisachion.blogspot.com/2016/09/copytoxlsx6-10.html
http://praisachion.blogspot.com/2016/01/export-dbf-to-msoffice-2007-xlsx-docx_24.html
http://praisachion.blogspot.com/2016/01/export-dbf-to-msoffice-2007-xlsx-docx.html
http://praisachion.blogspot.com/2016/01/exporttopptx-20.html
http://praisachion.blogspot.com/2016/01/exporttodocx-20.html
http://praisachion.blogspot.com/2016/01/copytopptx-20.html
http://praisachion.blogspot.com/2016/01/exporttoxlsx-20.html
http://praisachion.blogspot.com/2016/01/copytoxlsx-30.html
http://praisachion.blogspot.com/2015/12/exporttoxlsx-19-class.html
http://praisachion.blogspot.com/2015/12/copytoxlsx-210-procedure.html
http://praisachion.blogspot.com/2015/12/exporttoxlsx-18-class.html
http://praisachion.blogspot.com/2015/12/copytoxlsx-29.html
http://praisachion.blogspot.com/2015/02/export-pptx-13.html
http://praisachion.blogspot.com/2015/01/copytodocx-12.html
http://praisachion.blogspot.com/2015/01/exportdocx-1.html

OOffice -> DBF
http://praisachion.blogspot.com/2016/11/import-from-openoffice-libreoffice-for.html
http://praisachion.blogspot.com/2016/09/importfromoowrtext-10.html
http://praisachion.blogspot.com/2016/09/importfromooffice-1o.html
http://praisachion.blogspot.com/2016/08/importfromoocalc.html

DBF -> OOffice
http://praisachion.blogspot.com/2016/09/export-dbf-to-openoffice-libreoffice_52.html
http://praisachion.blogspot.com/2016/09/export-dbf-to-openoffice-libreoffice_16.html
http://praisachion.blogspot.com/2016/09/copy-to-ods-10-openoffice-calc.html
http://praisachion.blogspot.com/2016/09/export-dbf-to-openoffice-libreoffice.html
http://praisachion.blogspot.com/2016/09/copy-to-odt-10.html

3 comentarii:

  1. Acest comentariu a fost eliminat de autor.

    RăspundețiȘtergere

  2. 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
    1. Just added a "How to use it" section to answer to your question :)

      Ștergere