How do I convert CSV = > Excel in Alaska?

[skiman]

Hi,

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

[Tom]

There is a "DC_Array2Excel" in _DCFUNCT.PRG (DCLIPX.DLL) of eXpress++.

[Eugene Lutsenko]

We will assume that Excel is available on all computers, and if it is not-then a reasonable message should be issued to the user.

This is from DC_Array2Excel(). It shows how to test for Excel installed.

Code: Select all

  oExcel := CreateObject("Excel.Application")
  IF Empty( oExcel )
    IF lCSVFallBack
      DCMSGBOX 'Excel is not installed. Create CSV file instead?' YESNO TO lStatus
      IF lStatus
        RETURN DC_Array2CSV(cExcelFile,aData)
      ELSE
        RETURN .f.
      ENDIF
    ELSE
      DC_WinAlert( "Excel is not installed" )
    ENDIF
    RETURN .f.
  ENDIF

Thank you, Roger, for this feature! You need it!

[skiman]

Hi Tom,

Yes, I know. I don't know if that is using blocks? This makes a big difference in speed.

We also needed some things as a title, headers, footer, possibility to save as PDF, ...

[Eugene Lutsenko]

Hi, Skiman!

Thank for the you fuction. But it also converts an array to XLS instead of CSV. Right? And CSV files are often very large and can't be completely loaded into the array, because this causes a memory error.

[skiman]

Hi Eugene,

It would be rather easy to use this to read blocks of 500 lines from the csv, and put it in XLS.

I don't know how much lines you have in your CSV, but if your files are so big, it will always take some time to convert.