Using Jexcel for reading and writing

Question

I am making a simple algorithm, to read an excel spreadsheet, insert some records and save the altered spreadsheet.

To write my code, I based on the example of writing made available with the Jexcel package, so I have a method that is responsible for reading the spreadsheet, applying the content, writing this content in the spreadsheet 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();
    }

Follows the code of the method responsible for producing the content to be written, started simple, inserting new lines in the spreadsheet:

    private void aplicaConteudo(WritableWorkbook w) throws WriteException {

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

}

For testing:

    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: cannot Insert Row Within formula: Unrecognized token 61
Warning: cannot Insert Row Within formula: Unrecognized token 60
Warning: cannot Insert Row Within formula: Unrecognized token 60
Warning: cannot Insert Row Within formula: Unrecognized token 60
Warning: cannot Insert Row Within formula: Unrecognized token 60
Warning: cannot Insert Row Within formula: Unrecognized token 60
Warning: cannot 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.gerarPlanilhaInss(Generateexcellogic.java:45)
at br.com.Madi.projeto.Generateexcellogic.main(Generateexcellogic.java:68)

NOTE: My Excel spreadsheet works as a Template, it is only necessary to enter the data. In this case, when the insertRow() I ask him to insert a line into my Template, I get the above error, now when the line is outside the Template, do not get the error. However I cannot be sure if the spreadsheet has been changed or not.

Answer 1

The error is caused because the Jexcel library failed to evaluate a formula in its template sheet. The formula must have a crease of which one or more cells of that line are part and when trying to update the formula to include more lines it cannot. This is why in a new spreadsheet the code works.

Check that the formula is correct and that the Jexcel library supports that 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.