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

Jim Ludwig jsl at blarg.net
Sat Aug 16 12:16:46 CDT 2003


Hey there:

Once again I'd like to thank everyone who
responded to my query.

I'd like to report back that the problem has been
solved.  I heard back from the
Spreadsheet::WriteExcel developer, and it sounds
to me like it sounded to him that he'd encountered
this one before.

He asked if the "black box" into which I was
feeding my data contained an XML parser,
suggesting that, if it was, the data I was writing
out would be in UTF8 format.

    Even if the data is in the ASCII range it may
    coerce other WriteExcel internal strings to
    UTF8. This will cause bytes with the high bit
    set (such as the 129 row number) to be
    expanded to two bytes. This will in turn
    corrupt the file and lead to an Excel error
    such as the one you are seeing.

He then suggested I read the "WORKING WITH XML"
section of the man page, which contained the
needed fix:

    my $non_utf8_value = pack( 'C*', unpack( 'U*', $value ));

Heh, this is an issue I won't soon forget :).

Thanks again, everybody.  By the way, you can
make some really pretty spreadsheets in non-CSV
format ;).

  jim

> From: Jim Ludwig <jsl at blarg.net>
> To: Seattle Perl Users Group <spug-list at pm.org>
> Date: Fri, 15 Aug 2003 15:41:52 -0700
> Subject: Re: SPUG: / Spreadsheet::WriteExcel / any experience? /
>
> 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