Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:se.nrm.dina.inventory.client.fileupload.ExcelFileHandler.java

private TaxaData buildTaxaData(Row row) {

    Cell cell = row.getCell(taxonListTitleMap.get("Genus"));
    genus = cell == null ? "" : cell.getStringCellValue();

    cell = row.getCell(taxonListTitleMap.get("Species"));
    species = cell == null ? "" : cell.getStringCellValue();

    cell = row.getCell(taxonListTitleMap.get("Author"));
    author = cell == null ? "" : cell.getStringCellValue();

    cell = row.getCell(taxonListTitleMap.get("GUID"));
    guid = cell == null ? "" : cell.getStringCellValue();

    cell = row.getCell(taxonListTitleMap.get("Source"));
    source = cell == null ? "" : cell.getStringCellValue();

    if (taxonListTitleMap.containsKey("Notes (errors, synonyms, undescribed etc)")) {
        cell = row.getCell(taxonListTitleMap.get("Notes (errors, synonyms, undescribed etc)"));
    } else if (taxonListTitleMap.containsKey("Notes")) {
        cell = row.getCell(taxonListTitleMap.get("Notes"));
    } else {//from w  w w .  ja  v a 2  s.c  o m
        cell = null;
    }
    notes = cell == null ? "" : cell.getStringCellValue();

    String msg;
    cell = row.getCell(taxonListTitleMap.get("Taxon name (computed)"));
    computedName = cell == null ? "" : cell.getStringCellValue();
    if (computedName == null || computedName.isEmpty()) {
        msg = CommonMessages.getInstance().buildMessage(CommonMessages.getInstance().TAXON_COMPUTED_IS_EMPTY,
                row.getRowNum() + 1);
        throw new InvalidExcelFileException(msg);
    } else if (!computedName.trim().equals((genus + " " + species).trim())) {
        msg = CommonMessages.getInstance()
                .buildMessage(CommonMessages.getInstance().TAXON_COMPUTED_IS_INCORRECT, row.getRowNum() + 1);
        throw new InvalidExcelFileException(msg);
    }

    return new TaxaData(0, genus, species, computedName, guid, author, notes, source, "", null);
}

From source file:service.Read_Write_File.java

public static List<Consomation> Read_Fil_XLSX(File myFile) throws FileNotFoundException, IOException {
    List<CorpDetat> corpDetats = new ArrayList<CorpDetat>();
    List<Consomation> consomations = new ArrayList<Consomation>();

    FileInputStream fis = new FileInputStream(myFile);

    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    XSSFSheet mySheet = myWorkBook.getSheetAt(0);

    Iterator<Row> rowIterator = mySheet.iterator();

    Consomation consomation = null;// w  ww  .jav a  2  s.  c om
    CorpDetat corpDetat = null;
    ConsomationItem consomationItem = null;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (row.getRowNum() == 0) {// pour ne pas lire les  titres
            row = rowIterator.next();
        }

        int lascellNum = row.getLastCellNum();
        int firstcellNum = row.getFirstCellNum();
        System.out.println("first cell num" + firstcellNum + "  last cell num" + lascellNum);
        if (lascellNum < 3) {
            if (row.getRowNum() > 1) {
                //corpDetats.add(corpDetat);

                consomations.add(consomation);
                corpDetat = null;
                System.out.println("if dyal row celll");
            }
            consomation = new Consomation();
            consomation.setId((int) row.getCell(firstcellNum).getNumericCellValue());
            corpDetat = new CorpDetat();
            corpDetat.setTitre(row.getCell(firstcellNum + 1).getStringCellValue());

        } else {
            Post post = new Post();
            consomationItem = new ConsomationItem();

            for (int i = firstcellNum; i < lascellNum; i++) {
                Cell cell = row.getCell(i);

                switch (i) {
                case 0:
                    System.out.println("num de consomation item" + cell.getStringCellValue());
                    consomationItem.setId(cell.getStringCellValue());
                    break;
                case 1:
                    System.out.println("cell " + i + ":" + cell.getStringCellValue());
                    post.setTitre(cell.getStringCellValue());
                    break;
                case 2:
                    consomationItem.setUnite(cell.getStringCellValue());
                    break;
                case 3:
                    System.out.println("cell " + i + ":" + cell.getCellType());
                    consomationItem.setQuanite((int) cell.getNumericCellValue());
                    break;

                }
            }
            if (post != null) {
                post.setCorpdetat(corpDetat);
                consomationItem.setPost(post);
                post.setCorpdetat(corpDetat);
                corpDetat.getPosts().add(post);
                consomation.getConsomationItems().add(consomationItem);
                consomation.setCorpDetat(corpDetat);

            }
        }

    }

    consomations.add(consomation);
    // pour le dernier corp makydkholch l row li tab30

    return consomations;
}

From source file:som.file.StemmedDataFileReader.java

License:Open Source License

public void writeIntoInputDataSheet(List<String> wordsList) {
    XSSFWorkbook workbook = null;//  www.j a  v a  2  s .  c om
    try {
        System.out.println("Writing into Input File");
        FileInputStream file = new FileInputStream(
                new File(GenericHelper.getAbsolutePath() + REVISED_INPUT_SHEET_NAME));
        //FileInputStream file = new FileInputStream(new File(REVISED_INPUT_SHEET_NAME));
        //File file = new File(REVISED_INPUT_SHEET_NAME);

        //Get the workbook instance for XLS file 
        workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                continue;
            }

            Cell cell = row.getCell(IGenericConstants.STEMMED_DATA_COLUMN_NUMBER);
            if (wordsList.size() > row.getRowNum()) {
                cell.setCellValue(wordsList.get(row.getRowNum()));
            }
        }

        file.close();

        System.out.println("Writing to the file on the server");
        FileOutputStream outFile = new FileOutputStream(
                new File(GenericHelper.getAbsolutePath() + REVISED_INPUT_SHEET_NAME));
        workbook.write(outFile);
        System.out.println("File Writing Complete");
        outFile.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

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   ww w  .  j av  a2s .c om
                }

                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;*//* w w  w  .  j av  a2 s  .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   ww w. j  av a 2s  .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:standarapp.algorithm.ReadFileVector.java

public String lectureRegistry(String nameFile, String nameOut, int[] col, double percent, int rowBegin) {
    String answer = "";
    int quantityFound = 0;

    workbook = Lecture.lectureXLSX(nameFile);
    sheet = workbook.getSheetAt(0);// ww w .  jav  a 2s .co  m

    for (Row row : sheet) {
        if (row.getRowNum() < rowBegin) {
            continue;
        }

        String[] cellsWI = new String[col.length + 1];
        for (int i = 0; i < col.length; i++) {
            cellsWI[i] = "";
            try {
                Cell cell = row.getCell(col[i]);
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    cellsWI[i] = deleteTrash(cell.getStringCellValue());
                } else {
                    cellsWI[i] = cell.getDateCellValue().toString();
                    cellsWI[col.length] = String.valueOf(cell.getDateCellValue().getMonth());
                }
                if (i == 2) {
                    cellsWI[i] = cell.getStringCellValue();
                }
            } catch (Exception e) {
            }
        }
        registry.add(cellsWI);
    }

    System.out.println("Total: " + registry.size());
    /*for (int i = 0; i < registry.size(); i++) {
    System.out.println(i +" Municipio: " + registry.get(i)[0] + " | Localidad: " + registry.get(i)[1] + " | Especie: " + registry.get(i)[2] + " | Fecha: " + registry.get(i)[3]);
    }*/

    int rowCount = 0;
    int columnCount = 0;

    CellStyle cs = workbook.createCellStyle();
    //Font font = workbook.createFont();
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderRight(BorderStyle.THIN);
    cs.setBorderLeft(BorderStyle.THIN);
    cs.setBorderBottom(BorderStyle.THIN);
    cs.setBorderTop(BorderStyle.THIN);

    sheet = workbook.createSheet();
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    sheet.setColumnWidth(0, 20);

    Row row = sheet.createRow(0);
    Cell encabezado = row.createCell(rowCount);
    encabezado.setCellValue("Base de  datos coordenadas");
    encabezado.setCellStyle(cs);

    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8);
    sheet.addMergedRegion(region);

    row = sheet.createRow(++rowCount);
    Cell cell = row.createCell(columnCount);
    cell.setCellValue("Especie");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Municipio");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Codigo Municipio");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Vereda");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Codigo Vereda");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Mes");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Ao");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Latitud");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Longitud");
    cell.setCellStyle(cs);
    cell = row.createCell(++columnCount);
    cell.setCellValue("Fuente");
    cell.setCellStyle(cs);

    for (int i = 0; i < registry.size(); i++) {
        try {
            String[] registro = registry.get(i);
            columnCount = -1;
            int cod_Mncp = 0;
            row = sheet.createRow(++rowCount);
            double levenstein = 0;
            double localidad_oficial = 0;
            double levensteinActual = 0;

            for (Integer codMunicipio : codigo_Municipio.keySet()) {
                if (registro[0].equals(codigo_Municipio.get(codMunicipio))) {
                    cod_Mncp = codMunicipio;
                    break;
                }

                try {
                    double levenstein_local = FuzzySearch.ratio(registro[0],
                            codigo_Municipio.get(codMunicipio));
                    if (levenstein_local >= levensteinActual) {
                        cod_Mncp = codMunicipio;
                        levensteinActual = levenstein_local;
                    }

                    if (levensteinActual == 100) {
                        break;
                    }
                } catch (Exception e) {
                }
            }

            for (Double cod_Loc : mncp_localidad.get(cod_Mncp).keySet()) {
                String loc = mncp_localidad.get(cod_Mncp).get(cod_Loc);

                if (registro[1].equals(loc)) {
                    localidad_oficial = cod_Loc;
                    levenstein = 101;
                }

                try {
                    double levenstein_local = FuzzySearch.ratio(registro[1], loc);
                    if (levenstein_local >= levenstein) {
                        localidad_oficial = cod_Loc;
                        levenstein = levenstein_local;
                    }

                    if (levenstein == 100) {
                        break;
                    }

                } catch (Exception e) {
                }
            }

            String mncp_oficial = codigo_Municipio.get(cod_Mncp);
            String loc_oficial = codigo_localidad.get(localidad_oficial);
            String especie = registro[2];
            double locX = localidad_x.get(localidad_oficial);
            double locY = localidad_y.get(localidad_oficial);
            int year = 0;
            int month = 0;
            System.out.println();
            try {
                year = Integer.parseInt(registro[3].split(" ")[5]);
                month = Integer.parseInt(registro[registro.length - 1]) + 1;
            } catch (Exception e) {
                year = Integer.parseInt(registro[3].substring(registro[3].length() - 4));
                month = Integer
                        .parseInt(registro[3].substring(registro[3].length() - 7, registro[3].length() - 5));
            }

            quantityFound++;

            cell = row.createCell(++columnCount);
            cell.setCellValue(especie);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(mncp_oficial);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(cod_Mncp);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(loc_oficial);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(localidad_oficial);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(month);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(year);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(locY);
            cell.setCellStyle(cs);
            cell = row.createCell(++columnCount);
            cell.setCellValue(locX);
            cell.setCellStyle(cs);
        } catch (Exception e) {
            continue;
        }
    }

    sheet.setColumnWidth(0, 5800);
    sheet.setColumnWidth(1, 5800);
    sheet.setColumnWidth(2, 3000);
    sheet.setColumnWidth(3, 5800);
    sheet.setColumnWidth(4, 3000);
    sheet.setColumnWidth(5, 3000);
    sheet.setColumnWidth(6, 3000);
    sheet.setColumnWidth(7, 6400);
    sheet.setColumnWidth(8, 6400);

    answer = "Se generaron " + quantityFound + " vector(es)";
    try (FileOutputStream outputStream = new FileOutputStream(nameOut)) {
        workbook.write(outputStream);
    } catch (IOException ex) {
        quantityFound = 0;
        answer = "Cerrar el archivo de entrada ";
    }
    return answer;
}

From source file:standarapp.algorithm.ReadRegistry.java

public String lectureRegistry(String nameFile, String nameOut, int[] col, double percent) {
    String answer = "";
    int quantityFound = 0;
    float percentFound = 0;

    /*boolean xlsx = Lecture.determineExtensionFile(nameFile);
    if (xlsx) {*//*  w w  w.  j  a va2  s.  c  o m*/
    workbook = Lecture.lectureXLSX(nameFile);
    sheet = workbook.getSheetAt(0);

    for (Row row : sheet) {
        if (row.getRowNum() < 1) {
            continue;
        }

        String[] cellsWI = new String[col.length];
        for (int i = 0; i < col.length; i++) {
            cellsWI[i] = "";
            try {
                Cell cell = row.getCell(col[i]);
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    cellsWI[i] = deleteTrash(cell.getStringCellValue());
                }
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    cellsWI[i] = String.valueOf(cell.getNumericCellValue());
                }
            } catch (Exception e) {
                continue;
            }
        }
        registry.add(cellsWI);
    }

    System.out.println("size: " + registry.size());
    int rowCount = 0;
    int columnCount = 0;
    Row row;

    sheet = workbook.createSheet();
    row = sheet.createRow(rowCount);

    Cell cell = row.createCell(columnCount);
    cell.setCellValue("Cod_Dpto");
    cell = row.createCell(++columnCount);
    cell.setCellValue("Departamento");
    cell = row.createCell(++columnCount);
    cell.setCellValue("Cod_Mncp");
    cell = row.createCell(++columnCount);
    cell.setCellValue("Municipio");
    cell = row.createCell(++columnCount);
    cell.setCellValue("Cod_Localidad");
    cell = row.createCell(++columnCount);
    cell.setCellValue("Localidad");
    cell = row.createCell(++columnCount);
    cell.setCellValue("X");
    cell = row.createCell(++columnCount);
    cell.setCellValue("Y");
    cell = row.createCell(++columnCount);
    cell.setCellValue("Levenstein");

    for (int i = 0; i < registry.size(); i++) {
        //if (i == 5 || i == 264 || i == 267) {
        try {
            String[] registro = registry.get(i);
            columnCount = -1;
            int cod_Mncp = Integer.parseInt(registro[0]) * 1000 + Integer.parseInt(registro[1]);
            row = sheet.createRow(++rowCount);
            double levenstein = 0;
            double localidad_oficial = 0;

            if (true) { //i == 36) {

                /*
                System.out.println("-------------------");
                System.out.println("Registro numero " + (i + 1));
                for (int k = 0; k < registro.length; k++) {
                    System.out.println(registro[k]);
                }
                        
                System.out.println("+++++++++++");*/
            }

            for (int j = 2; j < 6; j++) {
                for (Double cod_Loc : mncp_localidad.get(cod_Mncp).keySet()) {
                    String loc = mncp_localidad.get(cod_Mncp).get(cod_Loc);
                    try {
                        if (determinarBarrio(registro[j])) {
                            j = 6;
                            registro[6] = registro[6].concat("Barrio");
                            levenstein = 0;
                            break;
                        }

                        int levenstein_local = FuzzySearch.tokenSetRatio(registro[j], loc);
                        //System.out.println(registro[j] + " con " + loc + " dio un lev1 de " + levenstein_local);

                        if (levenstein_local > percent) {
                            int levenstein_CapaDos = FuzzySearch.ratio(registro[j], loc);
                            //System.out.println(registro[j] + " con " + loc + " dio un lev2 de " + levenstein_local);
                            if (levenstein_CapaDos > levenstein) {
                                localidad_oficial = cod_Loc;
                                levenstein = levenstein_CapaDos;
                            }
                        }
                    } catch (Exception e) {
                        continue;
                    }

                }
            }

            if (levenstein == 0) {
                if (registro[8].equals(registro[7])) {
                    //System.out.println("Entro a revisar DIRECCIN y mun de res y ocurr son iguales");
                    String direccion = registro[6];

                    if (findWords(direccion)) {
                        direccion = codigo_Municipio.get(cod_Mncp);
                        //System.err.println(cod_Mncp + " " + i + " Centro poblado " + direccion);
                    }

                    direccion = deleteTrash(direccion);

                    for (Double cod_Loc : mncp_localidad.get(cod_Mncp).keySet()) {
                        String loc = mncp_localidad.get(cod_Mncp).get(cod_Loc);
                        try {

                            int levenstein_local = FuzzySearch.partialRatio(direccion, loc);
                            if (levenstein_local > percent) {
                                int levenstein_CapaDos = FuzzySearch.ratio(direccion, loc);

                                if (levenstein_CapaDos > levenstein) {
                                    localidad_oficial = cod_Loc;
                                    levenstein = levenstein_CapaDos;
                                }
                            }
                        } catch (Exception e) {
                            break;
                        }

                    }
                }
            }

            String dpto_oficial = codigo_Dpto.get(Integer.parseInt(registro[0]));
            String mncp_oficial = codigo_Municipio.get(cod_Mncp);
            String loc_oficial = "";
            double locX = 0;
            double locY = 0;

            if (levenstein == 0) {
                loc_oficial = "Indeterminable";
            } else {
                loc_oficial = codigo_localidad.get(localidad_oficial);
                locX = localidad_x.get(localidad_oficial);
                locY = localidad_y.get(localidad_oficial);
                quantityFound++;
            }

            //System.out.println(dpto_oficial + ", " + mncp_oficial + ", " + loc_oficial);
            cell = row.createCell(++columnCount);
            cell.setCellValue(Integer.parseInt(registro[0]));
            cell = row.createCell(++columnCount);
            cell.setCellValue(dpto_oficial);
            cell = row.createCell(++columnCount);
            cell.setCellValue(cod_Mncp);
            cell = row.createCell(++columnCount);
            cell.setCellValue(mncp_oficial);
            cell = row.createCell(++columnCount);
            cell.setCellValue(localidad_oficial);
            cell = row.createCell(++columnCount);
            cell.setCellValue(loc_oficial);
            cell = row.createCell(++columnCount);
            cell.setCellValue(locX);
            cell = row.createCell(++columnCount);
            cell.setCellValue(locY);
            cell = row.createCell(++columnCount);
            cell.setCellValue(levenstein);
        } catch (Exception e) {
            continue;
        }

    }

    percentFound = ((quantityFound) * 100) / (registry.size());
    answer = "Se rescato un " + percentFound + "% de la informacin.";
    try (FileOutputStream outputStream = new FileOutputStream(nameOut)) {
        workbook.write(outputStream);
    } catch (IOException ex) {
        Logger.getLogger(ReadRegistry.class.getName()).log(Level.SEVERE, null, ex);
    }

    return answer;
}

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

License:Apache License

private TableRange findTableArea(Sheet sheet) {
    // find first row and column with contents
    int row1 = -1;
    int row2 = -1;
    TableRange lastRowArea = null;/*from ww w  .j  a v  a 2 s  . c  o m*/
    for (Row row : sheet) {
        TableRange rowArea = findRowArea(row);
        if (lastRowArea == null && rowArea != null) {
            if (row1 < 0) {
                lastRowArea = rowArea;
                row1 = row.getRowNum();
                row2 = row1;
            }
        } else if (lastRowArea != null && rowArea == null) {
            if (row2 > row1) {
                break;
            } else {
                row1 = -1;
            }
        } else if (lastRowArea == null && rowArea == null) {
            row1 = -1;
        } else if (rowArea.startColumn < lastRowArea.startColumn || rowArea.endColumn > lastRowArea.endColumn) {
            lastRowArea = null;
            row2 = -1;
        } else {
            row2 = row.getRowNum();
        }
    }
    return row1 >= 0 && lastRowArea != null
            ? new TableRange(row1, row2, lastRowArea.startColumn, lastRowArea.endColumn)
            : null;
}

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);// w ww .ja va 2s  . co 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);
}