Mr. Jimmy
You can simplify your programs A LOT by using FWH built-in functions. When you recommend, they are easier to implement by other programmers.
This code:
Code: Select all | Expand
oExcel := CreateObject( "Excel.Application" )
oExcel:Application:Workbooks:open(ZPATH+cFILE)
// Make the first one active
oWorkBook := oExcel:activeWorkBook
oExcel:Application:Worksheets(1):activate()
// Speed things up by creating an object containing the cells
oSheet := oExcel:Worksheets(1):cells
oWorkBook:workSheets(1):usedRange:Select
can be replaced by single line code:
This code:
Code: Select all | Expand
numRows := oWorkBook:workSheets(1):usedRange:Rows:Count
numColumns := oWorkBook:workSheets(1):usedRange:Columns:Count
// make Array with same Size
FOR i := 1 TO numRows
AADD(aExcel,ARRAY(numColumns))
NEXT
can be replaced by this single line code, which is more efficient than your logic
For writing all values in a range to DBF:
Code: Select all | Expand
// Create of open dbf
( cAlias )->( FW_ExcelToDBF( oRange, [cFieldList], [lHasHeaders], [bProgress] )
Syntax:
FW_ExcelToDBF() --> lSuccess
Writes the contents of excel range to default alias.
Parameters:
1. [oRange] Defaults to used range in active worksheet if already open by the user.
2. [cFieldList]
a) NIL:
( i) If range has headers in first row: Copies contents of each column to
the field whose name is same as the header of the column. Skips when
no match is found.
(ii) If range has no headers:
Copies contents of excel columns from left to right into fields 1 to last.
- Char: Should be a comma delemited list of field names of the dbf.
( i) If range has headers in first row: Copies contents of columns with header
maching the contents of the list to fields with the same name. Skips when
no match is found.
(ii) If range has no headers:
Copies contents of excel columns from left to right into fields contained
in the list. Skips in case of no match.
- Two-dimensional array: First element of each item contains the field name and
the second element contains the corresponding header in the excel range or
number of the excel column or alpha-column number like "A", "C", "AB", etc.
3) [lHasHeaders]
- .T.: First row of the range is treated as headers and data is from 2nd row
- .F.: The sheet does not have any headers and all rows are treated as data.
- NIL: The function examines contents of 1st and 2nd rows, If 1st row contains
all character values and 2nd row contains atleast one non-characer value,
lHasHeaders defaults to .T.
Now, the suggested code for the original post is this:
Code: Select all | Expand
// Open or Create dbf
( cAlias )->( FW_ExcelToDBF( GetExcelRange( TrueName( cExcelFile ) ) ) )
instead of the 16 line code proposed by you.
This is even much simpler:
1. Open the excel book with Excel application and leave it open.
Then,
The function ExcelToDbf() will read the used range from the open workbook and write to default alias.
However what I am not sure, till I test at my end, is whether the huge data of 1000000 rows x 30 fields will create any memory issues. Also in view of the large size, it is desirable to provide progress bar.
