Bug 34023 - [RFE] shiftRows method does not update cell references in formula contained in shifted cells
Summary: [RFE] shiftRows method does not update cell references in formula contained i...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: PC Windows XP
: P2 enhancement with 2 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2005-03-15 21:26 UTC by R A Sequeira
Modified: 2008-01-08 09:19 UTC (History)
0 users

Test.xls is the input to the HSSFCreateSheet program (16.00 KB, application/vnd.ms-excel)
2005-03-15 21:29 UTC, R A Sequeira
Adds shiftFormulas method into shiftRows functionality (2.64 KB, patch)
2007-02-08 12:37 UTC, Brian Kalbfus
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description R A Sequeira 2005-03-15 21:26:16 UTC
I'm facing an issue with the shiftRows(...) method while trying to insert rows 
into an existing excel
spreadsheet. The following program reads an excel spreadsheet (which has cells 
with formulae in it) from 
the command line, inserts 10 rows and outputs the modified spreadsheet to the 
name specified in the
command line.

The issue I'm having is that if the original row (row that will be shifted 
during the "shiftRows" method)
had a cell that contained a formula that referenced other cells, the cell 
references do not change after 
"shiftRows" has been called. This behavior is different when you manually shift 
rows using MS Excel or
Open Office. When you do it manually, the cell references are updated in the 
formula as the row is being

I know the developers of POI don't expect POI to be a replacement to Excel (see 
but if there is a shiftRows method I think it should duplicate the 
functionality. Am I missing something here?

I haven't looked at the source code to understand POI since I've just started 
using POI. Lame excuse, I know :)
But if someone could confirm they are seeing the same behavior and whether the 
existing implementation/functionality of
shiftRows is the correct functionality, I'd really appreciate it.

I browsed through the user list archives, but could not find any indication 
that someone had faced this
problem. Maybe I missed something.

Windows XP
JDK 1.4.1_01

Tested on:
MS Excel 2002 SP3
Open Office 1.1.4

To run this example:

1) Compile HSSFCreateSheet.java (javac HSSFCreateSheet.java). Make sure you 
   poi-2.5.1-final-20040804.jar in your classpath
2) Create an empty excel file in the same location as the compiled class. Add a 
value 0 to cell B14 and 100 to cell C14. Add the formula =B14/C14 to cell B17. 
Save the excel file Test.xls.
3) Run HSSFCreateSheet as follows: java HSSFCreateSheet Test.xls shiftedTest.xls
4) Open "shiftedTest.xls". And check the formulae in the shifted row. The cell 
references in the 
   formula didn't change during the shift.

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.HSSFCellStyle;
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;

public class HSSFCreateSheet 
	private String fileName = null;
	private HSSFWorkbook hssfWb = null;
	public static void main(String [] args)
			HSSFCreateSheet hssf = new HSSFCreateSheet(args[0]);
			HSSFWorkbook wb = hssf.hssfWb;
			HSSFSheet sheet = wb.getSheetAt(0);

            // insert 10 rows starting at row 9
			sheet.shiftRows(8, sheet.getLastRowNum(), 10, true, 

			FileOutputStream fos = new FileOutputStream(args[1]);
		catch (Exception e)
			System.out.println("Exception encountered..." + 

	public HSSFCreateSheet(String inputExcelFileName) throws 
FileNotFoundException, IOException 
		this.fileName = inputExcelFileName;
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream
		hssfWb = new HSSFWorkbook(fs);


Thanks much,
Comment 1 R A Sequeira 2005-03-15 21:29:57 UTC
Created attachment 14493 [details]
Test.xls is the input to the HSSFCreateSheet program
Comment 2 Shawn Laubach 2005-03-15 22:58:09 UTC
This is more of a feature request.  I can confirm the functionality that you 
are seeing as when I wrote the shiftRows method, I didn't want to spend the 
time required to parse the forumula and move anything along with it.  I would 
get the most recent source and start looking at how to shift a formula (a 
shiftFormula function?).
Comment 3 R A Sequeira 2005-03-16 01:46:17 UTC
Thanks Shawn. Atleast I wasn't hallucinating.
I'll take a look at the source and see what I can do.

Comment 4 Brian Kalbfus 2007-02-08 12:37:43 UTC
Created attachment 19550 [details]
Adds shiftFormulas method into shiftRows functionality

Please review and apply this patch.  It uses regex matching to update row
references in formulas referencing rows being shifted.	It matches relative and
absolute references (i.e.  "A5:D7" or "$A$5:$D$7".  It does not match
references to entire rows (i.e.  "5:7" or "$5:$7").
Comment 5 Terence Jacyno 2007-04-24 04:18:02 UTC
The patch submitted by Brian works well. However, in order for the behaviour to
be similar to "shiftRows", the following should be added to the beginning of the
"shiftFormulas" method:


Comment 6 Nick Burch 2008-01-08 09:19:21 UTC
Fixed in svn

(Didn't use the supplied patch, instead processes the formulas properly, and
updates the reference parts it spots as required)