Read and Write StringGrid Cell?

[JRL]

Is there any way to programmatically read and write to a single StringGrid cell?

I know how to use LoadFromCSV and SaveToCSV so I can read the entire grid, parse out a particular cell, change the value, reconstruct the CSV and replace it in the Grid. Its also possible to get the cell value if a user clicks on a cell and a user can edit a selected cell. It feels like there should be a way to read and write to a cell.

Anyone done this?

[Marcus Tettmar]

No, not at present. Sorry. It's on the list though.

[JRL]

Ok. Glad it's on the agenda.

[hagchr]

Thanks JRL for opening my eyes for StringGrid. I am now trying to figure out the various parameters. Some implementations of StringGrid seem to have a Cells[row][column] kind of property to easily access individual elements. I think that is what you suggested and also seems to be on the list. In the meantime for anyone interested I created a simple SRT that would do the same thing, using the format:

Gosub>SGCells,Dialogxx,Objectxx,row,column,GET[SET],Variable[Data]

Example:

To put the string Banana into cell (2,2) in MSStringGrid1 in Dialog1:
Gosub>SGCells,Dialog1,MSStringGrid1,2,2,SET,Banana

To get the string in cell (3,3) from MSStringGrid1 in Dialog1 into the variable named result:
Gosub>SGCells,Dialog1,MSStringGrid1,3,3,GET,"result"
The variable result can then be used normally, eg MDL>result.

(The result variable name passed to the SRT needs to be in "" to avoid resolving problems, I am not yet fluent with working with VAREXPLICIT. Also since it is a simple example it will not work with any commas (,) in the string).

Example:

Code: Select all

Dialog>Dialog1
object Dialog1: TForm
  Left = 247
  Top = 96
  HelpContext = 5000
  BiDiMode = bdRightToLeftNoAlign
  BorderIcons = [biSystemMenu, biMinimize, biMaximize, biHelp]
  Caption = 'CustomDialog'
  ClientHeight = 557
  ClientWidth = 910
  Color = clBtnFace
  DragKind = dkDock
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = True
  ParentBiDiMode = False
  ShowHint = True
  OnTaskBar = False
  PixelsPerInch = 96
  TextHeight = 13
  object MSStringGrid1: tMSStringGrid
    Tag = 5
    Left = 48
    Top = 40
    Width = 657
    Height = 145
    Color = clWhite
    ColCount = 10
    DrawingStyle = gdsClassic
    FixedColor = clSilver
    Options = [goFixedVertLine, goFixedHorzLine, goVertLine, goHorzLine, goRowSizing, goColSizing, goRowMoving, goColMoving, goEditing, goTabs, goFixedColClick, goFixedRowClick, goFixedHotTrack]
    ParentBiDiMode = False
    TabOrder = 0
  end
  object MSStringGrid2: tMSStringGrid
    Left = 52
    Top = 222
    Width = 669
    Height = 75
    ColCount = 10
    DrawingStyle = gdsClassic
    FixedColor = clSilver
    RowCount = 2
    Options = [goFixedVertLine, goFixedHorzLine, goVertLine, goHorzLine]
    TabOrder = 1
  end
end
EndDialog>Dialog1

LabelToVar>CSVDataLabel,CSVData
SetDialogProperty>Dialog1,MSStringGrid1,LoadFromCSV,CSVData
Show>Dialog1

Wait>3
Gosub>SGCELLS,Dialog1,MSStringGrid1,SET,1,4,Value
Gosub>SGCELLS,Dialog1,MSStringGrid1,SET,2,4,Low
Gosub>SGCELLS,Dialog1,MSStringGrid1,SET,3,4,Medium
Gosub>SGCELLS,Dialog1,MSStringGrid1,SET,4,4,High
Gosub>SGCELLS,Dialog1,MSStringGrid1,SET,5,4,Low
Gosub>SGCELLS,Dialog1,MSStringGrid1,GET,3,2,"res1"
Wait>1

Let>ctr=0
While>ctr<5
    Add>ctr,1
    Gosub>SGCELLS,Dialog1,MSStringGrid2,SET,2,{%ctr%+1},%ctr%
    Wait>0.2
EndWhile

Wait>1
MDL>For top StringGrid:%CRLF%Cell (3,2) contains %res1%

SRT>SGCELLS
    //Gosub>SGCELLS,Dialogxx,MSStringGridxx,GET[SET],Row,Col,ResultVariable[Value]

    //Define variables for input information
    CodeBlock
        Let>DialogTarget=SGCELLS_VAR_1
        Let>ObjectTarget=SGCELLS_VAR_2
        Let>ActionTarget=SGCELLS_VAR_3
        UpperCase>ActionTarget,ActionTarget
        RegEx>\d+,%SGCELLS_VAR_4%,0,RowTarget,NRowTarget,0
        RegEx>\d+,%SGCELLS_VAR_5%,0,ColTarget,NColTarget,0
        If>ActionTarget=GET
            //Get Result variable
            Let>ResultVar=%SGCELLS_VAR_6%
            //Create the result variable without "" in ResultVar_1
            RegEx>(?<=").+?(?="),ResultVar,0,ResultVar,NM,0
        Else
            Let>DataTarget=SGCELLS_VAR_6
        Endif
    EndCodeBlock

    //Get current data from StringGrid in question into variable GridData
    GetDialogProperty>%DialogTarget%,%ObjectTarget%,SavetoCSV,GridData

    //Check so not out of bounds
    CodeBlock
        GetDialogProperty>%DialogTarget%,%ObjectTarget%,RowCount,RowMax
        GetDialogProperty>%DialogTarget%,%ObjectTarget%,ColCount,ColMax
        Let>Row=RowTarget_1
        Let>Col=ColTarget_1
        If>Row>RowMax
            MDL>Grid only contains %RowMax% rows.
            If>ActionTarget=GET
                Let>%ResultVar_1%=N/A
            EndIF
            Goto>bypass
        EndIf
        If>Col>ColMax
            MDL>Grid only contains %ColMax% columns.
            If>ActionTarget=GET
                Let>%ResultVar_1%=N/A
            EndIF
            Goto>bypass
        EndIf
        EndCodeBlock

    //Break down original table
    CodeBlock
        //Create GridArray_x_y based on StringGrid data (now in GridData)
        Let>delimiter=,
        Separate>GridData,%CRLF%,GridRowItems
        Let>tmprow=%GridRowItems_Count%-1
        Let>ctrx=0
        While>ctrx<tmprow
            Add>ctrx,1
            Let>GridRow=GridRowItems_%ctrx%
            Separate>GridRow,delimiter,GridRowData
            Let>tmpcol=%GridRowData_Count%
            Let>ctry=0
            While>ctry<tmpcol
                Add>ctry,1
                Let>tmp=GridRowData_%ctry%
                Let>GridArray_%ctrx%_%ctry%=%tmp%
            EndWhile
        EndWhile
        If>ActionTarget=GET
            Let>tmp=GridArray_%Row%_%Col%
            Let>%ResultVar_1%=tmp
        Endif
    EndCodeBlock

    //Put it together after changes and load back to StringGrid. Only relevant for SET actions
        If>ActionTarget=SET
            Let>result=
            Let>ctrx=0
            While>ctrx<tmprow
                Add>ctrx,1
                Let>ctry=0
                While>ctry<tmpcol
                    Add>ctry,1
                    Let>tmp=GridArray_%ctrx%_%ctry%
                    If>ctry=tmpcol
                        If>{(%ctrx%=%Row%)AND(%ctry%=%Col%)}
                            Let>result=%result%%DataTarget%
                        Else
                            Let>result=%result%%tmp%
                        Endif
                    Else
                        If>{(%ctrx%=%Row%)AND(%ctry%=%Col%)}
                            Let>result=%result%%DataTarget%,
                        Else
                            Let>result=%result%%tmp%,
                        Endif
                    Endif
                EndWhile
            Let>result=%result%%CRLF%
            EndWhile
           SetDialogProperty>%DialogTarget%,%ObjectTarget%,LoadFromCSV,%result%
        Endif

    Label>bypass
END>SGCELLS
/*
CSVDataLabel:
Shape,Colour,Size
Triangle,Red,Large
Square,Blue,Small
Oval,Yellow,Medium
Circle,Green,Huge
*/

Just the SRT:

Code: Select all

SRT>SGCELLS
    //Gosub>SGCELLS,Dialogxx,MSStringGridxx,GET[SET],Row,Col,ResultVariable[Value]

    //Define variables for input information
    CodeBlock
        Let>DialogTarget=SGCELLS_VAR_1
        Let>ObjectTarget=SGCELLS_VAR_2
        Let>ActionTarget=SGCELLS_VAR_3
        UpperCase>ActionTarget,ActionTarget
        RegEx>\d+,%SGCELLS_VAR_4%,0,RowTarget,NRowTarget,0
        RegEx>\d+,%SGCELLS_VAR_5%,0,ColTarget,NColTarget,0
        If>ActionTarget=GET
            //Get Result variable
            Let>ResultVar=%SGCELLS_VAR_6%
            //Create the result variable without "" in ResultVar_1
            RegEx>(?<=").+?(?="),ResultVar,0,ResultVar,NM,0
        Else
            Let>DataTarget=SGCELLS_VAR_6
        Endif
    EndCodeBlock

    //Get current data from StringGrid in question into variable GridData
    GetDialogProperty>%DialogTarget%,%ObjectTarget%,SavetoCSV,GridData

    //Check so not out of bounds
    CodeBlock
        GetDialogProperty>%DialogTarget%,%ObjectTarget%,RowCount,RowMax
        GetDialogProperty>%DialogTarget%,%ObjectTarget%,ColCount,ColMax
        Let>Row=RowTarget_1
        Let>Col=ColTarget_1
        If>Row>RowMax
            MDL>Grid only contains %RowMax% rows.
            If>ActionTarget=GET
                Let>%ResultVar_1%=N/A
            EndIF
            Goto>bypass
        EndIf
        If>Col>ColMax
            MDL>Grid only contains %ColMax% columns.
            If>ActionTarget=GET
                Let>%ResultVar_1%=N/A
            EndIF
            Goto>bypass
        EndIf
        EndCodeBlock

    //Break down original table
    CodeBlock
        //Create GridArray_x_y based on StringGrid data (now in GridData)
        Let>delimiter=,
        Separate>GridData,%CRLF%,GridRowItems
        Let>tmprow=%GridRowItems_Count%-1
        Let>ctrx=0
        While>ctrx<tmprow
            Add>ctrx,1
            Let>GridRow=GridRowItems_%ctrx%
            Separate>GridRow,delimiter,GridRowData
            Let>tmpcol=%GridRowData_Count%
            Let>ctry=0
            While>ctry<tmpcol
                Add>ctry,1
                Let>tmp=GridRowData_%ctry%
                Let>GridArray_%ctrx%_%ctry%=%tmp%
            EndWhile
        EndWhile
        If>ActionTarget=GET
            Let>tmp=GridArray_%Row%_%Col%
            Let>%ResultVar_1%=tmp
        Endif
    EndCodeBlock

    //Put it together after changes and load back to StringGrid. Only relevant for SET actions
        If>ActionTarget=SET
            Let>result=
            Let>ctrx=0
            While>ctrx<tmprow
                Add>ctrx,1
                Let>ctry=0
                While>ctry<tmpcol
                    Add>ctry,1
                    Let>tmp=GridArray_%ctrx%_%ctry%
                    If>ctry=tmpcol
                        If>{(%ctrx%=%Row%)AND(%ctry%=%Col%)}
                            Let>result=%result%%DataTarget%
                        Else
                            Let>result=%result%%tmp%
                        Endif
                    Else
                        If>{(%ctrx%=%Row%)AND(%ctry%=%Col%)}
                            Let>result=%result%%DataTarget%,
                        Else
                            Let>result=%result%%tmp%,
                        Endif
                    Endif
                EndWhile
            Let>result=%result%%CRLF%
            EndWhile
           SetDialogProperty>%DialogTarget%,%ObjectTarget%,LoadFromCSV,%result%
        Endif

    Label>bypass
END>SGCELLS

[JRL]

Nice job hagchr! Parse out the specific cell, change it, then rewrite the csv. I like that its modular and self contained. What I ended up doing was writing the StringGrid data out to a CSV file then using database functions against the CSV file. Last thing is to re-import the altered file back to the StringGrid. Works for me as I am maintaining the data in the CSV file anyway.

Maybe I should try making it a SubFunction like you did then it would be postable. Someday, maybe.

Edit-
One caveat, that I didn't previously realize, is that a csv file cannot be altered using SQL "Update" or "Delete" functions. You can query a csv file using SQL and you can use Insert which is nearly equivalent to simply using Macro Scheduler's WriteLn> function. Both append data as a new line at the end of the csv file. At the moment query and insert are all I need. If I need to update previously entered data I'll be sure to use hagchr's script above.

From:
http://support.microsoft.com/kb/281759

The ODBC text file driver does not support DELETE and UPDATE statements, because ODBC depends on a unique key index to determine which record to update or delete. The text file has no such index; therefore, the only allowable modifications are newly added records, which use the INSERT statement without DELETE or UPDATE statements.