UTF8 file open in excel COM

[benDelphi]

hello
i have problem to open UTF8 file in Tworkbook.
Anybody have the solution ?
Regards

 

[whosrdaddy]

what Delphi version?
what office version?

can you describe the problem (error messages...)

please provide more info so we can help you out.

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[benDelphi]

Ok thanks sorry
Delphi 7 with excell 2007 or 2010 .
Open a file wich contains UTF8 characters .
When i open the file directly with Excel , the origin file automaticly go to : ( 650001:Unicode(UTF8)) . the datas are correct on the spreadsheet.
It s not correct with my program ( createObjectfromFile())
the characters are not correct Regards

 

[whosrdaddy]

Well, the only thing I am sure about is that Delphi7 does not natively support unicode. (from D2009 this is the case).
However, since you are using a COM server (excel) this should work.

I don't have D7 (only XE or D5), but I will try to simulate in D5.

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

can you please show how you are creating the server (post the code)?
I suppose you are using a TOleContainer??

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

just did a test with D5/Excel 2007
I can open a file with unicode chars and display them??

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[benDelphi]

Hello
ok thanks for response , what s your method to open the file ?
Regards

 

[whosrdaddy]

procedure TForm1.FormCreate(Sender: TObject);
begin
 OleContainer1.CreateObjectFromFile('c:\temp\uni.xls',false);
 OleContainer1.Doverb(-1);
end;

Please show your code to avoid me stabbing in the dark...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[benDelphi]

Hello , Thanks a lot
my code is ( in the case of import a txt file ) :
CreateObject('Excel.Sheet.8',false);
Regards

 

[whosrdaddy]

aah, you are opening a txt file

please, again, provide more info!
Createobject just creates the COM server, you are not showing what you do to open the file....

/Daddy



-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[benDelphi]

Thanks
I have try CreateObjectFromFile but the resuklts is the same , bad characters in spreasheet

My code :

if(ext = '.TXT') or (ext = '.CSV') then
          begin
            CreateObject('Excel.Sheet.8',false);
			      ExcelApplication1 := TExContext.GetExcelApplication;
            if(ext = '.TXT') then
            begin
              tq := xlTextQualifierNone;
              tabdelim:= True;
              semicdelim := False;
            end
            else
            begin
              tq := xlTextQualifierNone;
              tabdelim:= False;
              semicdelim := True;
            end;
            catf:= TCatalogueFile.Create(Filename);
            with ExcelApplication1 do
            try
              xdf := catf.GetFieldInfo();
              if (ext = '.CSV') then
              begin
                if CopyFile(PAnsiChar(FileName), PAnsiChar(FileName+'.TXT'), True) then
                  Filename:= FileName+'.TXT'
                else raise Exception.Create(Format(rsCSVRenameError, [FileName+'.TXT']));
              end;
              // EBL: correction anomalie Excel: lorsqu'un fichier texte commence par ID,
              // Excel génère une erreur (fichier SYLK invalide) et plante s'il s'agit
              // d'un fichier csv
              if (catf.Data.Count > 0) and (AnsiStartsStr('ID', catf.Data.Strings[0])) then
                raise Exception.Create(rsSYLKError);
              OleVariant(Workbooks).OpenText(Filename, xlWindows, 1,
                       xlDelimited, tq, False, tabdelim, semicdelim,
                       False, False, False, EmptyParam, xdf);
              (Workbooks.Item[Workbooks.Count].Sheets.Item[1] as _worksheet).Cells.EntireRow.Copy((Workbooks.Item[Workbooks.Count -1].Sheets.Item[1] as _worksheet).Range['A1','A1']);
              Workbooks.Item[Workbooks.Count].Close(False,'',EmptyParam,GetUserDefaultLCID);
              if (ext = '.CSV') then
              begin
                catf.FileName:= LeftStr(FileName, Length(FileName)-4);
                catf.Save(tfCSV);
                DeleteFile(Filename);
              end;
            finally
              VarClear(xdf);
              catf.Free;
            end;

 

[whosrdaddy]

can you explain briefly what this code is supposed to do?
It's not clear for me where you actually display the data.

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[benDelphi]

Thanks
The code import a file ( txt ) into a spreadsheet
CAn you tell me just are the method to import txt UTF8 into spreadsheet ?
in excell the fileorigin is automatic put to Unicode , not with my code
Sorry for my bad english and for this code.
Regards

 

[whosrdaddy]

ok, so the culprit is the
Workbook.OpenText function.

if you read this page:

http://msdn.microsoft.com/en-us/library/bb223513(v=office.12).aspx

it states that the 'origin' parameter can be a platform constant OR a codepage.

so this should work for utf-8 files (codepage 65001):

OleVariant(Workbooks).OpenText(Filename, 65001, 1, xlDelimited, tq, False, tabdelim, semicdelim, False, False, False, EmptyParam, xdf);

you might want to check the existance of the BOM (first 2 bytes of a file = $FFFE) to check if the file is indeed unicode.

Cheers,
Daddy


-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[benDelphi]

thanks a lot
Merci beaucoup ( i m french)

 

[whosrdaddy]

pas de problème!

salutations,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!