Page 1 of 1

XLSReadWriteII 5 destroys xls strings when writing TWideMemo

Posted: Thu Mar 10, 2016 11:13 am
by Seeker
Hi, I am using Delphi XE10 on windows7 and I've built an application in which I try to create an xls file with XLSReadWriteII 5 component.
Specifically I am reading data that has been stored previously in an sql table and exporting it to a xls file.
Now, while everything works fine with ASCII characters, when I try to read (and thus write to the relative worksheet) Unicode data I am facing problem whenever I have to parse ntext fields. As all we know ntext sql's fields are handled by Delphi as TWideMemo fields, which is the appropriate data-type field to read Unicode data.
However, XLSReadWriteII 5 component not only does not write TWideMemo field's value in the xls file, but worst, it destroys any string data in any cell of the file that previously has written itself leaving only untouchable any numeric-type data.
And the most bizzare of all is that it does not creating any error during the writing procedure, which i would be able to catch using a try-except scheme and then try to solve it.
On the contrary, it executes the appropriate procedure like everything is fine (as it would be if writing ASCII characters data) and then when you try to open the xls file (either with excel or open-office), it gives you a couple of warning messages and after that you can only see cells with numeric data type and no string at all !!!
Here I give you the code which I use in my application:

Code: Select all

Procedure WriteDataToCell(adoQ:Tadoquery;row,col,fcol:integer); 
var inh:string; data_type:TfieldType;  XLappSun:TXLSReadWriteII5;  sheetSun:Txlsworksheet; 
   
begin
   sheetsun:=xlappsun.SheetByName('sheet1'); 
   data_type:=AdoQ.Fields[fcol].DataType;
   case data_type  of
    ftdate,fttime,ftdatetime,ftTimeStamp: 
                                begin
       		   inh:= formatdatetime('dd/mm/yyyy',AdoQ.Fields[fcol].value);
      		   sheetsun.asdatetime[col,row-1]:=AdoQ.Fields[fcol].value+0;
                                end;
    ftboolean:  begin
	                case AdoQ.Fields[fcol].asboolean of
        		         true : sheetsun.AsInteger[col,row-1]:=1;
        		         false: sheetsun.AsInteger[col,row-1]:=0;
      	                end;
                      end;
    ftSmallint,ftInteger,ftWord,ftAutoInc,ftLargeint: 
                                begin
		   sheetsun.asinteger[col,row-1]:=AdoQ.Fields[fcol].asinteger;
                                end;
    ftmemo,ftFmtMemo,ftwideMemo:
                      begin
                                try
           	                    sheetsun.Asstring[col,row-1]:=AdoQ.Fields[fcol].AsString ;
                                except
                                     sheetsun.Asstring[col,row-1]:='*****';
                                end;
                      end;
    ftblob,ftgraphic :
                      begin
        	              sheetsun.Asstring[col,row-1]:='';
                      end;
    ftFloat,ftCurrency,ftBCD:
                      begin
                              sheetsun.AsFloat[col,row-1]:=AdoQ.Fields[fcol].Value;
                      end;
    else
      try
         sheetsun.Asstring[col,row-1]:=AdoQ.Fields[fcol].AsString;
       except
         sheetsun.Asstring[col,row-1]:='*****';
       end;
   end;
end;
I Also have to say that the data which I store in the ntext field of my sql table is being provided through an RTF Edit-box in the relevant form, so that to be able giving specific formatting to the UNICODE text I want to store (In cases I want to give specific format like Bold, Underline etc...).
Maybe this is the reason of the problem ... I don't know...

Is there any way to solve this problem ?

Thank you in advance

Re: XLSReadWriteII 5 destroys xls strings when writing TWideMemo

Posted: Fri Mar 11, 2016 10:26 am
by larsa
Hello

What is the value of AdoQ.Fields[fcol].AsString when the text not is stored correct?

Re: XLSReadWriteII 5 destroys xls strings when writing TWideMemo

Posted: Fri Mar 11, 2016 10:59 am
by Seeker
Hi Larsa,
The value of AdoQ.Fields[fcol].AsString is the one that has been stored to the relative table of the DB.
What I mean is that, if you put a watch on AdoQ's value you would see that Delphi parses the value correctly no matter if the field is nvarchar or ntext.
However, XLS can not write AdoQ's value to the xls file - which Delphi has already parsed correctly - in TWideMemo data-type case.

If it can help I'll provide you a DML code just to create a simple table for testing reasons and a bunch of Unicode strings which you can put to the table and then parse them both as nvarchar and as ntext so that it might provide better view of the problem.

Here is the DML code
CREATE TABLE [dbo].[UnicodeT] (
[countryid] [int] NOT NULL ,
[Country] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[Comments] [ntext] COLLATE Greek_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And here is the Unicode data which will help you reproduce the problem:
1.Αυτό το αυτοκίνητο κινείτε πολύ γρήγορα και επικίνδυνα σε αυτόν τον στενό δρόμο - Greek
2.This car is very fast and dangerous in this narrow street - English
3.这辆车是在这个狭窄的街道非常快速和危险 - Chinese_simplified
4.Tento vůz je velmi rychlá a nebezpečná v této úzké uličce - Chez
5.ეს მანქანა არის ძალიან სწრაფი და საშიში ამ ვიწრო ქუჩაზე - Georgian
6.המכונית הזאת היא מאוד מהירה ומסוכנת הרחוב הצר הזה - Hebrew
7.Þessi bíll er mjög fljótur og hættulegur í þessum þrönga götu - Icelandic
8.この車はこの狭い通りに非常に高速かつ危険です - Japanese
9.ಈ ಕಾರು ಈ ಕಿರಿದಾದ ರಸ್ತೆಯಲ್ಲಿ ಅತ್ಯಂತ ವೇಗವಾಗಿ ಮತ್ತು ಅಪಾಯಕಾರಿ - Kannada
10.این خودرو بسیار سریع و خطرناک در این خیابان باریک است - Persian

Hope this might help....

Re: XLSReadWriteII 5 destroys xls strings when writing TWideMemo

Posted: Fri Mar 11, 2016 11:30 am
by larsa
Hello

Sorry, I don't have time to create databases...

What I want to see is the string value that you are trying to assign to the worksheet, that is, what is the value you are assigning to sheetsun.Asstring[col,row-1] when the cell value is wrong?

Re: XLSReadWriteII 5 destroys xls strings when writing TWideMemo

Posted: Fri Mar 11, 2016 12:07 pm
by Seeker
Hi Larsa,

I got it.. I will give you an example...
So suppose that the value stored in the DB is :'Þessi bíll er mjög fljótur og hættulegur í þessum þrönga götu'

Now, Delphi has already parsed and put this value to the AdoQ.Fields[fcol].AsString variable (this can be verified by either putting the cursor upon the variable or by creating a watch on that variable and check its value).

Now, XLS component can not write this value to the xls file (with the: sheetsun.Asstring[col,row-1]:=AdoQ.Fields[fcol].AsString equation), neither produces any kind of error to catch. And except of that, it also behaves abnormally and erases every string that may itself have written before in the xls file

Re: XLSReadWriteII 5 destroys xls strings when writing TWideMemo

Posted: Fri Mar 11, 2016 9:46 pm
by KenWhite
Lars: FYI, this question was asked at StackOverflow (http://stackoverflow.com/q/35917113/62576), and I provided code there that demonstrates that the problem is not in XLSRWII5, both by using an array of Unicode strings and an ADO table with the strings in Unicode fields. The code for both methods (including DML to create the table) is in the post there.

Re: XLSReadWriteII 5 destroys xls strings when writing TWideMemo

Posted: Sat Mar 12, 2016 12:26 am
by Seeker
Hi Lars,

Ken is totally right.
After many hours of debugging, i found out that the 3-d party DBMS components I was using were the one that eventually made the whole mess. These were handling input Unicode data such way that when it war retrieved back from the DB and passed to XLSRWII, it wasn't able to recognize and handle thus leading to such malfunctions.
But when using Delphi's components then everything works just fine. So, its crystal-clear that XLSRWII is not the one to blame for.
Case closed.