List,
I was too quick to throw this out - I have found the answer.
$format01 = $workbook->add_format();
$format01->set_align('center');
$ws1->keep_leading_zeros();
$ws1->write_row($rowcnt,0,[EMAIL PROTECTED], $format01);
Ken
Ken Barker
IT Lead
Americall Group, Inc
314-213-7927
[EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Ken
Barker
Sent: Thursday, May 26, 2005 3:04 PM
To: [email protected]
Subject: Spreadsheet:WriteExcel
List:
I am using Spreadsheet::WriteExcel and having some difficulty with formatting
cells.
I am seeing some strange things with the set_row. Take for example these two
lines:
$ws1->write_row($rowcnt,$colcnt,[EMAIL PROTECTED], -align=>center);
This line is the line that is writing the line into the spreadsheet
$ws1->set_column('H:H',11,$workbook->add_format(-align => center), num_format
=> '################');
This line is attempting to format the cells.
The format is not taking in either location - the cells are left justified and
the number column is forced into scientific notation. I do have successful
formats for the header row - which is raw text.
Got any ideas on what is going on?
Ken
Ken Barker
IT Lead
Americall Group, Inc
314-213-7927
[EMAIL PROTECTED]
-----Original Message-----
From: Cordoni, Ray [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 26, 2005 1:14 PM
To: Ken Barker
Subject: RE: Win32 OLE - Excel formatting
Hi Ken
I haven't used set_selection at all. I took a look at some of the
examples and I think it is not used to set the range to apply formatting
to - instead I think it is only used to set the active cell(s). For
example, if you wanted to create a spreadsheet where you wanted it to
open with a certain cell as the active cell (maybe for data-entry), you
could use set_selection. I used set_column, set_row, and the various
write methods and all of these included the row and column to apply the
format to.
-
_________________________________
Ray Cordoni
City of Richmond
604-276-4003, Fax 604-276-4296
[EMAIL PROTECTED]
_________________________________
Better living through Technology!
-----Original Message-----
From: Ken Barker [mailto:[EMAIL PROTECTED]
Sent: Thursday, 26 May 2005 10:41 AM
To: Cordoni, Ray
Subject: RE: Win32 OLE - Excel formatting
Ray,
Thanks for all of your help. I had to upgrade to 5.8 - and now I am
moving forward. Have you used the set_selection for formatting ranges
versus using set_row or set_column? I cannot seem to get it to work. I
have tried many variations - but it seems that this just selects the
target area - and I cannot find a add_format syntax to modify the
selection.
Let me know if you have a trick for this.
Thanks again for your help.
Ken
Ken Barker
IT Lead
Americall Group, Inc
314-213-7927
[EMAIL PROTECTED]
-----Original Message-----
From: Cordoni, Ray [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 25, 2005 11:18 AM
To: Ken Barker
Subject: RE: Win32 OLE - Excel formatting
Hi Ken
The problem seems to be that Activestate no longer keeps a repository
for Perl 5.6 - or at least no longer has a 5.6 version of
Spreadsheet-WriteExcel. You can either upgrade your Perl version to 5.8
(which I have done a few months ago and works for me as long as you make
sure to add additional repositories to ppm for some modules that
Activestate does not have) or you can download
Spreadsheet-WriteExcel-2.14 from CPAN
(http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/Spreadsheet-Write
Excel-2.14.tar.gz) and install it manually to your 5.6 installation.
This is not that hard - you need to unpack the download file to a
temporary directory as well as have the nmake.exe (Windows version of
make) program available. You change to the unpacked directory and run
the following commands:
perl Makefile.PL
nmake
nmake test
nmake install
You can then delete the temporary directory as the nmake install command
will copy the needed files to your perl installation. If you want to do
this, I have downloaded the tar file from CPAN for you and repacked it
with zip and also included the nmake.exe program. I tried this on a
test PC with an old install of Perl 5.6 and the install worked even
though the "nmake test" mostly failed - I think because my perl
installation on the test PC was based on copying files from another PC
instead of running the Activestate perl installer. Here is what it
looked like when I did the install:
H:\Perl\Modules>dir
Volume in drive H is Misc Data
Volume Serial Number is EA72-6050
Directory of H:\Perl\Modules
25/05/2005 08:50a <DIR> .
25/05/2005 08:50a <DIR> ..
17/06/1998 12:00a 77,882 NMAKE.EXE
25/05/2005 08:46a <DIR> Spreadsheet-WriteExcel-2.14
1 File(s) 77,882 bytes
3 Dir(s) 54,787,452,928 bytes free
H:\Perl\Modules>cd spreadsheet-writeexcel-2.14
H:\Perl\Modules\Spreadsheet-WriteExcel-2.14>perl Makefile.PL Checking if
your kit is complete...
Looks good
Writing Makefile for Spreadsheet::WriteExcel
H:\Perl\Modules\Spreadsheet-WriteExcel-2.14>..\nmake
Microsoft (R) Program Maintenance Utility Version 6.00.8168.0
Copyright (C) Microsoft Corp 1988-1998. All rights reserved.
cp lib/Spreadsheet/WriteExcel/BIFFwriter.pm
blib\lib\Spreadsheet\WriteExcel\BIFF
writer.pm
cp lib/Spreadsheet/WriteExcel.pm blib\lib\Spreadsheet\WriteExcel.pm
cp lib/Spreadsheet/WriteExcel/Formula.pm
blib\lib\Spreadsheet\WriteExcel\Formula
.pm
cp lib/Spreadsheet/WriteExcel/Worksheet.pm
blib\lib\Spreadsheet\WriteExcel\Works
heet.pm
cp lib/Spreadsheet/WriteExcel/Utility.pm
blib\lib\Spreadsheet\WriteExcel\Utility
.pm
cp lib/Spreadsheet/WriteExcel/Format.pm
blib\lib\Spreadsheet\WriteExcel\Format.p
m
cp lib/Spreadsheet/WriteExcel/Big.pm
blib\lib\Spreadsheet\WriteExcel\Big.pm
cp lib/Spreadsheet/WriteExcel/OLEwriter.pm
blib\lib\Spreadsheet\WriteExcel\OLEwr
iter.pm
cp lib/Spreadsheet/WriteExcel/Workbook.pm
blib\lib\Spreadsheet\WriteExcel\Workbo
ok.pm
cp lib/Spreadsheet/WriteExcel/WorkbookBig.pm
blib\lib\Spreadsheet\WriteExcel\Wor
kbookBig.pm
cp lib/Spreadsheet/WriteExcel/Chart.pm
blib\lib\Spreadsheet\WriteExcel\Chart.pm
C:\Devtools\Perl\bin\perl.exe -IC:\Devtools\Perl\lib
-IC:\Devtools\Perl\ lib -MExtUtils::Command -e cp bin/chartex
blib\script\chartex
C:\Devtools\Perl\bin\perl.exe -IC:\Devtools\Perl\lib
-IC:\Devtools\Perl\ lib -e "system qq[pl2bat.bat ].shift"
blib\script\chartex
H:\Perl\Modules\Spreadsheet-WriteExcel-2.14>..\nmake test
Microsoft (R) Program Maintenance Utility Version 6.00.8168.0
Copyright (C) Microsoft Corp 1988-1998. All rights reserved.
C:\Devtools\Perl\bin\perl.exe -IC:\Devtools\Perl\lib
-IC:\Devtools\Perl\ lib -MExtUtils::Command -e cp bin/chartex
blib\script\chartex
C:\Devtools\Perl\bin\perl.exe -IC:\Devtools\Perl\lib
-IC:\Devtools\Perl\ lib -e "system qq[pl2bat.bat ].shift"
blib\script\chartex
C:\Devtools\Perl\bin\perl.exe -Mblib -IC:\Devtools\Perl\lib
-IC:\Devtool s\Perl\lib -e "use Test::Harness qw(&runtests $verbose);
$verbose=0; runtests @A RGV;" t\00_IEEE_double.t t\01_add_worksheet.t
t\01_merge_formats.t t\11_date_tim e.t t\12_date_only.t
t\13_date_seconds.t Using
H:/Perl/Modules/Spreadsheet-WriteExcel-2.14/blib
t\00_IEEE_double....ok
t\01_add_worksheet..Can't locate Test/More.pm in @INC (@INC contains:
H:/Perl/Mo
dules/Spreadsheet-WriteExcel-2.14/blib/arch
H:/Perl/Modules/Spreadsheet-WriteExc
el-2.14/blib/lib C:\Devtools\Perl\lib C:\Devtools\Perl\lib
C:/Devtools/Perl/lib C:/Devtools/Perl/site/lib . C:/Devtools/Perl/lib
C:/Devtools/Perl/site/lib .) at t\01_add_worksheet.t line 16.
BEGIN failed--compilation aborted at t\01_add_worksheet.t line 16.
t\01_add_worksheet..dubious
Test returned status 2 (wstat 512, 0x200)
t\01_merge_formats..Can't locate Test/More.pm in @INC (@INC contains:
H:/Perl/Mo
dules/Spreadsheet-WriteExcel-2.14/blib/arch
H:/Perl/Modules/Spreadsheet-WriteExc
el-2.14/blib/lib C:\Devtools\Perl\lib C:\Devtools\Perl\lib
C:/Devtools/Perl/lib C:/Devtools/Perl/site/lib . C:/Devtools/Perl/lib
C:/Devtools/Perl/site/lib .) at t\01_merge_formats.t line 18.
BEGIN failed--compilation aborted at t\01_merge_formats.t line 18.
t\01_merge_formats..dubious
Test returned status 2 (wstat 512, 0x200)
t\11_date_time......Can't locate Test/More.pm in @INC (@INC contains:
H:/Perl/Mo
dules/Spreadsheet-WriteExcel-2.14/blib/arch
H:/Perl/Modules/Spreadsheet-WriteExc
el-2.14/blib/lib C:\Devtools\Perl\lib C:\Devtools\Perl\lib
C:/Devtools/Perl/lib C:/Devtools/Perl/site/lib . C:/Devtools/Perl/lib
C:/Devtools/Perl/site/lib .) at t\11_date_time.t line 15.
BEGIN failed--compilation aborted at t\11_date_time.t line 15.
t\11_date_time......dubious
Test returned status 2 (wstat 512, 0x200)
t\12_date_only......Can't locate Test/More.pm in @INC (@INC contains:
H:/Perl/Mo
dules/Spreadsheet-WriteExcel-2.14/blib/arch
H:/Perl/Modules/Spreadsheet-WriteExc
el-2.14/blib/lib C:\Devtools\Perl\lib C:\Devtools\Perl\lib
C:/Devtools/Perl/lib C:/Devtools/Perl/site/lib . C:/Devtools/Perl/lib
C:/Devtools/Perl/site/lib .) at t\12_date_only.t line 15.
BEGIN failed--compilation aborted at t\12_date_only.t line 15.
t\12_date_only......dubious
Test returned status 2 (wstat 512, 0x200)
t\13_date_seconds...Can't locate Test/More.pm in @INC (@INC contains:
H:/Perl/Mo
dules/Spreadsheet-WriteExcel-2.14/blib/arch
H:/Perl/Modules/Spreadsheet-WriteExc
el-2.14/blib/lib C:\Devtools\Perl\lib C:\Devtools\Perl\lib
C:/Devtools/Perl/lib C:/Devtools/Perl/site/lib . C:/Devtools/Perl/lib
C:/Devtools/Perl/site/lib .) at t\13_date_seconds.t line 15.
BEGIN failed--compilation aborted at t\13_date_seconds.t line 15.
t\13_date_seconds...dubious
Test returned status 2 (wstat 512, 0x200)
Failed Test Status Wstat Total Fail Failed List of Failed
------------------------------------------------------------------------
--------
t\01_add_worksheet.t 2 512 ?? ?? % ??
t\01_merge_formats.t 2 512 ?? ?? % ??
t\11_date_time.t 2 512 ?? ?? % ??
t\12_date_only.t 2 512 ?? ?? % ??
t\13_date_seconds.t 2 512 ?? ?? % ??
Failed 5/6 test scripts, 16.67% okay. 0/2 subtests failed, 100.00% okay.
NMAKE : fatal error U1077: 'C:\Devtools\Perl\bin\perl.exe' : return code
'0x2'
Stop.
H:\Perl\Modules\Spreadsheet-WriteExcel-2.14>..\nmake install
Microsoft (R) Program Maintenance Utility Version 6.00.8168.0
Copyright (C) Microsoft Corp 1988-1998. All rights reserved.
C:\Devtools\Perl\bin\perl.exe -IC:\Devtools\Perl\lib
-IC:\Devtools\Perl\ lib -MExtUtils::Command -e cp bin/chartex
blib\script\chartex
C:\Devtools\Perl\bin\perl.exe -IC:\Devtools\Perl\lib
-IC:\Devtools\Perl\ lib -e "system qq[pl2bat.bat ].shift"
blib\script\chartex Installing
C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel.pm
Installing
C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\BIFFwriter.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\Big.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\Chart.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\Format.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\Formula.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\OLEwriter.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\Utility.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\Workbook.pm
Installing
C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\WorkbookBig.pm
Installing C:\Devtools\Perl\site\lib\Spreadsheet\WriteExcel\Worksheet.pm
Installing C:\Devtools\Perl\bin\chartex
Installing C:\Devtools\Perl\bin\chartex.bat Writing
C:\Devtools\Perl\site\lib\auto\Spreadsheet\WriteExcel\.packlist
Appending installation info to C:\Devtools\Perl\lib/perllocal.pod
H:\Perl\Modules\Spreadsheet-WriteExcel-2.14>perl -e "use
Spreadsheet::WriteExcel ;"
^-- No errors above means module should be usable.
-
_________________________________
Ray Cordoni
City of Richmond
604-276-4003, Fax 604-276-4296
[EMAIL PROTECTED]
_________________________________
Better living through Technology!
-----Original Message-----
From: Ken Barker [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 25 May 2005 08:03
To: Cordoni, Ray
Subject: RE: Win32 OLE - Excel formatting
Ray,
I do not see that list when I run PPM. What version of PPM are you
using (I have 2.2.0). When I try to install Spreadsheet::WriteExcel I
get the following:
c:\>ppm
PPM interactive shell (2.2.0) - type 'help' for available commands.
PPM> install spreadsheet::WriteExcel
Install package 'spreadsheet-WriteExcel?' (y/N): y Installing package
'spreadsheet-WriteExcel'...
Error installing package 'spreadsheet-WriteExcel': Read a PPD for
'spreadsheet-W riteExcel', but it is not intended for this build of Perl
(MSWin32-x86-multi-thr
ead)
PPM>
I am using Perl version 5.6.1 build 635 with PPM version 2.2.0.
I agree that the examples are very sketchy for the Win32:OLE and would
prefer to use this module if I could get it installed.
Ken
Ken Barker
IT Lead
Americall Group, Inc
314-213-7927
[EMAIL PROTECTED]
-----Original Message-----
From: Cordoni, Ray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 24, 2005 4:33 PM
To: Ken Barker
Subject: RE: Win32 OLE - Excel formatting
Hi Ken
It looks like this should be available from the ActiveState ppm
repository. I am using perl 5.8 and here is the ppm search I did to
show what is out there. Note with ActiveState, you search for
"Spreadsheet-WriteExcel" instead of "Spreadsheet::WriteExcel":
ppm> set fields="NAME VERSION REPOSITORY"
Setting 'fields' set to 'name version repository'.
ppm> search Spreadsheet-WriteExcel
Using cached search result set 1.
1. Spreadsheet-WriteExcel [2.14] ActiveState PPM2 Repository
[http://ppm.ActiveState.co~
2. Spreadsheet-WriteExcel [0.41] ActiveState Package
Repository [http://ppm.ActiveState~
3. Spreadsheet-WriteExcel [0.42] ActiveState Package
Repository [http://ppm.ActiveState~
4. Spreadsheet-WriteExcel [2.02] ActiveState Package
Repository [http://ppm.ActiveState~
5. Spreadsheet-WriteExcel [2.03] ActiveState Package
Repository [http://ppm.ActiveState~
6. Spreadsheet-WriteExcel [2.04] ActiveState Package
Repository [http://ppm.ActiveState~
7. Spreadsheet-WriteExcel [2.10] ActiveState Package
Repository [http://ppm.ActiveState~
8. Spreadsheet-WriteExcel [2.11] ActiveState Package
Repository [http://ppm.ActiveState~
9. Spreadsheet-WriteExcel [2.12] ActiveState Package
Repository [http://ppm.ActiveState~
10. Spreadsheet-WriteExcel [2.13] ActiveState Package
Repository [http://ppm.ActiveState~
11. Spreadsheet-WriteExcel [2.14] ActiveState Package
Repository [http://ppm.ActiveState~
12. Spreadsheet-WriteExcel-FromDB [0.09] ActiveState PPM2 Repository
[http://ppm.ActiveState.co~
13. Spreadsheet-WriteExcel-FromXML [1.02] ActiveState PPM2 Repository
[http://ppm.ActiveState.co~
14. Spreadsheet-WriteExcel-FromXML [1.01] ActiveState Package
Repository [http://ppm.ActiveState~
15. Spreadsheet-WriteExcel-FromXML [1.02] ActiveState Package
Repository [http://ppm.ActiveState~
16. Spreadsheet-WriteExcel-Simple [1.02] ActiveState PPM2 Repository
[http://ppm.ActiveState.co~
17. Spreadsheet-WriteExcel-Simple [1.02] ActiveState Package
Repository [http://ppm.ActiveState~
18. Spreadsheet-WriteExcelXML [0.09] ActiveState PPM2 Repository
[http://ppm.ActiveState.co~
19. Spreadsheet-WriteExcelXML [0.02] ActiveState Package
Repository [http://ppm.ActiveState~
20. Spreadsheet-WriteExcelXML [0.05] ActiveState Package
Repository [http://ppm.ActiveState~
21. Spreadsheet-WriteExcelXML [0.06] ActiveState Package
Repository [http://ppm.ActiveState~
22. Spreadsheet-WriteExcelXML [0.07] ActiveState Package
Repository [http://ppm.ActiveState~
23. Spreadsheet-WriteExcelXML [0.08] ActiveState Package
Repository [http://ppm.ActiveState~
24. Spreadsheet-WriteExcelXML [0.09] ActiveState Package
Repository [http://ppm.ActiveState~
ppm>
-
_________________________________
Ray Cordoni
City of Richmond
604-276-4003, Fax 604-276-4296
[EMAIL PROTECTED]
_________________________________
Better living through Technology!
-----Original Message-----
From: Ken Barker [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 24 May 2005 2:21 PM
To: Cordoni, Ray
Subject: RE: Win32 OLE - Excel formatting
Ray,
Thanks very much. I was unable to get Spreadsheet::WriteExcel to load
using ppm.
Where did you get the module.
I am running XP.
Ken
Ken Barker
IT Lead
Americall Group, Inc
314-213-7927
[EMAIL PROTECTED]
-----Original Message-----
From: Cordoni, Ray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 24, 2005 4:13 PM
To: Ken Barker
Subject: RE: Win32 OLE - Excel formatting
Hi Ken
I just finished writing a perl program to summarize information into
Excel and I used Spreadsheet::WriteExcel instead of Win32::OLE. I
didn't look too closely at Win32::OLE other than to discover that
examples are few and far between and you have to learn to "translate"
VBA examples to perl with Win32::OLE. Spreadsheet::WriteExcel seemed a
lot more straightforward and the documentation that came with more than
adaquate for the relatively simple formatting that I needed to do. The
hardest part to deal with is some commands expect cell notation in Excel
native format (A1, B1, etc) while others commands support row and column
numbers (which is generally easier to deal with). The other advantage
of Spreadsheet::WriteExcel is that it will work on a Unix system as well
as it is writing a binary formatted file and does not require Excel or
related COM objects installed on the user's workstation. I did some of
the testing of the program on a Windows 2000 PC that had Open Office
installed and no Excel and it worked pretty well - except the default
font was different so the column widths had to be changed to work with
Excel for the final version.
Rather than just showing you the Spreadsheet::WriteExcel stuff, I give
the entire program which also has stuff to handle zip files and load CSV
files to Oracle tables (which in turn drive the Excel output) - all
wrapped up in a simplistic Tk GUI. The end result I compiled with
ActiveState's PDK to distribute to the user as an exe file. The only
thing I have changed in the program from completely working in
production is some hard-coded userids and passwords.
Hope this helps
-
_________________________________
Ray Cordoni
City of Richmond
604-276-4003, Fax 604-276-4296
[EMAIL PROTECTED]
_________________________________
Better living through Technology!
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Ken Barker
Sent: Tuesday, 24 May 2005 12:59 PM
To: [email protected]
Subject: Win32 OLE - Excel formatting
I am new to the mail list. I was looking for any examples in formatting
cells in Excel using Win32:OLE.
Any help would be much appreciated.
Ken Barker
[EMAIL PROTECTED]
_______________________________________________
Perl-Win32-Users mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
______________________________________________________________________
This e-mail has been scanned by MCI Managed Email Content Service, using
Skeptic(tm) technology powered by MessageLabs. For more information on
MCI's Managed Email Content Service, visit http://www.mci.com.
______________________________________________________________________
______________________________________________________________________
This e-mail has been scanned by MCI Managed Email Content Service, using
Skeptic(tm) technology powered by MessageLabs. For more information on
MCI's Managed Email Content Service, visit http://www.mci.com.
______________________________________________________________________
______________________________________________________________________
This e-mail has been scanned by MCI Managed Email Content Service, using
Skeptic(tm) technology powered by MessageLabs. For more information on
MCI's Managed Email Content Service, visit http://www.mci.com.
______________________________________________________________________
______________________________________________________________________
This e-mail has been scanned by MCI Managed Email Content Service, using
Skeptic(tm) technology powered by MessageLabs. For more information on MCI's
Managed Email Content Service, visit http://www.mci.com.
______________________________________________________________________
_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
______________________________________________________________________
This e-mail has been scanned by MCI Managed Email Content Service, using
Skeptic(tm) technology powered by MessageLabs. For more information on MCI's
Managed Email Content Service, visit http://www.mci.com.
______________________________________________________________________
______________________________________________________________________
This e-mail has been scanned by MCI Managed Email Content Service, using
Skeptic(tm) technology powered by MessageLabs. For more information on MCI's
Managed Email Content Service, visit http://www.mci.com.
______________________________________________________________________
_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
