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/Spreadsh...()

John.
--

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, jmcnam...@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-

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

    }

__END__

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

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

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.
--

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

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.
--

On May 22, 4:59 am, jmcnamara <jmcnam...@cpan.org> wrote:

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.

On Jun 19, 2:09 pm, Chrisy <chr...@flirble.org> wrote:

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.
--

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

On Jun 21, 2:24 pm, jmcnamara <jmcnam...@cpan.org> wrote:

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

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.
--