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

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

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

Introduction

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

Prototype

@Override
    public int getLastRowNum()

Source Link

Usage

From source file:appform.Complaint.java

public static void updateComplaint(String complaint) {
    try {//w  ww  . jav a2  s  .  co  m
        FileInputStream file = new FileInputStream(new File("Complaint.xls"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        HashMap data;
        data = new HashMap<>();
        data.put("7", new Object[] { complaint });
        Set keyset = data.keySet();
        int rownum = sheet.getLastRowNum() + 1;
        for (Object key : keyset) {
            Row row = sheet.createRow(rownum++);
            Object[] objArr = (Object[]) data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                if (obj instanceof String)
                    cell.setCellValue((String) obj);
                else if (obj instanceof Double)
                    cell.setCellValue((Double) obj);
            }
        }
        FileOutputStream out = new FileOutputStream(new File("Complaint.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Complaint written successfully..");

        //</string></string,></string,>
    }

    catch (Exception e) {
    }
}

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

@Override
public void handleFileUpload(FileUploadEvent event) {
    clearVars();//from w  w w . j  a v  a  2  s . co 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 . ja va2 s .com
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: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);/*from  w w  w .ja  va2  s.c o  m*/
        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);
            }
        }
    }

    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.control.ExcelDAO.java

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

    Plano plano = new Plano();
    ManipulaDadosSQLite banco = new ManipulaDadosSQLite();
    String[] funcionalidade = null;
    try {/*from   w  ww.java2 s  .  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 = 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  ava  2s  .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;
}

From source file:com.accounting.accountMBean.DifferentAccReports.java

public void simplePostProcessXLSX(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);//w w  w  .  j a v a 2s . c o m
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES AGEING REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSXOfStock(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/*from  w w  w. j  a va2 s. c  o  m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("STOCK VALUE WITH VALUES");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSX(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);//from   w  w  w  .j a v a2s.c  om
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(pageName);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.adobe.acs.commons.data.Spreadsheet.java

License:Apache License

/**
 * Parse out the input file synchronously for easier unit test validation
 *
 * @return List of files that will be imported, including any renditions
 * @throws IOException if the file couldn't be read
 *//*from www. java  2  s.  c o  m*/
private void parseInputFile(InputStream file) throws IOException {

    XSSFWorkbook workbook = new XSSFWorkbook(file);

    final XSSFSheet sheet = workbook.getSheetAt(0);
    rowCount = sheet.getLastRowNum();
    final Iterator<Row> rows = sheet.rowIterator();

    Row firstRow = rows.next();
    headerRow = readRow(firstRow).stream().map(v -> v != null ? convertHeaderName(v.toString()) : null)
            .collect(Collectors.toList());
    headerTypes = readRow(firstRow).stream().map(Variant::toString)
            .collect(Collectors.toMap(this::convertHeaderName, this::detectTypeFromName, this::upgradeToArray));

    Iterable<Row> remainingRows = () -> rows;
    dataRows = StreamSupport.stream(remainingRows.spliterator(), false).map(this::buildRow)
            .filter(Optional::isPresent).map(Optional::get).collect(Collectors.toList());
}