Hello again Jason (look, I got your name right this time) As you requested, I put together some test code and could not replicate the error you described. I created a very simple spreadsheet and then deleted a row. As you said to me, the row is not actually deleted simply cleared but the basic formulae I also entered nto the rows were not affected. The files have been attached to this message so that you can have a look; there is an input file named before.xls and a Java source code file called DeleteTest.java. If you compile the source code and then run it, you should see a new output file created - called after.xls (imaginative???). It is a hack, try not to be too critical of the code, it contains a number of very undesirable things - catch(Exception ex) for example!! But it does demonstarte that you were correct, removeRow() does not actually remove the row!! Sorry, but I was confused because I use both HSSF and JExcelAPI, the latter does actually delete the row - removing it from the worksheet.
That does not solve your problem, but this might. When you see the REF message from Excel it indicates that - to quote Microsoft - 'a cell reference is not valid'. Sadly, there are a number of possible causes but - luckilly - there is a tool to help trace the cause. This next bit has been cut from the help files of Excel; Locate cells that cause errors in a formula 1. Display the Auditing toolbar by pointing to Auditing on the Tools menu and then clicking Show Auditing Toolbar. Before you use the Auditing toolbar, make sure Show all or Show placeholders is selected under Objects on the View tab in the Options dialog box (Tools menu). 2. Click the cell that displays the error. 3. Click Trace Error . Note To select the cell at the other end of an arrow, double-click the arrow. Good luck and I hope that helps. --- "Gordon, Jason [IT]" <[EMAIL PROTECTED]> wrote: > I am not doing any formula stuff with POI. I am > using an existing sheet with formulas as input. I > then loop through it row by row in POI. For each > row, I check the string value of the cell in column > #2 to see if it exists in a Hashtable of data I got > from somewhere else. If it does exist, I delete the > current row and move on to the next row. If it > doesnt exist, I leave the row as is. Then I save > the Excel file. Note that column #2 does NOT > contain any formulas, just strings. > > So as you can see, I am not messing with formulas in > POI and I am not doing any row shifting. The result > of this is a spreadsheet with a bunch of blank rows > (because I didnt shift up) mixed in with rows that > have data, including formulas that are now messed > up. I agree with your logic about having to change > the formulas when you shift rows, but it seems that > POI doesnt shift rows when you delete a row. Maybe > I am improperly deleting rows. Could you provide > some pseudocode for deleting looping through all the > rows of a sheet and deleting some. > > Thanks, > Jason > > -----Original Message----- > From: Anthony Andrews > [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 01, 2005 12:40 PM > To: POI Users List > Subject: RE: Deleting rows ruins formulas > > > Hello again Gordon > > Just to be clear, are you saying that after you > delete > a row, you parse through all of the formulae to > ensure > that they correctly address the remaining rows? > > If so, I cannot see why you are experiencing any > problems at all. My second though was that deleting > a > row rendered all of the references in formulae that > appreared in rows below it invalid. To look at the > example I was trying to explain - very badly and I > apologise - before, I was imagining that a cell on > row > 11 contained a formula such as; > > =SUM(A11 : B11) > > Now, if you deleted row 10, what had been row 11 > would > become 'new' row 10. Therefore, the above formula > would need to be modified to work correctly, changed > from > > =SUM(A11 : B11) > > to > > =SUM(A10 : B10) > > Other than a problem such as that, I cannot see why > POI is mucking up your formulas. Sorry. > > > --- "Gordon, Jason [IT]" > <[EMAIL PROTECTED]> > wrote: > > > POI doesnt automatically shift cells up. I do > that > > part in code after I delete the rows I want. But > > ignoring that, simply deleting the rows without > > doing the shifting also ruins the formulas. I > dont > > see why that should happen. I guess POI doesnt > > support what I need in this case. > > > > > > > > -----Original Message----- > > From: Anthony Andrews > > [mailto:[EMAIL PROTECTED] > > Sent: Thursday, December 01, 2005 11:22 AM > > To: POI Users List > > Subject: RE: Deleting rows ruins formulas > > > > > > In that case, I suspect that it is simply because > > you > > are deleting rows. > > > > Imagine that you delete row 10. In this case, what > > was > > row 11 now becomes the 'new' row ten. Any rows > below > > the old row 10 will also move up - metaphorically > > speaking, one row. > > > > In this case, any formulae that appear in cells > > below > > the old row 10 will be wrong. > > > > --- "Gordon, Jason [IT]" > > <[EMAIL PROTECTED]> > > wrote: > > > > > They aren't though. The formula cells only > > > reference cells in the same row as itself, so > > > deleting rows should be fine. The sheet is as > > > follows: > > > > > > CUSIP NAME PRICE1 PRICE2 PRICEDIFF > > > 02081T9A8 BRANDS REGS 100.25 100.25 0 > > > > > > The PRICEDIFF column is a formula: PRICE1 - > PRICE2 > > > > > > -----Original Message----- > > > From: Anthony Andrews > > > [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, December 01, 2005 11:07 AM > > > To: POI Users List > > > Subject: Re: Deleting rows ruins formulas > > > > > > > > > I am guessing that one or more of those cells in > > the > > > rows you are deleting are referenced by formulae > > and > > > that those formulae are in cells on rows that > you > > > are > > > not deleting. > > > > > > --- "Gordon, Jason [IT]" > > > <[EMAIL PROTECTED]> > > > wrote: > > > > > > > I am reading in an existing spreadsheet (with > > > > formulas) and looping through it row by row. > > Some > > > > rows I am deleting and others I am leaving > > alone. > > > > When I save the updated spreadsheet, the > > formulas > > > in > > > > the remaining rows show =#REF! All I am doing > > is > > > > reading in rows and deleting them if I find a > > > > certain cell has a certain value. The cell I > am > > > > reading does not have a formula. I am not > > writing > > > > any new data to the sheet, just deleting rows. > > > > > Additionally, Excel says: File error: data may > > > have > > > > been lost. > > > > > > > > Any ideas why this is? > > > > Here is some code: > > > > > > > > // loop through range > > > > for(int i=(this.startRow-1); > i<=(this.endRow-1); > > > > i++){ > > > > tempRow = sheet.getRow(i); > > > > if(tempRow!=null){ > > > > tempCell = tempRow.getCell(columnIndex); > > > > if(tempCell != null){ > > > > try { > > > > tempString = > tempCell.getStringCellValue(); > > > > } catch(NumberFormatException ex) { > > > > logger.error(ex); > > > > tempString = "" + > > > > (int)tempCell.getNumericCellValue(); > > > > } > > > > value = tempString.toUpperCase(); > > > > > > > > > > > > if(!bonds.containsKey(value)){ > > > > // Bond was found, delete row > > > > sheet.removeRow(tempRow); > > > > > > > > } > > > > } > > > > } > > > > } > > > > > > > > > > > > > > --------------------------------------------------------------------- > > > > To unsubscribe, e-mail: > === message truncated === __________________________________ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs
before.xls
Description: 691221969-before.xls
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
