Example usage for org.apache.poi.xssf.usermodel XSSFRow getLastCellNum

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getLastCellNum

Introduction

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

Prototype

@Override
public short getLastCellNum()

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

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 w w  .j a va 2 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:com.appdynamics.jrbronet.projectplan.ExcelManager.java

private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum,
        XSSFSheet destinationWorksheet, int destinationRowNum) {
    // Get the source / new row
    XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row

    if (newRow != null) {
        destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1);
    } else {// ww w .  j a  v  a2 s.co  m
        newRow = destinationWorksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        //newCell.setCellValue(oldCell.getRawValue());
        //newCell.setCellType(oldCell.getCellType());                        

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }

    }

    // If there are are any merged regions in the source row, copy to new row
    /*
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
    CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
    if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
        CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                (newRow.getRowNum() +
                        (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                )),
                cellRangeAddress.getFirstColumn(),
                cellRangeAddress.getLastColumn());
        worksheet.addMergedRegion(newCellRangeAddress);
    }            
    }
    */
}

From source file:com.axelor.apps.admin.service.ViewDocExportService.java

License:Open Source License

private void writeCell(XSSFRow row, Integer oldRowIndex, int count, XSSFCellStyle cellStyle) {

    XSSFRow oldRow = oldSheet.getRow(oldRowIndex);

    while (count < oldRow.getLastCellNum()) {
        XSSFCell oldCell = oldRow.getCell(count);
        Cell cell = row.createCell(count);
        cell.setCellStyle(cellStyle);// w  ww  . j  a v  a2s. co m
        cell.setCellValue(oldCell.getStringCellValue());
        count++;
    }

}

From source file:com.centurylink.mdw.drools.Excel2007Parser.java

License:Apache License

@SuppressWarnings("deprecation")
private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) {

    int mergedRegionCount = sheet.getNumMergedRegions();
    CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount];
    for (int i = 0; i < mergedRegionCount; i++) {
        mergedRanges[i] = sheet.getMergedRegion(i);
    }/*from   ww  w  .  j av a 2 s  .  com*/

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            newRow(listeners, i, row.getLastCellNum());
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                XSSFCell cell = row.getCell(cellNum);
                if (cell != null) {
                    CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

                    if (merged != null) {
                        XSSFRow topRow = sheet.getRow(merged.getFirstRow());
                        XSSFCell topLeft = topRow.getCell(merged.getFirstColumn());
                        String cellValue = topLeft.getCellType() == CellType.NUMERIC.getCode()
                                ? String.valueOf(topLeft.getNumericCellValue())
                                : topLeft.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, topLeft.getColumnIndex());
                    } else {
                        String cellValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        else
                            cellValue = cell.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java

License:Apache License

private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) {

    int mergedRegionCount = sheet.getNumMergedRegions();
    CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount];
    for (int i = 0; i < mergedRegionCount; i++) {
        mergedRanges[i] = sheet.getMergedRegion(i);
    }/*from ww  w .  ja  v  a  2s  .  c o m*/

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            newRow(listeners, i, row.getLastCellNum());
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                XSSFCell cell = row.getCell(cellNum);
                if (cell != null) {
                    CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

                    if (merged != null) {
                        XSSFRow topRow = sheet.getRow(merged.getFirstRow());
                        XSSFCell topLeft = topRow.getCell(merged.getFirstColumn());
                        newCell(listeners, i, cellNum, topLeft.getStringCellValue(), topLeft.getColumnIndex());
                    } else {
                        String cellValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        else
                            cellValue = cell.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

From source file:com.cn.util.ExcelImport.java

/**
* ?2007excel//ww w.j av a 2 s  .c om
* 
* @param file
* @return
*/
private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;
    Object val = null;
    DecimalFormat df = new DecimalFormat("0");// ?
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ?

    for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        List<Object> objList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell == null) {
                val = null;
                objList.add(val);
                continue;
            }
            switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else {
                    val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java

License:Apache License

private void readXmlBasedExcel(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data)
        throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        Cell cell = null;// w w  w .j ava 2s  .c  o  m

        List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>();

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }

            Map<String, String> columnData = new HashMap<String, String>();

            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK);

                columnData.put("column" + (cellIndex + 1), cell.toString());
            }

            sheetData.add(columnData);
        }

        data.put("sheet" + (sheetIndex + 1), sheetData);
    }

}

From source file:com.denimgroup.threadfix.csv2ssl.parser.FormatParser.java

License:Mozilla Public License

public static Option<String[]> getHeadersExcel(File file) {
    try {// w ww  . j  av a2 s. c  o  m
        FileInputStream fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);

        XSSFSheet ws = wb.getSheetAt(0); // read the first sheet
        int totalRows = ws.getLastRowNum();

        if (totalRows == 0) {
            return Option.failure("No lines found in file " + file.getName());
        }

        XSSFRow row = ws.getRow(0);

        String[] headers = new String[row.getLastCellNum()];

        for (int index = 0; index < row.getLastCellNum(); index++) {
            XSSFCell cell = row.getCell(index);

            assert cell != null : "Got null cell at index " + index;

            headers[index] = cell.toString();
        }

        return Option.success(headers);

    } catch (IOException e) {
        e.printStackTrace();
        return Option.failure("Encountered IOException.");
    }
}

From source file:com.esd.cs.common.XExcelSheetParser.java

License:Open Source License

public List<List<Object>> getDatasInSheet(int sheetNumber) {
    List<List<Object>> result = new ArrayList<List<Object>>();
    // sheet//from   w  w w .j  av  a2 s. c  om
    XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
    // sheet
    int rowCount = sheet.getLastRowNum();
    logger.info("found excel rows count:" + rowCount);
    if (rowCount < 1) {
        return result;
    }
    // ??row
    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        // 
        XSSFRow row = sheet.getRow(rowIndex);
        if (null != row) {
            List<Object> rowData = new ArrayList<Object>();
            // ?
            int cellCount = row.getLastCellNum();
            // ??cell
            for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                XSSFCell cell = row.getCell(cellIndex);
                // ??
                Object cellStr = this.getCellString(cell);

                rowData.add(cellStr);
            }
            result.add(rowData);
        }
    }

    return result;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * Generate the preview of the xls grid/*from   ww w  .  j av a 2  s. c  o  m*/
 * @param filename
 * @param sheetNo
 * @return
 * @throws FileNotFoundException
 * @throws IOException
 * @throws JSONException
 */
public static JSONObject parseXLSX(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    FileInputStream fs = new FileInputStream(filename);
    XSSFWorkbook wb = new XSSFWorkbook(fs);
    XSSFSheet sheet = wb.getSheetAt(sheetNo);
    //DateFormat sdf = new SimpleDateFormat(df);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }

    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            XSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == 0) {
                maxCol = row.getLastCellNum();
            }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                XSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;

                if (cell != null) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            val = Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == 0) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLSX has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}