set_header() in multi-byte string

[Takeshi Iwanami]

Hi, I'm trying to use set_header() method in order to describe in
the multi-byte string(Japanese), but the garble occurs. The
following codes is what I tried. I'd like to let anyone correct
my code.

use strict;
use Encode;
use Unicode::String qw(utf8 utf16);
use Spreadsheet::WriteExcel;
use Jcode;
my $string;

# multi-byte string in euc-jp, the word "worksheet" in Japanese.
my $str1 = "\xa5\xef\xa1\xbc\xa5\xaf\xa5\xb7\xa1\xbc\xa5\xc8";
my $workbook = Spreadsheet::WriteExcel->new("sample.xls");
my $sheet = $workbook->add_worksheet(to_utf16($str1), 1);
$sheet->set_header("&L&\"MS PGothic\"".to_utf16($str1)."&R&P / &N", 0.51);

sub to_utf16 {
return utf8( encode("utf-8",
decode("cp932", Jcode->new($_[0],"euc")->sjis)) )->utf16;
}

Another incident, can it be possible to write vertically using
the vertical fonts of MS PGothic in the cell formatting? The
vertical writing is different from the rotation, which is in the
horizontal writing, from the top to the bottom.

Thanks,
Takeshi Iwanami

[jmcnamara]

Takeshi Iwanami wrote:

> Hi, I'm trying to use set_header() method in order to describe in
> the multi-byte string(Japanese), but the garble occurs. The
> following codes is what I tried. I'd like to let anyone correct
> my code.

Hi,

When dealing with non-ascii codeset in Spreadsheet::WriteExcel it is
best to use UTF-8.

UTF-16 support is there for older versions of Perl. If you are using
Perl 5.8 or later then you will find it easiest to just use UTF-8.

Here is your example in UTF8.

#!/usr/bin/perl -w

use strict;
use Encode;
use Spreadsheet::WriteExcel;

# Multi-byte string in euc-jp, the word "worksheet" in Japanese.
my $str_euc = "\xa5\xef\xa1\xbc\xa5\xaf\xa5\xb7\xa1\xbc\xa5\xc8";
my $str_utf8 = decode('euc-jp', $str_euc);

my $workbook = Spreadsheet::WriteExcel->new('sample6.xls');
my $sheet = $workbook->add_worksheet($str_utf8);

$sheet->set_header('&L&"MS PGothic"'. $str_utf8 .'&R&P / &N',
0.51);
$sheet->write('A1', $str_utf8);

John.
--

[jmcnamara]

Takeshi Iwanami wrote:

> Another incident, can it be possible to write vertically using
> the vertical fonts of MS PGothic in the cell formatting? The
> vertical writing is different from the rotation, which is in the
> horizontal writing, from the top to the bottom.

Hi,

You can get top to bottom alignment using the special case angle 270 in
the set_rotation format property. For example:

#!/usr/bin/perl -w

use strict;
use Encode;
use Spreadsheet::WriteExcel;

# Multi-byte string in euc-jp, the word "worksheet" in Japanese.
my $str_euc = "\xa5\xef\xa1\xbc\xa5\xaf\xa5\xb7\xa1\xbc\xa5\xc8";
my $str_utf8 = decode('euc-jp', $str_euc);

my $workbook = Spreadsheet::WriteExcel->new('sample7.xls');
my $sheet = $workbook->add_worksheet($str_utf8);
my $format = $workbook->add_format(rotation => 270);

$sheet->set_header('&L&"MS PGothic"'. $str_utf8 .'&R&P / &N',
0.51);

$sheet->write('A1', $str_utf8);

$sheet->write('A2', $str_utf8, $format);

John.
--

[Takeshi Iwanami]

Hi, Thank you for your reply.

> # Multi-byte string in euc-jp, the word "worksheet" in Japanese.
> my $str_euc = "\xa5\xef\xa1\xbc\xa5\xaf\xa5\xb7\xa1\xbc\xa5\xc8";
> my $str_utf8 = decode('euc-jp', $str_euc);
>
> my $workbook = Spreadsheet::WriteExcel->new('sample6.xls');
> my $sheet = $workbook->add_worksheet($str_utf8);
>
> $sheet->set_header('&L&"MS PGothic"'. $str_utf8 .'&R&P / &N',
> 0.51);
> $sheet->write('A1', $str_utf8);

This sample resolved the garbling.
In Japanese, the simple conversion to utf-8 causes the wave-dash
-fullwidth-tilde problem, so I tried 2 step conversion, and
worked well.

----

use strict;
use Encode;
use Spreadsheet::WriteExcel;

my $string;

# multi-byte string in euc-jp, the word "worksheet" in Japanese.
my $str1 = "\xa5\xef\xa1\xbc\xa5\xaf\xa5\xb7\xa1\xbc\xa5\xc8";
my $workbook = Spreadsheet::WriteExcel->new("sample.xls");
my $sheet = $workbook->add_worksheet(to_utf8($str1), 1);
$sheet->set_header("&L&\"MS PGothic\"".to_utf8($str1)."&R&P / &N");

sub to_utf8 {
Encode::from_to(my $str = $_[0], "euc-jp", "sjis");
return decode("cp932", $str);
}
----

> my $format = $workbook->add_format(rotation => 270);

> $sheet->write('A2', $str_utf8, $format);

This sample also can be a solution to my problem.

Thank you very much.
Takeshi Iwanami