Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Received-SPF: pass (athena.apache.org: domain of frozi2006@gmail.com
 designates 209.85.132.246 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:mime-version:content-type;
        b=waI6LDLoX1clt/hh2fdwkSAKt5UKOYS4qMLk7CsMwlcerilXvBDG6na4yWZti4vYDYBWQWzzHE9iEz2ywkYtXd5x8fA7wfg+zuYdhk0W9HJNZDAmENqg4qK/BD/7aEH7VpqsqPAzq50JDOlaqO1EUOcKKrRo5O/bFYK8xEEnNFQ=
Message-ID: <37a935c70803071114r4cfb7904oc7e91ab45a435bc7@mail.gmail.com>
Date: Fri, 7 Mar 2008 14:14:46 -0500
From: "Faraz Rozi" <frozi2006@gmail.com>
To: user@poi.apache.org
Subject: Regarding Writing to existing excel worksheet using HSSF usermodel
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_12339_7076035.1204917286331"

------=_Part_12339_7076035.1204917286331
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

 Hi folks,
I have been working on this problem for quite some time and haven't
gotten anywhere with it. The situation is quite critical and urgent.
I'm using Apache POI-HSSF usermodel. I have written a program which produces
a
JTable of certain data and then exports this into a pre-existing excel
spreadsheet based on user interaction from the GUI. My spreadsheet has
certain cells with pre-existing formulas which rely on the values of
other cells that the program is writing to. There is one column in
particular in the excel spreadsheet, with dates (preset for dates in
excel) that initiates all the calculations. In other words, once the
spreadsheet realizes a certain row has a date, it realizes that row
must be a new entry and specific cells begin to evaluate formulas.
Here is the problem: The values do show up correctly in the excel
spreadsheet, but the formulas are not calculated. When I double
click on one of the date cells (any of them), and then click on
another cell or press enter (as if I just edited the cell), the
formulas kick in and values are computed. However, not all the
formulas work and certain cells in another sheet of the workbook,
with formulas that use cells from the first worksheet, show #VALUE. I set my
dates EXACTLY the
way they do it in the POI examples. I write to the file EXACTLY the
way they do it as well. Someone had mentioned to rewrite all the cells
to their current values once the data is there (refresh the cells). I tried
this and
nothing changes.

Here is how I set a cell's date:

public void setCellDateValue(String value, int row, int col)
{
   try
   {
      HSSFRow sheetRow;
      HSSFCell cell;
      Calendar calendar;
      String tokens[] = value.replaceFirst("^s+", "").split("/");
      sheetRow = sheet.getRow(row);
      cell = sheetRow.getCell((short) col);

      if ( (tokens[2].charAt(0)) == '0' )
         tokens[2] = ("20".concat(tokens[2]));
      else
         tokens[2] += ("19".concat(tokens[2]));

      calendar = new GregorianCalendar(Integer.parseInt(tokens[2]),
Integer.parseInt(tokens[0]), Integer.parseInt(tokens[1]) - 1);

      HSSFCellStyle cellStyle = workBook.createCellStyle();
      cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

      cell.setCellValue(calendar);
      cell.setCellStyle(cellStyle);

      outputStream = new FileOutputStream("C:test.xls");

      workBook.write(outputStream);

      outputStream.close();

   }
   catch (FileNotFoundException e)
   {
      System.out.println("file not found\n");
      e.printStackTrace();
   }
   catch (IOException e)
   {
      System.out.println("IO Exception\n");
   }
}


Any ideas? Thanks so much.

------=_Part_12339_7076035.1204917286331--