Purpose :
Select random exelfiles ex. many files we retrieve from manufacturers for updating pricelist and all of
them are different in form/headers
The headers will be centralised in the dbf for new update files received from manufacturers and the matching dbf fields
we be linked automatic.
Match the headers with the structure of our dbf that contains ALL priceslist for an import/update prcedure
Issues :
1. I'm doing it wrong with the new FW_exeltodbf (no data in dbf). I think the array that i use is wrong made ?
2. If we only use 3 fields can the function only output these 3 fields to dbf (The program select the ones the user want)
Use of the test :
1 select a random exel file
2 a browse will showup with the oRange and headers. Right click and the headers will be put in a central dbf
here we match the headers with the dbf fields (pulldown in browse)
SELECT A GREEN TAG AND THE CORRESPONDING DBF TAG FOR SELECTION TO USE
3 EXIT THE BROWSE AND CONFIRM THE UPDATE
4 FW_Exeltodbf will do his job and the data should be there. (Not at this moment)
The Sample :
- Code: Select all Expand view RUN
#include "fivewin.ch"
REQUEST DBFCDX
function Main()
SET DATE BRITISH
SET CENTURY ON
SET DELETED ON
RDDSETDEFAULT( "DBFCDX" )
maketestdata() // make dbf's for the test
readdata() // select random exel file, Xbrowse shows oRange AND RIGHT CLICK TO PROCEDE
Browsedata() // browse result
close all
return nil
//----------------------------------------------------------------------------//
function Browsedata()
local oDlg, oFont, oBold, oMono, oGet, oBrw
DEFINE FONT oFont NAME "Segoe UI" SIZE 0,-12
DEFINE FONT oBold NAME "TAHOMA" SIZE 0,-14 BOLD
DEFINE FONT oMono NAME "Lucida Console" SIZE 0,-12
DEFINE DIALOG oDlg SIZE 1600,800 PIXEL TRUEPIXEL FONT oFont ;
TITLE "Test data view on Fw Version : "+FWVERSION
@ 90,20 XBROWSE oBrw SIZE 900,-20 PIXEL OF oDlg ;
DATASOURCE "data" ;
COLUMNS "code", "naam", "prijs","Korting","Brand", "RefLev","Link" ;
HEADERS "code", "naam", "prijs","Kor", "Brand", "RefLev","Link" ;
COLSIZES 100,300,80,40,100,100,100 ;
AUTOSORT LINES NOBORDER
WITH OBJECT oBrw
:nMarqueeStyle := MARQSTYLE_HIGHLROWRC
:bChange := { || oDlg:Update() }
:lIncrFilter := .t.
:CreateFromCode()
END
@ 10,20 BUTTON "Update Master file" size 80,20 pixel OF oDlg //ACTION UpdateMaster font oFont
@ 20, 10 SAY TRIM( data->naam ) SIZE 860,30 PIXEL OF oDlg CENTER ;
FONT oBold UPDATE
@ 60, 20 SAY "Filter containing all words any where" SIZE 300,20 PIXEL OF oDlg
@ 60,340 SAY oBrw:oSeek PROMPT oBrw:cSeek SIZE 540,20 PIXEL OF oDlg ;
COLOR CLR_HRED,CLR_YELLOW
@ 90,1000 SAY "CODE" SIZE 460,30 PIXEL OF oDlg CENTER ;
COLOR CLR_BLACK, nRGB( 231, 242, 255 )
@ 120,1000 GET oGet VAR data->memo SIZE 460,540 PIXEL OF oDlg ;
MEMO READONLY FONT oMono UPDATE
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont, oMono, oBold
return nil
//----------------------------------------------------------------------------//
function readdata()
Local cCurFile, oRange, Cp, oBrw
local aConvert:={}
cp := HB_SETCODEPAGE( "UTF8" ) // Depends on region ?
cCurFile := cGetFile( "Exel file| *.xl*| ", "Please select a file" )
oRange := GetExcelRange( cCurfile , "Sheet1" , ) // Sheet1 Tab named and used insite the exel file
msginfo("When the xbrowse shows up, right click to see the headers")
xbrowser(oRange) TITLE "RIGHT CLICK ON BROWSE TO ACTIVATE THE HEADER UPDATE : Version : "+FWVERSION;
SETUP (oBrw:bRclicked := { |r,c,f,o| checkheader(o) })
If MsgYesNo("Add exeldata to dbf")
xlsfields->(dbgotop()) // the selected headers from dbf are used
do while !xlsfields->(eof()) // only when xls header exist and the corresponding dbf file is selected
if xlsfields->xlsselect .and. xlsfields->select
AADD(aConvert,{xlsfields->dbffield,xlsfields->xlsfield})
endif
xlsfields->(dbskip())
enddo
xbrowse(aConvert)
select data
//aTest:= { { "CODE", "CUSTOMS CODE" }, { "NAAM", "DESCRIPTION" } }
//xbrowse(aTest)
if len(aConvert) > 0 // only when there are fields selected
FW_ExcelToDBF( oRange, aConvert , .t. )
// FW_ExcelToDBF( oRange, aTest , .t. )
endif
endif
oRange:WorkSheet:Parent:Close() // close exel link
data->(dbgotop())
return nil
function checkheader(oBrw)
local aheaders, I , cZoek
local oDlgH, oFont, oBold, oMono, oGet,oBrwH
local aFieldnames
if msgYesNo("Headers of XLS file will be added to database")
aHeaders := oBrw:cHeaders // Accessing column data returns an array of values.
for i = 1 to len(aHeaders)
cZoek = alltrim(upper(aheaders[i]))
if !xlsfields->(dbseek(cZoek))
xlsfields->(dbappend())
xlsfields->xlsfield = cZoek
xlsfields->xlsselect = .t.
else
xlsfields->xlsselect = .t. // Fieldnames present will be tagged to see
endif
next
endif
//xbrowser "xlsfields" TITLE "Conversion Table. Match de correct Xls to DBF headers" FASTEDIT
aFieldNames := data->(ARRAY(FCOUNT()))
data->(aSort(AFIELDS(aFieldNames)))
DEFINE FONT oFont NAME "Segoe UI" SIZE 0,-12
DEFINE FONT oBold NAME "TAHOMA" SIZE 0,-14 BOLD
DEFINE FONT oMono NAME "Lucida Console" SIZE 0,-12
DEFINE DIALOG oDlgH SIZE 600,800 PIXEL TRUEPIXEL FONT oFont ;
TITLE "Conversion table"
@ 50,20 XBROWSE oBrwH SIZE -20,-20 PIXEL OF oDlgH ;
DATASOURCE "xlsfields" ;
COLUMNS "Xlsselect","xlsfield", "dbffield", "select" ;
HEADERS "Found","Xlsfield", "Dbffield", "Selected" ;
COLSIZES 50,250,100,50 ;
AUTOSORT LINES NOBORDER FASTEDIT
WITH OBJECT oBrwH
:nMarqueeStyle := MARQSTYLE_HIGHLROWRC
:bChange := { || oDlgH:Update() }
:lIncrFilter := .t.
oBrwH:SetChecks()
oBrwH:Found:bClrStd := { || IF( xlsfields->xlsselect , { CLR_BLACK,CLR_HGREEN } , { CLR_HRED,CLR_WHITE } ) }
WITH OBJECT :dbffield
:nEditType := EDIT_LISTBOX
:aEditListTxt := aFieldnames
END
WITH OBJECT :found
:nEditType := EDIT_GET
END
WITH OBJECT :selected
:nEditType := EDIT_GET
END
:CreateFromCode()
END
//@ 10,20 BUTTON "Start import" size 150,30 pixel OF oDlgH ACTION UpdateMaster font oFont
@ 10, 20 SAY "Green = Headers fund in current exel. Select also the dbf fields to be used. " SIZE 550,20 PIXEL OF oDlgH
ACTIVATE DIALOG oDlgH CENTERED
RELEASE FONT oFont, oMono, oBold
return nil
return NIL
function maketestdata()
local aStruct
FIELD xlsfield
// here we create datafile for testing (data is always blank for testing)
DBCREATE( "data.DBF", { ;
{ "code", 'C', 30, 0 }, ;
{ "naam", 'C',150, 0 }, ;
{ "prijs", 'N', 9, 2 }, ;
{ "Korting", 'N', 6, 2 }, ;
{ "Brand", 'C', 30, 0 }, ;
{ "RefLev", 'C', 30, 0 }, ;
{ "DATE", 'D', 8, 0 }, ;
{ "LINK", 'C',250, 0 }, ;
{ "memo", 'M', 10, 0 }}, ;
"DBFCDX", .T., "DB" )
FW_CdxCreate()
CLOSE DB
if !file("xlsfields.dbf") // will contain de headers from the xls files and the corresponding dbf header
aStruct := {}
AADD(aStruct, { "xlsSelect", "L", 1, 0 }) // Id
AADD(aStruct, { "xlsfield", "C", 50, 0 }) // Id
AADD(aStruct, { "dbffield", "C", 15, 0 }) // Id
AADD(aStruct, { "select", "L", 1, 0 }) // Id
DbCreate( "xlsfields.dbf", aStruct)
use xlsfields NEW
index on xlsfield tag xlsfield
close all
endif
use xlsfields NEW
select xlsfields
replace xlsselect with .f. all // put False so only the new headers will be selected in the browse
xlsfields->(dbsetorder("xlsfield"))
USE data VIA "DBFCDX" NEW
data->(dbsetorder("code"))
return NIL
