?
Solved

PB6, Excel OLE and non-English characters

Posted on 2003-03-21
8
Medium Priority
?
728 Views
Last Modified: 2013-12-20
Life was easy when I used to fill my Excel worksheet only with English characters, like Sheet.Cells[1,1].Value="string value". But things changed when I tried to transfer some Russian characters (cp1251) from PB to Excel. Excel's cell looks like I don't have Russian font - letters with accents, etc - like international ASCII 128+ characters. My next step was to discover how Excel stores Russian strings in its cells. I tried to read cell's value from Excel and got totally unreadable crap with "undefined character" boxes. Furthermore, when I tried to transfer one Russian (1,1) value to another (3,3) (through PB), using Sheet.Cells[3,3].Value = Sheet.Cells[1,1].Value - I got another unreadable crap in Excel's cell 3,3 ! Value for (1,1) was, say, "test(eng)+test(rus)" and in (3,3) I got "test(eng)+crap(unknown)".

This stuff drives me insane. Some non-PB people gave me a clue to use win32api function called OemToAnsi in order to convert my strings to some form of Unicode or something. I declared this function like FUNCTION boolean OemToAnsi ( ref string src, ref string dst ) Library "USER32". During test run ...

boolean res

string str1
string str2

str1 = "A"
str2 = "B"

res = OemToAnsi( str1, str2 )

... application crashes with "Error calling external function OemToAnsi". I don't have so-called PowerBuilder-for-Unicode-edition, so I don't have any useful ideas about how to transfer my Russian strings from PB to Excel.

Please help, I'm giving all points that I've got.
0
Comment
Question by:NMi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 5

Accepted Solution

by:
berXpert earned 270 total points
ID: 8204388
Hi NMi,

your function crash becase OemToAnsi was used in Win 16 in a dll called keyboard, so if you are in Win16 change the "user32" for "keyboard" and it'll work.

In Win32 you use OemToCharA:

FUNCTION Long OemToChar( ref string src, ref string dst ) Library "User32" ALIAS FOR "OemToCharA"

With your code but now Long:

--------
Long res

string str1
string str2

str1 = "A"
str2 = "B"

res = OemToChar( str1, str2 )

MessageBox(str1, str2)


----------
I tested and application don't crash.

BTW the opposite function is



FUNCTION Long CharToOem( ref string src, ref string dst ) Library "User32" ALIAS FOR "CharToOemA"


Let me know if it works

BerX
0
 
LVL 2

Author Comment

by:NMi
ID: 8208812
Yeah, _this function_ seems to work. But I this that's not what I need. What I get from CharToOem function is some weird string str2 with the same length as str1 - that's absolutely not the case of Excel - I don't think that str2 gets converted to Unicode. When I see what Excel contains in its cells, it has length between Len and Len*2 of the string within the cell.

Furthermore, it's still a kind of miracle for me that when I simply try to transfer one cell to another (like in original message), I get crap instead of similar cell...
0
 
LVL 5

Expert Comment

by:berXpert
ID: 8212418
I would like to help you more but my Windows is in spanish and the movement between cells doesn't change chars like in you sample, maybe if you send me a XLS with some chars I can try something else.

I did this:

ole_Excel.Application.Cells[3,3].Value = ole_Excel.Application.Cells[1,1].Value

On a cell with : "Hola á ü ÑñÁÚÜ"
and the result was as expected: "Hola á ü ÑñÁÚÜ"

I read that Russian characters need 1.7 bytes average to be represented while latinamerican need only 1.1 on a UTF8 format. Maybe you are losing 1 byte of information while the movement (or getting 1 extra).

I use a function to convert my string into UTF8 and get this:

"Hola á ü ÑñÁÚÜ"

That corresponds with what you said about lenght = len*2

I also read that Excel doest convert UTF8 but it need ISO-8859-1.

Sorry but maybe you wont be able to solve your problem.

BerX
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:NMi
ID: 8216281
WOW !!! I don't know what kind ot b*s*t is that !!!
I just can't get why do the contents of the target cell depend on its position !!! Check this out:

lole_Sheet.Cells[1,2].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[1,3].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[1,4].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,1].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,2].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,3].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,4].Value = lole_Sheet.Cells[1,1].Value

Guess what ? Do I get the same result in my cells ? I'm in deep shock !

http://nmi.ru/tmp/excel-pb.gif

As you can see, we've got original value in cell[1,1]. It is three Russian letters - in transcription that would be ABVabv. Its lenght equals to 6, no surprise. But what I get in other cells is 3 chars long ! Futhermore, the result is highly unstable and unpredictable - I can get another set of results the next time I run my application.
0
 
LVL 2

Author Comment

by:NMi
ID: 8216391
Cool ! My friend told me that the first thing to do is to upgrade to the latest maintenance release. After upgrading to 6.5 build 444, the problem has gone ! :))

Now I'll try to enter my own values to the cells...
0
 
LVL 2

Author Comment

by:NMi
ID: 8216403
... seems like Russian started to work since maintenance release 6.5 :) But anyhow - thanks for the support :)
0
 
LVL 2

Author Comment

by:NMi
ID: 8216411
Not a solution that I need, but still good advice ;)
0
 
LVL 5

Expert Comment

by:berXpert
ID: 8218356
I'm glad you solve it!

Any time :0)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question