Hi! I am experiencing a similar problem where the sheet name reference in a
formula is being changed, except that it occurs when I use setSheetOrder().
I believe this may be related to bugs 50083, 48294, and 46028.
I'm using HSSF, but my code is in ColdFusion rather than Java, and so I've
taken Carl's Java code and rewritten it to provide an example of what I'm
trying to do. Namely...
Sheet #0, called Summary, contains the formula FY11!A1 in a cell
Sheet #1, called FY11, contains data in cell A1
If I create Sheet #0 before Sheet #1, then the formula on the Summary sheet
shows as #REF!A1. The #REF error never clears itself even though the FY11
sheet exists in the workbook. (When I create these sheets manually in this
order, I get the same result, so it must be normal Excel behavior.)
If I create Sheet #1 before Sheet #0, then reorder them using
setSheetOrder(), then the formula on the Summary sheet changes to
Summary!A1, which gives me a circular reference error.
If I create Sheet #1 before Sheet #0 and leave the order, then the formula
works fine. Butm my users want to see the Summary sheet first in the
workbook. And so, I really need some way to keep setSheetOrder() from
changing the sheet reference in my formula.
Here is my ColdFusion code reworked into Java:
@Test
public void create() throws IOException
{
Workbook wb = new HSSFWorkbook();
File file = new File("c:\\test.xls");
Sheet sheet1 = wb.createSheet("FY11");
sheet1.createRow(0).createCell((short) 0).setCellValue(99.99);
Sheet sheet2 = wb.createSheet("Summary");
sheet1.createRow(0).createCell((short) 0).setCellFormula("FY11!A1");
wb.setSheetOrder("Summary",0);
wb.setSheetOrder("FY11",1);
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
}
Many thanks in advance!
Sharon
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/XSSFWorkbook-setSheetName-breaks-existing-named-ranges-tp3402985p3876849.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
