Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum)

Source Link

Document

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

Usage

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

int replaceOneHolder(HSSFSheet sheet, int row, int col, Map<String, Object> models, String rsname) {
    // Do the replacement
    Object mod = (models.size() == 1 ? models.values().iterator().next() : models.get(rsname));
    if (mod == null)
        return NOROWCHANGE;
    if (mod instanceof TableModel)
        return replaceOneHolder(sheet, row, col, (TableModel) mod);

    // It's just a simple item; put it in
    HSSFRow row0 = sheet.getRow(row);
    HSSFCell c0 = row0.getCell((short) col);
    HSSFComment comment = c0.getCellComment();
    HSSFCellStyle style = c0.getCellStyle();
    row0.removeCell(c0);/*www  .ja v  a2  s  . co m*/
    HSSFCell c1 = row0.createCell((short) col);
    if (comment != null)
        c1.setCellComment(comment);
    if (style != null)
        c1.setCellStyle(style);
    setValue(c1, mod);
    return NOROWCHANGE;
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** @returns net number of rows inserted */
int replaceOneHolder(HSSFSheet sheet, int row, int col, TableModel mod) {
    int n = mod.getRowCount();

    // Set up proper number of rows
    insertRowsFixup(sheet, row, n, row, col, col + mod.getColumnCount(), row, row + mod.getRowCount());
    HSSFRow r0 = sheet.getRow(row + n); // Our model row

    // Fill in the data, iterating through the model...
    for (int r = 0; r < mod.getRowCount(); ++r) {
        //System.out.println("r=" + r);
        HSSFRow r2 = sheet.getRow(row + r);
        if (r2 == null)
            r2 = sheet.createRow(row + r);

        for (int c = 0; c < mod.getColumnCount(); ++c) {
            //System.out.println("  c=" + c);
            HSSFCell c2 = r2.getCell((short) (col + c));
            if (c2 == null)
                c2 = r2.createCell((short) (col + c));
            Object val = mod.getValueAt(r, c);
            setValue(c2, val);
        }//from  w ww . ja  v  a 2s .  co m
    }
    deleteRowsFixup(sheet, row + n, 1, 0, 0);
    return n - 1;
}

From source file:cliente.ReclamoPiezas.GUINuevoReclamoFabrica.java

License:Open Source License

protected void llenarFormulario() {
    String archivo = "Formulario_SRC.xls";
    Date hoy = new Date();
    String nombre_archivo = pedido_pieza.getPedido().getReclamo().getReclamante().getNombre_apellido()
            + "_pedido_" + pedido_pieza.getNumero_pedido();
    Boolean inmovilizado = pedido_pieza.getPedido().getReclamo().getInmovilizado();
    String vin = pedido_pieza.getPedido().getReclamo().getVehiculo().getVin();
    String dominio = pedido_pieza.getPedido().getReclamo().getVehiculo().getDominio();
    String nombre_cliente = pedido_pieza.getPedido().getReclamo().getReclamante().getNombre_apellido();
    String telefono = mediador.obtenerTelefono(pedido_pieza);
    try {/*from   w  w w.  j  av  a2s  .c  o  m*/

        InputStream ExcelFileToRead = new FileInputStream("Formulario_SRC.xls");

        HSSFWorkbook formulario_src = new HSSFWorkbook(ExcelFileToRead);

        HSSFSheet sheet = formulario_src.getSheetAt(0);

        HSSFRow row;
        HSSFCell cell;
        //tipo reclamo
        row = sheet.getRow(10);
        cell = row.getCell(4);
        cell.setCellValue("Repuestos");
        //inmovilizado
        if (inmovilizado) {
            row = sheet.getRow(11);
            cell = row.getCell(4);
            cell.setCellValue("Si");
        } else {
            row = sheet.getRow(11);
            cell = row.getCell(4);
            cell.setCellValue("No");
        }
        //garantia
        row = sheet.getRow(12);
        cell = row.getCell(4);
        cell.setCellValue("Si");
        //Vin
        row = sheet.getRow(15);
        cell = row.getCell(4);
        cell.setCellValue(vin);
        //dominio
        row = sheet.getRow(16);
        cell = row.getCell(4);
        cell.setCellValue(dominio);
        //modelo
        LinkedList<String> modelos = new LinkedList<String>();
        modelos.add("FLUENCE");
        modelos.add("CLIO");
        modelos.add("KANGOO");
        modelos.add("SYMBOL");
        modelos.add("LOGAN");
        modelos.add("SANDERO");
        modelos.add("MASTER");
        modelos.add("KOLEOS");
        modelos.add("MEGANE III");
        modelos.add("DUSTER");
        modelos.add("LATITUDE");
        if (modelos.contains(pedido_pieza.getPedido().getReclamo().getVehiculo().getModelo().getNombre_modelo()
                .toUpperCase())) {
            row = sheet.getRow(17);
            cell = row.getCell(4);
            cell.setCellValue(pedido_pieza.getPedido().getReclamo().getVehiculo().getModelo().getNombre_modelo()
                    .toUpperCase());
        } else {
            row = sheet.getRow(17);
            cell = row.getCell(4);
            cell.setCellValue("OTROS");
        }
        //nombre cliente
        row = sheet.getRow(11);
        cell = row.getCell(8);
        cell.setCellValue(nombre_cliente);
        //telefono
        row = sheet.getRow(12);
        cell = row.getCell(8);
        cell.setCellValue(telefono);
        //motivo
        row = sheet.getRow(21);
        cell = row.getCell(2);
        cell.setCellValue(ePMotivo.getText());
        //pieza
        row = sheet.getRow(33);
        cell = row.getCell(3);
        cell.setCellValue(pedido_pieza.getPieza().getNumero_pieza());
        row = sheet.getRow(33);
        cell = row.getCell(4);
        cell.setCellValue(pedido_pieza.getPieza().getDescripcion());
        row = sheet.getRow(33);
        cell = row.getCell(7);
        cell.setCellValue("1");
        row = sheet.getRow(33);
        cell = row.getCell(8);
        cell.setCellValue(pedido_pieza.getNumero_pedido());

        //write this workbook to an Outputstream.
        int i = 0;
        String aux = nombre_archivo;
        File formulario_guardado = new File(RootAndIp.getPath_reportes() + aux + ".xls");
        while (formulario_guardado.exists()) {
            aux = nombre_archivo + "_" + i;
            formulario_guardado = new File(RootAndIp.getPath_reportes() + aux + ".xls");
            i++;
        }

        FileOutputStream fileOut = new FileOutputStream(formulario_guardado);
        formulario_src.write(fileOut);
        fileOut.flush();
        fileOut.close();

        Desktop.getDesktop().open(formulario_guardado);

        formulario = formulario_guardado.getPath();
        btnVerFormulario.setEnabled(true);
        btnEnviarFormulario.setEnabled(true);
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:cn.baiweigang.qtaf.toolkit.dbunit.data.excel.XlsTable.java

License:Open Source License

static ITableMetaData createMetaData(String tableName, HSSFRow sampleRow, HSSFWorkbook workbook) {
    logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);

    List<Column> columnList = new ArrayList<Column>();
    List<String> primaryKeyList = new ArrayList<String>();
    for (int i = 0;; i++) {
        HSSFCell cell = sampleRow.getCell(i);
        if (cell == null) {
            break;
        }/*from   w  w  w .j  av a2s  .  com*/

        String columnName = cell.getRichStringCellValue().getString();
        if (columnName != null) {
            columnName = columnName.trim();
        }

        // Bugfix for issue ID 2818981 - if a cell has a formatting but no
        // name also ignore it
        if (columnName.length() <= 0) {
            logger.debug(
                    "The column name of column # {} is empty - will skip here assuming the last column was reached",
                    String.valueOf(i));
            break;
        }

        Column column = new Column(columnName, DataType.UNKNOWN);
        columnList.add(column);

        // Unique identification key
        byte underline = cell.getCellStyle().getFont(workbook).getUnderline();
        if (underline == 1) {
            primaryKeyList.add(columnName);
        }

    }
    Column[] columns = columnList.toArray(new Column[0]);

    if (!primaryKeyList.isEmpty()) {
        return new DefaultTableMetaData(tableName, columns,
                primaryKeyList.toArray(new String[primaryKeyList.size()]));
    } else {
        return new DefaultTableMetaData(tableName, columns);
    }

}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static List getMonthInfo(int index, HSSFRow row, HSSFSheet templateSheet) {
    List months = new ArrayList();
    row = templateSheet.getRow(index - 1);
    for (int j = 3; j < 1000; j++) {
        HSSFCell monthCell = row.getCell((short) j);
        if (monthCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }//from w  ww .j a v a2s.  co m
        } else {
            String monthValue = monthCell.getStringCellValue();
            if (monthValue != null && !monthValue.equals("")) {
                months.add(monthCell.getStringCellValue());
            }
        }
    }
    return months;
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeSubActivityTime() {
    Map userHours = getHoursInfo();
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage");
    String lastSubActivityName = null;
    List months = new ArrayList();
    int count = 0;
    for (int i = 5; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }//from  w  ww .j av a 2  s  .c  o  m
        }
        HSSFCell cell = row.getCell((short) 4);
        HSSFCell userNameCell = row.getCell((short) 2);
        if (cell != null) {
            String userName = userNameCell.getStringCellValue();
            if ("Month".equals(userName.trim()) || "User Name".equals(userName.trim())) {
                continue;
            }
            if ("Normal Working day of a Month".equals(userName.trim())) {
                months = getMonthInfo(i, row, templateSheet);
                continue;
            }
            if (lastSubActivityName == null) {
                lastSubActivityName = cell.getStringCellValue();
                count = 1;
            } else {
                String newSubActivityName = cell.getStringCellValue();
                if (newSubActivityName != null) {
                    if (newSubActivityName.equals(lastSubActivityName)) {
                        count++;
                        for (int j = 4, k = 0; j < 1000;) {
                            HSSFCell detailCell = row.getCell((short) (j));
                            if (detailCell == null) {
                                if (row.getCell((short) (j + 1)) == null) {
                                    break;
                                }
                            } else {
                                percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                        .getCellStyle().getFont(templateWbk));
                                HSSFCell precOfType = row.getCell((short) (j + 2));
                                HSSFCell precOfSubActivity = row.getCell((short) (j + 3));
                                //                                    String prec = precOfType.getStringCellValue().trim();
                                double prec = precOfType.getNumericCellValue();
                                String key = userName + "." + months.get(k);
                                TimeUsage timeUsage = (TimeUsage) userHours.get(key);

                                Region region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j));
                                region.setColumnTo((short) (j));
                                templateSheet.addMergedRegion(region);
                                templateSheet.getRow(i - 1).getCell((short) j).getCellStyle()
                                        .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 3));
                                region.setColumnTo((short) (j + 3));
                                templateSheet.addMergedRegion(region);
                                templateSheet.getRow(i - 1).getCell((short) (j + 3)).getCellStyle()
                                        .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                                if (prec > 0 && timeUsage.getPercentage() > 0) {
                                    templateSheet.getRow(i - count + 1).getCell((short) (j + 3))
                                            .setCellStyle(percentageStyle);
                                    double oldValue = templateSheet.getRow(i - count + 1)
                                            .getCell((short) (j + 3)).getNumericCellValue();
                                    templateSheet.getRow(i - count + 1).getCell((short) (j + 3))
                                            .setCellValue(oldValue + prec);
                                }
                                j += 6;
                                k++;
                                continue;
                            }
                            j++;
                        }
                    } else {
                        lastSubActivityName = newSubActivityName;
                        writePrecOfSubActivity(templateSheet, months, userName, userHours, row, i);
                        count = 1;
                    }
                } else {
                    lastSubActivityName = newSubActivityName;
                    count = 1;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeIndividualTime() {
    Map userHours = getHoursInfo();
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage");
    String lastUserName = null;/*from ww w .j  a  v a2s  .co m*/
    List months = new ArrayList();
    for (int i = 5; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        }
        HSSFCell cell = row.getCell((short) 2);
        if (cell != null) {
            if (lastUserName == null) {
                lastUserName = cell.getStringCellValue();
            } else {
                String newUserName = cell.getStringCellValue();
                if ("Month".equals(newUserName.trim()) || "User Name".equals(newUserName.trim())) {
                    continue;
                }
                if ("Normal Working day of a Month".equals(newUserName.trim())) {
                    months = new ArrayList();
                    row = templateSheet.getRow(i - 1);
                    for (int j = 3; j < 1000; j++) {
                        HSSFCell monthCell = row.getCell((short) j);
                        if (monthCell == null) {
                            if (row.getCell((short) (j + 1)) == null) {
                                break;
                            }
                        } else {
                            String monthValue = monthCell.getStringCellValue();
                            if (monthValue != null && !monthValue.equals("")) {
                                months.add(monthCell.getStringCellValue());
                            }
                        }
                    }
                    continue;
                }
                if (newUserName != null) {
                    if (newUserName.equals(lastUserName)) {
                        Region region = new Region();
                        region.setRowTo(i);
                        region.setRowFrom(i - 1);
                        region.setColumnFrom((short) 2);
                        region.setColumnTo((short) 2);
                        templateSheet.addMergedRegion(region);
                        templateSheet.getRow(i - 1).getCell((short) 2).getCellStyle()
                                .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                        HSSFCell activityCell = row.getCell((short) 3);
                        HSSFCell oldActivityCell = templateSheet.getRow(i - 1).getCell((short) 3);
                        if (activityCell.getStringCellValue().equals(oldActivityCell.getStringCellValue())) {
                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 3);
                            region.setColumnTo((short) 3);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                        }

                        for (int j = 4, k = 0; j < 1000;) {
                            HSSFCell detailCell = row.getCell((short) (j));
                            if (detailCell == null) {
                                if (row.getCell((short) (j + 1)) == null) {
                                    break;
                                }
                            } else {
                                percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                        .getCellStyle().getFont(templateWbk));
                                HSSFCell precOfType = row.getCell((short) (j + 2));
                                String prec = precOfType.getStringCellValue().trim();
                                String key = newUserName + "." + months.get(k);
                                TimeUsage timeUsage = (TimeUsage) userHours.get(key);
                                if (!prec.equals("")) {
                                    if (timeUsage.getHours() > 0) {
                                        precOfType.setCellStyle(percentageStyle);
                                        precOfType.setCellValue(
                                                (new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                                    }
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 4));
                                region.setColumnTo((short) (j + 4));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getHours() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 4))
                                            .setCellValue(timeUsage.getHours());
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 5));
                                region.setColumnTo((short) (j + 5));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getPercentage() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellStyle(percentageStyle);
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellValue(timeUsage.getPercentage());
                                }
                                j += 6;
                                k++;
                                continue;
                            }
                            j++;
                        }
                    } else {
                        lastUserName = newUserName;
                        writePrec(templateSheet, months, newUserName, userHours, row, i);
                    }
                } else {
                    lastUserName = newUserName;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writePrecOfSubActivity(HSSFSheet templateSheet, List months, String newUserName,
        Map userHours, HSSFRow row, int rowIndex) {
    for (int j = 4, k = 0; j < 1000;) {
        HSSFCell detailCell = row.getCell((short) (j));
        if (detailCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }/*from   w  w w  .  java2 s.  c  o  m*/
        } else {
            percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle()
                    .getFont(templateWbk));
            HSSFCell precOfType = row.getCell((short) (j + 2));
            HSSFCell precOfSubActivity = row.getCell((short) (j + 3));
            try {
                double prec = precOfType.getNumericCellValue();
                String key = newUserName + "." + months.get(k);
                TimeUsage timeUsage = (TimeUsage) userHours.get(key);
                if (prec > 0) {
                    if (timeUsage.getHours() > 0) {
                        precOfSubActivity.setCellStyle(percentageStyle);
                        precOfSubActivity.setCellValue(prec);
                    }
                }
            } catch (Exception e) {
            }
            j += 6;
            k++;
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writePrec(HSSFSheet templateSheet, List months, String newUserName, Map userHours,
        HSSFRow row, int rowIndex) {
    for (int j = 4, k = 0; j < 1000;) {
        HSSFCell detailCell = row.getCell((short) (j));
        if (detailCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }// ww  w .ja  v  a 2  s.  c  om
        } else {
            percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle()
                    .getFont(templateWbk));
            HSSFCell precOfType = row.getCell((short) (j + 2));
            String prec = precOfType.getStringCellValue().trim();
            String key = newUserName + "." + months.get(k);
            TimeUsage timeUsage = (TimeUsage) userHours.get(key);
            if (!prec.equals("")) {
                if (timeUsage.getHours() > 0) {
                    precOfType.setCellStyle(percentageStyle);
                    precOfType.setCellValue((new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                }
            }
            j += 6;
            k++;
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static Map getHoursInfo() {
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage (1)");
    boolean fillMap = false;
    Map userHours = new HashMap();
    List months = new ArrayList();
    for (int i = 4; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            } else {
                fillMap = false;/*from w w w  .j a v  a 2 s.  com*/
            }
        } else {
            HSSFCell userNameCell = row.getCell((short) 2);
            if (userNameCell == null) {
                continue;
            }
            String userNameCellValue = userNameCell.getStringCellValue();
            if ("Month".equals(userNameCellValue.trim())) {
                months = new ArrayList();
                for (int j = 3; j < 1000; j++) {
                    HSSFCell monthCell = row.getCell((short) j);
                    if (monthCell == null) {
                        if (row.getCell((short) (j + 1)) == null) {
                            break;
                        }
                    } else {
                        String monthValue = monthCell.getStringCellValue();
                        if (monthValue != null && !monthValue.equals("")) {
                            months.add(monthCell.getStringCellValue());
                        }
                    }
                }
                continue;
            }
            if ("Normal Working day of a Month".equals(userNameCellValue.trim())) {
                continue;
            }
            if ("User Name".equals(userNameCellValue)) {
                fillMap = true;
            } else if (fillMap) {
                for (int j = 3, k = 0; j < 1000; j++) {
                    HSSFCell detailCell = row.getCell((short) j);
                    if (detailCell == null) {
                        break;
                    } else {
                        if (j % 2 == 1) {
                            TimeUsage timeUsage = new TimeUsage();
                            String hoursStr = detailCell.getStringCellValue().trim();
                            String month = (String) months.get(k);
                            userHours.put(userNameCellValue + "." + month, timeUsage);
                            if (hoursStr != null && !hoursStr.equals("")) {
                                double hours = (new BigDecimal(hoursStr)).doubleValue();
                                timeUsage.setHours(hours);
                            }
                        } else {
                            String month = (String) months.get(k);
                            TimeUsage timeUsage = (TimeUsage) userHours.get(userNameCellValue + "." + month);
                            timeUsage.setPercentage(detailCell.getNumericCellValue());
                            k++;
                        }
                    }
                }
            }
        }
    }
    return userHours;
}