5

I have a simple excel sheet with basic excel formulas, such as sums, and value retrieval from another cell. Now, I want to essentially insert a blank line in between two existing lines (I've taken a look at How to insert a row between two rows in an existing excel with HSSF (Apache POI), but I'm running into some strange errors).

So I tried shifting rows by doing this:

worksheet.shiftRows(15,16,2);

and I'm getting this in return:

Exception in thread "main" java.lang.RuntimeException: not implemented yet
    at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:127)
    at org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:615)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:462)
    at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
    at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
    at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
    at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
    at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
    at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
    at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
    at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
    at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
    at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
    at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateNamedRanges(XSSFRowShifter.java:116)
    at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2363)
    at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2306)
    at com.shel.myProgram.workbook.copyAndInsert(workbook.java:120)
    at com.shel.myProgram.workbook.test(workbook.java:111)
    at com.shel.myProgram.driver.main(driver.java:24)

EDIT:

I'm using this dependency:

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
Community
  • 1
  • 1
Stupid.Fat.Cat
  • 10,755
  • 23
  • 83
  • 144
  • Take a look at **org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook** line 127. May be in your library this function is "not implemented yet" – Thrash Bean Feb 07 '14 at 18:47
  • @ThrashBean I'm getting 127 is not a valid number in org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook ... I'm so confused. In fact the file appears to be empty. I'm using apache poi version 3.9 – Stupid.Fat.Cat Feb 07 '14 at 18:57
  • Yes, it's not implemented. Neither is in 3.10-beta2, nor in 3.11-beta1 (the latest nightly build). So you have to look for other way to do this. Just curious - are you using xlsx? – Thrash Bean Feb 07 '14 at 19:15
  • yes, macro enabled though. @ThrashBean – Stupid.Fat.Cat Feb 07 '14 at 19:16
  • Sorry about you, look for something different, this will not work. – Thrash Bean Feb 07 '14 at 19:18
  • 1
    Looks like you have a problematic named range that POI is struggling to update for the shift. Any chance you could post any non-standard named ranges you have in the sheet? – Gagravarr Feb 09 '14 at 13:16
  • I have the same problem. For simple file shiftRows method works fine, but if I run this method for large file, it shows me the same error as for user ~ Stupid.Fat.Cat My version POI is 3.10. Thanks in advance! – Piotr Jun 04 '14 at 15:02
  • There was a lot of work on this part of the code of POI lately, so it would be interesting what happens if you run it with a current nightly build! – centic Jul 30 '14 at 06:11

1 Answers1

0

Try This. sheet.shiftRows(currentRow, sheet.getLastRowNum()+1, amount, true,true);

Shrikant
  • 523
  • 4
  • 15