How to get cell value excel in perl [Perl]

Prev: Reverse engineering of Perl Code
Next: FAQ 3.23 Can I write useful Perl programs on the command line?

From: Obama on 19 Nov 2009 20:15

Hi good people:
I need to get value of the cell so I can split by : and do some
calculation,the format of numbers is like
12:03:20. Is there any way that I can get the value of the cell in
perl...thanks in advance..

my $workbook = Spreadsheet::WriteExcel->new($out);
my $worksheet = $workbook->add_worksheet("Result");
my $t1 = xl_rowcol_to_cell(2, 4); # E2
my $t2 = xl_rowcol_to_cell(2, 5); # F2

my ($t1_h, $t1_min, $t1_sec) = split (/\:/, $t1);
my ($t2_h, $t2_min, $t2_sec) = split (/\:/, $t1);

my $delta= $worksheet->write('G2', '=$t2_h - t1_h', $unlocked);

But it doesn't work!

From: Andrew DeFaria on 19 Nov 2009 20:39

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
<style type="text/css">
body {
font: Helvetica, Arial, sans-serif;
}
p {
font: Helvetica, Arial, sans-serif;
}
..standout {
font-family: verdana,
arial,
sans-serif;
font-size: 12px;
color: #993333;
line-height: 13px;
font-weight: bold;
margin-bottom: 10px;
}
..code {
border-top: 1px solid #ddd;
border-left: 1px solid #ddd;
border-right: 2px solid #000;
border-bottom: 2px solid #000;
padding: 10px;
margin-top: 5px;
margin-left: 5%;
margin-right: 5%;
background: #ffffea;
color: black;
-moz-border-radius: 10px;
}
..codedark {
border-top: 10px solid #03f;
border-left: 1px solid #ddd;
border-right: 2px solid grey;
border-bottom: 2px solid grey;
padding: 10px;
margin-top: 5px;
margin-left: 5%;
margin-right: 5%;
background: black;
color: yellow;
-moz-border-radius: 10px;
}
#code {
color: black;
font-size: 14px;
font-family: courier;
padding-left: 5px;
}
#line-number {
color: #804000;
font-family: Arial;
font-size: 14px;
padding-right: 5px;
border-right: 1px dotted #804000;
}
blockquote[type=cite] {
padding: 0em .5em .5em .5em !important;
border-right: 2px solid blue !important;
border-left: 2px solid blue !important;
}
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid maroon !important;
border-left: 2px solid maroon !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid teal !important;
border-left: 2px solid teal !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid purple !important;
border-left: 2px solid purple !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid green !important;
border-left: 2px solid green !important;
}
a:link {
color: blue;
}

a:visited {
color: darkblue;
}

a:hover {
color: black;
background-color: #ffffcc;
text-decoration: underline;
}

a:active {
color: red;
}
</style>
</head>
<body>
On 11/19/2009 06:15 PM, Obama wrote:
<blockquote
id="mid_93974cf9-afd3-4b95-8868-caf048371405_g1g2000pra_googlegroups_com"
cite="mid:93974cf9-afd3-4b95-8868-caf048371405(a)g1g2000pra.googlegroups.com"
type="cite">Hi good people: 
I need to get value of the cell so I can split by : and do some 
calculation,the format of numbers is like 
12:03:20. Is there any way that I can get the value of the cell in 
perl...thanks in advance.. 
 
my $workbook = Spreadsheet::WriteExcel->new($out); 
my $worksheet = $workbook->add_worksheet("Result"); 
my $t1 = xl_rowcol_to_cell(2, 4); # E2 
my $t2 = xl_rowcol_to_cell(2, 5); # F2 
 
my ($t1_h, $t1_min, $t1_sec) = split (/\:/, $t1); 
my ($t2_h, $t2_min, $t2_sec) = split (/\:/, $t1); 
 
my $delta= $worksheet->write('G2', '=$t2_h - t1_h', $unlocked); 
 
 
But it doesn't work! 
</blockquote>
What was your first indication that it failed? 
 
Have you tried using a debugger? They are very useful. 
<div class="moz-signature">-- 
<a href="http://defaria.com">Andrew DeFaria</a> 
Sometimes too much drink is not enough.
</div>
</body>
</html>

From: Obama on 19 Nov 2009 21:12

>What was your first indication that it failed?
> Have you tried using a debugger? They are very useful.--Andrew DeFariaSometimes too much drink is not enough.

The value I get is E2....E400, so I can't split it and the
calculation, what I need is to get the value of the cell not columns
number!

From: Justin C on 20 Nov 2009 05:12

On 2009-11-20, Obama <cyrusgreats(a)gmail.com> wrote:
> Hi good people:
> I need to get value of the cell so I can split by : and do some
> calculation,the format of numbers is like
> 12:03:20. Is there any way that I can get the value of the cell in
> perl...thanks in advance..
>
> my $workbook = Spreadsheet::WriteExcel->new($out);
You've created a new workbook, it is empty.

> my $worksheet = $workbook->add_worksheet("Result");
You have created a new worksheet in the above workbook, it too is empty.

> my $t1 = xl_rowcol_to_cell(2, 4); # E2
> my $t2 = xl_rowcol_to_cell(2, 5); # F2
$t1 now contains the string "E3" (not E2 as you state).
$t2 now contains the string "F3" (rows/cols counted from zero not one)

> my ($t1_h, $t1_min, $t1_sec) = split (/\:/, $t1);
> my ($t2_h, $t2_min, $t2_sec) = split (/\:/, $t1);

This makes no sense in light of what you have set those variables to
above.

> my $delta= $worksheet->write('G2', '=$t2_h - t1_h', $unlocked);
>
> But it doesn't work!

To read Excel files you need Spreadsheet::ParseExcel it should be
available from the same place you got Spreadsheet::WriteExcel.

In addition, the number format you've given looks like a time. Excel
stores time and date differently, in Excel it may look like 12:03:20,
but that may be down to the cell formatting, and not the actual contents
of the cell. You need to be sure that the actual cell content *is* as
you have shown, otherwise you'll not be able to split as you intend. For
example, 0.62 in a cell, with standard number formatting looks just like
that, change it to time formatting nn:nn:nn and it reads 14:57:02, but
you won't be able to split it with perl because that's not what perl
will see, it will see only 0.62. There are methods for reading the cell
formatting with perl, these may help you out if the number is stored as
I describe.

Justin.

--
Justin C, by the sea.

From: Obama on 20 Nov 2009 12:01

Justin,
> You have created a new worksheet in the above workbook, it too is empty.
Yes correct it is empty but I do have other function which read a file
and populate the data there so there problem here is not if this is
empty , assume it is not empty
> > my $t1 = xl_rowcol_to_cell(2, 4); # E2
> > my $t2 = xl_rowcol_to_cell(2, 5); # F2
>
> $t1 now contains the string "E3" (not E2 as you state).
> $t2 now contains the string "F3" (rows/cols counted from zero not one)
>
Not matter which column, my question was how to read/get the value
from cell

| Next | Last
Pages: 1 2
Prev: Reverse engineering of Perl Code
Next: FAQ 3.23 Can I write useful Perl programs on the command line?