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: > > > [EMAIL PROTECTED] > > > Mailing List: > > > http://jakarta.apache.org/site/mail2.html#poi > > > The Apache Jakarta Poi Project: > > > http://jakarta.apache.org/poi/ > > > > > > > > > > > > > > > > > > __________________________________ > > Yahoo! Mail - PC Magazine Editors' Choice 2005 > > http://mail.yahoo.com > > > > > --------------------------------------------------------------------- > > 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/ > > > > > > > --------------------------------------------------------------------- > > 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/ > > > > > > > > > > __________________________________ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.com > > --------------------------------------------------------------------- > 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/ > > > --------------------------------------------------------------------- > 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/ > > __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com --------------------------------------------------------------------- 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/
