AdvSpreadgrid

I have a strange occurrence in my advspreadgrid.

I've created an Excel like grid where we can handle values and add them to any group we like.

But since a short while my Endresult field is giving me a headache.

I'm trying to add some fields depending on which are being filled and do a (sub) totalize of the records i hold. Now for every field this goes well except the Endresult.



I get an error when displaying the result. Invalid Cell reference at character 10. Now the (sub) total show 0,00 in the grid.



When I set the grid to show the formula, no error shows and the formula seems to be right. All others seem ok as well and all show the right added values also.

The difference between the cell that go right is that they have the value like: =1234,45

and the Endresult is a formula which displays the value.



When I do an export to Excel of the grid all the fields show the correct values including the (sub)totals of Endresult. Which are now correctly filled. (Excel and Open Excel)



The code for filling the (sub)totals is here:



procedure TSheetTotalize.TotalRow_SetFormulas(const Sheet:IROTSheet;

   const aDispRow,aFromDispRow,aToDispRow:integer;const bTotal:boolean);

var

range: TGridRect;

i: Integer;

Column: IROTColumn;

Ind: integer;

cim: IClassInstManager;

cSheet: TSheet;

begin

if (IROTSheet(fSheet).QueryInterface(IClassInstManager, cim) = S_OK) and

     (cim.GetClassInst(TObject(cSheet)) = S_OK) then

begin

    for Ind := aFromDispRow to aToDispRow do

      cSheet.ReaplynmA1Formulas(Ind);

end;



for i := 0 to Sheet.Layout.Columns.Count - 1 do

begin

    if not (Sheet.Layout.Columns.ByIndex(i, Column) = S_OK) then

      continue;

    while (fTotalRowFormulas.Count - 1 < i) do

      fTotalRowFormulas.Add('=');



    if (Column.VCol_ID = VCOL_LINE) then

    begin

      if bTotal then

        fGrid.Cells[Column.GridColumnIndex, aDispRow] := Translate('@Total')

      else

        fGrid.Cells[Column.GridColumnIndex, aDispRow] := Translate('@SubTotal');

      continue;

    end;



    range.Left := Column.GridColumnIndex;

    range.Top := aFromDispRow;

    range.Right := Column.GridColumnIndex;

    range.Bottom := aToDispRow;



    {if Column.FormulaAssigned then

    begin

      if (fTotalRowFormulas = '=') then

        fTotalRowFormulas := '=SUM(' + fGrid.RangeToName(range) + ')'

      else

        fTotalRowFormulas := fTotalRowFormulas + '+' +

           'SUM(' + fGrid.RangeToName(range) + ')';

      fGrid.Cells[Column.GridColumnIndex, aDispRow] := '=SUM(' +

        fGrid.RangeToName(range) + ')';

    end

    else}

    if Column.FormulaAssigned or

       (Column.Format <> COLUMN_FORMAT_STRING) and

       (Column.Format <> '') and

       not (Column.VCol_ID in [VCOL_LINE, VCOL_GRAPHICS, VCOL_SCALE, VCOL_FREQUENCY, VCOL_FORMULA]) then

    begin

      if (fTotalRowFormulas = '=') then

        fTotalRowFormulas := '=SUM(' + fGrid.RangeToName(range) + ';0)'

      else

        fTotalRowFormulas := fTotalRowFormulas + '+' + 'SUM(' + fGrid.RangeToName(range) + ';0)';



      fGrid.Cells[Column.GridColumnIndex, aDispRow] := '=SUM(' + fGrid.RangeToName(range) + ';0)';



//      fGrid.ShowFormula := true; // flip it to show value or formula

    end

    else if not (Column.VCol_ID in [VCOL_LINE, VCOL_FORMULA]) then

      fGrid.Cells[Column.GridColumnIndex, aDispRow] := GetTextPerRange(TRect(range));

end;

end;



Screenshot with error:





Screenshot with formula:





Extra info

Develop machine: Windows 8.1

Environment: Delphi XE5

Bitrate: 32 & 64 bit

TMS Grid: AdvSpreadgrid - 2.3.1.0

If my understanding is correct from your description, it is the calculation of =SUM(R1:R2;0) that is going wrong?

This seems a simple SUM on cells that have another formula. 
I created the same situation here on a default TAdvSpreadGrid initialized with:

procedure TForm1.FormCreate(Sender: TObject);
begin
  advspreadgrid1.ColCount := 26;
  advspreadgrid1.randomfill;
  advspreadgrid1.Cells[18,1] := '=A1+B1';
  advspreadgrid1.Cells[18,2] := '=A2+B2';
  advspreadgrid1.Cells[18,3] := '=SUM(R1:R2)';
  advspreadgrid1.Cells[18,4] := '=SUM(R1:R2)';
end;

but this works fine.
When you remove the cell formulas in R1 & R2, does sum in R3 work? 

Thanks for your responce.



Nope. When i Set a fixed value =1987654321 it also adds up to zero. Strange thing is that fields like Oppervlakte (screrenshot) do add up right.



Total with fixed value





Formula with fixed value

We could not reproduce an issue here so far.
What happens when you replace =1987654321 by 1987654321?

What happens when you replace in the original project =SUM(R1:R2;0) by =SUM(R1:R2); 

If a problem persists, please provide some sample source project with which we can reproduce this issue here.

Well I hoped it was just a setting i missed because I'm using a formula instead of direct value.



But for the endresult formula i do:



Updating grid with subtotals rows

procedure TSheet.SHEETCMD_UPDATEFORMULAS_();

var

iDispRow: integer;

begin

if fReportMode then

begin

    fGrid.FixedRows.MinValue;

    iDispRow := fGrid.FixedRows;

    while (iDispRow < fGrid.RowCount) do

    begin

      if IsRegularRow(iDispRow)

      then ReaplynmA1Formulas(iDispRow);



      inc(iDispRow);

    end;

end;

end;



Creating the formulas for grid

procedure TSheet.ReaplynmA1Formulas(const aDispRow:integer);



function RowContainesFxFormula(const aDispRow:integer):boolean;

var

    Column: IROTColumn;

    iColIdx: integer;

    sCell: string;

begin

    result := false;

    if (fLayout.Columns.ByVCol_ID(VCOL_FORMULA, Column) = S_OK) then

    begin

      //test for std. formula number

      iColIdx := Column.GridColumnIndex;

      sCell := fGrid.Cells[iColIdx, aDispRow];

      result := ( (sCell <> '') and

        not (AnsiStrLComp(PChar(sCell), '91<', 3) = 0) );

    end;

end;



var

iColIdx: integer;

cColumns: TSheetColumns;

cim: IClassInstManager;

iVCOL: integer;

Column: IROTColumn;

err: integer;

iRowIdx: integer;

begin

iRowIdx := fGrid.RealRowIndex(aDispRow);

if (fLayout.Columns.QueryInterface(IClassInstManager, cim) = S_OK) and

     (cim.GetClassInst(TObject(cColumns)) = S_OK) then

begin

    if MapVCOL_ID2ColIdx(fGrid, self, VCOL_ENDRESULT, iColIdx) and not

       RowContainesFxFormula(aDispRow) then

    begin

      fGrid.Cells[iColIdx, aDispRow] := EndresultnmA1Formula(aDispRow);

      fGrid.CellValue[iColIdx, aDispRow]; // this will calculate it

    end;

    for iVCOL := VCOL_FIRST_CUSTOM to cColumns.NextVColID - 1 do

      if MapVCOL_ID2ColIdx(fGrid, self, iVCOL, iColIdx) and

        MapColindex2IROTColumn(fGrid, iColIdx, Column) and

        Column.FormulaAssigned then

      begin

        fGrid.Cells[iColIdx, aDispRow] := ColumnFormula2nmA1(iRowIdx, Column.Formula, err);

        fGrid.CellValue[iColIdx, aDispRow]; // this will calculate it

      end;

end;

end;



Creating the Endresult formula

function TSheet.EndresultnmA1Formula(const aDispRow:integer):string;



function _nmA1CellRef(const aVCol,aDispRow:integer):string;

var

    iColIdx: integer;

begin

    if MapVCOL_ID2ColIdx(fGrid, self, aVCol, iColIdx) then

      result := fGrid.CellToName(iColIdx, aDispRow)

    else

      result := '0';

end;



var

sP: string;

sL: string;

sW: string;

sH: string;

sA: string;

sF: string;

begin

sP := _nmA1CellRef(VCOL_PICES, aDispRow);

sL := _nmA1CellRef(VCOL_LENGTH, aDispRow);

sW := _nmA1CellRef(VCOL_WIDTH, aDispRow);

sH := _nmA1CellRef(VCOL_HEIGHT, aDispRow);

sA := _nmA1CellRef(VCOL_AREA, aDispRow);

sF := _nmA1CellRef(VCOL_FREQUENCY, aDispRow);

result :=

     FCL('=if(%0:s+%1:s+%2:s+%3:s+%4:s;(' +                                    // 30

       'if(%0:s;%0:s;1)' +                                                     // 16 - 46

       'if(%1:s;%1:s;1)
' +                                                     // 16 - 62

       'if(%2:s;%2:s;1)' +                                                     // 16 - 78

       'if(%3:s;%3:s;1)
' +                                                     // 16 - 94

//       'if(%4:s;%4:s;1)*' +                                                     // 16 - 110

//       'if(%5:s;%5:s;1)' +                                                      // 15 - 125

       'if(%4:s;%4:s;1)' +                                                     // 16 - 110

     ');0)',                                                                    // 4 - 129

     [sP, sL, sW, sH, sA]); //, sF]);

end;



For the direct value I placed:

result := '=1987654321';




Extra wrapper for filling in the Stringvalues

// ******************************************************

// INPUT : FormatString, Arguments

// OUTPUT : Parsed String

// SPECIAL:

// TYPE   : [ ]private [ ]protected [ ]public [ ]published [ ]local [X]global

//          [ ]class ..name..

// USED IN:

// HINT   : Automat returning the parsed string. Acts like the format function in C++

//          i.e. Format('test \ntest2',[])      returns 'test #13#10test2'

//               Format('test \13\00065 test2',[]) returns 'test #13A test2'

//          021030 OM added support for boolean (%b)

function FCL(const aFormat: string; const aArgs: array of const):string;

begin

result := FormatCLike(aFormat,aArgs);

end;

I'm sorry but a couple of loose snippets really don't help to try to reproduce this. It contains lots of references to parts not in these loose snippets and it would cost a significant amount of time to try to make sense of this and even then, we are missing the component properties & event handlers.
Please provide a ready to use sample source project and send it by email.