CODEWITHSUNDEEP
excelperl
user3106831
user3106831

Reputation: 31

read and write excel file in perl

I am a recent Perl user. I need some help regarding the reading and writing of files in perl.

Let me explain the scenario: For example, I have a input.xls file which contains string,numeric,drop down list. Some cells in the sheets are locked.

I want to read the data from input.xls file and want to write it in a new file file called output.xls.

The problem I am facing here is I am unable to retain the formatting of the file from which I am reading.

I.e the output file which is generated does not display the drop down as well as the the cells which are locked in input.xls file does not appear in the output.xls file.

Secondly even the formatting of the input file is disturbed in the output file. For e.g if cells are merged in input file then the formatting does not appear the same in output file. Kindly guide.

Here is my code for your reference:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::SaveParser;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
print qq[Content-type:text/html \n\n];
my $cs='Book2.xls';
 # Open the template with SaveParser
my $parser   = new Spreadsheet::ParseExcel::SaveParser;
#my $formatter=Spreadsheet::ParseExcel::FmtJapan->new();
my $template = $parser->Parse('e.xls');

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

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



# Write data to some cells
$template->AddCell(0, $row,   $col,   1,     $format);
$template->AddCell(0, $row+1, $col, "This is a hello world eg", $format);
#$format->{Lock};
# Add a new worksheet
# $template->AddWorksheet('New Data');

# The SaveParser SaveAs() method returns a reference to a
# Spreadsheet::WriteExcel object. If you wish you can then
# use this to access any of the methods that aren't
# available from the SaveParser object. If you don't need
# to do this just use SaveAs().
#
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('new.xls');

}

# Use Spreadsheet::WriteExcel methods

my $worksheet  = $workbook->sheets(0);
# $worksheet->protect();
#$worksheet->write('A1:B1','=1+2');
#my $locked  = $workbook->add_format();
# $locked->set_locked(1); # A non-op

#my $unlocked = $workbook->add_format();
#$locked->set_locked(0);

# Enable worksheet protection
#$worksheet->protect();

# This cell cannot be edited.
#$worksheet->write('A1:B1', '=1+2', $locked);

$worksheet->write($row+2, $col, "World2");

$workbook->close();
print qq[
<head>
<script> 

</script>
</head>
<body>

<p>The download should start shortly. If it doesn't, click
<a id="downloadLink" href="http://128.9.45.168/~mint/MINT_Portal/macro             /963/cgi/$cs"     download="$cs" target="_blank">here</a>.</p>
</body>
</html>

];

Upvotes: 3

Views: 8623

Answers (1)

David-SkyMesh
David-SkyMesh

Reputation: 5171

It will be extremely hard to keep the formatting of the cells (for some arbitrary spreadsheet). Spreadsheet::ParseExcel doesn't really understand that much of formatting (mostly ignores it).

You may find that you get it all working for a particular spreadsheet, but then you'll find a spreadsheet with more intricate formatting that doesn't work as well.

What you need is something that losslessly interacts with the Excel document object model (DOM). (i.e. load an existing spreadsheet, clone some rows, change the data values, drop some other rows, save out to a different file).

The only code I've found that does this reliably is Apache POI.

Unfortunately, that's a Java API. I ended up writing a bunch of plumbing code (in Java) embedded in my perl script using Inline::Java. (There are .NET APIs that can do this too, but I was on Linux, and I could never get the MONO+Wine stack to run most of the Office automation stuff.)

This approach works, but the code to pull it off is very complicated. I would discourage trying :-)

Is there some way you could avoid doing this in perl? (Or just this part?)

Upvotes: 1

Related Questions