bb.donnay-software.com

Donnay Software Web Forums
It is currently Wed Aug 12, 2020 2:22 am

All times are UTC - 7 hours




Post new topic Reply to topic  [ 26 posts ]  Go to page Previous  1, 2, 3
Author Message
PostPosted: Mon May 18, 2020 12:26 am 
Offline

Joined: Thu Jan 28, 2010 1:22 am
Posts: 864
Location: Sijsele, Belgium
Hi,

This is my function to save an array to XLS. I'm using blocks of 500 rows to obtain better speed.

Code:
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

_________________
Best regards,

Chris.
www.aboservice.be


Top
 Profile  
 
PostPosted: Mon May 18, 2020 12:46 am 
Offline
User avatar

Joined: Thu Jan 28, 2010 12:59 am
Posts: 789
Location: Berlin, Germany
There is a "DC_Array2Excel" in _DCFUNCT.PRG (DCLIPX.DLL) of eXpress++. 8-)

_________________
Best regards,
Tom

"Did I offend you?"
"No."
"Okay, give me a second chance."


Top
 Profile  
 
PostPosted: Mon May 18, 2020 1:39 am 
Offline
User avatar

Joined: Sat Feb 04, 2012 2:23 am
Posts: 1349
Location: Russia, Southern federal district, city of Krasnodar
rdonnay wrote:
Quote:
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:
  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!

_________________
http://lc.kubagro.ru/
https://www.researchgate.net/profile/Eugene_Lutsenko
http://ej.kubagro.ru/
http://ej.kubagro.ru/a/viewaut.asp?id=11


Top
 Profile  
 
PostPosted: Mon May 18, 2020 1:39 am 
Offline

Joined: Thu Jan 28, 2010 1:22 am
Posts: 864
Location: Sijsele, Belgium
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, ...

_________________
Best regards,

Chris.
www.aboservice.be


Top
 Profile  
 
PostPosted: Mon May 18, 2020 1:59 am 
Offline
User avatar

Joined: Sat Feb 04, 2012 2:23 am
Posts: 1349
Location: Russia, Southern federal district, city of Krasnodar
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.

_________________
http://lc.kubagro.ru/
https://www.researchgate.net/profile/Eugene_Lutsenko
http://ej.kubagro.ru/
http://ej.kubagro.ru/a/viewaut.asp?id=11


Top
 Profile  
 
PostPosted: Mon May 18, 2020 4:49 am 
Offline

Joined: Thu Jan 28, 2010 1:22 am
Posts: 864
Location: Sijsele, Belgium
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.

_________________
Best regards,

Chris.
www.aboservice.be


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 26 posts ]  Go to page Previous  1, 2, 3

All times are UTC - 7 hours


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group