Page 1 of 1
Text '_x3hhM_' leads to corrupt xlsx file
Posted: Wed Apr 15, 2015 1:37 pm
by Vladimir Baryshnikov
Hello,
I'm using Delphi 2007 and XLSReadWriteII 5.20.46.
If I insert text '_x3hhM_' into a cell, the xlsx file is created incorrectly. Excel 2007 (and 2010) displayes an error saying that the file was corrupted.
Code: Select all
procedure TfrmMain.btnWriteClick(Sender: TObject);
var
XLS: TXLSReadWriteII5;
begin
XLS := TXLSReadWriteII5.Create(Self);
XLS.DirectRead := False;
XLS.DirectWrite := False;
XLS.ComponentVersion := '50.20.47';
XLS.Version := xvExcel2007;
XLS[0].AsString[0,0] := '_x3hhM_';
XLS.Filename := edWriteFilename.Text;
XLS.Write;
XLS.Free;
end;
Please help me with this problem.
Re: Text '_x3hhM_' leads to corrupt xlsx file
Posted: Thu Apr 16, 2015 6:41 am
by larsa
Hello
This will be fixed in the next update, ready by the beginning of next week.
Re: Text '_x3hhM_' leads to corrupt xlsx file
Posted: Fri Apr 17, 2015 8:38 am
by d3nton
Hi!
I have a similar problem with excel 2013 when i add a string like "test_xflat_test.txt" to an excel file (leads to a corrupt excel file) and solved the problem with a simple regex.
The main problem is that excel encodes strings with "_x + Hex value + _" (and only these strings!),but XLS ReadWrite also changes strings like _x3hhM_ which is not a hex value.
My solution:
unit Xc12DataSST5.pas:
Code: Select all
1.
Include System.Regex in uses.
2.
type TXc12DataSST = class(TXc12Data):
added protected member FRegEx: TRegex;
3.
change constructor:
constructor TXc12DataSST.Create(AStyles: TXc12DataStyleSheet);
const
cExcelEscape = '_x[0-9a-fA-F]{4}_';
begin
FStyles := AStyles;
FFonts := FStyles.Fonts;
FHashTable := TStringHash.Create($FFFF);
FRegEx := TRegex.Create(cExcelEscape);
end;
4. change:
procedure TXc12DataSST.EncodeString(var AText: AxUCString);
var
i: integer;
lMatches: TMatchCollection;
lMatch: TMatch;
begin
i := 0;
lMatches := fregex.Matches(AText);
for lMatch in lMatches do begin
System.Insert('_x005F',AText,lMatch.Index + i*6);
inc(i)
end;
end;
Thats all.
Re: Text '_x3hhM_' leads to corrupt xlsx file
Posted: Mon Apr 20, 2015 1:22 pm
by larsa
Hello
Thank you for your suggestion. Unfortunately, Regex unit is not available in D6, so I created a similar solution without Regex.
Re: Text '_x3hhM_' leads to corrupt xlsx file
Posted: Fri May 15, 2015 9:42 am
by d3nton
Thanks for your fix. Unfortunately your solution without regex seems to be wrong (50.20.48). Our xlsx unit tests fail since
strings like "test_x1001_test" is not escaped to "test_x005F_x1001_test" like expected.
Re: Text '_x3hhM_' leads to corrupt xlsx file
Posted: Mon May 18, 2015 1:39 pm
by larsa
Hello
This is fixed in update 5.20.49
Re: Text '_x3hhM_' leads to corrupt xlsx file
Posted: Fri May 22, 2015 8:35 am
by d3nton
Unfotunately still problems with some values which needs more than one value to escape like:
test_x1111__xfabc_ which should be escaped two times -> test_x0005F_x1111_x0005F_xfabc_ but this is not done.
If there is no easy fix without a regex, maybe you could use the regex solution with {$if CompilerVersion > 14} (= Delphi 6).
I think this will cover the most cases.
Re: Text '_x3hhM_' leads to corrupt xlsx file
Posted: Mon May 25, 2015 7:06 am
by larsa
Hello
There will be a new try to fix it in the next update
