Example usage for org.apache.poi.ss.usermodel Cell setCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellValue

Introduction

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

Prototype

void setCellValue(boolean value);

Source Link

Document

Set a boolean value for the cell

Usage

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

/**
 * Set the cell blank//from  w  w w  . j a va2s  .  c o  m
 * 
 * @param p_sheet
 *            the sheet
 * @param p_row
 *            the row position
 * @param p_colLen
 *            the blank column length
 * @throws Exception
 */
private void writeBlank(Sheet p_sheet, int p_row, int p_colLen) throws Exception {
    for (int col = 0; col < p_colLen; col++) {
        Row row = p_sheet.getRow(p_row);
        Cell cell = getCell(row, col);
        cell.setCellValue("");
        col++;
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

/**
 * Create workbook name areas for category failure drop down list, it is
 * from "AA8" to "AAn"./*w  ww. j  a  v a  2  s . c  o  m*/
 * <P>
 * Only write the data of drop down list into the first sheet as it can be
 * referenced from all sheets.
 * </P>
 * <P>
 * The formula is like
 * "[sheetName]!$AA$[startRow]:$AA$[endRow]",i.e."TER!$AA$8:$AA$32".
 * </P>
 */
private void createCategoryFailureNameArea(Workbook p_workbook) {
    try {
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> categories = getFailureCategoriesList();
            // Set the categories in "AA" column, starts with row 8.
            int col = 26;
            for (int i = 0; i < categories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(categories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AA$" + (SEGMENT_START_ROW + 1) + ":$AA$"
                    + (SEGMENT_START_ROW + categories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(CATEGORY_FAILURE_DROP_DOWN_LIST);

            // Hide "AA" column
            firstSheet.setColumnHidden(26, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void createQualityAssessmentNameArea(Workbook p_workbook) {
    try {//from ww  w.java 2  s. c o  m
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> qualityCategories = getQualityAssessmentList();
            // Set the categories in "AA" column, starts with row 8.
            int col = 27;
            for (int i = 0; i < qualityCategories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(qualityCategories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AB$" + (SEGMENT_START_ROW + 1) + ":$AB$"
                    + (SEGMENT_START_ROW + qualityCategories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(QUALITY_ASSESSMENT_LIST);

            // Hide "AB" column
            firstSheet.setColumnHidden(27, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void createMarketSuitabilityNameArea(Workbook p_workbook) {
    try {//from  w w  w .  jav a  2 s . c  om
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> marketCategories = getMarketSuitabilityList();
            // Set the categories in "AC" column, starts with row 11.
            int col = 28;
            for (int i = 0; i < marketCategories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(marketCategories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AC$" + (SEGMENT_START_ROW + 1) + ":$AC$"
                    + (SEGMENT_START_ROW + marketCategories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(MARKET_SUITABILITY_LIST);

            // Hide "AC" column
            firstSheet.setColumnHidden(28, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ReviewersCommentsReportGenerator.java

License:Apache License

/**
 * Add title to the sheet/*from  w ww.j a  v  a  2 s  . co m*/
 * 
 * @param p_workBook
 * @param p_sheet
 *            the sheet
 * @throws Exception
 */
private void addTitle(Workbook p_workBook, Sheet p_sheet) throws Exception {
    Font titleFont = p_workBook.createFont();
    titleFont.setUnderline(Font.U_NONE);
    titleFont.setFontName("Times");
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle cs = p_workBook.createCellStyle();
    cs.setFont(titleFont);

    Row titleRow = getRow(p_sheet, 0);
    Cell titleCell = getCell(titleRow, 0);
    titleCell.setCellValue(m_bundle.getString("review_reviewers_comments"));
    titleCell.setCellStyle(cs);
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ReviewersCommentsReportGenerator.java

License:Apache License

/**
 * Add hidden info "RCR_taskID" for offline uploading. When upload, system
 * can know the report type and current task ID report generated from.
 * /*from  ww w.jav  a  2 s  .  com*/
 * @param p_workbook
 * @param p_sheet
 * @param p_job
 * @param p_targetLocale
 * @throws Exception
 */
private void addHidenInfoForUpload(Workbook p_workbook, Sheet p_sheet, Job p_job,
        GlobalSightLocale p_targetLocale) throws Exception {
    String reportInfo = "";
    for (Workflow wf : p_job.getWorkflows()) {
        if (p_targetLocale.getId() == wf.getTargetLocale().getId()) {
            Collection tasks = ServerProxy.getTaskManager().getCurrentTasks(wf.getId());
            if (tasks != null) {
                for (Iterator it = tasks.iterator(); it.hasNext();) {
                    Task task = (Task) it.next();
                    reportInfo = ReportConstants.REVIEWERS_COMMENTS_REPORT_ABBREVIATION + "_" + task.getId();
                }
            }
        }
    }

    Row titleRow = getRow(p_sheet, 0);
    Cell taskIdCell = getCell(titleRow, 26);
    taskIdCell.setCellValue(reportInfo);
    taskIdCell.setCellStyle(contentStyle);

    p_sheet.setColumnHidden(26, true);
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ReviewersCommentsReportGenerator.java

License:Apache License

/**
 * Add segment header to the sheet for Reviewers Comments Report
 * /* w ww . j a v  a2  s  . c  o  m*/
 * @param p_sheet
 *            the sheet
 * @param p_row
 *            start row number
 * @throws Exception
 */
private void addSegmentHeader(Workbook p_workBook, Sheet p_sheet) throws Exception {
    int col = 0;
    int row = SEGMENT_HEADER_ROW;
    Row segHeaderRow = getRow(p_sheet, row);
    CellStyle headerStyle = getHeaderStyle(p_workBook);

    Cell cell_A = getCell(segHeaderRow, col);
    cell_A.setCellValue(m_bundle.getString("lb_source_segment"));
    cell_A.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 40 * 256);
    col++;

    Cell cell_B = getCell(segHeaderRow, col);
    cell_B.setCellValue(m_bundle.getString("lb_target_segment"));
    cell_B.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 40 * 256);
    col++;

    Cell cell_C = getCell(segHeaderRow, col);
    cell_C.setCellValue(m_bundle.getString("latest_comments"));
    cell_C.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 40 * 256);
    col++;

    Cell cell_D = getCell(segHeaderRow, col);
    cell_D.setCellValue(m_bundle.getString("reviewers_comments_header"));
    cell_D.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 40 * 256);
    col++;

    Cell cell_E = getCell(segHeaderRow, col);
    cell_E.setCellValue(m_bundle.getString("lb_category_failure"));
    cell_E.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 40 * 256);
    col++;

    Cell cell_F = getCell(segHeaderRow, col);
    cell_F.setCellValue(m_bundle.getString("lb_comment_status"));
    cell_F.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 15 * 256);
    col++;

    Cell cell_G = getCell(segHeaderRow, col);
    cell_G.setCellValue(m_bundle.getString("lb_tm_match_original"));
    cell_G.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 20 * 256);
    col++;

    Cell cell_H = getCell(segHeaderRow, col);
    cell_H.setCellValue(m_bundle.getString("lb_glossary_source"));
    cell_H.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 25 * 256);
    col++;

    Cell cell_I = getCell(segHeaderRow, col);
    cell_I.setCellValue(m_bundle.getString("lb_glossary_target"));
    cell_I.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 25 * 256);
    col++;

    Cell cell_J = getCell(segHeaderRow, col);
    cell_J.setCellValue(m_bundle.getString("lb_job_id_report"));
    cell_J.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 15 * 256);
    col++;

    Cell cell_K = getCell(segHeaderRow, col);
    cell_K.setCellValue(m_bundle.getString("lb_segment_id"));
    cell_K.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 15 * 256);
    col++;

    Cell cell_L = getCell(segHeaderRow, col);
    cell_L.setCellValue(m_bundle.getString("lb_page_name"));
    cell_L.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 25 * 256);
    col++;

    Cell cell_M = getCell(segHeaderRow, col);
    cell_M.setCellValue(m_bundle.getString("lb_sid"));
    cell_M.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 15 * 256);
    col++;
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ReviewersCommentsReportGenerator.java

License:Apache License

/**
 * For Reviewers Comments Report(Language Sign-off report), Write segment
 * information into each row of the sheet.
 * //w  w  w .  j a va  2  s.c o m
 * @param p_sheet
 *            the sheet
 * @param p_job
 *            the job data of report
 * @param p_targetLocale
 *            the target locale
 * @param p_row
 *            the segment row in sheet
 * @throws Exception
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
private int writeSegmentInfo(Workbook p_workBook, Sheet p_sheet, Job p_job, GlobalSightLocale p_targetLocale,
        int p_row) throws Exception {
    Vector<TargetPage> targetPages = new Vector<TargetPage>();
    TranslationMemoryProfile tmp = p_job.getL10nProfile().getTranslationMemoryProfile();
    Vector<String> excludItems = null;
    if (tmp != null) {
        excludItems = tmp.getJobExcludeTuTypes();
    }

    long jobId = p_job.getId();

    for (Workflow workflow : p_job.getWorkflows()) {
        if (Workflow.PENDING.equals(workflow.getState()) || Workflow.CANCELLED.equals(workflow.getState())
        // || Workflow.EXPORT_FAILED.equals(workflow.getState())
                || Workflow.IMPORT_FAILED.equals(workflow.getState())) {
            continue;
        }
        if (p_targetLocale.getId() == workflow.getTargetLocale().getId()) {
            targetPages = workflow.getTargetPages();
            break;
        }
    }

    if (!targetPages.isEmpty()) {
        LeverageMatchLingManager lmLingManager = LingServerProxy.getLeverageMatchLingManager();
        TermLeverageManager termLeverageManager = ServerProxy.getTermLeverageManager();

        Locale sourcePageLocale = p_job.getSourceLocale().getLocale();
        Locale targetPageLocale = p_targetLocale.getLocale();
        TermLeverageOptions termLeverageOptions = getTermLeverageOptions(sourcePageLocale, targetPageLocale,
                p_job.getL10nProfile().getProject().getTermbaseName(), String.valueOf(p_job.getCompanyId()));
        Map<Long, Set<TermLeverageMatch>> termLeverageMatchResultMap = null;
        if (termLeverageOptions != null) {
            termLeverageMatchResultMap = termLeverageManager.getTermMatchesForPages(p_job.getSourcePages(),
                    p_targetLocale);
        }

        String category = null;
        PseudoData pData = new PseudoData();
        pData.setMode(PseudoConstants.PSEUDO_COMPACT);
        String sid = null;
        for (int i = 0; i < targetPages.size(); i++) {
            if (cancel)
                return 0;

            TargetPage targetPage = (TargetPage) targetPages.get(i);
            SourcePage sourcePage = targetPage.getSourcePage();

            SegmentTuUtil.getTusBySourcePageId(sourcePage.getId());
            List sourceTuvs = SegmentTuvUtil.getSourceTuvs(sourcePage);
            List targetTuvs = SegmentTuvUtil.getTargetTuvs(targetPage);

            MatchTypeStatistics tuvMatchTypes = lmLingManager.getMatchTypesForStatistics(
                    sourcePage.getIdAsLong(), targetPage.getLocaleId(), p_job.getLeverageMatchThreshold());
            Map fuzzyLeverageMatchMap = lmLingManager.getFuzzyMatches(sourcePage.getIdAsLong(),
                    targetPage.getLocaleId());

            boolean m_rtlSourceLocale = EditUtil.isRTLLocale(sourcePageLocale.toString());
            boolean m_rtlTargetLocale = EditUtil.isRTLLocale(targetPageLocale.toString());

            // Find segment all comments belong to this target page
            Map<Long, IssueImpl> issuesMap = CommentHelper.getIssuesMap(targetPage.getId());

            for (int j = 0; j < targetTuvs.size(); j++) {
                if (cancel)
                    return 0;

                int col = 0;
                Tuv targetTuv = (Tuv) targetTuvs.get(j);
                Tuv sourceTuv = (Tuv) sourceTuvs.get(j);
                category = sourceTuv.getTu(jobId).getTuType();
                if (excludItems != null && excludItems.contains(category)) {
                    continue;
                }

                // Comment
                List issueHistories = null;
                String lastComment = "";
                String failure = "";
                String commentStatus = "";
                Issue issue = issuesMap.get(targetTuv.getId());
                if (issue != null) {
                    issueHistories = issue.getHistory();
                    failure = issue.getCategory();
                    commentStatus = issue.getStatus();
                }
                if (issueHistories != null && issueHistories.size() > 0) {
                    IssueHistory issueHistory = (IssueHistory) issueHistories.get(0);
                    lastComment = issueHistory.getComment();
                }

                sid = sourceTuv.getSid();

                // TM Match
                StringBuilder matches = getMatches(fuzzyLeverageMatchMap, tuvMatchTypes, excludItems,
                        sourceTuvs, targetTuvs, sourceTuv, targetTuv, jobId);

                // Get Terminology/Glossary Source and Target.
                String sourceTerms = "";
                String targetTerms = "";
                if (termLeverageMatchResultMap != null) {
                    Set<TermLeverageMatch> termLeverageMatchSet = termLeverageMatchResultMap
                            .get(sourceTuv.getId());
                    if (termLeverageMatchSet != null) {
                        TermLeverageMatch tlm = termLeverageMatchSet.iterator().next();
                        sourceTerms = tlm.getMatchedSourceTerm();
                        targetTerms = tlm.getMatchedTargetTerm();
                    }
                }

                CellStyle contentStyle = getContentStyle(p_workBook);
                Row currentRow = getRow(p_sheet, p_row);

                // Source segment
                CellStyle srcStyle = m_rtlSourceLocale ? getRtlContentStyle(p_workBook) : contentStyle;
                Cell cell_A = getCell(currentRow, col);
                cell_A.setCellValue(getSegment(pData, sourceTuv, m_rtlSourceLocale, jobId));
                cell_A.setCellStyle(srcStyle);
                col++;

                // Target segment
                CellStyle trgStyle = m_rtlTargetLocale ? getRtlContentStyle(p_workBook) : contentStyle;
                Cell cell_B = getCell(currentRow, col);
                cell_B.setCellValue(getSegment(pData, targetTuv, m_rtlTargetLocale, jobId));
                cell_B.setCellStyle(trgStyle);
                col++;

                // Comments
                CellStyle commentStyle = m_rtlTargetLocale ? getRtlContentStyle(p_workBook)
                        : getContentStyle(p_workBook);
                if (m_rtlTargetLocale) {
                    lastComment = EditUtil.toRtlString(lastComment);
                }
                Cell cell_C = getCell(currentRow, col);
                cell_C.setCellValue(lastComment);
                cell_C.setCellStyle(commentStyle);
                col++;

                //Reviewers comments
                CellStyle reviewersCommentStyle = m_rtlTargetLocale ? getUnlockedRightStyle(p_workBook)
                        : getUnlockedStyle(p_workBook);
                Cell cell_D = getCell(currentRow, col);
                cell_D.setCellValue("");
                cell_D.setCellStyle(reviewersCommentStyle);
                col++;

                // Category failure
                CellStyle unlockedStyle = getUnlockedStyle(p_workBook);
                Cell cell_E = getCell(currentRow, col);
                cell_E.setCellValue(failure);
                cell_E.setCellStyle(unlockedStyle);
                col++;

                // Comment Status
                Cell cell_F = getCell(currentRow, col);
                cell_F.setCellValue(commentStatus);
                cell_F.setCellStyle(unlockedStyle);
                col++;

                // TM match
                Cell cell_G = getCell(currentRow, col);
                cell_G.setCellValue(matches.toString());
                cell_G.setCellStyle(contentStyle);
                col++;

                // Glossary source
                Cell cell_H = getCell(currentRow, col);
                cell_H.setCellValue(sourceTerms);
                cell_H.setCellStyle(contentStyle);
                col++;

                // Glossary target
                Cell cell_I = getCell(currentRow, col);
                cell_I.setCellValue(targetTerms);
                cell_I.setCellStyle(contentStyle);
                col++;

                // Job id
                Cell cell_J = getCell(currentRow, col);
                cell_J.setCellValue(p_job.getId());
                cell_J.setCellStyle(contentStyle);
                col++;

                // SID// Segment id
                Cell cell_K = getCell(currentRow, col);
                cell_K.setCellValue(sourceTuv.getTu(jobId).getId());
                cell_K.setCellStyle(contentStyle);
                col++;

                // Fix for GBS-1484
                String externalPageId = sourcePage.getExternalPageId();
                String[] pathNames = externalPageId.split("\\\\");
                String Name = pathNames[pathNames.length - 1];
                boolean temp = pathNames[0].contains(")");
                if (temp) {
                    String[] firstNames = pathNames[0].split("\\)");
                    String detailName = firstNames[0];
                    Name = Name + detailName + ")";
                }
                // Page Name
                Cell cell_L = getCell(currentRow, col);
                cell_L.setCellValue(Name);
                cell_L.setCellStyle(contentStyle);
                col++;

                // SID
                Cell cell_M = getCell(currentRow, col);
                cell_M.setCellValue(sid);
                cell_M.setCellStyle(contentStyle);
                col++;

                p_row++;
            }
        }

        // Add category failure drop down list here.
        addCategoryFailureValidation(p_sheet, SEGMENT_START_ROW, p_row, CATEGORY_FAILURE_COLUMN,
                CATEGORY_FAILURE_COLUMN);

        addCommentStatusValidation(p_sheet, SEGMENT_START_ROW, p_row, COMMENT_STATUS_COLUMN,
                COMMENT_STATUS_COLUMN);
    }

    return p_row;
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ReviewersCommentsSimpleReportGenerator.java

License:Apache License

/**
 * Add title to the sheet/*from   ww w . j a  va  2s .c o m*/
 * 
 * @param p_workBook
 * @param p_sheet
 *            the sheet
 * @throws Exception
 */
private void addTitle(Workbook p_workBook, Sheet p_sheet) throws Exception {
    Font titleFont = p_workBook.createFont();
    titleFont.setUnderline(Font.U_NONE);
    titleFont.setFontName("Times");
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle cs = p_workBook.createCellStyle();
    cs.setFont(titleFont);

    Row titleRow = getRow(p_sheet, 0);
    Cell titleCell = getCell(titleRow, 0);
    titleCell.setCellValue(m_bundle.getString("review_reviewers_comments_simple"));
    titleCell.setCellStyle(cs);
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ReviewersCommentsSimpleReportGenerator.java

License:Apache License

/**
 * Add hidden info "RCSR_taskID" for offline uploading. When upload, system
 * can know the report type and current task ID report generated from.
 * //from   w  ww . java 2s. com
 * @param p_workbook
 * @param p_sheet
 * @param p_job
 * @param p_targetLocale
 * @throws Exception
 */
private void addHidenInfoForUpload(Workbook p_workbook, Sheet p_sheet, Job p_job,
        GlobalSightLocale p_targetLocale) throws Exception {
    String reportInfo = "";
    for (Workflow wf : p_job.getWorkflows()) {
        if (p_targetLocale.getId() == wf.getTargetLocale().getId()) {
            Collection tasks = ServerProxy.getTaskManager().getCurrentTasks(wf.getId());
            if (tasks != null) {
                for (Iterator it = tasks.iterator(); it.hasNext();) {
                    Task task = (Task) it.next();
                    reportInfo = ReportConstants.REVIEWERS_COMMENTS_SIMPLE_REPORT_ABBREVIATION + "_"
                            + task.getId();
                }
            }
        }
    }

    Row titleRow = getRow(p_sheet, 0);
    Cell taskIdCell = getCell(titleRow, 26);
    taskIdCell.setCellValue(reportInfo);
    taskIdCell.setCellStyle(contentStyle);

    p_sheet.setColumnHidden(26, true);
}