Search code examples
exceldelphidelphi-7xlsxlsx

XlsReadWriteII Component deletes strings when trying to write UTF8 characters in xls sheet

I am building a project with Delphi 7 in which I use the XLSReadWriteII5 component from Axolot. Now I am trying to create an xls sheet in which I am trying to copy data of specific sql tables in it. Specifically I have a table name "CredentialsT" in which I have the following records:

A/A ServerURL     UserName      Password    Numbers

1   11111         DFGDFSG       4emÜø}ý     275,12073,1780
2   adsf          asdf          7g2ÌkßJ     275,12073,1780
3   sdfasdf       afsadfasdf    4e8@Â:Ïœ     275,12073,1780
4   asdfdsafasd   gggggggg      0‰n>ª3­ý      19504,32539,275

The data in the "Password" column has been encrypted, that is why its contents seem of - somehow - no logic. The "Numbers column" is of string type too.

Now, after I create the XLSReadWrite object, I read the above records and then write them in an xls file which I create with the XLSReadWrite object. However even though no error or warning is being raised through read or write procedure, when I try to open the xls file - with excel - it produces 4 warnings about the confidentiality of the file - in which you just have to click the 'ok/yes' button and the comes up the following fatal error message:

"Discontinued department: Department /xl/sharedStrings.xml XML-error. (Strings) Illegal xml character. Line 83, column 5. Discontinued entries: Cell Information from the section /xl/worksheets/sheet1.xml"

By clicking the "ok/yes" button the xls file opens but keeps only numerical data (the A/A column). Everything else is missing, even the column headers (A/A, ServerURL, etc..). On the other hand if i copy from my server and write in my xls file all the columns except the "Password" one, then everything works like a charm. So, I guess that the whole problem has to do with the encoding. That is because Delphi-7 does not use the UTF8 encoding but the ANSI one.

I tried to find if XLSReadWriteII5 component has any "encoding" property but I failed.

So could anyone provide me a way to workaround this issue?

Solution

  • Encode the encrypted data to a valid sting format.

    Encrypted data is 8-bit bytes and not all 8-bit bytes are UTF-8 characters. Generally if the encrypted data needs to be UTF-8 characters it is encoded to either a Base64 or hexadecimal character string.