CODEWITHSUNDEEP
javaexcelapache-poi
Rafael Guevara Traverso
Rafael Guevara Traverso

Reputation: 3

issue after writing workbook with Apache-POI

I've a problem after inserting the data into the sheet of an excel template with the .xlsm extension. The code works. It correctly copies the data in question, but when I try to run a specific macro, which takes the imported data, it does not recognize all rows, only the first two.

However, if I copy and paste any cells in the second column, e.g. if I copy B2 in B2 and execute the macro, it recognizes the total number of inserted rows. Can you help me? I hope I was clear.

Here is my Function Code

public String generarExcelSalida(String codCampaña,JDateChooser fechaParametro,String tfOutFolder){
String fileName="Modelo Caida "+codCampaña+".xlsm";
String exitFilePath=tfOutFolder+"\\"+fileName;
FileInputStream fileIn=null;
FileOutputStream fileOut=null;
SimpleDateFormat dParam=new SimpleDateFormat("dd/MM/yyyy");
String dateParam = dParam.format(fechaParametro.getDate()).toString();
ResultSet rs=DBManager.ConsultaCaida(dateParam, codCampaña);
try {
    // Here I'm opening my template file
    fileIn=new FileInputStream(FileManagement.carpetaTemplate+"\\Template_Caidas.xlsm");

    XSSFWorkbook workbook=new XSSFWorkbook(OPCPackage.open(fileIn));
    XSSFSheet sheet9=workbook.getSheet("BASE_DATOS");

    int i=1; // This parameter is used to getRow(i)
    while(rs.next()){
        XSSFRow row = sheet9.getRow(i);

        XSSFCell cell1=row.getCell(0);
        XSSFCell cell2=row.getCell(1);
        XSSFCell cell3=row.getCell(2);
        XSSFCell cell4=row.getCell(3);
        XSSFCell cell5=row.getCell(4);
        XSSFCell cell6=row.getCell(5);
        //Writing de ResultSet values on Sheet "BASE_DATOS"
        cell1.setCellValue(rs.getString("Producto Comercial"));
        cell2.setCellValue(rs.getInt("Nro Certificados"));
        cell3.setCellValue(rs.getString("DefEstado"));
        cell4.setCellValue(rs.getDate("Año-Mes Venta"));
        cell5.setCellValue(rs.getDate("Año-Mes Inicio Vigencia"));
        cell6.setCellValue(rs.getDate("AM Estado"));
        i++;// next Sheet.row

        }

    fileOut=new FileOutputStream(exitFilePath);
    //writing the workbook in a new file, it is like SAVE AS
    workbook.write(fileOut);
    if(fileOut!=null) fileOut.close();
    if(fileIn!=null) fileIn.close();
} catch (IOException | InvalidFormatException | SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
//return the path of the new file
return exitFilePath;
}

Here is the macro's code

Private Sub Base_Datos()
Dim Numero_Registro As Integer
Dim Celda As Range
Dim Columna, Fila As Integer

Sheets("BASE_DATOS").Select

' Busqueda del limite de Filas
Fila = Range("Numero_Registro") + 1

' Busqueda del limite de Columnas
Set Celda = Range("1:1").Find("BAJAS")
Columna = Celda.Column

With Range("a1")
' Copiar Pegar
Range(.Cells(2, Columna), .Cells(2, 100)).Select
Selection.Copy

Range(.Cells(3, Columna), .Cells(Fila, 100)).Select
Selection.PasteSpecial Paste:=xlFormulas

End With
End Sub

Upvotes: 0

Views: 881

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

So from your comments the issue is about the needed recalculation because the macro relies on a cell value which uses COUNTA to count filled cells.

Two possible solutions:

First: You could use a FormulaEvaluator to let evaluate the formulas from apache poi before saving the workbook:

...
    //writing the workbook in a new file, it is like SAVE AS
    XSSFFormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    evaluator.evaluateAll();
    workbook.write(fileOut);
...

This may fail dependent of the kind of the formulas.

If it fails using evaluateAll() you could at least let evaluate the cell with the COUNTA formula. This should not fail since apache poi supports COUNTA function.

Read Formula Evaluation for how to do.

Second: You could force Excel to do the recalculation while it is opening the file:

...
    //writing the workbook in a new file, it is like SAVE AS
    workbook.setForceFormulaRecalculation(true);
    workbook.write(fileOut);
...

This may lead to long time process while opening the file.

Upvotes: 1

Related Questions