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

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

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

Introduction

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

Prototype

void setActiveSheet(int sheetIndex);

Source Link

Document

Convenience method to set the active sheet.

Usage

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

License:Open Source License

/**
 * Clears the given Spreadsheet and loads the given Workbook into it.
 *
 * @param spreadsheet/*from   w  w w  .ja v  a  2s  .c om*/
 *            Target Spreadsheet
 * @param workbook
 *            Workbook to load or null to generate a new workbook with one
 *            sheet.
 * @param rowCount
 *            Number of rows to generate in the first sheet. Only applies
 *            when the workbook parameter is null.
 * @param columnCount
 *            Number of columns to generate in the first sheet. Only applies
 *            when the workbook parameter is null.
 */
static void loadSpreadsheetWith(Spreadsheet spreadsheet, Workbook workbook, int rowCount, int columnCount) {
    spreadsheet.clearSheetServerSide();
    final Sheet sheet;
    if (workbook == null) {
        workbook = new XSSFWorkbook();
        sheet = createNewSheet(workbook);
        spreadsheet.setInternalWorkbook(workbook);
        generateNewSpreadsheet(spreadsheet, sheet, rowCount, columnCount);
    } else {
        int activeSheetIndex = workbook.getActiveSheetIndex();
        if (workbook.isSheetHidden(activeSheetIndex) || workbook.isSheetVeryHidden(activeSheetIndex)) {
            workbook.setActiveSheet(SpreadsheetUtil.getFirstVisibleSheetPOIIndex(workbook));
        }
        sheet = workbook.getSheetAt(activeSheetIndex);
        spreadsheet.setInternalWorkbook(workbook);
        reloadSpreadsheetData(spreadsheet, sheet);
    }
    loadWorkbookStyles(spreadsheet);
}

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

License:Open Source License

/**
 * Adds a new sheet to the given Spreadsheet and Workbook.
 *
 * @param spreadsheet//from   w w  w . ja  v  a2  s  .c  o m
 *            Target Spreadsheet
 * @param workbook
 *            Target Workbook
 * @param sheetName
 *            Name of the new sheet
 * @param rows
 *            Row count for the new sheet
 * @param columns
 *            Column count for the new sheet
 */
static void addNewSheet(final Spreadsheet spreadsheet, final Workbook workbook, final String sheetName,
        int rows, int columns) {
    final Sheet sheet;
    if (sheetName == null) {
        sheet = createNewSheet(workbook);
    } else {
        sheet = workbook.createSheet(sheetName);
    }
    int sheetIndex = workbook.getSheetIndex(sheet);
    workbook.setActiveSheet(sheetIndex);
    spreadsheet.reloadActiveSheetData();
    spreadsheet.reloadActiveSheetStyles();
    final SpreadsheetState state = spreadsheet.getState();
    int[] verticalScrollPositions = Arrays.copyOf(state.verticalScrollPositions, state.sheetNames.length);
    int[] horizontalScrollPositions = Arrays.copyOf(state.horizontalScrollPositions, state.sheetNames.length);
    state.verticalScrollPositions = verticalScrollPositions;
    state.horizontalScrollPositions = horizontalScrollPositions;
    generateNewSpreadsheet(spreadsheet, sheet, rows, columns);
}

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @return/*  w w w  .  ja  v a 2  s . com*/
 */
public String generateScoresheet() {
    if (!getFormats().isEmpty() && !getTimeFormats().isEmpty() && !getRound1().isEmpty()) {
        try {
            // load WCA template from file
            InputStream is = ServletActionContext.getServletContext()
                    .getResourceAsStream(getSpreadSheetFilename());
            Workbook workBook;
            workBook = WorkbookFactory.create(is);
            is.close();

            // build special registration sheet
            generateRegistrationSheet(workBook, getCompetition());

            // build result sheets
            generateResultSheets(workBook, getCompetition(), getFormats(), getTimeFormats(), getRound1(),
                    getRound2(), getRound3(), getRound4());

            // set default selected sheet
            workBook.setActiveSheet(workBook.getSheetIndex(SHEET_TYPE_REGISTRATION));

            // output generated spreadsheet
            log.debug("Ouputting generated workbook");
            out = new ByteArrayOutputStream();
            workBook.write(out);
            out.close();

            return Action.SUCCESS;
        } catch (InvalidFormatException e) {
            log.error("Spreadsheet template are using an unsupported format.", e);
        } catch (IOException e) {
            log.error("Error reading spreadsheet template.", e);
        }
        return Action.ERROR;
    } else {
        return Action.INPUT;
    }
}

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @return/*from   ww  w.j ava 2  s . c om*/
 */
public String exportResults() {
    if (competitionId != null) {
        Competition competitionTemplate = getCompetitionService().find(competitionId);
        if (competitionTemplate == null) {
            log.error("Could not load competition: {}", competitionId);
            return Action.ERROR;
        }
        setCompetition(competitionTemplate);

        try {
            // load WCA template from file
            InputStream is = ServletActionContext.getServletContext()
                    .getResourceAsStream(getSpreadSheetFilename());
            Workbook workBook;
            workBook = WorkbookFactory.create(is);
            is.close();

            // build special registration sheet
            generateRegistrationSheet(workBook, getCompetition());

            // build result sheets
            generateResultSheets(workBook, getCompetition());

            // set default selected sheet
            workBook.setActiveSheet(workBook.getSheetIndex(SHEET_TYPE_REGISTRATION));

            // email or just output to pdf?
            if (isSubmitResultsToWCA()) {
                // write workbook to temp file
                File temp = File.createTempFile(getCompetitionId(), ".xls");
                temp.deleteOnExit();
                OutputStream os = new FileOutputStream(temp);
                workBook.write(os);
                os.close();

                // Create the attachment
                EmailAttachment attachment = new EmailAttachment();
                attachment.setPath(temp.getPath());
                attachment.setDisposition(EmailAttachment.ATTACHMENT);
                attachment.setName(getCompetitionId() + ".xls");

                // send email
                MultiPartEmail email = new MultiPartEmail();
                email.setCharset(Email.ISO_8859_1);
                email.setHostName(getText("email.smtp.server"));
                if (!getText("email.username").isEmpty() && !getText("email.password").isEmpty()) {
                    email.setAuthentication(getText("email.username"), getText("email.password"));
                }
                email.setSSL("true".equals(getText("email.ssl")));
                email.setSubject("Results from " + getCompetition().getName());
                email.setMsg(getText("admin.export.message",
                        new String[] { getCompetition().getName(), getCompetition().getOrganiser() }));
                email.setFrom(getCompetition().getOrganiserEmail(), getCompetition().getOrganiser());
                email.addTo(getText("admin.export.resultsteamEmail"), getText("admin.export.resultsteam"));
                email.addCc(getCompetition().getOrganiserEmail(), getCompetition().getOrganiser());
                email.addCc(getCompetition().getWcaDelegateEmail(), getCompetition().getWcaDelegate());
                email.attach(attachment);
                email.send();

                return Action.SUCCESS;
            } else {
                // output generated spreadsheet
                log.debug("Ouputting generated workbook");
                out = new ByteArrayOutputStream();
                workBook.write(out);
                out.close();

                return "spreadsheet";
            }
        } catch (InvalidFormatException e) {
            log.error("Spreadsheet template are using an unsupported format.", e);
        } catch (IOException e) {
            log.error("Error reading spreadsheet template.", e);
        } catch (EmailException e) {
            log.error(e.getMessage(), e);
        }
        return Action.ERROR;
    } else {
        return Action.INPUT;
    }
}

From source file:net.bis5.resumegen.component.ReportGenerator.java

License:Apache License

private void initReportProcListeners() {
    reportProcListeners.add(new ReportProcessAdaptor() {
        /*/*from  ww w. j av a2  s. com*/
         * (non Javadoc)
         * @see org.bbreak.excella.reports.listener.ReportProcessAdaptor#
         * postBookParse(org.apache.poi.ss.usermodel.Workbook,
         * org.bbreak.excella.reports.model.ReportBook)
         */
        @Override
        public void postBookParse(Workbook workbook, ReportBook reportBook) {
            workbook.setActiveSheet(2);
        }
    });
}

From source file:org.bbreak.excella.core.handler.DebugErrorHandler.java

License:Open Source License

/**
 * ?//  www .  j a  v  a2  s. c o m
 * 
 * @param workbook 
 * @param errorCell
 * @param exception
 */
protected void markupErrorCell(Workbook workbook, ParseException exception) {
    Cell errorCell = exception.getCell();
    if (errorCell == null) {
        return;
    }
    // ????
    workbook.setActiveSheet(workbook.getSheetIndex(errorCell.getSheet()));
    errorCell.setAsActiveCell();

    if (workbook instanceof XSSFWorkbook) {
        XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;

        CellStyle errorCellStyle = xssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // TODO:???????????????
        // XSSFComment xssfComment = ((XSSFSheet)sheet).createComment();
        // xssfComment.setRow( errorCell.getRowIndex());
        // xssfComment.setColumn( (short)errorCell.getColumnIndex());
        // XSSFRichTextString string = new XSSFRichTextString( ex.getMessage());
        // xssfComment.setString( ex.getMessage());
    } else {
        HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        for (int cnt = 0; cnt < sheetNum; cnt++) {
            hssfWorkbook.getSheetAt(cnt).setSelected(false);
        }

        // ?
        CellStyle errorCellStyle = hssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // ?
        short commentColFrom = (short) (errorCell.getColumnIndex() + 1);
        short commentColTo = (short) (errorCell.getColumnIndex() + ERROR_COMENT_COL_SIZE);
        int commentRowFrom = errorCell.getRowIndex();
        int commentRowTo = errorCell.getRowIndex() + ERROR_COMENT_ROW_SIZE;

        HSSFSheet hssfSheet = (HSSFSheet) errorCell.getSheet();
        HSSFPatriarch patr = hssfSheet.createDrawingPatriarch();
        hssfSheet.setSelected(true);
        HSSFComment comment = patr.createComment(
                new HSSFClientAnchor(0, 0, 0, 0, commentColFrom, commentRowFrom, commentColTo, commentRowTo));
        comment.setVisible(true);
        comment.setString(new HSSFRichTextString(createCommentMessage(exception)));
        errorCell.setCellComment(comment);
    }
}

From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java

License:Open Source License

@Override
public void export(Map<String, String> criteria, File fileName, String serverBase,
        Map<String, String> options) {
    processOptions(options);//  w w w . j a va 2s.  c  o  m
    if (!GraphicsEnvironment.isHeadless()) {
        progressDialog = new ProgressDialog(maxSteps, locale);
        progressDialog.setVisible(true);
    }
    questionsById = new HashMap<Long, QuestionDto>();
    currentStep = 1;
    this.serverBase = serverBase;
    PrintWriter pw = null;
    boolean useQuestionId = "true".equals(options.get("useQuestionId"));
    String from = options.get("from");
    String to = options.get("to");
    String limit = options.get("maxDataReportRows");
    try {
        SwingUtilities.invokeLater(new StatusUpdater(currentStep++, LOADING_QUESTIONS.get(locale)));

        List<SurveyGroupDto> sgs = fetchSurveyGroup(criteria.get(SurveyRestRequest.SURVEY_ID_PARAM), serverBase,
                criteria.get("apiKey"));

        if (sgs != null && !sgs.isEmpty()) {
            monitoringGroup = sgs.get(0).getMonitoringGroup();
        }

        Map<QuestionGroupDto, List<QuestionDto>> questionMap = loadAllQuestions(
                criteria.get(SurveyRestRequest.SURVEY_ID_PARAM), performGeoRollup, serverBase,
                criteria.get("apiKey"));
        if (questionMap != null) {
            for (List<QuestionDto> qList : questionMap.values()) {
                for (QuestionDto q : qList) {
                    questionsById.put(q.getKeyId(), q);
                    if (q.getLocaleNameFlag() != null && q.getLocaleNameFlag()) {
                        displayNameQuestionIds.add(q.getKeyId());
                    }
                }
            }
        }
        if (!DEFAULT_LOCALE.equals(locale) && questionMap.size() > 0) {
            // if we are using some other locale, we need to check for
            // translations
            SwingUtilities.invokeLater(new StatusUpdater(currentStep++, LOADING_DETAILS.get(locale)));
            loadFullQuestions(questionMap, criteria.get("apiKey"));
        } else {
            currentStep++;
        }
        Workbook wb = new SXSSFWorkbook(100);
        if (questionMap != null && questionMap.size() > 0) {

            headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
            Font headerFont = wb.createFont();
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);

            SummaryModel model = fetchAndWriteRawData(criteria.get(SurveyRestRequest.SURVEY_ID_PARAM),
                    serverBase, questionMap, wb, isFullReport, fileName, criteria.get("apiKey"), lastCollection,
                    useQuestionId, from, to, limit);
            if (isFullReport) {
                SwingUtilities.invokeLater(new StatusUpdater(currentStep++, WRITING_SUMMARY.get(locale)));
                writeSummaryReport(questionMap, model, null, wb);
                SwingUtilities.invokeLater(new StatusUpdater(currentStep++, WRITING_ROLLUPS.get(locale)));
            }
            if (model.getSectorList() != null && model.getSectorList().size() > 0) {

                Collections.sort(model.getSectorList(), new Comparator<String>() {
                    @Override
                    public int compare(String o1, String o2) {
                        if (o1 != null && o2 != null) {
                            return o1.toLowerCase().compareTo(o2.toLowerCase());
                        } else {
                            return 0;
                        }
                    }
                });
                for (String sector : model.getSectorList()) {
                    writeSummaryReport(questionMap, model, sector, wb);
                }
            }

            FileOutputStream fileOut = new FileOutputStream(fileName);
            wb.setActiveSheet(isFullReport ? 1 : 0);
            wb.write(fileOut);
            fileOut.close();

            SwingUtilities.invokeLater(new StatusUpdater(currentStep++, COMPLETE.get(locale)));
        } else {
            log.info("No questions for survey: " + criteria.get(SurveyRestRequest.SURVEY_ID_PARAM)
                    + " - instance: " + serverBase);
        }

    } catch (Exception e) {
        log.error("Error generating report: " + e.getMessage(), e);
    } finally {
        if (pw != null) {
            pw.close();
        }
    }
}

From source file:packtest.SelectedSheet.java

License:Apache License

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();

    wb.createSheet("row sheet");
    wb.createSheet("another sheet");
    Sheet sheet3 = wb.createSheet(" sheet 3 ");
    sheet3.setSelected(true);//w  w w. j  ava 2  s .  co m
    wb.setActiveSheet(2);

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("selectedSheet.xlsx");
    wb.write(fileOut);
    fileOut.close();

    wb.close();
}

From source file:poi.xssf.usermodel.examples.SelectedSheet.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();

    Sheet sheet = wb.createSheet("row sheet");
    Sheet sheet2 = wb.createSheet("another sheet");
    Sheet sheet3 = wb.createSheet(" sheet 3 ");
    sheet3.setSelected(true);/*  www .  j  a v a 2s  .  c o m*/
    wb.setActiveSheet(2);

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("selectedSheet.xlsx");
    wb.write(fileOut);
    fileOut.close();
}