[Module] GridExModule.pbi

[Thorsten1867]

Extended Grid Gadget Module

Extended grid gadget (editable & resize / hide / freeze columns & rows | align / enclose text)
GridExModule is based on MyGrid from' said'. I reworked it and converted it into a module.

• Various cell types: Normal / Combobox / Checkbox / Button / Image
• Various cell formats: String / Integer / Float / Money / Date / Time / Grades
• Cells (Normal / ComboBox) can be edited.
• Autocomplete for editable cells available
• Popup menu for Grid
• Cells with the mouse enlarge/reduce & autobroad width/height at header click
• Allow or forbid to change the size of the column and row
• Freeze rows/columns (not scrolled)
• Sorting row ranges (also in German) & sorting by clicking on column header
• Multiple sorting possible (e. g. last name, first name)
• Formatting the different cell formats.
• Mark(colored) cell contents if they fulfill a condition.
• Checking the validity of entries for editable cells (e.g. Date, Float, ....)
• Accept only valid entries for editable cells, if requested
• Cells with calculations for other cell contents (simple spreadsheet calculation)
• Export of cell contents as CSV
• Copy selected cells to/from ClipBoard (CSV or Tabulator)
• Save/load the changed column/row size and grid size
• Support of themes for grid design (default & user)
• Load/Save grid as worksheet (including the used images)

Download: GridExModule.pbi (with example worksheet)

[skywalk]

Really impressive work.
My request is an example that demonstrates copying the contents of a selection of 1 or more rows x columns into the clipboard or just to the debug window as csv text?
And vice versa.
Paste some csv text from the clipboard to a selection of rows x columns.
Thanks.

[Andre]

Like I already wrote in the related topic on German PB forum: very good work, thank you very much!

@skywalk: +1 (I would be interested too)

[loulou2522]

Thanks for this excellent module.
Is-it possible to have autocomplete for a column you choose ?
Have a nice day

[Thorsten1867]

Added: GetSelectedCells(GID.i, Array Cells.s(2))

After the original code didn't allow for evaluating selections, I added a command to read the selected cells into an array. (see example 1)

[Kwai chang caine]

Very very useful code so much missing at PB
Thanks a lot at SAID and you for sharing this jewel

Have you planned to resize the columns and rows with mouse, and/or better,.... automatic adjust them with double clic ?
And image insert in a cell ?

[skywalk]

Very nice. I added the header row to Cells(). Not sure if you have another method to grab the associated header columns?

Code: Select all

  Procedure   GetSelectedCells(GID.i, Array Cells.s(2))
    Define gid$ = Str(GID)
    Define.i r, c, Row1, Row2, Col1, Col2, idxR=0, idxC=0
    If IsSelected(GID)
      
      Row1 = Grid(gid$)\Row\Current
      Row2 = Grid(gid$)\Block\Row2
      If Row1 > Row2
        Row1 = Grid(gid$)\Block\Row2
        Row2 = Grid(gid$)\Row\Current
      EndIf
      
      Col1 = Grid(gid$)\Col\Current
      Col2 = Grid(gid$)\Block\Col2
      If Col1 > Col2
        Col1 = Grid(gid$)\Block\Col2
        Col2 = Grid(gid$)\Col\Current
      EndIf
      ;//////////////////////////////////////////////////
      ; Prepend Header Row to Cells()
      ;//////////////////////////////////////////////////
      Dim Cells.s(Row2-Row1+1, Col2-Col1)
      r = 0
      idxC = 0 
      For c = Col1 To Col2
        Cells(idxR, idxC) = Grid(gid$)\Col\Label(c)\Value
        idxC + 1
      Next c
      idxR + 1
      ;//////////////////////////////////////////////////
      For r = Row1 To Row2
        idxC = 0 
        For c = Col1 To Col2
          Cells(idxR, idxC) = Grid(gid$)\Rows(r)\Cell(c)\Value
          idxC + 1
        Next c
        idxR + 1
      Next r
    EndIf
    
  EndProcedure  

[Thorsten1867]

Updated ...

Code: Select all

  Procedure  GetSelectedCells(GID.i, Array Cells.s(2), HeaderRow=#False)
    Define gid$ = Str(GID)
    Define.i r, c, Row1, Row2, Col1, Col2, idxR=0, idxC=0
    
    If IsSelected(GID)
      
      Row1 = Grid(gid$)\Row\Current
      Row2 = Grid(gid$)\Block\Row2
      If Row1 > Row2
        Row1 = Grid(gid$)\Block\Row2
        Row2 = Grid(gid$)\Row\Current
      EndIf
      
      Col1 = Grid(gid$)\Col\Current
      Col2 = Grid(gid$)\Block\Col2
      If Col1 > Col2
        Col1 = Grid(gid$)\Block\Col2
        Col2 = Grid(gid$)\Col\Current
      EndIf
      
      If HeaderRow
        Dim Cells.s(Row2-Row1+1, Col2-Col1)
        idxC = 0 
        For c = Col1 To Col2
          Cells(idxR, idxC) = Grid(gid$)\Col\Label(c)\Value
          idxC + 1
        Next
        idxR + 1
      Else
        Dim Cells.s(Row2-Row1, Col2-Col1)
      EndIf
      
      For r = Row1 To Row2
        idxC = 0 
        For c = Col1 To Col2
          Cells(idxR, idxC) = Grid(gid$)\Rows(r)\Cell(c)\Value
          idxC + 1
        Next c
        idxR + 1
      Next r
    EndIf
    
  EndProcedure  

[Thorsten1867]

Added: AutoComplete for editable cells (list of matching words)
Test it with "Te..." in example 2.

Added: CopyToClipboard(GID.i, Flags.i)
Added: ExportCells() - Export Grid to File (CSV)

[said]

Nice additions Thorsten1867 ... i like your approach to implement the calculations

Due to the amount of changes, I wont be able to add most of the new additions ...
However, looks like few glitches were introduced with your heavy re-work e.g. i am no longer able to resize a row or col, or when i scroll down and then click on a cell the grid scrolls to the top on its own ...

I hope i will be able to upload the latest version of MyGrid within few days (i am adopting it now to 5.61), i had improved some core routines that you can probably make use of in your implementation (by the way since long time now it has the sort implemented, both single or multi columns not rows) ... Good continuation

Said

[Thorsten1867]

However, looks like few glitches were introduced with your heavy re-work e.g. i am no longer able to resize a row or col, or when i scroll down and then click on a cell the grid scrolls to the top on its own .

-> Bugs fixed

[skywalk]

This shared file or folder link has been removed or is unavailable to you.
Think it's a mistake? No worries: Just email the owner or get in touch with Box support. We're here to help.

EDIT: download works now...

[IndigoFuzz]

File down

[Thorsten1867]

Added: Load/Save Grid size & rows height and colums width

[USCode]

Is this expected to work on the Mac?
Using PB 5.61 x64, getting an "Invalid memory access" error on line 5603:

Code: Select all

Grid(gid$)\Rows(r)\Height = Size\Rows(r)