Problem using Spreadsheet::Excel

[dave]

Hello all,

I have been searching the archives for a while and can't seem to find a
solution to my problem. The quick and dirty of it is I am pulling data
from an Oracle database via DBI. I am using the following code to pull
data and write it into an excel spreadsheet:

59 while ( my @row = $sth->fetchrow_array() ) {
60 my $array_ref = \@row;
61 $worksheet1->keep_leading_zeros();
62 $worksheet1->write_row( $row_num, 0, $array_ref );
63 $row_num = $row_num + 1;
64
65 my $line = join("|", map{defined($_) ? $_ : ""} @row);
66 print $line, "\n";
67 push(@DOCIDS, "$row[2]");
68 push(@DATA, $line);
69 }

After executing this I fill up the array @DATA and @DOCIDS and I print
each row (ie line 66) so I know I am fetching the data correctly. But
my Excel doc is not being written. If I were to put a die statement at
line 62 it would say "File not found". What does this mean. This is
really driving me nuts! It should work. Let me know if I need to
supply more info. Below is the entire code from script:
1 #!/usr/bin/perl
2
3 use warnings;
4 use strict;
5
6 use DBI;
7 use Spreadsheet::WriteExcel;
8 use Getopt::Std;
9
10 # Database variables
11 # my $ORAINST = "BLAH";
12 my $ORAINST = "BLAH.BLAH.COM";
13 my $DBUSER = "xxxxxx";
14 # my $DBPASSWD = "xxxxxxx";
15 my $DBPASSWD = "xxxxxxxx";
16 my $DBH =
DBI->connect("dbi:Oracle:$ORAINST","$DBUSER","$DBPASSWD") or
17 die "Couldn't open database connection: $!";
18
19 # Options
20 use vars qw/%opt/;
21
22 # Globals
23 my @DATA = ();
24 my $ROW_LIMIT = 65536;
25 my @DOCIDS = ();
26 my $BUFFER_SIZE = 5000000;
27 my $LONG_RAW_TYPE = 113;
28
29
30 my $workbook = Spreadsheet::WriteExcel->new('test.xls');
31 $worksheet1 = $workbook->add_worksheet("test 1");
32
33 # Build initial headings
34 $worksheet1->write(0, 0, 'Supplier Id');
35 $worksheet1->write(0, 1, 'Agreement Id');
36 $worksheet1->write(0, 2, 'Document Id');
37 $worksheet1->write(0, 3, 'Outlet Id');
38 $worksheet1->write(0, 4, 'Supplier Customer Number');
39 $worksheet1->write(0, 5, 'Outlet');
40 $worksheet1->write(0, 6, 'Address 1');
41 $worksheet1->write(0, 7, 'City');
42 $worksheet1->write(0, 8, 'State');
43 $worksheet1->write(0, 9, 'Zip');
44 $worksheet1->write(0, 10, 'Display Name');
45 $worksheet1->write(0, 11, 'Display Type');
46 $worksheet1->write(0, 12, 'Product Name');
47 $worksheet1->write(0, 13, 'UPC');
48 $worksheet1->write(0, 14, 'Wholesaler Item Code');
49 $worksheet1->write(0, 15, 'Order Quantity');
50
51 #&init();
52
53 my $sqlStmt = &sql_statement_setup( $opt{S}, $opt{P} );
54
55 my $sth = $DBH->prepare($sqlStmt) || die "\nPrepare error: $DBI::err
.... $DBI::err str\n";
56 $sth->execute() || die "\nExecute error: $DBI::err ...
$DBI::errstr\n";
57
58 my $row_num = 1;
59 while ( my @row = $sth->fetchrow_array() ) {
60 my $array_ref = \@row;
61 $worksheet1->keep_leading_zeros();
62 $worksheet1->write_row( $row_num, 0, $array_ref );
63 $row_num = $row_num + 1;
64
65 my $line = join("|", map{defined($_) ? $_ : ""} @row);
66 print $line, "\n";
67 push(@DOCIDS, "$row[2]");
68 push(@DATA, $line);
69 }
70
71
72
73
74
75
76
77 # Subroutines
78
79 sub init() {
80 my $opt_string = 'hS:';
81 getopts("$opt_string", \%opt) or usage();
82 if (!defined($opt{S})) { $opt{S} = 1248; }
83 if (!defined($opt{P})) { $opt{P} = 18; }
84 usage() if $opt{h};
85
86 return 0;
87 }
88
89 sub usage() {
90 print <<EOF;
91 usage: $0 -S <supp id> -P <prgrm id>
92
93 -h rints this help message.
94 -S <supplier id> :The supplier id number.
95 -P <program id :The program id number.
96 EOF
97
98 return 1;
99 }
100
101
102 # sql_statement_setup()
103 sub sql_statement_setup() {
104 my $supplier_id = $_[0];
105 my $program_id = $_[1];
106
107 my $sql = <<END;
108 SELECT a.supplier_id,
109 a.agreement_id,
110 a.document_id,
111 a.outlet_id,
112 a.supplier_customer_number,
113 b.outlet_name,
114 c.address_1,
115 c.city,
116 c.state,
117 c.zip,
118 e.display_name,
119 e.display_type,
120 h.product_name,
121 h.upc,
122 (SELECT z.wholesaler_item_code
123 FROM stsdm.product_lu z
124 WHERE z.wholesaler_id = a.supplier_id
125 AND z.p_display_id = f.p_display_id
126 AND z.upc = h.upc
127 AND z.wholesaler_id = a.supplier_id) AS
WHOLESALER_ITEM_CODE,
128 nvl((SELECT z.order_quantity
129 FROM stsdm.product_lu z
130 WHERE z.wholesaler_id = a.supplier_id
131 AND z.p_display_id = f.p_display_id
132 AND z.upc = h.upc),
133 (SELECT max(z.order_quantity)
134 FROM stsdm.product_lu z
135 WHERE z.p_display_id = f.p_display_id
136 AND z.upc = h.upc)) AS ORDER_QUANTITY
137 FROM stsdm.agreement a,
138 sts.outlet b,
139 sts.location c,
140 stsdm.agreement_detail d,
141 stsdm.display e,
142 stsdm.program_display f,
143 stsdm.product_lu h
144 WHERE a.supplier_id = \'$supplier_id\'
145 AND a.program_id = \'$program_id\'
146 AND a.outlet_id = b.outlet_id
147 AND b.loc_id = c.loc_id
148 AND a.agreement_id = d.agreement_id
149 AND d.p_display_id = f.p_display_id
150 AND f.display_id = e.display_id
151 AND f.p_display_id = h.p_display_id
152 ORDER BY a.supplier_id, a.outlet_id, e.display_name
153 END
154
155 return $sql;
156 }
157

 

[J. Gleixner]

But
my Excel doc is not being written. If I were to put a die statement at
line 62 it would say "File not found". What does this mean.

It means the file it's trying to write isn't found. Put those die
statements back in, and add them to the rest of your code to help you
debug it. You have it for your DBI related code, so why not the rest?
Also, you could narrow down your issue by working only the
WriteExcel related code, once it writes row 0, then add in
some fake data for the rows, then add in the DBI.

30 my $workbook = Spreadsheet::WriteExcel->new('test.xls')

or die "Can't create text.xls: $!";

31 $worksheet1 = $workbook->add_worksheet("test 1");

Hang on.. you have enabled strict but it doesn't complain about
$worksheet1 not being defined?

53 my $sqlStmt = &sql_statement_setup( $opt{S}, $opt{P} );

Don't use '&', also give placeholders a try, in your SQL.

54
55 my $sth = $DBH->prepare($sqlStmt) || die "\nPrepare error: $DBI::err
... $DBI::err str\n";
56 $sth->execute() || die "\nExecute error: $DBI::err ...
$DBI::errstr\n";
57
58 my $row_num = 1;
59 while ( my @row = $sth->fetchrow_array() ) {

Could use fetchrow_arrayref.

60 my $array_ref = \@row;

No reason to do that.

61 $worksheet1->keep_leading_zeros();

Possibly you only need to call this once.

62 $worksheet1->write_row( $row_num, 0, $array_ref );
63 $row_num = $row_num + 1;

$worksheet1->write_row( $row_num++, 0, \@row );

64
65 my $line = join("|", map{defined($_) ? $_ : ""} @row);

What is the map doing that join('|', @row) doesn't do?

 

[dave]

Ok so I followed your suggestions. I did a little house cleaning and
prototyped my subroutines and took out the "&" that I put in front of
them. I also simplified my join function and it still works like you
said it would.

I put die statements after every function call, both DBI and WriteExcel
and my script died at line 62 (now it is 68) like I had said originally
with a "File not found". I am kind of at a loss since the excel file
gets created at the beginning of the script and I am able to create
worksheets just fine. I would think the worksheet data structures
would be pointing to the correct location of the file. I know that
they work ok because in lines 34 thru 49 I execute
"$worksheet1->write(0, X, '<heading name>')" for each one of my column
names where X is the column number and <heading name> is...well my
heading name.

Its the following code where the breakdown is:
65 $worksheet1->keep_leading_zeros() or die "Couldn't execute
keep_leading_zeros method: $!";
66 my $row_num = 1;
67 while ( my @row = $sth->fetchrow_array() ) {
68 $worksheet1->write_row( $row_num, 0, \@row ) or die "Couldn't
execute write method: $!";
69 $row_num = $row_num + 1;
70
71 my $line = join("|", map{defined($_) ? $_ : ""} @row);
72 #my $line = join("|",@row);
73 print $line, "\n";
74 push(@DOCIDS, "$row[2]");
75 push(@DATA, $line);
76 }

line 68 just does not want to work. I don't want to use
$sth->fetchrow_arrayref() because I like to be able to build the @DATA
and @DOCID arrays simultaneously. I am sure there is a better way to
do this but this should work.

No where in my script do I ever change the current working directory of
the script's process to another directory. That is the only
concievable way I can think of that would return an error of "File not
found". The other is maybe the $worksheet1 data structure has fallen
out of scope but that doesn't make any sense since it is a globaly
defined variable. And also I think the error would be worse than a
file not found.

Any ideas?

Dave

 

[dave]

Here is some new data. I read in the WriteExcel docs that the write
method returns 0 on success. Well I evaluated this in my code and I
found that my write_row method was indeed returning 0 which would leave
me to believe that at least it thinks it is writing correctly. But at
the same time $! = "No such file or directory".

I am ignorant to the way perl internals work so if this behavior is not
unusal then I would be much obliged if someone could clue me in.

Dave

Ok so I followed your suggestions. I did a little house cleaning and
prototyped my subroutines and took out the "&" that I put in front of
them. I also simplified my join function and it still works like you
said it would.

I put die statements after every function call, both DBI and WriteExcel
and my script died at line 62 (now it is 68) like I had said originally
with a "File not found". I am kind of at a loss since the excel file
gets created at the beginning of the script and I am able to create
worksheets just fine. I would think the worksheet data structures
would be pointing to the correct location of the file. I know that
they work ok because in lines 34 thru 49 I execute
"$worksheet1->write(0, X, '<heading name>')" for each one of my column
names where X is the column number and <heading name> is...well my
heading name.

Its the following code where the breakdown is:
65 $worksheet1->keep_leading_zeros() or die "Couldn't execute
keep_leading_zeros method: $!";
66 my $row_num = 1;
67 while ( my @row = $sth->fetchrow_array() ) {
68 $worksheet1->write_row( $row_num, 0, \@row ) or die "Couldn't
execute write method: $!";
69 $row_num = $row_num + 1;
70
71 my $line = join("|", map{defined($_) ? $_ : ""} @row);
72 #my $line = join("|",@row);
73 print $line, "\n";
74 push(@DOCIDS, "$row[2]");
75 push(@DATA, $line);
76 }

line 68 just does not want to work. I don't want to use
$sth->fetchrow_arrayref() because I like to be able to build the @DATA
and @DOCID arrays simultaneously. I am sure there is a better way to
do this but this should work.

No where in my script do I ever change the current working directory of
the script's process to another directory. That is the only
concievable way I can think of that would return an error of "File not
found". The other is maybe the $worksheet1 data structure has fallen
out of scope but that doesn't make any sense since it is a globaly
defined variable. And also I think the error would be worse than a
file not found.

Any ideas?

Dave

statements back in, and add them to the rest of your code to help you
debug it. You have it for your DBI related code, so why not the rest?
Also, you could narrow down your issue by working only the
WriteExcel related code, once it writes row 0, then add in
some fake data for the rows, then add in the DBI.
$worksheet1 not being defined?

 

[Jim Gibson]

Hello all,

I have been searching the archives for a while and can't seem to find a
solution to my problem. The quick and dirty of it is I am pulling data
from an Oracle database via DBI. I am using the following code to pull
data and write it into an excel spreadsheet:

59 while ( my @row = $sth->fetchrow_array() ) {
60 my $array_ref = \@row;
61 $worksheet1->keep_leading_zeros();
62 $worksheet1->write_row( $row_num, 0, $array_ref );
63 $row_num = $row_num + 1;
64
65 my $line = join("|", map{defined($_) ? $_ : ""} @row);
66 print $line, "\n";
67 push(@DOCIDS, "$row[2]");
68 push(@DATA, $line);
69 }

After executing this I fill up the array @DATA and @DOCIDS and I print
each row (ie line 66) so I know I am fetching the data correctly. But
my Excel doc is not being written. If I were to put a die statement at
line 62 it would say "File not found". What does this mean. This is
really driving me nuts! It should work. Let me know if I need to
supply more info.

The documentation for Spreadsheet::WriteExcel describes what is
returned by the write methods. It does not mention setting $!, if that
is what you are printing to get "File not found". Since you don't show
us the code that produced that warning, we cannot tell. The value of $!
is not being set and is misleading. You should save and print the
actual numerical value returned by write_row and consult the
documentation as to what it means.

Since you are having trouble creating the spreadsheet and not
apparently the DBI module, you should first create a program that
writes some fixed data to a spreadsheet. If that program does not work,
someone here will be able to tell you why.

If the spreadsheet program does work, then you can add the database
code to fetch the data from the database. If that combination doesn't
work, then feel free to post the program here. But you should first
make sure you can create a spreadsheet successfully before suspecting
an interaction between the spreadsheet and the database statements.

Please make an attempt to follow the guidelines for this group,
including 1) not top-posting and 2) posting real code that compiles.

Thank you.