bb.donnay-software.com

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