Spread sheet custom control

[KetilO]

Version 0.0.1.3
- Changed how memory are managed. This made filling a sheet rowvise twice as fast
  and columnvise 100 times faster.
- Memory allocation are now dynamic.

Version 0.0.1.4
- Fixed font bug. Bold was not set.
- SPRM_SPLITTHOR and SPRM_SPLITTVER now checks to see if you attemt to split a window that is not the last window.
- Fixed bug with SPRN_SELCHANGE notification.
- CheckBox and ComboBox cells now sends SPRN_BEFOREUPDATE and SPRN_AFTERUPDATE notifications.
- SPRM_EXPANDCELL now checks to see if it will create overlapping expansions.
- Fixed bug where hidden scrollbar could cause scrollbar area not painted.
- Added SPRIF_DOUBLE. Converts to / from double when used with SPRM_GETCELLDATA and SPRM_SETCELLDATA
- Added SPRIF_SINGLE. Converts to / from single when used with SPRM_GETCELLDATA and SPRM_SETCELLDATA
- Added TPE_FORCETYPE. The cell will preserve its type when edited.
  To be combined with TPE_TEXT, TPE_INTEGER, TPE_FLOAT, TPE_TEXTMULTILINE, TPE_BUTTON, TPE_WIDEBUTTON or TPE_HYPERLINK
  If conditions are not met on TPE_INTEGER and TPE_FLOAT, cell data will not be updated.
- Added TPE_FIXEDSIZE. Will force a 15 by 15 pixel image. To be combined with TPE_BUTTON, TPE_CHECKBOX or TPE_COMBOBOX
- SPRN_BUTTONCLICK will now be sendt only if you click on button.
- CheckBox cell will now only change if you click on checkmark.
- TPE_BUTTON now has '...' as caption.
- Some speed optimalization on large number of rows.

Version 0.0.1.5
- Improved combobox cell handling.
- wParam of SPRM_CREATECOMBO can now have the height of the combobox.

Version 0.0.1.6
- Created library.
- Speed optimized. Can now handle up to the max (65000) rows on a modern computer.
- Hold Ctrl key to scroll horizontaly with mouse wheel.
- Fixed some minor bugs.

Version 0.0.1.7
- Added option to display graph tick values. The optional number given is the number of decimals.
- Fixed bug introduced in 0.0.1.6 where saved sheets could not be loaded.
- Fixed bug where formatting with zero decimals could show wrong result.

Version 0.0.1.8
- Added SPRM_DELETECELL, Deletes a cell.
- Added Access database example.

Version 0.0.1.9
- Added TPE_DATE cell type. To be combined with TPE_INTEGER
- Added SPRM_GETDATEFORMAT and SPRM_SETDATEFORMAT. Use it to change default date format string (yyyy-MM-dd).
- Added CDate function. Converts a date string to days since 1601-01-01.

Version 0.0.2.0
- Fixed bug with SPRN_BUTTONCLICK. Did not return row and column.
- Fixed bug with SPRN_HYPRLINKCLICK. Did not return row and column.

Version 0.0.2.1
- Single click on CheckBox cell changes checked state.
- Enter or space in a CheckBox cell changes checked state.
- Single click on ComboBox button shows combolist.
- Enter or space in a ComboBox cell shows combolist.
- Enter in a combolist selects the item.
- Escape in a combolist hides the list.
- Enter or space in a Button cell that has the STATE_LOCKED state sends a SPRN_BUTTONCLICK notification.
- Column headers are now multiline.
- Fixed bug with hyperlink coloring.

Version 0.0.2.2
- lParam on SPRM_DELETECOL, SPRM_INSERTCOL, SPRM_DELETEROW and SPRM_INSERTROW specifies the number of rows / columns.
- Improved row / column resizing.
- Variable speed selection scrolling by mouse.
- Selection change by using keyboard now jumps over cancelled SPRN_SELCHANGE cells.
- SPRM_GETCELLSTRING can now be used to get a formula or graph.
- Fixed bug with reporting cell calculation errors.
- Fixed bug with column header formatting.
  NOTE! A multiline column header must have the FMTA_TOP alignment.
- Fixed bug with graph global back color.
- Fixed bug with long column header text.

KetilO

Version 0.0.1.3, 196 Downloads
Version 0.0.1.5, 66 Downloads
Version 0.0.1.6, 8 Downloads
Version 0.0.1.7, 134 Downloads
Version 0.0.1.8, 96 Downloads
Version 0.0.2.0, 21 Downloads
Version 0.0.2.1, 356 Downloads
Version 0.0.2.2, 8 Downloads. Forgot to rebuild library.

[attachment deleted by admin]

[six_L]

 :U    :cheekygreen:

[farrier]

KetilO,

Beautiful!!  :thumbu

farrier

[KetilO]

Version 0.0.1.5

Whats new:
0.0.1.4
- Fixed font bug. Bold was not set.
- SPRM_SPLITTHOR and SPRM_SPLITTVER now checks to see if you attemt to split a window that is not the last window.
- Fixed bug with SPRN_SELCHANGE notification.
- CheckBox and ComboBox cells now sends SPRN_BEFOREUPDATE and SPRN_AFTERUPDATE notifications.
- SPRM_EXPANDCELL now checks to see if it will create overlapping expansions.
- Fixed bug where hidden scrollbar could cause scrollbar area not painted.
- Added SPRIF_DOUBLE. Converts to / from double when used with SPRM_GETCELLDATA and SPRM_SETCELLDATA
- Added SPRIF_SINGLE. Converts to / from single when used with SPRM_GETCELLDATA and SPRM_SETCELLDATA
- Added TPE_FORCETYPE. The cell will preserve its type when edited.
  To be combined with TPE_TEXT, TPE_INTEGER, TPE_FLOAT, TPE_TEXTMULTILINE, TPE_BUTTON, TPE_WIDEBUTTON or TPE_HYPERLINK
  If conditions are not met on TPE_INTEGER and TPE_FLOAT, cell data will not be updated.
- Added TPE_FIXEDSIZE. Will force a 15 by 15 pixel image. To be combined with TPE_BUTTON, TPE_CHECKBOX or TPE_COMBOBOX
- SPRN_BUTTONCLICK will now be sendt only if you click on button.
- CheckBox cell will now only change if you click on checkmark.
- TPE_BUTTON now has '...' as caption.
- Some speed optimalization on large number of rows.
0.0.1.5
- Improved combobox cell handling.
- wParam of SPRM_CREATECOMBO can now have the height of the combobox.

KetilO

Attachment removed.

[hutch--]

Hi Ketil,

This is genuinely slick.  :U

Compliments.

[ramguru]

I experience huge CPU usage, to be more precisely:
I opened grid_demo
pressed one of gray cell
and boom 50% CPU for few seconds (I have two cores so 50% is equal to 100%), system halts for few seconds
and every time I press gray cell same happens

[KetilO]

Thanks ramguru

This problem was fixed in version 0.0.1.6

New upload in first post.

KetilO

[KetilO]

Version 0.0.1.7 Uploaded
- Added option to display graph tick values. The optional number given is the number of decimals.
- Fixed bug introduced in 0.0.1.6 where saved sheets could not be loaded.
- Fixed bug where formatting with zero decimals could show wrong result.

KetilO

[KetilO]

Version 0.0.1.8 uploaded.
- Added SPRM_DELETECELL, Deletes a cell.
- Added Access database example.

KetilO

[Mark N]

Hi Ketil,

New member here!

I'm using the control with good success so far but I'm having some trouble getting some features to work properly. These things all work in your demo programs so it must be something I'm doing wrong!

First off, if it matters I'm not using assembler, I'm using the control with PowerBASIC. I can create the spreadsheet fine and exercise most of the messages and events without much difficulty.

But I am having trouble with checkboxes, buttons and comboboxes. I'm sure the answers I need are in the demo programs, but it has been many, many years since I've looked much at assembler (and never with Windows) so I'm a bit lost.

For the checkboxes, the cell is created and is checked to start with, however clicking on the checkbox doesn't change the visual state, it stays checked. In the SPRN_AFTERUPDATE event, the lsb of the 'lpdta' item toggles between 1 and 0 with each click. The cell text is 'Checkbox'. Looking at the 'SprTest.inc' demo file, there are slight differences in how a checked and unchecked box are initialized, but I don't see quite what's going on.

For the buttons, again I can create them, set the colors and such without problems. Clicking on a button sends the SPRN_BUTTONCLICK event, but the Row and Col items of the SPR_BUTTON struct are always 1 and 0 respectively, no matter where the button is actually located. The other items of the SPR_BUTTON struct match how the button was created. 

Finally, for the comboboxes I don't see how to link the combobox handle to the combobox cell. The cell shows a combobox but it only has the text 'Err' visible. Trying to drop the combobox list results in a GPF. I'm following the code in the 'SprTest.asm' demo to create and populate the combo:

Code Select Expand


(This is PowerBASIC code, but I think you'll get the idea.)

hCombo is a DWORD handle to the combobox
dCombo is a DWORD handle to the combobox index

        hCombo = SendMessage(hGrid, %SPRM_CREATECOMBO, 80, 0)

' Put some data into the ComboBox
szTmp = "Item 1"
lRet = SendMessage(hCombo, %LB_ADDSTRING, 0, VarPtr(szTmp))
szTmp = "Item 2"
lRet = SendMessage(hCombo, %LB_ADDSTRING, 0, VarPtr(szTmp))

spri.flag = %SPRIF_DATA Or %SPRIF_TYPE Or %SPRIF_TEXTALIGN
spri.fmt.tpe = %TPE_COMBOBOX
spri.fmt.txtal = %FMTA_LEFT
spri.row = 4
spri.col = 2
spri.lpdta = VarPtr(dCombo)
SendMessage hGrid, %SPRM_SETCELLDATA, 0, VarPtr(spri)


The return values for the LB_ADDSTRING are correct, 0 the first time and 1 for the second string.

Well, that's enough for now!  :bg Thanks for any and all help and thanks for a really cool control.

Mark N.

[KetilO]

Hi Mark N
Welcome to the masm board.

Included is a FreeBASIC project. Maybe it will clear up a few things.
It also includes version 0.0.2.0 of the control. It fixes a bug with the SPRN_BUTTONCLICK row and column.

KetilO

[attachment deleted by admin]

[KetilO]

Version 0.0.2.0 Uploaded
0.0.1.9
- Added TPE_DATE cell type. To be combined with TPE_INTEGER
- Added SPRM_GETDATEFORMAT and SPRM_SETDATEFORMAT. Use it to change default date format string (yyyy-MM-dd).
- Added CDate function. Converts a date string to days since 1601-01-01.
0.0.2.0
- Fixed bug with SPRN_BUTTONCLICK. Did not return row and column.
- Fixed bug with SPRN_HYPRLINKCLICK. Did not return row and column.

KetilO

[Mark N]

Thanks very much!  :U  The FB code certainly made things clear.

Glad I could help you in return.  :bg

[Mark N]

Hi again!

Some suggestions/requests for the spreadsheet control:

1) For the cells that are defined as a checkbox or combobox, it takes 2 clicks of the mouse to activate the item: The first one selects the cell containing the control, and the second click acts on the control itself, either toggling the checkbox value or dropping down the combobox list. Could this be changed to a single-click process?

The button and hyperlink controls already act as single-click controls.

2) When a cell containing a control is selected it would be good to have a keyboard interface to the control. Typically Windows uses the space bar to toggle a checkbox, the Enter key or a spacebar to click a button, the down arrow key to drop down a combobox, F2 to edit the existing cell contents, Escape to cancel a dropped combobox, etc.

Thanks!

Mark N

[jcfuller]

KetilO,


I have used your Grid control in the past with  multi-line Col headers.

I cannot see how to do this with the Spreadsheet control.

Is this function available? If not could it be added?

James