Text '_x3hhM_' leads to corrupt xlsx file

[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.

[larsa]

Hello

This will be fixed in the next update, ready by the beginning of next week.

[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.

[larsa]

Hello

Thank you for your suggestion. Unfortunately, Regex unit is not available in D6, so I created a similar solution without Regex.

[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.

[larsa]

Hello

This is fixed in update 5.20.49

[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.

[larsa]

Hello

There will be a new try to fix it in the next update