InsertRows throwing out Name Definition range
Posted: Fri Mar 20, 2015 2:05 am
I'm using XLS RW Component Version 5.20.44
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
calling the unit
Let me know if you have any Questions
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;