The value in the array must also be converted to a character string.
Terry - Replace the function in _DCFUNCT.PRG with the below code an let's see if this works for you.
Code: Select all
FUNCTION DC_WorkArea2Excel( cExcelFile, nOrientation, lDisplayAlerts, ;
lVisible, aFields, lAutoFit )
LOCAL oExcel, oBook, oSheet, nRow, aStru, i, cHeader, ;
cFieldName, cFieldType, nFieldLen, nFieldDec, cFormat, ;
xValue, GetList[0], GetOptions, oDlg, nCount := 0, ;
cDbfName, nKeyCount, oProgress, lStatus := .t., aData, ;
cRow, cColumns, cRange, aRow, bError, aStru2, nFound
DEFAULT nOrientation := xlLandscape, ;
lDisplayAlerts := .f., ;
lVisible := .f., ;
lAutoFit := .f., ;
cExcelFile := DC_Path(AppName(.t.)) + 'worksheet.xls'
#if XPPVER > 1900000
// Create the "Excel.Application" object
oExcel := CreateObject("Excel.Application")
IF Empty( oExcel )
DC_WinAlert( "Excel is not installed" )
RETURN .f.
ENDIF
#else
DC_WinAlert('This feature is available in Xbase++ 1.9 and later only!')
RETURN .f.
#endif
// Avoid message boxes such as "File already exists". Also,
// ensure the Excel application is visible.
oExcel:DisplayAlerts := lDisplayAlerts
oExcel:visible := lVisible
// Add a workbook to the Excel application. Query for
// the active sheet (sheet-1) and set up page/paper
// orientation.
cDbfName := dbInfo(DBO_FILENAME)
IF cDbfName = '<CURSOR>'
nKeyCount := RecCount()
ELSE
nKeyCount := DC_KeyCount()
ENDIF
@ 0,0 DCSAY 'Creating Excel Worksheet: ' + cExcelFile SAYSIZE 0
@ 1,0 DCPROGRESS oProgress SIZE 50,1 ;
TYPE XBPSTATIC_TYPE_TEXT ;
COLOR GRA_CLR_CYAN, GRA_CLR_WHITE ;
PERCENT ;
PERCENTCOLOR GRA_CLR_RED ;
RADIUS 20 ;
OUTLINE ;
DYNAMIC ;
EVERY Int(nKeyCount/100)
@ 3,0 DCPUSHBUTTON CAPTION 'Cancel' SIZE 9,1.2 ACTION {||lStatus:=.f.}
DCGETOPTIONS NORESIZE ALWAYSONTOP _PIXEL .f.
DCREAD GUI FIT TITLE 'Exporting to Excel' ;
MODAL EXIT PARENT @oDlg OPTIONS GetOptions NOAUTORESTORE
oBook := oExcel:workbooks:Add()
oSheet := oBook:ActiveSheet
oSheet:PageSetup:Orientation := nOrientation
DC_DbGoTop()
nRow := 1
// Feed in the data from the table to the Cells
// of the sheet.
aStru := dbStruct()
IF Valtype(aFields) == 'A'
aStru2 := AClone(aStru)
aStru := Array(0)
FOR i := 1 TO Len(aFields)
cFieldName := Upper(Alltrim(aFields[i]))
nFound := AScan(aStru2,{|a|Upper(a[1])==cFieldName})
IF nFound > 0
AAdd( aStru, aStru2[nFound] )
ENDIF
NEXT
ENDIF
aFields := Array(0)
FOR i := 1 TO Len(aStru)
cFieldName := aStru[i,1]
nFieldDec := astru[i,4]
IF Valtype(&(cFieldName)) $ 'NF'
IF nFieldDec == 0
cFormat := '0'
ELSE
cFormat := '0.' + Repl('0',nFieldDec)
ENDIF
ELSEIF Valtype(&(cFieldName)) == 'D'
cFormat := 'd/m/yyyy;@'
ELSE
cFormat := ''
ENDIF
AAdd(aFields,{cFieldName,cFieldName,cFormat})
NEXT
FOR i := 1 TO Len(aFields)
IF Len(aFields[i]) < 3
ASize(aFields[i],3)
ENDIF
cHeader := aFields[i,2]
IF !Empty(cHeader)
oSheet:Cells(nRow,i):Value := cHeader
ENDIF
cFormat := aFields[i,3]
IF !Empty(cFormat)
oSheet:Columns(i):NumberFormat := cFormat
ENDIF
NEXT
aRow := Array(Len(aStru))
aData := Array(0)
cColumns := Get_Excel_Column_ID(Len(aRow))
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
cRange := 'A3:' + cColumns + Ltrim(Str(nRow-1))
oDlg:destroy()
oSheet:Range(cRange):Value := aData
// Force a reformat for the size of the first column
IF lAutoFit
FOR i := 1 TO Len(aFields)
oSheet:Columns(i):AutoFit()
NEXT
ENDIF
bError := ErrorBlock( {|e| Break(e) } )
BEGIN SEQUENCE
// Save workbook as ordinary excel file.
oBook:SaveAs(cExcelFile,xlWorkbookNormal)
RECOVER
END SEQUENCE
ErrorBlock(bError)
oSheet:destroy()
oBook:close()
oBook:destroy()
// Quit Excel
oExcel:Quit()
oExcel:Destroy()
RETURN .t.