Fivewin and ADO

[Jimmy]

hi,

ah, ok understand

is there a Way to find out what ADO Component are installed

[nageswaragunupudi]

now i want to "FASTEDIT" Sheet but how to configure XBROWSE and/or "ADODB.Recordset"

This is easier with Excel Range.
Let us first try with Excel Range.

Test program:

Code: Select all  Expand view

function EditExcelRange()

   local oRange   := GetExcelRange( TrueName( "test3.xlsx" ) )

   BrowseIt( oRange )

return nil

static function BrowseIt( u )

   local oWnd, oBrw

   DEFINE WINDOW oWnd TITLE "Edit ExcelRange " + FWVERSION
   @ 0,0 XBROWSE oBrw SIZE 0,0 PIXEL DATASOURCE u ;
      AUTOCOLS FASTEDIT CELL LINES NOBORDER
   WITH OBJECT oBrw
      :nEditTypes := { 1,1,1 }
      :CreateFromCode()
   END
   oWnd:oClient   := oBrw
   ACTIVATE WINDOW oWnd CENTERED

return nil

When we edit and change the values in XBrowse, the corresponding values in the Excel sheet are also immediately changed automatically.

[Jimmy]

hi

thx for Sample

but it does not work for me with "big" *.XLSx ... (work only with small Sample)

when open a *.XLSx it does appear "ok" in upper left but when CENTER all is EMPTY
it also seems not to "close" Connection so i can´t call EXCEL "direct" to open "same" *.XLSx

i was not able to change a CELL in *.XLSx even when "display" in XBROWSE

---

i wonder "how" GetExcelRange() will work as it "only" Return oRange, not Recorsd-Set

what is

Code: Select all  Expand view

  :nEditTypes := { 1,1,1 }

i "normal" knew

Code: Select all  Expand view

  :nEditTypes := EDIT_GET

so what is Array mean

btw. we NEED a Description of XBROWSE and those DATA in HELP File

---

what i´m doing now

1.) Connection -> FW_OpenADOExcelBook() -> return oCn
2.) Record-Set -> FW_OpenADOExcelSheet() -> return oRs

3.) XBROWSE oRs -> :nEditTypes := ??? -> "write" to *.XLSx

but i fail in Step 3 to "write back" ..

i have try :nEditTypes := { 1,1,1 } but it make no Difference in my Sample

can somebody help me how to configure XBROWSE when using "ADODB.Connection" and "ADODB.Recordset" to "write back"

[nageswaragunupudi]

what is
Code:
:nEditTypes := { 1,1,1 }

i "normal" knew
Code:
:nEditTypes := EDIT_GET

so what is Array mean

oBrw:anydatas := uVal is a a short cut for

Code: Select all  Expand view

AEval( oBrw:aCols, { |o| o:anydata := uVal } )
 

Note: anydatas = 'anydata' + 's' is plural of 'anydata'

oBrw:anydatas := aValues is short cut for

Code: Select all  Expand view

for n := 1 to Len( aValues )
   oBrw:oCol( n ):anydata := aValues[ n ]
next
 

where
oBrw:oCol( n ) --> Column Object whose nCreationOrder is n.
More about oBrw:oCol

Code: Select all  Expand view

oBrw:oCol( c ) // --> oCol whose header is "c"
oBrw:oCol( "anydata", uVal ) --> oCol where oCol:anydata is uVal
oBrw:oCol( {|o|cond} ) --> oCol which satisfies the condition in the codeblock
 

All the above are provided as short cuts to save program code and programmers' time.

We can also query the values of any data of all columns

Code: Select all  Expand view

aSortOrders := oBrw:cSortOrders
? aSortOrders
 

Now,
what is the difference between
oBrw:aCols[ n ] and oBrw:oCol( n ) ?

At the time of creation, both return the same column object.
But at runtime, user can shuffle the columns as he likes by hiding some columns, moving / swapping some columns.
So, when we need to refer to the 'n'th column as at the time of creation, oBrw:aCols[ n ] may point to a different column. oBrw:oCol( n ) refers to the nth column at the time of creation.
When we define some actions at runtime we should not use oBrw:aCols[ n ] but use oBrw:oCol( n ) or oBrw:oCol( cHeader )

[nageswaragunupudi]

but it does not work for me with "big" *.XLSx ... (work only with small Sample)

What 'big' size are you referring to? (rows,cols)?

[nageswaragunupudi]

i wonder "how" GetExcelRange() will work as it "only" Return oRange, not Recorsd-Set

GetExcelRange() returns oRange object of oExcel
This does not use ADO, but uses Excel OLE.
Similar to Word OLE and PowerPoint OLE.
Please study the subject of Microsoft Office OLE for better clarity.

GetExcelRange() function simplifies the programmers work by including long code of
1. Open Excel OLE object
2. oExcel:WorkBooks:Add( cBook ), if the cBook is not already open on the computer or if already used, uses the workbook object that is already open.
3. Obtains the specified sheet object. Defaults to the ActiveSheet
4. Obtains the range object. Defaults to the UsedRange.
All this work requires long lines of program code and this function simplifies the entire work.

If the user has already any Excel file open on the desktop, calling GetExcelRange() without any parameters, returns the used range of the active sheet of the book already open on the computer.

[nageswaragunupudi]

Code: Select all  Expand view

what i´m doing now

1.) Connection -> FW_OpenADOExcelBook() -> return oCn
2.) Record-Set -> FW_OpenADOExcelSheet() -> return oRs
 

Step (1) is not necessary because Step(2) automatically opens the ADO connection.

[nageswaragunupudi]

ExcelRange Vs ADO

Use ExceRange for reasonable sized ranges. The data need not be organized like a table, can contain formulas and can be or ragged size.
Planned improvements in future: Will soon provide editing excel formulas in xbrowse itself.

Use ADO for organized larger tables. We can use for "Named" tables also if the Excel File has named tables.
In other cases, the data in the table should be organized like a table. Same number of columns in all rows and continuous rows, with same data types.

In both cases, modifications made in the XBrowse are properly written to the underlying Excel File.

[Jimmy]

hi,

thx for all those Information

the *.XLSx was about 17000 x 11

if GetExcelRange() is based on EXCEL than i can´t use it
i want to build a Tool which can "read/write" *.XLSx without EXCEL

but i must install ADO "Pack" ( Microsoft.ACE.OLEDB.12.0 ) else FW_OpenADOExcelSheet() will fail
so here my Version to "read/write" *.XLSx using FiveWin XBROWSE

Code: Select all  Expand view

FUNCTION ADOsheet( cFile )
LOCAL cTitle := cFile
LOCAL objRS, oBrw, jj, aEdit := {}
LOCAL cSheet, cRange, lHeaders := .T.
LOCAL oFont, oDlg

   objRS := FW_OpenADOExcelSheet( cFile, cSheet, cRange, lHeaders )

   FOR jj := 0 TO objRS:Fields:Count - 1  // FIELDs are zero-based
      AADD(aEdit, EDIT_GET)
   NEXT

   SET CENTURY ON
   SET DATE GERMAN

   DEFINE FONT oFont NAME "Segoe UI" SIZE 0, - 18
      ACTIVATE FONT oFont
   END FONT

   DEFINE DIALOG oDlg SIZE 1280, 1024 PIXEL FONT oFont TITLE cTitle COLOR BFcolor, BGcolor ;
         STYLE nOR( DS_MODALFRAME, WS_POPUP, WS_CAPTION, WS_SYSMENU, WS_MAXIMIZEBOX, WS_MINIMIZEBOX, WS_THICKFRAME )

      @  1,  1 XBROWSE oBrw SIZE - 1, - 1 PIXEL OF oDlg ;
              RECORDSET objRS ;
              AUTOCOLS ;
              CELL LINES NOBORDER FASTEDIT UPDATE ;
              FONT oFont COLOR BFcolor, BGcolor

      oBrw:nEditTypes := aEdit

      oBrw:CreateFromCode()

   END DIALOG

   ACTIVATE DIALOG oDlg CENTERED

   objRS:Close()
   RELEASE FONT oFont

RETURN nil

CODE run fine using BCC7 32 Bit but with MSVC 64 Bit XBROWSE is not "refresh" after "edit"

---

some more Question

how do i get active ROW / COL where Cursor are ?

when using FASTEDIT i got a "big" Input Windows ... how to get "Inline Edit" ?
can i use oBrowse:EditSource() with Record-Set ? ( Header as "Structure" ? )
can i "seek" in XBROWSE when use a Record-Set ?

[nageswaragunupudi]

This is not necessary:

Code: Select all  Expand view

  FOR jj := 0 TO objRS:Fields:Count - 1  // FIELDs are zero-based
      AADD(aEdit, EDIT_GET)
   NEXT
//
//
      oBrw:nEditTypes := aEdit
 

This single line is enough:

Code: Select all  Expand view

oBrw:nEditTypes := EDIT_GET
 

[nageswaragunupudi]

want to build a Tool which can "read/write" *.XLSx without EXCEL
but i must install ADO "Pack" ( Microsoft.ACE.OLEDB.12.0 ) else FW_OpenADOExcelSheet() will fail

You want to run your software on PCs where Office is not installed. For this reason, you want to use ADO.
That is fine.
If we confine our application to "*.xls" files only, this is very simple. With Jet OLEDB that is installed by default on alll PCs your application will run fine without any problems at all on all PCs whether Office is installed or not.

But "*.xlsx" requires installation of ACE.OLEDB.
Our program does not run on all PCs, unless all our clients install ACE OLEDB.
Here we face other kind of problems.

CODE run fine using BCC7 32 Bit but with MSVC 64 Bit XBROWSE is not "refresh" after "edit"

ACE OLEDB installed on the target computer can be 32-bits version or 64-bits version.
If our application is 32bits, it can use only ACE 32-bit provider only.
To use 64-bit ACE provider, we need to build our application in 64-bits.
So which application we distribute? Our 32/64 bit application?
So, think about it.
In real life, this is not so simple.

[nageswaragunupudi]

how to get "Inline Edit?

"Inline Edit" is the inbuilt way of editing XBrowse cells. This is available at all times.
The programmer does not have to do anything more than setting oCol:nEditType > 0 (usually EDIT_GET.

if
oCol:nEditType is > 0 and
oCol:lReadOnly is .F. ( default ) and
oCol:bEditWhen is NIL (default ) or Evaluates to .T.
Inline Edit of the cell is automatically available (whether oBrw:lFastEdit is .T. or .F. )

When the user presses Enter Key on a cell or double clicks on a cell, Inline Edit of the cell is automatically invoked. There is nothing the programmer needs to do for this.

FASTEDIT. ( oBrw:lFastEdit is .T.)
This is the only difference. If the oBrw:lFastEdit is .F. (default), user needs to press Enter key to invoke Inline Edit.
If oBrw:lFastEdit is set to .T., if the user presses any (acceptable) Key, the InlineEdit is invoked treating the key pressed as the first key of the Get object.
Acceptable key is alpha-numeric key for cells with character value and numeric key of numeric value.
This the ONLY difference between FastEdit and no FastEdit

Behavior of XBrowse is very similar to Excel sheet editing.

Methods oBrw:Edit( lAppend ) and oBrw:EditSource( lAppend ) are provided if the programmer wants to provide edit of the Row in a dialog form. These methods use TDataRow/FW_Record class.

can i use oBrowse:EditSource() with Record-Set ? ( Header as "Structure" ? )

Yes.
This method works with any datasource, eg. dbf, tdatabase, ado recordset, tmysql, dolphin, fwhmysql or any othe datasource with identical appearance and behavior.

can i "seek" in XBROWSE when use a Record-Set ?

Yes.
For that matter any datasource.

how do i get active ROW / COL where Cursor are ?

oBrw:nRowSel
oBrw:nColSel
But you rarely need this information.

[Jimmy]

hi,

This is not necessary:
This single line is enough:

Code: Select all  Expand view

oBrw:nEditTypes := EDIT_GET
 

ah, ok

---

as you wrote {1,1,1}, which work "only" on Column 1 - 3, i thought it mus be a Array for each Column

[Jimmy]

hi,

ACE OLEDB installed on the target computer can be 32-bits version or 64-bits version.
If our application is 32bits, it can use only ACE 32-bit provider only.
To use 64-bit ACE provider, we need to build our application in 64-bits.
So which application we distribute? Our 32/64 bit application?

on 32 Bit OS i use ADO 32 Bit and BCC7 32 Bit and XBNROWSE "refresh" fine after "edit"
on 64 Bit OS i use ADO 64 Bit and MSVC 64 Bit and XBNROWSE do NOT "refresh" after "edit"

so what i´m doing wrong

---

have found out that "Input Window" is when have Type "C"
when all Column are Type "C" than it does not "refresh" under 64 Bit

[Jimmy]

hi,

if
oCol:nEditType is > 0 and
oCol:lReadOnly is .F. ( default ) and
oCol:bEditWhen is NIL (default ) or Evaluates to .T.
Inline Edit of the cell is automatically available (whether oBrw:lFastEdit is .T. or .F. )

you talk about oCol but i use oBro ...

Code: Select all  Expand view

  oBrw:nEditTypes := EDIT_GET    // work

   oBrw:lReadOnly := .F.         // no Effect ?
   oBrw:bEditWhen := {|| .T. }   // no Effect ?

   oBrw:CreateFromCode()

how to change to get "Inline edit" instead of "Input Window"

p.s. all those useful Information MUST be in Help File special when we need "combination" of Option