Page 1 of 2
DC_WorkArea2Excel
Posted: Sat Mar 19, 2011 9:56 pm
by TWolfe
I have been trying to get a database to convert to an Excel work sheet using DC_WorkArea2Excel and have not been able to get Character fields to convert properly if they contain strings that look like numbers (ie. 109100004.98 in a dbf field formatted as C 12).
Attached is a small dbf to try. Also the conversion errors out after 20 to 30 records. I am running express version 1.9.254 and X++ version 1.90.355 SL1
Roger, If you could send me the latest version of _dcfunct.prg, perhaps these problems have already been fixed.
Thanks,
Terry
Re: DC_WorkArea2Excel
Posted: Sat Mar 19, 2011 10:19 pm
by Auge_Ohr
hi,
how does your Field Array Look like ? (5th Parameter of DC_WorkArea2Excel() )
Code: Select all
cFormat := aFields[i,3]
IF !Empty(cFormat)
oSheet:Columns(i):NumberFormat := cFormat
ENDIF
Re: DC_WorkArea2Excel
Posted: Sat Mar 19, 2011 10:58 pm
by TWolfe
No field array was used:
DC_WorkArea2Excel('c:\CB.XLS',NIL,.T.,.T.)
Trying to dump the complete .DBF
I also tried it with and without the lAutoFit option. (The documentation states that lAutoFit defaults to .T. however the function actually defaults to .F.)
Terry
Re: DC_WorkArea2Excel
Posted: Sun Mar 20, 2011 12:24 am
by Auge_Ohr
TWolfe wrote:No field array was used:
DC_WorkArea2Excel('c:\CB.XLS',NIL,.T.,.T.)
so try it with Field Array
use 5th Parameter and fill Field Array Element 3 with NIL .
Code: Select all
aFields := { ;
{ 'AREACODE', 'Area Code', nil}, ;
{ 'EXCHANGE', 'Exchange', nil}, ;
{ 'NUMBER', 'Number', nil}, ;
{ 'CITY', 'City', nil}, ;
{ 'STATE', 'State', nil}, ;
{ 'COUNTRY', 'Country', nil}, ;
{ 'TEL_CALLED', 'Number Called', nil}, ;
{ 'DATE', 'Date', nil}, ;
{ 'TIME', 'Time', nil} }
cExcelFile := DC_Path(AppName(.t.)) + 'workarea.xls'
DC_WorkArea2Excel(cExcelFile,,,,aFields)
Re: DC_WorkArea2Excel
Posted: Sun Mar 20, 2011 8:18 am
by TWolfe
Jimmy,
The fifth parameter of DC_WorkArea2Excel (aFields) is a single-dimension array
{"INDEX_NO",FINE","BANK_DATE"}
I have tried that and it makes no difference.
Terry
Re: DC_WorkArea2Excel
Posted: Sun Mar 20, 2011 8:25 am
by RDalzell
Terry,
I use the attached without issue...
Rick
Re: DC_WorkArea2Excel
Posted: Sun Mar 20, 2011 10:28 am
by rdonnay
Terry -
There have been no code changes since 254, but I remember this issue when working with Bobby Drakos, however that was using DC_Excel2WorkArea().
I thought that I had resolved that issue using formatting, but I don't remember. I will look into this.
Roger
Re: DC_WorkArea2Excel
Posted: Sun Mar 20, 2011 1:15 pm
by rdonnay
When I run the function against your data, I get a runtime error.
This appears to be due to the fact that Excel doesn't like DATE fields in the array.
I made the below change to my code in _DCFUNCT.PRG (DC_WorkArea2Excel()) and it created the XLS file correctly.
I'm going to need to do more research to find out what format is needed for dates.
Code: Select all
nRow += 2
DO WHILE !DC_Eof() .AND. lStatus
DC_CompleteEvents()
DC_GetProgress(oProgress,nCount++,nKeyCount)
FOR i := 1 TO Len(aFields)
cFieldName := aFields[i,1]
IF Valtype(&(cFieldName)) == 'D'
aRow[i] := Dtoc(&(cFieldName))
ELSE
aRow[i] := &(cFieldName)
ENDIF
NEXT
AAdd( aData, AClone(aRow) )
nRow++
DC_DbSkip(1)
ENDDO
Re: DC_WorkArea2Excel
Posted: Sun Mar 20, 2011 6:12 pm
by Auge_Ohr
rdonnay wrote:This appears to be due to the fact that Excel doesn't like DATE fields in the array.
try something like this
Re: DC_WorkArea2Excel
Posted: Mon Mar 21, 2011 9:24 am
by rdonnay
Thank you Jimmy.
I will give that a try.