I've been working on upgrading an app to use the SS control and it posed a few challenges to me.
I needed real combobox functionality, instead of the simple drop-downs the SS control provides.
I needed right-click context menus, something the SS control doesn't support.
I needed it to work with a tabbed interface and being a custom control the normal GUI doesn't handle it's hiding and unhiding, and ComboX gets a little squirrelly with tabs too.
I wanted the user to be able to move between cells with tab & shift-tab
So, I finally got it to work and thought I'd post a little demo script that demonstrates what's necessary. It's a simple app with 2 tabs, has two ComboX controls integrated in the spreadsheet, and right-clicking a cell in the SS will activate it, as well as produce a context menu. Right-clicking a column or row header will highlight the entire column or row and display a context menu too.
There are a few things that could use some improvement. Just before posting this I realized that the keyboard's context menu button doesn't work - only right-clicking brings up the menu - and I don't have time to figure that out just yet. Also, I'd eventually like the logic that resizes the width of the combox's listview to account for a possible vertical scrollbar. Finally, I'd like to think the logic for determining which cell is right-clicked on could be simplified but I can't see any other way to do it with the API functions we have.
Anyway, hopefully this will help someone else that wants to use the SS control with similar functionality later.
Code:
; Demo of spreadsheet control using the ComboX control for true comboboxes,
; a handler to allow for context menus with awareness of the column and row the
; user clicks on, demonstrates how to hide and show the spreadsheet when used
; with a Tab control, and enables movement with the tab key.
#SingleInstance, force
; Setup handler for converting tab press to arrow keys for SS controls
WM_KEYDOWN = 0x100
OnMessage( WM_KEYDOWN, "CheckTab" )
; create a context menu
Menu, ssContextMenu, Add, Show Cell Info, ContextSSShowCellInfo
Menu, ssContextMenu, Add, Show Column Info, ContextSSShowColInfo
Gui, +LastFound +Resize
hwnd := WinExist()
; Add tab control
Gui, Add, Tab2, w200 h180 gTabSwitch vMyTab, Tab 1|Tab 2
; Add spreadsheet control
hSS := SS_Add(hwnd, 16, 30, 185, 140, "GRIDLINES CELLEDIT ROWSIZE COLSIZE STATUS", "SSHandler" )
; Subclass the spreadsheet control so we have have a custom handler to
; catch right-clicks
Win_Subclass(hSS, "SS_wndProc")
SS_SetFont(hSS, 0, "s9, MS Sans Serif")
SS_SetColCount( hSS, 3 )
SS_SetRowCount( hSS, 5 )
hSS@colhdr := 0 ; use this var for storing a column header click
hSS@rowhdr := 0 ; use this var for storing a row header click
; populate the spreadsheet
SS_SetCell(hSS,0,0, "txtal=RIGHT", "w=25")
SS_SetCell(hSS,1,0, "txt=Col 1", "txtal=LEFT", "w=52")
SS_SetCell(hSS,2,0, "txt=Col 2", "txtal=LEFT", "w=52")
SS_SetCell(hSS,3,0, "txt=Col 3", "txtal=LEFT", "w=52")
SS_SetCell(hSS,1,1, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,2,1, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,3,1, "type=TEXT FORCETYPE", "txt=" )
SS_SetCell(hSS,1,2, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,2,2, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,3,2, "type=TEXT FORCETYPE", "txt=" )
SS_SetCell(hSS,1,3, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,2,3, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,3,3, "type=TEXT FORCETYPE", "txt=" )
SS_SetCell(hSS,1,4, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,2,4, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,3,4, "type=TEXT FORCETYPE", "txt=" )
SS_SetCell(hSS,1,5, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,2,5, "type=TEXT BUTTON FORCETYPE", "imgal=RIGHT", "txt=" )
SS_SetCell(hSS,3,5, "type=TEXT FORCETYPE", "txt=" )
; Create the 2 ComboX controls
Gui, Add, ListView, HWNDhlvcbx1 vlvcbx1 w50 h70 xs ys -Hdr -Multi Grid Sort, A
FillTheList( "lvcbx1", "A1|B2" )
ComboX_Set( hlvcbx1, "2LD esc space enter click ", "OnComboX")
Win_SetOwner( hlvcbx1, hwnd ) ; necessary to hide lv child window
Gui, Add, ListView, HWNDhlvcbx2 vlvcbx2 w50 h70 xs ys -Hdr -Multi Grid Sort, A
FillTheList( "lvcbx2", "X|Y|Z" )
ComboX_Set( hlvcbx2, "2LD esc space enter click ", "OnComboX")
Win_SetOwner( hlvcbx2, hwnd ) ; necessary to hide lv child window
Gui, Tab, Tab 2
Gui, Add, GroupBox, Section w178 h140, Notes
Gui, Add, Edit, xs+10 ys+20 w155 r8 veNotes,
Gui, Show, h200 w220
; Hide the listviews initially
ComboX_Hide(hlvcbx1)
ComboX_Hide(hlvcbx2)
Return
ContextSSShowCellInfo:
SS_GetCurrentCell( hSS, Col, Row )
MsgBox Active Cell - Col: %col% Row: %Row%
return
ContextSSShowColInfo:
SS_GetCurrentCell( hSS, Col, Row )
MsgBox Right-clicked Column Header: %hSS@colhdr%
return
GuiClose:
ExitApp
TabSwitch:
; When invoked, MyTab contains the name of the tab that has been deactivated
; Hide the SS control if moving off the tab containing it
if ( MyTab = "Tab 1" ) {
; Since SS was on this tab, we need to make it invisible
Win_Show( hSS, false )
}
; Next, fetch the current value of MyTab.
GuiControlGet, MyTab
; If switching to the tab containing the SS, show it.
if ( MyTab = "Tab 1" ) {
Win_Show( hSS, true )
Gui, Show ; without this window is not active following Win_Show()
; Hide the ComboX list made active and visible when returning
; to the tab containing the controls
ComboX_Hide(hlvcbx1)
ComboX_Hide(hlvcbx2)
}
return
OnComboX(hlv, Event) {
global
if (Event != "select")
return
if ( hlv = hlvcbx1 ) {
Gui, ListView, lvcbx1 ; Set the listview to active
} else if ( hlv = hlvcbx2 ) {
Gui, ListView, lvcbx2 ; Set the listview to active
}
r := LV_GetNext() ; Determine the selected item
if r ; If we have a selected item...
{
LV_GetText( val, r, 1 ) ; Fetch it's value
SS_SetCellString( hSS, val ) ; set the cell value
}
}
FillTheList( p_list, p_values ) {
Gui, Listview, %p_list%
Loop, Parse, p_values, |
{
LV_Add("", A_LoopField )
}
LV_ModifyCol() ; Auto-size the listview's first column
}
SSHandler(hWnd, Event, EArg, Col, Row) {
static i=0
global hSS, hSS@colhdr, hSS@rowhdr, lvcbx1, lvcbx2
if ( hWnd = hSS ) {
if ( Event = "C" ) { ; Clicked button so launch ComboX
WinGetPos, x, y, ,
ControlGetPos,ssx, ssy,,,, ahk_id %hSS% ; Get the x and y coords of the SS control
SS_GetCellRect( hWnd, t, l, r, b )
; Fetch the column and listview width so we can display the listview,
; accounting for the cell width and user-supplied values.
; To Do: figure out whether the listview will use a vertical scroll bar
; and account for it's width too.
width := SS_GetColWidth( hSS, col )
lv_w := LVM_GetColWidth( hlvcbx%col%, 1 )
if ( width > lv_w )
LV_ModifyCol( 1, width )
else
width := lv_w
ComboX_Show( hlvcbx%col%, x+ssx+r-width+2, y+ssy+b, width)
}
; Column selected via left-click or keyboard, so remove column or row highlight
if ( Event = "S" ) {
if ( hSS@colhdr > 0 )
UnhighlightColumn( hSS, hSS@colhdr )
; if a row was previously highlighted, unhighlight it
if ( hSS@rowhdr > 0 )
UnhighlightRow( hSS, hSS@rowhdr )
}
; Update comboX controls with new values after they've been keyed
; in manually.
if ( Event = "UA"
and Col in 1,2 ) {
cell_value := SS_GetCellText( hSS, Col, Row )
Gui, Listview, lvcbx%col%
found := false
Loop % LV_GetCount()
{
LV_GetText( item, A_Index )
if ( item = cell_value )
found := true
}
if ( ! found ) {
LV_Add("select focus", cell_value )
LV_ModifyCol()
}
}
}
}
ss_wndProc(Hwnd, UMsg, WParam, LParam) {
global hSS, hSS@colhdr, hSS@rowhdr
static i=0
critical ;safe, always in new thread
if ( UMsg = 0x7B ) { ; "WM_CONTEXTMENU" - To Do: detect context menu button too.
y := LParam>>16
x := LParam & 0x0000FFFF + 0
if CellHitTest( hSS, x, y, col, row ) { ; Determine what cell what clicked on and change focus
; if a column was previously highlighted, unhighlight it
if ( hSS@colhdr > 0 )
UnhighlightColumn( hSS, hSS@colhdr ), hSS@colhdr := 0
; if a row was previously highlighted, unhighlight it
if ( hSS@rowhdr > 0 )
UnhighlightRow( hSS, hSS@rowhdr ), hSS@rowhdr := 0
; if the row or col = 0, disable the cell info menu and highlight the row/column
if ( row = 0 or col = 0 ) {
Menu, ssContextMenu, Disable, Show Cell Info
if ( row = 0 )
hSS@colhdr := col ; User right-clicked a column header
else
HighlightRow( hSS, Row )
if ( col = 0 )
hSS@rowhdr := row ; User right-clicked a row header
else {
HighlightColumn( hSS, Col )
Menu, ssContextMenu, Enable, Show Column Info
}
} else {
Menu, ssContextMenu, Enable, Show Cell Info
}
if ( row > 0 and col = 0 )
Menu, ssContextMenu, Disable, Show Column Info
; Finally, display the context menu
CoordMode, Menu, Screen
Menu, ssContextMenu, Show, %x%, %y%
CoordMode, Menu, Relative
}
; return without passing to SS control, else it passes msg to main window
; and that triggers the GuiContextMenu routine, without all the info needed
; to act on it.
return 0
} else
; Not a message we process, so pass the message to the default handler.
res := DllCall("CallWindowProcA", "UInt", A_EventInfo, "UInt", hSS, "UInt", UMsg, "UInt", WParam, "UInt", LParam)
return res
ss_wndProc:
return
}
HighlightColumn( hSS, Col, bg="0xFF", fg="0xFFFFFF" ) {
Loop % SS_GetRowcount( hSS )
SS_SetCell( hSS, Col, A_Index, "bg=" bg, "fg=" fg )
SS_Redraw( hSS )
}
UnhighlightColumn( hSS, Col ) {
SS_GetGlobalFields( hSS, "cell_fg cell_bg", fg, bg )
Loop % SS_GetRowcount( hSS )
SS_SetCell( hSS, Col, A_Index, "bg=" bg, "fg=" fg )
SS_Redraw( hSS )
}
HighlightRow( hSS, Row, bg="0xFF", fg="0xFFFFFF" ) {
Loop % SS_GetColCount( hSS )
SS_SetCell( hSS, A_Index, Row, "bg=" bg, "fg=" fg )
SS_Redraw( hSS )
}
UnhighlightRow( hSS, Row ) {
SS_GetGlobalFields( hSS, "cell_fg cell_bg", fg, bg )
Loop % SS_GetColCount( hSS )
SS_SetCell( hSS, A_Index, Row, "bg=" bg, "fg=" fg )
SS_Redraw( hSS )
}
CellHitTest( hSS, x, y, ByRef rtn_col="", ByRef rtn_row="", change_focus=true ){
WinGetPos, wx, wy,,,A ; Get the window coords
; Get the number of cols, rows and the header height and width
SS_GetGlobalFields( hSS, "ncols nrows ghdrht ghdrwt", ncols, nrows, chdr_ht, rhdr_width )
ControlGetPos,ssx, ssy,,,, ahk_id %hSS% ; Get the x and y coords of the SS control
; Convert the passed mouse x/y coords to x/y coords within the SS control
x -= ( wx + ssx )
y -= ( wy + ssy )
rtn_row := rtn_col := -1 ; Init to -1
; Check to see if the user has clicked in the row or column header.
if ( y <= chdr_ht )
rtn_row := 0, change_focus := false
if ( x <= rhdr_width )
rtn_col := 0, change_focus := false
; Now try to position the active cell under the mouse cursor. We check to see if the
; mouse x/y coords fall within the current cell. If not, we move the current cell
; in the direction of the mouse and check again until we either have either made the
; correct cell active, or determined that the coords are outside the area of the SS
SS_GetCurrentCell( hSS, col, row ) ; Get and save current cell location
starting_col := col, starting_row := row
success := true
Loop
{
SS_GetCellRect( hSS, top, left, right, bottom )
if !( x>right or (x<left and rtn_col != 0)
or y>bottom or (y<top and rtn_row != 0) )
break ; we're correctly positioned, so break out of the loop
if ( x>right )
col++ ; need to move to the right
if ( x<left and rtn_col != 0 )
col-- ; need to move to the left
if ( y>bottom )
row++ ; need to move down
if ( y<top and rtn_row != 0 )
row-- ; need to move up
if ( col > 0 and col <= ncols and row > 0 and row <= nrows )
; if we're still within the spreadsheet, set the current cell
SS_SetCurrentCell( hSS, col, row )
else {
success := false
break
}
}
if ( !success or !change_focus )
SS_SetCurrentCell( hSS, starting_col, starting_row ) ; reset to orig cell
if ( success ) {
if change_focus
SS_Redraw( hss ) ; redraw the SS
rtn_col := ( rtn_col = -1 and col > 0 ) ? col : rtn_col
rtn_row := ( rtn_row = -1 and row > 0 ) ? row : rtn_row
}
return success
}
; h = ListView handle.
; c = 1 based column index to get width of.
LVM_GetColWidth(h, c)
{
Return DllCall("SendMessage", "UInt", h, "UInt", 4125, "UInt", c-1, "UInt", 0) ; LVM_GETCOLUMNWIDTH
}
CheckTab( WParam, LParam, UMsg, Hwnd ) {
static WM_KEYDOWN = 0x100, VK_TAB=09, VK_RIGHT=0x27, VK_LEFT=0x25, VK_RETURN=0xD
ControlGetFocus, Ctrl, A ; get the name of the control currently in focus
if ( UMsg = WM_KEYDOWN and WParam = VK_TAB ) {
; if the spreadsheet's edit control is active, end editing
; NOTE: The edit control's number - in this case 1 - is dependent on the
; number of other edit boxes in the GUI and order they're created
if ( Ctrl = "Edit1" ) {
PostMessage, WM_KEYDOWN, VK_RETURN, ,%ctrl%
}
; if the sheet control is focused, convert the tab to an arrow key
if ( Ctrl = "SHEET_WIN1" ) {
If ( GetKeyState( "Shift" ) )
; Send {Left}
PostMessage, WM_KEYDOWN, VK_LEFT, ,%ctrl%
else
; Send {Right}
PostMessage, WM_KEYDOWN, VK_RIGHT, ,%ctrl%
}
}
}
#include SpreadSheet.ahk
#include ComboX.ahk
Login
Register
FAQ
Search
