This was a Bad Example
Look at the example 2 posts below
Sheet[0].InsertRows not updating formulas above
Sheet[0].InsertRows not updating formulas above
Last edited by Dmeade on Tue Jul 16, 2013 11:38 pm, edited 2 times in total.

 Posts: 13
 Joined: Tue Feb 13, 2007 8:47 am
 Location: West Sussex England
Re: Sheet[0].InsertRows not updating formulas above
Not a lot Are you adding rows (as you say), or are you adding columns?Dmeade wrote:Im using the TXLSReadWriteII4 version 4.00.60
I have a total band at the top of the excel sheet e.g. Field A1 =Sum(F1:X1)
I also have the same formula at Field Y1 e.g. =Sum(F1:X1)
if I insert a new row at J1 the Formula range for Field A1 is not being updated where the Y1 Formula range and position has been updated
e.g. Sheet[0].InsertRows(10, 1)
A1 still equals =Sum(F1:X1) < this hasn't changed
Y1 has changed to Z1 and the range now equals =Sum(F1:Y1) < Which is what I want to happen to the A1 field
is there anyway to do this easily or am I going to have to keep track of the changes and update the A1 Field Manually?
Hope this make sense
I assume you meant .InsertColumns and tried it with 4.00.66 (the latest version) and get the same as you. I tried altering A1 to "=Y1" and that didn't change to "=Z1".
I also tried setting a name for the rane and making A1 "=Sum(TheRange)  It didn't update the range, so I lost the last cell.
Looks like you'll have to keep track of it manually, which is a pain.
Joe Griffin
GerbilSoft Associates Limited
GerbilSoft Associates Limited
Re: Sheet[0].InsertRows not updating formulas above
I've changed the example  hope this helps
Im using the TXLSReadWriteII4 version 4.00.60 on Delphi XE2
we have an excel template that is ran monthly  the Delphi program reads the template and inserts data where it needs to
I have a total band at the top of the excel sheet e.g. Field A1 =Sum(A3:A10)
I also have the same formula at Field A11 e.g. =Sum(A3:A10)
if I insert a new row at A5 the Formula range for Field A1 is not being updated where the A11 Formula range and position has been updated
e.g. Sheet[0].InsertRows(5, 1)
A1 still equals =Sum(A3:A10) < this hasn't changed
A11 has changed to A12 and the range now equals =Sum(A3:A11) < Which is what I want to happen to the A1 field
is there anyway to do this easily  I was hoping for some function that's not obvious
as it does recalculate for the functions below the row insert
Does version 5 have an option to do this if version 4 does not
I have modified the Sample FormatCellsSample if this Helps
In the Procedure AddFormats  ignore the part above this
//The Code Above is creating an example of the monthly template
is there a better way to do this? to keep the formulas in fields above happy?
XLS.Sheets[0].InsertRows(8, 1);
XLS.Sheets[0].MoveCells(0, 9, 2, 9, 0, 8 , [ccoForceAdjust] + CopyAllCells);
XLS.Sheets[0].AsString[0,9] := 'Value 5';
XLS.Sheets[0].AsInteger[1,9] := 5;
Im using the TXLSReadWriteII4 version 4.00.60 on Delphi XE2
we have an excel template that is ran monthly  the Delphi program reads the template and inserts data where it needs to
I have a total band at the top of the excel sheet e.g. Field A1 =Sum(A3:A10)
I also have the same formula at Field A11 e.g. =Sum(A3:A10)
if I insert a new row at A5 the Formula range for Field A1 is not being updated where the A11 Formula range and position has been updated
e.g. Sheet[0].InsertRows(5, 1)
A1 still equals =Sum(A3:A10) < this hasn't changed
A11 has changed to A12 and the range now equals =Sum(A3:A11) < Which is what I want to happen to the A1 field
is there anyway to do this easily  I was hoping for some function that's not obvious
as it does recalculate for the functions below the row insert
Does version 5 have an option to do this if version 4 does not
I have modified the Sample FormatCellsSample if this Helps
Code: Select all
unit Main;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, XLSReadWriteII4, XLSFonts4, CellFormats4, BIFFRecsII4, ShellAPI, XLSUtils4;
type
TfrmMain = class(TForm)
Label1: TLabel;
Button1: TButton;
edFilename: TEdit;
Button2: TButton;
Button3: TButton;
dlgSave: TSaveDialog;
XLS: TXLSReadWriteII4;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
private
procedure AddFormats;
public
{ Public declarations }
end;
var
frmMain: TfrmMain;
implementation
{$R *.dfm}
procedure TfrmMain.AddFormats;
var test : WideString;
begin
XLS.Sheets[0].AsString[0,0] := 'Group 1 Totals Option 1';
XLS.Sheets[0].Cell[0,0].FontStyle := [xfsBold];
XLS.Sheets[0].AsString[0,1] := 'Group 1 Totals Option 2';
XLS.Sheets[0].Cell[0,1].FontStyle := [xfsBold];
XLS.Sheets[0].AsString[0,4] := 'Group 1';
XLS.Sheets[0].Cell[0,4].FontStyle := [xfsBold];
XLS.Sheets[0].AsString[0,5] := 'Value 1';
XLS.Sheets[0].AsInteger[1,5] := 1;
XLS.Sheets[0].AsString[0,6] := 'Value 2';
XLS.Sheets[0].AsInteger[1,6] := 2;
XLS.Sheets[0].AsString[0,7] := 'Value 3';
XLS.Sheets[0].AsInteger[1,7] := 3;
XLS.Sheets[0].AsString[0,8] := 'Value 4';
XLS.Sheets[0].AsInteger[1,8] := 4;
XLS.Sheets[0].AsString[0,9] := 'Group 1 Totals';
XLS.Sheets[0].Cell[0,9].FontStyle := [xfsBold];
XLS.Sheets[0].AsFormula[1, 9] := 'SUM(B6:B9)';
XLS.Sheets[0].Cell[1,9].FontStyle := [xfsBold];
XLS.Sheets[0].AsFormula[1, 0] := 'SUM(B6:B9)';
XLS.Sheets[0].Cell[1,0].FontStyle := [xfsBold];
XLS.Sheets[0].AsFormula[1, 1] := 'B10';
XLS.Sheets[0].Cell[1,1].FontStyle := [xfsBold];
//The Code Above is creating an example of the monthly template
//The Code Below is an example of inserting data into the template
//if you open up the Excel file after you will notice that the top format ranges have not been changed
XLS.Sheets[0].InsertRows(8, 1);
XLS.Sheets[0].MoveCells(0, 9, 2, 9, 0, 8 , [ccoForceAdjust] + CopyAllCells);
XLS.Sheets[0].AsString[0,9] := 'Value 5';
XLS.Sheets[0].AsInteger[1,9] := 5;
end;
procedure TfrmMain.Button1Click(Sender: TObject);
begin
XLS.Filename := edFilename.Text;
AddFormats;
XLS.Write;
ShellExecute(frmMain.Handle, 'Open', PChar(edFilename.Text), PChar(''), nil, 1);
end;
procedure TfrmMain.Button2Click(Sender: TObject);
begin
dlgSave.FileName := edFilename.Text;
if dlgSave.Execute then
edFilename.Text := dlgSave.FileName;
end;
procedure TfrmMain.Button3Click(Sender: TObject);
begin
Close;
end;
end.
//The Code Above is creating an example of the monthly template
is there a better way to do this? to keep the formulas in fields above happy?
XLS.Sheets[0].InsertRows(8, 1);
XLS.Sheets[0].MoveCells(0, 9, 2, 9, 0, 8 , [ccoForceAdjust] + CopyAllCells);
XLS.Sheets[0].AsString[0,9] := 'Value 5';
XLS.Sheets[0].AsInteger[1,9] := 5;

 Posts: 13
 Joined: Tue Feb 13, 2007 8:47 am
 Location: West Sussex England
Re: Sheet[0].InsertRows not updating formulas above
I haven't got a clue about version 5 (but I doubt if it's fixed).Dmeade wrote:is there anyway to do this easily  I was hoping for some function that's not obvious
as it does recalculate for the functions below the row insert
Does version 5 have an option to do this if version 4 does not
I have, however, made a discovery by looking at the source.
There are several different "InsertRow" procedures. In the module "FormulaHandler.pas", there's one which adjusts the row numbers for all named ranges. However, this is NOT called by the "ordinary" XLS.Sheet[0].InsertRows. It is called by the XLS.InsertRows function.
So, to get it to work as required, there are two changes to make.
Firstly, use a named range for the summing.
Change
Code: Select all
XLS.Sheets[0].AsFormula[1, 9] := 'SUM(B6:B9)';
XLS.Sheets[0].Cell[1,9].FontStyle := [xfsBold];
XLS.Sheets[0].AsFormula[1, 0] := 'SUM(B6:B9)';
XLS.Sheets[0].Cell[1,0].FontStyle := [xfsBold];
Code: Select all
// D.P. (Joe) Griffin  17/Jul/2013
with XLS.InternalNames.Add do
begin
Name := 'TestRange';
Definition := 'Sheet1!$B$6:$B$9 ';
end; // with
XLS.Sheets[0].AsFormula[1, 9] := 'SUM(TestRange)';
XLS.Sheets[0].Cell[1,9].FontStyle := [xfsBold];
XLS.Sheets[0].AsFormula[1, 0] := 'SUM(TestRange)';
XLS.Sheets[0].Cell[1,0].FontStyle := [xfsBold];
Code: Select all
XLS.Sheets[0].InsertRows(8, 1);
Code: Select all
// D.P. (Joe) Griffin  17/Jul/2013
XLS.InsertRows(0, 8, 1);
Best regards,
Last edited by Joe Griffin on Thu Jul 18, 2013 9:22 am, edited 1 time in total.
Joe Griffin
GerbilSoft Associates Limited
GerbilSoft Associates Limited
Re: Sheet[0].InsertRows not updating formulas above
This works With formula name ranges
Thanks Joe !!!
Code: Select all
// D.P. (Joe) Griffin  17/Jul/2013
XLS.InsertRows(0, 8, 1);