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

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

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

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

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

Usage

From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java

License:Open Source License

public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
    assert wb != null;
    assert reportFile != null;

    //Precompute formula
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);/*from  w  ww . ja  va 2 s  .  co m*/

        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        evaluator.evaluateFormulaCell(c);
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                }
            }
        }
    }

    File tmp = File.createTempFile("tmp_", ".xlsx");
    try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
        wb.write(out);
    }

    //Find page orientation
    int maxColumnsGlobal = 0;
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
        }
    }

    Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
    Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);

    PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
    addHeader(writer, header);
    pdfDocument.open();
    //we have two columns in the Excel sheet, so we create a PDF table with two columns
    //Note: There are ways to make this dynamic in nature, if you want to.
    //Loop through sheets
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);

        //Loop through rows, to find number of columns
        int minColumns = 1000;
        int maxColumns = 0;
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            if (row.getFirstCellNum() >= 0)
                minColumns = Math.min(minColumns, row.getFirstCellNum());
            if (row.getLastCellNum() >= 0)
                maxColumns = Math.max(maxColumns, row.getLastCellNum());
        }
        if (maxColumns == 0)
            continue;

        //Loop through first rows, to find relative width
        float[] widths = new float[maxColumns];
        int totalWidth = 0;
        for (int c = 0; c < maxColumns; c++) {
            int w = sheet.getColumnWidth(c);
            widths[c] = w;
            totalWidth += w;
        }

        for (int c = 0; c < maxColumns; c++) {
            widths[c] /= totalWidth;
        }

        //Create new page and a new chapter with the sheet's name
        if (sheetNo > 0)
            pdfDocument.newPage();
        Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);

        PdfPTable pdfTable = null;
        PdfPCell pdfCell = null;
        boolean inTable = false;

        //Loop through cells, to create the content
        //         boolean leftBorder = true;
        //         boolean[] topBorder = new boolean[maxColumns+1];
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            //Check if we exited a table (empty line)
            if (row == null) {
                if (pdfTable != null) {
                    addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
                    pdfTable = null;
                }
                inTable = false;
                continue;
            }

            //Check if we start a table (>MIN_COL_IN_TABLE columns)
            if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
                inTable = true;
            }

            if (!inTable) {
                //Process the data outside table, just add the text
                boolean hasData = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        continue;
                    Chunk chunk = getChunk(wb, cell);
                    pdfSheet.add(chunk);
                    pdfSheet.add(new Chunk(" "));
                    hasData = true;
                }
                if (hasData)
                    pdfSheet.add(Chunk.NEWLINE);

            } else {
                //Process the data in table
                if (pdfTable == null) {
                    //Create table
                    pdfTable = new PdfPTable(maxColumns);
                    pdfTable.setWidths(widths);
                    //                  topBorder = new boolean[maxColumns+1];
                }

                int cellNumber = minColumns;
                //               leftBorder = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
                        pdfCell = new PdfPCell();
                        pdfCell.setBorder(0);
                        pdfTable.addCell(pdfCell);
                    }

                    Chunk phrase = getChunk(wb, cell);
                    pdfCell = new PdfPCell(new Phrase(phrase));
                    pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
                    pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
                    pdfCell.setPaddingLeft(1);
                    pdfCell.setHorizontalAlignment(
                            cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
                                    : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
                                            ? PdfPCell.ALIGN_RIGHT
                                            : PdfPCell.ALIGN_LEFT);
                    pdfCell.setUseBorderPadding(false);
                    pdfCell.setUseVariableBorders(false);
                    pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
                    pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
                            : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
                            : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
                            : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
                    String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
                            : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
                    if (color != null)
                        pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
                    pdfTable.addCell(pdfCell);
                    cellNumber++;
                }
                for (; cellNumber < maxColumns; cellNumber++) {
                    pdfCell = new PdfPCell();
                    pdfCell.setBorder(0);
                    pdfTable.addCell(pdfCell);
                }
            }

            //Custom code to add all images on the first sheet (works for reporting)
            if (sheetNo == 0 && row.getRowNum() == 0) {
                for (PictureData pd : wb.getAllPictures()) {
                    try {
                        Image pdfImg = Image.getInstance(pd.getData());
                        pdfImg.scaleToFit(
                                pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
                                        - pageSize.getBorderWidthRight(),
                                pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
                                        - pageSize.getBorderWidthBottom());
                        pdfSheet.add(pdfImg);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if (pdfTable != null) {
            addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
        }

        pdfDocument.add(pdfSheet);
    }
    pdfDocument.close();

}

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) {
    ListHashMap<Integer, Integer> col2lens = new ListHashMap<>();
    for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) {
        Row r = sheet.getRow(row);
        if (r == null || r.getFirstCellNum() < 0)
            continue;
        short maxH = 0;

        for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) {
            Cell c = r.getCell(col);/* w w  w . ja v  a2 s . co m*/
            if (c == null
                    || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC))
                continue;

            Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex());
            String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue()
                    : "" + c.getNumericCellValue();
            String[] lines = MiscUtils.split(s, "\n");
            int maxLen = 1;
            for (int i = 0; i < lines.length; i++) {
                maxLen = Math.max(lines[i].length(), maxLen);
            }
            if (font.getFontHeightInPoints() < 12) {
                col2lens.add(col, 700
                        + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20);
            }
            maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2));
        }
        if (resizeHeight)
            r.setHeight(maxH);
    }

    for (int col : col2lens.keySet()) {
        List<Integer> lens = col2lens.get(col);
        Collections.sort(lens);
        int len = lens.get(lens.size() - 1);
        if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) {
            len = lens.get(lens.size() - 2);
        }
        sheet.setColumnWidth(col,
                Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500));
    }
}

From source file:com.alibaba.ims.platform.util.ExcelUtil.java

License:Open Source License

/**
 * ?//from w  w  w.ja v  a 2  s.c om
 *
 * @param workbook
 * @return
 */
private static List<String[]> readFromWorkbook(Workbook workbook) {
    List<String[]> rowList = new ArrayList<String[]>();
    if (workbook == null) {
        return rowList;
    }

    Sheet sheet = workbook.getSheetAt(0);
    if (sheet.getPhysicalNumberOfRows() <= 0) {
        return rowList;
    }

    for (Row row : sheet) {
        int last = Math.min(row.getLastCellNum(), 20);
        String[] rowContent = new String[last];
        for (int i = 0; i < last; i++) {
            Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
            if (cell != null) {
                rowContent[i] = getCellValue(cell);
            }
        }
        rowList.add(rowContent);
    }
    return rowList;
}

From source file:com.asakusafw.testdriver.excel.ExcelSheetSinkTest.java

License:Apache License

/**
 * many columns./*from  w w  w  .  j  a  v a2  s  . com*/
 * @throws Exception if occur
 */
@Test
public void many_columns() throws Exception {
    Object[] value = new Object[256];
    Map<PropertyName, PropertyType> map = new TreeMap<>();
    for (int i = 0; i < value.length; i++) {
        map.put(PropertyName.newInstance(String.format("p%04x", i)), PropertyType.INT);
        value[i] = i;
    }
    ArrayModelDefinition def = new ArrayModelDefinition(map);

    File file = folder.newFile("temp.xls");
    ExcelSheetSinkFactory factory = new ExcelSheetSinkFactory(file);
    try (DataModelSink sink = factory.createSink(def, new TestContext.Empty())) {
        sink.put(def.toReflection(value));
    }

    try (InputStream in = new FileInputStream(file)) {
        Workbook workbook = Util.openWorkbookFor(file.getPath(), in);
        Sheet sheet = workbook.getSheetAt(0);
        Row title = sheet.getRow(0);
        assertThat(title.getLastCellNum(), is((short) 256));

        Row content = sheet.getRow(1);
        for (int i = 0; i < title.getLastCellNum(); i++) {
            assertThat(content.getCell(i).getNumericCellValue(), is((double) (Integer) value[i]));
        }
    }
}

From source file:com.avaya.plds.excel.ExcelRead.java

public List<String> getPoeticFeatureLoad(String value1, String value2, int sheetNo, int headers) {
    System.out.println(" Inside of  getPoeticFeatureLoad method ...");
    sheet = xssfWorkbook.getSheetAt(sheetNo);
    boolean read = false;
    List<String> dataList = new ArrayList<String>();
    rowIterator = sheet.iterator();/*from  w ww. ja v a  2 s. co m*/
    while (rowIterator.hasNext()) {
        StringBuilder builder = new StringBuilder();
        Row row = rowIterator.next();
        int rowNumber = row.getRowNum();
        if (row != null) {
            for (short i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {

                if (row.getCell(i) != null && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING)
                        && row.getCell(i).getStringCellValue().contains(value1) && i == 0) {
                    read = true;
                    break;
                    //builder.append(value1).append("\t");
                }

                else if (row.getCell(i) != null
                        && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING)
                        && row.getCell(i).getStringCellValue().contains(value2)) {
                    read = false;
                } else if (read) {
                    //   System.out.println("rowNumber "+ rowNumber);
                    maxCellIndex = (row.getLastCellNum() > maxCellIndex && rowNumber > 0) ? row.getLastCellNum()
                            : maxCellIndex;
                    //   System.out.println("maxCellIndex "+ maxCellIndex);
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            //if(i >0)
                            builder.append(
                                    cell != null ? Double.valueOf(cell.getNumericCellValue()).longValue() : "")
                                    .append("\t");
                        } else {
                            //if(i >0 )
                            builder.append(cell != null ? cell.getStringCellValue() : "").append("\t");
                        }
                    } else {
                        //if(i >0)
                        builder.append("").append("\t");
                    }
                }
                if (headers == rowNumber) {
                    //if(i>0)
                    builder.append(row.getCell(i).getStringCellValue()).append("\t");
                }
            }
            if (!builder.toString().equals("") && !builder.toString().matches("^ null.*"))
                dataList.add(builder.toString().replaceFirst(",", ""));
        }

    }
    return dataList;

}

From source file:com.b2international.snowowl.datastore.server.importer.TerminologyImportExcelParser.java

License:Apache License

private Set<Row> processMembers(final Sheet sheet, int memberStartIndex) {

    final int lastRowNum = sheet.getLastRowNum();
    final Set<Row> componentMembers = Sets.newHashSet();

    for (int i = memberStartIndex; i <= lastRowNum; i++) {
        final Row row = sheet.getRow(i);
        if (null != row) {

            boolean hasValue = false;
            short lastCellNum = row.getLastCellNum();

            for (int j = 0; j < lastCellNum; j++) {
                final String cellValue = ExcelUtilities.extractContentAsString(row.getCell(j));

                // member header row
                if (memberStartIndex == i) {
                    // header row does not have values, but column index to name map
                    columnIndexesByName.put(cellValue, j);
                } else if (!StringUtils.isEmpty(cellValue)) {
                    hasValue = true;//  ww w  . j a  v  a2  s . c  o  m
                    break;
                }
            }

            if (hasValue) {
                componentMembers.add(row);
            }
        }
    }

    return componentMembers;

}

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

private void parse(Sheet sheet) throws SnowowlServiceException {

    int firstRowIndex = findFirstRow(sheet);

    if (firstRowIndex == -1) {
        return;// w  w  w  .  jav a 2 s.  c  om
    }

    if (hasHeader) {
        header = collectRowValues(sheet.getRow(firstRowIndex));
        firstRowIndex++;
    } else {
        final Row firstRow = sheet.getRow(firstRowIndex);
        Cell first = firstRow.getCell(firstRow.getFirstCellNum());
        Cell second = firstRow.getCell(firstRow.getFirstCellNum() + 1);
        if (isNumeric(first) || isNumeric(second)) {
            header.add("ID");
        }
        if (isString(first) || isString(second)) {
            header.add("Label");
        }
    }

    for (int i = firstRowIndex; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);

        // totally empty row w/o any value
        if (row == null) {
            if (!skipEmptyRows) {
                content.add(Collections.<String>emptyList());
            }
            continue;
        }

        if (row.getLastCellNum() > maxWidth) {
            maxWidth = row.getLastCellNum();
        }

        List<String> rowValues = collectRowValues(row);

        if (rowValues.isEmpty()) {
            if (!skipEmptyRows) {
                content.add(Collections.<String>emptyList());
            }
            continue;
        }

        content.add(rowValues);
    }
}

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

private List<String> collectRowValues(Row row) {

    List<String> list = newArrayListWithExpectedSize(row.getLastCellNum());

    boolean hasAnyCellWithValue = false;
    for (int i = 0; i < row.getLastCellNum(); i++) {
        String cellValue = getStringValue(row.getCell(i, Row.RETURN_BLANK_AS_NULL));
        hasAnyCellWithValue = !StringUtils.isEmpty(cellValue);
        list.add(cellValue);//w  w  w .  j a v  a 2 s  .com
    }

    return hasAnyCellWithValue ? list : Collections.<String>emptyList();
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private List<String> collectRowValues(final Row row) {
    List<String> list = Lists.newArrayList();
    for (int i = 0; i < row.getLastCellNum(); i++) {
        list.add(getStringValue(row.getCell(i, Row.CREATE_NULL_AS_BLANK)));
    }/*from  w w  w .  j a va 2 s  . c  om*/

    return list;
}

From source file:com.bawan.vims.common.util.ExcelHelper.java

/**
 * ?excel/*from  w  ww. j av  a  2s.co  m*/
 */
public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) {

    Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

    InputStream in = null;
    Workbook wb = null;
    try {
        File excelFile = new File(excelFilePath);
        if (excelFile == null || !excelFile.exists()) {
            logger.error("ExcelHelper[parserExcel]  excel file don't exist!");
            return null;
        }
        in = new FileInputStream(excelFile);

        String suffix = excelFilePath.substring(excelFilePath.lastIndexOf("."));
        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            logger.error("ExcelHelper[parserExcel]  file suffix do'not match[*.xls, *.xlsx]! ");
            return null;
        } /*else if ("xls".equals(suffix)){
           wb = new HSSFWorkbook(in);
          } else if("xlsx".equals(suffix)) {
           wb = new XSSFWorkbook(in);
          }*/

        wb = WorkbookFactory.create(in); // POI 3.8?, ??xls?xlsx?
        int sheetSize = 0;

        while (true) {
            Sheet sheet = wb.getSheetAt(sheetSize);
            if (sheet == null) {
                break;
            }
            String sheetName = sheet.getSheetName();

            List<Map<String, Object>> sheetContent = new ArrayList<Map<String, Object>>();
            for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                Map<String, Object> rowMap = new HashMap<String, Object>();
                StringBuffer rowContent = new StringBuffer(
                        "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
                rowContent.append("'").append(IDGenerator.getID(32)).append("',");
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    //                  if (cell == null) {
                    //                     rowMap.put(rowNum + "_" + cellIndex, null);
                    //                  } else {
                    //                     rowMap.put(rowNum + "_" + cellIndex, cell.toString());
                    //                  }
                    if (cellIndex == 2) {
                        if (cell == null) {
                            rowContent.append(0).append(",");
                        } else if ("mpv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(1).append(",");
                        } else if ("suv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(2).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(3).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(4).append(",");
                        } else if ("?".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(5).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(6).append(",");
                        }
                        continue;
                    }

                    if (cell == null || cell.toString().trim().length() == 0) {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("default").append(",");
                        } else {
                            rowContent.append("0").append(",");
                        }
                    } else {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("'").append(cell.toString()).append("',");
                        } else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8
                                || cellIndex == 9) {
                            String value = cell.toString().substring(0, cell.toString().indexOf("."));
                            rowContent.append(Integer.valueOf(value)).append(",");
                        } else {
                            rowContent.append(cell.toString()).append(",");
                        }
                    }
                }
                String sql = rowContent.toString();
                sql = sql.substring(0, sql.length() - 1);
                sql += ");";
                System.out.println(sql);
                sheetContent.add(rowMap);
            }

            result.put(sheetName, sheetContent);
            sheetSize++;
        }

    } catch (Exception e) {
        e.printStackTrace();
        logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e);
    } finally {
        try {
            if (wb != null) {
                wb.close();
                wb = null;
            }
        } catch (IOException e1) {
        }

        try {
            if (in != null) {
                in.close();
                in = null;
            }
        } catch (IOException e) {
        }
    }

    return result;
}