Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow.

Prototype

@Override
public XSSFRow getRow(int rownum)

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:at.metalab.m68k.dmnimport.DmnXslx.java

License:Apache License

public static String getColumnAllowedValues(int column, XSSFSheet sheet) {
    return sheet.getRow(5).getCell(column).getStringCellValue();
}

From source file:at.metalab.m68k.dmnimport.DmnXslx.java

License:Apache License

public static boolean isRowUsed(int row, XSSFSheet sheet) {
    return sheet.getRow(row).getCell(0, Row.RETURN_BLANK_AS_NULL) != null;
}

From source file:at.nhmwien.schema_mapping_tool.schemaReaders.XlsxConverter.java

License:Apache License

public LinkedHashMap<String, LinkedHashMap> parseFile(InputStream inputFile) {
    LinkedHashMap<String, LinkedHashMap> fields = null;

    try {/* w  w w.ja v a  2 s .  c o  m*/

        XSSFWorkbook workbook = new XSSFWorkbook(inputFile);
        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow row = sheet.getRow(0);

        Iterator<Cell> cellIt = row.cellIterator();

        fields = new LinkedHashMap();

        while (cellIt.hasNext()) {
            Cell currCell = cellIt.next();

            // Ignore any non-string cells (because they are not suitable as headers)
            if (currCell.getCellType() != Cell.CELL_TYPE_STRING)
                continue;

            LinkedHashMap fieldInfo = new LinkedHashMap();
            fieldInfo.put("name", currCell.getStringCellValue());
            fieldInfo.put("subfields", null);

            fields.put(currCell.getStringCellValue(), fieldInfo);
        }

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

    return fields;
}

From source file:be.thomasmore.service.FileUploadServiceImp1.java

@Override
public void handleFileUpload(FileUploadEvent event) {
    clearVars();/*from w  w w.j av  a  2  s.  c o  m*/
    InputStream file;
    XSSFWorkbook workbook = null;
    try {
        //geupload excel bestand inlezen
        file = event.getFile().getInputstream();
        workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);

        //de excell overlopen en de gegevens eruit halen
        //klas ophalen
        int i = 0;
        XSSFRow row = sheet.getRow(i++);
        klas.setNaam(row.getCell(1).getStringCellValue());// =>naam klas (bv 5a)
        //vak ophalen
        row = sheet.getRow(i++);
        vak.setNaam(row.getCell(1).getStringCellValue());// => naam vak (bv java)
        //test ophalen
        row = sheet.getRow(i++);
        test.setNaam(row.getCell(1).getStringCellValue()); // => naam test
        row = sheet.getRow(i++);
        test.setTotaal((int) row.getCell(1).getNumericCellValue());// => totaal test
        //vak.setTesten(new ArrayList<Test>(test));
        //test.setVak(vak);
        i += 2;
        //studenten +scores ophalen
        while (i <= sheet.getLastRowNum()) {
            row = sheet.getRow(i++);
            Student student = new Student();
            student.setStudentennummer(String.valueOf((int) row.getCell(0).getNumericCellValue()));
            student.setNaam(row.getCell(1).getStringCellValue());
            Score score = new Score();
            score.setKlas(klas);
            score.setStudent(student);
            score.setTest(test);
            score.setScore((int) row.getCell(2).getNumericCellValue());
            scores.add(score);
        }
        test.setScores(scores);
        klas.setScores(scores);

        // klas.setStudenten(studenten);
    } catch (IOException e) {
        // facesContext
        //   .addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        e.printStackTrace();
    }
}

From source file:CDatos.Excel.java

/**
 * Lee una hoja de un excel y devuelve una matriz con los datos
 * @return Matriz con los datos del excel
 *///from   w  ww.j  a  v a  2  s .c  o m
public ArrayList getDatosHoja() {
    ArrayList<ArrayList> filas = new ArrayList();
    XSSFSheet sheet = workbook.getSheetAt(hoja);
    int numColumnas = -1;

    // Recorremos fila a fila
    for (int r = 0; r <= sheet.getLastRowNum(); r++) {
        ArrayList<String> celdas = new ArrayList();
        XSSFRow row = sheet.getRow(r);
        if (row == null)
            break;
        else {
            // En la primera fila se leen las cabeceras, por lo que aprovechamos para 
            // guardar el nmero de columnas porque cuando una fila tiene celdas vacas el tamao 
            // de la lista es menor
            if (numColumnas == -1)
                numColumnas = row.getLastCellNum();
            // Recorremos celda a celda
            for (int c = 0; c < numColumnas; c++) {
                XSSFCell cell = row.getCell(c);
                String cellValue = "";
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat formateador = new SimpleDateFormat("yyyy-MM-dd");
                            //cellValue = cell.getDateCellValue().toString();
                            cellValue = formateador.format(cell.getDateCellValue());
                        } else {
                            cellValue = (Integer.toString((int) cell.getNumericCellValue()));
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    }
                }
                celdas.add(cellValue);
            }
            filas.add(celdas);
        }
    }
    return filas;
}

From source file:cn.comgroup.tzmedia.server.report.CustomerOrderReport.java

public CustomerOrderReportResult runOrderReport(String deployPath, List<CustomerOrder> orders)
        throws ParseException, FileNotFoundException, IOException {
    String reportTemplate = deployPath + File.separator + "template" + File.separator
            + "CustomerOrderReport.xlsx";
    final XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(reportTemplate));
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    XSSFSheet sheet = workbook.getSheetAt(0);
    int startRow = 1;
    for (CustomerOrder co : orders) {
        XSSFRow row = sheet.getRow(startRow);
        if (row == null) {
            row = sheet.createRow(startRow);
        }/*from   w  w w  .j av  a2 s . c  o  m*/
        XSSFCell cellOrderNumber = row.getCell(0);
        if (cellOrderNumber == null) {
            cellOrderNumber = row.createCell(0);
        }

        XSSFCell cellOrderDate = row.getCell(1);
        if (cellOrderDate == null) {
            cellOrderDate = row.createCell(1);
        }
        XSSFCell cellOrderType = row.getCell(2);
        if (cellOrderType == null) {
            cellOrderType = row.createCell(2);
        }
        XSSFCell cellOrderStatus = row.getCell(3);
        if (cellOrderStatus == null) {
            cellOrderStatus = row.createCell(3);
        }
        XSSFCell cellETN = row.getCell(4);
        if (cellETN == null) {
            cellETN = row.createCell(4);
        }
        XSSFCell cellShop = row.getCell(5);
        if (cellShop == null) {
            cellShop = row.createCell(5);
        }
        XSSFCell cellUserId = row.getCell(6);
        if (cellUserId == null) {
            cellUserId = row.createCell(6);
        }
        XSSFCell cellUserName = row.getCell(7);
        if (cellUserName == null) {
            cellUserName = row.createCell(7);
        }
        XSSFCell cellPaymentTerm = row.getCell(8);
        if (cellPaymentTerm == null) {
            cellPaymentTerm = row.createCell(8);
        }
        XSSFCell cellOrderAmount = row.getCell(9);
        if (cellOrderAmount == null) {
            cellOrderAmount = row.createCell(9);
        }
        XSSFCell cellCouponAmount = row.getCell(10);
        if (cellCouponAmount == null) {
            cellCouponAmount = row.createCell(10);
        }
        cellOrderNumber.setCellValue(co.getOrderNumber());
        if (co.getOrderDate() != null) {
            cellOrderDate.setCellValue(dateFormat.format(co.getOrderDate().getTime()));
        } else {
            cellOrderDate.setCellValue(dateFormat.format(co.getOrderTime().getTime()));
        }

        cellOrderType.setCellValue(co.getOrderType().toString());
        cellOrderStatus.setCellValue(co.getOrderStatus().toString());
        if (co.getExternalTransactionNumber() != null) {
            cellETN.setCellValue(co.getExternalTransactionNumber());
        }

        cellShop.setCellValue(co.getShopName());
        cellUserId.setCellValue(co.getUserId());
        cellUserName.setCellValue(co.getUserName());
        cellPaymentTerm.setCellValue(co.getPaymentTerm().toString());
        cellOrderAmount.setCellValue(co.getOrderAmount());
        cellCouponAmount.setCellValue(co.getCouponAmount());
        startRow++;
        System.out.println(startRow + " orders in the report");
    }
    DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd-HHMMSS");
    String reportName = "CustomerOrderReport" + dateTimeFormat.format(new Date()) + ".xlsx";
    String reportPath = deployPath + File.separator + reportName;
    FileOutputStream fos = new FileOutputStream(reportPath);
    try (BufferedOutputStream bout = new BufferedOutputStream(fos)) {
        workbook.write(bout);
        bout.flush();
    }
    return new CustomerOrderReportResult(reportName);
}

From source file:co.com.runt.runistac.logica.ReporteLogica.java

public static byte[] generar(InputStream plantilla, Map<String, String> parametros, List<Object[]> datos)
        throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook(plantilla);
    XSSFSheet mySheet = wb.getSheetAt(0);
    for (int i = 0; i < mySheet.getLastRowNum(); i++) {
        Row row = mySheet.getRow(i);
        if (row != null && row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.STRING) {
            for (String key : parametros.keySet()) {
                String valor = row.getCell(0).getStringCellValue();
                valor = valor.replaceAll("\\{" + key + "\\}", parametros.get(key));
                row.getCell(0).setCellValue(valor);
            }//from www .j a va  2 s  .c  o m
        }
    }

    int rows = mySheet.getLastRowNum();
    int i = 0;
    Row base = mySheet.getRow(rows);
    CellStyle[] cs = null;
    if (!datos.isEmpty()) {
        int cant = datos.get(0).length;
        cs = new CellStyle[cant];
        for (int j = 0; j < cant; j++) {
            cs[j] = base.getCell(j).getCellStyle();
        }
    }
    for (Object[] o : datos) {
        Row row = mySheet.createRow(rows + i);
        for (int j = 0; j < o.length; j++) {
            Cell c = row.createCell(j);
            String value = "";
            if (o[j] != null) {
                if (o[j] instanceof String) {
                    value = (String) o[j];
                    c.setCellValue(value);
                } else if (o[j] instanceof Integer) {//integer
                    c.setCellValue((Integer) o[j]);
                } else if (o[j] instanceof Double) {
                    c.setCellValue((Double) o[j]);
                } else if (o[j] instanceof Float) {
                    c.setCellValue((Float) o[j]);
                } else if (o[j] instanceof BigDecimal) {
                    c.setCellValue(((BigDecimal) o[j]).doubleValue());
                } else if (o[j] instanceof Date) {
                    c.setCellValue(((Date) o[j]));
                } else if (o[j] instanceof BigInteger) {
                    c.setCellValue(((BigInteger) o[j]).intValue());
                } else {
                    c.setCellValue(o[j].toString());
                    System.out.println("No se encontro tipo: " + j + "-" + o[j].getClass());
                }
            }
            c.setCellStyle(cs[j]);
        }
        i++;
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);

    return baos.toByteArray();
}

From source file:com.accenture.bean.PlanoExcel.java

public void extraiPlanilha() {
    try {/*from   w w w  .  j ava 2s . com*/
        //Leitura
        FileInputStream arquivo = new FileInputStream(new File(fileName));

        // Carregando workbook
        XSSFWorkbook wb = new XSSFWorkbook(arquivo);
        // Selecionando a primeira aba
        XSSFSheet s = wb.getSheetAt(1);

        // Caso queira pegar valor por referencia
        CellReference cellReference = new CellReference("M8");
        Row row = s.getRow(cellReference.getRow());
        Cell cell = row.getCell(cellReference.getCol());
        System.out.println("Valor Refe:" + cell.getStringCellValue());

        // Fazendo um loop em todas as linhas
        for (Row rowFor : s) {
            // FAzendo loop em todas as colunas
            for (Cell cellFor : rowFor) {
                try {
                    // Verifica o tipo de dado
                    if (cellFor.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        // Na coluna 6 tenho um formato de data
                        if (cellFor.getColumnIndex() == 6) {
                            // Se estiver no formato de data
                            if (DateUtil.isCellDateFormatted(cellFor)) {
                                // Formatar para o padrao brasileiro
                                Date d = cellFor.getDateCellValue();
                                DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                                System.out.println(df.format(d));
                            }
                        } else {
                            // Mostrar numerico
                            System.out.println(cellFor.getNumericCellValue());
                        }
                    } else {
                        // Mostrar String
                        System.out.println(cellFor.getStringCellValue());
                    }
                } catch (Exception e) {
                    // Mostrar Erro
                    System.out.println(e.getMessage());
                }
            }
            // Mostrar pulo de linha
            System.out.println("------------------------");
        }

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

From source file:com.accenture.control.ExcelDAO.java

public String[] carregaPlanilhaFuncionalidade() throws IOException, ClassNotFoundException, SQLException {

    Plano plano = new Plano();
    ManipulaDadosSQLite banco = new ManipulaDadosSQLite();
    String[] funcionalidade = null;
    try {//from w w  w . j a v  a 2  s  .  co  m
        FileInputStream arquivo = new FileInputStream(new File(ExcelDAO.fileName));
        XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
        //setado a planilha de configuraes
        XSSFSheet sheetPlano = workbook.getSheetAt(2);
        //linha pa
        int linha = 1;
        int coluna = 4;

        funcionalidade = new String[sheetPlano.getLastRowNum()];
        int index = 0;
        for (int count = 1; count < sheetPlano.getLastRowNum(); count++) {
            Row row = sheetPlano.getRow(count);
            for (int countColuna = 0; countColuna < 1; countColuna++) {
                Cell cell = row.getCell(coluna, Row.CREATE_NULL_AS_BLANK);
                System.out.println(cell.getColumnIndex() + "-" + cell.getRowIndex());
                if (cell.getCellType() == CELL_TYPE_BLANK) {
                    System.out.println("Campo vazio");
                } else if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                    double valor = cell.getNumericCellValue();

                    System.out.println(valor);
                } else {
                    String valor = cell.getStringCellValue();
                    System.out.println(valor);
                    funcionalidade[index] = valor;
                    System.out.println(funcionalidade[index]);
                    banco.insertTabelaConf("TB_FUNCIONALIDADE", "DESC_FUNCIONALIDADE", valor);
                    index++;
                }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelDAO.class.getName()).log(Level.SEVERE, null, ex);

    }
    return funcionalidade;
}

From source file:com.accenture.control.ExcelDAO.java

public String[] carregaPlanilhaSistemaMaster() throws IOException {

    Plano plano = new Plano();

    String[] sistemaMaster = null;
    try {//from w w w .j  av a2s . c o  m
        FileInputStream arquivo = new FileInputStream(new File(ExcelDAO.fileName));
        XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
        //setado a planilha de configuraes
        XSSFSheet sheetPlano = workbook.getSheetAt(2);
        //linha pa
        int linha = 1;
        int coluna = 2;

        sistemaMaster = new String[sheetPlano.getLastRowNum()];
        int index = 0;
        for (int count = 1; count < sheetPlano.getLastRowNum(); count++) {
            Row row = sheetPlano.getRow(count);
            for (int countColuna = 0; countColuna < 1; countColuna++) {
                Cell cell = row.getCell(coluna, Row.CREATE_NULL_AS_BLANK);
                System.out.println(cell.getColumnIndex() + "-" + cell.getRowIndex());
                if (cell.getCellType() == CELL_TYPE_BLANK) {
                    System.out.println("Campo vazio");
                } else if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                    double valor = cell.getNumericCellValue();
                    System.out.println(valor);
                } else {
                    String valor = cell.getStringCellValue();
                    System.out.println(valor);
                    sistemaMaster[index] = valor;
                    System.out.println(sistemaMaster[index]);
                    index++;
                }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelDAO.class.getName()).log(Level.SEVERE, null, ex);
    }
    return sistemaMaster;
}