howto row_range() / col_range()

[fhleung]

I am new to Perl and I'd like to use this language to program MS Excel
spreedsheet.

I found the following code from John McNamara account which help to
start my own Perl program.

[code]
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('excel.xls');
my $worksheet = $workbookR->worksheet(0);
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
[/code]

Question: If I $worksheet->write() a extra row, I found that the
row_range() function not work, still have the same count of number of
rows.

Anyone have idea on this problem?

LOOKING FORWARD TO REPLY, Thank you!

[Terrence Brannon]

Leung,

We need the complete program you are working and perhaps the excel sheet you are working on.

You may find http://www.perlmonks.org a bit more friendly towards beginners.

--
You received this message because you are subscribed to the Spreadsheet::WriteExcel Google Group.

For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-writeexcel?hl=en

You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-writeexcel?hl=en

To unsubscribe send an email to spreadsheet-write...@googlegroups.com

[fhleung]

1. The following is the complete program
2. For the excel file, you can just create a "empty" excel file to run
this program

[code]
#!/usr/local/bin/perl -wT
print "Content-type: text/html\n\n";

use strict;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;


############## 30 Sept 2011

my $parser = Spreadsheet::ParseExcel->new();

my $workbookR = $parser->parse('file2.xls');

my $worksheetR = $workbookR->worksheet(0); #6 Oct

my ( $row_min, $row_max ) = $worksheetR->row_range();
my ( $col_min, $col_max ) = $worksheetR->col_range();

print "row min:$row_min row_max:$row_max col_max:$col_max\n\n";


#####################
# Open the template with SaveParser
my $parser2 = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser2->Parse('file2.xls');

my $sheet = 0;
my $row = 0;
my $col = 0;


# Get the format from the cell
my $format = $template->{Worksheet}[$sheet]
->{Cells}[$row][$col]
->{FormatNo};

my $workbook;
{
# SaveAs generates a lot of harmless warnings about unset
# Worksheet properties. You can ignore them if you wish.
local $^W = 0;
# Rewrite the file or save as a new file
$workbook = $template->SaveAs('file2.xls');

}

# Use Spreadsheet::WriteExcel methods
my $worksheet = $workbook->sheets(0);

if($row_max == -1){
for my $i (0..4){
$worksheet->write(0, $i, "A$i", $format);
}}

for my $i (0..$col_max){
$worksheet->write($row_max+1, $i, "B$i", $format);
}

$workbookR->close();
$workbook->close();
[/code]

On Oct 10, 10:55 pm, Terrence Brannon <scheme...@gmail.com> wrote:
> Leung,
>
> We need the complete program you are working and perhaps the excel sheet you
> are working on.
>

> You may findhttp://www.perlmonks.orga bit more friendly towards beginners.

> > spreadsheet-write...@googlegroups.com- Hide quoted text -
>
> - Show quoted text -

[jmcnamara]

On Oct 12, 5:35 am, fhleung <fhle...@gmail.com> wrote:
> 1. The following is the complete program
> 2. For the excel file, you can just create a "empty" excel file to run
> this program

Hi,

I ran your program and it seems to produce the required results (apart
from the $workbookR->close() call which is an error).

First time prints:
row min:0 row_max:-1 col_max:-1

file2.xls:
A0 A1 A2 A3 A4

Second time prints:
row min:0 row_max:0 col_max:4

file2.xls:
A0 A1 A2 A3 A4
B0 B1 B2 B3 B4


Note, rows and columns are zero indexed, so row_max = 0 means that
there is 1 row.

Am I missing something? Do you have some issue here that I am not
seeing?

John.
--

[fhleung]

Hello John,

First of all, thank you for the reply. Also thank you for your code
which gave me a good start.

1. Well, I forget to let you know: When I ran the program many times,
the row_max is counting
but NOTHING write to excel

2. I run the Perl program in hosting company, if "apart from the

$workbookR->close() call which is an error"

how can I debug this error? What I got only print function.

Thank you once again.

[jmcnamara]

On Oct 12, 11:11 am, fhleung <fhle...@gmail.com> wrote:
> Hello John,
>
> First of all, thank you for the reply. Also thank you for your code
> which gave me a good start.
>
> 1. Well, I forget to let you know: When I ran the program many times,
> the row_max is counting
> but NOTHING write to excel
>
> 2. I run the Perl program in hosting company, if "apart from the
> $workbookR->close() call which is an error"
> how can I debug this error? What I got only print function.

Hi,

In relation to 2: ParseExcel doesn't have a close() method so that
line is a error. You should remove it.

In relation to 1: After fixing 2 try 1 again. I tried your program
(see my last post) and I got what seems to be the expected result.

Regards,

John.
--

[fhleung]

Hello John,

The way I run the program is: in the address field of browser, type
www.mydomain.com/cgi-bin/program.pl so the program is inside
"public_html/cgi-bin"
if I put the excel file in same directory like in "public_html/cgi-
bin", I may not get the expected result.

So I'd like to put the input excel file in other directory like
"public_html/test"
<code>my $workbookR = $parser->parse('/public_html/test/file.xls');</
code> but this NOT work

My question is: howto parse excel file which is not at default path?
This question may out of scope but hope if anyone know howto, please
help.

[shawn wilson]

On Oct 12, 2011 10:36 PM, "fhleung" <fhl...@gmail.com> wrote:
>

>
> So I'd like to put the input excel file in other directory like
> "public_html/test"
> <code>my $workbookR = $parser->parse('/public_html/test/file.xls');</
> code>  but this NOT work
>
> My question is: howto parse excel file which is not at default path?
> This question may out of scope but hope if anyone know howto, please
> help.
>

Out of scope. However, if you can open the file from any other script, it'll work here. Ie, write something that pumps out a text file to the screen (browser) and go from there. Your file probably isn't opening because there isn't a /public_html directory. Mind security if you give users the ability to select different files. Probably use taint as well.

You mentioned not seeing your errors. This has everything to do with your web server and how you're calling the script. If you're running Apache, you'll probably notice it in the error.log file. Also look at %SIG stuff, Data::Dumper, psgi interactive debugger, perl's -d(ebug) option, log4perl, carp, StackTrace::More. I'd suggest you get a decent grasp on debugging before you do *anything* else - you'll be using this skill as much or more than any other in programming.

Per your experience and scope of different forums: perlmonks might help, however I would go to the begi...@perl.org list and #perl-help or #perl on irc and (maybe) stack overflow as well.

Also, if you're doing web work, get familiar with MVC. For small projects, it might not be necessary (I defer to Web::Simple until things get too big for instance) but its good to know the right way to do things so that in a year you pull less of your hair out.

On another note, I got started with perl by dealing with spreadsheets using this module. It is probably the best documented module you will find (the examples were golden to me). When you move on, you'll care about different things like what a method returns in different contexts (\me grumbles about Image::Magick's non-pod craptastic doc). While I haven't gotten intimate with this module in a few months, the doc has never failed me. It is what I think about when I consider how another module's doc should have been written.

HTH

[fhleung]

Why not the author of module have something to say?

[jmcnamara]

On Oct 14, 3:44 am, fhleung <fhle...@gmail.com> wrote:
> Why not the author of module have something to say?

Hi.

I don't always have time to reply to emails to the group. I try to
look out for posts that have some very specific technical query that
may require my direct response.

For more general queries, such as your second question on how to
access a non-default directory from a CGI program, I usually wait to
see if someone else will step in with an answer (which in many cases
they do and I am grateful for that). In fact, in this particular case
I am not even the best person to answer since I rarely work with CGI.

As with 2 of the previous commentators I would also suggest asking
your second question on Perlmonks.

John.
--

[GainfulShrimp]

I think you're going to struggle debugging your code if you are *only*
running it at the hosting company. You could try using CGI::Carp's
fatalsToBrowser or warningsToBrowser features but it's probably better
to get a better handle on debugging in general, as Shawn suggests, on
your local machine.

Maybe try installing Apache on your machine (it's free!) - it'll be
useful to learn how to run CGIs there first. Once you have a basic
Hello World type script that outputs valid headers and HTML to a
browser on the same machine/LAN, move on to reading/writing external
files and only when you have it working locally, try to do it on the
hosted platform. At least, that's what I would do.

You need to be especially careful if this code will be publicly
hosted, though. The bad guys will have a much better grasp on
security than you will as a CGI beginner and it's worth reading up on
some basic best practices to help avoid exposing your site to attack -
using taint mode and that sort of thing.

[Terrence Brannon]

Here is a case of writing another row below original range:

https://github.com/metaperl/vcpaste/blob/master/paste/2011-10-14-09-55-34/excel

As you can see the row_range method returns a new row range.