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: [email protected]
For additional commands, e-mail: [email protected]
