Re: Possible bug writing to "new" sheets

Danny Mui Fri, 20 Jun 2003 07:58:26 -0700

To change the sheet order:

http://jakarta.apache.org/poi/apidocs/index.html

See HSSFWorkbook.setSheetOrder

To see if this is a bug that's been reported:

http://nagoya.apache.org/bugzilla/buglist.cgi?product=POI

If it's not there, feel free to submit it (with your test case you provided for the list and your basic code) and vote for it.

Any help is appreciated :)

danny

[EMAIL PROTECTED] wrote:

Thank you Thomas,

This is the conclusion I have come to. However it presents a dilemma. There
is no way to re-create a "new" sheet in POI. What I mean is I see no way to
obtain a writable reference to unused sheets. If I create a new sheet then
it is appended to the end of the collection of existing sheets. If I create
a new sheet with POI using the same name as an existing sheet then it
creates a duplicate and the original stays in place while the new one shows
at the end. So my work-around is to delete the "new" sheet and re-create
it. While this works it still leaves the sheet at the end of the
collection, out of sequence and it feels a little goofy. Is there a way to
change the sequence of sheets in POI? Also, shouldn't this be listed as a
bug for the development team to work out? By bug I am referring to the
inability to obtain a writeable reference to unused sheets. Has this
problem been reported before?

-Cliff

"Thomas Luyt" <[EMAIL PROTECTED] To: "POI Users List" <[EMAIL PROTECTED]> > cc: Subject: Re: Possible bug writing to "new" sheets 06/20/2003 09:04 AM Please respond to "POI Users List"

I tried your example on Win98 / Excel 97 / POI 1.10 dev.
It did not work.
However, my guess is that sheets work just like rows or cells. You can see
it with Excel but it does not really "exists" for HSSF.
So you have to create it if nothing is written in it.
I tried to change the template file, populating one cell in the second
sheet.
And surprise, it worked !
So I would say that if no cell exists (ie sheet.getPhysicalNumberOfRows()
= 0), you have to create a sheet from scratch, then write
in it.

Below is the code I used, derived from yours. On the workbook I used on
testing purposes, the first and second sheet both have one
cell defined. The third sheet ("Feuil3" in my example) is empty. As
expected, sheet.getPhysicalNumberOfRows() returns 0 in this
case.

Thomas

=======================================================
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
* @author craic0ld
* Created Jun 17, 2003
*
* Purpose:
*
*/
public class ExcelTest {

public static void main(String[] args) {
 try {
  String xlFile = "C:\\Template2.xls";
  POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(xlFile));
  HSSFWorkbook wkbk = new HSSFWorkbook(fs);
  System.out.println("Nombre de feuilles : " + wkbk.getNumberOfSheets());
  HSSFSheet sheet = null;
  HSSFRow row = null;
  HSSFCell cell = null;
  System.out.println("******Feuille 1 **************");
  sheet = wkbk.getSheet("Feuil1");
  System.out.println("derni�re ligne : " + sheet.getLastRowNum());
  System.out.println("nombre de lignes : " + sheet.getPhysicalNumberOfRows
  ());

System.out.println("******Feuille 2 **************");

  sheet = wkbk.getSheetAt(wkbk.getSheetIndex("Feuil2"));
  System.out.println("derni�re ligne : " + sheet.getLastRowNum());
  System.out.println("nombre de lignes : " + sheet.getPhysicalNumberOfRows
  ());

  row = sheet.getRow(0);
  if(row == null) {
   row = sheet.createRow(0);
  }
  cell = row.getCell((short) 1);
  if(cell == null) {
   cell = row.createCell((short) 1);
  }
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);
  cell.setCellValue("Hello World!");

  System.out.println("******Feuille 3 **************");
  sheet = wkbk.getSheet("Feuil3");
  System.out.println("derni�re ligne : " + sheet.getLastRowNum());
  System.out.println("nombre de lignes : " + sheet.getPhysicalNumberOfRows
  ());

  FileOutputStream fo = new FileOutputStream("C:\\Results.xls");
  wkbk.write(fo);
  fo.close();
  System.out.println("Done!");
 } catch (Exception e) {
  System.out.println(e.getMessage());
  e.printStackTrace();
 }
}
}


----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "POI Users List" <[EMAIL PROTECTED]>
Sent: Friday, June 20, 2003 1:45 PM
Subject: RE: Possible bug writing to "new" sheets

No that's not the problem. In version 1.8.0 the createRow() method from

the

Worksheet object takes an int instead of a short. I believe in older versions it took a short. I remember updating this across my app when I upgraded. Anyway it wouldn't run much less compile if that were the case. Has anybody got my example running? If so, what are your conclusions?

This

is really looking like a POI short coming to me. Am I mistaken?

-Cliff

"Kendall Coolidge" To: "POI Users List"

<[EMAIL PROTECTED]>

<Kendall.Coolidge cc: @ccbai.com> Subject: RE: Possible bug

writing to "new" sheets

                     06/19/2003 06:00
                     PM
                     Please respond to
                     "POI Users List"
I think I see the problem. In your example, change the following line:

HSSFRow row = sheet.createRow((short)1);

You weren't casting the "1" to a short.

Kendall
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 2:41 PM
To: POI Users List
Subject: Possible bug writing to "new" sheets
I apologize. for sending this example out so many times. All the
messages
from the past two days just came through my mail client here confirming
that they were going out the whole time. (I have Notes R5 and sometimes
when I open multiple windows to my in-box the last one doesn't refresh
properly.) Anyway, due to the responses I was getting I got concerned
and
sent multiple emails with the same info. My apologies to all (and I'm
including it again here!) I am still struggling with the issue, however.
Has anyone figured it out? I don't know where else to turn outside of
reading through the source code myself.
Stranded,
-Cliff
----- Forwarded by Clifton C Craig/US/DNY on 06/19/2003 02:36 PM -----
[EMAIL PROTECTED]
                     .com                     To:       "POI Users
List" <[EMAIL PROTECTED]>
                                              cc:
                     06/18/2003 10:29         Subject:  Possible bug
writing to "new" sheets
                     AM
Please respond to

"POI Users List"
Are my emails getting through? I apologize if I sent this email already
it's just that my email in the office has been acting funny lately. I
normally get a copy of each one I send out but the last two have never
come
back to me. Anyway, This is the message I sent yesterday outlining a
simple
example. I just attempted several iterations going through the POI
source
in debug and looking for inconsistencies. This may have nothing to do
with
it but when the workbook serializes the individual sheets report
different
sizes confirming the loss of data. If I write "Hello World!" to Sheet1
then
the sizes are 335, 263, and 263 for sheets 1-3 respectively. If I write
"Hello World!" to Sheet2 then the sizes are 301, 263, and 263
respectively.
Is there no way to write to a "new" sheet in POI? Must all sheets be
either
used or created from scratch?
-Cliff
----- Forwarded by Clifton C Craig/US/DNY on 06/18/2003 10:22 AM -----
                     Clifton C Craig
                                              To:      "POI Users List"
<[EMAIL PROTECTED]>
                     06/17/2003 04:20         cc:
                     PM                       Subject: Re: Cannot write
to
                     extra sheets(Document link: Clifton C Craig)
An example...
I create a new excel workbook named Template2.xls in my root (C) drive
using Excel 2000 on Win2K.
In Sheet1 I change the value of cell A:1 to "My Template".
I save this to my C drive.
I run the following program and the Sheet2 in Results.xls is not
updated.
What gives?
package com.craig.JAS;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* @author craic0ld
* Created Jun 17, 2003
*
* Purpose:
*
*/
public class ExcelTest
{
     public static void main(String[] args)
     {
           try
           {
                 String xlFile = "C:\\Template2.xls";
                 POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(xlFile));
                 HSSFWorkbook wkbk = new HSSFWorkbook(fs);
                 HSSFSheet sheet = wkbk.getSheet("Sheet2");
                 HSSFRow row = sheet.createRow(1);
                 HSSFCell cell = row.createCell((short)1);
                 cell.setCellValue("Hello World!");
                 FileOutputStream fo = new FileOutputStream("C:
\\Results.xls");
                 wkbk.write(fo);
                 fo.close();
                 System.out.println("Done!");
           } catch (Exception e)
           {
                 System.out.println(e.getMessage());
                 e.printStackTrace();
           }
     }
}
If I change it to write to Sheet1 then it works fine.
                     [EMAIL PROTECTED]
                     .com                     To:       "POI Users
List"
                     <[EMAIL PROTECTED]>
                                              cc:
                     06/17/2003 03:03         Subject:  Re: Cannot
write
                     to extra sheets
                     PM
                     Please respond to
                     "POI Users List"
Steve,
That's just the problem. With a "new" sheet I can't write to it. I mean,
I
can write but the data is lost when it is serialized. What I'm saying is
the sheets that Excel creates brand new (When you Insert Worksheet or
create a new Workbook) cannot be written to unless they've been "used".
Cliff
                     "Steve"
                     <[EMAIL PROTECTED]        To:
                     <[EMAIL PROTECTED]>
                     M>                       cc:
                                              Subject:  Re: Cannot
write
                     to extra sheets
                     06/17/2003 02:05
                     PM
                     Please respond to
                     "POI Users List"
if the sheet exists, you don't want to create it.
you want to use
wb is the Workbook object
sheetnumber is the int sheetnumber you want
String sheetname = wb.getSheetName(sheetnumber);
HSSFSheet sheet = wb.getSheet(sheetname);
now that you have the sheet, you can operate on it.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "POI Users List" <[EMAIL PROTECTED]>
Sent: Tuesday, June 17, 2003 10:46 AM
Subject: Re: Cannot write to extra sheets
Does anybody have any clue as to how I can handle this scenario? Is

there

even a quick and easy way to test for a "new" or "empty" spreadsheet

so

that I can at least have my code delete the sheet prior to re-creating

it?

Is the a way to shuffle or re-organize sheets in a workbook? I'm stuck

on
this. Please help.
Thanks in advance,
-Cliff
[EMAIL PROTECTED] .com To: "POI Users
List" <[EMAIL PROTECTED]>

cc: 06/16/2003 11:55 Subject: Re: Cannot

write to extra sheets
                     AM
                     Please respond to
                     "POI Users List"
I don't think creating the sheet is appropriate in this scenario. I
have 3

sheets in a brand new workbook. The 1st sheet has some data on it and

I

need to write to the 2nd sheet leaving the 3rd sheet blank. If I do a createSheet("Sheet2") I end up with two Sheet2's where the one I

create in

POI is at the end. There is no method createSheetAt(int idx) available

to

create a sheet over an existing sheet. Now if there was some easy way

to

test for this condition then maybe I could delete/rewrite the sheet in question but it would be a good solution. I'd have problems getting

the new

sheet in the correct order. If you consider a workbook with these

"new"

sheets intermingled and sporadically placed throughout other used

sheets

then it becomes a real nightmare. What do I do?

-Cliff

"Steve" <[EMAIL PROTECTED] To: "POI Users

List"

<[EMAIL PROTECTED]> M> cc: Subject: Re: Cannot

write
                     to extra sheets
                     06/16/2003 10:34
                     AM
                     Please respond to
                     "POI Users List"
it doesn't create extra sheets. only the space for them.
you need to create all sheets that you want,
as far as I know.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 16, 2003 8:59 AM
Subject: Cannot write to extra sheets
Hello all,

I am having a problem writing to the extra sheets that Excel creates
by

default when you create a new workbook. I'm using the jakarta-poi1.8.0-dev-20020919.jar version. I've developed a

conversion

program that allows data to be merged with existing spreadsheets.

The

merge

works when I attempt to write on a sheet that has already been

written

on.

However, whenever I attempt to write to one of the blank sheets

(sheets 2

and 3) that Excel creates automatically with an empty workbook it

doesn't

work. I can adjust the width of cells in these sheets but they do

not

retain any info from the cell.setCellValue() method calls. I

verified in

debug that these methods are not failing. My debugger (Eclipse)

allows me

to see the cell values before and after the method calls. I've

attempted

many tests where I enter the exact same parameters to merge data to

an

existing workbook changing only the sheet number. The data is

retained

only

on the 1st sheet where I've already added data. This is the only

variance

in my test runs. In all cases I obtain references to the sheet, row

and

cell objects the same way. I do a Workbook.getSheetAt(int num) to

get the

existing sheet. (I've tried with Workbook.getSheet(String name) as

well.)

I

am using Worksheet.createRow(short row) to create the row and Row.createCell(short cell) to create cell references. In my code I

verify

that the rows and cells do not exist prior to creating. In the

latter
case
I use the corresponding methods to obtain references to the existing
objects. What am I missing?
-Cliff

> > >

---------------------------------------------------------------------

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Possible bug writing to "new" sheets

Reply via email to