modifing existing Excel cells using perl in Unix possible?

D

dd007

Hi,
I have been trying WriteExcel, and ParseExcel both to modify the
existing excel cell data, but I am not able to get through. I would
really appreciate it if some one can help me out. I have been looking
for examples for this, and a few examples showed how you can add data
to the existing spreadsheet, but so far i could not find anything on
modifying existing cell data. Any pointers?
Thanks in advance,
dd007
 
P

Peter J. Holzer

Jim said:
I use Spreadsheet::parseExcel to read Excel files and
Spreadsheet::WriteExcel to write simple spreadsheet files. As far as I
know, these modules cannot be used to update an existing spreadsheet.
See, for example, the documentation for WriteExcel:

" This module cannot be used to write to an existing Excel
file."

That's hardly a limitation, as not even MS Excel can write to an
existing Excel file (it deletes the original file and writes a new one).

The real question is: Do you lose information if you use ParseExcel to
read an Excel file and WriteExcel to write the information to an Excel
file? If so, do you lose *important* (for your problem domain)
information?

hp
 
D

dd007

Thank you every one.. but this still does not solve my problem :(

I have HTML forms with input textboxes, using which I create an Excel
file. Now I need to reload this excel into the same HTML forms with the
input textboxes displaying the values from Excel sheets. Is is too
difficult? I have no clue how to do this..

If I can find out how to display values from excel sheets to the HTML
forms, I can manage to save as a new file name, and then run system
Linux command 'mv' in my perl script to save it as an original file
name. But, for this, i need some hel in re-loading html forms with
retrieving values from excel file.

Any help will be appreciated..

Thanks again,
dd
 
B

Ben Morrow

Quoth "dd007 said:
I have HTML forms with input textboxes, using which I create an Excel
file. Now I need to reload this excel into the same HTML forms with the
input textboxes displaying the values from Excel sheets. Is is too
difficult? I have no clue how to do this..

You should be able to do this with Spreadsheet::parseExcel.
If I can find out how to display values from excel sheets to the HTML
forms, I can manage to save as a new file name, and then run system
Linux command 'mv' in my perl script to save it as an original file
name.

You do not need to use mv(1) for this; see File::Copy, or indeed just
rename in perlfunc.

Ben
 
D

DJ Stunks

Abigail said:
Ben Morrow ([email protected]) wrote on MMMMDCXXXV September MCMXCIII
in <URL:{}
{} You do not need to use mv(1) for this; see File::Copy, or indeed just
{} rename in perlfunc.


File::Copy is utterly broken, and has extremely limited capabilities
compared to the utilities it's supposed to be an alternative for.

I wouldn't touch File::Copy with a 10 feet pole.

I'd use 'rename' if and only if I'm sure both the source and the
destination are files in the same filesystem - otherwise I would use
'system mv'. And I rather write a Java program moving the file for me
then have File::Copy screw up.

Friends don't let friend use File::Copy.

well, that's quite an odd thing to say, particularly as it comes from
someone who has and does contribute to Perl. Therefore, I feel a bit
like I'm preaching to the choir, but the whole crux of the open source
community is to feed back and resolve bugs... In addition, counselling
thousands of people to roll their own copy() subroutine invalidates the
whole theory behind the CPAN archive.

So I would humbly suggest that if File::Copy really is broken (and I'd
be quite surprised if it is since 1) it's a standard module, and 2)
I've used it without issue in production) that you submit a bug report
(at minimum) or a bug report and it's corresponding bugfix.

Finally, I would also suggest re-reading _The Cathedral and the Bazaar_
to brush up on the notion that, as Linus once famously said, "Given
enough eyes, all bugs are shallow."

-jp
 
L

l v

Abigail said:
DJ Stunks ([email protected]) wrote on MMMMDCXXXV September MCMXCIII in
<URL://
// Abigail wrote:
// > Ben Morrow ([email protected]) wrote on MMMMDCXXXV September MCMXCIII
// > in <URL:// > {}
// > {} You do not need to use mv(1) for this; see File::Copy, or indeed just
// > {} rename in perlfunc.
// >
// >
// > File::Copy is utterly broken, and has extremely limited capabilities
// > compared to the utilities it's supposed to be an alternative for.
// >
// > I wouldn't touch File::Copy with a 10 feet pole.
// >
// > I'd use 'rename' if and only if I'm sure both the source and the
// > destination are files in the same filesystem - otherwise I would use
// > 'system mv'. And I rather write a Java program moving the file for me
// > then have File::Copy screw up.
// >
// > Friends don't let friend use File::Copy.
//
// well, that's quite an odd thing to say, particularly as it comes from
// someone who has and does contribute to Perl. Therefore, I feel a bit
// like I'm preaching to the choir, but the whole crux of the open source
// community is to feed back and resolve bugs... In addition, counselling
// thousands of people to roll their own copy() subroutine invalidates the
// whole theory behind the CPAN archive.
//
// So I would humbly suggest that if File::Copy really is broken (and I'd
// be quite surprised if it is since 1) it's a standard module, and 2)
// I've used it without issue in production) that you submit a bug report
// (at minimum) or a bug report and it's corresponding bugfix.
//


Please do your homework.


I've submitted the bug report.


Years ago.


And a few times a year, I rant about its brokenness. Noone seems to care
(but oh by god, people do care if you ignore a broken Perl module and
call something from a working toolbox - after all, most Perl programmers
that are active in forums are religious nuts)

In 2003, for instance, in this very forum:
<[email protected]>
Or in a thread in 2004: <[email protected]>

And if you have the stomach to visit perlmonks, you'll find posts by
me pointing out the brokenness of File::Copy.


Don't tell me I didn't warn "the community" - I've been doing so for years.


Here's a program that shows the brokenness of File::Copy::copy (move
suffers from the same problem if it needs to do a copy & delete action).


#!/usr/bin/perl

use strict;
use warnings;
no warnings 'syntax';

use File::Copy;


my ($src, $dest1, $dest2) = ("/tmp/src", "/tmp/dest1", "/tmp/dest2");

for my $file ($src, $dest1, $dest2) {
die "$file exists, not running this test\n" if -f $file;
}

umask 022 or die "umask: $!\n";

open my $fh => ">", $src or die "open: $!\n";
close $fh or die "close: $!\n";

chmod 0755 => $src or die "chmod: $!\n";

copy $src => $dest1 or die "copy: $!\n";
system cp => $src, $dest2 and die "cp: $?\n";

my $d1_perm = ((stat $dest1) [2]) & 07777;
my $d2_perm = ((stat $dest2) [2]) & 07777;

if ($d1_perm == $d2_perm) {
print "Permissions equal\n";
}
else {
die sprintf "Expected permissions %3o, got permissions %3o\n"
=> $d2_perm, $d1_perm;
}

__END__


Running the program gives:

Expected permissions 755, got permissions 644


Losing your execute bits when copying files means the module is broken.


// Finally, I would also suggest re-reading _The Cathedral and the Bazaar_
// to brush up on the notion that, as Linus once famously said, "Given
// enough eyes, all bugs are shallow."


Linus is wrong. Fixing bugs needs _tuits_, not eyes. Furthermore, more
thatn 99% of the users of an open source product will never look at the
code.


Abigail

I'm not sure I'd call that broken rather lacking an option considering
File::Copy's documentation states:

"For Unix systems, this is equivalent to the simple copy routine.'

On the AIX OSes that I use, a simple `cp file1 file2` will not keep the
file permissions, however, I need to use `cp -p file1 file2` to keep the
permissions. Other *nix's may behave differently. Therefore I submit
that File::Copy::copy() be modified to have an optional 4th parameter to
preserve file attributes if possible. This would be compatible with the
man pages for cp.
 
L

l v

Abigail said:
l v ([email protected]) wrote on MMMMDCXXXV September MCMXCIII in [snip]
<> >
<> >
<> > Running the program gives:
<> >
<> > Expected permissions 755, got permissions 644
<> >
<> >
<> > Losing your execute bits when copying files means the module is broken.
<> >
<> >
<> > // Finally, I would also suggest re-reading _The Cathedral and the Bazaar_
<> > // to brush up on the notion that, as Linus once famously said, "Given
<> > // enough eyes, all bugs are shallow."
<> >
<> >
<> > Linus is wrong. Fixing bugs needs _tuits_, not eyes. Furthermore, more
<> > thatn 99% of the users of an open source product will never look at the
<> > code.
<> >
<> >
<> > Abigail
<>
<> I'm not sure I'd call that broken rather lacking an option considering
<> File::Copy's documentation states:
<>
<> "For Unix systems, this is equivalent to the simple copy routine.'
<>
<> On the AIX OSes that I use, a simple `cp file1 file2` will not keep the
<> file permissions, however, I need to use `cp -p file1 file2` to keep the
<> permissions. Other *nix's may behave differently. Therefore I submit
<> that File::Copy::copy() be modified to have an optional 4th parameter to
<> preserve file attributes if possible. This would be compatible with the
<> man pages for cp.


Then your version of AIX isn't POSIX compliant. I quote from the specs:

[case of source_file that's a regular file, and a non existing dest_file]

if _dest_file_ does not exist, a file descriptor shall be obtained
by performing actions equivalent to the _open()_ function defined
in the Systems Interfaces volume of IEEE Std 1003.1-2001 called
using _dest_file_ as the _path_ argument, and the bitwise-inclusive
OR of O_WRONLY and O_CREAT as the _oflag_ argument. The file
permission bits of _source_file_ shall be the _mode_ argument.

POSIX mandates the implementation of '-p' for cp as well, but such an
implementation should do more than just copy file permission bits - it
also copies UID and GID, atime and mtime.

I use AIX as well, and 'cp' has never dropped execute bits on me.
Perhaps you have a restrictive umask?



Abigail

Perhaps we have a more secured environment where restrictive umasks are
mandated. In my environments cp has behaved as I've stated for 6 years
over several versions. Therefore I would not and had not expected
File::Copy to copy the permissions.

Would it be more accurate to state that File::Copy is not POSIX
compliant then?
 
M

Mumia W.

Ben said:
Quoth "dd007 said:
I have HTML forms with input textboxes, using which I create an Excel
file. Now I need to reload this excel into the same HTML forms with the
input textboxes displaying the values from Excel sheets. Is is too
difficult? I have no clue how to do this..

You should be able to do this with Spreadsheet::parseExcel.
[...]

Spreadsheet::parseExcel is in ALPHA and has been since 2001. I wasn't
able to get it to work.

YMMV
 
M

Mirco Wahab

Hi Abigail

you mentioned
[snip] - after all, most Perl programmers
that are active in forums are religious nuts

I don't think that this is still
true - these times are long gone.

This (my) observation corresponds
to the notion of 'fresh languages',
their paradigm of 'originality' and
'independence'. These languages tend
to draw the religious people into
their tail waves nowadays (doesn't
mean these languages are 'bad').

If you are some newbie, then believing
into the 'outstandingness' of your
language to learn helps alot ...

This is what you always see in the
language newsgroups today - but no
newbie would bother to learn Perl
anymore (at least I know nobody).

Because they get drawn *first* into some
other language (more likely than not) -
and then develop their (necessary) religious
feeling about it - there's no way back,
see first commandmend) ;-))

So one may found people here to be religious
about themselves - but not about Perl anymore.

Regards

Mirco
 
U

usenet

On the AIX OSes that I use, a simple `cp file1 file2` will not keep the
file permissions, however, I need to use `cp -p file1 file2` to keep the
permissions.

Oh, really? What sort of AIX are you using to say that? Consider:

$ uname -M
IBM,9111-520
$ oslevel -r
5300-03
$ umask
022
$ ls >test.txt
$ ls -l test.txt
-rw-r--r-- 1 stuv stuv 28274 May 11 10:53 test.txt
$ chmod 755 test.txt
$ ls -l test.txt
-rwxr-xr-x 1 stuv stuv 28274 May 11 10:53 test.txt
cp test.txt test2.txt
$ cp test.txt test2.txt
$ ls -l test2.txt
-rwxr-xr-x 1 stuv stuv 28274 May 11 10:56 test2.txt

Hmmm. Looks like my AIX 5.3 system managed to preserve the permission
bits with a simple 'cp' command.

The problem with File::Copy is that the syscopy routine is the same as
the copy routine for UNIX machines. Abagail is correct - that is drain
bamaged.
 
L

l v

Abigail said:
l v ([email protected]) wrote on MMMMDCXXXVI September MCMXCIII in
<URL:== Abigail wrote:
== > l v ([email protected]) wrote on MMMMDCXXXV September MCMXCIII in
== [snip]
== > <> >
== > <> >
== > <> > Running the program gives:
== > <> >
== > <> > Expected permissions 755, got permissions 644
== > <> >
== > <> >
== > <> > Losing your execute bits when copying files means the module is broken.
== > <> >
== > <> >
== > <> > // Finally, I would also suggest re-reading _The Cathedral and the Bazaar_
== > <> > // to brush up on the notion that, as Linus once famously said, "Given
== > <> > // enough eyes, all bugs are shallow."
== > <> >
== > <> >
== > <> > Linus is wrong. Fixing bugs needs _tuits_, not eyes. Furthermore, more
== > <> > thatn 99% of the users of an open source product will never look at the
== > <> > code.
== > <> >
== > <> >
== > <> > Abigail
== > <>
== > <> I'm not sure I'd call that broken rather lacking an option considering
== > <> File::Copy's documentation states:
== > <>
== > <> "For Unix systems, this is equivalent to the simple copy routine.'
== > <>
== > <> On the AIX OSes that I use, a simple `cp file1 file2` will not keep the
== > <> file permissions, however, I need to use `cp -p file1 file2` to keep the
== > <> permissions. Other *nix's may behave differently. Therefore I submit
== > <> that File::Copy::copy() be modified to have an optional 4th parameter to
== > <> preserve file attributes if possible. This would be compatible with the
== > <> man pages for cp.
== >
== >
== > Then your version of AIX isn't POSIX compliant. I quote from the specs:
== >
== > [case of source_file that's a regular file, and a non existing dest_file]
== >
== > if _dest_file_ does not exist, a file descriptor shall be obtained
== > by performing actions equivalent to the _open()_ function defined
== > in the Systems Interfaces volume of IEEE Std 1003.1-2001 called
== > using _dest_file_ as the _path_ argument, and the bitwise-inclusive
== > OR of O_WRONLY and O_CREAT as the _oflag_ argument. The file
== > permission bits of _source_file_ shall be the _mode_ argument.
== >
== > POSIX mandates the implementation of '-p' for cp as well, but such an
== > implementation should do more than just copy file permission bits - it
== > also copies UID and GID, atime and mtime.
== >
== > I use AIX as well, and 'cp' has never dropped execute bits on me.
== > Perhaps you have a restrictive umask?
== >
== >
== >
== > Abigail
==
== Perhaps we have a more secured environment where restrictive umasks are
== mandated. In my environments cp has behaved as I've stated for 6 years
== over several versions. Therefore I would not and had not expected
== File::Copy to copy the permissions.
==
== Would it be more accurate to state that File::Copy is not POSIX
== compliant then?
==


And the difference with broken is ... ?



Abigail

just a difference of opinion based on interpretation of unclear
documentation.
 
L

l v

Oh, really? What sort of AIX are you using to say that? Consider:

$ uname -M
IBM,9111-520
$ oslevel -r
5300-03
$ umask
022
$ ls >test.txt
$ ls -l test.txt
-rw-r--r-- 1 stuv stuv 28274 May 11 10:53 test.txt
$ chmod 755 test.txt
$ ls -l test.txt
-rwxr-xr-x 1 stuv stuv 28274 May 11 10:53 test.txt
cp test.txt test2.txt
$ cp test.txt test2.txt
$ ls -l test2.txt
-rwxr-xr-x 1 stuv stuv 28274 May 11 10:56 test2.txt

Hmmm. Looks like my AIX 5.3 system managed to preserve the permission
bits with a simple 'cp' command.

The problem with File::Copy is that the syscopy routine is the same as
the copy routine for UNIX machines. Abagail is correct - that is drain
bamaged.

# uname -M
IBM,9117-570
# oslevel -r
5200-05
# umask
027
# ls >test.txt
# ls -l test.txt
-rw-r----- 1 veatcla sapsys 90 May 11 20:30 test.txt

# chmod 755 test.txt
# ls -l test.txt
-rwxr-xr-x 1 veatcla sapsys 90 May 11 20:30 test.txt
# cp test.txt test2.txt
# ls -l test2.txt
-rwxr-x--- 1 veatcla sapsys 90 May 11 20:32 test2.txt

# chmod 775 test.txt
# ls -l test.txt
-rwxrwxr-x 1 veatcla sapsys 90 May 11 20:30 test.txt
# cp test.txt test3.txt
# ls -l test3.txt
-rwxr-x--- 1 veatcla sapsys 90 May 11 20:35 test3.txt
 
M

Mumia W.

Jim said:
It works for me (version 0.2603 under Perl 5.8.6), although I only do
simple text extraction. Can you post a short sample program that is not
working so someone can help you?

I got it to work. Initially, I was confused about how many sub-objects
have to be dereferenced.

Here's my program:

#!/usr/bin/perl
use strict;
use warnings;
use CGI qw(escapeHTML);
use Spreadsheet::parseExcel;
use Data::Dumper;

my $ofile = new Spreadsheet::parseExcel;
my $obook = $ofile->Parse('excel.xls')
or die ("Failed to open the excel file.\n");
my $wks = $obook->{Worksheet}[0];

print <<EOF;
<title> Parsing Excel </title>
<p> Data should appear below. </p>
EOF

my ($row, $col);
die ("Can't find min/max rows\n")
unless (defined($wks->{MinRow}) && defined($wks->{MaxRow}));
die ("Can't find min/max columns\n")
unless (defined($wks->{MaxCol}) && defined($wks->{MinCol}));

print "<table border=1>\n";
for $row ($wks->{MinRow}..$wks->{MaxRow}) {
print "<tr> ";
for $col ($wks->{MinCol}..$wks->{MaxCol}) {
my $cell = $wks->{Cells}[$row][$col];
if ($cell) {
my $value = $cell->{Val};
$value = escapeHTML($value);
print "<td> $value </td> ";
}
}
print "</tr>\n";
}
print "</table>\n";
 
M

Mumia W.

Mumia said:
I got it to work. Initially, I was confused about how many sub-objects
have to be dereferenced.

Here's my program:
[...]

Which is wrong because it doesn't deal with empty cells correctly.
Oh well, at least Spreadsheet::parseExcel works well, and that's what's
important.
 
L

l v

Abigail said:
l v ([email protected]) wrote on MMMMDCXXXVII September MCMXCIII in [snip]

[]
[] # uname -M
[] IBM,9117-570
[] # oslevel -r
[] 5200-05
[] # umask
[] 027
[] # ls >test.txt
[] # ls -l test.txt
[] -rw-r----- 1 veatcla sapsys 90 May 11 20:30 test.txt
[]
[] # chmod 755 test.txt
[] # ls -l test.txt
[] -rwxr-xr-x 1 veatcla sapsys 90 May 11 20:30 test.txt
[] # cp test.txt test2.txt
[] # ls -l test2.txt
[] -rwxr-x--- 1 veatcla sapsys 90 May 11 20:32 test2.txt
[]
[] # chmod 775 test.txt
[] # ls -l test.txt
[] -rwxrwxr-x 1 veatcla sapsys 90 May 11 20:30 test.txt
[] # cp test.txt test3.txt
[] # ls -l test3.txt
[] -rwxr-x--- 1 veatcla sapsys 90 May 11 20:35 test3.txt
[]


So, it's *NOT* losing the execute bits. The fact that it loses the
permissions of 'other' has all to do with your umask - it's set to
027, meaning that cp remove the write bit of group writeable files,
and all the permission bits for others.

Copy the 'test.txt' with File::Copy, using the same umask, and you'll
see that the copied file will end up with '-rw-r-----' as permissions.


Your cp is POSIX compliant, and not misbehaving.



Abigail

I think we are loosing site of my point. From my viewpoint -
File::Copy, my cp, and Win32::CopyFile behave the same with regards to
copying permissions. It's irrelevant to me that it kept the execute bit
and lost 'other'. Since permissions were not copied 100% all 3 methods
can be considered broke. All 3 copy methods require *me* to think about
security and act accordingly, therefore I would not have ever considered
that File::Copy /should/ have copied permissions.

This is what prompted me to send my initial post -- a different opinion.
 
B

Ben Morrow

Quoth (e-mail address removed):
Here's a program that shows the brokenness of File::Copy::copy (move
suffers from the same problem if it needs to do a copy & delete action).
Running the program gives:

Expected permissions 755, got permissions 644

Wow... OK, I've just seriously revised my opinion of that module...

Thanks

Ben
 
P

Peter J. Holzer

Abigail said:
copy the execution bits. In fact, File::Copy::copy completely ignores
the permission bits of the to be copied file.

Right. That's also what the pod says:

| The File::Copy module provides two basic functions, "copy" and
"move",
| which are useful for getting the contents of a file from one
place to
| another.
[...]
| For
| Unix systems, this is equivalent to the simple "copy" routine,
which
| doesn’t preserve OS-specific attributes.

Permission bits are not part of the contents of the file, and they look
pretty OS-specific to me.
That's not equivalent to 'cp' (which it claims to be).

Where exactly does it claim that?

hp
 
P

Peter J. Holzer

Abigail said:
copy the execution bits. In fact, File::Copy::copy completely ignores
the permission bits of the to be copied file.

Right. That's also what the pod says:

| The File::Copy module provides two basic functions, "copy" and "move",
| which are useful for getting the contents of a file from one place to
| another.
[...]
| For
| Unix systems, this is equivalent to the simple "copy" routine, which
| doesn't preserve OS-specific attributes.

Permission bits are not part of the contents of the file, and they look
pretty OS-specific to me.
That's not equivalent to 'cp' (which it claims to be).

Where exactly does it claim that?

hp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,769
Messages
2,569,579
Members
45,053
Latest member
BrodieSola

Latest Threads

Top