SpreadSheet

SpreadSheet control is extremelly fast and small Excell like control, developed in Assembler.

(See SpreadSheet.png)

Summary
SpreadSheetSpreadSheet control is extremelly fast and small Excell like control, developed in Assembler.
AddAdd control to the Gui
BlankCellErase the cell.
CreateComboCreates COMBOBOX cell type.
ConvertDateConverts date from / to integer.
DeleteCellDelete cell.
DeleteColDelete column.
ExpandCellExpand a cell to cover more than one cell.
GetCellGet the cell parameters.
GetCellArrayGet all cell parameters into array.
GetCellBlobReturns pointer to the current cell BLOB.
GetCellDataGet the cell data.
GetCellRectGet the current cells rect in active splitt.
GetCellTextGet cell text
GetCellTypeGet cell data type
GetColCountGet number of columns
GetColWidthGet column width.
GetCurrentCellGet current cell in active window.
GetCurrentColGet current column in active window.
GetCurrentRowGet current row in active window.
GetCurrentWinGet active splitt window.
GetDateFormatGet date format.
GetGlobalFieldsGet individual global parameters from the control.
GetLockColGet lock cols in active splitt.
GetLockRowGet lock rows in active splitt.
GetMultiSelGet multiselection.
GetRowCountGet number of rows.
GetRowHeightReturns row height.
ImportLineImport a line of data.
InsertColInsert column.
InsertRowInsert row.
LoadFileLoad a file.
NewSheetClears the sheet.
ReCalcRecalculates the sheet.
RedrawRedraw the control.
SaveFileSave a file.
ScrollCellScrolls current cell into view.
SetCellSet content of the cell.
SetCellDataSet the data of the cell.
SetCellBLOBSet the cell binary data.
SetCellStringSet the text of the selected cell.
SetColWidthSet column width.
SetCurrentCellSet current cell in the active window.
SetCurrentWinSet active splitt window.
SetDateFormatSet date format.
SetColCountSet number of columns.
SetFontSet font.
SetGlobalSet all global parameters for the control.
SetGlobalFieldsSet individual global parameters for the control.
SetLockColLock columns in active split.
SetLockRowLock rows in active split.
SetMultiSelSet multiselection
SetRowCountSet number of rows.
SetRowHeightSet row height.
SplittHorCreate horizontal splitt in current splitt at current row.
SplittVerCreate vertical splitt in current splitt at current col.
SplittCloseClose the current splitt.
SplittSyncSynchronizes a splitt window with it’s parent.
Formulas
Graphs
Examples
Known Bugs
About

Add

SS_Add(HParent,  
X,  
Y,  
W,  
H,  
Style = "",
Handler = "",
DllPath = "")

Add control to the Gui

Parameters

HParentParent’s handle.
X..HControl coordinates.
StyleWhite separated list of control styles.
HandlerNotification handler, optional.
DllPathPath to the dll, by default look at the working folder.

Styles

VSCROLL HSCROLL STATUS GRIDLINES ROWSELECT CELLEDIT GRIDMODE COLSIZE ROWSIZE WINSIZE MULTISELECT

Handler

result := Handler(HCtrl, Event, EArg, Col, Row)
HCtrlHandle of the speradsheet control that sends the notification.
EventEvent that ocured.  Can be S (select), EB (before edit), EA (after edit), UB (before update), UA (after update), C (click) and D (draw)
EArgEvent argument.  Depends on event.  See below.
ColColumn of the associated cell.
RowRow of the associated cell.
resultHandler result, depends on event.  See bellow.

Event types and returns

Handler’s EArg parameter is specific to each event:

SCurrent splitt window number.  Return 1 to prevent selection.
EAUser input.  Return 1 to discard user input.
CB (Button) or H (Hyperlink).  Return value isn’t used.
EB,UB,UBEmpty (argument isn’t used).  Return value isn’t used.
DPointer to DRAWITEMSTRUCT.  See http://msdn.microsoft.com/en-us/library/bb775802(VS.85).aspx.

Returns

Control’s handle.

BlankCell

SS_BlankCell(hCtrl,  
Col = "",
Row = "")

Erase the cell.

CreateCombo

SS_CreateCombo(hCtrl,  
Content,  
Height = 150)

Creates COMBOBOX cell type.

Parameters

Content| separated list of ComboBox items.
HeightHeight of the combo box.

Returns

Handle of the ComboBox.  Use it with txt parameter of the <CetCell> function.

ConvertDate

SS_ConvertDate(hCtrl,  
Date,  
RefreshFormat = false)

Converts date from / to integer.

Parameters

DateInteger or textual representation of the date.
RefreshFormatSet to TRUE to refresh control’s date format that is stored internaly on first call.

Returns

If Date is integer the return value is the date string, otherwise the retun value is integer representation of the date.

DeleteCell

SS_DeleteCell(hCtrl,  
Col = "",
Row = "")

Delete cell.

Remarks

Its misterious to me what is the difference between this one and BlankCell.

DeleteCol

SS_DeleteCol(hCtrl,  
Col = "")

Delete column.

Parameters

ColColumn index.  Of omited, current column is used.

ExpandCell

SS_ExpandCell(hCtrl,
Left,
Top,
Right,
Bottom)

Expand a cell to cover more than one cell.

Parameters

Left, Top, Right, BottomCoordinates of the expanded cell.

GetCell

SS_GetCell( hCtrl,  
 Col,  
 Row,  
 pQ,  
ByRef o1 = "",
ByRef o2 = "",
ByRef o3 = "",
ByRef o4 = "",
ByRef o5 = "")

Get the cell parameters.

Parameters

Col, RowCell coordinates.  If set to empty current cell coordinates will be used.
pQQuery parameter.  See SetCell for the list of possible cell parameters (txt, data, w, h, bg, fg, type, state, txtal, imgal, fnt)
o1 .. o5Reference to variables to receive output in order specified in pQ parameter.

Returns

o1, so you don’t need to use reference variables to grab only 1 field i.e. state := SS_GetCell(hctrl, 1,1, “state”).

GetCellArray

SS_GetCellArray(hCtrl,  
V,  
Col = "",
Row = "")

Get all cell parameters into array.

Parameters

VArray prefix.  The array is used to return data back.  See SetCell for the list of possible cell parameters.
Col, RowCell coordinates.  If omited current cell coordinates will be used.

Remarks

To get individual fields, its faster to use GetCell.  Also, this function creates global variables.

GetCellBlob

Returns pointer to the current cell BLOB.

Parameters

EArgD event handlers event argument (pointer to DRAWITEM struct).
GetText?Set to true to return text instead of binary data.

GetCellData

SS_GetCellData(hCtrl,  
Col = "",
Row = "")

Get the cell data.

Parameters

Col, RowCell coordinates.  If omited, current cell will be used.

Remarks

This funcion also returns data for OVERDRAWINTEGER type.  Its faster to use to get the integer then other functions.

GetCellRect

SS_GetCellRect( hCtrl,
ByRef top,
ByRef left,
ByRef right,
ByRef bottom)

Get the current cells rect in active splitt.

Returns

Handle of active splitt window.

GetCellText

SS_GetCellText(hCtrl,  
Col = "",
Row = "")

Get cell text

Parameters

Col, RowCoordinates of the cell.  If omited, current cell will be used.

Remarks

This function retrieves any kind of text from all types of cells, no matter the internal representation.  For ComboBox selected item will be returned and for CheckBox 1 or 0.

GetCellType

SS_GetCellType(hCtrl,  
Col = "",
Row = "",
Flag = 0)

Get cell data type

Parameters

Col, RowCoordinates.  If omited, current cell will be used
FlagUsed internaly.  1 to return numeric type, 2 to return numeric base type without modifiers Returns: Number (not text for now)

GetColCount

SS_GetColCount(hCtrl)

Get number of columns

GetColWidth

SS_GetColWidth(hCtrl,
col)

Get column width.

GetCurrentCell

SS_GetCurrentCell( hCtrl,
ByRef Col,
ByRef Row)

Get current cell in active window.

Parameters

Col, RowReference to variables to receive output.

GetCurrentCol

SS_GetCurrentCol(hCtrl)

Get current column in active window.

Returns

Current column index.

GetCurrentRow

SS_GetCurrentRow(hCtrl)

Get current row in active window.

Returns

Current row index.

GetCurrentWin

SS_GetCurrentWin(hCtrl)

Get active splitt window.

GetDateFormat

SS_GetDateFormat(hCtrl)

Get date format.

GetGlobalFields

SS_GetGlobalFields( hCtrl,  
 Fields,  
ByRef v1 = "",
ByRef v2 = "",
ByRef v3 = "",
ByRef v4 = "",
ByRef v5 = "",
ByRef v6 = "",
ByRef v7 = "")

Get individual global parameters from the control.

Parameters

FieldsWhite space separated list of field names to get.
v1 .. v7Field values, respecting the order of names in fields argument.

Example

SS_GetGlobalFields(hCtrl, "nrows ncols", rows, cols)

GetLockCol

SS_GetLockCol(hCtrl)

Get lock cols in active splitt.

GetLockRow

SS_GetLockRow(hCtrl)

Get lock rows in active splitt.

GetMultiSel

SS_GetMultiSel( hCtrl,  
ByRef Top = "",
ByRef Left = "",
ByRef Right = "",
ByRef Bottom = "")

Get multiselection.

Parameters

Top, Left, Right, BottmReference to variables to receive the output.  You can omit any you don’t need.

GetRowCount

SS_GetRowCount(hCtrl)

Get number of rows.

GetRowHeight

SS_GetRowHeight(hCtrl,
Row)

Returns row height.

ImportLine

SS_ImportLine(hCtrl,  
DataLine,  
SepChar = ";")

Import a line of data.

Parameters

DataLineText containing the data.
SepCharData separator, by default “;”.

InsertCol

SS_InsertCol(hCtrl,  
Col = -1)

Insert column.

Parameters

ColColumn index after which to insert column.  By deault -1 means to append it.

InsertRow

SS_InsertRow(hCtrl,  
Row = -1)

Insert row.

Parameters

RowPosition after which to insert row.  By deault -1 means to append it.

LoadFile

SS_LoadFile(hCtrl,
File)

Load a file.

Parameters

FileFile name.

NewSheet

SS_NewSheet(hCtrl)

Clears the sheet.

ReCalc

SS_ReCalc(hCtrl)

Recalculates the sheet.

Redraw

SS_Redraw(hCtrl)

Redraw the control.

SaveFile

SS_SaveFile(hCtrl,
File)

Save a file.

ScrollCell

SS_ScrollCell(hCtrl)

Scrolls current cell into view.

SetCell

SS_SetCell(hCtrl,  
Col = "",
Row = "",
o1 = "",
o2 = "",
o3 = "",
o4 = "",
o5 = "",
o6 = "",
o7 = "",
o8 = "",
o9 = "",
o10 = "",
o11 = "")

Set content of the cell.

Parameters

Col, RowCell coordinates.
o1 .. o11Named parameters.

Named Parameters

typeType of the cell.  See bellow for list of types.  You will generally use type when setting up cells initially and omit it when changing existing cells.
w, hWidth, height of the cell.
bg, fgBackground, foreground color.
stateCell state.
txtalText alignment and decimals.  See aligment section for list of kewords.  Use number to set FLOAT precision (1-12, all, sci).
imgalImage alignment and imagelist/control index .
fntCell font index (0-15).

Type Dependent Named Parameters

txtString (TEXT,CHECKBOX,*HDR), Number (INTEGER), hwndCombo (COMBOBOX), Formula Definition (FORMULA), Graph Definition (GRAPH)
data0-based selected index, 0 by default (COMBOBOX), 1|0 (CHECKBOX), size (OVERDRAWBLOB, return)

Types

  • TEXT TEXTMULTILINE INTEGER(32b) FLOAT(32b-80b) HYPERLINK CHECKBOX COMBOBOX FORMULA GRAPH
  • OWNERDRAWINTEGER - Ownerdraw integer.  You can implement D (draw) event.
  • OWNERDRAWBLOB - Ownerdraw BLOB.  You can put any binary data as cell’s BLOB content.  There is special support for textual BLOBs.
  • EMPTY - The cell contains formatting only.
  • COLHDR ROWHDR WINHDR - Column, row and window (splitt) header.
  • EXPANDED - Part of expanded cell, internally used.

Type Modifiers

BUTTONThe cell contains button.  Can be combined with TEXT or TEXTMULTILINE.
WIDEBUTTONThe cell will be entirely covered by button.  Can be combined with TEXT or TEXTMULTILINE.
DATECan be combined with INTEGER.
FORCETYPEThe cell will preserve its type when edited.  Can be combined with TEXT, INTEGER, FLOAT, TEXTMULTILINE, BUTTON, WIDEBUTTON or HYPERLINK.
FIXEDSIZEWill force a 15 by 15 pixel image.  To be combined with BUTTON, CHECKBOX or COMBOBOX.  Can be combined with BUTTON, CHECKBOX or COMBOBOX .

States

LOCKEDCell is locked for editing.
HIDDENCell content is not displayed.
REDRAWCell is being redrawn.
RECALCCell is being recalculated.
ERRORThere are 4 error states: ERROR DIV0 UNDERFLOW OVERFLOW.

Aligments

LEFT RIGHT MIDDLEX aligments.
TOP CENTER BOTTOMY aligments.
AUTOText left middle, numbers right middle.
GLOBALIf you omit aligment attribute, this one will be used.

SetCellData

SS_SetCellData(hCtrl,  
Data,  
Col = "",
Row = "")

Set the data of the cell.

Parameters

DataData to set.
Col,RowCell coordinates.  If omited current cell will be used.

SetCellBLOB

SS_SetCellBLOB( hCtrl,  
ByRef BLOB,  
 Col = "",
 Row = "")

Set the cell binary data.

Parameters

BLOBReference to binary data to set.  First word contains BLOB size.
Col, RowCell coordinates.  If omited current cell coordinates will be used.

SetCellString

SS_SetCellString(hCtrl,  
Txt = "",
Type = "")

Set the text of the selected cell.

Parameters

TxtText to set, by default empty.
TypeType.  If omited current cell type will be used.

SetColWidth

SS_SetColWidth(hCtrl,
Col,
Width)

Set column width.

SetCurrentCell

SS_SetCurrentCell(hCtrl,
Col,
Row)

Set current cell in the active window.

Parameters

Col, RowCoordinates of the cell to select.

SetCurrentWin

SS_SetCurrentWin(hCtrl,
Win)

Set active splitt window.

Parameters

WinWindow number (0-7).

SetDateFormat

SS_SetDateFormat(hCtrl,
Format)

Set date format.

Parameters

FormatDate format.  See Date Formats section in FormatTime AHK documentation for details.

SetColCount

SS_SetColCount(hCtrl,
nCols)

Set number of columns.

SetFont

SS_SetFont(HCtrl,
Idx,
Font)

Set font.

Parameters

IdxFont index to set (0-15).
FontFont description in usual AHK format ( “style, name”).

Returns

Font handle

SetGlobal

SS_SetGlobal(hCtrl,
g,
cell,
colhdr,
rowhdr,
winhdr)

Set all global parameters for the control.

Parameters

gGlobal formating array base name.
cellCell formatting array base name.
colhdrColumn header formatting array base name.
rowhdrRow header formatting array base name.
winhdrWindow header formatting array base name.

Global array elements

colhdrbtnColumn header button.
rowhdrbtnRow header button.
winhdrbtnWin header button.
lockcolBack color of locked cell.
hdrgrdcolHeader grid color.
grdcolCell grid color.
bcknfcolBack color of active cell, lost focus.
txtnfcolText color of active cell, lost focus.
bckfocolBack color of active cell, has focus.
txtfocolText color of active cell, has focus.
ncolsNumber of columns, 0-600, by default 255.
nrowsNumber of rows, 0-65000, by default 255.
ghdrwtHeader width.
ghdrhtHeader height.
gcellwCell width.
gcellhtCell height.

Header and cell formating elements

bgBackground color.
fgForeground color.
imgalImage align.
txtalText align.
fntFont index.
tpeControl type.

SetGlobalFields

SS_SetGlobalFields(hCtrl,  
Fields,  
v1 = "",
v2 = "",
v3 = "",
v4 = "",
v5 = "",
v6 = "",
v7 = "")

Set individual global parameters for the control.

Parameters

fieldsWhite space separated list of field names to set.
v1 .. v7Field values, respecting the order of names in fields argument.

Example:x

SS_SetGlobalFields(hCtrl, "nrows ncols", 2, 2)
SS_SetGlobalFields(hCtrl, "cell_txtal", "RIGHT MIDDLE")

SetLockCol

SS_SetLockCol(hCtrl,  
Cols = 1)

Lock columns in active split.

Parameter

ColsNumber of columns to lock.

SetLockRow

SS_SetLockRow(hCtrl,  
Rows = 1)

Lock rows in active split.

Parameter

RowsNumber of rows to lock.

SetMultiSel

SS_SetMultiSel(hCtrl,
Left,
Top,
Right,
Bottom)

Set multiselection

SetRowCount

SS_SetRowCount(hCtrl,
nRows)

Set number of rows.

SetRowHeight

SS_SetRowHeight(hCtrl,  
Row = 0,
Height = 0)

Set row height.

Parameters

RowIndex of the row, by default 0 (header)
HeightHeight of the row, by default 0.

SplittHor

SS_SplittHor(hCtrl)

Create horizontal splitt in current splitt at current row.

SplittVer

SS_SplittVer(hCtrl)

Create vertical splitt in current splitt at current col.

SplittClose

SS_SplittClose(hCtrl)

Close the current splitt.

SplittSync

SS_SplittSync(hCtrl,  
Flag = 1)

Synchronizes a splitt window with it’s parent.

Formulas

Functions

Type                        Example                     Description
---------------------------------------------------------------------------------------------------
And, Or, Xor                AA1>=0 And AA2<=0           Logical operators
<, <=, =, >=, >, <>         AA1>=5.5                    Compare operators
+, -, *, /                  ((AA1+AA2)/2-3.5)*4         Basic math operators
x^y                         AA1^3.5                     x to the power of y
Sum(r1:r2)                  Sum(AA1:AC6)                Sum of all cells in an area
Cnt(r1:r2)                  Cnt(AA1:AA5)                Number of cells in an area that contains a value
Avg(r1:r2)                  Avg(AB1:AB6)                Average value of an area
Min(r1:r2)                  Min(AA1:AA7)                Returns smallest number in an area
Max(r1:r2)                  Max(AA1:AB3)                Returns largest number in an area
Var(r1:r2)                  Var(AB1:AC6)                The variance of an area
Std(r1:r2)                  Std(AB1:AB6)                The standard deviation of an area
Sqt(num)                    Sqt(AA1+AA2)                Sqear root
Sin(num)                    Sin(AA5)                    Sine of an angle in radians
Cos(num)                    Cos(PI()/8)                 Cosine of an angle in radians
Tan(num)                    Tan(Rad(45))                Tangent of an angle in radians
Rad(num)                    Rad(45)                     Converts grades to radians
PI()                        PI()/4                      Returns PI
IIf(Cnd,TP,FP)              IIf(AA1=0,AA2,AA3)          IIf(Condition,TruePart,FalsePart)
On(val,num[,num[,num...]])  On(AA1,AA2,AA3,AA4)         Depending on val, return num
                                                            If val<=0, return first num
                                                            If val>=number of num, return last num
Abs(num)                    Abs(AA5-7)                  Absolute. Returns absolute value of num
Sgn(num)                    Sgn(AA5)                    Sign. Returns -1, 0 or +1
Int(num)                    Int(AA5)                    Integer. Returns integer value of num
Log(num)                    Log(AA5)                    Logarithm to base 10
Ln(num)                     Ln(e())                     Natural Logarithm
e()                         e()^AA5                     Returns e
Asin(num)                   Asin(AA4)                   Inverse Sine
Acos(num)                   Acos(AA3)                   Inverse Cosine
Atan(num)                   Atan(AA1)                   Inverse Tangent
Grd(num)                    Grd(PI()/4)                 Converts radians to grades
Rgb(num,num,num)            Rgb(255,0,0)                Converts red, green, blue to color
x()                         Sin(x())                    x value used in calculating graphs fx() math functions
@(rc,rr)                    @(-1,2)                     Relative cell reference
CDate(str)                  CDate("2008-01-01")         Converts a date string to days since 1601-01-01

Error Conditions

Type                        Dscription
---------------------------------------------------------------------------------------------------
####Ref                     Circular reference or reference to cell with error condition
####Err                     Meaning depends on function
####Div                     Division by zero
####Ovf                     Overflow
####Unf                     Underflow

Graphs

Syntax

Grp( [T], X, Y, fx1 | gx1, fx2 | gx2, ...)      ;no spaces allowed

Definition

T(xp,yp,Orientation,Color,”Text” )Text (max 16)
X,Y(xMin,xMax,xOrigo,xStep,Color,”Text”)X and Y axis (max 1)
fx(function,xStep,Color,”Text”)Graph from math function (max 16)
gx(r1:r2,Color,”Text”)Graph from cell values (max 16)

Note

Bad graph definitions will burn CPU or crash application.  Even single space more in Grp syntax will do that, and you will have to kill the AutoHotKey.exe using task manager.

Examples

First Sample

#Singleinstance, force
    Gui, +LastFound +ToolWindow
    hwnd := WinExist()

    hCtrl := SS_Add(hwnd, 0, 0, 300, 400, "VSCROLL  GRIDMODE CELLEDIT ROWSIZE COLSIZE ROWSELECT")
    SS_SetRowHeight(hCtrl)

    SS_SetColCount(hCtrl, 2)
    SS_SetRowCount(hCtrl, 8)

    SS_SetColWidth(hCtrl, 1, 148)
    SS_SetColWidth(hCtrl, 2, 145)

    SS_SetGlobalFields(hCtrl, "cell_txtal", "CENTER MIDDLE")

    hCombo := SS_CreateCombo(hCtrl, "item1|item2|item3|item4")

    SS_SetCell(hCtrl, 1, 1, "type=TEXT", "txt=Caption", "bg=0xFF", "fg=0xFFFFFF")
    SS_SetCell(hCtrl, 2, 1, "type=FORCETEXT")

    SS_SetCell(hCtrl, 1, 2, "type=TEXT", "txt=Style", "bg=0xFF", "fg=0xFFFFFF")
    SS_SetCell(hCtrl, 2, 2, "type=COMBOBOX", "txt=" hCombo, "data=1", "imgal=RIGHT",  "txtal=CENTER")   ;select 2nd item

    SS_SetCell(hCtrl, 1, 3, "type=TEXT", "txt=Anchor", "bg=0xFF", "fg=0xFFFFFF")
    SS_SetCell(hCtrl, 2, 3, "type=BUTTON TEXT", "txt=w0.5 h", "imgal=RIGHT", "txtal=CENTER")

    SS_SetCell(hCtrl, 1, 4, "type=TEXT", "txt=Visible", "bg=0xFF", "fg=0xFFFFFF")
    SS_SetCell(hCtrl, 2, 4, "type=CHECKBOX", "data=1", "imgal=CENTER")

    SS_SetCell(hCtrl, 1, 6, "type=TEXT", "txt=Help", "bg=0xFFFF", "fg=-1")
    SS_SetCell(hCtrl, 2, 6, "type=HYPERLINK", "txt=www.autohotkey.com", "txtal=CENTER")

    SS_SetCell(hCtrl, 1, 8, "type=WIDEBUTTON TEXT", "Txt=Wide &Button", "txtal=CENTER")

    Gui, Show, w300 h150
return

Get Cell Information

SS_GetCellArray(hCtrl, "cell")    ;"cell" name of pseudo array holding the data, use selected cell
msg =
(LTrim
   Text   = %cell_txt%
   Data   = %cell_data%
   State  = %cell_hState%

   bg     = %cell_bg%
   fg     = %cell_fg%
   txtal  = %cell_htxtal%
   imgal  = %cell_himgal%
   fnt    = %cell_fnt%
   type   = %cell_Type%
)

msgbox %msg%

Set All Global Data

;header and cell defaults
h_bg := c_bg   := 0xAAAAAA
h_txtal := c_txtal := "CENTER MIDDLE"
h_fg := 0xFF0000
h_fnt := 2

g_ncols     := 100           ;number of  columngs
g_nrows     := 100           ;number of rows

g_colhdrbtn := 0             ;button sytle col hdr
g_rowhdrbtn := 0             ;button style row hdr
g_winhdrbtn := 0             ;button style win hdr
g_lockcol   := 0xAAAAAA      ;Back color of locked cell
g_hdrgrdcol := 0xFF00FF      ;Header grid color
g_grdcol    := 0xFFFFFF      ;Cell grid color
g_bcknfcol  := 0xCCCCCC      ;Back color of active cell, lost focus
g_txtnfcol  := 1             ;Text color of active cell, lost focus
g_bckfocol  := 0xFFFF        ;Back color of active cell, has focus
g_txtfocol  := 0             ;Text color of active cell, has focus

g_ghdrwt    := 25            ;header width
g_ghdrht    := 25            ;header height
g_gcellw    := 50            ;cell width
g_gcellht   := 50            ;cell height

SS_SetGlobal(hCtrl, "g", "c", "h", "h", "h")

Formula Example

;formula
    Gui, +LastFound
    hwnd := WinExist()

    hCtrl := SS_Add(hwnd, 0, 25, 552, 477, "CELLEDIT STATUS")

    SS_SetCell(hCTrl, 1, 1, "txt= x  =", "type=TEXT", "txtal=CENTER", "fnt=1")
    SS_SetCell(hCTrl, 1, 2, "txt= y  =", "type=TEXT", "txtal=CENTER", "fnt=1")
    SS_SetCell(hCTrl, 1, 3, "txt=x+y =", "type=TEXT", "txtal=CENTER", "fnt=1")

    SS_SetCell(hCtrl, 2, 1, "type=INTEGER", "txt=90", "fnt=1", "txtal=LEFT")
    SS_SetCell(hCtrl, 2, 2, "type=INTEGER", "txt=20", "fnt=1", "txtal=LEFT" )
    SS_SetCell(hCtrl, 2, 3, "type=FORMULA", "txt=AB1+AB2", "txtal=LEFT")
    SS_ReCalc(hCtrl)

    Gui, Show, w550 h500, SpreadSheet
return

Graph Example

    Gui, +LastFound
    hwnd := WinExist()

    hCtrl := SS_Add(hwnd, 0, 25, 552, 477)

    graph =
    (LTrim Join
        Grp(
            T(-1,0,0,Rgb(0,0,0),"Graph Demo"),
            X(0,PI()*4,0,1,Rgb(0,0,255),"x-axi),
            Y(-1.1,1.1,0,0.5,Rgb(255,0,0),"y-axs"is"),
            gx(AJ1:AJ13,Rgb(0,0,0),"Cell values"),
            fx(Sin(x()),0.1,Rgb(255,0,255),"Sin(x)"),
            fx(x()^3-x()^2-x(),0.1,Rgb(0,128,0),"x^3-x^2-x"))
    )

    SS_SetCell(hCtrl, 1, 1, "type=GRAPH", "txt=" graph, "bg=0x0D0FFFF")
    SS_ExpandCell(hCtrl, 1, 1, 6, 15)

    Gui, Show, w550 h500, SpreadSheet
return

Known Bugs

  • Cell cursor disapears in expanded cells.
  • Multiselect scrolling by mouse is too fast.
  • Scroll-locked area does not work well with splitts.
  • Float to ascii does not work on numbers > 1e000 or < 1e-4000

About

SS_Add(HParent,  
X,  
Y,  
W,  
H,  
Style = "",
Handler = "",
DllPath = "")
Add control to the Gui
SS_BlankCell(hCtrl,  
Col = "",
Row = "")
Erase the cell.
SS_CreateCombo(hCtrl,  
Content,  
Height = 150)
Creates COMBOBOX cell type.
SS_ConvertDate(hCtrl,  
Date,  
RefreshFormat = false)
Converts date from / to integer.
SS_DeleteCell(hCtrl,  
Col = "",
Row = "")
Delete cell.
SS_DeleteCol(hCtrl,  
Col = "")
Delete column.
SS_ExpandCell(hCtrl,
Left,
Top,
Right,
Bottom)
Expand a cell to cover more than one cell.
SS_GetCell( hCtrl,  
 Col,  
 Row,  
 pQ,  
ByRef o1 = "",
ByRef o2 = "",
ByRef o3 = "",
ByRef o4 = "",
ByRef o5 = "")
Get the cell parameters.
SS_GetCellArray(hCtrl,  
V,  
Col = "",
Row = "")
Get all cell parameters into array.
SS_GetCellData(hCtrl,  
Col = "",
Row = "")
Get the cell data.
SS_GetCellRect( hCtrl,
ByRef top,
ByRef left,
ByRef right,
ByRef bottom)
Get the current cells rect in active splitt.
SS_GetCellText(hCtrl,  
Col = "",
Row = "")
Get cell text
SS_GetCellType(hCtrl,  
Col = "",
Row = "",
Flag = 0)
Get cell data type
SS_GetColCount(hCtrl)
Get number of columns
SS_GetColWidth(hCtrl,
col)
Get column width.
SS_GetCurrentCell( hCtrl,
ByRef Col,
ByRef Row)
Get current cell in active window.
SS_GetCurrentCol(hCtrl)
Get current column in active window.
SS_GetCurrentRow(hCtrl)
Get current row in active window.
SS_GetCurrentWin(hCtrl)
Get active splitt window.
SS_GetDateFormat(hCtrl)
Get date format.
SS_GetGlobalFields( hCtrl,  
 Fields,  
ByRef v1 = "",
ByRef v2 = "",
ByRef v3 = "",
ByRef v4 = "",
ByRef v5 = "",
ByRef v6 = "",
ByRef v7 = "")
Get individual global parameters from the control.
SS_GetLockCol(hCtrl)
Get lock cols in active splitt.
SS_GetLockRow(hCtrl)
Get lock rows in active splitt.
SS_GetMultiSel( hCtrl,  
ByRef Top = "",
ByRef Left = "",
ByRef Right = "",
ByRef Bottom = "")
Get multiselection.
SS_GetRowCount(hCtrl)
Get number of rows.
SS_GetRowHeight(hCtrl,
Row)
Returns row height.
SS_ImportLine(hCtrl,  
DataLine,  
SepChar = ";")
Import a line of data.
SS_InsertCol(hCtrl,  
Col = -1)
Insert column.
SS_InsertRow(hCtrl,  
Row = -1)
Insert row.
SS_LoadFile(hCtrl,
File)
Load a file.
SS_NewSheet(hCtrl)
Clears the sheet.
SS_ReCalc(hCtrl)
Recalculates the sheet.
SS_Redraw(hCtrl)
Redraw the control.
SS_SaveFile(hCtrl,
File)
Save a file.
SS_ScrollCell(hCtrl)
Scrolls current cell into view.
SS_SetCell(hCtrl,  
Col = "",
Row = "",
o1 = "",
o2 = "",
o3 = "",
o4 = "",
o5 = "",
o6 = "",
o7 = "",
o8 = "",
o9 = "",
o10 = "",
o11 = "")
Set content of the cell.
SS_SetCellData(hCtrl,  
Data,  
Col = "",
Row = "")
Set the data of the cell.
SS_SetCellBLOB( hCtrl,  
ByRef BLOB,  
 Col = "",
 Row = "")
Set the cell binary data.
SS_SetCellString(hCtrl,  
Txt = "",
Type = "")
Set the text of the selected cell.
SS_SetColWidth(hCtrl,
Col,
Width)
Set column width.
SS_SetCurrentCell(hCtrl,
Col,
Row)
Set current cell in the active window.
SS_SetCurrentWin(hCtrl,
Win)
Set active splitt window.
SS_SetDateFormat(hCtrl,
Format)
Set date format.
SS_SetColCount(hCtrl,
nCols)
Set number of columns.
SS_SetFont(HCtrl,
Idx,
Font)
Set font.
SS_SetGlobal(hCtrl,
g,
cell,
colhdr,
rowhdr,
winhdr)
Set all global parameters for the control.
SS_SetGlobalFields(hCtrl,  
Fields,  
v1 = "",
v2 = "",
v3 = "",
v4 = "",
v5 = "",
v6 = "",
v7 = "")
Set individual global parameters for the control.
SS_SetLockCol(hCtrl,  
Cols = 1)
Lock columns in active split.
SS_SetLockRow(hCtrl,  
Rows = 1)
Lock rows in active split.
SS_SetMultiSel(hCtrl,
Left,
Top,
Right,
Bottom)
Set multiselection
SS_SetRowCount(hCtrl,
nRows)
Set number of rows.
SS_SetRowHeight(hCtrl,  
Row = 0,
Height = 0)
Set row height.
SS_SplittHor(hCtrl)
Create horizontal splitt in current splitt at current row.
SS_SplittVer(hCtrl)
Create vertical splitt in current splitt at current col.
SS_SplittClose(hCtrl)
Close the current splitt.
SS_SplittSync(hCtrl,  
Flag = 1)
Synchronizes a splitt window with it’s parent.
Close