The Class FileXLS does not require MS-Excel to be installed in user's computer. If I am not wrong, the downside is that it will create Excel file in Excel 97 format only and not the latest excel file formats. It looks like that the class is not updated for a very long time.George wrote:Hi Forum,
2. What is the class suggested (or ActiveX) to import an Excel file to DBF without having MS-Excel installed in user's computer?
George
You just need their LibXL.dll along with your application exe folder(similar to FastReport). It creates Excel file in latest formats.
Code: Select all
static function import_data_cutomer_dari_excel(oBrw)
local cfilename := cGetFile('*.csv' ,'Import Data' )
local otextfile
local cline :=''
LOCAL ADATA :={}
LOCAL NCTL :=0
LOCAL NTEMP_INDEX :=0
LOCAL NTEMP_INDEX_SALES := 0
otextfile:=ttxtfile():new(cfilename)
IF OTEXTFILE:SEEK('DATA')
for nctl:=1 to otextfile:NTLINES()
cline := otextfile:readline()
IF upper(ALLTRIM(StrToken(cline, 1 ,';' )))=='DATA'
AADD(ADATA,{ StrToken(cline, 1 ,';' ) ,; // DATA
StrToken(cline, 2 ,';' ) ,; //KODE CUSTOMER
StrToken(cline, 3 ,';' ) ,; //STATUS PERUSAHAAN PT /CV /LTD DLL
StrToken(cline, 4 ,';' ) ,; // NAMA CUSTOMER
StrToken(cline, 5 ,';' ) ,; // ALAMAT
StrToken(cline, 6 ,';' ) ,; // KOTA
StrToken(cline, 7 ,';' ) ,; // NPWP
StrToken(cline, 8 ,';' ) ,; // KODE POS
StrToken(cline, 9 ,';' ) ,; // CONTACK PERSON
StrToken(cline, 10 ,';' ) ,; // JABATAN KONTAK
StrToken(cline, 11 ,';' ) ,; // TELP
StrToken(cline, 12 ,';' ) ,; // FAK
StrToken(cline, 13 ,';' ) ,; // EMAIL
StrToken(cline, 14 ,';' ) ,; // WILAYAH
StrToken(cline, 15 ,';' ) ,; // KETERANGAN
StrToken(cline, 16 ,';' ) ,; // KODE SALES
StrToken(cline, 17 ,';' ) ; // NAMA SALES
};
)
endif
otextfile:advance()
next
OTEXTFILE:CLOSE()
dbselectarea(AALIAS[DB_SALESMAN])
NTEMP_INDEX_SALES := INDEXORD()
SET ORDER TO TAG KODE
DBSELECTAREA(AALIAS[DB_CUSTOMER])
NTEMP_INDEX := INDEXORD()
SET ORDER TO TAG KODE
TRY
AdsBeginTransaction()
FOR NCTL =1 TO LEN(ADATA)
DBSELECTAREA(AALIAS[DB_CUSTOMER])
SEEK LEFT(ADATA[NCTL,2],6)
IF FOUND()
MSGALERT('Kode : '+alltrim(ADATA[NCTL,2]) +' sudah ada')
else
if flock()
append blank
replace kode with LEFT(ALLTRIM(ADATA[NCTL,2]),6)
replace titel with ALLTRIM(ADATA[NCTL,3])
replace nama with ALLTRIM(ADATA[NCTL,4])
replace alamat with ALLTRIM(ADATA[NCTL,5])
replace kota with ALLTRIM(ADATA[NCTL,6])
replace npwp with ALLTRIM(ADATA[NCTL,7])
replace zipcode with ALLTRIM(ADATA[NCTL,8])
// ALAMAT NPWP DISAMAKAN
replace nalamat with ALLTRIM(ADATA[NCTL,5])
replace nkota with ALLTRIM(ADATA[NCTL,6])
replace nzipcode with ALLTRIM(ADATA[NCTL,8])
//----alamat npwp
replace kontak with ALLTRIM(ADATA[NCTL,9])
replace jabatan with ALLTRIM(ADATA[NCTL,10])
replace phone with ALLTRIM(ADATA[NCTL,11])
replace phone3 with ALLTRIM(ADATA[NCTL,12])
replace email with ALLTRIM(ADATA[NCTL,13])
replace wilayah with ALLTRIM(ADATA[NCTL,14])
replace catatan with ALLTRIM(ADATA[NCTL,15])
replace kodesales with LEFT( ALLTRIM(ADATA[NCTL,16]) ,10) // PANJANG KODE SALES 10
ELSE
BREAK
ENDIF
dbselectarea(AALIAS[DB_SALESMAN])
SEEK LEFT( ADATA[NCTL,16] ,10)
IF !FOUND()
IF FLOCK()
append blank
REPLACE SL_KODE WITH LEFT( ADATA[NCTL,16] ,10)
REPLACE SL_NAMA WITH LEFT( ADATA[NCTL,17] ,35)
ELSE
BREAK
ENDIF
ENDIF
endif
NEXT
AdsCommitTransaction()
dbunlockall()
CATCH
AdsRollback()
dbunlockall()
msgstop('Proses Import Gagal!!!')
dbselectarea(AALIAS[DB_SALESMAN])
SET ORDER TO NTEMP_INDEX_SALES
DBSELECTAREA(AALIAS[DB_CUSTOMER])
SET ORDER TO NTEMP_INDEX
return nil
END
ELSE
OTEXTFILE:CLOSE()
MSGALERT('Proses dibatalkan!!')
RETURN NIL
endif
dbselectarea(AALIAS[DB_SALESMAN])
SET ORDER TO NTEMP_INDEX_SALES
DBSELECTAREA(AALIAS[DB_CUSTOMER])
SET ORDER TO NTEMP_INDEX
oBrw:REFRESH()
MSGINFO('Proses Complate')
return nil
Code: Select all
FUNCTION AddingCSV2ADS(nPercent1, oMeter, oDlg1, nTotalRecords, cFile, cTitle, nDivisor)
APPEND FROM &cFile WHILE (nTotalRecords++,;
nPercent1++,;
oMeter:Set(nPercent1),;
IIF(nPercent1/nDivisor = int(nPercent1/nDivisor),;
(oDlg1:cTitle := cTitle + "; Records Loaded: " +;
alltrim(str(nTotalRecords)), oDlg1:Update(),;
SysRefresh()), ), TRUE ) ;
DELIMITED WITH ({ '"', "," })
RETURN NIL
