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

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

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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:cartel.DynamicDegreeCalculation.java

License:Open Source License

public void compute(Graph graph) throws FileNotFoundException, IOException {

    Map<String, Integer> weightedDegreeForOneCountryfForOneYear;

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("weighted degree");
    Row row;
    Cell cell;//  w  ww.  jav  a  2 s .  com
    Map<String, Integer> countryIndices = new TreeMap();

    int index = 0;
    for (String country : CartelDynamic.europeanCountries) {
        index++;
        countryIndices.put(country, index);
    }

    //COLUMNS HEADER
    row = sheet.createRow((short) 0);
    index = 1;
    for (int i = 1948; i < 2009; i++) {
        cell = row.createCell(index);
        index++;
        cell.setCellValue(String.valueOf(i));
    }

    //CREATING EMPTY CELLS FOR EACH ROW
    for (String country : countryIndices.keySet()) {
        row = sheet.createRow((countryIndices.get(country)));
        index = 0;
        for (int i = 1948; i <= 2009; i++) {
            row.createCell(index);
            index++;
        }
    }

    //FILLING FIRST COLUMN WITH COUNTRIES
    for (String country : countryIndices.keySet()) {
        row = sheet.getRow(countryIndices.get(country));
        row.getCell(0).setCellValue(country);
    }

    int indexYear = 1;
    for (int i = 1948; i < 2009; i++) {
        weightedDegreeForOneCountryfForOneYear = new TreeMap();
        for (Node node : graph.getNodes()) {
            String nodeLabel = node.getLabel();
            int sumDegrees = 0;

            for (Edge edge : graph.getAllEdges()) {
                if (!edge.getSource().getLabel().equals(nodeLabel)
                        & !edge.getTarget().getLabel().equals(nodeLabel)) {
                    continue;
                }
                if (edge.getSource().getLabel().equals(edge.getTarget().getLabel())) {
                    continue;
                }
                AttributeValueList attributeValueList = edge.getAttributeValues();
                for (AttributeValue attributeValue : attributeValueList) {
                    if (!attributeValue.getAttribute().getTitle().equals("freq")) {
                        continue;
                    }

                    if (((Integer) attributeValue.getStartValue()) != i) {
                        continue;
                    }
                    sumDegrees = sumDegrees + Integer.parseInt(attributeValue.getValue());
                }
            }
            sumDegrees = sumDegrees / 2;
            row = sheet.getRow(countryIndices.get(nodeLabel));
            cell = row.getCell(indexYear);
            cell.setCellValue(String.valueOf(sumDegrees));

        }
        indexYear++;
    }
    String pathFile = "D:/workbook weighted degree.xlsx";
    FileOutputStream fileOut = new FileOutputStream(pathFile);

    wb.write(fileOut);

    fileOut.close();

}

From source file:Categorization.CategoriesLoader.java

License:Open Source License

public static void echoAsCSV(Sheet sheet) throws IOException {
    Row row;
    int startingRow = 2;
    Category category;/* w  ww.j av  a 2s .c  om*/
    boolean breakNow = false;
    for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
        if (breakNow) {
            break;
        }
        row = sheet.getRow(i);
        if (row == null) {
            break;
        }
        category = new Category();

        for (int j = 1; j < row.getLastCellNum(); j++) {

            //label of the category
            if (j == 1) {
                if (row.getCell(j).getStringCellValue().isEmpty()
                        || row.getCell(j).getStringCellValue() == null) {
                    breakNow = true;
                    break;
                }
                category.setCategoryName("CAT_" + row.getCell(j).getStringCellValue());
            }
            //if a cell is null, the row is empty.
            if (row.getCell(j) == null) {
                continue;
            }

            //check the keywords
            if (j > 1 & j <= maxColKeyWords) {
                System.out.println(row.getCell(j).getStringCellValue());
                if (row.getCell(j).getStringCellValue().startsWith("NOT ")) {
                    category.addExclusionKeyword(
                            row.getCell(j).getStringCellValue().toLowerCase().substring(4).trim());
                } else {
                    category.addKeyword(row.getCell(j).getStringCellValue().toLowerCase().trim());
                }
            }
            //check the min number of keywords that should be present in the text to match a classification
            if (j == maxColKeyWords + 1) {
                System.out.println("min Words: " + row.getCell(j).getStringCellValue());
                category.setMinNumberKeywords(Integer.parseInt(row.getCell(j).getStringCellValue()));
            }

            //check if single terms can lead to a direct classification
            if (j == maxColKeyWords + 2) {
                String[] directWords = row.getCell(j).getStringCellValue().split(";");
                for (String string : directWords) {
                    category.addDecisiveKeyword(string.toLowerCase().trim());
                }
            }
            //what supercategory the category belongs to
            if (j == maxColKeyWords + 3) {
                category.setSuperCategory(row.getCell(j).getStringCellValue());
            }

        }
        if (!category.getMinNumberKeywords().equals(0)) {
            categories.add(category);
        }
    }
}

From source file:cdiscisa.StreamUtil.java

private static Map<String, String> llenarAbreviaturas(Workbook wbAbrev) {
        Map<String, String> abreviaturas = new HashMap<>();
        Sheet wbListaSheet = wbAbrev.getSheetAt(0);
        Iterator<Row> rowIterator = wbListaSheet.iterator();
        Row row = null;

        while (rowIterator.hasNext()) {
            row = rowIterator.next();/*from   w w  w . ja  v  a 2  s  .co m*/
            if (row.getCell(0) == null || row.getCell(0).toString().isEmpty()) {
                break;
            } else {
                abreviaturas.put(row.getCell(0).getStringCellValue().trim(),
                        row.getCell(1).getStringCellValue().trim());
            }
        }

        return abreviaturas;

    }

From source file:cdiscisa.StreamUtil.java

private static ArrayList<Participante> llenarParticipantes(Workbook wbLista, String savePath) throws Exception {

        ArrayList<Participante> listaParticipantes = new ArrayList<>();
        Sheet wbListaSheet = wbLista.getSheetAt(0);
        Iterator<Row> rowIterator = wbListaSheet.iterator();
        String regex = "[A-Z]{1}[AEIOU]{1}[A-Z]{2}[0-9]{2}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}[HM]{1}(AS|BC|BS|CC|CS|CH|CL|CM|DF|DG|GT|GR|HG|JC|MC|MN|MS|NT|NL|OC|PL|QT|QR|SP|SL|SR|TC|TS|TL|VZ|YN|ZS|NE)[0-9A-Z]{5}$";
        /*//ww  w .java  2s  . co  m
        "[A-Z]{2}([AEIOU]{1}|X)[A-Z]{2}[0-9]{2}" +
        "[0-9]{2}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}" +
        "[HM]{1}" +
        "(AS|BC|BS|CC|CS|CH|CL|CM|DF|DG|GT|GR|HG|JC|MC|MN|MS|NT|NL|OC|PL|QT|QR|SP|SL|SR|TC|TS|TL|VZ|YN|ZS|NE)" +
        "[0-9A-Z]{5}$";
        */
        Boolean sw = false;
        File file = new File(savePath + File.separator + "CURP_incorrecto.csv");
        if (!file.exists()) {
            file.createNewFile();
        }

        FileWriter fw = new FileWriter(file.getAbsoluteFile());
        fw.write("");

        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();
            try {
                if (row.getCell(2) != null && row.getCell(2).getStringCellValue().equalsIgnoreCase("# Det.")) {
                    break;
                }
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "Error leyendo la columna Determinante del archivo Excel de Lista de participantes ");
            }
        }

        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();

            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);

            if (row.getCell(2) == null || row.getCell(2).getStringCellValue().isEmpty()) {
                break;
            }

            Participante p = new Participante();

            if (row.getCell(2) != null && row.getCell(2).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(2).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.determinante = row.getCell(2).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo determinante del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(3) != null && row.getCell(3).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(3).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.sucursal = row.getCell(3).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la sucursal del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(4) != null && row.getCell(4).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(4).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.nombre = row.getCell(4).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Nombre del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(5) != null && row.getCell(5).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(5).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.apellidos = row.getCell(5).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Apellidos del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(6) != null && row.getCell(6).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(6).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.curp = row.getCell(6).getStringCellValue().trim();
                    Pattern pat = Pattern.compile(regex);

                    Matcher mat = pat.matcher(p.curp);

                    if (!mat.matches()) {
                        fw.append(p.curp + "," + p.nombre + " " + p.apellidos + "\n");
                        sw = true;
                        //JOptionPane.showMessageDialog(null,"El CURP " + p.curp + " del participante " + p.nombre + " " + p.apellidos + " parece estar mal formado, favor de revisarlo");
                    } else {
                        p.curp_malformed = false;
                    }
                    /*if (p.curp.length()>18){
                        throw new netoCustomException("Hay un error en el CURP: " + p.curp + " de " + p.nombre + " " + p.apellidos + ".\n Debera tener 18 caracteres, y tiene " + p.curp.length());
                    } */
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna CURP del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(7) != null && row.getCell(7).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(7).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.area_puesto = row.getCell(7).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Area Puesto del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(8) != null && row.getCell(8).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(8).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.area_tematica = row.getCell(8).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Area Tematica del archivo Excel de Lista de participantes  ");
                }
            }

            p.aprovado = false;
            if (row.getCell(9) != null && row.getCell(9).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(9).getCellType() != Cell.CELL_TYPE_ERROR
                    && row.getCell(9).getStringCellValue().equalsIgnoreCase("Aprobado")) {
                try {
                    p.aprovado = true;
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Aprobado del archivo Excel de Lista de participantes  ");
                }
            }

            listaParticipantes.add(p);

        }

        fw.close();

        if (sw) {
            JOptionPane.showMessageDialog(null,
                    "Algunos participantes parecen tener el CURP incorrecto. Este archivo contiene los errores: \n\n"
                            + savePath + File.separator + "CURP_incorrecto.csv");
        }

        return listaParticipantes;
        // method body
    }

From source file:cfdi.clases.db.DerbyUtilities.java

License:Open Source License

/**
 * Exporta los registros de de CFDI datos generales o su detalle, con el filtro que se
 * haya utilizado en la interface grfica
 * /*from   w  w  w .  j av  a 2 s  .c  om*/
 * @param query es el query filtradn para la tabla de CFDI y CFDI_DETALLE
 * @param nombre nombre del archivo 
 * @param path directorio donde se va a crear el archivo de excel
 * @return the boolean
 */
public boolean exportarExcel(String query, String nombre, String path) {
    Connection connection = null;
    Statement st = null;
    ResultSet rs = null;
    boolean respuesta = false;
    BoneCP connectionPool = null;
    try {
        Class.forName(propiedades.getProperty("DB_DRIVER"));
        // setup the connection pool
        BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl(propiedades.getProperty("DB_SERVER")); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb
        config.setUsername(propiedades.getProperty("DB_USER"));
        config.setPassword(propiedades.getProperty("DB_PASSWORD"));
        config.setMinConnectionsPerPartition(5);
        config.setMaxConnectionsPerPartition(10);
        config.setPartitionCount(1);
        connectionPool = new BoneCP(config); // setup the connection pool
        FileOutputStream fileOut = new FileOutputStream(path + nombre + ".xlsx");
        connection = connectionPool.getConnection(); // fetch a connection

        if (connection != null) {
            st = connection.createStatement();
            rs = st.executeQuery(query);
            ResultSetMetaData metaData = rs.getMetaData();
            int count = metaData.getColumnCount();
            SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
            Sheet sheet = workbook.createSheet(nombre);
            int rownum = 0;
            Row row = sheet.createRow(rownum++);
            CellStyle stylec = workbook.createCellStyle();
            stylec.setBorderBottom(CellStyle.BORDER_THIN);
            stylec.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            Font fontc = workbook.createFont();
            fontc.setBoldweight(Font.BOLDWEIGHT_BOLD);
            stylec.setFont(fontc);
            for (int i = 1; i <= count; i++) {
                row.createCell(i).setCellValue(metaData.getColumnName(i));
                row.getCell(i).setCellStyle(stylec);
            }
            while (rs.next()) {
                Row rowh = sheet.createRow(rownum++);
                for (int i = 1; i <= count; i++) {
                    if (metaData.getColumnTypeName(i).equalsIgnoreCase("INT")
                            || metaData.getColumnTypeName(i).equalsIgnoreCase("INT UNSIGNED"))
                        rowh.createCell(i).setCellValue(rs.getInt(i));
                    else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DOUBLE"))
                        rowh.createCell(i).setCellValue(rs.getDouble(i));
                    else
                        rowh.createCell(i).setCellValue(rs.getString(i));
                }
            }
            /*if(rownum<5000){
            for (int i = 1; i <= count; i++)
                sheet.autoSizeColumn(i); 
            }*/
            try {
                workbook.write(fileOut);
                fileOut.flush();
                fileOut.close();

            } catch (FileNotFoundException e) {
                System.out.println("Error: export 1");
            } catch (IOException e) {
                System.out.println("Error: export 2");
            }
            respuesta = true;
            connectionPool.shutdown();
        }
    } catch (SQLException e) {
        System.out.println("Error: insertDatos 3");
        logger.log(Level.SEVERE, null, e);
    } catch (ClassNotFoundException ex) {
        logger.log(Level.SEVERE, null, ex);
    } catch (Exception ex) {
        System.out.println("Error: insertDatos 5");
        logger.log(Level.SEVERE, null, ex);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println("Error: insertDatos 4");
                logger.log(Level.SEVERE, null, e);
            }
        }
    }
    return respuesta;
}

From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java

/***
 * Prototype function. Not yet functional.
 * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template.
 * @param language Language of the label in the Excel file
 * @param rows Arrays of rows to include in the listing
 * @param excelFilePath Path of the outputed file
 * @throws FileNotFoundException//from   ww  w .  ja va  2 s . c  o m
 * @throws IOException
 * @throws InvalidFormatException 
 */
public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows,
        String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException {
    //open template
    URL url = Translation.class.getClassLoader()
            .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx");
    //Workbook wb = WorkbookFactory.create(new File(url.getPath()));
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath())));

    Sheet sheet = wb.getSheetAt(0);

    //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html
    //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook
    Row row;
    Cell cell;

    for (int i = 0; i < 20; i++) {
        row = sheet.getRow(i + 1);
        if (row == null) {
            row = sheet.createRow(i + 1);
        }

        for (int j = 0; j < 3; j++) {
            cell = row.getCell(j);
            if (cell == null) {
                cell = row.createCell(j);
            }

            switch (j) {
            case 0:
                cell.setCellValue(i);
                break;
            case 1:
                cell.setCellValue(i * j * -1);
                break;
            case 2:
                cell.setCellValue(i * j);
                break;
            }
        }
    }

    //redefine data range
    //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html
    XSSFSheet sheet1 = wb.getSheetAt(0);
    XSSFTable table = sheet1.getTables().get(0);
    CTTable cttable = table.getCTTable();

    AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2));
    /* Set Range to the Table */
    cttable.setRef(my_data_range.formatAsString());
    // cttable.setDisplayName("DATEN");      /* this is the display name of the table */
    //cttable.setName("test");    /* This maps to "displayName" attribute in &lt;table&gt;, OOXML */
    //cttable.setId(1L); //id attribute against table as long value

    /*
    //redefine data range
    Name rangeCell = wb.getName("DATEN");
    //Set new range for named range 
    //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface);
    String reference = sheet.getSheetName() + "!$A$2:$C$20";
    //Assigns range value to named range
    rangeCell.setRefersToFormula(reference);
    */

    //write to the file
    FileOutputStream fileOut = new FileOutputStream(excelFilePath);
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

From source file:ch.ledcom.log4jtools.config.ConfigReader.java

License:Apache License

private String readCell(Row row, int cellnum) {
    if (row == null) {
        return null;
    }/*from  w ww .j  a  v  a  2s. c  om*/
    Cell cell = row.getCell(cellnum);
    return cell == null ? (String) null : cell.getStringCellValue().trim();
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private void emptyWorksheetColumns(String wsName, int rowsFrom, List<Integer> colsToDelete) {
    String trainpathsNoOperatingDayMarker = getPropertyValue(tpaProps.TRAINPATHS_NO_OPERATING_DAY_MARKER);

    Sheet sheet = wb.getSheet(wsName);/*  w  w  w. j ava 2  s. co m*/
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        for (Integer j : colsToDelete) {
            Cell cell = row.getCell(j);
            if (cell != null && !formatter.formatCellValue(cell).equals(trainpathsNoOperatingDayMarker)) {
                cell.setCellValue((String) null);
            }
        }
    }
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

/**
 * @param wsName//w ww .  j av a  2 s  .  com
 * @param rowsFrom
 * @param colsToDelete
 */
private void resetRequestAllocations(String wsName, int rowsFrom, List<Integer> colsToDelete) {
    String requestsAllocatedDayMarker = getPropertyValue(tpaProps.REQUESTS_ALLOCATED_DAY_MARKER);
    String requestsRequestedDayMarker = getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER);

    Sheet sheet = wb.getSheet(wsName);
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        for (Integer j : colsToDelete) {
            Cell cell = row.getCell(j);
            if (cell != null && formatter.formatCellValue(cell).equals(requestsAllocatedDayMarker)) {
                cell.setCellValue(requestsRequestedDayMarker);
            }
        }
    }
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

/**
 * Put the train path ids in the format <train_path_slot_id><hour_of_day_>-<three_digit_sequence_number_within_hour>
 *
 * @param wsName//from ww  w.  j  a v  a  2  s .  com
 * @param rowsFrom
 * @param cols
 */
private void correctTrainPathIds(String wsName, int rowsFrom, Map<ColumnIdentifier, Integer> cols) {

    Sheet sheet = wb.getSheet(wsName);
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }

        Map<ColumnIdentifier, String> line = getWorksheetPointerStringMap(cols, row);
        String uncorrectedSlotName = line.get(trainPathLayout.ID);
        if (StringUtils.isBlank(uncorrectedSlotName)) {
            continue;
        }
        try {
            LocalTime startTime = LocalTime.parse(line.get(trainPathLayout.DEPTIME));

            String correctedSlotName = getNextSlotId(wsName, startTime.getHourOfDay());
            if (!correctedSlotName.equals(uncorrectedSlotName)) {
                LOGGER.warn("Correcting slot name " + uncorrectedSlotName + " => " + correctedSlotName);
                row.getCell(cols.get(trainPathLayout.ID)).setCellValue(correctedSlotName);
            }
        } catch (IllegalArgumentException e) {
            LOGGER.warn(corrupt_input,
                    "Illegal start time \"" + line.get(trainPathLayout.DEPTIME) + "\" for slot "
                            + uncorrectedSlotName + " in sheet " + wsName + " found; skipping this slot.",
                    e);
            continue;
        }
    }
}