New formatting options

set_indent()
set_rotation()
set_shrink()

Author
Copyright

Name

Spreadsheet::WriteExcel - Excel97 pre-release.

Version

This document refers to version 0.49.7 of Spreadsheet::WriteExcel, released April 13, 2003.

Introduction

This is a pre-release version of Spreadsheet::WriteExcel with Excel 97 support.

You should only use this version of the module if you have a specific requirement for some of the new features listed below.

This pre-release version has been tested with Excel97 and Excel2000 and Gnumeric.

Excel5 files and OpenOffice are not supported in this release, see below.

What's new in this release

Fixes to the internal storage of Unicode strings to fix a bug that caused Excel to crash in some cases. Thanks to Dmitry Kochurov for the bug report.

A change to the default format of the string required by the write_unicode() method. The string must now be big-endian UTF-16 instead of little-endian UTF-16. This change is mainly to make it easier to use other CPAN modules which generally default to handling UTF-16 data as big-endian regardless of the platform.

What's new from previous releases

Unicode support, see the write_unicode() method below.

Speed and stability.

The maximum string length has increased from 255 chars to 32767 chars.

The maximum row that can be referenced in a formula has increased from 16384 to 65536.

New Excel 97 format options have been added such as:

    indent   - Specify the amount of indentation in a cell
    rotation - Text can be rotated in degrees from -90 to 90
    shrink   - Text can be shrunk to fit the cell size

What's missing

Updated documentation. The main documentation relates to the Excel 5 version of Spreadsheet::WriteExcel. This document is currently the only documentation of the Excel97 specific features.

Support for other spreadsheet applications. Currently OpenOffice and Star Office can not read these files. This should be fixed soon. I haven't tested it with the latest version of Gnumeric but it works with older versions.

Excel 5 support. The code base is currently split between Excel 5 and Excel 97. These branches will be re-merged when all of the Excel 97 issues have been resolved.

New methods

write_unicode($row, $column, $string, $format)

This method is used to write Unicode strings to a cell in Excel. It is functionally the same as the write_string() method except that the string should be in Unicode format.

The Unicode format required by Excel is UTF-16. No other Unicode format is supported by Excel.

Additionally Spreadsheet::WriteExcel requires that the 16-bit characters are in big-endian or network order. This is generally referred to as UTF-16BE. To write UTF-16 strings in little-endian format use the write_unicode_le() method.

The following is a simple example showing how to write some Unicode strings:

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;
    use Unicode::Map();

    my $workbook  = Spreadsheet::WriteExcel->new('unicode.xls');
    my $worksheet = $workbook->addworksheet();

    # Increase the column width for clarity
    $worksheet->set_column('A:A', 25);

    # Write a Unicode character
    #
    my $smiley = pack "n", 0x263a;

    # Increase the font size for legibility.
    my $big_font = $workbook->addformat(size => 72);

    $worksheet->write_unicode('A3', $smiley, $big_font);

    # Write a phrase in Cyrillic using a hex-encoded string
    #
    my $uni_str = pack "H*", "042d0442043e0020044404400430043704300020043d" .
                             "043000200440044304410441043a043e043c0021";

    $worksheet->write_unicode('A5', $uni_str);

    # Map a string to UTF-16BE using an external module.
    #
    my $map   = Unicode::Map->new("ISO-8859-1");
    my $utf16 = $map->to_unicode("Hello world!");

    $worksheet->write_unicode('A7', $utf16);

The following is an example of creating an Excel file with some Japanese text. You will need to have a Unicode font installed, such as Arial Unicode MS, to view the results:

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;

    my $workbook  = Spreadsheet::WriteExcel->new('unicode.xls');
    my $worksheet = $workbook->addworksheet();

    # It is only required to specify a Unicode font via addformat() if
    # you are using Excel 97. For Excel 2000+ the text will display 
    # with the default font (if you have Unicode fonts installed).
    #
    my $uni_font  = $workbook->addformat(font => 'Arial Unicode MS');

    my $kanji     = pack 'n*', 0x65e5, 0x672c;
    my $katakana  = pack 'n*', 0xff86, 0xff8e, 0xff9d;
    my $hiragana  = pack 'n*', 0x306b, 0x307b, 0x3093;

    $worksheet->write_unicode('A1', $kanji,    $uni_font);
    $worksheet->write_unicode('A2', $katakana, $uni_font);
    $worksheet->write_unicode('A3', $hiragana, $uni_font);

    $worksheet->write('B1', 'Kanji');
    $worksheet->write('B2', 'Katakana');
    $worksheet->write('B3', 'Hiragana');

Note: UTF-8 data is not supported by Excel or Spreadsheet::WriteExcel. UTF-8 strings can be converted to the required UTF-16BE format using one of the many Unicode modules on CPAN. For example Unicode::Map and Unicode::String: http://search.cpan.org/author/MSCHWARTZ/Unicode-Map-0.112/Map.pm and http://search.cpan.org/author/GAAS/Unicode-String-2.06/String.pm

For a full list of the Perl Unicode modules see: http://search.cpan.org/search?query=unicode&mode=all

write_unicode_le($row, $column, $string, $format)

This method is used to write Unicode strings to a cell in Excel.

The string should be comprised of 16-bit characters in little-endian format. This is generally referred to as UTF-16LE.

    my $smiley = pack "v", 0x263a;

    $worksheet->write_unicode_le('A3', $smiley);

UTF-16 data can be changed from little-endian to big-endian format (and vide-versa) as follows:

    $utf16 = pack "n*", unpack "v*", $utf16;

To write UTF-16 strings in big-endian format use the write_unicode() method.

Note, it is slightly faster to write little-endian data via write_unicode_le() than it is to write big-endian data via write_unicode().

New formatting options

set_indent()

    Default state:      Text indentation is off
    Default action:     Indent text 1 level
    Valid args:         Positive integers

This method can be used to indent text. The argument, which should be an integer, is taken as the level of indentation:

    my $format = $workbook->addformat();
    $format->set_indent(2);
    $worksheet->write(0, 0, "This text is indented", $format);

Indentation is a horizontal alignment property. It will override any other horizontal properties but it can be used in conjunction with vertical properties.

set_rotation()

    Default state:      Text rotation is off
    Default action:     None
    Valid args:         Integers in the range -90 to 90 and 270

Set the rotation of the text in a cell. The rotation can be any angle in the range -90 to 90 degrees.

    my $format = $workbook->addformat();
    $format->set_rotation(30);
    $worksheet->write(0, 0, "This text is rotated", $format);

The angle 270 is also supported. This indicates text where the letters run from top to bottom.

set_shrink()

    Default state:      Text shrinking is off
    Default action:     Turn "shrink to fit" on
    Valid args:         1

This method can be used to shrink text so that it fits in a cell.

    my $format = $workbook->addformat();
    $format->set_shrink();
    $worksheet->write(0, 0, "Honey, I shrunk the text!", $format);

Author

John McNamara jmcnamara@cpan.org