SPUG: / Spreadsheet::WriteExcel / any experience? /

[Jim Ludwig]

Hey again:

I've gotten a few responses encouraging me to post
my Spreadsheet::WriteExcel problem, so here it is.

--------------
What I'm doing
--------------

I'm reading an Excel spreadsheet with hundreds of
rows (let's just say 1000 rows) with
Spreadsheet::ParseExcel.  In each of those rows
are 6 columns of data which I extract.

I feed the 6 items of data into a black box, and
out pops an additional 18 items of data which are
related to the original 6.

At this point I write out to a different
spreadsheet with Spreadsheet::WriteExcel -- first
I write back out the original 6 columns of data,
and then I write out the other 18 columns of data
in the same row.

I do this for each row encountered in the original
spreadsheet from which I'm reading.

FYI, every cell I write out has fewer than 128
characters of data (far less than the 255 limit).

---------------------------------------
First problem (Spreadsheet::WriteExcel)
---------------------------------------

This is where it gets weird, as I'm unable to
determine where Spreadsheet::WriteExcel is going
wrong.

gnumeric:

After one spreadsheet was done being processed, I
tried to open it up using gnumeric, and this was
the error message I got:

    Inconsistent block allocation table

Excel:

I tried opening the same spreadsheet with Excel,
in a Windows environment, and this was the error
message I got:

    'foo.xls' cannot be accessed.  The file may be
    read-only, or you may be trying to access a
    read-only location.  Or, the server the
    document is stored on may not be responding

---------------------------------------------
Second problem (Spreadsheet::WriteExcel::Big)
---------------------------------------------

Given the problems I was having with
Spreadsheet::WriteExcel, I thought it couldn't
hurt to try Spreadsheet::WriteExcel::Big.

I re-ran my script, and this time the error
messages were a little different.

gnumeric:

When I opened my resulting file on the command
line (gnumeric foo.xls), I got this (line wraps
are mine):

    foo.xls
    Excel 95

    (gnumeric:24066): gnumeric:read-WARNING **:
    XL: Xf index 0xF00 is not in the
    range[0..0x1E)

    ** (gnumeric:24066): CRITICAL **: file
    ../../src/sheet-style.c: line 901
    (cell_tile_apply_pos): assertion `col <
    SHEET_MAX_COLS' failed

    ** (gnumeric:24066): CRITICAL **: file
    ../../src/sheet.c: line 2407 (sheet_cell_new):
    assertion `col < SHEET_MAX_COLS' failed

    ** (gnumeric:24066): CRITICAL **: file
    ../../src/cell.c: line 273 (cell_set_value):
    assertion `cell != NULL' failed

    ** (gnumeric:24066): CRITICAL **: file
    ../../../plugins/excel/ms-biff.c: line 316
    (ms_biff_query_next): assertion `q->length <
    20000' failed
    Error, hit end without EOF

    ** (gnumeric:24066): CRITICAL **: file
    ../../../plugins/excel/ms-biff.c: line 316
    (ms_biff_query_next): assertion `q->length <
    20000' failed

Interestingly, it only displayed the first 129
rows of output, and on the 129th row, all columns
of data were missing from the 8th column on.  Rows
130 through 1000 were not apparent at all
(although when viewed through 'hexdump -C' or
'strings', all the rest of the data could be
seen).

Then the interesting part.  When I quit gnumeric
through the file menu, I saw these additional
output messages:

    ** (gnumeric:24066): WARNING **: Leaked 1
    nodes from value string pool.
    Leaking string [WOTNOH88] with ref_count=1.

    ** (gnumeric:24066): WARNING **: Leaked 1
    nodes from string pool.

The string which was leaked ("WOTNOH88") was the
one which was in the 8th column of the 129th row.

!!??

What's going on there?

Excel:

When I tried to open the same file using Excel, I
got a different error message from the one I'd
encountered previously:

    EXCEL.exe has generated errors and will be
    closed by Windows.  You will need to restart
    the program.  An error log is being created.

-----------
What gives?
-----------

What I'm trying to figure out is why/how
Spreadsheet::WriteExcel is writing a cell which
later gets "leaked".  I do not know anything about
the internals of Excel to know where to start, and
trudging through the source of
Spreadsheet::WriteExcel and all the modules it
depends upon doesn't sound that appealing.

I've tried this with different spreadsheets, and
it always seems to happen in various columns on
the 129th (1-based) row (when it happens at all).

Even when I don't write to the 129th row at all,
skipping on ahead straight to row 130, the same
phenomenon occurs.

So, more accurately, this happens on the first row
after the 128th (1-based) row (when it happens at
all).

The only 2 methods I'm using to write to cells are
write_blank() and write_string().

Has anyone encountered this before?  If so, did
you come across a solution?

  jim