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

Jim Ludwig jsl at blarg.net
Fri Aug 15 17:41:52 CDT 2003


Thanks to all of you who suggested writing out to
a file which contains comma- or tab-separated
values.

Naturally, barring any real workarounds, this is
what I will have to resort to doing.

Sadly, doing so would disallow me from having all
the beautiful auto-formatting I had anticipated.

Incidentally, I did not use any formatting in the
test cases I reported on below, nor were there any
freaky characters.

THE REAL THING I was kind of getting at was, what
in the world is going on after row 128?  I think I
will write to the WriteExcel developer.  In the
mean time, I will, :(, go with CSV.

  jim

----- Original Message ----- 
From: "Jim Ludwig" <jsl at blarg.net>
To: "Seattle Perl Users Group" <spug-list at pm.org>
Sent: Friday, August 15, 2003 10:29 AM
Subject: Re: SPUG: / Spreadsheet::WriteExcel / any experience? /


> 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



More information about the spug-list mailing list