Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.taobao.tddl.common.SQLPreParserTest.java

License:Open Source License

public static void main1(String[] args) throws IOException {
    String fileName = "D:/12_code/tddl/trunk/tddl/tddl-parser/test.xls";
    Workbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(fileName)));
    Sheet sheet = wb.getSheetAt(0);
    for (Row row : sheet) {
        Cell cell = row.getCell(2);// www . ja v  a  2  s.  c  o  m
        System.out.println(cell.getStringCellValue());
    }
}

From source file:com.taobao.tddl.common.SQLPreParserTest.java

License:Open Source License

public static void main(String[] args) throws IOException {
    //String fileName = "D:/12_code/tddl/trunk/tddl/tddl-parser/sqlsummary-icsg-db0-db15-group-20100901100337-export.xlsx";
    //String fileName = "D:/12_code/tddl/trunk/tddl/tddl-parser/sqlsummary-tcsg-instance-group-20100901100641-export.xlsx";

    int count = 0;
    long time = 0;

    File home = new File(System.getProperty("user.dir") + "/appsqls");
    for (File f : home.listFiles()) {
        if (f.isDirectory() || !f.getName().endsWith(".xlsx")) {
            continue;
        }/*from ww w .  ja va  2  s  .  com*/
        log.info("---------------------- " + f.getAbsolutePath());
        faillog.info("---------------------- " + f.getAbsolutePath());
        Workbook wb = new XSSFWorkbook(new FileInputStream(f));
        Sheet sheet = wb.getSheetAt(0);
        for (Row row : sheet) {
            Cell cell = row.getCell(2);
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                String sql = cell.getStringCellValue();

                long t0 = System.currentTimeMillis();
                String tableName = SQLPreParser.findTableName(sql);
                time += System.currentTimeMillis() - t0;
                count++;

                log.info(tableName + " <-- " + sql);
                if (tableName == null) {
                    sql = sql.trim().toLowerCase();
                    if (isCRUD(sql)) {
                        System.out.println("failed:" + sql);
                        faillog.error("failed:" + sql);
                    }
                }
            }
        }
        wb = null;
    }
    faillog.fatal("------------------------------- finished --------------------------");
    faillog.fatal(count + " sql parsed, total time:" + time + ". average time use per sql:"
            + (double) time / count + "ms/sql");
}

From source file:com.tecacet.jflat.excel.PoiExcelReader.java

License:Apache License

public PoiExcelReader(String filename, ReaderRowMapper<T> mapper) throws IOException {
    is = new FileInputStream(filename);
    Workbook wb;
    try {//w w w .  j  a  va2 s .  c o m
        wb = WorkbookFactory.create(is);
    } catch (InvalidFormatException e) {
        throw new IOException(e);
    }
    currentSheet = wb.getSheetAt(0);
    this.rowMapper = mapper;
    this.skipLines = DEFAULT_SKIP_LINES;
}

From source file:com.teeznar.poi.test.TestPoi.java

License:Open Source License

public void test() {
    InputStream inp;/*from  ww w .  ja v  a  2 s  .  co m*/
    try {
        inp = new FileInputStream("sample-file.xlsx");

        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row arow : sheet) {

            for (Cell acell : arow) {
                System.out.print(" cell: " + acell);
            }
            System.out.println("\n");
        }

        Row row = sheet.getRow(2);
        Cell cell = row.getCell(3);

        System.out.println(cell.toString());

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.ucuenca.dao.BaseExcelDao.java

/**
 * This method gets sheets of file excel
 *
 * @param workbook//from  www.j a va 2s  .  c o m
 * @Author pablo and adrian
 * @return list table
 */
public ArrayList<Table> getSheet(Workbook workbook) {
    int numberOfSheets = workbook.getNumberOfSheets();
    ArrayList<Table> listTable = new ArrayList<Table>();
    for (int i = 0; i < numberOfSheets; i++) {
        Table table = new Table_Excel();
        Sheet sheet = workbook.getSheetAt(i);
        table.setTitle(sheet.getSheetName());
        getColumn(sheet);
        listTable.add(table);

    }
    return listTable;
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java

License:Open Source License

/**
 * Reports the correct cell selection value (formula/data) and selection.
 * This method is called when the cell selection has changed via the address
 * field.//  w  ww .j  a  v  a 2  s  .com
 * 
 * @param rowIndex
 *            Index of row, 1-based
 * @param columnIndex
 *            Index of column, 1-based
 */
private void handleCellAddressChange(int rowIndex, int colIndex, boolean initialSelection) {
    if (rowIndex >= spreadsheet.getState().rows) {
        rowIndex = spreadsheet.getState().rows;
    }
    if (colIndex >= spreadsheet.getState().cols) {
        colIndex = spreadsheet.getState().cols;
    }
    MergedRegion region = MergedRegionUtil.findIncreasingSelection(spreadsheet.getMergedRegionContainer(),
            rowIndex, rowIndex, colIndex, colIndex);
    if (region.col1 != region.col2 || region.row1 != region.row2) {
        handleCellRangeSelection(
                new CellRangeAddress(region.row1 - 1, region.row2 - 1, region.col1 - 1, region.col2 - 1));
    } else {
        rowIndex = region.row1;
        colIndex = region.col1;
        Workbook workbook = spreadsheet.getWorkbook();
        final Row row = workbook.getSheetAt(workbook.getActiveSheetIndex()).getRow(rowIndex - 1);
        if (row != null) {
            final Cell cell = row.getCell(colIndex - 1);
            if (cell != null) {
                String value = "";
                boolean formula = cell.getCellType() == Cell.CELL_TYPE_FORMULA;
                if (!spreadsheet.isCellHidden(cell)) {
                    if (formula) {
                        value = cell.getCellFormula();
                    } else {
                        value = spreadsheet.getCellValue(cell);
                    }
                }
                spreadsheet.getRpcProxy().showSelectedCell(colIndex, rowIndex, value, formula,
                        spreadsheet.isCellLocked(cell), initialSelection);
            } else {
                spreadsheet.getRpcProxy().showSelectedCell(colIndex, rowIndex, "", false,
                        spreadsheet.isCellLocked(cell), initialSelection);
            }
        } else {
            spreadsheet.getRpcProxy().showSelectedCell(colIndex, rowIndex, "", false,
                    spreadsheet.isActiveSheetProtected(), initialSelection);
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java

License:Open Source License

/**
 * Reports the selected cell formula value, if any. This method is called
 * when the cell value has changed via sheet cell selection change.
 * /*from w w  w  .  j  a v a  2  s  .co  m*/
 * This method can also be used when the selected cell has NOT changed but
 * the value it displays on the formula field might have changed and needs
 * to be updated.
 * 
 * @param rowIndex
 *            1-based
 * @param columnIndex
 *            1-based
 */
private void handleCellSelection(int rowIndex, int columnIndex) {
    Workbook workbook = spreadsheet.getWorkbook();
    final Row row = workbook.getSheetAt(workbook.getActiveSheetIndex()).getRow(rowIndex - 1);
    if (row != null) {
        final Cell cell = row.getCell(columnIndex - 1);
        if (cell != null) {
            String value = "";
            boolean formula = cell.getCellType() == Cell.CELL_TYPE_FORMULA;
            if (!spreadsheet.isCellHidden(cell)) {
                if (formula) {
                    value = cell.getCellFormula();
                } else {
                    value = spreadsheet.getCellValue(cell);
                }
            }
            spreadsheet.getRpcProxy().showCellValue(value, columnIndex, rowIndex, formula,
                    spreadsheet.isCellLocked(cell));
        } else {
            spreadsheet.getRpcProxy().showCellValue("", columnIndex, rowIndex, false,
                    spreadsheet.isCellLocked(cell));
        }
    } else {
        spreadsheet.getRpcProxy().showCellValue("", columnIndex, rowIndex, false,
                spreadsheet.isActiveSheetProtected());
    }
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java

License:Open Source License

/**
 * Handles the new cell range that was given in the address field, returns
 * the range and new selected cell formula/value (if any)
 * /*from www.ja  v  a  2s .  c om*/
 * @param cra
 *            Range of cells to select
 */
protected void handleCellRangeSelection(CellRangeAddress cra) {
    int row1 = cra.getFirstRow();
    int row2 = cra.getLastRow();
    int col1 = cra.getFirstColumn();
    int col2 = cra.getLastColumn();
    Workbook workbook = spreadsheet.getWorkbook();
    final Row row = workbook.getSheetAt(workbook.getActiveSheetIndex()).getRow(row1);
    if (row != null) {
        final Cell cell = row.getCell(col1);
        if (cell != null) {
            String value = "";
            boolean formula = cell.getCellType() == Cell.CELL_TYPE_FORMULA;
            if (!spreadsheet.isCellHidden(cell)) {
                if (formula) {
                    value = cell.getCellFormula();
                } else {
                    value = spreadsheet.getCellValue(cell);
                }
            }
            spreadsheet.getRpcProxy().showSelectedCellRange(col1 + 1, col2 + 1, row1 + 1, row2 + 1, value,
                    formula, spreadsheet.isCellLocked(cell));
        } else {
            spreadsheet.getRpcProxy().showSelectedCellRange(col1 + 1, col2 + 1, row1 + 1, row2 + 1, "", false,
                    spreadsheet.isCellLocked(cell));
        }
    } else {
        spreadsheet.getRpcProxy().showSelectedCellRange(col1 + 1, col2 + 1, row1 + 1, row2 + 1, "", false,
                spreadsheet.isActiveSheetProtected());
    }
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java

License:Open Source License

/**
 * Sets the given range and starting point as the current selection.
 * /*from  w  ww.  ja v a  2s .  c  o  m*/
 * @param startingPoint
 *            Reference to starting point
 * @param cellsToSelect
 *            Selection area
 */
protected void handleCellRangeSelection(CellReference startingPoint, CellRangeAddress cellsToSelect,
        boolean scroll) {
    int row1 = cellsToSelect.getFirstRow();
    int row2 = cellsToSelect.getLastRow();
    int col1 = cellsToSelect.getFirstColumn();
    int col2 = cellsToSelect.getLastColumn();
    Workbook workbook = spreadsheet.getWorkbook();
    final Row row = workbook.getSheetAt(workbook.getActiveSheetIndex()).getRow(startingPoint.getRow());
    if (row != null) {
        final Cell cell = row.getCell(startingPoint.getCol());
        if (cell != null) {
            String value = "";
            boolean formula = cell.getCellType() == Cell.CELL_TYPE_FORMULA;
            if (!spreadsheet.isCellHidden(cell)) {
                if (formula) {
                    value = cell.getCellFormula();
                } else {
                    value = spreadsheet.getCellValue(cell);
                }
            }
            spreadsheet.getRpcProxy().setSelectedCellAndRange(startingPoint.getCol() + 1,
                    startingPoint.getRow() + 1, col1 + 1, col2 + 1, row1 + 1, row2 + 1, value, formula,
                    spreadsheet.isCellLocked(cell), scroll);
        } else {
            spreadsheet.getRpcProxy().setSelectedCellAndRange(startingPoint.getCol() + 1,
                    startingPoint.getRow() + 1, col1 + 1, col2 + 1, row1 + 1, row2 + 1, "", false,
                    spreadsheet.isCellLocked(cell), scroll);
        }
    } else {
        spreadsheet.getRpcProxy().setSelectedCellAndRange(startingPoint.getCol() + 1,
                startingPoint.getRow() + 1, col1 + 1, col2 + 1, row1 + 1, row2 + 1, "", false,
                spreadsheet.isActiveSheetProtected(), scroll);
    }
    selectedCellReference = startingPoint;
    cellRangeAddresses.clear();
    individualSelectedCells.clear();
    paintedCellRange = cellsToSelect;
    if (col1 != col2 || row1 != row2) {
        cellRangeAddresses.add(cellsToSelect);
    }
    ensureClientHasSelectionData();
    fireNewSelectionChangeEvent();
}

From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java

License:Open Source License

private void shiftRowsDownInSelection(int newLastRow) {
    CellRangeAddress paintedCellRange = spreadsheet.getCellSelectionManager().getSelectedCellRange();
    int r1 = paintedCellRange.getFirstRow() + 1;
    int r2 = paintedCellRange.getLastRow() + 1;
    int c1 = paintedCellRange.getFirstColumn() + 1;
    int c2 = paintedCellRange.getLastColumn() + 1;
    Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    for (int shiftedRowIndex = r1; shiftedRowIndex <= r2; shiftedRowIndex++) {
        final Row shiftedRow = activeSheet.getRow(shiftedRowIndex - 1);
        int newRowIndex = r2 + 1 + (shiftedRowIndex - r1);
        while (newRowIndex <= newLastRow) {
            if (shiftedRow != null) {
                Row newRow = activeSheet.getRow(newRowIndex - 1);
                if (newRow == null) {
                    newRow = activeSheet.createRow(newRowIndex - 1);
                }//from  ww w .j  ava 2s  .  c  om
                for (int c = c1; c <= c2; c++) {
                    Double sequenceIncrement = getColumnSequenceIncrement(c, r1, r2);
                    Cell shiftedCell = shiftedRow.getCell(c - 1);
                    Cell newCell = newRow.getCell(c - 1);
                    if (shiftedCell != null) {
                        if (newCell == null) {
                            newCell = newRow.createCell(c - 1);
                        }
                        shiftCellValue(shiftedCell, newCell, false, sequenceIncrement);
                    } else if (newCell != null) {
                        // update style to 0
                        newCell.setCellStyle(null);
                        spreadsheet.getSpreadsheetStyleFactory().cellStyleUpdated(newCell, true);
                        newCell.setCellValue((String) null);
                        getCellValueManager().cellDeleted(newCell);
                    }
                }
            } else {
                getCellValueManager().removeCells(newRowIndex, c1, newRowIndex, c2, true);
            }
            newRowIndex += r2 - r1 + 1;
        }
    }
}