xbrowse and Excel

[Silvio.Falconi]

I wish use the comand oBrowse:ToExcel
but I wish select some columns not all
then when the excel is created the oBrowse must be as I had
any solution please

[cnavarro]

From wiki
http://wiki.fivetechsoft.com/doku.php?i ... s_txbrowse

ToExcel XLS,PDF ( [bProgress], [nGroupBy], [aCols], [lShow], [cPDF], [bPrePDF] ) –> oSheet
Exports contents of the browse to a new Excel Sheet and returns the sheet object.

If Excel is not installed and OpenOffice is installed, the call is transferred to ::ToCalc() using bProgress, nGroupBy and aCols.

Parameters:
1. [bProgress]: By default, progress is shown in tex form on oBrw:oWnd:oMsgBar if exists. If bProgress bar is specified, the same is used with parameters nRowsExported, nTotal
2. [nGroupBy]:If specified, this number indicates the column number of the exported columns. If any columns have totals, the exported sheet is grouped by nGroupBy column and subtotals displayed. For this purpose, Excel’s subtotal method is used so that the results can be used for futher computations.
3. [aCols]: By default, all visible columns are exported in that order. See ::GetVisibleCols(). If aCols is specified, only the specified columns are exported, in that order. aCols is an array of ordinal positions in oBrw:aCols array.
4. [lShow]: Default .T. and shows the result of export. i.e., Excel Sheet or PDF as the case may be.
5. [cPDF]: Optional. Name of PDF file to be exported. If specified, the exported Excel sheet is saved as PDF. This PDF contains table with contents as text for futher use

[Silvio.Falconi]

exactly I know this well.
But I had asked for something else
I have a customer table with specific columns
I want to create a file for excel by selecting some columns not all columns of xbrowse
So I select the xbrowse columns from the header and then call the ToExcel() function. and it run ok

Problem when I return on my xbrowse : I no longer have the columns I had before but only the ones I selected.
How do I automatically get back the same configuration that I had before selecting columns for Excel?

[Marc Venken]

Silvio,

Maybe this can help you ? I don't know if you allready select the coloms this way.

viewtopic.php?f=3&t=34058&hilit=select+colums+in+xbrowse

If you put this code after the creation of the exel file, the colums are back.

AEval( oBrw:aCols, { |o| o:lHide := .f. } )
oBrw:refresh()

[nageswaragunupudi]

(1) If your program knows which columns are to be exported then you adopt this method.
Assume you want columns 5, 4, 2 to be exported.

Code: Select all  Expand view  RUN

oBrw:ToExcel( nil, nil, { oBrw:oCol( 5 ), oBrw:oCol( 4 ), oBrw:oCol( 2 ) } )
 

Your xbrowse is not changed at all.

(2) If you want your user to select the columns at runtime to be exported.
Initially save the state of xbrowse as cState := oBrw:SaveState()

After the user selects the columns and calls oBrw:ToExcel(), then call oBrw:RestoreState( cState )

[Silvio.Falconi]

Rao,
I'm Working on your function XbrColSelector( oBrowse )
it can run ok only I modified with

Code: Select all  Expand view  RUN

function XbrColSelector( oBrowse )

   local oDlg, oBrw
   local aSave    := aCopy( oBrowse:aCols )
   Local cSaveState:= oBrowse:SaveState()

   DEFINE DIALOG oDlg SIZE 300,400 PIXEL TRUEPIXEL

   @ 60,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oBrowse:aCols ;
      COLUMNS "lHide", "cHeader" ;
      HEADERS "", "Header" ;
      COLSIZES 40, 100 ;
      CELL LINES NOBORDER

   WITH OBJECT oBrw
      :nStretchCol   := 2
      WITH OBJECT :aCols[ 1 ]
         :bEditValue    := { |x| If( x == nil, !oBrw:aRow:lHide, oBrw:aRow:lHide := !x ) }
         :SetCheck( nil, .t. )
         :nHeadBmpNo    := 2
      END
      :CreateFromCode()
   END
   @ 20,10 BTNBMP PROMPT "Go to Excel " SIZE 100,30 FLAT PIXEL OF oDlg ACTION oBrowse:ToExcel()
   @ 20,180 BTNBMP PROMPT "Return" SIZE 100,30 FLAT PIXEL OF oDlg ACTION (oBrowse:RestoreState( cSaveState ) , oDlg:end() )

   ACTIVATE DIALOG oDlg CENTERED

   oBrowse:Refresh()

return nil

See cSaveState

[nageswaragunupudi]

As we explained above, it is not necessary to Hide/Unhide columns for exporting to Excel. You need to have modified my example to suit the present purpose.

Here is the modified function:

Code: Select all  Expand view  RUN

function ExportToExcel( oBrowse )

   local aCols    := {}
   local oDlg, oBrw

   AEval( oBrowse:aCols, { |o,i| AAdd( aCols, { !o:lHide, i, o:cHeader } ) } )

   DEFINE DIALOG oDlg SIZE 300,400 PIXEL TRUEPIXEL

   @ 60,20 XBROWSE oBrw SIZE -20,-40 PIXEL OF oDlg ;
      DATASOURCE aCols ;
      COLUMNS 1,2,3 ;
      HEADERS "", "No", "Header" ;
      COLSIZES 40, 40, 100 ;
      CELL LINES NOBORDER

   WITH OBJECT oBrw
      :nStretchCol   := STRETCHCOL_WIDEST
      WITH OBJECT :aCols[ 1 ]
         :SetCheck( nil, .t. )
         :nHeadBmpNo    := 2
      END
      :CreateFromCode()
   END

   @ 20,10 BTNBMP PROMPT "ToExcel" SIZE 100,30 FLAT PIXEL OF oDlg ;
      ACTION ( aCols := {}, ;
               AEval( oBrw:aArrayData, { |a| If( a[ 1 ], AAdd( aCols, oBrowse:aCols[ a[ 2 ] ] ), nil ) } ), ;
               oBrowse:ToExcel( nil, nil, aCols ) ;
             )

   @ 20,180 BTNBMP PROMPT "Close" SIZE 100,30 FLAT PIXEL OF oDlg ACTION oDlg:End()

   @ 370,020 SAY "Ctrl-Up/Ctrl-Dn to Swap Rows Up/Down" SIZE 260,20 PIXEL OF oDlg ;
      CENTER VCENTER COLOR CLR_HRED, oDlg:nClrPane

   ACTIVATE DIALOG oDlg CENTERED

return nil

User can select which columns are to be exported and also the order in which they are to be exported.

[Silvio.Falconi]

thanks

[Marc Venken]

Mr. Rao,

Could you please find some time to enhance this function with a save/restore button where the state off a selected colums is saved?

This would be a great tool for setup new xbrowses when develloping. We could open all columns, select some and some more and finaly we could code the needed colums.

Thanks in advance.