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.