You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Carl Pritchett <ca...@calibreft.com.au> on 2011/02/28 04:39:37 UTC
XSSFWorkbook.setSheetName breaks existing named ranges
Hi,
When I create a HSSFWorkbook with a named range and then set the sheet name
with Workbook.setSheetName() all the named ranges' formulas are renamed
also.
When I do this with a XSSFWorkbook the named ranges' formulas are not
renamed and thus are broken (have the value of #REF!").
Is this a bug or divergent behaviour? Any workarounds?
Here is a JUnit tests that creates a file with such a broken named range.
@Test
public void create() throws IOException
{
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
// HSSFWorkbook handles rename
// Workbook wb = new HSSFWorkbook();
//File file = new File("c:\\test.xls");
// XSSFWorkbook does not handle the rename
Workbook wb = new XSSFWorkbook();
File file = new File("c:\\test.xlsx");
Sheet sheet = wb.createSheet(sname);
sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);
// 1. create named range for a single cell using areareference
Name namedCell = wb.createName();
namedCell.setNameName(cname);
String reference = sname+"!A1:A1"; // area reference
namedCell.setRefersToFormula(reference);
// 2. rename the sheet
wb.setSheetName(wb.getSheetIndex(sheet), "newName");
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
}
Regards,
Carl Pritchett
Re: XSSFWorkbook.setSheetName breaks existing named ranges
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Not a real solution to the problem but rather a way to work around it. If you
try running this piece of test code, you should see that setting the order
of the worksheets within the workbook before they are populated produces a
valid workbook. On the other hand, setting the order after the worksheets
have been populated does produce an error. I have left in two sets of
commands that you can simply swap the comments on to see what I mean.
As I said, not a solution but it will allow you to proceed with your
project.
Yours
Mark B
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package workbookprotection;
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
/**
*
* @author win user
*/
public class SheetOrderTest {
public SheetOrderTest(String filename) throws IOException {
File file = null;
FileOutputStream fos = null;
HSSFWorkbook workbook = null;
HSSFSheet detailSheet = null;
HSSFSheet summarySheet = null;
HSSFCell cell = null;
HSSFRow row = null;
try {
workbook = new HSSFWorkbook();
detailSheet = workbook.createSheet("Detail Sheet");
summarySheet = workbook.createSheet("Summary Sheet");
// Setting the order of the sheets in the workbook before
// they are populated seems to work just fine.
workbook.setSheetOrder("Summary Sheet", 0);
workbook.setSheetOrder("Detail Sheet", 1);
row = detailSheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue("Test.");
cell = row.createCell(1);
cell.setCellValue(1234.56);
row = summarySheet.createRow(0);
cell = row.createCell(0);
cell.setCellFormula("'Detail Sheet'!$A$1");
cell = row.createCell(1);
cell.setCellFormula("'Detail Sheet'!$B$1");
// Setting the order of the worksheets here, after they have
been
// populated, will not work. The workbook is said to contain
// formulae with circular references when Excel tries to open
it.
//workbook.setSheetOrder("Summary Sheet", 0);
//workbook.setSheetOrder("Detail Sheet", 1);
file = new File(filename);
fos = new FileOutputStream(file);
workbook.write(fos);
fos.close();
fos = null;
}
finally {
if(fos != null) {
fos.close();
fos = null;
}
}
}
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/XSSFWorkbook-setSheetName-breaks-existing-named-ranges-tp3402985p3965623.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: XSSFWorkbook.setSheetName breaks existing named ranges
Posted by SharonG <sg...@northwestern.edu>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org