DC_WorkArea2Excel() size limit

[Victorio]

Thank you, this is better solution, than fox2x utility.

[pedroah]

Try this, I placed notes, as best I could, I hope this help


// aHeader = Array of tittles
// aFields = Array of Fields
// Note : Need excel to work ( Tested from Excel 2003 to 2016)
PROCEDURE ExportTableToExcel( aHeader,aFields )
LOCAL oExcel
LOCAL i := 1
LOCAL j := 1
LOCAL xValorColumna
LOCAL nRegistro := ( alias() )->( Recno() )

oExcel := CreateObject( "Excel.Application" ) // Create a Excel Object

IF NIL == oExcel
RETURN
ENDIF

oExcel:Workbooks:Add // Add New Workbooks
oExcel:Application:Visible = .T. // Make Excel Vosible


// Headers
FOR i:= 1 TO len( aHeader )
oExcel:Cells(j,i):Select
oExcel:Selection:Font:Name = "Courier"
oExcel:Selection:Font:Size = 10
oExcel:Selection:Font:Bold = .T.
oExcel:Cells(j,i):Value = aHeader
NEXT

// Details
i := 1
j++

WHILE ( alias() )->( !eof() )

FOR i := 1 TO len( aHeader )
oExcel:Cells(j,1):Select
oExcel:Selection:Font:Name = "Courier"
oExcel:Selection:Font:Size = 10
xValorColumna := aFields // Field Value
oExcel:Cells(j,i):Value = &xValorColumna // Replace value in Excel
NEXT

j++
( alias() )->( dbskip() )
ENDDO


oExcel:destroy()
goto nRegistro
RETURN

[Victorio]

pedroah: thank you, I will try it.
now I modify source to generate directly DBF file, then rename dbf extension to xls and open with excel.
it is quick.
If I put change to Fox2x I do not need convert CP from ANSI to OEM to correct national characters.
It looks like it will be ok, but your function want examine too.

[Wolfgang Ciriack]

The procedure from pedroah may be a little bit slow.
It is better to set the values in excel with a range and an array.

Code: Select all

aOutput:={}
aadd(aOutput, {"No.","Date","Value"})
do while !dc_eof()
    aadd(aOutput, { kb->nr, kb->kasdat,kb->summe})
    dc_dbskip()
enddo
oExcel:= CreateObject("Excel.Application")
oExcel:Visible:=.F.
oExcel:Application:DisplayAlerts:=.F.

oBook := oExcel:Workbooks:Add()
oWorkSheet := oBook:ActiveSheet
oWorkSheet:Select()
oWorkSheet:Columns( 3 ):NumberFormat := "#.#0,00"

SetExcelValue("A1",oWorkSheet,"Title")
SetExcelValue("A3",oWorkSheet,"Title 2")
SetExcelValue("B3",oWorkSheet,ntocmonth(monat)+" "+str(jahr,4))
SetExcelValue("A6:C"+ntrim(6+len(aOutput)-1),oWorkSheet,aOutput)

oWorkSheet:Columns( 2 ):AutoFit()
 
oBook:SaveAs(d)
oExcel:Quit()
oExcel:Destroy()

[Victorio]

slow function can be problem because database has about 700000 records.

[rdonnay]

I made a small change to DC_Array2Excel() and DC_WorkArea2Excel().

If you use a .XLSX extension on the file name it will now automatically save as type xlOpenXMLWorkbook thus allowing more than 65536 rows to be saved.

Copy _DCFUNCT.PRG to your \exp20\source\dclipx folder.
Run BUILD19_SL1.BAT or BUILD20.BAT to rebuild DCLIPX.DLL.

[Victorio]

Roger : Thank you very much !
I will try.