DC_WorkArea2Excel

[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

[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

[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

[Auge_Ohr]

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)

[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

[RDalzell]

Terry,

I use the attached without issue...

Rick

[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

[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

[Auge_Ohr]

This appears to be due to the fact that Excel doesn't like DATE fields in the array.

try something like this

Code: Select all

NumberFormat = "d/m/yyyy;@"

[rdonnay]

Thank you Jimmy.

I will give that a try.