Name In Formula - xvExcel97

[Dmeade]

Hi
We are converting from Delphi Xe2 XLS RW V4 to Delphi Xe5 XLS RW V5

i have two issues at the moment first is that Names no longer seem to work when working with xvExcel97
im Getting an error

Code: Select all

XLSReadWriteII52.Sheets[0].AsFormula[4,3] := 'Sum(NAMETEST)';  //<<--- This seems to work when working with excel file version xvExcel2007

Error in formula Sum(NAMETEST)
Unknown name NAMETEST.

The Second issue is with xvExcel2007
The name Ranges are not updating correctly with the Insert lines.

Code: Select all

XLSReadWriteII52.InsertRows( 0, 1,1 );            //<<-- Works with Version XLS RW 4
//XLSReadWriteII41.Sheets[0].InsertRows( 1,1 );   //<<-- this didnt work

e.g. Inserting 1 row seems to increase the named range by 5

The Following Code was Working in XLS RW V4

Code: Select all

uses XLSNames4, BIFFRecsII4, XLSFonts4, XLSReadWriteII4, System.SysUtils, Vcl.Forms;

procedure TForm7.btn2Click(Sender: TObject);
var XLSReadWriteII41 : TXLSReadWriteII4;
begin

  XLSReadWriteII41 := TXLSReadWriteII4.Create(nil);
  XLSReadWriteII41.Version := xvExcel97;
  try
    XLSReadWriteII41.Filename := IncludeTrailingPathDelimiter( ExtractFilePath(Application.exename) ) +
                                    'XLSTestV97' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLS' ;
    XLSReadWriteII41.Write;
    assert( FileExists( XLSReadWriteII41.Filename ) , 'File not Created. "'+ XLSReadWriteII41.Filename +'"');
    XLSReadWriteII41.Read;
    XLSReadWriteII41.Sheets[0].AsString[0,0] := 'START';
    XLSReadWriteII41.Sheets[0].AsFloat[3,0]  := 1.0;
    XLSReadWriteII41.Sheets[0].AsString[0,1] := 'END';
    XLSReadWriteII41.Sheets[0].AsFloat[3,1]  := 2.0;
    with XLSReadWriteII41.InternalNames.Add do begin
      name       := 'NAMETEST';
      Definition := 'Sheet1!$D$1:$D$2';
    end;
    XLSReadWriteII41.Write;
    XLSReadWriteII41.Sheets[0].AsString[0,3] := 'Sum Test';
    XLSReadWriteII41.Sheets[0].AsFormula[3,3] := 'Sum(D1:D2)';
    XLSReadWriteII41.Write;
    XLSReadWriteII41.Sheets[0].AsFormula[4,3] := 'Sum(NAMETEST)';
    XLSReadWriteII41.Write;
    XLSReadWriteII41.InsertRows( 0, 1,1 );            //<<-- Works with Version 4
    //XLSReadWriteII41.Sheets[0].InsertRows( 1,1 );   //<<-- this didnt work
    XLSReadWriteII41.Sheets[0].AsString[0,1] := 'INSERTED ROW';
    XLSReadWriteII41.Sheets[0].AsFloat[3,1]  := 1.1;
    XLSReadWriteII41.Write;
  finally
    XLSReadWriteII41.Free;
  end;
end;

This is the New Delphi Xe5 code That doesnt seem to work

Code: Select all

procedure TForm7.btn2Click(Sender: TObject);
var XLSReadWriteII52: TXLSReadWriteII5;
begin

  XLSReadWriteII52 := TXLSReadWriteII5.Create(nil);
  XLSReadWriteII52.Version := xvExcel97;
  //XLSReadWriteII52.Version := xvExcel2007;

  try
    XLSReadWriteII52.Filename := IncludeTrailingPathDelimiter( ExtractFilePath(Application.exename) ) +
                                    'XLSTestV97' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLS' ;
   //                                'XLSTestV2007' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLSX' ;
    XLSReadWriteII52.Write;
    assert( FileExists( XLSReadWriteII52.Filename ) , 'File not Created. "'+ XLSReadWriteII52.Filename +'"');
    XLSReadWriteII52.Read;
    XLSReadWriteII52.Sheets[0].AsString[0,0] := 'START';
    XLSReadWriteII52.Sheets[0].AsFloat[3,0]  := 1.0;
    XLSReadWriteII52.Sheets[0].AsString[0,1] := 'END';
    XLSReadWriteII52.Sheets[0].AsFloat[3,1]  := 2.0;

    XLSReadWriteII52.InternalNames.Add('NAMETEST', XLSReadWriteII51.Sheets[0].Name, 3, 0, 3, 1 ) ;

    XLSReadWriteII52.Write;
    XLSReadWriteII52.Sheets[0].AsString[0,3] := 'Sum Test';
    XLSReadWriteII52.Sheets[0].AsFormula[3,3] := 'Sum(D1:D2)';
    XLSReadWriteII52.Write;
    XLSReadWriteII52.Sheets[0].AsFormula[4,3] := 'Sum(NAMETEST)';  //<<--- This seems to work when working with excel file version xvExcel2007
    XLSReadWriteII52.Write;
    XLSReadWriteII52.InsertRows( 0, 1,1 );            //<<-- Works with Version XLS RW 4
    //XLSReadWriteII41.Sheets[0].InsertRows( 1,1 );   //<<-- this didnt work
    XLSReadWriteII52.Sheets[0].AsString[0,1] := 'INSERTED ROW';
    XLSReadWriteII52.Sheets[0].AsFloat[3,1]  := 1.1;
    XLSReadWriteII52.Write;
  finally
    XLSReadWriteII52.Free;
  end;
end;

Hope this all makes sense
if you have any tips or questions please let me know
Dan

[Dmeade]

Another weird thing

ive added some show messages

Before insert
"NAMETEST" Found "Sheet1!$D$1:$D$2"

After Insert
"NAMETEST" Found "Sheet1!$D$1:$D3"

there is a slight difference the $ is missing in front of the Row2 Number

and when you open the file using Microsoft Excel 2013 the name is showing as =Sheet1!$D$1:$D11

i suspect that the Missing $ is throwing it out

Code: Select all

procedure TForm7.btn2Click(Sender: TObject);
var XLSReadWriteII52: TXLSReadWriteII5;
    Nme : TXLSName;
begin

  XLSReadWriteII52 := TXLSReadWriteII5.Create(nil);
  //XLSReadWriteII52.Version := xvExcel97;
  XLSReadWriteII52.Version := xvExcel2007;
  try
    XLSReadWriteII52.Filename := IncludeTrailingPathDelimiter( ExtractFilePath(Application.exename) ) +
    //                                'XLSTestV97' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLS' ;
                                    'XLSTestV2007' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLSX' ;
    XLSReadWriteII52.Write;
    assert( FileExists( XLSReadWriteII52.Filename ) , 'File not Created. "'+ XLSReadWriteII52.Filename +'"');
    //XLSReadWriteII52.Read;
    XLSReadWriteII52.Sheets[0].AsString[0,0] := 'START';
    XLSReadWriteII52.Sheets[0].AsFloat[3,0]  := 1.0;
    XLSReadWriteII52.Sheets[0].AsString[0,1] := 'END';
    XLSReadWriteII52.Sheets[0].AsFloat[3,1]  := 2.0;

    //XLSReadWriteII52.InternalNames.Add('NAMETEST', XLSReadWriteII51.Sheets[0].Name, 3, 0, 3, 1 ) ;
    XLSReadWriteII52.names.Add( 'NAMETEST', 'Sheet1!$D$1:$D$2' );

    //XLSReadWriteII52.Write;
    XLSReadWriteII52.Sheets[0].AsString[0,3] := 'Sum Test';
    XLSReadWriteII52.Sheets[0].AsFormula[3,3] := 'Sum(D1:D2)';
    //XLSReadWriteII52.Write;

    Nme := XLSReadWriteII52.Names.Find( 'NAMETEST' );

    if Nme = nil then begin
      ShowMessage( '"NAMETEST" not Found' ) ;
    end else begin
      ShowMessage( '"NAMETEST" Found "'+ Nme.Definition +'"' ) ;
    end;

    XLSReadWriteII52.Sheets[0].AsFormula[4,3] := 'Sum(NAMETEST)';
    //XLSReadWriteII52.Write;
    XLSReadWriteII52.InsertRows( 0, 1,1 );            //<<-- Works with Version 4
    //XLSReadWriteII41.Sheets[0].InsertRows( 1,1 );   //<<-- this didnt work
    XLSReadWriteII52.Sheets[0].AsString[0,1] := 'INSERTED ROW';
    XLSReadWriteII52.Sheets[0].AsFloat[3,1]  := 1.1;
    XLSReadWriteII52.Write;

    Nme := XLSReadWriteII52.Names.Find( 'NAMETEST' );
    if Nme = nil then begin
      ShowMessage( '"NAMETEST" not Found' ) ;
    end else begin
      ShowMessage( '"NAMETEST" Found "'+ Nme.Definition +'"' ) ;
    end;

  finally
    XLSReadWriteII52.Free;
  end;
end;

[larsa]

Hello

This is fixed in update 5.20.44

[Dmeade]

With version 5.20.44

i am no longer getting the following error when running version xvExcel97

Error in formula Sum(NAMETEST)
Unknown name NAMETEST.

The Second issue is half resolved. Name definitions after inserting rows
not updating correctly in XLS version xvExcel2007

it is working for when working with xvExcel97

ill ask the question in a new post because you did address the subject for this post