If the formula contains a space or _ (underscore) charcters, setCellFormula throws array out of bounds exception. Sample code: import java.io.*; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.*; public class FormulaError { public static void main(String args[]) { try { // New workbook FileOutputStream out = new FileOutputStream ("D://java//programs//test.xls"); HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet1 = book.createSheet("Test Sheet1"); // Note the space in between HSSFSheet sheet2 = book.createSheet("Sheet2"); HSSFRow row = sheet1.createRow((short)0); HSSFCell cell = row.createCell((short)0); cell.setCellValue("SVUG1000"); HSSFRow row2 = sheet2.createRow((short)0); HSSFCell cell2 = row2.createCell((short)0); // The space in the sheet name causes the set formula to throw an exception cell2.setCellFormula("Test Sheet1!A1"); // Link to cell 0 on sheet 1 book.write(out); out.close(); } catch (Exception ex) { System.out.println("Ex: " + ex); } } }
Change the line cell2.setCellFormula("Test Sheet1!A1"); to cell2.setCellFormula("'Test Sheet1'!A1"); and it should work. cheers Conrad