~ubuntu-branches/ubuntu/intrepid/horae/intrepid

This will create an Excel file called <CODE>perl.xls</CODE> with a single worksheet and the text <CODE>"Hi Excel!"</CODE> in the relevant cell. And that's it. Okay, so there is actually a zeroth

338

step as well, but <CODE>use module</CODE> goes without saying. There are also more than 40 examples that come with

339

the distribution and which you can use to get you started. See <A HREF="#EXAMPLES">EXAMPLES</A>.

340

341

342

343

Those of you who read the instructions first and assemble the furniture

344

afterwards will know how to proceed. ;-)

345

346

347

348

<HR>

349

<H1><A NAME="WORKBOOK_METHODS">WORKBOOK METHODS</A></H1>

350

351

The Spreadsheet::WriteExcel module provides an object oriented interface to

352

a new Excel workbook. The following methods are available through a new

353

workbook.

354

355

356

357

<PRE>

358

new()

359

close()

360

set_tempdir()

361

add_worksheet()

362

add_format()

363

set_custom_color()

364

sheets()

365

set_1904()

366

set_codepage()

367

</PRE>

368

369

370

If you are unfamiliar with object oriented interfaces or the way that they

371

are implemented in Perl have a look at <CODE>perlobj</CODE> and <CODE>perltoot</CODE> in the main Perl documentation.

372

373

374

375

<HR>

376

377

378

A new Excel workbook is created using the <CODE>new()</CODE> constructor which accepts either a filename or a filehandle as a parameter.

379

The following example creates a new Excel file based on a filename:

380

381

382

383

<PRE>

384

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

385

my $worksheet = $workbook->add_worksheet();

386

$worksheet->write(0, 0, "Hi Excel!");

387

</PRE>

388

389

390

Here are some other examples of using <CODE>new()</CODE> with filenames:

391

392

393

394

<PRE>

395

my $workbook1 = Spreadsheet::WriteExcel->new($filename);

396

my $workbook2 = Spreadsheet::WriteExcel->new("/tmp/filename.xls");

397

my $workbook3 = Spreadsheet::WriteExcel->new("c:\\tmp\\filename.xls");

398

my $workbook4 = Spreadsheet::WriteExcel->new('c:\tmp\filename.xls');

399

</PRE>

400

401

402

The last two examples demonstrates how to create a file on DOS or Windows

403

where it is necessary to either escape the directory separator <CODE>\</CODE> or to use single quotes to ensure that it isn't interpolated. For more

404

information see <CODE>perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?</CODE>.

405

406

407

408

The <CODE>new()</CODE> constructor returns a Spreadsheet::WriteExcel object that you can use to

409

add worksheets and store data. It should be noted that although <CODE>my</CODE> is not specifically required it defines the scope of the new workbook

410

variable and, in the majority of cases, ensures that the workbook is closed

411

properly without explicitly calling the <CODE>close()</CODE> method.

412

413

414

415

If the file cannot be created, due to file permissions or some other

416

reason, <CODE>new</CODE> will return <CODE>undef</CODE>. Therefore, it is good practice to check the return value of <CODE>new</CODE> before proceeding. As usual the Perl variable <CODE>$!</CODE> will be set if there is a file creation error. You will also see one of the

417

warning messages detailed in <A HREF="#DIAGNOSTICS">DIAGNOSTICS</A>:

418

419

420

421

<PRE>

422

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

423

die "Problems creating new Excel file: $!" unless defined $workbook;

424

</PRE>

425

426

427

You can also pass a valid filehandle to the <CODE>new()</CODE> constructor. For example in a CGI program you could do something like this:

428

429

430

431

<PRE>

432

binmode(STDOUT);

433

my $workbook = Spreadsheet::WriteExcel->new(\*STDOUT);

434

</PRE>

435

436

437

The requirement for <CODE>binmode()</CODE> is explained below.

438

439

440

441

For CGI programs you can also use the special Perl filename <CODE>'-'</CODE> which will redirect the output to STDOUT:

442

443

444

445

<PRE>

446

my $workbook = Spreadsheet::WriteExcel->new('-');

447

</PRE>

448

449

450

See also, the <CODE>cgi.pl</CODE> program in the <CODE>examples</CODE> directory of the distro.

451

452

453

454

However, this special case will not work in <CODE>mod_perl</CODE> programs where you will have to do something like the following:

455

456

457

458

<PRE>

459

# mod_perl 1

460

...

461

tie *XLS, 'Apache';

462

binmode(XLS);

463

my $workbook = Spreadsheet::WriteExcel->new(\*XLS);

464

...

465

</PRE>

466

467

468

<PRE>

469

# mod_perl 2

470

...

471

tie *XLS => $r; # Tie to the Apache::RequestRec object

472

binmode(*XLS);

473

my $workbook = Spreadsheet::WriteExcel->new(\*XLS);

474

...

475

</PRE>

476

477

478

See also, the <CODE>mod_perl1.pl</CODE> and <CODE>mod_perl2.pl</CODE> programs in the <CODE>examples</CODE> directory of the distro.

479

480

481

482

Filehandles can also be useful if you want to stream an Excel file over a

483

socket or if you want to store an Excel file in a scalar.

484

485

486

487

For example here is a way to write an Excel file to a scalar with <CODE>perl 5.8</CODE>:

488

489

490

491

<PRE>

492

#!/usr/bin/perl -w

493

</PRE>

494

495

496

<PRE>

497

use strict;

498

use Spreadsheet::WriteExcel;

499

</PRE>

500

501

502

<PRE>

503

# Requires perl 5.8 or later

504

open my $fh, '>', \my $str or die "Failed to open filehandle: $!";

505

</PRE>

506

507

508

<PRE>

509

my $workbook = Spreadsheet::WriteExcel->new($fh);

510

my $worksheet = $workbook->add_worksheet();

511

</PRE>

512

513

514

<PRE>

515

$worksheet->write(0, 0, "Hi Excel!");

516

</PRE>

517

518

519

<PRE>

520

$workbook->close();

521

</PRE>

522

523

524

<PRE>

525

# The Excel file in now in $str. Remember to binmode() the output

526

# filehandle before printing it.

527

binmode STDOUT;

528

print $str;

529

</PRE>

530

531

532

See also the <CODE>write_to_scalar.pl</CODE> and <CODE>filehandle.pl</CODE> programs in the <CODE>examples</CODE> directory of the distro.

533

534

535

536

Note about the requirement for <CODE>binmode()</CODE>: An Excel file is comprised of binary data. Therefore, if you are using a

537

filehandle you should ensure that you <CODE>binmode()</CODE> it prior to passing it to <CODE>new()</CODE>.You should do this regardless of whether you are on a Windows platform or

538

not. This applies especially to users of perl 5.8 on systems where utf8 is

539

likely to be in operation such as RedHat Linux 9. If your program, either

540

intentionally or not, writes UTF8 data to a filehandle that is passed to <CODE>new()</CODE> it will corrupt the Excel file that is created.

541

542

543

544

You don't have to worry about <CODE>binmode()</CODE> if you are using filenames instead of filehandles. Spreadsheet::WriteExcel

545

performs the <CODE>binmode()</CODE> internally when it converts the filename to a filehandle. For more

546

information about <CODE>binmode()</CODE> see <CODE>perlfunc</CODE> and <CODE>perlopentut</CODE> in the main Perl documentation.

547

548

549

550

<HR>

551

<H2><A NAME="close_">close()</A></H2>

552

553

The <CODE>close()</CODE> method can be used to explicitly close an Excel file.

554

555

556

557

<PRE>

558

$workbook->close();

559

</PRE>

560

561

562

An explicit <CODE>close()</CODE> is required if the file must be closed prior to performing some external

563

action on it such as copying it, reading its size or attaching it to an

564

email.

565

566

567

568

In addition, <CODE>close()</CODE> may be required to prevent perl's garbage collector from disposing of the

569

Workbook, Worksheet and Format objects in the wrong order. Situations where

570

this can occur are:

571

572

573

<UL>

574

<LI>

575

576

If <CODE>my()</CODE> was not used to declare the scope of a workbook variable created using <CODE>new()</CODE>.

577

578

579

<LI>

580

581

If the <CODE>new()</CODE>, <CODE>add_worksheet()</CODE> or <CODE>add_format()</CODE> methods are called in subroutines.

582

583

584

</UL>

585

586

The reason for this is that Spreadsheet::WriteExcel relies on Perl's <CODE>DESTROY</CODE> mechanism to trigger destructor methods in a specific sequence. This may

587

not happen in cases where the Workbook, Worksheet and Format variables are

588

not lexically scoped or where they have different lexical scopes.

589

590

591

592

In general, if you create a file with a size of 0 bytes or you fail to

593

create a file you need to call <CODE>close()</CODE>.

594

595

596

597

The return value of <CODE>close()</CODE> is the same as that returned by perl when it closes the file created by <CODE>new()</CODE>. This allows you to handle error conditions in the usual way:

598

599

600

601

<PRE>

602

$workbook->close() or die "Error closing file: $!";

603

</PRE>

604

605

606

<HR>

607

<H2><A NAME="set_tempdir_">set_tempdir()</A></H2>

608

609

For speed and efficiency <CODE>Spreadsheet::WriteExcel</CODE> stores worksheet data in temporary files prior to assembling the final

610

workbook.

611

612

613

614

If Spreadsheet::WriteExcel is unable to create these temporary files it

615

will store the required data in memory. This can be slow for large files.

616

617

618

619

The problem occurs mainly with IIS on Windows although it could feasibly

620

occur on Unix systems as well. The problem generally occurs because the

621

default temp file directory is defined as <CODE>C:/</CODE> or some other directory that IIS doesn't provide write access to.

622

623

624

625

To check if this might be a problem on a particular system you can run a

626

simple test program with <CODE>-w</CODE> or <CODE>use warnings</CODE>. This will generate a warning if the module cannot create the required

627

temporary files:

628

629

630

631

<PRE>

632

#!/usr/bin/perl -w

633

</PRE>

634

635

636

<PRE>

637

use Spreadsheet::WriteExcel;

638

</PRE>

639

640

641

<PRE>

642

my $workbook = Spreadsheet::WriteExcel->new("test.xls");

643

my $worksheet = $workbook->add_worksheet();

644

</PRE>

645

646

647

To avoid this problem the <CODE>set_tempdir()</CODE> method can be used to specify a directory that is accessible for the

648

creation of temporary files.

649

650

651

652

The <CODE>File::Temp</CODE> module is used to create the temporary files. File::Temp uses <CODE>File::Spec</CODE> to determine an appropriate location for these files such as <CODE>/tmp</CODE> or <CODE>c:\windows\temp</CODE>. You can find out which directory is used on your system as follows:

653

654

655

656

<PRE>

657

perl -MFile::Spec -le "print File::Spec->tmpdir"

658

</PRE>

659

660

661

Even if the default temporary file directory is accessible you may wish to

662

specify an alternative location for security or maintenance reasons:

663

664

665

666

<PRE>

667

$workbook->set_tempdir('/tmp/writeexcel');

668

$workbook->set_tempdir('c:\windows\temp\writeexcel');

669

</PRE>

670

671

672

The directory for the temporary file must exist, <CODE>set_tempdir()</CODE> will not create a new directory.

673

674

675

676

One disadvantage of using the <CODE>set_tempdir()</CODE> method is that on some Windows systems it will limit you to approximately

677

800 concurrent tempfiles. This means that a single program running on one

678

of these systems will be limited to creating a total of 800 workbook and

679

worksheet objects. You can run multiple, non-concurrent programs to work

680

around this if necessary.

681

682

683

684

<HR>

685

<H2><A NAME="add_worksheet_sheetname_">add_worksheet($sheetname)</A></H2>

686

687

At least one worksheet should be added to a new workbook. A worksheet is

688

used to write data into cells:

689

690

691

692

<PRE>

693

$worksheet1 = $workbook->add_worksheet(); # Sheet1

694

$worksheet2 = $workbook->add_worksheet('Foglio2'); # Foglio2

695

$worksheet3 = $workbook->add_worksheet('Data'); # Data

696

$worksheet4 = $workbook->add_worksheet(); # Sheet4

697

</PRE>

698

699

700

If <CODE>$sheetname</CODE> is not specified the default Excel convention will be followed, i.e.

701

Sheet1, Sheet2, etc.

702

703

704

705

The worksheet name must be a valid Excel worksheet name, i.e. it cannot

706

contain any of the following characters, <CODE>[ ] : * ? / \</CODE> and it must be less than 32 characters. In addition, you cannot use the

707

same, case insensitive, <CODE>$sheetname</CODE> for more than one worksheet.

708

709

710

711

On systems with <CODE>perl 5.8</CODE> and later the <CODE>add_worksheet()</CODE> method will also handle strings in Perl's <CODE>utf8</CODE> format.

712

713

714

715

<PRE>

716

$worksheet5 = $workbook->add_worksheet("\x{263a}"); # Smiley

717

</PRE>

718

719

720

<HR>

721

<H2><A NAME="add_chart_ext_chart_data_char">add_chart_ext($chart_data, $chartname)</A></H2>

722

723

This method is use to include externally generated charts in a

724

Spreadsheet::WriteExcel file.

725

726

727

728

<PRE>

729

my $chart = $workbook->add_chart_ext('chart01.bin', 'Chart1');

730

</PRE>

731

732

733

This feature is new and would be best described as experimental. Read <CODE>charts.txt</CODE> in the charts directory of the distro for a full explanation.

734

735

736

737

<HR>

738

<H2><A NAME="add_format_properties_">add_format(%properties)</A></H2>

739

740

The <CODE>add_format()</CODE> method can be used to create new Format objects which are used to apply

741

formatting to a cell. You can either define the properties at creation time

742

via a hash of property values or later via method calls.

743

744

745

746

<PRE>

747

$format1 = $workbook->add_format(%props); # Set properties at creation

748

$format2 = $workbook->add_format(); # Set properties later

749

</PRE>

750

751

752

See the <A HREF="#CELL_FORMATTING">CELL FORMATTING</A> section for more details about Format properties and how to set them.

753

754

755

756

<HR>

757

<H2><A NAME="set_custom_color_index_red_">set_custom_color($index, $red, $green, $blue)</A></H2>

758

759

The <CODE>set_custom_color()</CODE> method can be used to override one of the built-in palette values with a

760

more suitable colour.

761

762

763

764

The value for <CODE>$index</CODE> should be in the range 8..63, see <A HREF="#COLOURS_IN_EXCEL">COLOURS IN EXCEL</A>.

765

766

767

768

The default named colours use the following indices:

769

770

771

772

<PRE>

773

8 => black

774

9 => white

775

10 => red

776

11 => lime

777

12 => blue

778

13 => yellow

779

14 => magenta

780

15 => cyan

781

16 => brown

782

17 => green

783

18 => navy

784

20 => purple

785

22 => silver

786

23 => gray

787

53 => orange

788

</PRE>

789

790

791

A new colour is set using its RGB (red green blue) components. The <CODE>$red</CODE>, <CODE>$green</CODE> and <CODE>$blue</CODE> values must be in the range 0..255. You can determine the required values

792

in Excel using the <CODE>Tools->Options->Colors->Modify</CODE> dialog.

793

794

795

796

The <CODE>set_custom_color()</CODE> workbook method can also be used with a HTML style <CODE>#rrggbb</CODE> hex value:

797

798

799

800

<PRE>

801

$workbook->set_custom_color(40, 255, 102, 0 ); # Orange

802

$workbook->set_custom_color(40, 0xFF, 0x66, 0x00); # Same thing

803

$workbook->set_custom_color(40, '#FF6600' ); # Same thing

804

</PRE>

805

806

807

<PRE>

808

my $font = $workbook->add_format(color => 40); # Use the modified colour

809

</PRE>

810

811

812

The return value from <CODE>set_custom_color()</CODE> is the index of the colour that was changed:

813

814

815

816

<PRE>

817

my $ferrari = $workbook->set_custom_color(40, 216, 12, 12);

818

</PRE>

819

820

821

<PRE>

822

my $format = $workbook->add_format(

823

bg_color => $ferrari,

824

pattern => 1,

825

border => 1

826

);

827

</PRE>

828

829

830

<HR>

831

<H2><A NAME="sheets_0_1_">sheets(0, 1, ...)</A></H2>

832

833

The <CODE>sheets()</CODE> method returns a list, or a sliced list, of the worksheets in a workbook.

834

835

836

837

If no arguments are passed the method returns a list of all the worksheets

838

in the workbook. This is useful if you want to repeat an operation on each

839

worksheet:

840

841

842

843

<PRE>

844

foreach $worksheet ($workbook->sheets()) {

845

print $worksheet->get_name();

846

}

847

</PRE>

848

849

850

You can also specify a slice list to return one or more worksheet objects:

851

852

853

854

<PRE>

855

$worksheet = $workbook->sheets(0);

856

$worksheet->write('A1', "Hello");

857

</PRE>

858

859

860

Or since return value from <CODE>sheets()</CODE> is a reference to a worksheet object you can write the above example as:

861

862

863

864

<PRE>

865

$workbook->sheets(0)->write('A1', "Hello");

866

</PRE>

867

868

869

The following example returns the first and last worksheet in a workbook:

870

871

872

873

<PRE>

874

foreach $worksheet ($workbook->sheets(0, -1)) {

875

# Do something

876

}

877

</PRE>

878

879

880

Array slices are explained in the perldata manpage.

881

882

883

884

<HR>

885

886

887

Excel stores dates as real numbers where the integer part stores the number

888

of days since the epoch and the fractional part stores the percentage of

889

the day. The epoch can be either 1900 or 1904. Excel for Windows uses 1900

890

and Excel for Macintosh uses 1904. However, Excel on either platform will

891

convert automatically between one system and the other.

892

893

894

895

Spreadsheet::WriteExcel stores dates in the 1900 format by default. If you

896

wish to change this you can call the <CODE>set_1904()</CODE> workbook method. You can query the current value by calling the <CODE>get_1904()</CODE> workbook method. This returns 0 for 1900 and 1 for 1904.

897

898

899

900

See also <A HREF="#DATES_IN_EXCEL">DATES IN EXCEL</A> for more information about working with Excel's date system.

901

902

903

904

In general you probably won't need to use <CODE>set_1904()</CODE>.

905

906

907

908

<HR>

909

<H2><A NAME="set_codepage_codepage_">set_codepage($codepage)</A></H2>

910

911

The default code page or character set used by Spreadsheet::WriteExcel is

912

ANSI. This is also the default used by Excel for Windows. Occasionally

913

however it may be necessary to change the code page via the <CODE>set_codepage()</CODE> method.

914

915

916

917

Changing the code page may be required if your are using

918

Spreadsheet::WriteExcel on the Macintosh and you are using characters

919

outside the ASCII 128 character set:

920

921

922

923

<PRE>

924

$workbook->set_codepage(1); # ANSI, MS Windows

925

$workbook->set_codepage(2); # Apple Macintosh

926

</PRE>

927

928

929

The <CODE>set_codepage()</CODE> method is rarely required.

930

931

932

933

<HR>

934

<H1><A NAME="WORKSHEET_METHODS">WORKSHEET METHODS</A></H1>

935

936

A new worksheet is created by calling the <CODE>add_worksheet()</CODE> method from a workbook object:

937

938

939

940

<PRE>

941

$worksheet1 = $workbook->add_worksheet();

942

$worksheet2 = $workbook->add_worksheet();

943

</PRE>

944

945

946

The following methods are available through a new worksheet:

947

948

949

950

<PRE>

951

write()

952

write_number()

953

write_string()

954

write_unicode()

955

write_unicode_le()

956

keep_leading_zeros()

957

write_blank()

958

write_row()

959

write_col()

960

write_url()

961

write_url_range()

962

write_formula()

963

store_formula()

964

repeat_formula()

965

add_write_handler()

966

insert_bitmap()

967

get_name()

968

activate()

969

select()

970

set_first_sheet()

971

protect()

972

set_selection()

973

set_row()

974

set_column()

975

outline_settings()

976

freeze_panes()

977

thaw_panes()

978

merge_range()

979

set_zoom()

980

</PRE>

981

982

983

<HR>

984

<H2><A NAME="Cell_notation">Cell notation</A></H2>

985

986

Spreadsheet::WriteExcel supports two forms of notation to designate the

987

position of cells: Row-column notation and A1 notation.

988

989

990

991

Row-column notation uses a zero based index for both row and column while

992

A1 notation uses the standard Excel alphanumeric sequence of column letter

993

and 1-based row. For example:

994

995

996

997

<PRE>

998

(0, 0) # The top left cell in row-column notation.

999

('A1') # The top left cell in A1 notation.

1000

</PRE>

1001

1002

1003

<PRE>

1004

(1999, 29) # Row-column notation.

1005

('AD2000') # The same cell in A1 notation.

1006

</PRE>

1007

1008

1009

Row-column notation is useful if you are referring to cells

1010

programmatically:

1011

1012

1013

1014

<PRE>

1015

for my $i (0 .. 9) {

1016

$worksheet->write($i, 0, 'Hello'); # Cells A1 to A10

1017

}

1018

</PRE>

1019

1020

1021

A1 notation is useful for setting up a worksheet manually and for working

1022

with formulas:

1023

1024

1025

1026

<PRE>

1027

$worksheet->write('H1', 200);

1028

$worksheet->write('H2', '=H1+1');

1029

</PRE>

1030

1031

1032

In formulas and applicable methods you can also use the <CODE>A:A</CODE> column notation:

1033

1034

1035

1036

<PRE>

1037

$worksheet->write('A1', '=SUM(B:B)');

1038

</PRE>

1039

1040

1041

The <CODE>Spreadsheet::WriteExcel::Utility</CODE> module that is included in the distro contains helper functions for dealing

1042

with A1 notation, for example:

1043

1044

1045

1046

<PRE>

1047

use Spreadsheet::WriteExcel::Utility;

1048

</PRE>

1049

1050

1051

<PRE>

1052

($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)

1053

$str = xl_rowcol_to_cell(1, 2); # C2

1054

</PRE>

1055

1056

1057

For simplicity, the parameter lists for the worksheet method calls in the

1058

following sections are given in terms of row-column notation. In all cases

1059

it is also possible to use A1 notation.

1060

1061

1062

1063

Note: in Excel it is also possible to use a R1C1 notation. This is not

1064

supported by Spreadsheet::WriteExcel.

1065

1066

1067

1068

<HR>

1069

<H2><A NAME="write_row_column_token_fo">write($row, $column, $token, $format)</A></H2>

1070

1071

Excel makes a distinction between data types such as strings, numbers,

1072

blanks, formulas and hyperlinks. To simplify the process of writing data

1073

the <CODE>write()</CODE> method acts as a general alias for several more specific methods:

1074

1075

1076

1077

<PRE>

1078

write_string()

1079

write_number()

1080

write_blank()

1081

write_formula()

1082

write_url()

1083

write_row()

1084

write_col()

1085

</PRE>

1086

1087

1088

The general rule is that if the data looks like a something then a something is written. Here are some examples in both row-column and A1 notation:

1089

1090

1091

1092

<PRE>

1093

# Same as:

1094

$worksheet->write(0, 0, "Hello" ); # write_string()

1095

$worksheet->write(1, 0, 'One' ); # write_string()

1096

$worksheet->write(2, 0, 2 ); # write_number()

1097

$worksheet->write(3, 0, 3.00001 ); # write_number()

1098

$worksheet->write(4, 0, "" ); # write_blank()

1099

$worksheet->write(5, 0, '' ); # write_blank()

1100

$worksheet->write(6, 0, undef ); # write_blank()

1101

$worksheet->write(7, 0 ); # write_blank()

1102

$worksheet->write(8, 0, 'http://www.perl.com/'); # write_url()

1103

$worksheet->write('A9', 'ftp://ftp.cpan.org/' ); # write_url()

1104

$worksheet->write('A10', 'internal:Sheet1!A1' ); # write_url()

1105

$worksheet->write('A11', 'external:c:\foo.xls' ); # write_url()

1106

$worksheet->write('A12', '=A3 + 3*A4' ); # write_formula()

1107

$worksheet->write('A13', '=SIN(PI()/4)' ); # write_formula()

1108

$worksheet->write('A14', \@array ); # write_row()

1109

$worksheet->write('A15', [\@array] ); # write_col()

1110

</PRE>

1111

1112

1113

<PRE>

1114

# And if the keep_leading_zeros property is set:

1115

$worksheet->write('A16, 2 ); # write_number()

1116

$worksheet->write('A17, 02 ); # write_string()

1117

$worksheet->write('A18, 00002 ); # write_string()

1118

</PRE>

1119

1120

1121

The "looks like" rule is defined by regular expressions:

1122

1123

1124

1125

<CODE>write_number()</CODE> if <CODE>$token</CODE> is a number based on the following regex: <CODE>$token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/</CODE>.

1126

1127

1128

1129

<CODE>write_string()</CODE> if <CODE>keep_leading_zeros()</CODE> is set and <CODE>$token</CODE> is an integer with leading zeros based on the following regex: <CODE>$token =~ /^0\d+$/</CODE>.

1130

1131

1132

1133

<CODE>write_blank()</CODE> if <CODE>$token</CODE> is undef or a blank string: <CODE>undef</CODE>, <CODE>""</CODE> or <CODE>''</CODE>.

1134

1135

1136

1137

<CODE>write_url()</CODE> if <CODE>$token</CODE> is a http, https, ftp or mailto URL based on the following regexes: <CODE>$token =~ m|^[fh]tt?ps?://|</CODE> or <CODE>$token =~ m|^mailto:|</CODE>.

1138

1139

1140

1141

<CODE>write_url()</CODE> if <CODE>$token</CODE> is an internal or external sheet reference based on the following regex: <CODE>$token =~ m[^(in|ex)ternal:]</CODE>.

1142

1143

1144

1145

<CODE>write_formula()</CODE> if the first character of <CODE>$token</CODE> is <CODE>"="</CODE>.

1146

1147

1148

1149

<CODE>write_row()</CODE> if <CODE>$token</CODE> is an array ref.

1150

1151

1152

1153

<CODE>write_col()</CODE> if <CODE>$token</CODE> is an array ref of array refs.

1154

1155

1156

1157

<CODE>write_string()</CODE> if none of the previous conditions apply.

1158

1159

1160

1161

The <CODE>$format</CODE> parameter is optional. It should be a valid Format object, see <A HREF="#CELL_FORMATTING">CELL FORMATTING</A>:

1162

1163

1164

1165

<PRE>

1166

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

1167

$format->set_bold();

1168

$format->set_color('red');

1169

$format->set_align('center');

1170

</PRE>

1171

1172

1173

<PRE>

1174

$worksheet->write(4, 0, "Hello", $format ); # Formatted string

1175

</PRE>

1176

1177

1178

The <CODE>write()</CODE> method will ignore empty strings or <CODE>undef</CODE> tokens unless a format is also supplied. As such you needn't worry about

1179

special handling for empty or <CODE>undef</CODE> values in your data. See also the <CODE>write_blank()</CODE> method.

1180

1181

1182

1183

One problem with the <CODE>write()</CODE> method is that occasionally data looks like a number but you don't want it

1184

treated as a number. For example, zip codes or ID numbers often start with

1185

a leading zero. If you write this data as a number then the leading

1186

<CODE>zero(s)</CODE> will be stripped. You can change this default

1187

behaviour by using the <CODE>keep_leading_zeros()</CODE> method. While this property is in place any integers with leading zeros

1188

will be treated as strings and the zeros will be preserved. See the <CODE>keep_leading_zeros()</CODE> section for a full discussion of this issue.

1189

1190

1191

1192

You can also add your own data handlers to the <CODE>write()</CODE> method using <CODE>add_write_handler()</CODE>.

1193

1194

1195

1196

On systems with <CODE>perl 5.8</CODE> and later the <CODE>write()</CODE> method will also handle Unicode strings in Perl's <CODE>utf8</CODE> format.

1197

1198

1199

1200

The <CODE>write</CODE> methods return:

1201

1202

1203

1204

<PRE>

1205

0 for success.

1206

-1 for insufficient number of arguments.

1207

-2 for row or column out of bounds.

1208

-3 for string too long.

1209

</PRE>

1210

1211

1212

<HR>

1213

<H2><A NAME="write_number_row_column_num">write_number($row, $column, $number, $format)</A></H2>

1214

1215

Write an integer or a float to the cell specified by <CODE>$row</CODE> and <CODE>$column</CODE>:

1216

1217

1218

1219

<PRE>

1220

$worksheet->write_number(0, 0, 123456);

1221

$worksheet->write_number('A2', 2.3451);

1222

</PRE>

1223

1224

1225

See the note about <A HREF="#Cell_notation">Cell notation</A>. The <CODE>$format</CODE> parameter is optional.

1226

1227

1228

1229

In general it is sufficient to use the <CODE>write()</CODE> method.

1230

1231

1232

1233

<HR>

1234

<H2><A NAME="write_string_row_column_str">write_string($row, $column, $string, $format)</A></H2>

1235

1236

Write a string to the cell specified by <CODE>$row</CODE> and <CODE>$column</CODE>:

1237

1238

1239

1240

<PRE>

1241

$worksheet->write_string(0, 0, "Your text here" );

1242

$worksheet->write_string('A2', "or here" );

1243

</PRE>

1244

1245

1246

The maximum string size is 32767 characters. However the maximum string

1247

segment that Excel can display in a cell is 1000. All 32767 characters can

1248

be displayed in the formula bar.

1249

1250

1251

1252

The <CODE>$format</CODE> parameter is optional.

1253

1254

1255

1256

On systems with <CODE>perl 5.8</CODE> and later the <CODE>write()</CODE> method will also handle strings in Perl's <CODE>utf8</CODE> format. With older perls you can also write Unicode in <CODE>UTF16</CODE> format via the <CODE>write_unicode()</CODE> method. See also the <CODE>unicode_*.pl</CODE> programs in the examples directory of the distro.

1257

1258

1259

1260

In general it is sufficient to use the <CODE>write()</CODE> method. However, you may sometimes wish to use the <CODE>write_string()</CODE> method to write data that looks like a number but that you don't want

1261

treated as a number. For example, zip codes or phone numbers:

1262

1263

1264

1265

<PRE>

1266

# Write as a plain string

1267

$worksheet->write_string('A1', '01209');

1268

</PRE>

1269

1270

1271

However, if the user edits this string Excel may convert it back to a

1272

number. To get around this you can use the Excel text format <CODE>@</CODE>:

1273

1274

1275

1276

<PRE>

1277

# Format as a string. Doesn't change to a number when edited

1278

my $format1 = $workbook->add_format(num_format => '@');

1279

$worksheet->write_string('A2', '01209', $format1);

1280

</PRE>

1281

1282

1283

See also the note about <A HREF="#Cell_notation">Cell notation</A>.

1284

1285

1286

1287

<HR>

1288

<H2><A NAME="write_unicode_row_column_st">write_unicode($row, $column, $string, $format)</A></H2>

1289

1290

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

1291

functionally the same as the <CODE>write_string()</CODE> method except that the string should be in UTF-16 Unicode format.

1292

1293

1294

1295

Note: on systems with <CODE>perl 5.8</CODE> and later the <CODE>write()</CODE> and <CODE>write_string()</CODE>methods will also handle strings in Perl's <CODE>utf8</CODE> format. With older perls you must use the <CODE>write_unicode()</CODE> method.

1296

1297

1298

1299

The Unicode format required by Excel is UTF-16. Additionally <CODE>Spreadsheet::WriteExcel</CODE> requires that the 16-bit characters are in big-endian order. This is

1300

generally referred to as UTF-16BE. To write UTF-16 strings in little-endian

1301

format use the <CODE>write_unicode_le()</CODE> method.

1302

1303

1304

1305

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

1306

strings:

1307

1308

1309

1310

<PRE>

1311

#!/usr/bin/perl -w

1312

</PRE>

1313

1314

1315

<PRE>

1316

use strict;

1317

use Spreadsheet::WriteExcel;

1318

use Unicode::Map();

1319

</PRE>

1320

1321

1322

<PRE>

1323

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

1324

my $worksheet = $workbook->add_worksheet();

1325

</PRE>

1326

1327

1328

<PRE>

1329

# Increase the column width for clarity

1330

$worksheet->set_column('A:A', 25);

1331

</PRE>

1332

1333

1334

<PRE>

1335

# Write a Unicode character

1336

1337

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

1338

</PRE>

1339

1340

1341

<PRE>

1342

# Increase the font size for legibility.

1343

my $big_font = $workbook->add_format(size => 72);

1344

</PRE>

1345

1346

1347

<PRE>

1348

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

1349

</PRE>

1350

1351

1352

<PRE>

1353

# Write a phrase in Cyrillic using a hex-encoded string

1354

1355

my $uni_str = pack "H*", "042d0442043e0020044404400430043704300020043d" .

1356

"043000200440044304410441043a043e043c0021";

1357

</PRE>

1358

1359

1360

<PRE>

1361

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

1362

</PRE>

1363

1364

1365

<PRE>

1366

# Map a string to UTF-16BE using an external module.

1367

1368

my $map = Unicode::Map->new("ISO-8859-1");

1369

my $utf16 = $map->to_unicode("Hello world!");

1370

</PRE>

1371

1372

1373

<PRE>

1374

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

1375

</PRE>

1376

1377

1378

The following is an example of creating an Excel file with some Japanese

1379

text. You will need to have a Unicode font installed, such as <CODE>Arial Unicode MS</CODE>, to view the results:

1380

1381

1382

1383

<PRE>

1384

#!/usr/bin/perl -w

1385

</PRE>

1386

1387

1388

<PRE>

1389

use strict;

1390

use Spreadsheet::WriteExcel;

1391

</PRE>

1392

1393

1394

<PRE>

1395

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

1396

my $worksheet = $workbook->add_worksheet();

1397

</PRE>

1398

1399

1400

<PRE>

1401

# It is only required to specify a Unicode font via add_format() if

1402

# you are using Excel 97. For Excel 2000+ the text will display

1403

# with the default font (if you have Unicode fonts installed).

1404

1405

my $uni_font = $workbook->add_format(font => 'Arial Unicode MS');

1406

</PRE>

1407

1408

1409

<PRE>

1410

my $kanji = pack 'n*', 0x65e5, 0x672c;

1411

my $katakana = pack 'n*', 0xff86, 0xff8e, 0xff9d;

1412

my $hiragana = pack 'n*', 0x306b, 0x307b, 0x3093;

1413

</PRE>

1414

1415

1416

<PRE>

1417

$worksheet->write_unicode('A1', $kanji, $uni_font);

1418

$worksheet->write_unicode('A2', $katakana, $uni_font);

1419

$worksheet->write_unicode('A3', $hiragana, $uni_font);

1420

</PRE>

1421

1422

1423

<PRE>

1424

$worksheet->write('B1', 'Kanji');

1425

$worksheet->write('B2', 'Katakana');

1426

$worksheet->write('B3', 'Hiragana');

1427

</PRE>

1428

1429

1430

Note: You can convert ascii encodings to the required UTF-16BE format using

1431

one of the many Unicode modules on CPAN. For example <CODE>Unicode::Map</CODE> and <CODE>Unicode::String</CODE>: <A

1432

HREF="http://search.cpan.org/author/MSCHWARTZ/Unicode-Map-0.112/Map.pm">http://search.cpan.org/author/MSCHWARTZ/Unicode-Map-0.112/Map.pm</A>

1433

and <A

1434

HREF="http://search.cpan.org/author/GAAS/Unicode-String-2.06/String.pm">http://search.cpan.org/author/GAAS/Unicode-String-2.06/String.pm</A>

1435

1436

1437

1438

1439

For a full list of the Perl Unicode modules see: <A

1440

HREF="http://search.cpan.org/search?query=unicode&mode=all">http://search.cpan.org/search?query=unicode&mode=all</A>

1441

1442

1443

1444

1445

See also the <CODE>unicode_*.pl</CODE> programs in the examples directory of the distro.

1446

1447

1448

1449

<HR>

1450

<H2><A NAME="write_unicode_le_row_column_">write_unicode_le($row, $column, $string, $format)</A></H2>

1451

1452

This method is the same as <CODE>write_unicode()</CODE> except that the string should be 16-bit characters in little-endian format.

1453

This is generally referred to as UTF-16LE.

1454

1455

1456

1457

UTF-16 data can be changed from little-endian to big-endian format (and

1458

vice-versa) as follows:

1459

1460

1461

1462

<PRE>

1463

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

1464

</PRE>

1465

1466

1467

Note, it is slightly faster to write little-endian data via

1468

<CODE>write_unicode_le()</CODE> than it is to write big-endian data via

1469

<CODE>write_unicode().</CODE>

1470

1471

1472

1473

<HR>

1474

<H2><A NAME="keep_leading_zeros_">keep_leading_zeros()</A></H2>

1475

1476

This method changes the default handling of integers with leading zeros

1477

when using the <CODE>write()</CODE> method.

1478

1479

1480

1481

The <CODE>write()</CODE> method uses regular expressions to determine what type of data to write to

1482

an Excel worksheet. If the data looks like a number it writes a number

1483

using <CODE>write_number()</CODE>. One problem with this approach is that occasionally data looks like a

1484

number but you don't want it treated as a number.

1485

1486

1487

1488

Zip codes and ID numbers, for example, often start with a leading zero. If

1489

you write this data as a number then the leading <CODE>zero(s)</CODE> will

1490

be stripped. This is the also the default behaviour when you enter data

1491

manually in Excel.

1492

1493

1494

1495

To get around this you can use one of three options. Write a formatted

1496

number, write the number as a string or use the <CODE>keep_leading_zeros()</CODE> method to change the default behaviour of <CODE>write()</CODE>:

1497

1498

1499

1500

<PRE>

1501

# Implicitly write a number, the leading zero is removed: 1209

1502

$worksheet->write('A1', '01209');

1503

</PRE>

1504

1505

1506

<PRE>

1507

# Write a zero padded number using a format: 01209

1508

my $format1 = $workbook->add_format(num_format => '00000');

1509

$worksheet->write('A2', '01209', $format1);

1510

</PRE>

1511

1512

1513

<PRE>

1514

# Write explicitly as a string: 01209

1515

$worksheet->write_string('A3', '01209');

1516

</PRE>

1517

1518

1519

<PRE>

1520

# Write implicitly as a string: 01209

1521

$worksheet->keep_leading_zeros();

1522

$worksheet->write('A4', '01209');

1523

</PRE>

1524

1525

1526

The above code would generate a worksheet that looked like the following:

1527

1528

1529

1530

<PRE>

1531

-----------------------------------------------------------

1532

| | A | B | C | D | ...

1533

-----------------------------------------------------------

1534

| 1 | 1209 | | | | ...

1535

| 2 | 01209 | | | | ...

1536

| 3 | 01209 | | | | ...

1537

| 4 | 01209 | | | | ...

1538

</PRE>

1539

1540

1541

The examples are on different sides of the cells due to the fact that Excel

1542

displays strings with a left justification and numbers with a right

1543

justification by default. You can change this by using a format to justify

1544

the data, see <A HREF="#CELL_FORMATTING">CELL FORMATTING</A>.

1545

1546

1547

1548

It should be noted that if the user edits the data in examples <CODE>A3</CODE> and <CODE>A4</CODE> the strings will revert back to numbers. Again this is Excel's default

1549

behaviour. To avoid this you can use the text format <CODE>@</CODE>:

1550

1551

1552

1553

<PRE>

1554

# Format as a string (01209)

1555

my $format2 = $workbook->add_format(num_format => '@');

1556

$worksheet->write_string('A5', '01209', $format2);

1557

</PRE>

1558

1559

1560

The <CODE>keep_leading_zeros()</CODE> property is off by default. The <CODE>keep_leading_zeros()</CODE> method takes 0 or 1 as an argument. It defaults to 1 if an argument isn't

1561

specified:

1562

1563

1564

1565

<PRE>

1566

$worksheet->keep_leading_zeros(); # Set on

1567

$worksheet->keep_leading_zeros(1); # Set on

1568

$worksheet->keep_leading_zeros(0); # Set off

1569

</PRE>

1570

1571

1572

See also the <CODE>add_write_handler()</CODE> method.

1573

1574

1575

1576

<HR>

1577

<H2><A NAME="write_blank_row_column_form">write_blank($row, $column, $format)</A></H2>

1578

1579

Write a blank cell specified by <CODE>$row</CODE> and <CODE>$column</CODE>:

1580

1581

1582

1583

<PRE>

1584

$worksheet->write_blank(0, 0, $format);

1585

</PRE>

1586

1587

1588

This method is used to add formatting to a cell which doesn't contain a

1589

string or number value.

1590

1591

1592

1593

Excel differentiates between an "Empty" cell and a "Blank" cell. An "Empty"

1594

cell is a cell which doesn't contain data whilst a "Blank" cell is a cell

1595

which doesn't contain data but does contain formatting. Excel stores

1596

"Blank" cells but ignores "Empty" cells.

1597

1598

1599

1600

As such, if you write an empty cell without formatting it is ignored:

1601

1602

1603

1604

<PRE>

1605

$worksheet->write('A1', undef, $format); # write_blank()

1606

$worksheet->write('A2', undef ); # Ignored

1607

</PRE>

1608

1609

1610

This seemingly uninteresting fact means that you can write arrays of data

1611

without special treatment for undef or empty string values.

1612

1613

1614

1615

See the note about <A HREF="#Cell_notation">Cell notation</A>.

1616

1617

1618

1619

<HR>

1620

<H2><A NAME="write_row_row_column_array_">write_row($row, $column, $array_ref, $format)</A></H2>

1621

1622

The <CODE>write_row()</CODE> method can be used to write a 1D or 2D array of data in one go. This is

1623

useful for converting the results of a database query into an Excel

1624

worksheet. You must pass a reference to the array of data rather than the

1625

array itself. The <CODE>write()</CODE> method is then called for each element of the data. For example:

1626

1627

1628

1629

<PRE>

1630

@array = ('awk', 'gawk', 'mawk');

1631

$array_ref = \@array;

1632

</PRE>

1633

1634

1635

<PRE>

1636

$worksheet->write_row(0, 0, $array_ref);

1637

</PRE>

1638

1639

1640

<PRE>

1641

# The above example is equivalent to:

1642

$worksheet->write(0, 0, $array[0]);

1643

$worksheet->write(0, 1, $array[1]);

1644

$worksheet->write(0, 2, $array[2]);

1645

</PRE>

1646

1647

1648

Note: For convenience the <CODE>write()</CODE> method behaves in the same way as <CODE>write_row()</CODE> if it is passed an array reference. Therefore the following two method

1649

calls are equivalent:

1650

1651

1652

1653

<PRE>

1654

$worksheet->write_row('A1', $array_ref); # Write a row of data

1655

$worksheet->write( 'A1', $array_ref); # Same thing

1656

</PRE>

1657

1658

1659

As with all of the write methods the <CODE>$format</CODE> parameter is optional. If a format is specified it is applied to all the

1660

elements of the data array.

1661

1662

1663

1664

Array references within the data will be treated as columns. This allows

1665

you to write 2D arrays of data in one go. For example:

1666

1667

1668

1669

<PRE>

1670

@eec = (

1671

['maggie', 'milly', 'molly', 'may' ],

1672

[13, 14, 15, 16 ],

1673

['shell', 'star', 'crab', 'stone']

1674

);

1675

</PRE>

1676

1677

1678

<PRE>

1679

$worksheet->write_row('A1', \@eec);

1680

</PRE>

1681

1682

1683

Would produce a worksheet as follows:

1684

1685

1686

1687

<PRE>

1688

-----------------------------------------------------------

1689

| | A | B | C | D | E | ...

1690

-----------------------------------------------------------

1691

| 1 | maggie | 13 | shell | ... | ... | ...

1692

| 2 | milly | 14 | star | ... | ... | ...

1693

| 3 | molly | 15 | crab | ... | ... | ...

1694

| 4 | may | 16 | stone | ... | ... | ...

1695

| 5 | ... | ... | ... | ... | ... | ...

1696

| 6 | ... | ... | ... | ... | ... | ...

1697

</PRE>

1698

1699

1700

To write the data in a row-column order refer to the <CODE>write_col()</CODE> method below.

1701

1702

1703

1704

Any <CODE>undef</CODE> values in the data will be ignored unless a format is applied to the data,

1705

in which case a formatted blank cell will be written. In either case the

1706

appropriate row or column value will still be incremented.

1707

1708

1709

1710

To find out more about array references refer to <CODE>perlref</CODE> and <CODE>perlreftut</CODE> in the main Perl documentation. To find out more about 2D arrays or "lists

1711

of lists" refer to <CODE>perllol</CODE>.

1712

1713

1714

1715

The <CODE>write_row()</CODE> method returns the first error encountered when writing the elements of the

1716

data or zero if no errors were encountered. See the return values described

1717

for the <CODE>write()</CODE> method above.

1718

1719

1720

1721

See also the <CODE>write_arrays.pl</CODE> program in the <CODE>examples</CODE> directory of the distro.

1722

1723

1724

1725

The <CODE>write_row()</CODE> method allows the following idiomatic conversion of a text file to an Excel

1726

file:

1727

1728

1729

1730

<PRE>

1731

#!/usr/bin/perl -w

1732

</PRE>

1733

1734

1735

<PRE>

1736

use strict;

1737

use Spreadsheet::WriteExcel;

1738

</PRE>

1739

1740

1741

<PRE>

1742

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

1743

my $worksheet = $workbook->add_worksheet();

1744

</PRE>

1745

1746

1747

<PRE>

1748

open INPUT, "file.txt" or die "Couldn't open file: $!";

1749

</PRE>

1750

1751

1752

<PRE>

1753

$worksheet->write($.-1, 0, [split]) while <INPUT>;

1754

</PRE>

1755

1756

1757

<HR>

1758

<H2><A NAME="write_col_row_column_array_">write_col($row, $column, $array_ref, $format)</A></H2>

1759

1760

The <CODE>write_col()</CODE> method can be used to write a 1D or 2D array of data in one go. This is

1761

useful for converting the results of a database query into an Excel

1762

worksheet. You must pass a reference to the array of data rather than the

1763

array itself. The <CODE>write()</CODE> method is then called for each element of the data. For example:

1764

1765

1766

1767

<PRE>

1768

@array = ('awk', 'gawk', 'mawk');

1769

$array_ref = \@array;

1770

</PRE>

1771

1772

1773

<PRE>

1774

$worksheet->write_col(0, 0, $array_ref);

1775

</PRE>

1776

1777

1778

<PRE>

1779

# The above example is equivalent to:

1780

$worksheet->write(0, 0, $array[0]);

1781

$worksheet->write(1, 0, $array[1]);

1782

$worksheet->write(2, 0, $array[2]);

1783

</PRE>

1784

1785

1786

As with all of the write methods the <CODE>$format</CODE> parameter is optional. If a format is specified it is applied to all the

1787

elements of the data array.

1788

1789

1790

1791

Array references within the data will be treated as rows. This allows you

1792

to write 2D arrays of data in one go. For example:

1793

1794

1795

1796

<PRE>

1797

@eec = (

1798

['maggie', 'milly', 'molly', 'may' ],

1799

[13, 14, 15, 16 ],

1800

['shell', 'star', 'crab', 'stone']

1801

);

1802

</PRE>

1803

1804

1805

<PRE>

1806

$worksheet->write_col('A1', \@eec);

1807

</PRE>

1808

1809

1810

Would produce a worksheet as follows:

1811

1812

1813

1814

<PRE>

1815

-----------------------------------------------------------

1816

| | A | B | C | D | E | ...

1817

-----------------------------------------------------------

1818

| 1 | maggie | milly | molly | may | ... | ...

1819

| 2 | 13 | 14 | 15 | 16 | ... | ...

1820

| 3 | shell | star | crab | stone | ... | ...

1821

| 4 | ... | ... | ... | ... | ... | ...

1822

| 5 | ... | ... | ... | ... | ... | ...

1823

| 6 | ... | ... | ... | ... | ... | ...

1824

</PRE>

1825

1826

1827

To write the data in a column-row order refer to the <CODE>write_row()</CODE> method above.

1828

1829

1830

1831

Any <CODE>undef</CODE> values in the data will be ignored unless a format is applied to the data,

1832

in which case a formatted blank cell will be written. In either case the

1833

appropriate row or column value will still be incremented.

1834

1835

1836

1837

As noted above the <CODE>write()</CODE> method can be used as a synonym for <CODE>write_row()</CODE> and <CODE>write_row()</CODE> handles nested array refs as columns. Therefore, the following two method

1838

calls are equivalent although the more explicit call to <CODE>write_col()</CODE> would be preferable for maintainability:

1839

1840

1841

1842

<PRE>

1843

$worksheet->write_col('A1', $array_ref ); # Write a column of data

1844

$worksheet->write( 'A1', [ $array_ref ]); # Same thing

1845

</PRE>

1846

1847

1848

To find out more about array references refer to <CODE>perlref</CODE> and <CODE>perlreftut</CODE> in the main Perl documentation. To find out more about 2D arrays or "lists

1849

of lists" refer to <CODE>perllol</CODE>.

1850

1851

1852

1853

The <CODE>write_col()</CODE> method returns the first error encountered when writing the elements of the

1854

data or zero if no errors were encountered. See the return values described

1855

for the <CODE>write()</CODE> method above.

1856

1857

1858

1859

See also the <CODE>write_arrays.pl</CODE> program in the <CODE>examples</CODE> directory of the distro.

1860

1861

1862

1863

<HR>

1864

<H2><A NAME="write_date_time_row_col_dat">write_date_time($row, $col, $date_string, $format)</A></H2>

1865

1866

The <CODE>write_date_time()</CODE> method can be used to write a date or time to the cell specified by <CODE>$row</CODE> and <CODE>$column</CODE>:

1867

1868

1869

1870

<PRE>

1871

$worksheet->write_date_time('A1', '2004-05-13T23:20', $date_format);

1872

</PRE>

1873

1874

1875

The <CODE>$date_string</CODE> should be in the following format:

1876

1877

1878

1879

<PRE>

1880

yyyy-mm-ddThh:mm:ss.sss

1881

</PRE>

1882

1883

1884

This conforms to am ISO8601 date but it should be noted that the full range

1885

of ISO8601 formats are not supported.

1886

1887

1888

1889

The following variations on the <CODE>$date_string</CODE> parameter are permitted:

1890

1891

1892

1893

<PRE>

1894

yyyy-mm-ddThh:mm:ss.sss # Standard format

1895

yyyy-mm-ddT # No time

1896

Thh:mm:ss.sss # No date

1897

yyyy-mm-ddThh:mm:ss.sssZ # Additional Z (but not time zones)

1898

yyyy-mm-ddThh:mm:ss # No fractional seconds

1899

yyyy-mm-ddThh:mm # No seconds

1900

</PRE>

1901

1902

1903

Note that the <CODE>T</CODE> is required in all cases.

1904

1905

1906

1907

A date should always have a <CODE>$format</CODE>, otherwise it will appear as a number, see <A HREF="#DATES_IN_EXCEL">DATES IN EXCEL</A> and <A HREF="#CELL_FORMATTING">CELL FORMATTING</A>. Here is a typical example:

1908

1909

1910

1911

<PRE>

1912

my $date_format = $workbook->add_format(num_format => 'mm/dd/yy');

1913

$worksheet->write_date_time('A1', '2004-05-13T23:20', $date_format);

1914

</PRE>

1915

1916

1917

Valid dates should be in the range 1900-01-01 to 9999-12-31, for the 1900

1918

epoch and 1904-01-01 to 9999-12-31, for the 1904 epoch. As with Excel,

1919

dates outside these ranges will be written as a string.

1920

1921

1922

1923

See also the date_time.pl program in the <CODE>examples</CODE> directory of the distro.

1924

1925

1926

1927

<HR>

1928

<H2><A NAME="write_url_row_col_url_str">write_url($row, $col, $url, $string, $format)</A></H2>

1929

1930

Write a hyperlink to a URL in the cell specified by <CODE>$row</CODE> and <CODE>$column</CODE>. The hyperlink is comprised of two elements: the visible label and the

1931

invisible link. The visible label is the same as the link unless an

1932

alternative string is specified. The parameters <CODE>$string</CODE> and the <CODE>$format</CODE> are optional and their position is interchangeable.

1933

1934

1935

1936

The label is written using the <CODE>write_string()</CODE> method. Therefore the 255 characters string limit applies to the label: the

1937

URL can be any length.

1938

1939

1940

1941

There are four web style URI's supported: <CODE>http://</CODE>, <CODE>https://</CODE>, <CODE>ftp://</CODE> and <CODE>mailto:</CODE>:

1942

1943

1944

1945

<PRE>

1946

$worksheet->write_url(0, 0, 'ftp://www.perl.org/' );

1947

$worksheet->write_url(1, 0, 'http://www.perl.com/', 'Perl home' );

1948

$worksheet->write_url('A3', 'http://www.perl.com/', $format );

1949

$worksheet->write_url('A4', 'http://www.perl.com/', 'Perl', $format);

1950

$worksheet->write_url('A5', 'mailto:jmcnamara@cpan.org' );

1951

</PRE>

1952

1953

1954

There are two local URIs supported: <CODE>internal:</CODE> and <CODE>external:</CODE>. These are used for hyperlinks to internal worksheet references or

1955

external workbook and worksheet references:

1956

1957

1958

1959

<PRE>

1960

$worksheet->write_url('A6', 'internal:Sheet2!A1' );

1961

$worksheet->write_url('A7', 'internal:Sheet2!A1', $format );

1962

$worksheet->write_url('A8', 'internal:Sheet2!A1:B2' );

1963

$worksheet->write_url('A9', q{internal:'Sales Data'!A1} );

1964

$worksheet->write_url('A10', 'external:c:\temp\foo.xls' );

1965

$worksheet->write_url('A11', 'external:c:\temp\foo.xls#Sheet2!A1' );

1966

$worksheet->write_url('A12', 'external:..\..\..\foo.xls' );

1967

$worksheet->write_url('A13', 'external:..\..\..\foo.xls#Sheet2!A1' );

1968

$worksheet->write_url('A13', 'external:\\\\NETWORK\share\foo.xls' );

1969

</PRE>

1970

1971

1972

All of the these URI types are recognised by the <CODE>write()</CODE> method, see above.

1973

1974

1975

1976

Worksheet references are typically of the form <CODE>Sheet1!A1</CODE>. You can also refer to a worksheet range using the standard Excel

1977

notation: <CODE>Sheet1!A1:B2</CODE>.

1978

1979

1980

1981

In external links the workbook and worksheet name must be separated by the <CODE>#</CODE> character: <CODE>external:Workbook.xls#Sheet1!A1'</CODE>.

1982

1983

1984

1985

You can also link to a named range in the target worksheet. For example say

1986

you have a named range called <CODE>my_name</CODE> in the workbook <CODE>c:\temp\foo.xls</CODE> you could link to it as follows:

1987

1988

1989

1990

<PRE>

1991

$worksheet->write_url('A14', 'external:c:\temp\foo.xls#my_name');

1992

</PRE>

1993

1994

1995

Note, you cannot currently create named ranges with <CODE>Spreadsheet::WriteExcel</CODE>.

1996

1997

1998

1999

Excel requires that worksheet names containing spaces or non alphanumeric

2000

characters are single quoted as follows <CODE>'Sales Data'!A1</CODE>. If you need to do this in a single quoted string then you can either

2001

escape the single quotes <CODE>\'</CODE> or use the quote operator <CODE>q{}</CODE> as described in <CODE>perlop</CODE> in the main Perl documentation.

2002

2003

2004

2005

Links to network files are also supported. MS/Novell Network files normally

2006

begin with two back slashes as follows <CODE>\\NETWORK\etc</CODE>. In order to generate this in a single or double quoted string you will

2007

have to escape the backslashes, <CODE>'\\\\NETWORK\etc'</CODE>.

2008

2009

2010

2011

If you are using double quote strings then you should be careful to escape

2012

anything that looks like a metacharacter. For more information see <CODE>perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?</CODE>.

2013

2014

2015

2016

Finally, you can avoid most of these quoting problems by using forward

2017

slashes. These are translated internally to backslashes:

2018

2019

2020

2021

<PRE>

2022

$worksheet->write_url('A14', "external:c:/temp/foo.xls" );

2023

$worksheet->write_url('A15', 'external://NETWORK/share/foo.xls' );

2024

</PRE>

2025

2026

2027

See also, the note about <A HREF="#Cell_notation">Cell notation</A>.

2028

2029

2030

2031

<HR>

2032

<H2><A NAME="write_url_range_row1_col1_r">write_url_range($row1, $col1, $row2, $col2, $url, $string, $format)</A></H2>

2033

2034

This method is essentially the same as the <CODE>write_url()</CODE> method described above. The main difference is that you can specify a link

2035

for a range of cells:

2036

2037

2038

2039

<PRE>

2040

$worksheet->write_url(0, 0, 0, 3, 'ftp://www.perl.org/' );

2041

$worksheet->write_url(1, 0, 0, 3, 'http://www.perl.com/', 'Perl home');

2042

$worksheet->write_url('A3:D3', 'internal:Sheet2!A1' );

2043

$worksheet->write_url('A4:D4', 'external:c:\temp\foo.xls' );

2044

</PRE>

2045

2046

2047

This method is generally only required when used in conjunction with merged

2048

cells. See the <CODE>merge_range()</CODE> method and the <CODE>merge</CODE> property of a Format object, <A HREF="#CELL_FORMATTING">CELL FORMATTING</A>.

2049

2050

2051

2052

There is no way to force this behaviour through the <CODE>write()</CODE> method.

2053

2054

2055

2056

The parameters <CODE>$string</CODE> and the <CODE>$format</CODE> are optional and their position is interchangeable. However, they are

2057

applied only to the first cell in the range.

2058

2059

2060

2061

See also, the note about <A HREF="#Cell_notation">Cell notation</A>.

2062

2063

2064

2065

<HR>

2066

<H2><A NAME="write_formula_row_column_fo">write_formula($row, $column, $formula, $format, $value)</A></H2>

2067

2068

Write a formula or function to the cell specified by <CODE>$row</CODE> and <CODE>$column</CODE>:

2069

2070

2071

2072

<PRE>

2073

$worksheet->write_formula(0, 0, '=$B$3 + B4' );

2074

$worksheet->write_formula(1, 0, '=SIN(PI()/4)');

2075

$worksheet->write_formula(2, 0, '=SUM(B1:B5)' );

2076

$worksheet->write_formula('A4', '=IF(A3>1,"Yes", "No")' );

2077

$worksheet->write_formula('A5', '=AVERAGE(1, 2, 3, 4)' );

2078

$worksheet->write_formula('A6', '=DATEVALUE("1-Jan-2001")');

2079

</PRE>

2080

2081

2082

See the note about <A HREF="#Cell_notation">Cell notation</A>. For more information about writing Excel formulas see <A HREF="#FORMULAS_AND_FUNCTIONS_IN_EXCEL">FORMULAS AND FUNCTIONS IN EXCEL</A>

2083

2084

2085

2086

2087

2088

See also the section "Improving performance when working with formulas" and

2089

the <CODE>store_formula()</CODE> and <CODE>repeat_formula()</CODE> methods.

2090

2091

2092

2093

If required, it is also possible to specify the calculated value of the

2094

formula. This is occasionally necessary when working with non-Excel

2095

applications that don't calculated the value of the formula. The calculated <CODE>$value</CODE> is added at the end of the argument list:

2096

2097

2098

2099

<PRE>

2100

$worksheet->write('A1', '=2+2', $format, 4);

2101

</PRE>

2102

2103

2104

However, this probably isn't something that will ever need to do. If you do

2105

use this feature then do so with care.

2106

2107

2108

2109

<HR>

2110

<H2><A NAME="store_formula_formula_">store_formula($formula)</A></H2>

2111

2112

The <CODE>store_formula()</CODE> method is used in conjunction with <CODE>repeat_formula()</CODE> to speed up the generation of repeated formulas. See "Improving performance

2113

when working with formulas" in <A HREF="#FORMULAS_AND_FUNCTIONS_IN_EXCEL">FORMULAS AND FUNCTIONS IN EXCEL</A>.

2114

2115

2116

2117

The <CODE>store_formula()</CODE> method pre-parses a textual representation of a formula and stores it for

2118

use at a later stage by the <CODE>repeat_formula()</CODE> method.

2119

2120

2121

2122

<CODE>store_formula()</CODE> carries the same speed penalty as <CODE>write_formula()</CODE>. However, in practice it will be used less frequently.

2123

2124

2125

2126

The return value of this method is a scalar that can be thought of as a

2127

reference to a formula.

2128

2129

2130

2131

<PRE>

2132

my $sin = $worksheet->store_formula('=SIN(A1)');

2133

my $cos = $worksheet->store_formula('=COS(A1)');

2134

</PRE>

2135

2136

2137

<PRE>

2138

$worksheet->repeat_formula('B1', $sin, $format, 'A1', 'A2');

2139

$worksheet->repeat_formula('C1', $cos, $format, 'A1', 'A2');

2140

</PRE>

2141

2142

2143

Although <CODE>store_formula()</CODE> is a worksheet method the return value can be used in any worksheet:

2144

2145

2146

2147

<PRE>

2148

my $now = $worksheet->store_formula('=NOW()');

2149

</PRE>

2150

2151

2152

<PRE>

2153

$worksheet1->repeat_formula('B1', $now);

2154

$worksheet2->repeat_formula('B1', $now);

2155

$worksheet3->repeat_formula('B1', $now);

2156

</PRE>

2157

2158

2159

<HR>

2160

<H2><A NAME="repeat_formula_row_col_form">repeat_formula($row, $col, $formula, $format, ($pattern => $replace, ...))</A></H2>

2161

2162

The <CODE>repeat_formula()</CODE> method is used in conjunction with <CODE>store_formula()</CODE> to speed up the generation of repeated formulas. See "Improving performance

2163

when working with formulas" in <A HREF="#FORMULAS_AND_FUNCTIONS_IN_EXCEL">FORMULAS AND FUNCTIONS IN EXCEL</A>.

2164

2165

2166

2167

In many respects <CODE>repeat_formula()</CODE> behaves like <CODE>write_formula()</CODE> except that it is significantly faster.

2168

2169

2170

2171

The <CODE>repeat_formula()</CODE> method creates a new formula based on the pre-parsed tokens returned by <CODE>store_formula()</CODE>. The new formula is generated by substituting <CODE>$pattern</CODE>, <CODE>$replace</CODE> pairs in the stored formula:

2172

2173

2174

2175

<PRE>

2176

my $formula = $worksheet->store_formula('=A1 * 3 + 50');

2177

</PRE>

2178

2179

2180

<PRE>

2181

for my $row (0..99) {

2182

$worksheet->repeat_formula($row, 1, $formula, $format, 'A1', 'A'.($row +1));

2183

}

2184

</PRE>

2185

2186

2187

It should be noted that <CODE>repeat_formula()</CODE> doesn't modify the tokens. In the above example the substitution is always

2188

made against the original token, <CODE>A1</CODE>, which doesn't change.

2189

2190

2191

2192

As usual, you can use <CODE>undef</CODE> if you don't wish to specify a <CODE>$format</CODE>:

2193

2194

2195

2196

<PRE>

2197

$worksheet->repeat_formula('B2', $formula, $format, 'A1', 'A2');

2198

$worksheet->repeat_formula('B3', $formula, undef, 'A1', 'A3');

2199

</PRE>

2200

2201

2202

The substitutions are made from left to right and you can use as many <CODE>$pattern</CODE>, <CODE>$replace</CODE> pairs as you need. However, each substitution is made only once:

2203

2204

2205

2206

<PRE>

2207

my $formula = $worksheet->store_formula('=A1 + A1');

2208

</PRE>

2209

2210

2211

<PRE>

2212

# Gives '=B1 + A1'

2213

$worksheet->repeat_formula('B1', $formula, undef, 'A1', 'B1');

2214

</PRE>

2215

2216

2217

<PRE>

2218

# Gives '=B1 + B1'

2219

$worksheet->repeat_formula('B2', $formula, undef, ('A1', 'B1') x 2);

2220

</PRE>

2221

2222

2223

Since the <CODE>$pattern</CODE> is interpolated each time that it is used it is worth using the <CODE>qr</CODE> operator to quote the pattern. The <CODE>qr</CODE> operator is explained in the <CODE>perlop</CODE> man page.

2224

2225

2226

2227

<PRE>

2228

$worksheet->repeat_formula('B1', $formula, $format, qr/A1/, 'A2');

2229

</PRE>

2230

2231

2232

Care should be taken with the values that are substituted. The formula

2233

returned by <CODE>repeat_formula()</CODE> contains several other tokens in addition to those in the formula and these

2234

might also match the pattern that you are trying to replace. In particular

2235

you should avoid substituting a single 0, 1, 2 or 3.

2236

2237

2238

2239

You should also be careful to avoid false matches. For example the

2240

following snippet is meant to change the stored formula in steps from <CODE>=A1 + SIN(A1)</CODE> to <CODE>=A10 + SIN(A10)</CODE>.

2241

2242

2243

2244

<PRE>

2245

my $formula = $worksheet->store_formula('=A1 + SIN(A1)');

2246

</PRE>

2247

2248

2249

<PRE>

2250

for my $row (1 .. 10) {

2251

$worksheet->repeat_formula($row -1, 1, $formula, undef,

2252

qw/A1/, 'A' . $row, #! Bad.

2253

qw/A1/, 'A' . $row #! Bad.

2254

);

2255

}

2256

</PRE>

2257

2258

2259

However it contains a bug. In the last iteration of the loop when <CODE>$row</CODE> is 10 the following substitutions will occur:

2260

2261

2262

2263

<PRE>

2264

s/A1/A10/; changes =A1 + SIN(A1) to =A10 + SIN(A1)

2265

s/A1/A10/; changes =A10 + SIN(A1) to =A100 + SIN(A1) # !!

2266

</PRE>

2267

2268

2269

The solution in this case is to use a more explicit match such as <CODE>qw/^A1$/</CODE>:

2270

2271

2272

2273

<PRE>

2274

$worksheet->repeat_formula($row -1, 1, $formula, undef,

2275

qw/^A1$/, 'A' . $row,

2276

qw/^A1$/, 'A' . $row

2277

);

2278

</PRE>

2279

2280

2281

Another similar problem occurs due to the fact that substitutions are made

2282

in order. For example the following snippet is meant to change the stored

2283

formula from <CODE>=A10 + A11</CODE> to <CODE>=A11 + A12</CODE>:

2284

2285

2286

2287

<PRE>

2288

my $formula = $worksheet->store_formula('=A10 + A11');

2289

</PRE>

2290

2291

2292

<PRE>

2293

$worksheet->repeat_formula('A1', $formula, undef,

2294

qw/A10/, 'A11', #! Bad.

2295

qw/A11/, 'A12' #! Bad.

2296

);

2297

</PRE>

2298

2299

2300

However, the actual substitution yields <CODE>=A12 + A11</CODE>:

2301

2302

2303

2304

<PRE>

2305

s/A10/A11/; changes =A10 + A11 to =A11 + A11

2306

s/A11/A12/; changes =A11 + A11 to =A12 + A11 # !!

2307

</PRE>

2308

2309

2310

The solution here would be to reverse the order of the substitutions or to

2311

start with a stored formula that won't yield a false match such as <CODE>=X10 + Y11</CODE>:

2312

2313

2314

2315

<PRE>

2316

my $formula = $worksheet->store_formula('=X10 + Y11');

2317

</PRE>

2318

2319

2320

<PRE>

2321

$worksheet->repeat_formula('A1', $formula, undef,

2322

qw/X10/, 'A11',

2323

qw/Y11/, 'A12'

2324

);

2325

</PRE>

2326

2327

2328

If you think that you have a problem related to a false match you can check

2329

the tokens that you are substituting against as follows.

2330

2331

2332

2333

<PRE>

2334

my $formula = $worksheet->store_formula('=A1*5+4');

2335

print "@$formula\n";

2336

</PRE>

2337

2338

2339

See also the <CODE>repeat.pl</CODE> program in the <CODE>examples</CODE> directory of the distro.

2340

2341

2342

2343

<HR>

2344

<H2><A NAME="write_comment_row_column_st">write_comment($row, $column, $string)</A></H2>

2345

2346

NOTE: this method is not available in this release. Use the 1.xx versions of this module if you need this feature.

2347

2348

2349

2350

The <CODE>write_comment()</CODE> method is used to add a comment to a cell. A cell comment is indicated in

2351

Excel by a small red triangle in the upper right-hand corner of the cell.

2352

Moving the cursor over the red triangle will cause the comment to appear.

2353

2354

2355

2356

The following example shows how to add a comment to a cell:

2357

2358

2359

2360

<PRE>

2361

$worksheet->write("C3", "Hello");

2362

$worksheet->write_comment("C3", "This is a comment.");

2363

</PRE>

2364

2365

2366

The cell comment can be up to 30,000 characters in length.

2367

2368

2369

2370

<HR>

2371

<H2><A NAME="add_write_handler_re_code_ref">add_write_handler($re, $code_ref)</A></H2>

2372

2373

This method is used to extend the Spreadsheet::WriteExcel

2374

<CODE>write()</CODE> method to handle user defined data.

2375

2376

2377

2378

If you refer to the section on <CODE>write()</CODE> above you will see that it acts as an alias for several more specific <CODE>write_*</CODE> methods. However, it doesn't always act in exactly the way that you would

2379

like it to.

2380

2381

2382

2383

One solution is to filter the input data yourself and call the appropriate <CODE>write_*</CODE> method. Another approach is to use the <CODE>add_write_handler()</CODE> method to add your own automated behaviour to <CODE>write()</CODE>.

2384

2385

2386

2387

The <CODE>add_write_handler()</CODE> method take two arguments, <CODE>$re</CODE>, a regular expression to match incoming data and <CODE>$code_ref</CODE> a callback function to handle the matched data:

2388

2389

2390

2391

<PRE>

2392

$worksheet->add_write_handler(qr/^\d\d\d\d$/, \&my_write);

2393

</PRE>

2394

2395

2396

(In the these examples the <CODE>qr</CODE> operator is used to quote the regular expression strings, see perlop for more details).

2397

2398

2399

2400

The method is use as follows. say you wished to write 7 digit ID numbers as

2401

a string so that any leading zeros were preserved*, you could do something

2402

like the following:

2403

2404

2405

2406

<PRE>

2407

$worksheet->add_write_handler(qr/^\d{7}$/, \&write_my_id);

2408

</PRE>

2409

2410

2411

<PRE>

2412

sub write_my_id {

2413

my $worksheet = shift;

2414

return $worksheet->write_string(@_);

2415

}

2416

</PRE>

2417

2418

2419

* You could also use the <CODE>keep_leading_zeros()</CODE> method for this.

2420

2421

2422

2423

Then if you call <CODE>write()</CODE> with an appropriate string it will be handled automatically:

2424

2425

2426

2427

<PRE>

2428

# Writes 0000000. It would normally be written as a number; 0.

2429

$worksheet->write('A1', '0000000');

2430

</PRE>

2431

2432

2433

The callback function will receive a reference to the calling worksheet and

2434

all of the other arguments that were passed to <CODE>write()</CODE>. The callback will see an <CODE>@_</CODE> argument list that looks like the following:

2435

2436

2437

2438

<PRE>

2439

$_[0] A ref to the calling worksheet. *

2440

$_[1] Zero based row number.

2441

$_[2] Zero based column number.

2442

$_[3] A number or string or token.

2443

$_[4] A format ref if any.

2444

$_[5] Any other argruments.

2445

...

2446

</PRE>

2447

2448

2449

<PRE>

2450

* It is good style to shift this off the list so the @_ is the same

2451

as the argument list seen by write().

2452

</PRE>

2453

2454

2455

Your callback should <CODE>return()</CODE> the return value of the <CODE>write_*</CODE> method that was called or <CODE>undef</CODE> to indicate that you rejected the match and want <CODE>write()</CODE> to continue as normal.

2456

2457

2458

2459

So for example if you wished to apply the previous filter only to ID values

2460

that occur in the first column you could modify your callback function as

2461

follows:

2462

2463

2464

2465

<PRE>

2466

sub write_my_id {

2467

my $worksheet = shift;

2468

my $col = $_[1];

2469

</PRE>

2470

2471

2472

<PRE>

2473

if ($col == 0) {

2474

return $worksheet->write_string(@_);

2475

}

2476

else {

2477

# Reject the match and return control to write()

2478

return undef;

2479

}

2480

}

2481

</PRE>

2482

2483

2484

Now, you will get different behaviour for the first column and other

2485

columns:

2486

2487

2488

2489

<PRE>

2490

$worksheet->write('A1', '0000000'); # Writes 0000000

2491

$worksheet->write('B1', '0000000'); # Writes 0

2492

</PRE>

2493

2494

2495

You may add more than one handler in which case they will be called in the

2496

order that they were added.

2497

2498

2499

2500

Note, the <CODE>add_write_handler()</CODE> method is particularly suited for handling dates.

2501

2502

2503

2504

See the <CODE>write_handler 1-4</CODE> programs in the <CODE>examples</CODE> directory for further examples.

2505

2506

2507

2508

<HR>

2509

<H2><A NAME="insert_bitmap_row_col_filen">insert_bitmap($row, $col, $filename, $x, $y, $scale_x, $scale_y)</A></H2>

2510

2511

This method can be used to insert a bitmap into a worksheet. The bitmap

2512

must be a 24 bit, true colour, bitmap. No other format is supported. The <CODE>$x</CODE>, <CODE>$y</CODE>, <CODE>$scale_x</CODE> and <CODE>$scale_y</CODE> parameters are optional.

2513

2514

2515

2516

<PRE>

2517

$worksheet1->insert_bitmap('A1', 'perl.bmp');

2518

$worksheet2->insert_bitmap('A1', '../images/perl.bmp');

2519

$worksheet3->insert_bitmap('A1', '.c:\images\perl.bmp');

2520

</PRE>

2521

2522

2523

Note: you must call <CODE>set_row()</CODE> or <CODE>set_column()</CODE> before <CODE>insert_bitmap()</CODE> if you wish to change the default dimensions of any of the rows or columns

2524

that the images occupies. The height of a row can also change if you use a

2525

font that is larger than the default. This in turn will affect the scaling

2526

of your image. To avoid this you should explicitly set the height of the

2527

row using <CODE>set_row()</CODE> if it contains a font size that will change the row height.

2528

2529

2530

2531

The parameters <CODE>$x</CODE> and <CODE>$y</CODE> can be used to specify an offset from the top left hand corner of the cell

2532

specified by <CODE>$row</CODE> and <CODE>$col</CODE>. The offset values are in pixels.

2533

2534

2535

2536

<PRE>

2537

$worksheet1->insert_bitmap('A1', 'perl.bmp', 32, 10);

2538

</PRE>

2539

2540

2541

The default width of a cell is 63 pixels. The default height of a cell is

2542

17 pixels. The pixels offsets can be calculated using the following

2543

relationships:

2544

2545

2546

2547

<PRE>

2548

Wp = int(12We) if We < 1

2549

Wp = int(7We +5) if We >= 1

2550

Hp = int(4/3He)

2551

</PRE>

2552

2553

2554

<PRE>

2555

where:

2556

We is the cell width in Excels units

2557

Wp is width in pixels

2558

He is the cell height in Excels units

2559

Hp is height in pixels

2560

</PRE>

2561

2562

2563

The offsets can be greater than the width or height of the underlying cell.

2564

This can be occasionally useful if you wish to align two or more images

2565

relative to the same cell.

2566

2567

2568

2569

The parameters <CODE>$scale_x</CODE> and <CODE>$scale_y</CODE> can be used to scale the inserted image horizontally and vertically:

2570

2571

2572

2573

<PRE>

2574

# Scale the inserted image: width x 2.0, height x 0.8

2575

$worksheet->insert_bitmap('A1', 'perl.bmp', 0, 0, 2, 0.8);

2576

</PRE>

2577

2578

2579

Note: although Excel allows you to import several graphics formats such as

2580

gif, jpeg, png and eps these are converted internally into a proprietary

2581

format. One of the few non-proprietary formats that Excel supports is 24

2582

bit, true colour, bitmaps. Therefore if you wish to use images in any other

2583

format you must first use an external application such as the ImageMagick convert utility to convert them to 24 bit bitmaps.

2584

2585

2586

2587

<PRE>

2588

convert test.png test.bmp

2589

</PRE>

2590

2591

2592

A later release will support the use of file handles and pre-encoded bitmap

2593

strings.

2594

2595

2596

2597

See also the <CODE>images.pl</CODE> program in the <CODE>examples</CODE> directory of the distro.

2598

2599

2600

2601

<HR>

2602

2603

2604

The <CODE>get_name()</CODE> method is used to retrieve the name of a worksheet. For example:

2605

2606

2607

2608

<PRE>

2609

foreach my $sheet ($workbook->sheets()) {

2610

print $sheet->get_name();

2611

}

2612

</PRE>

2613

2614

2615

<HR>

2616

<H2><A NAME="activate_">activate()</A></H2>

2617

2618

The <CODE>activate()</CODE> method is used to specify which worksheet is initially visible in a

2619

multi-sheet workbook:

2620

2621

2622

2623

<PRE>

2624

$worksheet1 = $workbook->add_worksheet('To');

2625

$worksheet2 = $workbook->add_worksheet('the');

2626

$worksheet3 = $workbook->add_worksheet('wind');

2627

</PRE>

2628

2629

2630

<PRE>

2631

$worksheet3->activate();

2632

</PRE>

2633

2634

2635

This is similar to the Excel VBA activate method. More than one worksheet

2636

can be selected via the <CODE>select()</CODE> method, however only one worksheet can be active. The default value is the

2637

first worksheet.

2638

2639

2640

2641

<HR>

2642

<H2><A NAME="select_">select()</A></H2>

2643

2644

The <CODE>select()</CODE> method is used to indicate that a worksheet is selected in a multi-sheet

2645

workbook:

2646

2647

2648

2649

<PRE>

2650

$worksheet1->activate();

2651

$worksheet2->select();

2652

$worksheet3->select();

2653

</PRE>

2654

2655

2656

A selected worksheet has its tab highlighted. Selecting worksheets is a way

2657

of grouping them together so that, for example, several worksheets could be

2658

printed in one go. A worksheet that has been activated via the <CODE>activate()</CODE> method will also appear as selected. You probably won't need to use the <CODE>select()</CODE> method very often.

2659

2660

2661

2662

<HR>

2663

<H2><A NAME="set_first_sheet_">set_first_sheet()</A></H2>

2664

2665

The <CODE>activate()</CODE> method determines which worksheet is initially selected. However, if there

2666

are a large number of worksheets the selected worksheet may not appear on

2667

the screen. To avoid this you can select which is the leftmost visible

2668

worksheet using <CODE>set_first_sheet()</CODE>:

2669

2670

2671

2672

<PRE>

2673

for (1..20) {

2674

$workbook->add_worksheet;

2675

}

2676

</PRE>

2677

2678

2679

<PRE>

2680

$worksheet21 = $workbook->add_worksheet();

2681

$worksheet22 = $workbook->add_worksheet();

2682

</PRE>

2683

2684

2685

<PRE>

2686

$worksheet21->set_first_sheet();

2687

$worksheet22->activate();

2688

</PRE>

2689

2690

2691

This method is not required very often. The default value is the first

2692

worksheet.

2693

2694

2695

2696

<HR>

2697

<H2><A NAME="protect_password_">protect($password)</A></H2>

2698

2699

The <CODE>protect()</CODE> method is used to protect a worksheet from modification:

2700

2701

2702

2703

<PRE>

2704

$worksheet->protect();

2705

</PRE>

2706

2707

2708

It can be turned off in Excel via the <CODE>Tools->Protection->Unprotect Sheet</CODE> menu command.

2709

2710

2711

2712

The <CODE>protect()</CODE> method also has the effect of enabling a cell's <CODE>locked</CODE> and <CODE>hidden</CODE> properties if they have been set. A "locked" cell cannot be edited. A

2713

"hidden" cell will display the results of a formula but not the formula

2714

itself. In Excel a cell's locked property is on by default.

2715

2716

2717

2718

<PRE>

2719

# Set some format properties

2720

my $unlocked = $workbook->add_format(locked => 0);

2721

my $hidden = $workbook->add_format(hidden => 1);

2722

</PRE>

2723

2724

2725

<PRE>

2726

# Enable worksheet protection

2727

$worksheet->protect();

2728

</PRE>

2729

2730

2731

<PRE>

2732

# This cell cannot be edited, it is locked by default

2733

$worksheet->write('A1', '=1+2');

2734

</PRE>

2735

2736

2737

<PRE>

2738

# This cell can be edited

2739

$worksheet->write('A2', '=1+2', $unlocked);

2740

</PRE>

2741

2742

2743

<PRE>

2744

# The formula in this cell isn't visible

2745

$worksheet->write('A3', '=1+2', $hidden);

2746

</PRE>

2747

2748

2749

See also the <CODE>set_locked</CODE> and <CODE>set_hidden</CODE> format methods in <A HREF="#CELL_FORMATTING">CELL FORMATTING</A>.

2750

2751

2752

2753

You can optionally add a password to the worksheet protection:

2754

2755

2756

2757

<PRE>

2758

$worksheet->protect('drowssap');

2759

</PRE>

2760

2761

2762

Note, the worksheet level password in Excel provides very weak protection.

2763

It does not encrypt your data in any way and it is very easy to deactivate.

2764

Therefore, do not use the above method if you wish to protect sensitive

2765

data or calculations. However, before you get worried, Excel's own workbook

2766

level password protection does provide strong encryption in Excel 97+. For

2767

technical reasons this will never be supported by <CODE>Spreadsheet::WriteExcel</CODE>.

2768

2769

2770

2771

<HR>

2772

<H2><A NAME="set_selection_first_row_first">set_selection($first_row, $first_col, $last_row, $last_col)</A></H2>

2773

2774

This method can be used to specify which cell or cells are selected in a

2775

worksheet. The most common requirement is to select a single cell, in which

2776

case <CODE>$last_row</CODE> and <CODE>$last_col</CODE> can be omitted. The active cell within a selected range is determined by

2777

the order in which <CODE>$first</CODE> and <CODE>$last</CODE> are specified. It is also possible to specify a cell or a range using A1

2778

notation. See the note about <A HREF="#Cell_notation">Cell notation</A>.

2779

2780

2781

2782

Examples:

2783

2784

2785

2786

<PRE>

2787

$worksheet1->set_selection(3, 3); # 1. Cell D4.

2788

$worksheet2->set_selection(3, 3, 6, 6); # 2. Cells D4 to G7.

2789

$worksheet3->set_selection(6, 6, 3, 3); # 3. Cells G7 to D4.

2790

$worksheet4->set_selection('D4'); # Same as 1.

2791

$worksheet5->set_selection('D4:G7'); # Same as 2.

2792

$worksheet6->set_selection('G7:D4'); # Same as 3.

2793

</PRE>

2794

2795

2796

The default cell selections is (0, 0), 'A1'.

2797

2798

2799

2800

<HR>

2801

<H2><A NAME="set_row_row_height_format_">set_row($row, $height, $format, $hidden, $level)</A></H2>

2802

2803

This method can be used to change the default properties of a row. All

2804

parameters apart from <CODE>$row</CODE> are optional.

2805

2806

2807

2808

The most common use for this method is to change the height of a row:

2809

2810

2811

2812

<PRE>

2813

$worksheet->set_row(0, 20); # Row 1 height set to 20

2814

</PRE>

2815

2816

2817

If you wish to set the format without changing the height you can pass <CODE>undef</CODE> as the height parameter:

2818

2819

2820

2821

<PRE>

2822

$worksheet->set_row(0, undef, $format);

2823

</PRE>

2824

2825

2826

The <CODE>$format</CODE> parameter will be applied to any cells in the row that don't have a format.

2827

For example

2828

2829

2830

2831

<PRE>

2832

$worksheet->set_row(0, undef, $format1); # Set the format for row 1

2833

$worksheet->write('A1', "Hello"); # Defaults to $format1

2834

$worksheet->write('B1', "Hello", $format2); # Keeps $format2

2835

</PRE>

2836

2837

2838

If you wish to define a row format in this way you should call the method

2839

before any calls to <CODE>write()</CODE>. Calling it afterwards will overwrite any format that was previously

2840

specified.

2841

2842

2843

2844

The <CODE>$hidden</CODE> parameter should be set to 1 if you wish to hide a row. This can be used,

2845

for example, to hide intermediary steps in a complicated calculation:

2846

2847

2848

2849

<PRE>

2850

$worksheet->set_row(0, 20, $format, 1);

2851

$worksheet->set_row(1, undef, undef, 1);

2852

</PRE>

2853

2854

2855

The <CODE>$level</CODE> parameter is used to set the outline level of the row. Outlines are

2856

described in <A HREF="#OUTLINES_AND_GROUPING_IN_EXCEL">OUTLINES AND GROUPING IN EXCEL</A>. Adjacent rows with the same outline level are grouped together into a

2857

single outline.

2858

2859

2860

2861

The following example sets an outline level of 1 for rows 1 and 2

2862

(zero-indexed):

2863

2864

2865

2866

<PRE>

2867

$worksheet->set_row(1, undef, undef, 0, 1);

2868

$worksheet->set_row(2, undef, undef, 0, 1);

2869

</PRE>

2870

2871

2872

The <CODE>$hidden</CODE> parameter can also be used to collapse outlined rows when used in

2873

conjunction with the <CODE>$level</CODE> parameter.

2874

2875

2876

2877

<PRE>

2878

$worksheet->set_row(1, undef, undef, 1, 1);

2879

$worksheet->set_row(2, undef, undef, 1, 1);

2880

</PRE>

2881

2882

2883

Excel allows up to 7 outline levels. Therefore the <CODE>$level</CODE> parameter should be in the range <CODE>0 <= $level <= 7</CODE>.

2884

2885

2886

2887

<HR>

2888

<H2><A NAME="set_column_first_col_last_col">set_column($first_col, $last_col, $width, $format, $hidden, $level)</A></H2>

2889

2890

This method can be used to change the default properties of a single column

2891

or a range of columns. All parameters apart from <CODE>$first_col</CODE> and <CODE>$last_col</CODE> are optional.

2892

2893

2894

2895

If <CODE>set_column()</CODE> is applied to a single column the value of <CODE>$first_col</CODE> and <CODE>$last_col</CODE> should be the same. It is also possible to specify a column range using the

2896

form of A1 notation used for columns. See the note about <A HREF="#Cell_notation">Cell notation</A>.

2897

2898

2899

2900

Examples:

2901

2902

2903

2904

<PRE>

2905

$worksheet->set_column(0, 0, 20); # Column A width set to 20

2906

$worksheet->set_column(1, 3, 30); # Columns B-D width set to 30

2907

$worksheet->set_column('E:E', 20); # Column E width set to 20

2908

$worksheet->set_column('F:H', 30); # Columns F-H width set to 30

2909

</PRE>

2910

2911

2912

The width corresponds to the column width value that is specified in Excel.

2913

It is approximately equal to the length of a string in the default font of

2914

Arial 10. Unfortunately, there is no way to specify "AutoFit" for a column

2915

in the Excel file format. This feature is only available at runtime from

2916

within Excel.

2917

2918

2919

2920

As usual the <CODE>$format</CODE> parameter is optional, for additional information, see <A HREF="#CELL_FORMATTING">CELL FORMATTING</A>. If you wish to set the format without changing the width you can pass <CODE>undef</CODE> as the width parameter:

2921

2922

2923

2924

<PRE>

2925

$worksheet->set_column(0, 0, undef, $format);

2926

</PRE>

2927

2928

2929

The <CODE>$format</CODE> parameter will be applied to any cells in the column that don't have a

2930

format. For example

2931

2932

2933

2934

<PRE>

2935

$worksheet->set_column('A:A', undef, $format1); # Set format for col 1

2936

$worksheet->write('A1', "Hello"); # Defaults to $format1

2937

$worksheet->write('A2', "Hello", $format2); # Keeps $format2

2938

</PRE>

2939

2940

2941

If you wish to define a column format in this way you should call the

2942

method before any calls to <CODE>write()</CODE>. If you call it afterwards it won't have any effect.

2943

2944

2945

2946

A default row format takes precedence over a default column format

2947

2948

2949

2950

<PRE>

2951

$worksheet->set_row(0, undef, $format1); # Set format for row 1

2952

$worksheet->set_column('A:A', undef, $format2); # Set format for col 1

2953

$worksheet->write('A1', "Hello"); # Defaults to $format1

2954

$worksheet->write('A2', "Hello"); # Defaults to $format2

2955

</PRE>

2956

2957

2958

The <CODE>$hidden</CODE> parameter should be set to 1 if you wish to hide a column. This can be

2959

used, for example, to hide intermediary steps in a complicated calculation:

2960

2961

2962

2963

<PRE>

2964

$worksheet->set_column('D:D', 20, $format, 1);

2965

$worksheet->set_column('E:E', undef, undef, 1);

2966

</PRE>

2967

2968

2969

The <CODE>$level</CODE> parameter is used to set the outline level of the column. Outlines are

2970

described in <A HREF="#OUTLINES_AND_GROUPING_IN_EXCEL">OUTLINES AND GROUPING IN EXCEL</A>. Adjacent columns with the same outline level are grouped together into a

2971

single outline.

2972

2973

2974

2975

The following example sets an outline level of 1 for columns B to G:

2976

2977

2978

2979

<PRE>

2980

$worksheet->set_column('B:G', undef, undef, 0, 1);

2981

</PRE>

2982

2983

2984

The <CODE>$hidden</CODE> parameter can also be used to collapse outlined columns when used in

2985

conjunction with the <CODE>$level</CODE> parameter.

2986

2987

2988

2989

<PRE>

2990

$worksheet->set_column('B:G', undef, undef, 1, 1);

2991

</PRE>

2992

2993

2994

Excel allows up to 7 outline levels. Therefore the <CODE>$level</CODE> parameter should be in the range <CODE>0 <= $level <= 7</CODE>.

2995

2996

2997

2998

<HR>

2999

<H2><A NAME="outline_settings_visible_symb">outline_settings($visible, $symbols_below, $symbols_right, $auto_style)</A></H2>

3000

3001

The <CODE>outline_settings()</CODE> method is used to control the appearance of outlines in Excel. Outlines are

3002

described in <A HREF="#OUTLINES_AND_GROUPING_IN_EXCEL">OUTLINES AND GROUPING IN EXCEL</A>.

3003

3004

3005

3006

The <CODE>$visible</CODE> parameter is used to control whether or not outlines are visible. Setting

3007

this parameter to 0 will cause all outlines on the worksheet to be hidden.

3008

They can be unhidden in Excel by means of the "Show Outline Symbols"

3009

command button. The default setting is 1 for visible outlines.

3010

3011

3012

3013

<PRE>

3014

$worksheet->outline_settings(0);

3015

</PRE>

3016

3017

3018

The <CODE>$symbols_below</CODE> parameter is used to control whether the row outline symbol will appear

3019

above or below the outline level bar. The default setting is 1 for symbols

3020

to appear below the outline level bar.

3021

3022

3023

3024

The <CODE>symbols_right</CODE> parameter is used to control whether the column outline symbol will appear

3025

to the left or the right of the outline level bar. The default setting is 1

3026

for symbols to appear to the right of the outline level bar.

3027

3028

3029

3030

The <CODE>$auto_style</CODE> parameter is used to control whether the automatic outline generator in

3031

Excel uses automatic styles when creating an outline. This has no effect on

3032

a file generated by <CODE>Spreadsheet::WriteExcel</CODE> but it does have an effect on how the worksheet behaves after it is

3033

created. The default setting is 0 for "Automatic Styles" to be turned off.

3034

3035

3036

3037

The default settings for all of these parameters correspond to Excel's

3038

default parameters.

3039

3040

3041

3042

The worksheet parameters controlled by <CODE>outline_settings()</CODE> are rarely used.

3043

3044

3045

3046

<HR>

3047

<H2><A NAME="freeze_panes_row_col_top_ro">freeze_panes($row, $col, $top_row, $left_col)</A></H2>

3048

3049

This method can be used to divide a worksheet into horizontal or vertical

3050

regions known as panes and to also "freeze" these panes so that the

3051

splitter bars are not visible. This is the same as the <CODE>Window->Freeze Panes</CODE> menu command in Excel

3052

3053

3054

3055

The parameters <CODE>$row</CODE> and <CODE>$col</CODE> are used to specify the location of the split. It should be noted that the

3056

split is specified at the top or left of a cell and that the method uses

3057

zero based indexing. Therefore to freeze the first row of a worksheet it is

3058

necessary to specify the split at row 2 (which is 1 as the zero-based

3059

index). This might lead you to think that you are using a 1 based index but

3060

this is not the case.

3061

3062

3063

3064

You can set one of the <CODE>$row</CODE> and <CODE>$col</CODE> parameters as zero if you do not want either a vertical or horizontal

3065

split.

3066

3067

3068

3069

Examples:

3070

3071

3072

3073

<PRE>

3074

$worksheet->freeze_panes(1, 0); # Freeze the first row

3075

$worksheet->freeze_panes('A2'); # Same using A1 notation

3076

$worksheet->freeze_panes(0, 1); # Freeze the first column

3077

$worksheet->freeze_panes('B1'); # Same using A1 notation

3078

$worksheet->freeze_panes(1, 2); # Freeze first row and first 2 columns

3079

$worksheet->freeze_panes('C2'); # Same using A1 notation

3080

</PRE>

3081

3082

3083

The parameters <CODE>$top_row</CODE> and <CODE>$left_col</CODE> are optional. They are used to specify the top-most or left-most visible

3084

row or column in the scrolling region of the panes. For example to freeze

3085

the first row and to have the scrolling region begin at row twenty:

3086

3087

3088

3089

<PRE>

3090

$worksheet->freeze_panes(1, 0, 20, 0);

3091

</PRE>

3092

3093

3094

You cannot use A1 notation for the <CODE>$top_row</CODE> and <CODE>$left_col</CODE> parameters.

3095

3096

3097

3098

See also the <CODE>panes.pl</CODE> program in the <CODE>examples</CODE> directory of the distribution.

3099

3100

3101

3102

<HR>

3103

<H2><A NAME="thaw_panes_y_x_top_row_le">thaw_panes($y, $x, $top_row, $left_col)</A></H2>

3104

3105

This method can be used to divide a worksheet into horizontal or vertical

3106

regions known as panes. This method is different from the <CODE>freeze_panes()</CODE> method in that the splits between the panes will be visible to the user and

3107

each pane will have its own scroll bars.

3108

3109

3110

3111

The parameters <CODE>$y</CODE> and <CODE>$x</CODE> are used to specify the vertical and horizontal position of the split. The

3112

units for <CODE>$y</CODE> and <CODE>$x</CODE> are the same as those used by Excel to specify row height and column width.

3113

However, the vertical and horizontal units are different from each other.

3114

Therefore you must specify the <CODE>$y</CODE> and <CODE>$x</CODE> parameters in terms of the row heights and column widths that you have set

3115

or the default values which are <CODE>12.75</CODE> for a row and <CODE>8.43</CODE> for a column.

3116

3117

3118

3119

You can set one of the <CODE>$y</CODE> and <CODE>$x</CODE> parameters as zero if you do not want either a vertical or horizontal

3120

split. The parameters <CODE>$top_row</CODE> and <CODE>$left_col</CODE> are optional. They are used to specify the top-most or left-most visible

3121

row or column in the bottom-right pane.

3122

3123

3124

3125

Example:

3126

3127

3128

3129

<PRE>

3130

$worksheet->thaw_panes(12.75, 0, 1, 0); # First row

3131

$worksheet->thaw_panes(0, 8.43, 0, 1); # First column

3132

$worksheet->thaw_panes(12.75, 8.43, 1, 1); # First row and column

3133

</PRE>

3134

3135

3136

You cannot use A1 notation with this method.

3137

3138

3139

3140

See also the <CODE>freeze_panes()</CODE> method and the <CODE>panes.pl</CODE> program in the <CODE>examples</CODE> directory of the distribution.

3141

3142

3143

3144

<HR>

3145

<H2><A NAME="merge_range_first_row_first_c">merge_range($first_row, $first_col, $last_row, $last_col, $token, $format)</A></H2>

3146

3147

Merging cells is generally achieved by setting the <CODE>merge</CODE> property of a Format object, see <A HREF="#CELL_FORMATTING">CELL FORMATTING</A>. However, this only allows simple Excel5 style horizontal merging which

3148

Excel refers to as "center across selection".

3149

3150

3151

3152

The <CODE>merge_range()</CODE> method allows you to do Excel97+ style formatting where the cells can

3153

contain other types of alignment in addition to the merging:

3154

3155

3156

3157

<PRE>

3158

my $format = $workbook->add_format(

3159

border => 6,

3160

valign => 'vcenter',

3161

align => 'center',

3162

);

3163

</PRE>

3164

3165

3166

<PRE>

3167

$worksheet->merge_range('B3:D4', 'Vertical and horizontal', $format);

3168

</PRE>

3169

3170

3171

WARNING. The format object that is used with a <CODE>merge_range()</CODE> method call is marked internally as being associated with a merged range.

3172

It is a fatal error to use a merged format in a non-merged cell. Instead

3173

you should use separate formats for merged and non-merged cells. This

3174

restriction will be removed in a future release.

3175

3176

3177

3178

<CODE>merge_range()</CODE> writes its <CODE>$token</CODE> argument using the worksheet <CODE>write()</CODE> method. Therefore it will handle numbers, strings, formulas or urls as

3179

required.

3180

3181

3182

3183

Setting the <CODE>merge</CODE> property of the format isn't required when you are using <CODE>merge_range()</CODE>. In fact using it will exclude the use of any other horizontal alignment

3184

option.

3185

3186

3187

3188

The full possibilities of this method are shown in the <CODE>merge3.pl</CODE>, <CODE>merge4.pl</CODE> and <CODE>merge5.pl</CODE> programs in the <CODE>examples</CODE> directory of the distribution.

3189

3190

3191

3192

<HR>

3193

<H2><A NAME="set_zoom_scale_">set_zoom($scale)</A></H2>

3194

3195

Set the worksheet zoom factor in the range <CODE>10 <= $scale <= 400</CODE>:

3196

3197

3198

3199

<PRE>

3200

$worksheet1->set_zoom(50);

3201

$worksheet2->set_zoom(75);

3202

$worksheet3->set_zoom(300);

3203

$worksheet4->set_zoom(400);

3204

</PRE>

3205

3206

3207

The default zoom factor is 100. You cannot zoom to "Selection" because it

3208

is calculated by Excel at run-time.

3209

3210

3211

3212

Note, <CODE>set_zoom()</CODE> does not affect the scale of the printed page. For that you should use <CODE>set_print_scale()</CODE>.

3213

3214

3215

3216

<HR>

3217

<H1><A NAME="PAGE_SET_UP_METHODS">PAGE SET-UP METHODS</A></H1>

3218

3219

Page set-up methods affect the way that a worksheet looks when it is

3220

printed. They control features such as page headers and footers and

3221

margins. These methods are really just standard worksheet methods. They are

3222

documented here in a separate section for the sake of clarity.

3223

3224

3225

3226

The following methods are available for page set-up:

3227

3228

3229

3230

<PRE>

3231

set_landscape()

3232

set_portrait()

3233

set_paper()

3234

center_horizontally()

3235

center_vertically()

3236

set_margins()

3237

set_header()

3238

set_footer()

3239

repeat_rows()

3240

repeat_columns()

3241

hide_gridlines()

3242

print_row_col_headers()

3243

print_area()

3244

fit_to_pages()

3245

set_print_scale()

3246

set_h_pagebreaks()

3247

set_v_pagebreaks()

3248

</PRE>

3249

3250

3251

A common requirement when working with Spreadsheet::WriteExcel is to apply

3252

the same page set-up features to all of the worksheets in a workbook. To do

3253

this you can use the <CODE>sheets()</CODE> method of the <CODE>workbook</CODE> class to access the array of worksheets in a workbook:

3254

3255

3256

3257

<PRE>

3258

foreach $worksheet ($workbook->sheets()) {

3259

$worksheet->set_landscape();

3260

}

3261

</PRE>

3262

3263

3264

<HR>

3265

<H2><A NAME="set_landscape_">set_landscape()</A></H2>

3266

3267

This method is used to set the orientation of a worksheet's printed page to

3268

landscape:

3269

3270

3271

3272

<PRE>

3273

$worksheet->set_landscape(); # Landscape mode

3274

</PRE>

3275

3276

3277

<HR>

3278

<H2><A NAME="set_portrait_">set_portrait()</A></H2>

3279

3280

This method is used to set the orientation of a worksheet's printed page to

3281

portrait. The default worksheet orientation is portrait, so you won't

3282

generally need to call this method.

3283

3284

3285

3286

<PRE>

3287

$worksheet->set_portrait(); # Portrait mode

3288

</PRE>

3289

3290

3291

<HR>

3292

<H2><A NAME="set_paper_index_">set_paper($index)</A></H2>

3293

3294

This method is used to set the paper format for the printed output of a

3295

worksheet. The following paper styles are available:

3296

3297

3298

3299

<PRE>

3300

Index Paper format Paper size

3301

===== ============ ==========

3302

0 Printer default -

3303

1 Letter 8 1/2 x 11 in

3304

2 Letter Small 8 1/2 x 11 in

3305

3 Tabloid 11 x 17 in

3306

4 Ledger 17 x 11 in

3307

5 Legal 8 1/2 x 14 in

3308

6 Statement 5 1/2 x 8 1/2 in

3309

7 Executive 7 1/4 x 10 1/2 in

3310

8 A3 297 x 420 mm

3311

9 A4 210 x 297 mm

3312

10 A4 Small 210 x 297 mm

3313

11 A5 148 x 210 mm

3314

12 B4 250 x 354 mm

3315

13 B5 182 x 257 mm

3316

14 Folio 8 1/2 x 13 in

3317

15 Quarto 215 x 275 mm

3318

16 - 10x14 in

3319

17 - 11x17 in

3320

18 Note 8 1/2 x 11 in

3321

19 Envelope 9 3 7/8 x 8 7/8

3322

20 Envelope 10 4 1/8 x 9 1/2

3323

21 Envelope 11 4 1/2 x 10 3/8

3324

22 Envelope 12 4 3/4 x 11

3325

23 Envelope 14 5 x 11 1/2

3326

24 C size sheet -

3327

25 D size sheet -

3328

26 E size sheet -

3329

27 Envelope DL 110 x 220 mm

3330

28 Envelope C3 324 x 458 mm

3331

29 Envelope C4 229 x 324 mm

3332

30 Envelope C5 162 x 229 mm

3333

31 Envelope C6 114 x 162 mm

3334

32 Envelope C65 114 x 229 mm

3335

33 Envelope B4 250 x 353 mm

3336

34 Envelope B5 176 x 250 mm

3337

35 Envelope B6 176 x 125 mm

3338

36 Envelope 110 x 230 mm

3339

37 Monarch 3.875 x 7.5 in

3340

38 Envelope 3 5/8 x 6 1/2 in

3341

39 Fanfold 14 7/8 x 11 in

3342

40 German Std Fanfold 8 1/2 x 12 in

3343

41 German Legal Fanfold 8 1/2 x 13 in

3344

</PRE>

3345

3346

3347

Note, it is likely that not all of these paper types will be available to

3348

the end user since it will depend on the paper formats that the user's

3349

printer supports. Therefore, it is best to stick to standard paper types.

3350

3351

3352

3353

<PRE>

3354

$worksheet->set_paper(1); # US Letter

3355

$worksheet->set_paper(9); # A4

3356

</PRE>

3357

3358

3359

If you do not specify a paper type the worksheet will print using the

3360

printer's default paper.

3361

3362

3363

3364

<HR>

3365

<H2><A NAME="center_horizontally_">center_horizontally()</A></H2>

3366

3367

Center the worksheet data horizontally between the margins on the printed

3368

page:

3369

3370

3371

3372

<PRE>

3373

$worksheet->center_horizontally();

3374

</PRE>

3375

3376

3377

<HR>

3378

<H2><A NAME="center_vertically_">center_vertically()</A></H2>

3379

3380

Center the worksheet data vertically between the margins on the printed

3381

page:

3382

3383

3384

3385

<PRE>

3386

$worksheet->center_vertically();

3387

</PRE>

3388

3389

3390

<HR>

3391

<H2><A NAME="set_margins_inches_">set_margins($inches)</A></H2>

3392

3393

There are several methods available for setting the worksheet margins on

3394

the printed page:

3395

3396

3397

3398

<PRE>

3399

set_margins() # Set all margins to the same value

3400

set_margins_LR() # Set left and right margins to the same value

3401

set_margins_TB() # Set top and bottom margins to the same value

3402

set_margin_left(); # Set left margin

3403

set_margin_right(); # Set right margin

3404

set_margin_top(); # Set top margin

3405

set_margin_bottom(); # Set bottom margin

3406

</PRE>

3407

3408

3409

All of these methods take a distance in inches as a parameter. Note: 1 inch

3410

= 25.4mm. ;-) The default left and right margin is 0.75 inch. The default

3411

top and bottom margin is 1.00 inch.

3412

3413

3414

3415

<HR>

3416

<H2><A NAME="set_header_string_margin_">set_header($string, $margin)</A></H2>

3417

3418

Headers and footers are generated using a <CODE>$string</CODE> which is a combination of plain text and control characters. The <CODE>$margin</CODE> parameter is optional.

3419

3420

3421

3422

The available control character are:

3423

3424

3425

3426

<PRE>

3427

Control Category Description

3428

======= ======== ===========

3429

&L Justification Left

3430

&C Center

3431

&R Right

3432

</PRE>

3433

3434

3435

<PRE>

3436

&P Information Page number

3437

&N Total number of pages

3438

&D Date

3439

&T Time

3440

&F File name

3441

&A Worksheet name

3442

&Z Workbook path

3443

</PRE>

3444

3445

3446

<PRE>

3447

&fontsize Font Font size

3448

&"font,style" Font name and style

3449

&U Single underline

3450

&E Double underline

3451

&S Strikethrough

3452

&X Superscript

3453

&Y Subscript

3454

</PRE>

3455

3456

3457

<PRE>

3458

&& Miscellaneous Literal ampersand &

3459

</PRE>

3460

3461

3462

Text in headers and footers can be justified (aligned) to the left, center

3463

and right by prefixing the text with the control characters <CODE>&L</CODE>, <CODE>&C</CODE> and <CODE>&R</CODE>.

3464

3465

3466

3467

For example (with ASCII art representation of the results):

3468

3469

3470

3471

<PRE>

3472

$worksheet->set_header('&LHello');

3473

</PRE>

3474

3475

3476

<PRE>

3477

---------------------------------------------------------------

3478

| |

3479

| Hello |

3480

| |

3481

</PRE>

3482

3483

3484

<PRE>

3485

$worksheet->set_header('&CHello');

3486

</PRE>

3487

3488

3489

<PRE>

3490

---------------------------------------------------------------

3491

| |

3492

| Hello |

3493

| |

3494

</PRE>

3495

3496

3497

<PRE>

3498

$worksheet->set_header('&RHello');

3499

</PRE>

3500

3501

3502

<PRE>

3503

---------------------------------------------------------------

3504

| |

3505

| Hello |

3506

| |

3507

</PRE>

3508

3509

3510

For simple text, if you do not specify any justification the text will be

3511

centred. However, you must prefix the text with <CODE>&C</CODE> if you specify a font name or any other formatting:

3512

3513

3514

3515

<PRE>

3516

$worksheet->set_header('Hello');

3517

</PRE>

3518

3519

3520

<PRE>

3521

---------------------------------------------------------------

3522

| |

3523

| Hello |

3524

| |

3525

</PRE>

3526

3527

3528

You can have text in each of the justification regions:

3529

3530

3531

3532

<PRE>

3533

$worksheet->set_header('&LCiao&CBello&RCielo');

3534

</PRE>

3535

3536

3537

<PRE>

3538

---------------------------------------------------------------

3539

| |

3540

| Ciao Bello Cielo |

3541

| |

3542

</PRE>

3543

3544

3545

The information control characters act as variables that Excel will update

3546

as the workbook or worksheet changes. Times and dates are in the users

3547

default format:

3548

3549

3550

3551

<PRE>

3552

$worksheet->set_header('&CPage &P of &N');

3553

</PRE>

3554

3555

3556

<PRE>

3557

---------------------------------------------------------------

3558

| |

3559

| Page 1 of 6 |

3560

| |

3561

</PRE>

3562

3563

3564

<PRE>

3565

$worksheet->set_header('&CUpdated at &T');

3566

</PRE>

3567

3568

3569

<PRE>

3570

---------------------------------------------------------------

3571

| |

3572

| Updated at 12:30 PM |

3573

| |

3574

</PRE>

3575

3576

3577

You can specify the font size of a section of the text by prefixing it with

3578

the control character <CODE>&n</CODE> where <CODE>n</CODE> is the font size:

3579

3580

3581

3582

<PRE>

3583

$worksheet1->set_header('&C&30Hello Big' );

3584

$worksheet2->set_header('&C&10Hello Small');

3585

</PRE>

3586

3587

3588

You can specify the font of a section of the text by prefixing it with the

3589

control sequence <CODE>&"font,style"</CODE> where <CODE>fontname</CODE> is a font name such as "Courier New" or "Times New Roman" and <CODE>style</CODE> is one of the standard Windows font descriptions: "Regular", "Italic",

3590

"Bold" or "Bold Italic":

3591

3592

3593

3594

<PRE>

3595

$worksheet1->set_header('&C&"Courier New,Italic"Hello');

3596

$worksheet2->set_header('&C&"Courier New,Bold Italic"Hello');

3597

$worksheet3->set_header('&C&"Times New Roman,Regular"Hello');

3598

</PRE>

3599

3600

3601

It is possible to combine all of these features together to create

3602

sophisticated headers and footers. As an aid to setting up complicated

3603

headers and footers you can record a page set-up as a macro in Excel and

3604

look at the format strings that VBA produces. Remember however that VBA

3605

uses two double quotes <CODE>""</CODE> to indicate a single double quote. For the last example above the

3606

equivalent VBA code looks like this:

3607

3608

3609

3610

<PRE>

3611

.LeftHeader = ""

3612

.CenterHeader = "&""Times New Roman,Regular""Hello"

3613

.RightHeader = ""

3614

</PRE>

3615

3616

3617

To include a single literal ampersand <CODE>&</CODE> in a header or footer you should use a double ampersand <CODE>&&</CODE>:

3618

3619

3620

3621

<PRE>

3622

$worksheet1->set_header('&CCuriouser && Curiouser - Attorneys at Law');

3623

</PRE>

3624

3625

3626

As stated above the margin parameter is optional. As with the other margins

3627

the value should be in inches. The default header and footer margin is 0.50

3628

inch. The header and footer margin size can be set as follows:

3629

3630

3631

3632

<PRE>

3633

$worksheet->set_header('&CHello', 0.75);

3634

</PRE>

3635

3636

3637

The header and footer margins are independent of the top and bottom

3638

margins.

3639

3640

3641

3642

Note, the header or footer string must be less than 255 characters. Strings

3643

longer than this will not be written and a warning will be generated.

3644

3645

3646

3647

On systems with <CODE>perl 5.8</CODE> and later the <CODE>set_header()</CODE> method can also handle Unicode strings in Perl's <CODE>utf8</CODE> format.

3648

3649

3650

3651

<PRE>

3652

$worksheet->set_header("&C\x{263a}")

3653

</PRE>

3654

3655

3656

See, also the <CODE>headers.pl</CODE> program in the <CODE>examples</CODE> directory of the distribution.

3657

3658

3659

3660

<HR>

3661

<H2><A NAME="set_footer_">set_footer()</A></H2>

3662

3663

The syntax of the <CODE>set_footer()</CODE> method is the same as <CODE>set_header()</CODE>, see above.

3664

3665

3666

3667

<HR>

3668

<H2><A NAME="repeat_rows_first_row_last_ro">repeat_rows($first_row, $last_row)</A></H2>

3669

3670

Set the number of rows to repeat at the top of each printed page.

3671

3672

3673

3674

For large Excel documents it is often desirable to have the first row or

3675

rows of the worksheet print out at the top of each page. This can be

3676

achieved by using the <CODE>repeat_rows()</CODE> method. The parameters <CODE>$first_row</CODE> and <CODE>$last_row</CODE> are zero based. The <CODE>$last_row</CODE> parameter is optional if you only wish to specify one row:

3677

3678

3679

3680

<PRE>

3681

$worksheet1->repeat_rows(0); # Repeat the first row

3682

$worksheet2->repeat_rows(0, 1); # Repeat the first two rows

3683

</PRE>

3684

3685

3686

<HR>

3687

<H2><A NAME="repeat_columns_first_col_last">repeat_columns($first_col, $last_col)</A></H2>

3688

3689

Set the columns to repeat at the left hand side of each printed page.

3690

3691

3692

3693

For large Excel documents it is often desirable to have the first column or

3694

columns of the worksheet print out at the left hand side of each page. This

3695

can be achieved by using the <CODE>repeat_columns()</CODE> method. The parameters <CODE>$first_column</CODE> and <CODE>$last_column</CODE> are zero based. The <CODE>$last_column</CODE> parameter is optional if you only wish to specify one column. You can also

3696

specify the columns using A1 column notation, see the note about <A HREF="#Cell_notation">Cell notation</A>.

3697

3698

3699

3700

<PRE>

3701

$worksheet1->repeat_columns(0); # Repeat the first column

3702

$worksheet2->repeat_columns(0, 1); # Repeat the first two columns

3703

$worksheet3->repeat_columns('A:A'); # Repeat the first column

3704

$worksheet4->repeat_columns('A:B'); # Repeat the first two columns

3705

</PRE>

3706

3707

3708

<HR>

3709

<H2><A NAME="hide_gridlines_option_">hide_gridlines($option)</A></H2>

3710

3711

This method is used to hide the gridlines on the screen and printed page.

3712

Gridlines are the lines that divide the cells on a worksheet. Screen and

3713

printed gridlines are turned on by default in an Excel worksheet. If you

3714

have defined your own cell borders you may wish to hide the default

3715

gridlines.

3716

3717

3718

3719

<PRE>

3720

$worksheet->hide_gridlines();

3721

</PRE>

3722

3723

3724

The following values of <CODE>$option</CODE> are valid:

3725

3726

3727

3728

<PRE>

3729

0 : Don't hide gridlines

3730

1 : Hide printed gridlines only

3731

2 : Hide screen and printed gridlines

3732

</PRE>

3733

3734

3735

If you don't supply an argument or use <CODE>undef</CODE> the default option is 1, i.e. only the printed gridlines are hidden.

3736

3737

3738

3739

<HR>

3740

<H2><A NAME="print_row_col_headers_">print_row_col_headers()</A></H2>

3741

3742

Set the option to print the row and column headers on the printed page.

3743

3744

3745

3746

An Excel worksheet looks something like the following;

3747

3748

3749

3750

<PRE>

3751

------------------------------------------

3752

| | A | B | C | D | ...

3753

------------------------------------------

3754

| 1 | | | | | ...

3755

| 2 | | | | | ...

3756

| 3 | | | | | ...

3757

| 4 | | | | | ...

3758

|...| ... | ... | ... | ... | ...

3759

</PRE>

3760

3761

3762

The headers are the letters and numbers at the top and the left of the

3763

worksheet. Since these headers serve mainly as a indication of position on

3764

the worksheet they generally do not appear on the printed page. If you wish

3765

to have them printed you can use the <CODE>print_row_col_headers()</CODE> method :

3766

3767

3768

3769

<PRE>

3770

$worksheet->print_row_col_headers();

3771

</PRE>

3772

3773

3774

Do not confuse these headers with page headers as described in the <CODE>set_header()</CODE> section above.

3775

3776

3777

3778

<HR>

3779

<H2><A NAME="print_area_first_row_first_co">print_area($first_row, $first_col, $last_row, $last_col)</A></H2>

3780

3781

This method is used to specify the area of the worksheet that will be

3782

printed. All four parameters must be specified. You can also use A1

3783

notation, see the note about <A HREF="#Cell_notation">Cell notation</A>.

3784

3785

3786

3787

<PRE>

3788

$worksheet1->print_area("A1:H20"); # Cells A1 to H20

3789

$worksheet2->print_area(0, 0, 19, 7); # The same

3790

$worksheet2->print_area('A:H'); # Columns A to H if rows have data

3791

</PRE>

3792

3793

3794

<HR>

3795

<H2><A NAME="fit_to_pages_width_height_">fit_to_pages($width, $height)</A></H2>

3796

3797

The <CODE>fit_to_pages()</CODE> method is used to fit the printed area to a specific number of pages both

3798

vertically and horizontally. If the printed area exceeds the specified

3799

number of pages it will be scaled down to fit. This guarantees that the

3800

printed area will always appear on the specified number of pages even if

3801

the page size or margins change.

3802

3803

3804

3805

<PRE>

3806

$worksheet1->fit_to_pages(1, 1); # Fit to 1x1 pages

3807

$worksheet2->fit_to_pages(2, 1); # Fit to 2x1 pages

3808

$worksheet3->fit_to_pages(1, 2); # Fit to 1x2 pages

3809

</PRE>

3810

3811

3812

The print area can be defined using the <CODE>print_area()</CODE> method as described above.

3813

3814

3815

3816

A common requirement is to fit the printed output to n pages wide but have the height be as long as necessary. To achieve this set

3817

the <CODE>$height</CODE> to zero or leave it blank:

3818

3819

3820

3821

<PRE>

3822

$worksheet1->fit_to_pages(1, 0); # 1 page wide and as long as necessary

3823

$worksheet2->fit_to_pages(1); # The same

3824

</PRE>

3825

3826

3827

Note that although it is valid to use both <CODE>fit_to_pages()</CODE> and <CODE>set_print_scale()</CODE> on the same worksheet only one of these options can be active at a time.

3828

The last method call made will set the active option.

3829

3830

3831

3832

Note that <CODE>fit_to_pages()</CODE> will override any manual page breaks that are defined in the worksheet.

3833

3834

3835

3836

<HR>

3837

<H2><A NAME="set_print_scale_scale_">set_print_scale($scale)</A></H2>

3838

3839

Set the scale factor of the printed page. Scale factors in the range <CODE>10 <= $scale <= 400</CODE> are valid:

3840

3841

3842

3843

<PRE>

3844

$worksheet1->set_print_scale(50);

3845

$worksheet2->set_print_scale(75);

3846

$worksheet3->set_print_scale(300);

3847

$worksheet4->set_print_scale(400);

3848

</PRE>

3849

3850

3851

The default scale factor is 100. Note, <CODE>set_print_scale()</CODE> does not affect the scale of the visible page in Excel. For that you should

3852

use <CODE>set_zoom()</CODE>.

3853

3854

3855

3856

Note also that although it is valid to use both <CODE>fit_to_pages()</CODE> and <CODE>set_print_scale()</CODE> on the same worksheet only one of these options can be active at a time.

3857

The last method call made will set the active option.

3858

3859

3860

3861

<HR>

3862

<H2><A NAME="set_h_pagebreaks_breaks_">set_h_pagebreaks(@breaks)</A></H2>

3863

3864

Add horizontal page breaks to a worksheet. A page break causes all the data

3865

that follows it to be printed on the next page. Horizontal page breaks act

3866

between rows. To create a page break between rows 20 and 21 you must

3867

specify the break at row 21. However in zero index notation this is

3868

actually row 20. So you can pretend for a small while that you are using 1

3869

index notation:

3870

3871

3872

3873

<PRE>

3874

$worksheet1->set_h_pagebreaks(20); # Break between row 20 and 21

3875

</PRE>

3876

3877

3878

The <CODE>set_h_pagebreaks()</CODE> method will accept a list of page breaks and you can call it more than

3879

once:

3880

3881

3882

3883

<PRE>

3884

$worksheet2->set_h_pagebreaks( 20, 40, 60, 80, 100); # Add breaks

3885

$worksheet2->set_h_pagebreaks(120, 140, 160, 180, 200); # Add some more

3886

</PRE>

3887

3888

3889

Note: If you specify the "fit to page" option via the <CODE>fit_to_pages()</CODE> method it will override all manual page breaks.

3890

3891

3892

3893

There is a silent limitation of about 1000 horizontal page breaks per

3894

worksheet in line with an Excel internal limitation.

3895

3896

3897

3898

<HR>

3899

<H2><A NAME="set_v_pagebreaks_breaks_">set_v_pagebreaks(@breaks)</A></H2>

3900

3901

Add vertical page breaks to a worksheet. A page break causes all the data

3902

that follows it to be printed on the next page. Vertical page breaks act

3903

between columns. To create a page break between columns 20 and 21 you must

3904

specify the break at column 21. However in zero index notation this is

3905

actually column 20. So you can pretend for a small while that you are using

3906

1 index notation:

3907

3908

3909

3910

<PRE>

3911

$worksheet1->set_v_pagebreaks(20); # Break between column 20 and 21

3912

</PRE>

3913

3914

3915

The <CODE>set_v_pagebreaks()</CODE> method will accept a list of page breaks and you can call it more than

3916

once:

3917

3918

3919

3920

<PRE>

3921

$worksheet2->set_v_pagebreaks( 20, 40, 60, 80, 100); # Add breaks

3922

$worksheet2->set_v_pagebreaks(120, 140, 160, 180, 200); # Add some more

3923

</PRE>

3924

3925

3926

Note: If you specify the "fit to page" option via the <CODE>fit_to_pages()</CODE> method it will override all manual page breaks.

3927

3928

3929

3930

<HR>

3931

<H1><A NAME="CELL_FORMATTING">CELL FORMATTING</A></H1>

3932

3933

This section describes the methods and properties that are available for

3934

formatting cells in Excel. The properties of a cell that can be formatted

3935

include: fonts, colours, patterns, borders, alignment and number

3936

formatting.

3937

3938

3939

3940

<HR>

3941

<H2><A NAME="Creating_and_using_a_Format_obje">Creating and using a Format object</A></H2>

3942

3943

Cell formatting is defined through a Format object. Format objects are

3944

created by calling the workbook <CODE>add_format()</CODE> method as follows:

3945

3946

3947

3948

<PRE>

3949

my $format1 = $workbook->add_format(); # Set properties later

3950

my $format2 = $workbook->add_format(%props); # Set at creation

3951

</PRE>

3952

3953

3954

The format object holds all the formatting properties that can be applied

3955

to a cell, a row or a column. The process of setting these properties is

3956

discussed in the next section.

3957

3958

3959

3960

Once a Format object has been constructed and it properties have been set

3961

it can be passed as an argument to the worksheet <CODE>write</CODE> methods as follows:

3962

3963

3964

3965

<PRE>

3966

$worksheet->write(0, 0, "One", $format);

3967

$worksheet->write_string(1, 0, "Two", $format);

3968

$worksheet->write_number(2, 0, 3, $format);

3969

$worksheet->write_blank(3, 0, $format);

3970

</PRE>

3971

3972

3973

Formats can also be passed to the worksheet <CODE>set_row()</CODE> and <CODE>set_column()</CODE> methods to define the default property for a row or column.

3974

3975

3976

3977

<PRE>

3978

$worksheet->set_row(0, 15, $format);

3979

$worksheet->set_column(0, 0, 15, $format);

3980

</PRE>

3981

3982

3983

<HR>

3984

<H2><A NAME="Format_methods_and_Format_proper">Format methods and Format properties</A></H2>

3985

3986

The following table shows the Excel format categories, the formatting

3987

properties that can be applied and the equivalent object method:

3988

3989

3990

3991

<PRE>

3992

Category Description Property Method Name

3993

-------- ----------- -------- -----------

3994

Font Font type font set_font()

3995

Font size size set_size()

3996

Font color color set_color()

3997

Bold bold set_bold()

3998

Italic italic set_italic()

3999

Underline underline set_underline()

4000

Strikeout font_strikeout set_font_strikeout()

4001

Super/Subscript font_script set_font_script()

4002

Outline font_outline set_font_outline()

4003

Shadow font_shadow set_font_shadow()

4004

</PRE>

4005

4006

4007

<PRE>

4008

Number Numeric format num_format set_num_format()

4009

</PRE>

4010

4011

4012

<PRE>

4013

Protection Lock cells locked set_locked()

4014

Hide formulas hidden set_hidden()

4015

</PRE>

4016

4017

4018

<PRE>

4019

Alignment Horizontal align align set_align()

4020

Vertical align valign set_align()

4021

Rotation rotation set_rotation()

4022

Text wrap text_wrap set_text_wrap()

4023

Justify last text_justlast set_text_justlast()

4024

Center across center_across set_center_across()

4025

Indentation indent set_indent()

4026

Shrink to fit shrink set_shrink()

4027

</PRE>

4028

4029

4030

<PRE>

4031

Pattern Cell pattern pattern set_pattern()

4032

Background color bg_color set_bg_color()

4033

Foreground color fg_color set_fg_color()

4034

</PRE>

4035

4036

4037

<PRE>

4038

Border Cell border border set_border()

4039

Bottom border bottom set_bottom()

4040

Top border top set_top()

4041

Left border left set_left()

4042

Right border right set_right()

4043

Border color border_color set_border_color()

4044

Bottom color bottom_color set_bottom_color()

4045

Top color top_color set_top_color()

4046

Left color left_color set_left_color()

4047

Right color right_color set_right_color()

4048

</PRE>

4049

4050

4051

There are two ways of setting Format properties: by using the object method

4052

interface or by setting the property directly. For example, a typical use

4053

of the method interface would be as follows:

4054

4055

4056

4057

<PRE>

4058

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

4059

$format->set_bold();

4060

$format->set_color('red');

4061

</PRE>

4062

4063

4064

By comparison the properties can be set directly by passing a hash of

4065

properties to the Format constructor:

4066

4067

4068

4069

<PRE>

4070

my $format = $workbook->add_format(bold => 1, color => 'red');

4071

</PRE>

4072

4073

4074

or after the Format has been constructed by means of the <CODE>set_properties()</CODE> method as follows:

4075

4076

4077

4078

<PRE>

4079

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

4080

$format->set_properties(bold => 1, color => 'red');

4081

</PRE>

4082

4083

4084

You can also store the properties in one or more named hashes and pass them

4085

to the required method:

4086

4087

4088

4089

<PRE>

4090

my %font = (

4091

font => 'Arial',

4092

size => 12,

4093

color => 'blue',

4094

bold => 1,

4095

);

4096

</PRE>

4097

4098

4099

<PRE>

4100

my %shading = (

4101

bg_color => 'green',

4102

pattern => 1,

4103

);

4104

</PRE>

4105

4106

4107

<PRE>

4108

my $format1 = $workbook->add_format(%font); # Font only

4109

my $format2 = $workbook->add_format(%font, %shading); # Font and shading

4110

</PRE>

4111

4112

4113

The provision of two ways of setting properties might lead you to wonder

4114

which is the best way. The answer depends on the amount of formatting that

4115

will be required in your program. Initially, Spreadsheet::WriteExcel only

4116

allowed individual Format properties to be set via the appropriate method.

4117

While this was sufficient for most circumstances it proved very cumbersome

4118

in programs that required a large amount of formatting. In addition the

4119

mechanism for reusing properties between Format objects was complicated.

4120

4121

4122

4123

As a result the Perl/Tk style of adding properties was added to, hopefully,

4124

facilitate developers who need to define a lot of formatting. In fact the

4125

Tk style of defining properties is also supported:

4126

4127

4128

4129

<PRE>

4130

my %font = (

4131

-font => 'Arial',

4132

-size => 12,

4133

-color => 'blue',

4134

-bold => 1,

4135

);

4136

</PRE>

4137

4138

4139

An additional advantage of working with hashes of properties is that it

4140

allows you to share formatting between workbook objects

4141

4142

4143

4144

You can also create a format "on the fly" and pass it directly to a write

4145

method as follows:

4146

4147

4148

4149

<PRE>

4150

$worksheet->write('A1', "Title", $workbook->add_format(bold => 1));

4151

</PRE>

4152

4153

4154

This corresponds to an "anonymous" format in the Perl sense of anonymous

4155

data or subs.

4156

4157

4158

4159

<HR>

4160

<H2><A NAME="Working_with_formats">Working with formats</A></H2>

4161

4162

The default format is Arial 10 with all other properties off.

4163

4164

4165

4166

Each unique format in Spreadsheet::WriteExcel must have a corresponding

4167

Format object. It isn't possible to use a Format with a

4168

<CODE>write()</CODE> method and then redefine the Format for use at a later

4169

stage. This is because a Format is applied to a cell not in its current

4170

state but in its final state. Consider the following example:

4171

4172

4173

4174

<PRE>

4175

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

4176

$format->set_bold();

4177

$format->set_color('red');

4178

$worksheet->write('A1', "Cell A1", $format);

4179

$format->set_color('green');

4180

$worksheet->write('B1', "Cell B1", $format);

4181

</PRE>

4182

4183

4184

Cell A1 is assigned the Format <CODE>$format</CODE> which is initially set to the colour red. However, the colour is

4185

subsequently set to green. When Excel displays Cell A1 it will display the

4186

final state of the Format which in this case will be the colour green.

4187

4188

4189

4190

In general a method call without an argument will turn a property on, for

4191

example:

4192

4193

4194

4195

<PRE>

4196

my $format1 = $workbook->add_format();

4197

$format1->set_bold(); # Turns bold on

4198

$format1->set_bold(1); # Also turns bold on

4199

$format1->set_bold(0); # Turns bold off

4200

</PRE>

4201

4202

4203

<HR>

4204

<H1><A NAME="FORMAT_METHODS">FORMAT METHODS</A></H1>

4205

4206

The Format object methods are described in more detail in the following

4207

sections. In addition, there is a Perl program called <CODE>formats.pl</CODE> in the <CODE>examples</CODE> directory of the WriteExcel distribution. This program creates an Excel

4208

workbook called <CODE>formats.xls</CODE> which contains examples of almost all the format types.

4209

4210

4211

4212

The following Format methods are available:

4213

4214

4215

4216

<PRE>

4217

set_font()

4218

set_size()

4219

set_color()

4220

set_bold()

4221

set_italic()

4222

set_underline()

4223

set_font_strikeout()

4224

set_font_script()

4225

set_font_outline()

4226

set_font_shadow()

4227

set_num_format()

4228

set_locked()

4229

set_hidden()

4230

set_align()

4231

set_align()

4232

set_rotation()

4233

set_text_wrap()

4234

set_text_justlast()

4235

set_center_across()

4236

set_indent()

4237

set_shrink()

4238

set_pattern()

4239

set_bg_color()

4240

set_fg_color()

4241

set_border()

4242

set_bottom()

4243

set_top()

4244

set_left()

4245

set_right()

4246

set_border_color()

4247

set_bottom_color()

4248

set_top_color()

4249

set_left_color()

4250

set_right_color()

4251

</PRE>

4252

4253

4254

The above methods can also be applied directly as properties. For example <CODE>$worksheet->set_bold()</CODE> is equivalent to <CODE>set_properties(bold => 1)</CODE>.

4255

4256

4257

4258

<HR>

4259

<H2><A NAME="set_properties_properties_">set_properties(%properties)</A></H2>

4260

4261

The properties of an existing Format object can be set by means of <CODE>set_properties()</CODE>:

4262

4263

4264

4265

<PRE>

4266

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

4267

$format->set_properties(bold => 1, color => 'red');

4268

</PRE>

4269

4270

4271

You can also store the properties in one or more named hashes and pass them

4272

to the <CODE>set_properties()</CODE> method:

4273

4274

4275

4276

<PRE>

4277

my %font = (

4278

font => 'Arial',

4279

size => 12,

4280

color => 'blue',

4281

bold => 1,

4282

);

4283

</PRE>

4284

4285

4286

<PRE>

4287

my $format = $workbook->set_properties(%font);

4288

</PRE>

4289

4290

4291

This method can be used as an alternative to setting the properties with <CODE>add_format()</CODE> or the specific format methods that are detailed in the following sections.

4292

4293

4294

4295

<HR>

4296

<H2><A NAME="set_font_fontname_">set_font($fontname)</A></H2>

4297

4298

<PRE>

4299

Default state: Font is Arial

4300

Default action: None

4301

Valid args: Any valid font name

4302

</PRE>

4303

4304

4305

Specify the font used:

4306

4307

4308

4309

<PRE>

4310

$format->set_font('Times New Roman');

4311

</PRE>

4312

4313

4314

Excel can only display fonts that are installed on the system that it is

4315

running on. Therefore it is best to use the fonts that come as standard

4316

such as 'Arial', 'Times New Roman' and 'Courier New'. See also the Fonts

4317

worksheet created by formats.pl

4318

4319

4320

4321

<HR>

4322

4323

4324

<PRE>

4325

Default state: Font size is 10

4326

Default action: Set font size to 1

4327

Valid args: Integer values from 1 to as big as your screen.

4328

</PRE>

4329

4330

4331

Set the font size. Excel adjusts the height of a row to accommodate the

4332

largest font size in the row. You can also explicitly specify the height of

4333

a row using the <CODE>set_row()</CODE> worksheet method.

4334

4335

4336

4337

<PRE>

4338

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

4339

$format->set_size(30);

4340

</PRE>

4341

4342

4343

<HR>

4344

<H2><A NAME="set_color_">set_color()</A></H2>

4345

4346

<PRE>

4347

Default state: Excels default color, usually black

4348

Default action: Set the default color

4349

Valid args: Integers from 8..63 or the following strings:

4350

'black'

4351

'blue'

4352

'brown'

4353

'cyan'

4354

'gray'

4355

'green'

4356

'lime'

4357

'magenta'

4358

'navy'

4359

'orange'

4360

'purple'

4361

'red'

4362

'silver'

4363

'white'

4364

'yellow'

4365

</PRE>

4366

4367

4368

Set the font colour. The <CODE>set_color()</CODE> method is used as follows:

4369

4370

4371

4372

<PRE>

4373

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

4374

$format->set_color('red');

4375

$worksheet->write(0, 0, "wheelbarrow", $format);

4376

</PRE>

4377

4378

4379

Note: The <CODE>set_color()</CODE> method is used to set the colour of the font in a cell. To set the colour

4380

of a cell use the <CODE>set_bg_color()</CODE> and <CODE>set_pattern()</CODE> methods.

4381

4382

4383

4384

For additional examples see the 'Named colors' and 'Standard colors'

4385

worksheets created by formats.pl in the examples directory.

4386

4387

4388

4389

See also <A HREF="#COLOURS_IN_EXCEL">COLOURS IN EXCEL</A>.

4390

4391

4392

4393

<HR>

4394

4395

4396

<PRE>

4397

Default state: bold is off

4398

Default action: Turn bold on

4399

Valid args: 0, 1 [1]

4400

</PRE>

4401

4402

4403

Set the bold property of the font:

4404

4405

4406

4407

<PRE>

4408

$format->set_bold(); # Turn bold on

4409

</PRE>

4410

4411

4412

[1] Actually, values in the range 100..1000 are also valid. 400 is normal,

4413

700 is bold and 1000 is very bold indeed. It is probably best to set the

4414

value to 1 and use normal bold.

4415

4416

4417

4418

<HR>

4419

<H2><A NAME="set_italic_">set_italic()</A></H2>

4420

4421

<PRE>

4422

Default state: Italic is off

4423

Default action: Turn italic on

4424

Valid args: 0, 1

4425

</PRE>

4426

4427

4428

Set the italic property of the font:

4429

4430

4431

4432

<PRE>

4433

$format->set_italic(); # Turn italic on

4434

</PRE>

4435

4436

4437

<HR>

4438

<H2><A NAME="set_underline_">set_underline()</A></H2>

4439

4440

<PRE>

4441

Default state: Underline is off

4442

Default action: Turn on single underline

4443

Valid args: 0 = No underline

4444

1 = Single underline

4445

2 = Double underline

4446

33 = Single accounting underline

4447

34 = Double accounting underline

4448

</PRE>

4449

4450

4451

Set the underline property of the font.

4452

4453

4454

4455

<PRE>

4456

$format->set_underline(); # Single underline

4457

</PRE>

4458

4459

4460

<HR>

4461

<H2><A NAME="set_font_strikeout_">set_font_strikeout()</A></H2>

4462

4463

<PRE>

4464

Default state: Strikeout is off

4465

Default action: Turn strikeout on

4466

Valid args: 0, 1

4467

</PRE>

4468

4469

4470

Set the strikeout property of the font.

4471

4472

4473

4474

<HR>

4475

<H2><A NAME="set_font_script_">set_font_script()</A></H2>

4476

4477

<PRE>

4478

Default state: Super/Subscript is off

4479

Default action: Turn Superscript on

4480

Valid args: 0 = Normal

4481

1 = Superscript

4482

2 = Subscript

4483

</PRE>

4484

4485

4486

Set the superscript/subscript property of the font. This format is

4487

currently not very useful.

4488

4489

4490

4491

<HR>

4492

<H2><A NAME="set_font_outline_">set_font_outline()</A></H2>

4493

4494

<PRE>

4495

Default state: Outline is off

4496

Default action: Turn outline on

4497

Valid args: 0, 1

4498

</PRE>

4499

4500

4501

Macintosh only.

4502

4503

4504

4505

<HR>

4506

<H2><A NAME="set_font_shadow_">set_font_shadow()</A></H2>

4507

4508

<PRE>

4509

Default state: Shadow is off

4510

Default action: Turn shadow on

4511

Valid args: 0, 1

4512

</PRE>

4513

4514

4515

Macintosh only.

4516

4517

4518

4519

<HR>

4520

<H2><A NAME="set_num_format_">set_num_format()</A></H2>

4521

4522

<PRE>

4523

Default state: General format

4524

Default action: Format index 1

4525

Valid args: See the following table

4526

</PRE>

4527

4528

4529

This method is used to define the numerical format of a number in Excel. It

4530

controls whether a number is displayed as an integer, a floating point

4531

number, a date, a currency value or some other user defined format.

4532

4533

4534

4535

The numerical format of a cell can be specified by using a format string or

4536

an index to one of Excel's built-in formats:

4537

4538

4539

4540

<PRE>

4541

my $format1 = $workbook->add_format();

4542

my $format2 = $workbook->add_format();

4543

$format1->set_num_format('d mmm yyyy'); # Format string

4544

$format2->set_num_format(0x0f); # Format index

4545

</PRE>

4546

4547

4548

<PRE>

4549

$worksheet->write(0, 0, 36892.521, $format1); # 1 Jan 2001

4550

$worksheet->write(0, 0, 36892.521, $format2); # 1-Jan-01

4551

</PRE>

4552

4553

4554

Using format strings you can define very sophisticated formatting of

4555

numbers.

4556

4557

4558

4559

<PRE>

4560

$format01->set_num_format('0.000');

4561

$worksheet->write(0, 0, 3.1415926, $format01); # 3.142

4562

</PRE>

4563

4564

4565

<PRE>

4566

$format02->set_num_format('#,##0');

4567

$worksheet->write(1, 0, 1234.56, $format02); # 1,235

4568

</PRE>

4569

4570

4571

<PRE>

4572

$format03->set_num_format('#,##0.00');

4573

$worksheet->write(2, 0, 1234.56, $format03); # 1,234.56

4574

</PRE>

4575

4576

4577

<PRE>

4578

$format04->set_num_format('$0.00');

4579

$worksheet->write(3, 0, 49.99, $format04); # $49.99

4580

</PRE>

4581

4582

4583

<PRE>

4584

$format05->set_num_format('�0.00');

4585

$worksheet->write(4, 0, 49.99, $format05); # �49.99

4586

</PRE>

4587

4588

4589

<PRE>

4590

$format06->set_num_format('�0.00');

4591

$worksheet->write(5, 0, 49.99, $format06); # �49.99

4592

</PRE>

4593

4594

4595

<PRE>

4596

$format07->set_num_format('mm/dd/yy');

4597

$worksheet->write(6, 0, 36892.521, $format07); # 01/01/01

4598

</PRE>

4599

4600

4601

<PRE>

4602

$format08->set_num_format('mmm d yyyy');

4603

$worksheet->write(7, 0, 36892.521, $format08); # Jan 1 2001

4604

</PRE>

4605

4606

4607

<PRE>

4608

$format09->set_num_format('d mmmm yyyy');

4609

$worksheet->write(8, 0, 36892.521, $format09); # 1 January 2001

4610

</PRE>

4611

4612

4613

<PRE>

4614

$format10->set_num_format('dd/mm/yyyy hh:mm AM/PM');

4615

$worksheet->write(9, 0, 36892.521, $format10); # 01/01/2001 12:30 AM

4616

</PRE>

4617

4618

4619

<PRE>

4620

$format11->set_num_format('0 "dollar and" .00 "cents"');

4621

$worksheet->write(10, 0, 1.87, $format11); # 1 dollar and .87 cents

4622

</PRE>

4623

4624

4625

<PRE>

4626

# Conditional formatting

4627

$format12->set_num_format('[Green]General;[Red]-General;General');

4628

$worksheet->write(11, 0, 123, $format12); # > 0 Green

4629

$worksheet->write(12, 0, -45, $format12); # < 0 Red

4630

$worksheet->write(13, 0, 0, $format12); # = 0 Default colour

4631

</PRE>

4632

4633

4634

<PRE>

4635

# Zip code

4636

$format13->set_num_format('00000');

4637

$worksheet->write(14, 0, '01209', $format13);

4638

</PRE>

4639

4640

4641

The number system used for dates is described in <A HREF="#DATES_IN_EXCEL">DATES IN EXCEL</A>.

4642

4643

4644

4645

The colour format should have one of the following values:

4646

4647

4648

4649

<PRE>

4650

[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]

4651

</PRE>

4652

4653

4654

Alternatively you can specify the colour based on a colour index as

4655

follows: <CODE>[Color n]</CODE>, where n is a standard Excel colour index - 7. See the 'Standard colors'

4656

worksheet created by formats.pl.

4657

4658

4659

4660

For more information refer to the documentation on formatting in the <CODE>doc</CODE> directory of the Spreadsheet::WriteExcel distro, the Excel on-line help or

4661

4662

HREF="http://office.microsoft.com/en-gb/assistance/HP051995001033.aspx">http://office.microsoft.com/en-gb/assistance/HP051995001033.aspx</A>

4663

4664

4665

4666

4667

You should ensure that the format string is valid in Excel prior to using

4668

it in WriteExcel.

4669

4670

4671

4672

Excel's built-in formats are shown in the following table:

4673

4674

4675

4676

<PRE>

4677

Index Index Format String

4678

0 0x00 General

4679

1 0x01 0

4680

2 0x02 0.00

4681

3 0x03 #,##0

4682

4 0x04 #,##0.00

4683

5 0x05 ($#,##0_);($#,##0)

4684

6 0x06 ($#,##0_);[Red]($#,##0)

4685

7 0x07 ($#,##0.00_);($#,##0.00)

4686

8 0x08 ($#,##0.00_);[Red]($#,##0.00)

4687

9 0x09 0%

4688

10 0x0a 0.00%

4689

11 0x0b 0.00E+00

4690

12 0x0c # ?/?

4691

13 0x0d # ??/??

4692

14 0x0e m/d/yy

4693

15 0x0f d-mmm-yy

4694

16 0x10 d-mmm

4695

17 0x11 mmm-yy

4696

18 0x12 h:mm AM/PM

4697

19 0x13 h:mm:ss AM/PM

4698

20 0x14 h:mm

4699

21 0x15 h:mm:ss

4700

22 0x16 m/d/yy h:mm

4701

.. .... ...........

4702

37 0x25 (#,##0_);(#,##0)

4703

38 0x26 (#,##0_);[Red](#,##0)

4704

39 0x27 (#,##0.00_);(#,##0.00)

4705

40 0x28 (#,##0.00_);[Red](#,##0.00)

4706

41 0x29 _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)

4707

42 0x2a _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)

4708

43 0x2b _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

4709

44 0x2c _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

4710

45 0x2d mm:ss

4711

46 0x2e [h]:mm:ss

4712

47 0x2f mm:ss.0

4713

48 0x30 ##0.0E+0

4714

49 0x31 @

4715

</PRE>

4716

4717

4718

For examples of these formatting codes see the 'Numerical formats'

4719

worksheet created by formats.pl. See also the number_formats1.html and the

4720

number_formats2.html documents in the <CODE>doc</CODE> directory of the distro.

4721

4722

4723

4724

Note 1. Numeric formats 23 to 36 are not documented by Microsoft and may

4725

differ in international versions.

4726

4727

4728

4729

Note 2. In Excel 5 the dollar sign appears as a dollar sign. In Excel

4730

97-2000 it appears as the defined local currency symbol.

4731

4732

4733

4734

Note 3. The red negative numeric formats display slightly differently in

4735

Excel 5 and Excel 97-2000.

4736

4737

4738

4739

<HR>

4740

<H2><A NAME="set_locked_">set_locked()</A></H2>

4741

4742

<PRE>

4743

Default state: Cell locking is on

4744

Default action: Turn locking on

4745

Valid args: 0, 1

4746

</PRE>

4747

4748

4749

This property can be used to prevent modification of a cells contents.

4750

Following Excel's convention, cell locking is turned on by default.

4751

However, it only has an effect if the worksheet has been protected, see the

4752

worksheet <CODE>protect()</CODE> method.

4753

4754

4755

4756

<PRE>

4757

my $locked = $workbook->add_format();

4758

$locked->set_locked(1); # A non-op

4759

</PRE>

4760

4761

4762

<PRE>

4763

my $unlocked = $workbook->add_format();

4764

$locked->set_locked(0);

4765

</PRE>

4766

4767

4768

<PRE>

4769

# Enable worksheet protection

4770

$worksheet->protect();

4771

</PRE>

4772

4773

4774

<PRE>

4775

# This cell cannot be edited.

4776

$worksheet->write('A1', '=1+2', $locked);

4777

</PRE>

4778

4779

4780

<PRE>

4781

# This cell can be edited.

4782

$worksheet->write('A2', '=1+2', $unlocked);

4783

</PRE>

4784

4785

4786

Note: This offers weak protection even with a password, see the note in

4787

relation to the <CODE>protect()</CODE> method.

4788

4789

4790

4791

<HR>

4792

<H2><A NAME="set_hidden_">set_hidden()</A></H2>

4793

4794

<PRE>

4795

Default state: Formula hiding is off

4796

Default action: Turn hiding on

4797

Valid args: 0, 1

4798

</PRE>

4799

4800

4801

This property is used to hide a formula while still displaying its result.

4802

This is generally used to hide complex calculations from end users who are

4803

only interested in the result. It only has an effect if the worksheet has

4804

been protected, see the worksheet <CODE>protect()</CODE> method.

4805

4806

4807

4808

<PRE>

4809

my $hidden = $workbook->add_format();

4810

$hidden->set_hidden();

4811

</PRE>

4812

4813

4814

<PRE>

4815

# Enable worksheet protection

4816

$worksheet->protect();

4817

</PRE>

4818

4819

4820

<PRE>

4821

# The formula in this cell isn't visible

4822

$worksheet->write('A1', '=1+2', $hidden);

4823

</PRE>

4824

4825

4826

Note: This offers weak protection even with a password, see the note in

4827

relation to the <CODE>protect()</CODE> method.

4828

4829

4830

4831

<HR>

4832

<H2><A NAME="set_align_">set_align()</A></H2>

4833

4834

<PRE>

4835

Default state: Alignment is off

4836

Default action: Left alignment

4837

Valid args: 'left' Horizontal

4838

'center'

4839

'right'

4840

'fill'

4841

'justify'

4842

'center_across'

4843

</PRE>

4844

4845

4846

<PRE>

4847

'top' Vertical

4848

'vcenter'

4849

'bottom'

4850

'vjustify'

4851

</PRE>

4852

4853

4854

This method is used to set the horizontal and vertical text alignment

4855

within a cell. Vertical and horizontal alignments can be combined. The

4856

method is used as follows:

4857

4858

4859

4860

<PRE>

4861

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

4862

$format->set_align('center');

4863

$format->set_align('vcenter');

4864

$worksheet->set_row(0, 30);

4865

$worksheet->write(0, 0, "X", $format);

4866

</PRE>

4867

4868

4869

Text can be aligned across two or more adjacent cells using the <CODE>center_across</CODE> property. However, for genuine merged cells it is better to use the <CODE>merge_range()</CODE> worksheet method.

4870

4871

4872

4873

The <CODE>vjustify</CODE> (vertical justify) option can be used to provide automatic text wrapping in

4874

a cell. The height of the cell will be adjusted to accommodate the wrapped

4875

text. To specify where the text wraps use the <CODE>set_text_wrap()</CODE> method.

4876

4877

4878

4879

For further examples see the 'Alignment' worksheet created by formats.pl.

4880

4881

4882

4883

<HR>

4884

<H2><A NAME="set_center_across_">set_center_across()</A></H2>

4885

4886

<PRE>

4887

Default state: Center across selection is off

4888

Default action: Turn center across on

4889

Valid args: 1

4890

</PRE>

4891

4892

4893

Text can be aligned across two or more adjacent cells using the <CODE>set_center_across()</CODE> method. This is an alias for the <CODE>set_align('center_across')</CODE> method call.

4894

4895

4896

4897

Only one cell should contain the text, the other cells should be blank:

4898

4899

4900

4901

<PRE>

4902

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

4903

$format->set_center_across();

4904

</PRE>

4905

4906

4907

<PRE>

4908

$worksheet->write(1, 1, 'Center across selection', $format);

4909

$worksheet->write_blank(1, 2, $format);

4910

</PRE>

4911

4912

4913

See also the <CODE>merge1.pl</CODE> to <CODE>merge5.pl</CODE> programs in the <CODE>examples</CODE> directory and the <CODE>merge_range()</CODE> method.

4914

4915

4916

4917

<HR>

4918

4919

4920

<PRE>

4921

Default state: Text wrap is off

4922

Default action: Turn text wrap on

4923

Valid args: 0, 1

4924

</PRE>

4925

4926

4927

Here is an example using the text wrap property, the escape character <CODE>\n</CODE> is used to indicate the end of line:

4928

4929

4930

4931

<PRE>

4932

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

4933

$format->set_text_wrap();

4934

$worksheet->write(0, 0, "It's\na bum\nwrap", $format);

4935

</PRE>

4936

4937

4938

Excel will adjust the height of the row to accommodate the wrapped text. A

4939

similar effect can be obtained without newlines using the <CODE>set_align('vjustify')</CODE> method. See the <CODE>textwrap.pl</CODE> program in the <CODE>examples</CODE> directory.

4940

4941

4942

4943

<HR>

4944

<H2><A NAME="set_rotation_">set_rotation()</A></H2>

4945

4946

<PRE>

4947

Default state: Text rotation is off

4948

Default action: None

4949

Valid args: Integers in the range -90 to 90 and 270

4950

</PRE>

4951

4952

4953

Set the rotation of the text in a cell. The rotation can be any angle in

4954

the range -90 to 90 degrees.

4955

4956

4957

4958

<PRE>

4959

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

4960

$format->set_rotation(30);

4961

$worksheet->write(0, 0, "This text is rotated", $format);

4962

</PRE>

4963

4964

4965

The angle 270 is also supported. This indicates text where the letters run

4966

from top to bottom.

4967

4968

4969

4970

<HR>

4971

<H2><A NAME="set_indent_">set_indent()</A></H2>

4972

4973

<PRE>

4974

Default state: Text indentation is off

4975

Default action: Indent text 1 level

4976

Valid args: Positive integers

4977

</PRE>

4978

4979

4980

This method can be used to indent text. The argument, which should be an

4981

integer, is taken as the level of indentation:

4982

4983

4984

4985

<PRE>

4986

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

4987

$format->set_indent(2);

4988

$worksheet->write(0, 0, "This text is indented", $format);

4989

</PRE>

4990

4991

4992

Indentation is a horizontal alignment property. It will override any other

4993

horizontal properties but it can be used in conjunction with vertical

4994

properties.

4995

4996

4997

4998

<HR>

4999

<H2><A NAME="set_shrink_">set_shrink()</A></H2>

5000

5001

<PRE>

5002

Default state: Text shrinking is off

5003

Default action: Turn "shrink to fit" on

5004

Valid args: 1

5005

</PRE>

5006

5007

5008

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

5009

5010

5011

5012

<PRE>

5013

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

5014

$format->set_shrink();

5015

$worksheet->write(0, 0, "Honey, I shrunk the text!", $format);

5016

</PRE>

5017

5018

5019

<HR>

5020

<H2><A NAME="set_text_justlast_">set_text_justlast()</A></H2>

5021

5022

<PRE>

5023

Default state: Justify last is off

5024

Default action: Turn justify last on

5025

Valid args: 0, 1

5026

</PRE>

5027

5028

5029

Only applies to Far Eastern versions of Excel.

5030

5031

5032

5033

<HR>

5034

<H2><A NAME="set_pattern_">set_pattern()</A></H2>

5035

5036

<PRE>

5037

Default state: Pattern is off

5038

Default action: Solid fill is on

5039

Valid args: 0 .. 18

5040

</PRE>

5041

5042

5043

Set the background pattern of a cell.

5044

5045

5046

5047

Examples of the available patterns are shown in the 'Patterns' worksheet

5048

created by formats.pl. However, it is unlikely that you will ever need

5049

anything other than Pattern 1 which is a solid fill of the background

5050

color.

5051

5052

5053

5054

<HR>

5055

<H2><A NAME="set_bg_color_">set_bg_color()</A></H2>

5056

5057

<PRE>

5058

Default state: Color is off

5059

Default action: Solid fill.

5060

Valid args: See set_color()

5061

</PRE>

5062

5063

5064

The <CODE>set_bg_color()</CODE> method can be used to set the background colour of a pattern. Patterns are

5065

defined via the <CODE>set_pattern()</CODE> method. If a pattern hasn't been defined then a solid fill pattern is used

5066

as the default.

5067

5068

5069

5070

Here is an example of how to set up a solid fill in a cell:

5071

5072

5073

5074

<PRE>

5075

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

5076

</PRE>

5077

5078

5079

<PRE>

5080

$format->set_pattern(); # This is optional when using a solid fill

5081

</PRE>

5082

5083

5084

<PRE>

5085

$format->set_bg_color('green');

5086

$worksheet->write('A1', 'Ray', $format);

5087

</PRE>

5088

5089

5090

For further examples see the 'Patterns' worksheet created by formats.pl.

5091

5092

5093

5094

<HR>

5095

<H2><A NAME="set_fg_color_">set_fg_color()</A></H2>

5096

5097

<PRE>

5098

Default state: Color is off

5099

Default action: Solid fill.

5100

Valid args: See set_color()

5101

</PRE>

5102

5103

5104

The <CODE>set_fg_color()</CODE> method can be used to set the foreground colour of a pattern.

5105

5106

5107

5108

For further examples see the 'Patterns' worksheet created by formats.pl.

5109

5110

5111

5112

<HR>

5113

<H2><A NAME="set_border_">set_border()</A></H2>

5114

5115

<PRE>

5116

Also applies to: set_bottom()

5117

set_top()

5118

set_left()

5119

set_right()

5120

</PRE>

5121

5122

5123

<PRE>

5124

Default state: Border is off

5125

Default action: Set border type 1

5126

Valid args: 0 No border

5127

1 Thin single border

5128

2 Medium single border

5129

3 Dashed border

5130

4 Dotted border

5131

5 Thick single border

5132

6 Double line border

5133

7 Hair border

5134

</PRE>

5135

5136

5137

A cell border is comprised of a border on the bottom, top, left and right.

5138

These can be set to the same value using <CODE>set_border()</CODE> or individually using the relevant method calls shown above. Examples of

5139

the available border styles are shown in the 'Borders' worksheet created by

5140

formats.pl.

5141

5142

5143

5144

<HR>

5145

<H2><A NAME="set_border_color_">set_border_color()</A></H2>

5146

5147

<PRE>

5148

Also applies to: set_bottom_color()

5149

set_top_color()

5150

set_left_color()

5151

set_right_color()

5152

</PRE>

5153

5154

5155

<PRE>

5156

Default state: Color is off

5157

Default action: Undefined

5158

Valid args: See set_color()

5159

</PRE>

5160

5161

5162

Set the colour of the cell borders. A cell border is comprised of a border

5163

on the bottom, top, left and right. These can be set to the same colour

5164

using <CODE>set_border_color()</CODE> or individually using the relevant method calls shown above. Examples of

5165

the border styles and colours are shown in the 'Borders' worksheet created

5166

by formats.pl.

5167

5168

5169

5170

<HR>

5171

<H2><A NAME="copy_format_">copy($format)</A></H2>

5172

5173

This method is used to copy all of the properties from one Format object to

5174

another:

5175

5176

5177

5178

<PRE>

5179

my $lorry1 = $workbook->add_format();

5180

$lorry1->set_bold();

5181

$lorry1->set_italic();

5182

$lorry1->set_color('red'); # lorry1 is bold, italic and red

5183

</PRE>

5184

5185

5186

<PRE>

5187

my $lorry2 = $workbook->add_format();

5188

$lorry2->copy($lorry1);

5189

$lorry2->set_color('yellow'); # lorry2 is bold, italic and yellow

5190

</PRE>

5191

5192

5193

The <CODE>copy()</CODE> method is only useful if you are using the method interface to Format

5194

properties. It generally isn't required if you are setting Format

5195

properties directly using hashes.

5196

5197

5198

5199

Note: this is not a copy constructor, both objects must exist prior to

5200

copying.

5201

5202

5203

5204

<HR>

5205

<H1><A NAME="COLOURS_IN_EXCEL">COLOURS IN EXCEL</A></H1>

5206

5207

Excel provides a colour palette of 56 colours. In Spreadsheet::WriteExcel

5208

these colours are accessed via their palette index in the range 8..63. This

5209

index is used to set the colour of fonts, cell patterns and cell borders.

5210

For example:

5211

5212

5213

5214

<PRE>

5215

my $format = $workbook->add_format(

5216

color => 12, # index for blue

5217

font => 'Arial',

5218

size => 12,

5219

bold => 1,

5220

);

5221

</PRE>

5222

5223

5224

The most commonly used colours can also be accessed by name. The name acts

5225

as a simple alias for the colour index:

5226

5227

5228

5229

<PRE>

5230

black => 8

5231

blue => 12

5232

brown => 16

5233

cyan => 15

5234

gray => 23

5235

green => 17

5236

lime => 11

5237

magenta => 14

5238

navy => 18

5239

orange => 53

5240

purple => 20

5241

red => 10

5242

silver => 22

5243

white => 9

5244

yellow => 13

5245

</PRE>

5246

5247

5248

For example:

5249

5250

5251

5252

<PRE>

5253

my $font = $workbook->add_format(color => 'red');

5254

</PRE>

5255

5256

5257

Users of VBA in Excel should note that the equivalent colour indices are in

5258

the range 1..56 instead of 8..63.

5259

5260

5261

5262

If the default palette does not provide a required colour you can override

5263

one of the built-in values. This is achieved by using the <CODE>set_custom_color()</CODE> workbook method to adjust the RGB (red green blue) components of the

5264

colour:

5265

5266

5267

5268

<PRE>

5269

my $ferrari = $workbook->set_custom_color(40, 216, 12, 12);

5270

</PRE>

5271

5272

5273

<PRE>

5274

my $format = $workbook->add_format(

5275

bg_color => $ferrari,

5276

pattern => 1,

5277

border => 1

5278

);

5279

</PRE>

5280

5281

5282

<PRE>

5283

$worksheet->write_blank('A1', $format);

5284

</PRE>

5285

5286

5287

The default Excel 97 colour palette is shown in <CODE>palette.html</CODE> in the <CODE>doc</CODE> directory of the distro. You can generate an Excel version of the palette

5288

using <CODE>colors.pl</CODE> in the <CODE>examples</CODE> directory.

5289

5290

5291

5292

A comparison of the colour components in the Excel 5 and Excel 97+ colour

5293

palettes is shown in <CODE>rgb5-97.txt</CODE> in the <CODE>doc</CODE> directory.

5294

5295

5296

5297

You may also find the following links helpful:

5298

5299

5300

5301

A detailed look at Excel's colour palette: <A

5302

HREF="http://www.geocities.com/davemcritchie/excel/colors.htm">http://www.geocities.com/davemcritchie/excel/colors.htm</A>

5303

5304

5305

5306

5307

A decimal RGB chart: <A

5308

HREF="http://www.hypersolutions.org/pages/rgbdec.html">http://www.hypersolutions.org/pages/rgbdec.html</A>

5309

5310

5311

5312

5313

A hex RGB chart: : <A

5314

HREF="http://www.hypersolutions.org/pages/rgbhex.html">http://www.hypersolutions.org/pages/rgbhex.html</A>

5315

5316

5317

5318

5319

<HR>

5320

<H1><A NAME="DATES_IN_EXCEL">DATES IN EXCEL</A></H1>

5321

5322

Dates and times in Excel are represented by real numbers, for example "Jan

5323

1 2001 12:30 AM" is represented by the number 36892.521.

5324

5325

5326

5327

The integer part of the number stores the number of days since the epoch

5328

and the fractional part stores the percentage of the day.

5329

5330

5331

5332

The epoch can be either 1900 or 1904. Excel for Windows uses 1900 and Excel

5333

for Macintosh uses 1904. The epochs are:

5334

5335

5336

5337

<PRE>

5338

1900: 0 January 1900 i.e. 31 December 1899

5339

1904: 1 January 1904

5340

</PRE>

5341

5342

5343

By default Spreadsheet::WriteExcel uses the Windows/1900 format although it

5344

generally isn't an issue since Excel on Windows and the Macintosh will

5345

convert automatically between one system and the other. To use the 1904

5346

epoch you must use the <CODE>set_1904()</CODE> workbook method.

5347

5348

5349

5350

There are two things to note about the 1900 date format. The first is that

5351

the epoch starts on 0 January 1900. The second is that the year 1900 is

5352

erroneously but deliberately treated as a leap year. Therefore you must add

5353

an extra day to dates after 28 February 1900. The reason for this anomaly

5354

is explained at <A

5355

HREF="http://support.microsoft.com/support/kb/articles/Q181/3/70.asp">http://support.microsoft.com/support/kb/articles/Q181/3/70.asp</A>

5356

5357

5358

5359

5360

A date or time in Excel is like any other number. To display the number as

5361

a date you must apply a number format to it. Refer to the <CODE>set_num_format()</CODE> method above:

5362

5363

5364

5365

<PRE>

5366

$format->set_num_format('mmm d yyyy hh:mm AM/PM');

5367

$worksheet->write('A1', 36892.521 , $format); # Jan 1 2001 12:30 AM

5368

</PRE>

5369

5370

5371

You can also use the <CODE>write_date_time()</CODE> worksheet method to write dates in ISO8601 date format.

5372

5373

5374

5375

<PRE>

5376

$worksheet->write_date_time('A2', '2001-01-01T12:20', format);

5377

</PRE>

5378

5379

5380

See the <CODE>write_date_time()</CODE> section of the documentation for more details.

5381

5382

5383

5384

See also the <CODE>Spreadsheet::WriteExcel::Utility</CODE> module that is included in the distro and which includes date handling

5385

functions and the DateTime::Format::Excel module, <A

5386

HREF="http://search.cpan.org/search?dist=DateTime-Format-Excel">http://search.cpan.org/search?dist=DateTime-Format-Excel</A>

5387

which is part of the DateTime project and which deals specifically with

5388

converting dates and times to and from Excel's format.

5389

5390

5391

5392

<HR>

5393

<H1><A NAME="OUTLINES_AND_GROUPING_IN_EXCEL">OUTLINES AND GROUPING IN EXCEL</A></H1>

5394

5395

Excel allows you to group rows or columns so that they can be hidden or

5396

displayed with a single mouse click. This feature is referred to as

5397

outlines.

5398

5399

5400

5401

Outlines can reduce complex data down to a few salient sub-totals or

5402

summaries.

5403

5404

5405

5406

This feature is best viewed in Excel but the following is an ASCII

5407

representation of what a worksheet with three outlines might look like.

5408

Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at level

5409

1. The lines at the left hand side are called outline level bars.

5410

5411

5412

5413

<PRE>

5414

------------------------------------------

5415

1 2 3 | | A | B | C | D | ...

5416

------------------------------------------

5417

_ | 1 | A | | | | ...

5418

| _ | 2 | B | | | | ...

5419

| | | 3 | (C) | | | | ...

5420

| | | 4 | (D) | | | | ...

5421

| - | 5 | E | | | | ...

5422

| _ | 6 | F | | | | ...

5423

| | | 7 | (G) | | | | ...

5424

| | | 8 | (H) | | | | ...

5425

| - | 9 | I | | | | ...

5426

- | . | ... | ... | ... | ... | ...

5427

</PRE>

5428

5429

5430

Clicking the minus sign on each of the level 2 outlines will collapse and

5431

hide the data as shown in the next figure. The minus sign changes to a plus

5432

sign to indicate that the data in the outline is hidden.

5433

5434

5435

5436

<PRE>

5437

------------------------------------------

5438

1 2 3 | | A | B | C | D | ...

5439

------------------------------------------

5440

_ | 1 | A | | | | ...

5441

| | 2 | B | | | | ...

5442

| + | 5 | E | | | | ...

5443

| | 6 | F | | | | ...

5444

| + | 9 | I | | | | ...

5445

- | . | ... | ... | ... | ... | ...

5446

</PRE>

5447

5448

5449

Clicking on the minus sign on the level 1 outline will collapse the

5450

remaining rows as follows:

5451

5452

5453

5454

<PRE>

5455

------------------------------------------

5456

1 2 3 | | A | B | C | D | ...

5457

------------------------------------------

5458

| 1 | A | | | | ...

5459

+ | . | ... | ... | ... | ... | ...

5460

</PRE>

5461

5462

5463

Grouping in <CODE>Spreadsheet::WriteExcel</CODE> is achieved by setting the outline level via the <CODE>set_row()</CODE> and <CODE>set_column()</CODE> worksheet methods:

5464

5465

5466

5467

<PRE>

5468

set_row($row, $height, $format, $hidden, $level)

5469

set_column($first_col, $last_col, $width, $format, $hidden, $level)

5470

</PRE>

5471

5472

5473

The following example sets an outline level of 1 for rows 1 and 2

5474

(zero-indexed) and columns B to G. The parameters <CODE>$height</CODE> and <CODE>$XF</CODE> are assigned default values since they are undefined:

5475

5476

5477

5478

<PRE>

5479

$worksheet->set_row(1, undef, undef, 0, 1);

5480

$worksheet->set_row(2, undef, undef, 0, 1);

5481

$worksheet->set_column('B:G', undef, undef, 0, 1);

5482

</PRE>

5483

5484

5485

Excel allows up to 7 outline levels. Therefore the <CODE>$level</CODE> parameter should be in the range <CODE>0 <= $level <= 7</CODE>.

5486

5487

5488

5489

Rows and columns can be collapsed by setting the <CODE>$hidden</CODE> flag:

5490

5491

5492

5493

<PRE>

5494

$worksheet->set_row(1, undef, undef, 1, 1);

5495

$worksheet->set_row(2, undef, undef, 1, 1);

5496

$worksheet->set_column('B:G', undef, undef, 1, 1);

5497

</PRE>

5498

5499

5500

For a more complete example see the <CODE>outline.pl</CODE> program in the examples directory of the distro.

5501

5502

5503

5504

Some additional outline properties can be set via the <CODE>outline_settings()</CODE> worksheet method, see above.

5505

5506

5507

5508

<HR>

5509

<H1><A NAME="FORMULAS_AND_FUNCTIONS_IN_EXCEL">FORMULAS AND FUNCTIONS IN EXCEL</A></H1>

5510

5511

<HR>

5512

<H2><A NAME="Caveats">Caveats</A></H2>

5513

5514

The first thing to note is that there are still some outstanding issues

5515

with the implementation of formulas and functions:

5516

5517

5518

5519

<PRE>

5520

1. Writing a formula is much slower than writing the equivalent string.

5521

2. You cannot use array constants, i.e. {1;2;3}, in functions.

5522

3. Unary minus isn't supported.

5523

4. Whitespace is not preserved around operators.

5524

5. Named ranges are not supported.

5525

6. Array formulas are not supported.

5526

</PRE>

5527

5528

5529

However, these constraints will be removed in future versions. They are

5530

here because of a trade-off between features and time. Also, it is possible

5531

to work around issue 1 using the <CODE>store_formula()</CODE> and <CODE>repeat_formula()</CODE> methods as described later in this section.

5532

5533

5534

5535

<HR>

5536

<H2><A NAME="Introduction">Introduction</A></H2>

5537

5538

The following is a brief introduction to formulas and functions in Excel

5539

and Spreadsheet::WriteExcel.

5540

5541

5542

5543

A formula is a string that begins with an equals sign:

5544

5545

5546

5547

<PRE>

5548

'=A1+B1'

5549

'=AVERAGE(1, 2, 3)'

5550

</PRE>

5551

5552

5553

The formula can contain numbers, strings, boolean values, cell references,

5554

cell ranges and functions. Named ranges are not supported. Formulas should

5555

be written as they appear in Excel, that is cells and functions must be in

5556

uppercase.

5557

5558

5559

5560

Cells in Excel are referenced using the A1 notation system where the column

5561

is designated by a letter and the row by a number. Columns range from A to

5562

IV i.e. 0 to 255, rows range from 1 to 65536. The <CODE>Spreadsheet::WriteExcel::Utility</CODE> module that is included in the distro contains helper functions for dealing

5563

with A1 notation, for example:

5564

5565

5566

5567

<PRE>

5568

use Spreadsheet::WriteExcel::Utility;

5569

</PRE>

5570

5571

5572

<PRE>

5573

($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)

5574

$str = xl_rowcol_to_cell(1, 2); # C2

5575

</PRE>

5576

5577

5578

The Excel <CODE>$</CODE> notation in cell references is also supported. This allows you to specify

5579

whether a row or column is relative or absolute. This only has an effect if

5580

the cell is copied. The following examples show relative and absolute

5581

values.

5582

5583

5584

5585

<PRE>

5586

'=A1' # Column and row are relative

5587

'=$A1' # Column is absolute and row is relative

5588

'=A$1' # Column is relative and row is absolute

5589

'=$A$1' # Column and row are absolute

5590

</PRE>

5591

5592

5593

Formulas can also refer to cells in other worksheets of the current

5594

workbook. For example:

5595

5596

5597

5598

<PRE>

5599

'=Sheet2!A1'

5600

'=Sheet2!A1:A5'

5601

'=Sheet2:Sheet3!A1'

5602

'=Sheet2:Sheet3!A1:A5'

5603

q{='Test Data'!A1}

5604

q{='Test Data1:Test Data2'!A1}

5605

</PRE>

5606

5607

5608

The sheet reference and the cell reference are separated by <CODE>!</CODE> the exclamation mark symbol. If worksheet names contain spaces, commas o

5609

parentheses then Excel requires that the name is enclosed in single quotes

5610

as shown in the last two examples above. In order to avoid using a lot of

5611

escape characters you can use the quote operator <CODE>q{}</CODE> to protect the quotes. See <CODE>perlop</CODE> in the main Perl documentation. Only valid sheet names that have been added

5612

using the <CODE>add_worksheet()</CODE> method can be used in formulas. You cannot reference external workbooks.

5613

5614

5615

5616

The following table lists the operators that are available in Excel's

5617

formulas. The majority of the operators are the same as Perl's, differences

5618

are indicated:

5619

5620

5621

5622

<PRE>

5623

Arithmetic operators:

5624

=====================

5625

Operator Meaning Example

5626

+ Addition 1+2

5627

- Subtraction 2-1

5628

* Multiplication 2*3

5629

/ Division 1/4

5630

^ Exponentiation 2^3 # Equivalent to **

5631

- Unary minus -(1+2) # Not yet supported

5632

% Percent (Not modulus) 13% # Not supported, [1]

5633

</PRE>

5634

5635

5636

<PRE>

5637

Comparison operators:

5638

=====================

5639

Operator Meaning Example

5640

= Equal to A1 = B1 # Equivalent to ==

5641

<> Not equal to A1 <> B1 # Equivalent to !=

5642

> Greater than A1 > B1

5643

< Less than A1 < B1

5644

>= Greater than or equal to A1 >= B1

5645

<= Less than or equal to A1 <= B1

5646

</PRE>

5647

5648

5649

<PRE>

5650

String operator:

5651

================

5652

Operator Meaning Example

5653

& Concatenation "Hello " & "World!" # [2]

5654

</PRE>

5655

5656

5657

<PRE>

5658

Reference operators:

5659

====================

5660

Operator Meaning Example

5661

: Range operator A1:A4 # [3]

5662

, Union operator SUM(1, 2+2, B3) # [4]

5663

</PRE>

5664

5665

5666

<PRE>

5667

Notes:

5668

[1]: You can get a percentage with formatting and modulus with MOD().

5669

[2]: Equivalent to ("Hello " . "World!") in Perl.

5670

[3]: This range is equivalent to cells A1, A2, A3 and A4.

5671

[4]: The comma behaves like the list separator in Perl.

5672

</PRE>

5673

5674

5675

The range and comma operators can have different symbols in non-English

5676

versions of Excel. These will be supported in a later version of

5677

Spreadsheet::WriteExcel. European users of Excel take note:

5678

5679

5680

5681

<PRE>

5682

$worksheet->write('A1', '=SUM(1; 2; 3)'); # Wrong!!

5683

$worksheet->write('A1', '=SUM(1, 2, 3)'); # Okay

5684

</PRE>

5685

5686

5687

The following table lists all of the core functions supported by Excel 5

5688

and Spreadsheet::WriteExcel. Any additional functions that are available

5689

through the "Analysis ToolPak" or other add-ins are not supported. These

5690

functions have all been tested to verify that they work.

5691

5692

5693

5694

<PRE>

5695

ABS DB INDIRECT NORMINV SLN

5696

ACOS DCOUNT INFO NORMSDIST SLOPE

5697

ACOSH DCOUNTA INT NORMSINV SMALL

5698

ADDRESS DDB INTERCEPT NOT SQRT

5699

AND DEGREES IPMT NOW STANDARDIZE

5700

AREAS DEVSQ IRR NPER STDEV

5701

ASIN DGET ISBLANK NPV STDEVP

5702

ASINH DMAX ISERR ODD STEYX

5703

ATAN DMIN ISERROR OFFSET SUBSTITUTE

5704

ATAN2 DOLLAR ISLOGICAL OR SUBTOTAL

5705

ATANH DPRODUCT ISNA PEARSON SUM

5706

AVEDEV DSTDEV ISNONTEXT PERCENTILE SUMIF

5707

AVERAGE DSTDEVP ISNUMBER PERCENTRANK SUMPRODUCT

5708

BETADIST DSUM ISREF PERMUT SUMSQ

5709

BETAINV DVAR ISTEXT PI SUMX2MY2

5710

BINOMDIST DVARP KURT PMT SUMX2PY2

5711

CALL ERROR.TYPE LARGE POISSON SUMXMY2

5712

CEILING EVEN LEFT POWER SYD

5713

CELL EXACT LEN PPMT T

5714

CHAR EXP LINEST PROB TAN

5715

CHIDIST EXPONDIST LN PRODUCT TANH

5716

CHIINV FACT LOG PROPER TDIST

5717

CHITEST FALSE LOG10 PV TEXT

5718

CHOOSE FDIST LOGEST QUARTILE TIME

5719

CLEAN FIND LOGINV RADIANS TIMEVALUE

5720

CODE FINV LOGNORMDIST RAND TINV

5721

COLUMN FISHER LOOKUP RANK TODAY

5722

COLUMNS FISHERINV LOWER RATE TRANSPOSE

5723

COMBIN FIXED MATCH REGISTER.ID TREND

5724

CONCATENATE FLOOR MAX REPLACE TRIM

5725

CONFIDENCE FORECAST MDETERM REPT TRIMMEAN

5726

CORREL FREQUENCY MEDIAN RIGHT TRUE

5727

COS FTEST MID ROMAN TRUNC

5728

COSH FV MIN ROUND TTEST

5729

COUNT GAMMADIST MINUTE ROUNDDOWN TYPE

5730

COUNTA GAMMAINV MINVERSE ROUNDUP UPPER

5731

COUNTBLANK GAMMALN MIRR ROW VALUE

5732

COUNTIF GEOMEAN MMULT ROWS VAR

5733

COVAR GROWTH MOD RSQ VARP

5734

CRITBINOM HARMEAN MODE SEARCH VDB

5735

DATE HLOOKUP MONTH SECOND VLOOKUP

5736

DATEVALUE HOUR N SIGN WEEKDAY

5737

DAVERAGE HYPGEOMDIST NA SIN WEIBULL

5738

DAY IF NEGBINOMDIST SINH YEAR

5739

DAYS360 INDEX NORMDIST SKEW ZTEST

5740

</PRE>

5741

5742

5743

You can also modify the module to support function names in the following

5744

languages: German, French, Spanish, Portuguese, Dutch, Finnish, Italian and

5745

Swedish. See the <CODE>function_locale.pl</CODE> program in the <CODE>examples</CODE> directory of the distro.

5746

5747

5748

5749

For a general introduction to Excel's formulas and an explanation of the

5750

syntax of the function refer to the Excel help files or the following

5751

links: <A

5752

HREF="http://msdn.microsoft.com/library/default.asp?URL=/library/officedev/office97/s88f2.htm">http://msdn.microsoft.com/library/default.asp?URL=/library/officedev/office97/s88f2.htm</A>

5753

and <A

5754

HREF="http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/office97/s992f.htm">http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/office97/s992f.htm</A>

5755

5756

5757

5758

5759

If your formula doesn't work in Spreadsheet::WriteExcel try the following:

5760

5761

5762

5763

<PRE>

5764

1. Verify that the formula works in Excel (or Gnumeric or OpenOffice.org).

5765

2. Ensure that it isn't on the Caveats list shown above.

5766

3. Ensure that cell references and formula names are in uppercase.

5767

4. Ensure that you are using ':' as the range operator, A1:A4.

5768

5. Ensure that you are using ',' as the union operator, SUM(1,2,3).

5769

6. Ensure that the function is in the above table.

5770

</PRE>

5771

5772

5773

If you go through steps 1-6 and you still have a problem, mail me.

5774

5775

5776

5777

<HR>

5778

<H2><A NAME="Improving_performance_when_worki">Improving performance when working with formulas</A></H2>

5779

5780

Writing a large number of formulas with Spreadsheet::WriteExcel can be

5781

slow. This is due to the fact that each formula has to be parsed and with

5782

the current implementation this is computationally expensive.

5783

5784

5785

5786

However, in a lot of cases the formulas that you write will be quite

5787

similar, for example:

5788

5789

5790

5791

<PRE>

5792

$worksheet->write_formula('B1', '=A1 * 3 + 50', $format);

5793

$worksheet->write_formula('B2', '=A2 * 3 + 50', $format);

5794

...

5795

...

5796

$worksheet->write_formula('B99', '=A999 * 3 + 50', $format);

5797

$worksheet->write_formula('B1000', '=A1000 * 3 + 50', $format);

5798

</PRE>

5799

5800

5801

In this example the cell reference changes in iterations from <CODE>A1</CODE> to <CODE>A1000</CODE>. The parser treats this variable as a token and arranges it according to predefined rules. However, since the parser is

5802

oblivious to the value of the token, it is essentially performing the same

5803

calculation 1000 times. This is inefficient.

5804

5805

5806

5807

The way to avoid this inefficiency and thereby speed up the writing of

5808

formulas is to parse the formula once and then repeatedly substitute

5809

similar tokens.

5810

5811

5812

5813

A formula can be parsed and stored via the <CODE>store_formula()</CODE> worksheet method. You can then use the <CODE>repeat_formula()</CODE> method to substitute <CODE>$pattern</CODE>, <CODE>$replace</CODE> pairs in the stored formula:

5814

5815

5816

5817

<PRE>

5818

my $formula = $worksheet->store_formula('=A1 * 3 + 50');

5819

</PRE>

5820

5821

5822

<PRE>

5823

for my $row (0..999) {

5824

$worksheet->repeat_formula($row, 1, $formula, $format, 'A1', 'A'.($row +1));

5825

}

5826

</PRE>

5827

5828

5829

On an arbitrary test machine this method was 10 times faster than the brute

5830

force method shown above.

5831

5832

5833

5834

For more information about how Spreadsheet::WriteExcel parses and stores

5835

formulas see the <CODE>Spreadsheet::WriteExcel::Formula</CODE> man page.

5836

5837

5838

5839

It should be noted however that the overall speed of direct formula parsing

5840

will be improved in a future version.

5841

5842

5843

5844

<HR>

5845

<H1><A NAME="EXAMPLES">EXAMPLES</A></H1>

5846

5847

<HR>

5848

<H2><A NAME="Example_1">Example 1</A></H2>

5849

5850

The following example shows some of the basic features of

5851

Spreadsheet::WriteExcel.

5852

5853

5854

5855

<PRE>

5856

#!/usr/bin/perl -w

5857

</PRE>

5858

5859

5860

<PRE>

5861

use strict;

5862

use Spreadsheet::WriteExcel;

5863

</PRE>

5864

5865

5866

<PRE>

5867

# Create a new workbook called simple.xls and add a worksheet

5868

my $workbook = Spreadsheet::WriteExcel->new("simple.xls");

5869

my $worksheet = $workbook->add_worksheet();

5870

</PRE>

5871

5872

5873

<PRE>

5874

# The general syntax is write($row, $column, $token). Note that row and

5875

# column are zero indexed

5876

</PRE>

5877

5878

5879

<PRE>

5880

# Write some text

5881

$worksheet->write(0, 0, "Hi Excel!");

5882

</PRE>

5883

5884

5885

<PRE>

5886

# Write some numbers

5887

$worksheet->write(2, 0, 3); # Writes 3

5888

$worksheet->write(3, 0, 3.00000); # Writes 3

5889

$worksheet->write(4, 0, 3.00001); # Writes 3.00001

5890

$worksheet->write(5, 0, 3.14159); # TeX revision no.?

5891

</PRE>

5892

5893

5894

<PRE>

5895

# Write some formulas

5896

$worksheet->write(7, 0, '=A3 + A6');

5897

$worksheet->write(8, 0, '=IF(A5>3,"Yes", "No")');

5898

</PRE>

5899

5900

5901

<PRE>

5902

# Write a hyperlink

5903

$worksheet->write(10, 0, '<A HREF="http://www.perl.com/">http://www.perl.com/</A>');

5904

</PRE>

5905

5906

5907

5908

5909

5910

<HR>

5911

<H2><A NAME="Example_2">Example 2</A></H2>

5912

5913

The following is a general example which demonstrates some features of

5914

working with multiple worksheets.

5915

5916

5917

5918

<PRE>

5919

#!/usr/bin/perl -w

5920

</PRE>

5921

5922

5923

<PRE>

5924

use strict;

5925

use Spreadsheet::WriteExcel;

5926

</PRE>

5927

5928

5929

<PRE>

5930

# Create a new Excel workbook

5931

my $workbook = Spreadsheet::WriteExcel->new("regions.xls");

5932

</PRE>

5933

5934

5935

<PRE>

5936

# Add some worksheets

5937

my $north = $workbook->add_worksheet("North");

5938

my $south = $workbook->add_worksheet("South");

5939

my $east = $workbook->add_worksheet("East");

5940

my $west = $workbook->add_worksheet("West");

5941

</PRE>

5942

5943

5944

<PRE>

5945

# Add a Format

5946

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

5947

$format->set_bold();

5948

$format->set_color('blue');

5949

</PRE>

5950

5951

5952

<PRE>

5953

# Add a caption to each worksheet

5954

foreach my $worksheet ($workbook->sheets()) {

5955

$worksheet->write(0, 0, "Sales", $format);

5956

}

5957

</PRE>

5958

5959

5960

<PRE>

5961

# Write some data

5962

$north->write(0, 1, 200000);

5963

$south->write(0, 1, 100000);

5964

$east->write (0, 1, 150000);

5965

$west->write (0, 1, 100000);

5966

</PRE>

5967

5968

5969

<PRE>

5970

# Set the active worksheet

5971

$south->activate();

5972

</PRE>

5973

5974

5975

<PRE>

5976

# Set the width of the first column

5977

$south->set_column(0, 0, 20);

5978

</PRE>

5979

5980

5981

<PRE>

5982

# Set the active cell

5983

$south->set_selection(0, 1);

5984

</PRE>

5985

5986

5987

5988

5989

5990

<HR>

5991

<H2><A NAME="Example_3">Example 3</A></H2>

5992

5993

This example shows how to use a conditional numerical format with colours

5994

to indicate if a share price has gone up or down.

5995

5996

5997

5998

<PRE>

5999

use strict;

6000

use Spreadsheet::WriteExcel;

6001

</PRE>

6002

6003

6004

<PRE>

6005

# Create a new workbook and add a worksheet

6006

my $workbook = Spreadsheet::WriteExcel->new("stocks.xls");

6007

my $worksheet = $workbook->add_worksheet();

6008

</PRE>

6009

6010

6011

<PRE>

6012

# Set the column width for columns 1, 2, 3 and 4

6013

$worksheet->set_column(0, 3, 15);

6014

</PRE>

6015

6016

6017

<PRE>

6018

# Create a format for the column headings

6019

my $header = $workbook->add_format();

6020

$header->set_bold();

6021

$header->set_size(12);

6022

$header->set_color('blue');

6023

</PRE>

6024

6025

6026

<PRE>

6027

# Create a format for the stock price

6028

my $f_price = $workbook->add_format();

6029

$f_price->set_align('left');

6030

$f_price->set_num_format('$0.00');

6031

</PRE>

6032

6033

6034

<PRE>

6035

# Create a format for the stock volume

6036

my $f_volume = $workbook->add_format();

6037

$f_volume->set_align('left');

6038

$f_volume->set_num_format('#,##0');

6039

</PRE>

6040

6041

6042

<PRE>

6043

# Create a format for the price change. This is an example of a

6044

# conditional format. The number is formatted as a percentage. If it is

6045

# positive it is formatted in green, if it is negative it is formatted

6046

# in red and if it is zero it is formatted as the default font colour

6047

# (in this case black). Note: the [Green] format produces an unappealing

6048

# lime green. Try [Color 10] instead for a dark green.

6049

6050

my $f_change = $workbook->add_format();

6051

$f_change->set_align('left');

6052

$f_change->set_num_format('[Green]0.0%;[Red]-0.0%;0.0%');

6053

</PRE>

6054

6055

6056

<PRE>

6057

# Write out the data

6058

$worksheet->write(0, 0, 'Company',$header);

6059

$worksheet->write(0, 1, 'Price', $header);

6060

$worksheet->write(0, 2, 'Volume', $header);

6061

$worksheet->write(0, 3, 'Change', $header);

6062

</PRE>

6063

6064

6065

<PRE>

6066

$worksheet->write(1, 0, 'Damage Inc.' );

6067

$worksheet->write(1, 1, 30.25, $f_price ); # $30.25

6068

$worksheet->write(1, 2, 1234567, $f_volume); # 1,234,567

6069

$worksheet->write(1, 3, 0.085, $f_change); # 8.5% in green

6070

</PRE>

6071

6072

6073

<PRE>

6074

$worksheet->write(2, 0, 'Dump Corp.' );

6075

$worksheet->write(2, 1, 1.56, $f_price ); # $1.56

6076

$worksheet->write(2, 2, 7564, $f_volume); # 7,564

6077

$worksheet->write(2, 3, -0.015, $f_change); # -1.5% in red

6078

</PRE>

6079

6080

6081

<PRE>

6082

$worksheet->write(3, 0, 'Rev Ltd.' );

6083

$worksheet->write(3, 1, 0.13, $f_price ); # $0.13

6084

$worksheet->write(3, 2, 321, $f_volume); # 321

6085

$worksheet->write(3, 3, 0, $f_change); # 0 in the font color (black)

6086

</PRE>

6087

6088

6089

6090

6091

6092

<HR>

6093

<H2><A NAME="Example_4">Example 4</A></H2>

6094

6095

The following is a simple example of using functions.

6096

6097

6098

6099

<PRE>

6100

#!/usr/bin/perl -w

6101

</PRE>

6102

6103

6104

<PRE>

6105

use strict;

6106

use Spreadsheet::WriteExcel;

6107

</PRE>

6108

6109

6110

<PRE>

6111

# Create a new workbook and add a worksheet

6112

my $workbook = Spreadsheet::WriteExcel->new("stats.xls");

6113

my $worksheet = $workbook->add_worksheet('Test data');

6114

</PRE>

6115

6116

6117

<PRE>

6118

# Set the column width for columns 1

6119

$worksheet->set_column(0, 0, 20);

6120

</PRE>

6121

6122

6123

<PRE>

6124

# Create a format for the headings

6125

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

6126

$format->set_bold();

6127

</PRE>

6128

6129

6130

<PRE>

6131

# Write the sample data

6132

$worksheet->write(0, 0, 'Sample', $format);

6133

$worksheet->write(0, 1, 1);

6134

$worksheet->write(0, 2, 2);

6135

$worksheet->write(0, 3, 3);

6136

$worksheet->write(0, 4, 4);

6137

$worksheet->write(0, 5, 5);

6138

$worksheet->write(0, 6, 6);

6139

$worksheet->write(0, 7, 7);

6140

$worksheet->write(0, 8, 8);

6141

</PRE>

6142

6143

6144

<PRE>

6145

$worksheet->write(1, 0, 'Length', $format);

6146

$worksheet->write(1, 1, 25.4);

6147

$worksheet->write(1, 2, 25.4);

6148

$worksheet->write(1, 3, 24.8);

6149

$worksheet->write(1, 4, 25.0);

6150

$worksheet->write(1, 5, 25.3);

6151

$worksheet->write(1, 6, 24.9);

6152

$worksheet->write(1, 7, 25.2);

6153

$worksheet->write(1, 8, 24.8);

6154

</PRE>

6155

6156

6157

<PRE>

6158

# Write some statistical functions

6159

$worksheet->write(4, 0, 'Count', $format);

6160

$worksheet->write(4, 1, '=COUNT(B1:I1)');

6161

</PRE>

6162

6163

6164

<PRE>

6165

$worksheet->write(5, 0, 'Sum', $format);

6166

$worksheet->write(5, 1, '=SUM(B2:I2)');

6167

</PRE>

6168

6169

6170

<PRE>

6171

$worksheet->write(6, 0, 'Average', $format);

6172

$worksheet->write(6, 1, '=AVERAGE(B2:I2)');

6173

</PRE>

6174

6175

6176

<PRE>

6177

$worksheet->write(7, 0, 'Min', $format);

6178

$worksheet->write(7, 1, '=MIN(B2:I2)');

6179

</PRE>

6180

6181

6182

<PRE>

6183

$worksheet->write(8, 0, 'Max', $format);

6184

$worksheet->write(8, 1, '=MAX(B2:I2)');

6185

</PRE>

6186

6187

6188

<PRE>

6189

$worksheet->write(9, 0, 'Standard Deviation', $format);

6190

$worksheet->write(9, 1, '=STDEV(B2:I2)');

6191

</PRE>

6192

6193

6194

<PRE>

6195

$worksheet->write(10, 0, 'Kurtosis', $format);

6196

$worksheet->write(10, 1, '=KURT(B2:I2)');

6197

</PRE>

6198

6199

6200

6201

6202

6203

<HR>

6204

<H2><A NAME="Example_5">Example 5</A></H2>

6205

6206

The following example converts a tab separated file called <CODE>tab.txt</CODE> into an Excel file called <CODE>tab.xls</CODE>.

6207

6208

6209

6210

<PRE>

6211

#!/usr/bin/perl -w

6212

</PRE>

6213

6214

6215

<PRE>

6216

use strict;

6217

use Spreadsheet::WriteExcel;

6218

</PRE>

6219

6220

6221

<PRE>

6222

open (TABFILE, "tab.txt") or die "tab.txt: $!";

6223

</PRE>

6224

6225

6226

<PRE>

6227

my $workbook = Spreadsheet::WriteExcel->new("tab.xls");

6228

my $worksheet = $workbook->add_worksheet();

6229

</PRE>

6230

6231

6232

<PRE>

6233

# Row and column are zero indexed

6234

my $row = 0;

6235

</PRE>

6236

6237

6238

<PRE>

6239

while (<TABFILE>) {

6240

chomp;

6241

# Split on single tab

6242

my @Fld = split('\t', $_);

6243

</PRE>

6244

6245

6246

<PRE>

6247

my $col = 0;

6248

foreach my $token (@Fld) {

6249

$worksheet->write($row, $col, $token);

6250

$col++;

6251

}

6252

$row++;

6253

}

6254

</PRE>

6255

6256

6257

<HR>

6258

<H2><A NAME="Additional_Examples">Additional Examples</A></H2>

6259

6260

If you performed a normal installation the following examples files should

6261

have been copied to your <CODE>~site/Spreadsheet/WriteExcel/examples</CODE> directory:

6262

6263

6264

6265

The following is a description of the example files that are provided with

6266

Spreadsheet::WriteExcel. They are intended to demonstrate the different

6267

features and options of the module.

6268

6269

6270

6271

<PRE>

6272

Getting started

6273

===============

6274

bug_report.pl A template for submitting bug reports.

6275

demo.pl Creates a demo of some of the features.

6276

formats.pl Creates a demo of the available formatting.

6277

regions.pl Demonstrates multiple worksheets.

6278

simple.pl An example of some of the basic features.

6279

stats.pl Basic formulas and functions.

6280

</PRE>

6281

6282

6283

<PRE>

6284

Advanced

6285

========

6286

bigfile.pl Write past the 7MB limit with OLE::Storage_Lite.

6287

cgi.pl A simple CGI program.

6288

chess.pl An example of formatting using properties.

6289

colors.pl Demo of the colour palette and named colours.

6290

copyformat.pl Example of copying a cell format.

6291

diag_border.pl A simple example of diagonal cell borders.

6292

easter_egg.pl Expose the Excel97 flight simulator. A must see.

6293

filehandle.pl Examples of working with filehandles.

6294

formula_result.pl Formulas with user specified results.

6295

headers.pl Examples of worksheet headers and footers.

6296

hyperlink1.pl Shows how to create web hyperlinks.

6297

hyperlink2.pl Examples of internal and external hyperlinks.

6298

images.pl Adding bitmap images to worksheets.

6299

indent.pl An example of cell indentation.

6300

merge1.pl A simple example of cell merging.

6301

merge2.pl A simple example of cell merging with formatting.

6302

merge3.pl Add hyperlinks to merged cells.

6303

merge4.pl An advanced example of merging with formatting.

6304

merge5.pl An advanced example of merging with formatting.

6305

mod_perl1.pl A simple mod_perl 1 program.

6306

mod_perl2.pl A simple mod_perl 2 program.

6307

outline.pl An example of outlines and grouping.

6308

panes.pl An examples of how to create panes.

6309

protection.pl Example of cell locking and formula hiding.

6310

hide_sheet.pl Simple example of hiding a worksheet.

6311

repeat.pl Example of writing repeated formulas.

6312

sales.pl An example of a simple sales spreadsheet.

6313

sendmail.pl Send an Excel email attachment using Mail::Sender.

6314

stats_ext.pl Same as stats.pl with external references.

6315

stocks.pl Demonstrates conditional formatting.

6316

textwrap.pl Demonstrates text wrapping options.

6317

win32ole.pl A sample Win32::OLE example for comparison.

6318

write_arrays.pl Example of writing 1D or 2D arrays of data.

6319

write_to_scalar.pl Example of writing an Excel file to a Perl scalar.

6320

write_handler1.pl Example of extending the write() method. Step 1.

6321

write_handler2.pl Example of extending the write() method. Step 2.

6322

write_handler3.pl Example of extending the write() method. Step 3.

6323

write_handler4.pl Example of extending the write() method. Step 4.

6324

</PRE>

6325

6326

6327

<PRE>

6328

Unicode

6329

=======

6330

unicode.pl Simple example of using Unicode UTF16 strings.

6331

unicode_japan.pl Write Japanese Unicode strings using UTF16.

6332

unicode_cyrillic.pl Write Russian cyrillic strings using UTF8.

6333

unicode_list.pl List the chars in a Unicode font.

6334

unicode_2022_jp.pl Japanese: ISO-2022-JP to utf8 in perl 5.8.

6335

unicode_8859_11.pl Thai: ISO-8859_11 to utf8 in perl 5.8.

6336

unicode_8859_7.pl Greek: ISO-8859_7 to utf8 in perl 5.8.

6337

unicode_big5.pl Chinese: BIG5 to utf8 in perl 5.8.

6338

unicode_cp1251.pl Russian: CP1251 to utf8 in perl 5.8.

6339

unicode_cp1256.pl Arabic: CP1256 to utf8 in perl 5.8.

6340

unicode_koi8r.pl Russian: KOI8-R to utf8 in perl 5.8.

6341

unicode_polish_utf8.pl Polish : UTF8 to utf8 in perl 5.8.

6342

unicode_shift_jis.pl Japanese: Shift JIS to utf8 in perl 5.8.

6343

</PRE>

6344

6345

6346

<PRE>

6347

Utility

6348

=======

6349

csv2xls.pl Program to convert a CSV file to an Excel file.

6350

datecalc1.pl Convert Unix/Perl time to Excel time.

6351

datecalc2.pl Calculate an Excel date using Date::Calc.

6352

lecxe.pl Convert Excel to WriteExcel using Win32::OLE.

6353

tab2xls.pl Program to convert a tab separated file to xls.

6354

</PRE>

6355

6356

6357

<PRE>

6358

Developer

6359

=========

6360

convertA1.pl Helper functions for dealing with A1 notation.

6361

function_locale.pl Add non-English function names to Formula.pm.

6362

writeA1.pl Example of how to extend the module.

6363

</PRE>

6364

6365

6366

<HR>

6367

<H1><A NAME="LIMITATIONS">LIMITATIONS</A></H1>

6368

6369

The following limits are imposed by Excel:

6370

6371

6372

6373

<PRE>

6374

Description Limit

6375

----------------------------------- ------

6376

Maximum number of chars in a string 32767

6377

Maximum number of columns 256

6378

Maximum number of rows 65536

6379

Maximum chars in a sheet name 31

6380

Maximum chars in a header/footer 254

6381

</PRE>

6382

6383

6384

The minimum file size is 6K due to the OLE overhead. The maximum file size

6385

is approximately 7MB (7087104 bytes) of BIFF data. This can be extended by

6386

using Takanori Kawai's OLE::Storage_Lite module <A

6387

HREF="http://search.cpan.org/search?dist=OLE-Storage_Lite">http://search.cpan.org/search?dist=OLE-Storage_Lite</A>

6388

see the <CODE>bigfile.pl</CODE> example in the <CODE>examples</CODE> directory of the distro.

6389

6390

6391

6392

<HR>

6393

<H1><A NAME="DOWNLOADING">DOWNLOADING</A></H1>

6394

6395

The latest version of this module is always available at: <A

6396

HREF="http://search.cpan.org/search?dist=Spreadsheet-WriteExcel/">http://search.cpan.org/search?dist=Spreadsheet-WriteExcel/</A>

6397

6398

6399

6400

6401

<HR>

6402

<H1><A NAME="REQUIREMENTS">REQUIREMENTS</A></H1>

6403

6404

This module requires Perl 5.005 (or later), Parse::RecDescent and

6405

File::Temp:

6406

6407

6408

6409

<PRE>

6410

<A HREF="http://search.cpan.org/search?dist=Parse-RecDescent/">http://search.cpan.org/search?dist=Parse-RecDescent/</A>

6411

<A HREF="http://search.cpan.org/search?dist=File-Temp/">http://search.cpan.org/search?dist=File-Temp/</A>

6412

</PRE>

6413

6414

6415

<HR>

6416

<H1><A NAME="INSTALLATION">INSTALLATION</A></H1>

6417

6418

See the INSTALL or install.html docs that come with the distribution or:

6419

6420

6421

6422

6423

HREF="http://search.cpan.org/doc/JMCNAMARA/Spreadsheet-WriteExcel-2.11/WriteExcel/doc/install.html">http://search.cpan.org/doc/JMCNAMARA/Spreadsheet-WriteExcel-2.11/WriteExcel/doc/install.html</A>

6424

6425

6426

6427

6428

<HR>

6429

<H1><A NAME="PORTABILITY">PORTABILITY</A></H1>

6430

6431

Spreadsheet::WriteExcel will work on the majority of Windows, UNIX and

6432

Macintosh platforms. Specifically, the module will work on any system where

6433

perl packs floats in the 64 bit IEEE format. The float must also be in

6434

little-endian format but it will be reversed if necessary. Thus:

6435

6436

6437

6438

<PRE>

6439

print join(" ", map { sprintf "%#02x", $_ } unpack("C*", pack "d", 1.2345)), "\n";

6440

</PRE>

6441

6442

6443

should give (or in reverse order):

6444

6445

6446

6447

<PRE>

6448

0x8d 0x97 0x6e 0x12 0x83 0xc0 0xf3 0x3f

6449

</PRE>

6450

6451

6452

In general, if you don't know whether your system supports a 64 bit IEEE

6453

float or not, it probably does. If your system doesn't, WriteExcel will <CODE>croak()</CODE> with the message given in the <A HREF="#DIAGNOSTICS">DIAGNOSTICS</A> section. You can check which platforms the module has been tested on at the

6454

CPAN testers site: <A

6455

HREF="http://testers.cpan.org/search?request=dist&dist=Spreadsheet-WriteExcel">http://testers.cpan.org/search?request=dist&dist=Spreadsheet-WriteExcel</A>

6456

6457

6458

6459

6460

<HR>

6461

<H1><A NAME="DIAGNOSTICS">DIAGNOSTICS</A></H1>

6462

<DL>

6463

<DT><A NAME="item_Filename">Filename required by Spreadsheet::WriteExcel->new()</A><DD>

6464

6465

A filename must be given in the constructor.

6466

6467

6468

<DT><A NAME="item_Can">Can't open filename. It may be in use or protected.</A><DD>

6469

6470

The file cannot be opened for writing. The directory that you are writing

6471

to may be protected or the file may be in use by another program.

6472

6473

6474

<DT><A NAME="item_Unable">Unable to create tmp files via File::Temp::tempfile()...</A><DD>

6475

6476

This is a <CODE>-w</CODE> warning. You will see it if you are using Spreadsheet::WriteExcel in an

6477

environment where temporary files cannot be created, in which case all data

6478

will be stored in memory. The warning is for information only: it does not

6479

affect creation but it will affect the speed of execution for large files.

6480

See the <CODE>set_tempdir</CODE> workbook method.

6481

6482

6483

<DT><A NAME="item_Maximum">Maximum file size, 7087104, exceeded.</A><DD>

6484

6485

The current OLE implementation only supports a maximum BIFF file of this

6486

size. This limit can be extended, see the <A HREF="#LIMITATIONS">LIMITATIONS</A> section.

6487

6488

6489

<DT>Can't locate Parse/RecDescent.pm in @INC ...<DD>

6490

6491

Spreadsheet::WriteExcel requires the Parse::RecDescent module. Download it

6492

from CPAN: <A

6493

HREF="http://search.cpan.org/search?dist=Parse-RecDescent">http://search.cpan.org/search?dist=Parse-RecDescent</A>

6494

6495

6496

6497

<DT><A NAME="item_Couldn">Couldn't parse formula ...</A><DD>

6498

6499

There are a large number of warnings which relate to badly formed formulas

6500

and functions. See the <A HREF="#FORMULAS_AND_FUNCTIONS_IN_EXCEL">FORMULAS AND FUNCTIONS IN EXCEL</A> section for suggestions on how to avoid these errors. You should also check

6501

the formula in Excel to ensure that it is valid.

6502

6503

6504

<DT><A NAME="item_Required">Required floating point format not supported on this platform.</A><DD>

6505

6506

Operating system doesn't support 64 bit IEEE float or it is byte-ordered in

6507

a way unknown to WriteExcel.

6508

6509

6510

<DT><A NAME="item__file_xls_">'file.xls' cannot be accessed. The file may be read-only ...</A><DD>

6511

6512

You may sometimes encounter the following error when trying to open a file

6513

in Excel: "file.xls cannot be accessed. The file may be read-only, or you

6514

may be trying to access a read-only location. Or, the server the document

6515

is stored on may not be responding."

6516

6517

6518

6519

This error generally means that the Excel file has been corrupted. There

6520

are two likely causes of this: the file was FTPed in ASCII mode instead of

6521

binary mode or else the file was created with UTF8 data returned by an XML

6522

parser. See <A HREF="#WORKING_WITH_XML">WORKING WITH XML</A> for further details.

6523

6524

6525

</DL>

6526

6527

<HR>

6528

<H1><A NAME="THE_EXCEL_BINARY_FORMAT">THE EXCEL BINARY FORMAT</A></H1>

6529

6530

The following is some general information about the Excel binary format for

6531

anyone who may be interested.

6532

6533

6534

6535

Excel data is stored in the "Binary Interchange File Format" (BIFF) file

6536

format. Details of this format are given in the Excel SDK, the "Excel

6537

Developer's Kit" from Microsoft Press. It is also included in the MSDN CD

6538

library but is no longer available on the MSDN website. Versions of the

6539

BIFF documentation are available at www.wotsit.org, <A

6540

HREF="http://www.wotsit.org/search.asp?page=2&s=database">http://www.wotsit.org/search.asp?page=2&s=database</A>

6541

6542

6543

6544

6545

Charles Wybble has collected together almost all of the available

6546

information about the Excel file format. See "The Chicago Project" at <A

6547

HREF="http://chicago.sourceforge.net/devel/">http://chicago.sourceforge.net/devel/</A>

6548

6549

6550

6551

6552

Daniel Rentz of OpenOffice.org has also written a detailed description of

6553

the Excel workbook records, see <A

6554

HREF="http://sc.openoffice.org/excelfileformat.pdf">http://sc.openoffice.org/excelfileformat.pdf</A>

6555

6556

6557

6558

6559

The BIFF portion of the Excel file is comprised of contiguous binary

6560

records that have different functions and that hold different types of

6561

data. Each BIFF record is comprised of the following three parts:

6562

6563

6564

6565

<PRE>

6566

Record name; Hex identifier, length = 2 bytes

6567

Record length; Length of following data, length = 2 bytes

6568

Record data; Data, length = variable

6569

</PRE>

6570

6571

6572

The BIFF data is stored along with other data in an OLE Compound File. This

6573

is a structured storage which acts like a file system within a file. A

6574

Compound File is comprised of storages and streams which, to follow the

6575

file system analogy, are like directories and files.

6576

6577

6578

6579

The documentation for the OLE::Storage module, <A

6580

HREF="http://user.cs.tu-berlin.de/~schwartz/pmh/guide.html">http://user.cs.tu-berlin.de/~schwartz/pmh/guide.html</A>

6581

, contains one of the few descriptions of the OLE Compound File in the

6582

public domain. The Digital Imaging Group have also detailed the OLE format

6583

in the JPEG2000 specification: see Appendix A of <A

6584

HREF="http://www.i3a.org/pdf/wg1n1017.pdf">http://www.i3a.org/pdf/wg1n1017.pdf</A>

6585

6586

6587

6588

6589

For a open source implementation of the OLE library see the 'cole' library

6590

at <A HREF="http://atena.com/libole2.php">http://atena.com/libole2.php</A>

6591

6592

6593

6594

The source code for the Excel plugin of the Gnumeric spreadsheet also

6595

contains information relevant to the Excel BIFF format and the OLE

6596

container, <A

6597

HREF="http://www.gnome.org/projects/gnumeric/">http://www.gnome.org/projects/gnumeric/</A>

6598

and <A

6599

HREF="ftp://ftp.ximian.com/pub/ximian-source/">ftp://ftp.ximian.com/pub/ximian-source/</A>

6600

6601

6602

6603

6604

In addition the source code for OpenOffice.org is available at <A

6605

HREF="http://www.openoffice.org/">http://www.openoffice.org/</A>

6606

6607

6608

6609

An article describing Spreadsheet::WriteExcel and how it works appears in

6610

Issue #19 of The Perl Journal, <A

6611

HREF="http://www.samag.com/documents/s=1272/sam05030004/">http://www.samag.com/documents/s=1272/sam05030004/</A>

6612

It is reproduced, by kind permission, in the <CODE>doc</CODE> directory of the distro.

6613

6614

6615

6616

Please note that the provision of this information does not constitute an

6617

invitation to start hacking at the BIFF or OLE file formats. There are more

6618

interesting ways to waste your time. ;-)

6619

6620

6621

6622

<HR>

6623

<H1><A NAME="WRITING_EXCEL_FILES">WRITING EXCEL FILES</A></H1>

6624

6625

Depending on your requirements, background and general sensibilities you

6626

may prefer one of the following methods of getting data into Excel:

6627

6628

6629

<UL>

6630

<LI><A NAME="item_Win32">Win32::OLE module and office automation</A>

6631

6632

This requires a Windows platform and an installed copy of Excel. This is

6633

the most powerful and complete method for interfacing with Excel. See <A

6634

HREF="http://www.activestate.com/ASPN/Reference/Products/ActivePerl-5.6/faq/Windows/ActivePerl-Winfaq12.html">http://www.activestate.com/ASPN/Reference/Products/ActivePerl-5.6/faq/Windows/ActivePerl-Winfaq12.html</A>

6635

and <A

6636

HREF="http://www.activestate.com/ASPN/Reference/Products/ActivePerl-5.6/site/lib/Win32/OLE.html">http://www.activestate.com/ASPN/Reference/Products/ActivePerl-5.6/site/lib/Win32/OLE.html</A>

6637

If your main platform is UNIX but you have the resources to set up a

6638

separate Win32/MSOffice server, you can convert office documents to text,

6639

postscript or PDF using Win32::OLE. For a demonstration of how to do this

6640

using Perl see Docserver: <A

6641

HREF="http://search.cpan.org/search?mode=module&query=docserver">http://search.cpan.org/search?mode=module&query=docserver</A>

6642

6643

6644

6645

<LI><A NAME="item_CSV">CSV, comma separated variables or text</A>

6646

6647

If the file extension is <CODE>csv</CODE>, Excel will open and convert this format automatically. Generating a valid

6648

CSV file isn't as easy as it seems. Have a look at the DBD::RAM, DBD::CSV,

6649

Text::xSV and Text::CSV_XS modules.

6650

6651

6652

6653

6654

Excel files contain an internal index table that allows them to act like a

6655

database file. Using one of the standard Perl database modules you can

6656

connect to an Excel file as a database.

6657

6658

6659

<LI><A NAME="item_DBD">DBD::Excel</A>

6660

6661

You can also access Spreadsheet::WriteExcel using the standard DBI

6662

interface via Takanori Kawai's DBD::Excel module <A

6663

HREF="http://search.cpan.org/dist/DBD-Excel">http://search.cpan.org/dist/DBD-Excel</A>

6664

6665

6666

6667

<LI><A NAME="item_Spreadsheet">Spreadsheet::WriteExcelXML</A>

6668

6669

This module allows you to create an Excel XML file using the same interface

6670

as Spreadsheet::WriteExcel. See: <A

6671

HREF="http://search.cpan.org/dist/Spreadsheet-WriteExcelXML">http://search.cpan.org/dist/Spreadsheet-WriteExcelXML</A>

6672

6673

6674

6675

<LI><A NAME="item_Excel">Excel::Template</A>

6676

6677

This module allows you to create an Excel file from an XML template in a

6678

manner similar to HTML::Template. See <A

6679

HREF="http://search.cpan.org/dist/Excel-Template/">http://search.cpan.org/dist/Excel-Template/</A>

6680

6681

6682

6683

<LI><A NAME="item_Spreadsheet">Spreadsheet::WriteExcel::FromXML</A>

6684

6685

This module allows you to turn a simple XML file into an Excel file using

6686

Spreadsheet::WriteExcel as a backend. The format of the XML file is defined

6687

by a supplied DTD: <A

6688

HREF="http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromXML">http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromXML</A>

6689

6690

6691

6692

<LI><A NAME="item_Spreadsheet">Spreadsheet::WriteExcel::Simple</A>

6693

6694

This provides an easier interface to Spreadsheet::WriteExcel: <A

6695

HREF="http://search.cpan.org/dist/Spreadsheet-WriteExcel-Simple">http://search.cpan.org/dist/Spreadsheet-WriteExcel-Simple</A>

6696

6697

6698

6699

<LI><A NAME="item_Spreadsheet">Spreadsheet::WriteExcel::FromDB</A>

6700

6701

This is a useful module for creating Excel files directly from a DB table:

6702

6703

HREF="http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB">http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB</A>

6704

6705

6706

6707

<LI><A NAME="item_HTML">HTML tables</A>

6708

6709

This is an easy way of adding formatting via a text based format.

6710

6711

6712

6713

6714

The Excel XML and HTML file specification are available from <A

6715

HREF="http://msdn.microsoft.com/library/officedev/ofxml2k/ofxml2k.htm">http://msdn.microsoft.com/library/officedev/ofxml2k/ofxml2k.htm</A>

6716

6717

6718

6719

</UL>

6720

6721

For other Perl-Excel modules try the following search: <A

6722

HREF="http://search.cpan.org/search?mode=module&query=excel">http://search.cpan.org/search?mode=module&query=excel</A>

6723

6724

6725

6726

6727

<HR>

6728

<H1><A NAME="READING_EXCEL_FILES">READING EXCEL FILES</A></H1>

6729

6730

To read data from Excel files try:

6731

6732

6733

<UL>

6734

<LI><A NAME="item_Spreadsheet">Spreadsheet::ParseExcel</A>

6735

6736

This uses the OLE::Storage-Lite module to extract data from an Excel file.

6737

6738

HREF="http://search.cpan.org/dist/Spreadsheet-ParseExcel">http://search.cpan.org/dist/Spreadsheet-ParseExcel</A>

6739

6740

6741

6742

<LI><A NAME="item_Spreadsheet">Spreadsheet::ParseExcel_XLHTML</A>

6743

6744

This module uses Spreadsheet::ParseExcel's interface but uses xlHtml (see

6745

below) to do the conversion: <A

6746

HREF="http://search.cpan.org/dist/Spreadsheet-ParseExcel_XLHTML">http://search.cpan.org/dist/Spreadsheet-ParseExcel_XLHTML</A>

6747

Spreadsheet::ParseExcel_XLHTML

6748

6749

6750

<LI><A NAME="item_xlHtml">xlHtml</A>

6751

6752

This is an open source "Excel to HTML Converter" C/C++ project at <A

6753

HREF="http://www.xlhtml.org/">http://www.xlhtml.org/</A> See also, the OLE

6754

Filters Project at <A

6755

HREF="http://atena.com/libole2.php">http://atena.com/libole2.php</A>

6756

6757

6758

<LI><A NAME="item_DBD">DBD::Excel (reading)</A>

6759

6760

You can also access Spreadsheet::ParseExcel using the standard DBI

6761

interface via Takanori Kawai's DBD::Excel module <A

6762

HREF="http://search.cpan.org/dist/DBD-Excel">http://search.cpan.org/dist/DBD-Excel</A>

6763

6764

6765

6766

<LI><A NAME="item_Win32">Win32::OLE module and office automation (reading)</A>

6767

6768

See, the section <A HREF="#WRITING_EXCEL_FILES">WRITING EXCEL FILES</A>.

6769

6770

6771

<LI><A NAME="item_HTML">HTML tables (reading)</A>

6772

6773

If the files are saved from Excel in a HTML format the data can be accessed

6774

using HTML::TableExtract <A

6775

HREF="http://search.cpan.org/dist/HTML-TableExtract">http://search.cpan.org/dist/HTML-TableExtract</A>

6776

6777

6778

6779

6780

6781

See, the section <A HREF="#WRITING_EXCEL_FILES">WRITING EXCEL FILES</A>.

6782

6783

6784

<LI><A NAME="item_XML">XML::Excel</A>

6785

6786

Converts Excel files to XML using Spreadsheet::ParseExcel <A

6787

HREF="http://search.cpan.org/dist/XML-Excel.">http://search.cpan.org/dist/XML-Excel.</A>

6788

6789

6790

6791

<LI><A NAME="item_OLE">OLE::Storage, aka LAOLA</A>

6792

6793

This is a Perl interface to OLE file formats. In particular, the distro

6794

contains an Excel to HTML converter called Herbert, <A

6795

HREF="http://user.cs.tu-berlin.de/~schwartz/pmh/">http://user.cs.tu-berlin.de/~schwartz/pmh/</A>

6796

This has been superseded by the Spreadsheet::ParseExcel module.

6797

6798

6799

</UL>

6800

6801

For other Perl-Excel modules try the following search: <A

6802

HREF="http://search.cpan.org/search?mode=module&query=excel">http://search.cpan.org/search?mode=module&query=excel</A>

6803

6804

6805

6806

6807

If you wish to view Excel files on a UNIX/Linux platform check out the

6808

excellent Gnumeric spreadsheet application at <A

6809

HREF="http://www.gnome.org/projects/gnumeric/">http://www.gnome.org/projects/gnumeric/</A>

6810

or OpenOffice.org at <A

6811

HREF="http://www.openoffice.org/">http://www.openoffice.org/</A>

6812

6813

6814

6815

If you wish to view Excel files on a Windows platform which doesn't have

6816

Excel installed you can use the free Microsoft Excel Viewer <A

6817

HREF="http://office.microsoft.com/downloads/2000/xlviewer.aspx">http://office.microsoft.com/downloads/2000/xlviewer.aspx</A>

6818

6819

6820

6821

6822

<HR>

6823

<H1><A NAME="Warning_about_XML_Parser_and_Pe">Warning about XML::Parser and Perl 5.6</A></H1>

6824

6825

You must be careful when using Spreadsheet::WriteExcel in conjunction with

6826

Perl 5.6 and XML::Parser (and other XML parsers) due to the fact that the

6827

data returned by the parser is generally in UTF8 format.

6828

6829

6830

6831

When UTF8 strings are added to Spreadsheet::WriteExcel's internal data it

6832

causes the generated Excel file to become corrupt.

6833

6834

6835

6836

Note, this doesn't affect Perl 5.005 (which doesn't try to handle UTF8) or

6837

5.8 (which handles it correctly).

6838

6839

6840

6841

To avoid this problem you should upgrade to Perl 5.8, if possible, or else

6842

you should convert the output data from XML::Parser to ASCII or ISO-8859-1

6843

using one of the following methods:

6844

6845

6846

6847

<PRE>

6848

$new_str = pack 'C*', unpack 'U*', $utf8_str;

6849

</PRE>

6850

6851

6852

<PRE>

6853

use Unicode::MapUTF8 'from_utf8';

6854

$new_str = from_utf8({-str => $utf8_str, -charset => 'ISO-8859-1'});

6855

</PRE>

6856

6857

6858

<HR>

6859

6860

6861

Formulas are formulae.

6862

6863

6864

6865

This version of the module doesn't support the <CODE>write_comment()</CODE>

6866

method. This will be fixed soon.

6867

6868

6869

6870

XML and UTF8 data on Perl 5.6 can cause Excel files created by

6871

Spreadsheet::WriteExcel to become corrupt. See <A HREF="#Warning_about_XML_Parser_and_Pe">Warning about XML::Parser and Perl 5.6</A> for further details.

6872

6873

6874

6875

The format object that is used with a <CODE>merge_range()</CODE> method call is marked internally as being associated with a merged range.It

6876

is a fatal error to use a merged format in a non-merged cell. The current

6877

workaround is to use separate formats for merged and non-merged cell. This

6878

restriction will be removed in a future release.

6879

6880

6881

6882

Nested formulas sometimes aren't parsed correctly and give a result of

6883

"#VALUE". If you come across a formula that parses like this, let

6884

me know.

6885

6886

6887

6888

Spreadsheet::ParseExcel: All formulas created by Spreadsheet::WriteExcel

6889

are read as having a value of zero. This is because Spreadsheet::WriteExcel

6890

only stores the formula and not the calculated result.

6891

6892

6893

6894

OpenOffice.org: Some formatting is not displayed correctly.

6895

6896

6897

6898

Gnumeric: Some formatting is not displayed correctly. URLs are not

6899

displayed as links. Page setup can cause Gnumeric to crash.

6900

6901

6902

6903

The lack of a portable way of writing a little-endian 64 bit IEEE float.

6904

There is beta code available to fix this. Let me know if you wish to test

6905

it on your platform.

6906

6907

6908

6909

If you wish to submit a bug report run the <CODE>bug_report.pl</CODE> program in the <CODE>examples</CODE> directory of the distro.

6910

6911

6912

6913

<HR>

6914

6915

6916

The roadmap is as follows:

6917

6918

6919

<UL>

6920

<LI><A NAME="item_Add">Add write_comment().</A>

6921

<LI><A NAME="item_Add">Add AutoFilters.</A>

6922

</UL>

6923

6924

Also, here are some of the most requested features that probably won't get

6925

added:

6926

6927

6928

<UL>

6929

<LI><A NAME="item_Macros">Macros.</A>

6930

6931

This would solve some other problems neatly. However, the format of Excel

6932

macros isn't documented.

6933

6934

6935

<LI><A NAME="item_Some">Some feature that you really need. ;-)</A>

6936

</UL>

6937

6938

If there is some feature of an Excel file that you really, really need then

6939

you should use Win32::OLE with Excel on Windows. If you are on Unix you

6940

could consider connecting to a Windows server via Docserver or SOAP, see <A HREF="#WRITING_EXCEL_FILES">WRITING EXCEL FILES</A>.

6941

6942

6943

6944

<HR>

6945

<H1><A NAME="MAILING_LIST">MAILING LIST</A></H1>

6946

6947

There is a Google group for discussing and asking questions about

6948

Spreadsheet::WriteExcel: <A

6949

HREF="http://groups-beta.google.com/group/spreadsheet-writeexcel/">http://groups-beta.google.com/group/spreadsheet-writeexcel/</A>

6950

6951

6952

<tr>

6953

6954

6955

</td>

6956

<td colspan=2 align=center>Subscribe to Spreadsheet::WriteExcel</td>

6957

</tr>

6958

6959

<tr>

6960

<td>Email: <input type=text name=email></td>

6961

<td>

6962

<table

6963

style="background-color:#ffcc33;padding:2px;border:2px outset #ffcc33;">

6964

<tr>

6965

<td>

6966

6967

</td>

6968

</tr>

6969

</table>

6970

</td>

6971

</tr>

6972

</form>

6973

6974

<a href="http://groups-beta.google.com/group/spreadsheet-writeexcel">Browse Archives</a> at

6975

<a href="http://groups-beta.google.com/">groups-beta.google.com</a>

6976

</td></tr>

6977

</table>

6978

6979

6980

Alternatively you can keep up to date with future releases by subscribing

6981

at: <A

6982

HREF="http://freshmeat.net/projects/writeexcel/">http://freshmeat.net/projects/writeexcel/</A>

6983

6984

6985

6986

6987

<HR>

6988

<H1><A NAME="DONATATIONS">DONATATIONS</A></H1>

6989

6990

If you'd care to donate to the Spreadsheet::WriteExcel project, you can do

6991

so via PayPal:

6992

6993

6994

6995

6996

6997

6998

6999

7000

7001

7002

7003

</form>

7004

</center>

7005

7006

7007

7008

<HR>

7009

7010

7011

Spreadsheet::ParseExcel: <A

7012

HREF="http://search.cpan.org/dist/Spreadsheet-ParseExcel">http://search.cpan.org/dist/Spreadsheet-ParseExcel</A>

7013

7014

7015

7016

7017

Spreadsheet-WriteExcel-FromXML: <A

7018

HREF="http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromXML">http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromXML</A>

7019

7020

7021

7022

7023

Spreadsheet::WriteExcel::FromDB: <A

7024

HREF="http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB">http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB</A>

7025

7026

7027

7028

7029

Excel::Template: <A

7030

HREF="http://search.cpan.org/~rkinyon/Excel-Template/">http://search.cpan.org/~rkinyon/Excel-Template/</A>

7031

7032

7033

7034

7035

DateTime::Format::Excel: <A

7036

HREF="http://search.cpan.org/dist/DateTime-Format-Excel">http://search.cpan.org/dist/DateTime-Format-Excel</A>

7037

7038

7039

7040

7041

"Reading and writing Excel files with Perl" by Teodor Zlatanov, atIBM

7042

developerWorks: <A

7043

HREF="http://www-106.ibm.com/developerworks/library/l-pexcel/">http://www-106.ibm.com/developerworks/library/l-pexcel/</A>

7044

7045

7046

7047

7048

"Excel-Dateien mit Perl erstellen - Controller im Gl�ck" by Peter

7049

Dintelmann and Christian Kirsch in the German Unix/web journal iX: <A

7050

HREF="http://www.heise.de/ix/artikel/2001/06/175/">http://www.heise.de/ix/artikel/2001/06/175/</A>

7051

7052

7053

7054

7055

"Spreadsheet::WriteExcel" in The Perl Journal: <A

7056

HREF="http://www.samag.com/documents/s=1272/sam05030004/">http://www.samag.com/documents/s=1272/sam05030004/</A>

7057

7058

7059

7060

7061

Spreadsheet::WriteExcel documentation in Japanese by Takanori Kawai. <A

7062

HREF="http://member.nifty.ne.jp/hippo2000/perltips/Spreadsheet/WriteExcel.htm">http://member.nifty.ne.jp/hippo2000/perltips/Spreadsheet/WriteExcel.htm</A>

7063

7064

7065

7066

7067

Oesterly user brushes with fame: <A

7068

HREF="http://oesterly.com/releases/12102000.html">http://oesterly.com/releases/12102000.html</A>

7069

7070

7071

7072

7073

<HR>

7074

<H1><A NAME="ACKNOWLEDGEMENTS">ACKNOWLEDGEMENTS</A></H1>

7075

7076

The following people contributed to the debugging and testing of

7077

Spreadsheet::WriteExcel:

7078

7079

7080

7081

Alexander Farber, Andre de Bruin, Arthur@ais, Artur Silveira da Cunha,

7082

Borgar Olsen, Brian White, Bob Mackay, Cedric Bouvier, Chad Johnson, CPAN

7083

testers, Damyan Ivanov, Daniel Berger, Daniel Gardner, Dmitry Kochurov,

7084

Eric Frazier, Ernesto Baschny, Felipe P�rez Galiana, Gordon Simpson, Hanc

7085

Pavel, Harold Bamford, James Holmes, James Wilkinson, Johan Ekenberg,

7086

Johann Hanne, Jonathan Scott Duff, J.C. Wren, Kenneth Stacey, Keith Miller,

7087

Kyle Krom, Marc Rosenthal, Markus Schmitz, Michael Braig, Michael

7088

Buschauer, Mike Blazer, Michael Erickson, Michael W J West, Ning Xie, Paul

7089

J. Falbe, Paul Medynski, Peter Dintelmann, Pierre Laplante, Praveen Kotha,

7090

Reto Badertscher, Rich Sorden, Shane Ashby, Shenyu Zheng, Stephan Loescher,

7091

Steve Sapovits, Sven Passig, Svetoslav Marinov, Tamas Gulacsi, Troy

7092

Daniels, Vahe Sarkissian.

7093

7094

7095

7096

The following people contributed patches, examples or Excel information:

7097

7098

7099

7100

Andrew Benham, Bill Young, Cedric Bouvier, Charles Wybble, Daniel Rentz,

7101

David Robins, Franco Venturi, Guy Albertelli, Ian Penman, John Heitmann,

7102

Jon Guy, Kyle R. Burton, Pierre-Jean Vouette, Rubio, Marco Geri, Mark

7103

Fowler, Matisse Enzer, Sam Kington, Takanori Kawai, Tom O'Sullivan.

7104

7105

7106

7107

Many thanks to Ron McKelvey, Ronzo Consulting for Siemens, who sponsored

7108

the development of the formula caching routines.

7109

7110

7111

7112

Additional thanks to Takanori Kawai for translating the documentation into

7113

Japanese.

7114

7115

7116

7117

Gunnar Wolf maintains the Debian distro.

7118

7119

7120

7121

Thanks to Damian Conway for the excellent Parse::RecDescent.

7122

7123

7124

7125

Thanks to Tim Jenness for File::Temp.

7126

7127

7128

7129

Thanks to Michael Meeks and Jody Goldberg for their work on Gnumeric.

7130

7131

7132

7133

<HR>

7134

<H1><A NAME="AUTHOR">AUTHOR</A></H1>

7135

7136

John McNamara <A HREF="mailto:jmcnamara@cpan.org">jmcnamara@cpan.org</A>

7137

7138

7139

7140

<PRE>

7141

Olha que coisa mais linda

7142

Mais cheia de gra�a. � ela menina

7143

Que vem e que passa

7144

Num doce balan�o a caminho do mar

7145

</PRE>

7146

7147

7148

<PRE>

7149

-- Vinicius de Moraes

7150

</PRE>

7151

7152

7153

<HR>

7154

<H1><A NAME="COPYRIGHT">COPYRIGHT</A></H1>

7155

7156

� MM-MMV, John McNamara.

7157

7158

7159

7160

7161

redistributed and/or modified under the same terms as Perl itself.

7162

7163

7164

7165

</BODY>

7166

7167

</HTML>

Older »