poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Luyt" <tl...@aedian.com>
Subject Re: Possible bug writing to "new" sheets
Date Fri, 20 Jun 2003 13:04:45 GMT

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: <Clifton.Craig@rrd.com>
To: "POI Users List" <poi-user@jakarta.apache.org>
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" <poi-user@jakarta.apache.org>
>                       <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: Clifton.Craig@rrd.com [mailto:Clifton.Craig@rrd.com]
> 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 -----
>
>
>                       Clifton.Craig@rrd
>
>                       .com                     To:       "POI Users
> List" <poi-user@jakarta.apache.org>
>                                                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"
>
> <poi-user@jakarta.apache.org>
>                       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.
>
>
>
>                       Clifton.Craig@rrd
>                       .com                     To:       "POI Users
> List"
>                       <poi-user@jakarta.apache.org>
>                                                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"
>                       <stephen@Basit.CO        To:
>                       <poi-user@jakarta.apache.org>
>                       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: <Clifton.Craig@rrd.com>
> To: "POI Users List" <poi-user@jakarta.apache.org>
> 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
> >
> >
> >
> >                       Clifton.Craig@rrd
> >                       .com                     To:       "POI Users
> List"
> <poi-user@jakarta.apache.org>
> >                                                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"
> >                       <stephen@Basit.CO        To:       "POI Users
> List"
> >                       <poi-user@jakarta.apache.org>
> >                       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: <Clifton.Craig@rrd.com>
> > To: <poi-user@jakarta.apache.org>
> > 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: poi-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: poi-user-help@jakarta.apache.org
> > >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: poi-user-help@jakarta.apache.org
> >
> >
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: poi-user-help@jakarta.apache.org
> >
> >
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: poi-user-help@jakarta.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>


Mime
View raw message