Using JExcel for reading and writing

2

I'm making a simple algorithm, to read an excel sheet, insert some records and save the changed sheet.

To write my code, I took as a basis the writing example provided with the JExcel package, so I have a method that is responsible for reading the worksheet, applying the content, writing that content to the worksheet, and saving it.

    public void gerarPlanilhaPreenchida(String inputFile, String outputFile) throws IOException, RowsExceededException, WriteException{

    File inputTemplate = new File(inputFile);
    File outputTemplate = new File(outputFile);

    Workbook planilhaTemplate;
    WritableWorkbook planilhaResultado;

    try{

        planilhaTemplate = Workbook.getWorkbook(inputTemplate);
        planilhaResultado = Workbook.createWorkbook(outputTemplate, planilhaTemplate);

        aplicaConteudo(planilhaResultado);

        planilhaResultado.write();
        planilhaResultado.close();

    }
    catch(BiffException e){
        e.printStackTrace();
    }

Here is the code of the method responsible for producing the content to be written, I started by simple inserting new lines in the worksheet:

    private void aplicaConteudo(WritableWorkbook w) throws WriteException {

    WritableSheet excelSheet = w.getSheet("INSS");
    excelSheet.insertRow(11);

}

To test:

    public static void main(String[] args) throws RowsExceededException, WriteException, IOException {

    String inputFile = "<<HOME_FOLDER>>/entrada/PlanilhaTemplate.xls";
    String outputFile = "<HOME_FOLDER<>>/saida/PlanilhaTemplate2.xls";

    GenerateExcelLogic novaPlanilhaTeste = new GenerateExcelLogic();
        novaPlanilhaTeste.gerarPlanilhaPreenchida(inputFile, outputFile);
}

This is the error log when running main :

  

Warning: can not insert row within formula: Unrecognized token 61
  Warning: can not insert row within formula: Unrecognized token 60
  Warning: can not insert row within formula: Unrecognized token 60
  Warning: can not insert row within formula: Unrecognized token 60
  Warning: can not insert row within formula: Unrecognized token 60
  Warning: can not insert row within formula: Unrecognized token 60
  Warning: can not insert row within formula: Unrecognized token 60
  Exception in thread "main" java.lang.NullPointerException
    at jxl.biff.formula.TokenFormulaParser.getBytes (TokenFormulaParser.java:495)
    at jxl.biff.formula.FormulaParser.getBytes (FormulaParser.java:183)
    at   jxl.write.biff.ReadFormulaRecord.getData (ReadFormulaRecord.java:145)
    at jxl.biff.WritableRecordData.getBytes (WritableRecordData.java:71)
    at jxl.write.biff.File.write (File.java:147)     at jxl.write.biff.RowRecord.writeCells (RowRecord.java:342)
    at jxl.write.biff.SheetWriter.write (SheetWriter.java:480)
    at jxl.write.biff.WritableSheetImpl.write (WritableSheetImpl.java:1558)
    at jxl.write.biff.WritableWorkbookImpl.write (WritableWorkbookImpl.java:950)
    at br.com.madi.projeto.GenerateExcelLogic.gerarInstructor (GenerateExcelLogic.java:45)
    at

NOTE: My Excel worksheet works as a Template , you just need to enter the data. In this case, when in insertRow() I ask him to insert a line inside my Template , I get the error above, now when the line is outside the Template , I do not receive the error. But I can not be sure if the worksheet has been changed or not.

    
asked by anonymous 04.11.2015 / 01:54

1 answer

1

The error is caused because the JExcel library was unable to evaluate a formula in its template worksheet. The formula must have a range of which one or more cells in that row are part and, when trying to update the formula to include more rows, it can not. That's why a new spreadsheet works.

Verify that the formula is correct and that the JExcel library supports the functions and syntax used. One possibility is that some feature of newer versions of Excel is being used and the library is not up to date.

Finally, look for the formula with problems, and if you need more help, update the question with the formula that causes the error.

    
04.11.2015 / 03:40