If you Create a name that has a range
then insert lines inside that range
the range is not updating properly when using XLSRW.Version = xvExcel2007
if you are working with XLSRW.Version = xvExcel97 it seems to be ok
i suspect that this is the problem
name "NAMETEST" Definition
Before Insert "Sheet1!$D$1:$D$2"
After Insert "Sheet1!$D$1:$D3" <<---- Missing the $3
when you open the XLSX file with Microsoft Excel 2013
the sum(NAMETEST) column is not working correctly
Here is code to reproduce this issue
Unit XLSTestRun
Code: Select all
unit XLSTestRun;
interface
uses Xc12Utils5, XLSReadWriteII5, XLSNames5, SysUtils, typinfo, Vcl.Dialogs;
const EXPECTED_NAME_DEFINITION_VALUE = 'Sheet1!$D$1:$D$3';
procedure XLSNameTest( XLSVersion : TExcelVersion; sFilename : String ) ;
implementation
procedure XLSNameTest( XLSVersion : TExcelVersion; sFilename : String );
var XLSReadWriteII52: TXLSReadWriteII5;
Nme : TXLSName;
dCellE5DefinitionValue, dCellF5DefinitionValue : String;
Procedure ShowAMessage(sString : String);
var sMessage : String;
begin
sMessage := 'XLS.Version = ' + GetEnumName(TypeInfo(TExcelVersion), Ord(XLSVersion)) + sLineBreak +
'XLS.FileName = "'+sFilename+'"' + sLineBreak +
sString ;
ShowMessage( sMessage );
end;
begin
XLSReadWriteII52 := TXLSReadWriteII5.Create(nil);
XLSReadWriteII52.Version := XLSVersion;
XLSReadWriteII52.Filename := sFilename;
try
XLSReadWriteII52.Write;
assert( FileExists( XLSReadWriteII52.Filename ) , 'File not Created. "'+ XLSReadWriteII52.Filename +'"');
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.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)';
Nme := XLSReadWriteII52.Names.Find( 'NAMETEST' );
if Nme = nil then begin
raise exception.Create( 'XLS Name "NAMETEST" not Found');
end;
XLSReadWriteII52.Sheets[0].AsFormula[4,3] := 'Sum(NAMETEST)';
XLSReadWriteII52.InsertRows( 0, 1, 1 );
XLSReadWriteII52.Sheets[0].AsString[0,1] := 'INSERTED ROW';
XLSReadWriteII52.Sheets[0].AsFloat[3,1] := 1.1;
XLSReadWriteII52.Write;
XLSReadWriteII52.names.Add( 'NAMETEST2', 'Sheet1!$D$1:$D$3' );
XLSReadWriteII52.Sheets[0].AsFormula[5,4] := 'Sum(NAMETEST2)';
XLSReadWriteII52.Write;
XLSReadWriteII52.Calculate;
XLSReadWriteII52.Write;
Nme := XLSReadWriteII52.Names.Find( 'NAMETEST' );
if Nme = nil then begin
raise exception.Create( 'XLS Name "NAMETEST" not Found');
end else begin
dCellE5DefinitionValue := Nme.Definition;
if dCellF5DefinitionValue <> EXPECTED_NAME_DEFINITION_VALUE then
ShowAMessage( 'name "NAMETEST" Definition was "'+ dCellE5DefinitionValue +'" Expected "'+ EXPECTED_NAME_DEFINITION_VALUE +'"' )
end;
Nme := XLSReadWriteII52.Names.Find( 'NAMETEST2' );
if Nme = nil then begin
raise exception.Create( 'XLS Name "NAMETEST2" not Found');
end else begin
dCellF5DefinitionValue := Nme.Definition;
if dCellF5DefinitionValue <> EXPECTED_NAME_DEFINITION_VALUE then
ShowAMessage( 'name "NAMETEST2" Definition was "'+ dCellF5DefinitionValue +'" Expected "'+ EXPECTED_NAME_DEFINITION_VALUE +'"' )
end;
finally
XLSReadWriteII52.Free;
end;
end;
end.Code: Select all
procedure TForm7.btn3Click(Sender: TObject);
var slTests : TStringList;
begin
XLSNameTest(xvExcel97, IncludeTrailingPathDelimiter( ExtractFilePath(Application.exename) ) +
'XLSTestV97' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLS' );
XLSNameTest(xvExcel2007, IncludeTrailingPathDelimiter( ExtractFilePath(Application.exename) ) +
'XLSTestV2007' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLSX' );
end;