Liberty BASIC Community Forum
« Data Grid / Spreadsheet »

Welcome Guest. Please Login or Register.
Oct 24th, 2014, 06:26am


Rules|Home|Help|Search|Recent Posts|Notification


« Previous Topic | Next Topic »
Pages: 1  Notify Send Topic Print
 thread  Author  Topic: Data Grid / Spreadsheet  (Read 361 times)
Rod
Global Moderator
ImageImageImageImageImage


member is online

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 4233
xx Data Grid / Spreadsheet
« Thread started on: Sep 21st, 2013, 11:24am »

This has been discussed on the Yahoo board. Richard has been toying with code originally crafted by Dan that I tweaked a little and added some rem statements..

I thought I'd post it here so that Richard can respond to any questions. He has a version that uses a hidden default button that might be worth posting.

Code:
'Original code by KCDan with help from Janet and Brent.
'LOCATE and scrolling added by Richard Russell
 nomainwin

 'set up a 5x150 cell sheet
 numCols=5 'x
 numRows=150 'y
 cellW=120 'cell width in pixels
 cellH=25 'cell height in pixels
 cellColor$="black"
 cellBackcolor$="white"
 dim cell$(numCols,numRows)

 'fill the sheet's array with data
 for C=1 to numCols
 for R=1 to numRows
 cell$(C,R)=str$(C)+":"+str$(R)
 next R
 next C

 'textbox offsets in cell
 txtW=95
 txtH=25
 txtX=-81
 txtY=-26

 'checkbox offsets in cell
 okW=20
 okH=24
 okX=-25
 okY=-25

 'open a graphicbox inside a window_nf but first
 'calculate how much space Windows will take
 'Anatoly's tip
 WindowWidth = 200
 WindowHeight = 200
 open "Adjusting..." for graphics_nf_nsb as #1
 #1 "home ; down ; posxy w h"
 themeW=200-2*w : themeH = 200-2*h
 'themeW and themeH now contain the number of pixels
 'the Windows theme takes out of the graphics area
 close #1

 WindowWidth =numCols*cellW+themeW
 WindowHeight=10*cellH+themeH
 UpperLeftX=(DisplayWidth-WindowWidth)/2
 UpperLeftY=(DisplayHeight-WindowHeight)/2
 maxscroll = numRows*cellH - WindowHeight + themeH

 graphicbox #main.g,0,0,numCols*cellW+2,WindowHeight-themeH
 checkbox #main.ok,"",[okclicked],[okclicked],okX,okY,okW,okH
 textbox #main.txt, txtX, txtY, txtW, txtH
 open "SpreadSheet" for window_nf as #main

 #main "font ms_sans_serif 10 bold"
 #main "trapclose [quit]"
 #main.g "down;fill buttonface;flush;vertscrollbar on 0 "; maxscroll
 #main.g "when leftButtonMove [cellclicked]"
 #main.g "when leftButtonDown [cellclicked]"
 goto [redraw]
 wait

 [cellclicked]
 'capture the cell coordinates and set the
 'textbox and checkbox offsets
 'then refresh to draw the controls at the cell location
 hw = hwnd(#main.g)
 calldll #user32, "EnableScrollBar", hw as ulong, _
 _SB_VERT as long, _ESB_DISABLE_BOTH as long, ret as long
 calldll #user32, "GetScrollPos", hw as ulong, _
 _SB_VERT as long, vscroll as long
 curx=int(MouseX/cellW)+1
 cury=int(MouseY/cellH)+1
 if curx<1 or curx>numCols then curx=0
 if cury<1 or cury>numRows then cury=0
 txtX=(curx-1)*cellW+1
 txtY=(cury-1)*cellH+1 - vscroll
 okX=(curx-1)*cellW+100
 okY=(cury-1)*cellH+2 - vscroll
 #main.txt "!locate ";txtX;" ";txtY;" ";txtW;" ";txtH
 #main.ok "locate ";okX;" ";okY;" ";okW;" ";okH
 #main "refresh"
 #main.txt cell$(curx,cury)
 #main.txt "!setfocus"
 goto [redraw]

 [okclicked]
 'move the text and checkbox controls off screen and save the changes
 hw = hwnd(#main.g)
 calldll #user32, "EnableScrollBar", hw as ulong, _
 _SB_VERT as long, _ESB_ENABLE_BOTH as long, ret as long
 txtX=-100
 txtY=-100
 okX=-100
 okY=-100
 #main.txt "!locate ";txtX;" ";txtY;" ";txtW;" ";txtH
 #main.ok "locate ";okX;" ";okY;" ";okW;" ";okH
 #main "refresh"
 #main.txt "!contents? t$"
 cell$(curx,cury)=t$
 #main.txt ""
 #main.ok "reset"

 [redraw]
 'delete the last segment, redraw all cells and flush
 #main.g "delsegment t"
 #main.g "color ";cellColor$;";backcolor ";cellBackcolor$
 for C=1 to numCols
 for R=1 to numRows
 #main.g "place ";(C-1)*cellW;" ";(R-1)*cellH
 #main.g "boxfilled ";C*cellW+1;" ";R*cellH+1
 #main.g "place ";(C-1)*cellW+2;" ";((R-1)*cellH)+18;";|";cell$(C,R)
 next R
 next C
 #main.g "flush t"
 wait

 [quit]
 close #main
 end

 


User IP Logged

RichardRussell
Guest
xx Re: Data Grid / Spreadsheet
« Reply #1 on: Sep 21st, 2013, 5:49pm »

on Sep 21st, 2013, 11:24am, Rod wrote:
He has a version that uses a hidden default button that might be worth posting.

All I did was change the checkbox to an offscreen default pushbutton to trap pressing the Enter key. Here is a version with that modification; it also supports both horizontal and vertical scrolling:

Code:
    'Original code by KCDan with help from Janet and Brent.
    'Tweaked and comments added by Rod Bird.
    'Locate, scrolling & Enter key mods by Richard Russell.
    nomainwin

    'set up a 10x50 cell sheet
    numCols=10  'x
    numRows=50  'y
    cellW=120   'cell width in pixels
    cellH=25    'cell height in pixels
    cellColor$="black"
    cellBackcolor$="white"
    dim cell$(numCols,numRows)

    'fill the sheet's array with data
    for C=1 to numCols
        for R=1 to numRows
            cell$(C,R)=str$(C)+":"+str$(R)
        next R
    next C

    'open a graphicbox inside a window_nf but first
    'calculate how much space Windows will take
    'Anatoly's tip
    WindowWidth = 200
    WindowHeight = 200
    open "Adjusting..." for graphics_nf_nsb as #1
    #1 "home ; down ; posxy w h"
    themeW=200-2*w : themeH = 200-2*h
    'themeW and themeH now contain the number of pixels
    'the Windows theme takes out of the graphics area
    close #1

    WindowWidth =600
    WindowHeight=400
    UpperLeftX=(DisplayWidth-WindowWidth)/2
    UpperLeftY=(DisplayHeight-WindowHeight)/2
    maxhscroll = numCols*cellW - WindowWidth + themeW + 16
    maxvscroll = numRows*cellH - WindowHeight + themeH + 16

    graphicbox #main.g,0,0,WindowWidth-themeW,WindowHeight-themeH
    button #main.ok,"",[okclicked],UL,-25,-25,20,20
    stylebits #main.ok, _BS_DEFPUSHBUTTON, 0, 0, 0
    textbox #main.txt, -100, -100, cellW, cellH
    open "SpreadSheet" for window_nf as #main

    #main "font ms_sans_serif 10 bold"
    #main "trapclose [quit]"
    #main.g "down;fill buttonface;flush"
    #main.g "horizscrollbar on 0 "; maxhscroll
    #main.g "vertscrollbar on 0 "; maxvscroll
    #main.g "when leftButtonMove [cellclicked]"
    #main.g "when leftButtonDown [cellclicked]"
    goto [redraw]
    wait

    [cellclicked]
    'capture the cell coordinates and set the textbox offsets
    'then refresh to draw the textbox at the cell location
    hw = hwnd(#main.g)
    calldll #user32, "EnableScrollBar", hw as ulong, _
        _SB_HORZ as long, _ESB_DISABLE_BOTH as long, ret as long
    calldll #user32, "EnableScrollBar", hw as ulong, _
        _SB_VERT as long, _ESB_DISABLE_BOTH as long, ret as long
    calldll #user32, "GetScrollPos", hw as ulong, _
        _SB_HORZ as long, hscroll as long
    calldll #user32, "GetScrollPos", hw as ulong, _
        _SB_VERT as long, vscroll as long
    curx=int(MouseX/cellW)+1
    cury=int(MouseY/cellH)+1
    if curx<1 or curx>numCols then curx=0
    if cury<1 or cury>numRows then cury=0
    txtX=(curx-1)*cellW+1 - hscroll
    txtY=(cury-1)*cellH+1 - vscroll
    #main.txt "!locate ";txtX;" ";txtY;" ";cellW;" ";cellH
    #main "refresh"
    #main.txt cell$(curx,cury)
    #main.txt "!setfocus"
    goto [redraw]

    [okclicked]
    'move the textbox off screen and save the changes
    hw = hwnd(#main.g)
    calldll #user32, "EnableScrollBar", hw as ulong, _
        _SB_HORZ as long, _ESB_ENABLE_BOTH as long, ret as long
    calldll #user32, "EnableScrollBar", hw as ulong, _
        _SB_VERT as long, _ESB_ENABLE_BOTH as long, ret as long
    txtX=-100
    txtY=-100
    #main.txt "!locate ";txtX;" ";txtY;" ";cellW;" ";cellH
    #main "refresh"
    #main.txt "!contents? t$"
    cell$(curx,cury)=t$
    #main.txt ""

    [redraw]
    'delete the last segment, redraw all cells and flush
    #main.g "delsegment t"
    #main.g "color ";cellColor$;";backcolor ";cellBackcolor$
    for C=1 to numCols
        for R=1 to numRows
            #main.g "place ";(C-1)*cellW;" ";(R-1)*cellH
            #main.g "boxfilled ";C*cellW+1;" ";R*cellH+1
            #main.g "place ";(C-1)*cellW+2;" ";((R-1)*cellH)+18;";|";cell$(C,R)
        next R
    next C
    #main.g "flush t"
    wait

    [quit]
    close #main
    end 

Richard.
« Last Edit: Sep 21st, 2013, 5:55pm by RichardRussell » User IP Logged

CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 271
xx Re: Data Grid / Spreadsheet
« Reply #2 on: Sep 22nd, 2013, 09:03am »

This code crashes if you change NumRows and NumCols such as 5 x 5.
User IP Logged

.....
RichardRussell
Guest
xx Re: Data Grid / Spreadsheet
« Reply #3 on: Sep 22nd, 2013, 09:46am »

on Sep 22nd, 2013, 09:03am, CryptoMan wrote:
This code crashes if you change NumRows and NumCols such as 5 x 5.

It doesn't 'crash', but LB reports an error because the scrollbars aren't required and it doesn't like receiving negative values for the 'horizscrollbar' or 'vertscrollbar' commands.

If your spreadsheet is so small that it doesn't require scrolling you are probably best to use the original, simpler, program. But if you want to make the code bomb-proof you can test for maxhscroll or maxvscroll being negative:

Code:
    if maxhscroll > 0 then #main.g "horizscrollbar on 0 "; maxhscroll
    if maxvscroll > 0 then #main.g "vertscrollbar on 0 "; maxvscroll 

Richard.
User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is online

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 4233
xx Native Data Grid / Spreadsheet
« Reply #4 on: Feb 25th, 2014, 03:16am »

Added tabbing using Brad Moore's Hocus Focus lesson.

A dutch member has also added variable column width, I hope he will share.

Code:
    nomainwin

    'set up a 6x25 cell sheet
    numCols=6   'x
    numRows=25  'y
    cellW=120   'cell width in pixels
    cellH=25    'cell height in pixels
    gridW=numCols*cellW 'total pixel width needed
    gridH=numRows*cellH 'total pixel height needed
    gridX=50    'location of data grid on window
    gridY=50
    cellColor$="black"
    cellBackcolor$="lightgray"

    'fill the sheet's array with dummy data
    dim cell$(numCols,numRows)
    for C=1 to numCols
        for R=1 to numRows
            cell$(C,R)=str$(C)+":"+str$(R)
        next R
    next C

    'open a standard 800x800 window
    WindowWidth=800
    WindowHeight=800
    UpperLeftX=(DisplayWidth-WindowWidth)/2
    UpperLeftY=(DisplayHeight-WindowHeight)/2
    Margin=3
    'place a graphicbox at gridX,gridY,gridW and gridH
    graphicbox #main.g,gridX,gridY,gridW+Margin,gridH+Margin

    'set up a hidden default button
    'now if enter is pressed [okclicked] will be called
    button #main.ok,"",[okclicked],UL,-25,-25,20,20
    stylebits #main.ok, _BS_DEFPUSHBUTTON, 0, 0, 0

    'set up a hidden textbox
    'this will be relocated to intercept data input
    'when a cell is clicked
    textbox #main.txt, -100, -100, cellW, cellH


    button #main.print, "Print", [printit], LR, 105, 50
    button #main.submit, "Submit", [okclicked],LR, 50,50

    open "Data Grid" for window_nf as #main
    #main "trapclose [quit]"
    #main "font comic_sans 10 bold"
    #main.g "font comic_sans 10 bold"
    #main.g "down;fill buttonface;flush"

    #main.g "when leftButtonUp [cellclicked]"
    'find the handle of the moveable textbox and the ok button
    'so we can check which has focus
    htxt = hWnd(#main.txt)
    hok = hWnd(#main.submit)

    goto [redraw]




    [cellclicked]
    'set an editing flag and capture cell coordinates
    editing=1
    curx=int(MouseX/cellW)+1
    cury=int(MouseY/cellH)+1

    [editit]
    'set x and y to locate the textbox at the cell location
    txtX=(curx-1)*cellW+1+gridX
    txtY=(cury-1)*cellH+1+gridY
    #main.txt "!locate ";txtX;" ";txtY;" ";cellW;" ";cellH
    #main "refresh"
    #main.txt cell$(curx,cury)
    #main.txt "!setfocus"
    goto [redraw]

    [okclicked]
    'the user either pressed enter or clicked the ok button
    'set the editing flag, move the textbox off screen and save the changes
    timer 0
    editing=0
    txtX=-100
    txtY=-100
    #main.txt "!locate ";txtX;" ";txtY;" ";cellW;" ";cellH
    #main "refresh"
    #main.txt "!contents? t$"
    cell$(curx,cury)=t$


    [redraw]
    'delete the last segment, redraw all cells and flush
    #main.g "delsegment t"
    #main.g "color ";cellColor$;";backcolor ";cellBackcolor$
    for C=1 to numCols
        for R=1 to numRows
            #main.g "place ";(C-1)*cellW;" ";(R-1)*cellH
            #main.g "boxfilled ";C*cellW+1;" ";R*cellH+1
            #main.g "place ";(C-1)*cellW+8;" ";((R-1)*cellH)+17;";|";cell$(C,R)
        next R
    next C
    #main.g "flush t"

    'now if we are editing start to repeatedly check if the user
    'has moved focus by tabbing, pressing enter or
    'clicking the ok button.

    if editing then
        timer 100, [check]
        wait

        [check]
        'check if we have lost focus
        CallDLL #user32, "GetFocus", h As ulong
        'if focus is not on the textbox check if it
        'is on the ok button and branch accordingly
        if htxt<>h then
            if h=hok then goto [okclicked]
            timer 0
            'save the changes
            #main.txt "!contents? t$"
            cell$(curx,cury)=t$
            'tab to next cell
            curx=curx+1
            if curx>numCols then curx=1 : cury=cury+1
            if cury>numRows then cury=1 : curx=1
            goto [editit]
        end if
    end if
    wait



    [printit]
    timer 0
    #main.g "cls"
    'redraw all cells and flush
    #main.g "delsegment t"
    #main.g "color ";cellColor$;";backcolor ";cellBackcolor$
    for C=1 to numCols
        for R=1 to numRows
            #main.g "place ";(C-1)*cellW;" ";(R-1)*cellH
            #main.g "boxfilled ";C*cellW+1;" ";R*cellH+1
            #main.g "place ";(C-1)*cellW+8;" ";((R-1)*cellH)+17;";|";cell$(C,R)
        next R
    next C
    #main.g "flush t"
    #main.g "print ";gridW+Margin
    wait


    [quit]
    close #main
    end
  


« Last Edit: Mar 3rd, 2014, 05:39am by Rod » User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is online

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 4233
xx Re: Data Grid / Spreadsheet
« Reply #5 on: Mar 3rd, 2014, 05:35am »

C. Massar adds variable column widths, looking really neat now.


Code:
'   programname = My Grid - v3 - improved with tab key detection

    nomainwin

'   set up a DIMENSIONED cell sheet



    numCols = 7                                 'x = 7

    numRows = 5                                'y = 16



    cellW = 120                                 'cell width in pixels

    cellH = 25                                  'cell height in pixels



    DIM colwidth(7)



    colwidth(1) = 315

    colwidth(2) = 36

    colwidth(3) = 36

    colwidth(4) = 48

    colwidth(5) = 42

    colwidth(6) = 42

    colwidth(7) = 42



    FOR QQ = 1 TO numCols

        gridW = gridW + colwidth(QQ)            'accumulate the total column width needed

        NEXT QQ

    gridH = numRows * cellH                     'total pixel height needed



    gridX = 50                                  'location of data grid on window

    gridY = 50



    cellColor$ = "black"

    cellBackcolor$ = "lightgray"



    dim cell$(numCols, numRows)

    for C = 1 to numCols

        for R = 1 to numRows

            cell$(C, R) = ""                    'fill the sheet's array with EMPTY data

            next R

        next C



    WindowWidth  = 800                          'DEFINE a standard window

    WindowHeight = 800



    UpperLeftX = (DisplayWidth - WindowWidth) / 2

    UpperLeftY = (DisplayHeight - WindowHeight) / 2



    Margin = 3



'   place a graphicbox at gridX, gridY, gridW and gridH



    graphicbox #main.g, gridX, gridY, gridW + Margin, gridH + Margin



'   set up a hidden default button, if enter is pressed [okclicked] will be called



    button    #main.ok, "", [okclicked], UL, -25, -25, 20, 20

    stylebits #main.ok, _BS_DEFPUSHBUTTON, 0, 0, 0



'   set up a hidden textbox and relocate it to intercept data input when any cell is clicked



    textbox #main.txt, -100, -100, cellW, cellH



    button  #main.print,  "Print",  [printit],   LR, 105, 50

    button  #main.submit, "Submit", [okclicked], LR,  50, 50



    open "Data Grid Trial - v3" for window as #main

    #main   "trapclose [quit]"

    #main   "font consolas 10 bold"

    #main.g "font consolas 10 bold"

    #main.g "down;fill buttonface;flush"

    #main.g "when leftButtonUp [cellclicked]"

'   find the handle of the moveable textbox and the ok button so we can check WHICH has focus

    htxt = hWnd(#main.txt)

    hok  = hWnd(#main.submit)

    goto [redraw]



[quit]

    close #main

    end



[cellclicked]                       'set an editing flag and capture cell coordinates

    editing = 1

    cum.boxwidth = 0

    cury = int(MouseY / cellH) + 1



    for QQ = 1 to 8

        if cum.boxwidth > MouseX then

            curx = QQ - 1

            cum.boxwidth = cum.boxwidth - colwidth(QQ - 1)

            exit for

        else

            cum.boxwidth = cum.boxwidth + colwidth(QQ)

            end if

        next QQ



[editit]                            'set x and y to locate the textbox at the cell with FOCUS

    focusX = cum.boxwidth + 1 + gridX

    focusY = (cury - 1) * cellH + 1 + gridY



    #main.txt "!locate "; focusX; " "; focusY; " "; colwidth(curx); " "; cellH

    #main     "refresh"

    #main.txt cell$(curx,cury)

    #main.txt "!setfocus"

    goto [redraw]



[okclicked]                 'the user either pressed enter or clicked the ok button

'   set the editing flag, move the textbox off screen and save the changes

    timer 0

    editing = 0

    #main.txt "!locate -100 -100 " ;cellW; " " ;cellH

    #main     "refresh"

    #main.txt "!contents? t$"

    cell$(curx, cury) = t$



[redraw]                    'delete the last segment, redraw all cells and flush

    #main.g "delsegment t"

    #main.g "color "; cellColor$; ";backcolor "; cellBackcolor$



    boxXY = 0

    for C = 1 to numCols

        cellW = colwidth(C)

        for R = 1 to numRows

            #main.g "place ";     boxXY;             " "; (R - 1) * cellH

            #main.g "boxfilled "; boxXY + cellW + 1; " "; R * cellH + 1

            #main.g "place ";     boxXY + 4;         " "; ((R - 1) * cellH) + 17;";|";cell$(C, R)

            next R

        boxXY = boxXY + colwidth(C)

        next C

    #main.g "flush t"



'   if we are editing start to repeatedly check if the user has moved focus by

'   TABbing, pressing ENTER or clicking the SUBMIT button



    if editing then

        timer 100, [check]

        wait

    [check]                                         'check if we have lost focus

        CallDLL #user32, "GetFocus", h As ulong     'if focus is not on the textbox check if it

        if htxt <> h then

            if h = hok then goto [okclicked]        'is on the SUBMIT button and branch accordingly

            timer 0

            #main.txt "!contents? t$"

            cell$(curx, cury) = t$                  'save the changes

            cum.boxwidth = cum.boxwidth + colwidth(curx) '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

            curx = curx + 1                         'tab to next cell

            if curx > numCols then

                curx = 1

                cury = cury + 1

                cum.boxwidth = 0

                end if

            if cury > numRows then cury = 1 : curx = 1

            goto [editit]

            end if

        end if

    wait



[printit]

    timer 0

    #main.g "cls"

    #main.g "delsegment t"

    #main.g "color "; cellColor$; ";backcolor "; cellBackcolor$



    cum.boxwidth = 0

    for C = 1 to numCols

        cellW = colwidth(C)

        for R = 1 to numRows

            #main.g "place ";     cum.boxwidth;             " "; (R - 1) * cellH

            #main.g "boxfilled "; cum.boxwidth + cellW + 1; " "; R * cellH + 1

            #main.g "place ";     cum.boxwidth + 4;         " "; ((R - 1) * cellH) + 17;";|";cell$(C, R)

            next R

        cum.boxwidth = cum.boxwidth + colwidth(C)

        next C



    #main.g "flush t"

    wait



 
« Last Edit: Mar 4th, 2014, 02:45am by Rod » User IP Logged

Pages: 1  Notify Send Topic Print
« Previous Topic | Next Topic »

Rules|Home|Help|Search|Recent Posts|Notification

Donate $6.99 for 50,000 Ad-Free Pageviews!

| |

This forum powered for FREE by Conforums ©
Sign up for your own Free Message Board today!
Terms of Service | Privacy Policy | Conforums Support | Parental Controls