Re: How do I convert CSV = > Excel in Alaska?
Posted: Mon May 18, 2020 12:26 am
Hi,
This is my function to save an array to XLS. I'm using blocks of 500 rows to obtain better speed.
This is my function to save an array to XLS. I'm using blocks of 500 rows to obtain better speed.
Code: Select all
function exportXLS(aColTitles, aData, cHeader, cSubHeader, cType, lMsg, lAuto, cFile, oExcel, oBook, oSheet)
**********************************************************************************************************************
local i, j, nRows:=len(aData), cMsg:="", lExcelDestroy:=iif(!empty(oExcel), .F., .T.), lExcelVisible:=iif(lAuto, .F., .T.)
local nCols:=len(aData[1]), cRange:="", cColumns:="", aRow:={}, nBlokken:=0, aDataBlock:={}, nTotBreedte:=0, aOwners:={}
local nColOWNER:=0, nRowStart:=5
local xlEdgeBottom:=9, xlPortrait:=1, xlLandscape:=2, xlExcel8:=56
default cType:="X", lMsg:=.T., lAuto:=.F., cFile:=""
default oExcel:=iif(!empty(oExcel), oExcel, nil)
default oBook:=iif(!empty(oBook), oBook, nil)
default oSheet:=iif(!empty(oSheet), oSheet, nil)
if empty(oExcel)
oExcel:=CreateObject("Excel.Application")
endif
if empty(oExcel)
cMsg:="Excel not installed!"
iif(lAuto .or. !lMsg, logitem(cMsg), msgbox(cMsg))
RETURN .F.
endif
if empty(oBook) .and. !empty(oExcel)
oBook:=oExcel:Workbooks:Add()
endif
if empty(oSheet) .and. !empty(oBook)
oSheet:=oBook:ActiveSheet
endif
if !empty(nRows)
if !lAuto
DC_HourGlassOn()
endif
aSize(aRow, nCols)
for i = 1 to nCols
oSheet:cells(3,i):value := aColTitles[i]
oSheet:cells(3,i):font:bold := .T.
nTotBreedte += aColTitles[i][2]
next
if nTotBreedte < 80
oSheet:PageSetup:Orientation := xlPortrait
else
oSheet:PageSetup:Orientation := xlLandscape
endif
oSheet:PageSetup:TopMargin:=iif(!empty(cSubHeader), 45, 30)// 10 is gelijk aan 35 mm.
oSheet:PageSetup:CenterHeader:= "&B&10 " + cHeader//TODOhardcoded
if !empty(cSubHeader)
oSheet:PageSetup:CenterHeader+=chr(13)+cSubHeader
endif
oSheet:PageSetup:LeftFooter:="&B&6 "+dtoc(amain(4,1))// + dtoc(date())
oSheet:PageSetup:CenterFooter:="&B&6 &P/&N"
oSheet:PageSetup:RightFooter:="&B&6 " + amain(3,2)//TODO
for i = 1 To nRows
aRow := aData[i]
aadd(aDataBlock,aClone(aRow))
if len(aDataBlock) == 500
cColumns := Get_Excel_Column_ID(nCols)
cRange := 'A'+ alltrim(str(nRowStart+nBlokken*500,6,0))+':' + cColumns + Ltrim(Str(len(aDataBlock)+nRowStart-1+nBlokken*500))
oSheet:Range(cRange):Value := aDataBlock
asize(aDataBlock,0)
nBlokken++
endif
next
if len(aDataBlock)>0
cColumns := Get_Excel_Column_ID(nCols)
cRange := 'A'+ alltrim(str(nRowStart+nBlokken*500,6,0))+':' + cColumns + Ltrim(Str(len(aDataBlock)+nRowStart-1+nBlokken*500))
oSheet:Range(cRange):Value:=aDataBlock
endif
if !lAuto
DC_HourGlassOff()
endif
if nTotBreedte > 120
cRange:=chr(65)+alltrim(str(3))+":"+ cColumns + Ltrim(Str(len(aDataBlock)+nRowStart-1+nBlokken*500))
oSheet:Range(cRange):Borders(xlEdgeBottom):LineStyle:=1 // xlContinuous
oSheet:Range(cRange):Borders(xlEdgeBottom):Weight:=1 //xlHairline
oSheet:Range(cRange):font:size := 8
endif
for i = 1 to nCols
oSheet:Columns(i):AutoFit()
next
oExcel:ActiveWindow:SplitRow := 3
oExcel:Windows(1):FreezePanes:=.T.
do case
case cType == "P" // export to PDF
if empty(cFile)
cFile:=amain(5,2)+"\list\report"+alltrim(cHeader)+".pdf"
endif
oSheet:ExportAsFixedFormat(0, cFile)
if !lAuto
DllCall('Shell32.dll', 32,'ShellExecuteA', 0, Chr(0), amain(5,2)+'\list\report'+alltrim(cHeader)+'.pdf', Chr(0), Chr(0), 3)
oBook:Close(.F.)
endif
case !empty(cFile) // export as XLS
oSheet:SaveAs(cFile) //oSheet:SaveAs(cFile, xlExcel8)
if !lAuto
oBook:Close(.F.)
endif
otherwise
oExcel:Visible:=lExcelVisible
endcase
endif//if !empty(nRows)
if lExcelDestroy
oBook:close(.F.)
iif(valtype(oSheet) == "O", oSheet:Destroy(), nil)
iif(valtype(oBook) == "O", oBook:Destroy(), nil)
iif(valtype(oExcel) == "O", oExcel:Destroy(), nil)
oSheet := nil
oBook := nil
oExcel := nil
endif
return nil