Read the calculated data using formula in Excel and pass it to Jtable

Question

Read the calculated data using formula in Excel and pass it to Jtable

Navigation

#1 by (0 votes)

0

I have already solved the export of an Excel sheet to a Jtable in Java. The problem arises in Excel cells that have been calculated using formula. In these cases, the Jtable does not interpret the format and the export is interrupted. Do you know any solution about it? This is the code that I use.

public class importExcel {

    Workbook wb;

    public String Importar (File archivo, JTable tabla){

        String respuesta="No se ha podido importar el fichero Excel.";
        DefaultTableModel modeloT=new DefaultTableModel();
        tabla.setModel(modeloT);
        try{
            wb=WorkbookFactory.create(new FileInputStream(archivo));
            Sheet hoja=wb.getSheetAt(0);
            Iterator filaIterator=hoja.rowIterator();
            int indiceFila=-1;
            while(filaIterator.hasNext()){
                indiceFila++;
                Row fila=(Row)filaIterator.next();
                Iterator columnaIterator=fila.cellIterator();
                Object[] listaColumna= new Object[20];//Averiguar porque es 5
                int indiceColumna=-1;
                while(columnaIterator.hasNext()){
                    indiceColumna++;
                    Cell celda=(Cell) columnaIterator.next();
                    if(indiceFila==0){
                        modeloT.addColumn(celda.getStringCellValue());
                    }else{
                        if(celda!=null){
                            switch(celda.getCellType()){
                                case Cell.CELL_TYPE_NUMERIC:
                                    if( HSSFDateUtil.isCellDateFormatted(celda) ){
                                    CellStyle cellStyle = wb.createCellStyle();
                                    CreationHelper createHelper = wb.getCreationHelper();
                                    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
                                    //Aqui se puede cortar el bucle con una condición sobre la fecha o cualquier otra cosa
                                    celda.setCellValue(celda.getDateCellValue());
                                    celda.setCellStyle(cellStyle);

                                    listaColumna[indiceColumna]=celda.getDateCellValue();

                            }else{
                                    listaColumna[indiceColumna]=celda.getNumericCellValue();

                            }break;
                                case Cell.CELL_TYPE_STRING:
                                    listaColumna[indiceColumna]=celda.getStringCellValue();
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    listaColumna[indiceColumna]=celda.getBooleanCellValue();
                                    break;
                                default:
                                    listaColumna[indiceColumna]=celda.getDateCellValue();
                                    break;
                            }
                        }
                    } 
                }
                if(indiceFila!=0){
                    modeloT.addRow(listaColumna);
                }
            }
            respuesta="Exportación realizada con éxito";
        }catch(Exception e){

        }

        return respuesta;
    }

java excel

asked by Luis 20.12.2018 в 18:52

source

1 answer

Pass Context to XML Django Problems with "react-native run-android" in linux

score 0 · Answer 1

This is an example to use DecimalFormat, you put a pattern and this returns the value in string format and so you send it to the table.

if(cell.getCellTypeEnum() == CellType.NUMERIC)
                          texto = new DecimalFormat("0").format(cell.getNumericCellValue());

Another way would be to directly format the table by inviting the interest column in the type of value you want to place it. (double)

 tabla.setModel(new javax.swing.table.DefaultTableModel(
        new Object [][] {
            {null, null, null, null},
            {null, null, null, null},
            {null, null, null, null}
        },
        new String [] {
            "Title 1", "Title 2", "Title 3", "85.3"
        }
    ) {
        Class[] types = new Class [] {
            java.lang.Object.class, java.lang.Object.class, java.lang.Object.class, java.lang.Double.class
        };

        public Class getColumnClass(int columnIndex) {
            return types [columnIndex];
        }
    });