Sheet[0].InsertRows not updating formulas above

[Dmeade]

This was a Bad Example

Look at the example 2 posts below

[Joe Griffin]

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

Not a lot Are you adding rows (as you say), or are you adding columns?

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.

[Dmeade]

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

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.

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;

[Joe Griffin]

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 haven't got a clue about version 5 (but I doubt if it's fixed).

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];

to

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];

Then instead of calling the Sheet's .InsertRows

Code: Select all

  XLS.Sheets[0].InsertRows(8, 1);

you need to call the Workbook's .InsertRows

Code: Select all

  // D.P. (Joe) Griffin - 17/Jul/2013
  XLS.InsertRows(0, 8, 1);

Hope that helps, and thanks for the inspiration to dig into the source to investigate this.

Best regards,

[Dmeade]

This works With formula name ranges

Code: Select all

// D.P. (Joe) Griffin - 17/Jul/2013
XLS.InsertRows(0, 8, 1);

Thanks Joe !!!