WARNING: Spreadsheet::WriteExcel and Office Service Pack 3

[jmcnamara]

Q: Why do I get the following error when I open my
Spreadsheet::WriteExcel files in Excel with Office Service Pack 3:

"File Error: data may have been lost".

A: Office Service Pack 3 changes the default behaviour of Excel when
dealing with multiple data in a cell.

Discussion
==========

After SP3 if Excel encounters multiple data in a cell it will issue a
warning when the file is opened and it will display the first data
that was written. Prior to SP3 it didn't issue a warning and displayed
the last data written.

Consider the following program where two strings are written to cell
A1:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('error.xls');
my $worksheet = $workbook->add_worksheet();

$worksheet->write('A1', 'Hello' );
$worksheet->write('A1', 'Goodbye');

__END__

If you open the output file in Excel SP3 you will get the following
error warning and the string 'Hello' will be displayed in cell A1:

"File Error: data may have been lost".


Workaround 1
===========

The main workaround for this issue is not to write duplicate data to
cells. This may seem a little restrictive but there isn't any valid
reason to write duplicate data to a cell and clearly Excel doesn't
want you to do it.


Workaround 2
===========

As an alternative workaround you can use Spreadsheet::WriteExcel's
compatibility_mode() which generates an Excel file with greater
fidelity at the cost of an additional speed and memory overhead. A
side effect of this is that duplicate cell data is removed
automatically.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('no_error.xls');
$workbook->compatibility_mode();

my $worksheet = $workbook->add_worksheet();

$worksheet->write('A1', 'Hello' );
$worksheet->write('A1', 'Goodbye');

__END__

If you open the output file in Excel SP3 you will not get a warning
and the string 'Goodbye' will be displayed in cell A1.

Please, read the documentation on compatibility_mode() before you use
this feature:

http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#compatibility_mode()


John.
--

[jmcnamara]

Here is a simple utility program to help locate duplicate cell data in
a Spreadsheet::WriteExcel file.


#!/usr/bin/perl -w

#############################################################################
#
# find_duplicate_cells.pl.
#
# Simple Perl utility to find cells with duplicate data in an Excel
file.
#
# reverse('©'), May 2008, John McNamara, jmcn...@cpan.org
#

use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel::Utility 'xl_rowcol_to_cell';

my %cell_data;
my $duplicate_found;

main();

#############################################################################
#
# Parse and Excel file and look for duplicates cell data.
#
sub main {

# Check for an input file and print a simple usage.
my $file_name = $ARGV[0];

if ( !$file_name ) {
die "Usage: perl $0 file.xls\n";
}

# Create the ParseExcel parser with a different cell handler.
my $parse_excel = Spreadsheet::ParseExcel->new(
CellHandler => \&cell_handler,
NotSetCell => 1
);

# Parse the Excel file.
my $workbook = $parse_excel->Parse($file_name);

# Print a message if the file didn't contain duplicates.
print "No duplicates cells found.\n" if !$duplicate_found;
}

#############################################################################
#
# Callback function to replace ParseExcel's default cell handler.
#
sub cell_handler {

my $workbook = $_[0];
my $sheet_index = $_[1];
my $row = $_[2];
my $col = $_[3];
my $cell = $_[4];

# Convert the row and col to A1 notation.
my $cell_index = xl_rowcol_to_cell( $row, $col );

# Get the worksheet name.
my $sheet_name = $workbook->{Worksheet}->[$sheet_index]->{Name};

# Check if data already exists for this cell.
if ( exists $cell_data{$sheet_name}->{$row}
&& exists $cell_data{$sheet_name}->{$row}->{$col} )
{

# Print a header. Once only.
print "Duplicates cells found:\n" if !$duplicate_found++;

# Check which data has been overwritten.
my $existing_data = $cell_data{$sheet_name}->{$row}->{$col};

# Let the user know which cell data is duplicated.
printf "'%s'!%s = %s\n", $sheet_name, $cell_index,
$existing_data;
printf "'%s'!%s = %s\n", $sheet_name, $cell_index, $cell-
>Value();

}
else {
# Keep track of cells that have already been seen.
$cell_data{$sheet_name}->{$row}->{$col} = $cell->Value();

}
}

__END__

[Chris Hamel]

Great explanation... I had no idea why we encountered these errors on
some spreadsheets but not others.

For what it's worth, whenever I switch from WriteExcel to
WriteExcel::Big, the problem appears to go away. It doesn't appear to
have any performance impact that I'm aware of.

Thanks,
Chris

[jmcnamara]

On Jun 2, 5:47 pm, Chris Hamel <hame...@hotmail.com> wrote:
> For what it's worth, whenever I switch from WriteExcel to
> WriteExcel::Big, the problem appears to go away.

Hi Chris,

That's very interesting. I hadn't noticed that. That gives me a clue
in the direction of fixing that warning.

Thanks.

John.
--

[jmcnamara]

On Jun 2, 5:47 pm, Chris Hamel <hame...@hotmail.com> wrote:

> For what it's worth, whenever I switch from WriteExcel to
> WriteExcel::Big, the problem appears to go away.

Hi Chris,

Unfortunately, that is due to an error on my part. Compatibility mode
is (erroneously) on by default in ::Big. Therefore, you are actually
getting the second workaround that I referred to above.

Still, your observation helped me track down a bug of sorts. :-)

John.
--

[Chrisy]

On May 22, 4:59 am, jmcnamara <jmcnam...@cpan.org> wrote:
> The main workaround for this issue is not to write duplicate data to
> cells

What if my intention is to overwrite/replace a value that has already
been written? I don't want a duplicate.

This is exactly what happened to me recently, though quite
inadvertently. I've since fixed it, but a mechanism to overwrite, or
delete, the contents of a cell seems to be lacking.

Chris.

[jmcnamara]

Hi Chris,

Spreadsheet::WriteExcel takes the approach of writing data to a file
as soon as possible. While this limits the user's ability to
manipulate the data afterwards it has significant advantages in that
it reduces the memory requirement and speeds up the overall creation
of an Excel file.

In addition Perl provides a variety of tools to overwrite, replace and
delete data such as hashes, tied hashes or database interfaces. So
these types of problems are best solved in the Perl domain and not in
Spreadsheet::WriteExcel.

Having said that, the compatibility_mode() option detailed above
actually gives you the overwrite/replace option and at some later
stage it might also be possible to delete data. However, that
flexibility comes with a performance cost which for very large files
can be significant.

John.
--

[ali_pedhi]

hey,
I am trying to overwrite some data in some cells in an existing
spreadsheet.
am able to make a copy of the sheet using SaveAs() n stuff...
at first I could not write into any cell which had some data & would
get a 'File Error- Data may be lost' on opening the sheet.
on trying to include 'compatibility_mode()' I get a message saying
'Cant Locate object compatibility_mode() via package
Spreadsheet::WriteExcel'
I am using perl 5.8.8.

any idea/help will be appreciated...

ali

[jmcnamara]

On Jun 24, 2:39 pm, ali_pedhi <aliasgar.pedhiw...@gmail.com> wrote:

> on trying to include 'compatibility_mode()' I get a message saying
> 'Cant Locate object compatibility_mode() via package
> Spreadsheet::WriteExcel'
> I am using perl 5.8.8.

Hi,

Compatibility mode is only supported in the latest version, 2.21, of
Spreadsheet::WriteExcel.

The error message above means that you don't have that version
installed.

John.
--

[Ashwin Kumar]

Hi John

I am also getting the same error  "File Error: data may have been lost". I tried adding the 'compatibility_mode()' did not help. Ran your script above to find out any duplicates, it gave me "No duplicates cells found."

Spreadsheet::WriteExcel VERSION = '2.39'

What else could be the reason for this error?

Could you please help here. 

[GainfulShrimp]

It's very difficult to help if you don't post some code as a clue... please try reducing your code to the bare bones which still produces this error and then post it here, so we can try to help.

Alternatively, check every loop to make sure there's no chance that data gets written twice to the same cell.

Cheers,

Matt

[Vinay Jain]

Hi I am tring to run below code from http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.37/lib/Spreadsheet/WriteExcel.pm#MODIFYING_AND_REWRITING_EXCEL_FILES

but it giving me below error?!

"Can't call method "AddCell" on unblessed reference at test.pl line 21."

Please help!