Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getColumnIndex.

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:standarapp.algorithm.CodeAssign.java

public CodeAssign(String nameExcel) throws IOException {
    //Logica de la aplicacion
    diccionario_UbicacionLocalidad = new Hashtable<>();
    codigo_Dpto = new Hashtable<>();
    codigo_Municipio = new Hashtable<>();
    dpto_Municipio = new Hashtable<>();
    codigo_localidad = new Hashtable<>();
    localidad_X = new Hashtable<>();
    localidad_Y = new Hashtable<>();
    codigo_municipioLocalidad = new Hashtable<>();

    XSSFWorkbook xwb = Lecture.lectureXLSX(nameExcel);
    XSSFSheet xsheet = xwb.getSheetAt(0);
    double codigoTemporal = 0;

    for (Row row : xsheet) {
        if (row.getRowNum() > 0) {
            String departamento = "", municipio = "", localidad = "";
            int cod_departamento = 0, cod_municipio = 0;
            double cod_localidad = 0, x = 0, y = 0;
            for (Cell cell : row) {
                if (cell.getColumnIndex() == 0) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        codigoTemporal = Double.valueOf(cell.getStringCellValue());
                        cod_departamento = (int) codigoTemporal;
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        cod_departamento = (int) cell.getNumericCellValue();
                        continue;
                    }//from   w  w  w  .jav a2  s .co m
                }

                if (cell.getColumnIndex() == 1) {
                    departamento = cell.getStringCellValue();
                }

                if (cell.getColumnIndex() == 2) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        codigoTemporal = Double.valueOf(cell.getStringCellValue());
                        cod_municipio = (int) codigoTemporal;
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        cod_municipio = (int) cell.getNumericCellValue();
                        continue;
                    }
                }

                if (cell.getColumnIndex() == 3) {
                    municipio = cell.getStringCellValue();
                }

                if (cell.getColumnIndex() == 4) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        cod_localidad = Double.valueOf(cell.getStringCellValue());
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        cod_localidad = (double) cell.getNumericCellValue();
                        continue;
                    }
                }

                if (cell.getColumnIndex() == 5) {
                    localidad = cell.getStringCellValue();
                }

                if (cell.getColumnIndex() == 6) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        x = Double.valueOf(cell.getStringCellValue());
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        x = (double) cell.getNumericCellValue();
                        continue;
                    }
                }

                if (cell.getColumnIndex() == 7) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        y = Double.valueOf(cell.getStringCellValue());
                        continue;
                    case Cell.CELL_TYPE_NUMERIC:
                        y = (double) cell.getNumericCellValue();
                        continue;
                    }
                }
            }

            if (!diccionario_UbicacionLocalidad.containsKey(departamento)) {
                Hashtable<String, Hashtable<String, Double>> primerMunicipio = new Hashtable<>();
                Hashtable<String, Double> primerLocalidad = new Hashtable<>();
                Hashtable<Double, String> primerLocalidadInv = new Hashtable<>();
                codigo_Dpto.put(cod_departamento, departamento);
                codigo_Municipio.put(cod_municipio, municipio);
                codigo_localidad.put(cod_localidad, localidad);
                localidad_X.put(cod_localidad, x);
                localidad_Y.put(cod_localidad, y);

                primerLocalidadInv.put(cod_localidad, localidad);
                codigo_municipioLocalidad.put(cod_municipio, primerLocalidadInv);
                primerLocalidad.put(localidad, cod_localidad);
                primerMunicipio.put(municipio, primerLocalidad);
                diccionario_UbicacionLocalidad.put(departamento, primerMunicipio);

            } else if (!diccionario_UbicacionLocalidad.get(departamento).containsKey(municipio)) {
                Hashtable<String, Double> primerLocalidad = new Hashtable<>();
                codigo_Municipio.put(cod_municipio, municipio);
                codigo_localidad.put(cod_localidad, localidad);
                localidad_X.put(cod_localidad, x);
                localidad_Y.put(cod_localidad, y);
                primerLocalidad.put(localidad, cod_localidad);

                Hashtable<Double, String> primerLocalidadInv = new Hashtable<>();

                primerLocalidadInv.put(cod_localidad, localidad);
                codigo_municipioLocalidad.put(cod_municipio, primerLocalidadInv);

                diccionario_UbicacionLocalidad.get(departamento).put(municipio, primerLocalidad);

            } else if (!diccionario_UbicacionLocalidad.get(departamento).get(municipio)
                    .containsKey(localidad)) {
                codigo_localidad.put(cod_localidad, localidad);
                localidad_X.put(cod_localidad, x);
                localidad_Y.put(cod_localidad, y);
                codigo_municipioLocalidad.get(cod_municipio).put(cod_localidad, localidad);
                diccionario_UbicacionLocalidad.get(departamento).get(municipio).put(localidad, cod_localidad);
            }
        }
    }

}

From source file:standarapp.algorithm.Lecture.java

private void fixXLS(String nameIn, String nameOut, int nameSheet, int columnas[]) {
    HSSFWorkbook xwb = lectureXLS(nameIn);
    HSSFSheet xsheet = xwb.getSheetAt(0);
    HSSFSheet xsheet_WRITE = xwb.createSheet();
    for (Row row : xsheet) {
        xsheet_WRITE.createRow(row.getRowNum());
        for (Cell cell : row) {
            try {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    String contenido = cell.getStringCellValue();
                    if (columnas.length == 0 || containsInColumns(columnas, cell.getColumnIndex()))
                        contenido = fixWords(contenido);
                    xsheet_WRITE.getRow(row.getRowNum()).createCell(cell.getColumnIndex())
                            .setCellValue(contenido);
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    double contenido_Numerico = cell.getNumericCellValue();
                    xsheet_WRITE.getRow(row.getRowNum()).createCell(cell.getColumnIndex())
                            .setCellValue(contenido_Numerico);
                    break;
                /*default:
                    System.err.print(cell + "\t\t");
                    xsheet_WRITE.getRow(row.getRowNum()).createCell(cell.getColumnIndex()).setCTCell((CTCell) cell);
                    break;*//*from  www .j a va2s .  com*/
                }
            } catch (Exception e) {
            }
        }
    }
    try (FileOutputStream outputStream = new FileOutputStream(nameOut)) {
        xwb.write(outputStream);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:standarapp.algorithm.Lecture.java

private void fixXLSX(String nameIn, String nameOut, int sheet, int columnas[]) {
    XSSFWorkbook xwb = lectureXLSX(nameIn);
    XSSFSheet xsheet = xwb.getSheetAt(sheet);

    for (Row row : xsheet) {
        //xsheet.createRow(row.getRowNum());
        for (Cell cell : row) {
            try {
                if (columnas.length == 0 || containsInColumns(columnas, cell.getColumnIndex())) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        String contenido = cell.getStringCellValue();
                        if (!cell.getStringCellValue().equals("")) {
                            contenido = fixWords(contenido);
                            xsheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex())
                                    .setCellValue(contenido);
                        } else {
                            xsheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).setCellValue("");
                        }//from   w  w w .  j  a  v a 2  s .c o m
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double contenido_Numerico = cell.getNumericCellValue();
                        xsheet.getRow(row.getRowNum()).createCell(cell.getColumnIndex())
                                .setCellValue(contenido_Numerico);
                        break;
                    default:
                        System.err.print(cell + "\t\t");
                        xsheet.getRow(row.getRowNum()).createCell(cell.getColumnIndex())
                                .setCTCell((CTCell) cell);
                        break;
                    }
                }
            } catch (Exception e) {
            }
        }
    }

    try (FileOutputStream outputStream = new FileOutputStream(nameOut)) {
        xwb.write(outputStream);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:step.datapool.excel.ExcelDataPoolImpl.java

License:Open Source License

private int mapHeaderToCellNum(Sheet sheet, String header, boolean createHeaderIfNotExisting) {
    if (configuration.getHeaders().get()) {
        Row row = sheet.getRow(0);//from w  w w.  j av a2 s .  c  o m
        if (row != null) {
            for (Cell cell : row) {
                String key = ExcelFunctions.getCellValueAsString(cell, workbookSet.getMainFormulaEvaluator());
                if (key != null && key.equals(header)) {
                    return cell.getColumnIndex();
                }
            }
        } else {
            if (createHeaderIfNotExisting) {
                sheet.createRow(0);
            } else {
                throw new ValidationException("The sheet " + sheet.getSheetName() + " contains no headers");
            }
        }
        if (createHeaderIfNotExisting) {
            return addHeader(sheet, header);
        } else {
            throw new ValidationException(
                    "The column " + header + " doesn't exist in sheet " + sheet.getSheetName());
        }
    } else {
        return CellReference.convertColStringToIndex(header);
    }
}

From source file:step.datapool.excel.ExcelDataPoolImpl.java

License:Open Source License

private int addHeader(Sheet sheet, String header) {
    if (configuration.getHeaders().get()) {
        Row row = sheet.getRow(0);//w  w  w  .  ja v  a2s.  c  om
        Cell cell = row.createCell(Math.max(0, row.getLastCellNum()));
        cell.setCellValue(header);
        updated = true;
        return cell.getColumnIndex();
    } else {
        throw new RuntimeException("Unable to create header for excel configured not to use headers.");
    }
}

From source file:step.datapool.excel.ExcelFunctions.java

License:Open Source License

private static int evaluateFormulaCell(Cell cell, FormulaEvaluator evaluator) {
    int typ = -1;
    try {/*from   w ww .  j  av a2  s  .  co m*/
        typ = evaluator.evaluateFormulaCell(cell);
    } catch (RuntimeException e) {
        String cellRef = CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
        String errMsg = "Error while evaluating cell " + cellRef + " from sheet "
                + cell.getSheet().getSheetName() + ": " + e.getMessage();
        throw new RuntimeException(errMsg, e);
    }
    return typ;
}

From source file:tech.tablesaw.io.xlsx.XlsxReader.java

License:Apache License

private TableRange findRowArea(Row row) {
    int col1 = -1;
    int col2 = -1;
    for (Cell cell : row) {
        Boolean blank = isBlank(cell);
        if (col1 < 0 && Boolean.FALSE.equals(blank)) {
            col1 = cell.getColumnIndex();
            col2 = col1;/*from www .j  a va  2s  .c  o  m*/
        } else if (col1 >= 0 && col2 >= col1) {
            if (Boolean.FALSE.equals(blank)) {
                col2 = cell.getColumnIndex();
            } else if (Boolean.TRUE.equals(blank)) {
                break;
            }
        }
    }
    return col1 >= 0 && col2 >= col1 ? new TableRange(0, 0, col1, col2) : null;
}

From source file:techgarden.Controller.java

public Object[][] getData(String excelFilePath) throws IOException, InvalidFormatException {

    FileInputStream fis = new FileInputStream(new File(excelFilePath));
    org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);
    org.apache.poi.ss.usermodel.Sheet firstSheet = workbook.getSheetAt(0);
    int rownum = firstSheet.getLastRowNum();
    int colnum = firstSheet.getRow(0).getLastCellNum();
    Object[][] data = new Object[rownum][colnum];
    //String[][] stringData = new String[rownum][colnum];
    for (int i = 0; i < rownum; i++) {
        Row row = firstSheet.getRow(i);//from  w  ww .ja  v a  2 s .  c om
        if (row != null) {
            for (int j = 0; j < colnum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    try {

                        if (cell.getColumnIndex() == 0) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            data[i][j] = cell.getStringCellValue();
                            // System.out.println(cell.getStringCellValue());
                        } else if (cell.getColumnIndex() == 1) {
                            data[i][j] = cell.getDateCellValue();
                            // System.out.println(cell.getDateCellValue());
                        } else {
                            data[i][j] = cell.getNumericCellValue();
                        }
                    } catch (IllegalStateException e) {
                        e.printStackTrace();
                        //
                    }
                }
            }
        }
    }
    workbook.close();
    fis.close();
    return data;
}

From source file:testpoi.GenerateDailyExcel.java

License:Open Source License

private static void makeEntry(Department deptt) {
    //create new row in xlsx to be generated
    Row newRow = sheetNew.createRow(rowCnt++);
    //Create a new cell in current row
    Cell newCell = newRow.createCell(0);
    //Set value to the department's name
    newCell.setCellValue(deptt.name);/*  w w  w . ja v  a 2 s  . c  om*/

    double random = Math.random();
    Row row = null;
    if (deptt.name.equals("Gynaecology")) {
        //Pick a row from female sheet randomly (Female sheet should have all reproducible ages)
        int rowNum = (int) (random * sheetFemale.getPhysicalNumberOfRows());

        row = sheetFemale.getRow(rowNum);
    } else if (deptt.name.equals("Paediatrics")) {
        //Pick a row from children sheet randomly (Children sheet should have all ages under 13)
        int rowNum = (int) (random * sheetChildren.getPhysicalNumberOfRows());

        row = sheetChildren.getRow(rowNum);
    } else {
        //Pick a row from all sheet randomly
        int rowNum = (int) (random * sheetAll.getPhysicalNumberOfRows());

        row = sheetAll.getRow(rowNum);
    }
    assert (row != null);

    //read and write fetched row
    Iterator<Cell> cellIterator = row.cellIterator();
    int newCellCnt = 1;
    while (cellIterator.hasNext()) {
        //May we write all cells as strings?
        Cell cell = cellIterator.next();
        String cellValue = null;
        try {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                cellValue = cell.getNumericCellValue() + "";
            else
                cellValue = cell.getStringCellValue();

            newCell = newRow.createCell(newCellCnt++);
            newCell.setCellValue(cellValue);
            //                System.out.print (cellValue+"("+cell.getColumnIndex()+")\t");
        } catch (Exception e) {
            System.out.println("Could not write from cell (value:" + cellValue +
            //                        ", column:"+cell.getSheet().getWorkbook().+
                    ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:"
                    + cell.getColumnIndex() + ")");
            e.printStackTrace();
        }
    }
    System.out.println();

    //delete row read
    if (row.getSheet() == sheetFemale)
        sheetFemale.removeRow(row);
    else if (row.getSheet() == sheetChildren)
        sheetChildren.removeRow(row);
    else
        sheetAll.removeRow(row);
}

From source file:testpoi.GenerateDailyExcelPickingRowsSequentially.java

License:Open Source License

private static void makeEntry(Department deptt) {
    //create new row in xlsx to be generated
    Row newRow = sheetNew.createRow(rowCnt++);
    //Create a new cell in current row
    Cell newCell = newRow.createCell(0);
    //Set value to the department's name
    newCell.setCellValue(deptt.name);/*ww  w.j a  v  a2s  . c  o  m*/

    Row row = null;
    if (deptt.name.equals("Obs & Gynae") && femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) {
        //            //Pick a row from female sheet randomly (Female sheet should have all reproducible ages)
        //            int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows());

        row = sheetFemale.getRow(femaleRowNum++);
        System.out.println("Sheet:Female, row: " + row.getRowNum());
    } else if (deptt.name.equals("Paediatrics") && childRowNum < sheetChildren.getPhysicalNumberOfRows()) {
        row = sheetChildren.getRow(childRowNum++);
        System.out.println("Sheet:Children, row: " + row.getRowNum());
    } else //if (allRowNum<sheetAll.getPhysicalNumberOfRows())
    {
        row = sheetAll.getRow(allRowNum++);
        System.out.println("Sheet:All, row: " + row.getRowNum());
    }
    assert row != null;

    //read and write fetched row
    Iterator<Cell> cellIterator = row.cellIterator();
    int newCellCnt = 1;
    while (cellIterator.hasNext()) {
        //May we write all cells as strings?
        Cell cell = cellIterator.next();
        String cellValue = null;
        try {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                cellValue = (int) (cell.getNumericCellValue()) + "";
            else
                cellValue = cell.getStringCellValue();

            newCell = newRow.createCell(newCellCnt++);
            newCell.setCellValue(cellValue);
        } catch (Exception e) {
            System.out.println("Could not write from cell (value:" + cellValue +
            //                        ", column:"+cell.getSheet().getWorkbook().+
                    ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:"
                    + cell.getColumnIndex() + ")");
            e.printStackTrace();
        }
    }
    System.out.println();

    //        //delete row read
    //        if (row.getSheet()==sheetFemale)
    //            sheetFemale.removeRow(row);
    //        else if (row.getSheet()==sheetChildren)
    //            sheetChildren.removeRow(row);
    //        else
    //            sheetAll.removeRow(row);
}