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

Introduction

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

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:org.wso2.carbon.dataservices.core.description.query.ExcelQuery.java

License:Open Source License

private String[] getHeader() throws Exception {
    if (!this.isHasHeader()) {
        return null;
    }//from   w w  w  .  ja  v a2 s  . com
    Workbook wb = this.getConfig().createWorkbook();
    Sheet sheet = wb.getSheet(this.getWorkbookName());
    return this.extractRowData(sheet.getRow(this.getHeaderRow() - 1));
}

From source file:org.wso2.carbon.dataservices.core.description.query.ExcelQuery.java

License:Open Source License

public Object runPreQuery(InternalParamCollection params, int queryLevel) throws DataServiceFault {
    try {/*w ww  . j a va  2  s. c  o m*/
        Workbook wb = this.getConfig().createWorkbook();
        return wb.getSheet(this.getWorkbookName());
    } catch (Exception e) {
        throw new DataServiceFault(e, "Error in ExcelQuery.runQuery.");
    }
}

From source file:org.wso2.carbon.dataservices.sql.driver.query.drop.ExcelDropQuery.java

License:Open Source License

private boolean isSheetExists(Workbook workbook) {
    Sheet sheet = workbook.getSheet(this.getTableName());
    return (sheet != null);
}

From source file:org.wso2.carbon.dataservices.sql.driver.query.insert.ExcelInsertQuery.java

License:Open Source License

private synchronized int executeSQL() throws SQLException {
    int rowCount = 0;
    if (!(getConnection() instanceof TExcelConnection)) {
        throw new SQLException("Connection does not refer to a Excel connection");
    }//from  w w w . j av a  2s .c o  m
    TExcelConnection excelConnection = (TExcelConnection) this.getConnection();
    //begin transaction,
    excelConnection.beginExcelTransaction();
    Workbook workbook = excelConnection.getWorkbook();
    Sheet sheet = workbook.getSheet(getTargetTableName());
    if (sheet == null) {
        throw new SQLException("Excel sheet named '" + this.getTargetTableName() + "' does not exist");
    }
    int lastRowNo = sheet.getLastRowNum();

    if (getParameters() != null) {
        Row row = sheet.createRow(lastRowNo + 1);
        for (ParamInfo param : getParameters()) {
            Cell cell = row.createCell(param.getOrdinal());
            switch (param.getSqlType()) {
            case Types.VARCHAR:
                cell.setCellValue((String) param.getValue());
                break;
            case Types.INTEGER:
                cell.setCellValue((Integer) param.getValue());
                break;
            case Types.DOUBLE:
                cell.setCellValue((Double) param.getValue());
                break;
            case Types.BOOLEAN:
                cell.setCellValue((Boolean) param.getValue());
                break;
            case Types.DATE:
                cell.setCellValue((Date) param.getValue());
                break;
            default:
                cell.setCellValue((String) param.getValue());
                break;
            }
        }
        rowCount++;
    }
    TDriverUtil.writeRecords(workbook, ((TExcelConnection) getConnection()).getPath());
    return rowCount;
}

From source file:org.wso2.carbon.dataservices.sql.driver.query.update.ExcelUpdateQuery.java

License:Open Source License

private int executeSQL() throws SQLException {
    Map<Integer, DataRow> result;
    if (getCondition().getLhs() == null && getCondition().getRhs() == null) {
        result = getTargetTable().getRows();
    } else {//from   www.  j  av a  2s .  c om
        result = getCondition().process(getTargetTable());
    }

    if (!(getConnection() instanceof TExcelConnection)) {
        throw new SQLException("Connection does not refer to a Excel connection");
    }
    TExcelConnection excelConnection = (TExcelConnection) this.getConnection();
    //begin transaction,
    excelConnection.beginExcelTransaction();
    Workbook workbook = excelConnection.getWorkbook();
    Sheet sheet = workbook.getSheet(getTargetTableName());
    if (sheet == null) {
        throw new SQLException("Excel sheet named '" + this.getTargetTableName() + "' does not exist");
    }

    ColumnInfo[] headers = TDriverUtil.getHeaders(getConnection(), getTargetTableName());
    for (Map.Entry<Integer, DataRow> row : result.entrySet()) {
        Row updatedRow = sheet.getRow(row.getKey() + 1);
        for (ColumnInfo column : getTargetColumns()) {
            int columnId = findColumnId(headers, column.getName());
            updatedRow.getCell(columnId).setCellValue(column.getValue().toString());
        }
    }
    TDriverUtil.writeRecords(workbook, ((TExcelConnection) getConnection()).getPath());
    return 0;
}

From source file:org.wso2.carbon.dataservices.sql.driver.TDriverUtil.java

License:Open Source License

private static ColumnInfo[] getExcelHeaders(Connection connection, String tableName) throws SQLException {
    List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
    if (!(connection instanceof TExcelConnection)) {
        throw new SQLException("Invalid connection type");
    }//w ww.  ja  v a  2 s . co  m
    Workbook workbook = ((TExcelConnection) connection).getWorkbook();
    Sheet sheet = workbook.getSheet(tableName);
    if (sheet == null) {
        throw new SQLException("Sheet '" + tableName + "' does not exist");
    }
    Iterator<Cell> cellItr = sheet.getRow(0).cellIterator();
    while (cellItr.hasNext()) {
        Cell header = cellItr.next();
        ColumnInfo column = new ColumnInfo(header.getStringCellValue());
        column.setTableName(tableName);
        column.setSqlType(header.getCellType());
        column.setId(header.getColumnIndex());

        columns.add(column);
    }
    return columns.toArray(new ColumnInfo[columns.size()]);
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addReports(ConfigurationContext configContext) throws Exception {
    File reportsDirectory = new File(CommandHandler.getReportsLocation());
    if (reportsDirectory.exists()) {
        ReportGeneratorServiceClient client = new ReportGeneratorServiceClient(cookie,
                CommandHandler.getServiceURL(), configContext);
        Workbook[] workbooks = PopulatorUtil.getWorkbooks(reportsDirectory, "list");
        for (Workbook workbook : workbooks) {
            Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
            if (sheet == null || sheet.getLastRowNum() == -1) {
                throw new RuntimeException("The first sheet is empty");
            }/*w  w  w. j  a  v a2 s.  c o m*/
            int limit = sheet.getLastRowNum();
            for (int i = 0; i <= limit; i++) {
                Row row = sheet.getRow(i);
                if (row == null || row.getCell(0) == null) {
                    break;
                }
                client.saveReport(PopulatorUtil.getReportConfigurationBean(row.getCell(0).getStringCellValue(),
                        row.getCell(1).getStringCellValue(), row.getCell(2).getStringCellValue(),
                        row.getCell(3).getStringCellValue()));
            }
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addPermissions(ConfigurationContext configContext, File dataDirectory) throws Exception {
    UserManagementClient userManager = new UserManagementClient(cookie, CommandHandler.getServiceURL(),
            configContext);//  w ww .  j av a  2  s .c o m
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "permission");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            userManager.setRoleResourcePermission(getCellValue(row.getCell(0), null),
                    getCellValue(row.getCell(1), null), splitAndTrim(getCellValue(row.getCell(2), null), ","));
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addAssociations(Registry registry, File dataDirectory) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "association");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }//from   w ww. j  a  v  a2s .  c o  m
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            registry.addAssociation(getCellValue(row.getCell(0), null), getCellValue(row.getCell(1), null),
                    getCellValue(row.getCell(2), null));
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addCommentsRatingsAndTags(Registry registry, File dataDirectory) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "community");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }/*from w  w  w .  j  a  v  a 2 s . c o  m*/
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            String type = getCellValue(row.getCell(0), null).toLowerCase();
            if (type.contains("tag")) {
                String tag = getCellValue(row.getCell(2), null);
                if (tag == null) {
                    String[] parts = getCellValue(row.getCell(1), null).split(";");
                    registry.removeTag(parts[0], parts[1].substring(5));
                } else {
                    registry.applyTag(getCellValue(row.getCell(1), null), tag);
                }
            } else if (type.contains("comment")) {
                String path = getCellValue(row.getCell(1), null);
                if (path.contains(";")) {
                    String comment = getCellValue(row.getCell(2), null);
                    if (comment == null) {
                        registry.removeComment(path);
                    } else {
                        registry.editComment(path, comment);
                    }
                } else {
                    registry.addComment(path, new Comment(getCellValue(row.getCell(2), null)));
                }
            } else if (type.contains("rate") || type.contains("rating")) {
                registry.rateResource(getCellValue(row.getCell(1), null),
                        Integer.parseInt(getCellValue(row.getCell(2), "0")));
            }
        }
    }
}