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?
I created the same situation here on a default TAdvSpreadGrid initialized with:
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
What happens when you replace in the original project =SUM(R1:R2;0) by =SUM(R1:R2);
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.