adding and removing rows in HSSFSheet

[Andrea Nenni]

Hi,

I just generated two Excel files. The first one is a simple one with
one column and four rows.

For the second file, I added two lines of code to add a row and remove
it again immediately:

HSSFSheet sheet;
//...
HSSFRow row = sheet.createRow(4);
sheet.removeRow(row);

The resulting files differ (Unix "diff" says "binary files differ").
Why? Is that a bug?

In case you wonder why I'm doing this nonsense... I'm trying to import
the generated Excel files into Microsoft Project (using a self-defined
import/export map). My problem is that files with an even number of
rows won't be imported correctly. The last row is missing in MS Project.

After opening and saving the file in MS Excel, it works fine. (This
works with OpenOffice, too). I'm not sure if this is a POI problem or
an MS Project problem.

Thanks.

Andrea

[Andrea Nenni]

Hello everybody,

I still could not find a solution for my MS Project import problem.

Perhaps I shoud give you some more detailed information:

I create Excel sheets with POI and open them with Microsoft Project.
Each row in my sheet corresponds to a "task" in MS Project. (You can
define special mappings to and from MS Excel in MS Project.)

Everything works fine, when the number of rows in the created Excel
file is odd. However, when the row number is even, MS Project will just
ignore the last row, i.e. one "task" is missing. There seems to be some
difference in the file ending between HSSFSheets with odd and even row
numbers. Im still not sure, if this is a bug or just part of Microsoft's
definition of the MS Excel format.

Next, I tried to cheat MS Project, by using an additional empty row at
the end of the sheet. Consequentially, MS Project shows an empty "task"
at the bottom.

What I found somehow amazing: When I remove the additional row immediately
after adding it in POI, MS Project still shows the additional "task".

Has anybody an idea how to work around this problem?
Please help me.

Andrea

[Avik Sengupta]

You'll probably need to test this with a hand crafted excel file, to
find out if this is POI issue or an MS project issue.

Post by Andrea Nenni
Hello everybody,
I still could not find a solution for my MS Project import problem.
I create Excel sheets with POI and open them with Microsoft Project.
Each row in my sheet corresponds to a "task" in MS Project. (You can
define special mappings to and from MS Excel in MS Project.)
Everything works fine, when the number of rows in the created Excel
file is odd. However, when the row number is even, MS Project will just
ignore the last row, i.e. one "task" is missing. There seems to be some
difference in the file ending between HSSFSheets with odd and even row
numbers. Im still not sure, if this is a bug or just part of Microsoft's
definition of the MS Excel format.
Next, I tried to cheat MS Project, by using an additional empty row at
the end of the sheet. Consequentially, MS Project shows an empty "task"
at the bottom.
What I found somehow amazing: When I remove the additional row immediately
after adding it in POI, MS Project still shows the additional "task".
Has anybody an idea how to work around this problem?
Please help me.
Andrea
---------------------------------------------------------------------

[Andrea Nenni]

Hello,

I dumped some of my Excel files with the org.apache.poi.hssf.dev.BiffViewer:

- "poi.dump" (from the Excel sheet created by HSSF)
- "excel.dump" ("poi.dump" opened and saved with MS Excel 2002)
- "openo.dump" ("poi.dump" opened and saved with Open Office 1.1.0)


This is what I see, when I compare the files with UNIX diff (-u):

--- poi.dump 2004-01-26 13:21:14.000000000 +0100
+++ excel.dump 2004-01-26 13:49:14.000000000 +0100

<snip>

recordid = 0x200, size =14
[DIMENSIONS]
.firstrow = 0
- .lastrow = 3
+ .lastrow = 4
.firstcol = 0
.lastcol = 1
.zero = 0
[/DIMENSIONS]

============================================

I get the same result when comparing "poi.dump" to "openo.dump".

My generated sheet has 4 rows. HSSF writes a "lastrow" entry of 3.
MS Excel and Open Office both change the "lastrow" entry to 4 which
makes them readable for MS Project.

I also had a look into the BiffViewer dump of a POI generated Excel
sheet with 5 rows. It has a "lastrow" entry of 5 (!).

This looks like a bug to me which might cause the problems in opening
the file with MS Project.

Any ideas or comments?

Can anyone tell me, if there is an easy way to change the "lastrow" entry
in an Excel file? Is there a way to do it with HSSF?

Regards,
Andrea

-----Original Message-----
Sent: Saturday, January 24, 2004 11:26 AM
You'll probably need to test this with a hand crafted excel file, to
find out if this is POI issue or an MS project issue.

[Andrea Nenni]

Hello again,

if anybody happens to stumble across the same MS Project problem that
I had:
I just added what I consider the solution to the problem as bug report
and patch to the POI bugzilla database. The bug number is 26465.

Thanks for your interest.

Andrea

-----Original Message-----
Sent: Friday, January 23, 2004 9:45 AM
To: 'POI Users List'
Subject: Opening HSSFSheets in MS Project - Is it a bug?
Hello everybody,
I still could not find a solution for my MS Project import problem.
I create Excel sheets with POI and open them with Microsoft Project.
Each row in my sheet corresponds to a "task" in MS Project. (You can
define special mappings to and from MS Excel in MS Project.)
Everything works fine, when the number of rows in the created Excel
file is odd. However, when the row number is even, MS Project
will just
ignore the last row, i.e. one "task" is missing. There seems
to be some
difference in the file ending between HSSFSheets with odd and
even row
numbers. Im still not sure, if this is a bug or just part of
Microsoft's
definition of the MS Excel format.
Next, I tried to cheat MS Project, by using an additional
empty row at
the end of the sheet. Consequentially, MS Project shows an
empty "task"
at the bottom.
What I found somehow amazing: When I remove the additional
row immediately
after adding it in POI, MS Project still shows the additional "task".
Has anybody an idea how to work around this problem?
Please help me.
Andrea
---------------------------------------------------------------------