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.ReviewersCommentsSimpleReportGenerator.java

License:Apache License

/**
 * Add segment header to the sheet for Reviewers Comments Report
 * //from  w w  w .j  a v  a2 s .  c om
 * @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("reviewers_comments_header"));
    cell_C.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 40 * 256);
    col++;

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

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

    Cell cell_F = getCell(segHeaderRow, col);
    cell_F.setCellValue(m_bundle.getString("lb_approved_glossary_target"));
    cell_F.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 30 * 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_job_id_report"));
    cell_H.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 15 * 256);
    col++;

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

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

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

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

License:Apache License

/**
 * For Reviewers Comments Report(Language Sign-off report), Write segment
 * information into each row of the sheet.
 * /*from  w ww  . ja  v  a 2s  .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
                String failure = "";
                Issue issue = issuesMap.get(targetTuv.getId());
                if (issue != null) {
                    failure = issue.getCategory();
                }
                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;
                if (matches.toString().equals("In Context Match"))
                    contentStyle = getIceContentStyle(p_workBook);
                else if (matches.toString().equals("100%"))
                    contentStyle = getExactContentStyle(p_workBook);
                else
                    contentStyle = getContentStyle(p_workBook);

                Row currentRow = getRow(p_sheet, p_row);

                // Source segment
                CellStyle srcStyle;
                if (m_rtlSourceLocale) {
                    if (matches.toString().equals("In Context Match"))
                        srcStyle = getIceRtlContentStyle(p_workBook);
                    else if (matches.toString().equals("100%"))
                        srcStyle = getExactRtlContentStyle(p_workBook);
                    else
                        srcStyle = getRtlContentStyle(p_workBook);
                } else {
                    srcStyle = 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;
                if (m_rtlTargetLocale) {
                    if (matches.toString().equals("In Context Match"))
                        trgStyle = getIceRtlContentStyle(p_workBook);
                    else if (matches.toString().equals("100%"))
                        trgStyle = getExactRtlContentStyle(p_workBook);
                    else
                        trgStyle = getRtlContentStyle(p_workBook);
                } else {
                    trgStyle = contentStyle;
                }
                Cell cell_B = getCell(currentRow, col);
                cell_B.setCellValue(getSegment(pData, targetTuv, m_rtlTargetLocale, jobId));
                cell_B.setCellStyle(trgStyle);
                col++;

                //Reviewers comments
                CellStyle reviewersCommentStyle;
                if (m_rtlTargetLocale) {
                    if (matches.toString().equals("In Context Match"))
                        reviewersCommentStyle = getIceUnlockedRightStyle(p_workBook);
                    else if (matches.toString().equals("100%"))
                        reviewersCommentStyle = getExactUnlockedRightStyle(p_workBook);
                    else
                        reviewersCommentStyle = getUnlockedRightStyle(p_workBook);
                } else {
                    if (matches.toString().equals("In Context Match"))
                        reviewersCommentStyle = getIceUnlockedStyle(p_workBook);
                    else if (matches.toString().equals("100%"))
                        reviewersCommentStyle = getExactUnlockedStyle(p_workBook);
                    else
                        reviewersCommentStyle = getUnlockedStyle(p_workBook);
                }
                Cell cell_C = getCell(currentRow, col);
                cell_C.setCellValue("");
                cell_C.setCellStyle(reviewersCommentStyle);
                col++;

                // Category failure
                Cell cell_D = getCell(currentRow, col);
                cell_D.setCellValue(failure);
                cell_D.setCellStyle(reviewersCommentStyle);
                col++;

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

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

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

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

                // SID// Segment id
                Cell cell_I = getCell(currentRow, col);
                cell_I.setCellValue(sourceTuv.getTu(jobId).getId());
                cell_I.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_J = getCell(currentRow, col);
                cell_J.setCellValue(name);
                cell_J.setCellStyle(contentStyle);
                col++;

                // SID
                Cell cell_K = getCell(currentRow, col);
                cell_K.setCellValue(sid);
                cell_K.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);
    }

    p_sheet.setColumnHidden(7, true);
    p_sheet.setColumnHidden(8, true);
    p_sheet.setColumnHidden(10, true);

    return p_row;
}

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

License:Apache License

/**
 * Add segment header to the sheet for Comments Analysis Report
 * /*w  ww  .  ja va  2 s .c o m*/
 * @param p_workBook
 * @param p_sheet
 *            the sheet
 * @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(bundle.getString("lb_job_id"));
    cell_A.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 10 * 256);
    col++;

    Cell cell_B = getCell(segHeaderRow, col);
    cell_B.setCellValue(bundle.getString("lb_job_name"));
    cell_B.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 30 * 256);
    col++;

    Cell cell_C = getCell(segHeaderRow, col);
    cell_C.setCellValue(bundle.getString("lb_target_locale"));
    cell_C.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 14 * 256);
    col++;

    for (Select select : categoryList) {
        Cell cell_category = getCell(segHeaderRow, col);
        cell_category.setCellValue(select.getValue());
        cell_category.setCellStyle(headerStyle);
        p_sheet.setColumnWidth(col, 21 * 256);
        col++;
    }

    Cell cell_AVG = getCell(segHeaderRow, col);
    cell_AVG.setCellValue(bundle.getString("lb_avg_score"));
    cell_AVG.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 13 * 256);
    col++;

    Cell cell_Overall = getCell(segHeaderRow, col);
    cell_Overall.setCellValue("Overall");
    cell_Overall.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 10 * 256);
    col++;

    Cell cell_Comments = getCell(segHeaderRow, col);
    cell_Comments.setCellValue(bundle.getString("lb_comments"));
    cell_Comments.setCellStyle(headerStyle);
    p_sheet.setColumnWidth(col, 100 * 256);
    col++;
}

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

License:Apache License

/**
 * Add title to the sheet// w  w w.j av  a 2  s .  c  om
 * 
 * @param p_workBook
 * @param p_sheet
 *            the sheet
 * @throws Exception
 */
private void addTitle(Workbook p_workBook, Sheet p_sheet) throws Exception {
    Row titleRow = getRow(p_sheet, 0);
    Cell titleCell = getCell(titleRow, 0);
    titleCell.setCellValue(bundle.getString("scorecard_report"));
    titleCell.setCellStyle(getTitleStyle(p_workBook));
}

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

License:Apache License

/**
 * For Comments Analysis Report, Write segment information into each row of
 * the sheet.//from ww w. ja  va 2  s. c o m
 * 
 * @param p_workBook
 *            the workBook
 * @param p_sheet
 *            the sheet
 * @param p_job
 *            the job data for report
 * @param p_targetLocale
 *            the target locale
 * @param p_dateFormat
 *            the dateFormat
 * @param p_row
 *            the segment row in sheet
 * @throws Exception
 */
@SuppressWarnings("unchecked")
private int writeSegmentInfo(Workbook p_workBook, Sheet p_sheet, Job p_job, Workflow p_workflow, int p_row,
        DecimalFormat numFormat, GlobalSightLocale trgLocale,
        HashMap<String, HashMap<String, Integer>> socreSum, HashMap<String, HashMap<String, Integer>> socreNum,
        Set<GlobalSightLocale> tempLocaleSet) throws Exception {
    List<ScorecardScore> scoreList = ScorecardScoreHelper.getScoreByWrkflowId(p_workflow.getId());
    contentStyle = getContentStyle(p_workBook);
    if (scoreList.size() > 0) {
        int col = 0;
        Row currentRow = getRow(p_sheet, p_row);
        // Job id
        Cell cell_A = getCell(currentRow, col);
        cell_A.setCellValue(p_job.getId());
        cell_A.setCellStyle(contentStyle);
        col++;

        // job name
        Cell cell_B = getCell(currentRow, col);
        cell_B.setCellValue(p_job.getJobName());
        cell_B.setCellStyle(contentStyle);
        col++;

        // TargetPage id
        Cell cell_C = getCell(currentRow, col);
        cell_C.setCellValue(p_workflow.getTargetLocale().toString());
        cell_C.setCellStyle(contentStyle);
        col++;

        int workflowScoreSum = 0;
        int workflowScoreNum = 0;
        //Scores
        for (Select select : categoryList) {
            Cell cell_score = getCell(currentRow, col);
            int scoreValue = 0;
            for (ScorecardScore score : scoreList) {
                if (score.getScorecardCategory().equals(select.getValue())) {
                    scoreValue = score.getScore();
                    String tl = trgLocale.toString();
                    String categoryString = select.getValue();

                    socreSum.get(tl).put(categoryString, socreSum.get(tl).get(categoryString) + scoreValue);
                    workflowScoreSum = workflowScoreSum + scoreValue;
                    socreNum.get(tl).put(categoryString, socreNum.get(tl).get(categoryString) + 1);
                    workflowScoreNum++;

                    tempLocaleSet.add(trgLocale);
                    break;
                }
            }
            if (scoreValue > 0) {
                cell_score.setCellValue(scoreValue);
                cell_score.setCellStyle(getScoreCellStyle(p_workBook, (double) scoreValue));
            } else {
                cell_score.setCellValue("");
                cell_score.setCellStyle(contentStyle);
            }
            col++;
        }

        // AVG Score
        Cell cell_AvgScore = getCell(currentRow, col);
        double avgScore = 0;
        if (workflowScoreNum > 0) {
            avgScore = (double) workflowScoreSum / workflowScoreNum;
            cell_AvgScore.setCellValue(Double.parseDouble(numFormat.format(avgScore)));
            cell_AvgScore.setCellStyle(getScoreCellStyle(p_workBook, avgScore));
        } else {
            cell_AvgScore.setCellValue("");
            cell_AvgScore.setCellStyle(contentStyle);
        }
        col++;

        // Overall
        Cell cell_Overall = getCell(currentRow, col);
        if (avgScore > 0 && avgScore < 3) {
            cell_Overall.setCellValue("Negative");
        } else if (avgScore >= 3) {
            cell_Overall.setCellValue("Positive");
        }
        cell_Overall.setCellStyle(contentStyle);
        col++;

        // Comments
        boolean rtlTargetLocale = EditUtil.isRTLLocale(p_workflow.getTargetLocale().toString());
        CellStyle commentStyle = rtlTargetLocale ? getUnlockedRightStyle(p_workBook)
                : getUnlockedStyle(p_workBook);
        Cell cell_H = getCell(currentRow, col);
        cell_H.setCellValue(((WorkflowImpl) p_workflow).getScorecardComment());
        cell_H.setCellStyle(commentStyle);

        p_row++;
    }

    return p_row;
}

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

License:Apache License

private void addStatisticsSheet(Workbook p_workbook, HashMap<String, HashMap<String, Integer>> socreSum,
        HashMap<String, HashMap<String, Integer>> socreNum, Set<GlobalSightLocale> p_targetLocales,
        DecimalFormat numFormat) throws Exception {
    Sheet statisticsSheet = p_workbook.createSheet("Statistics");
    int row = 0;//  w w  w  . j a  v  a2 s  . c o m
    int col = 0;
    //header
    Row headerRow = getRow(statisticsSheet, row);

    CellStyle headerStyle = getHeaderStyle(p_workbook);
    Cell localeHeaderCell = getCell(headerRow, col);
    localeHeaderCell.setCellValue(bundle.getString("lb_target_locale"));
    localeHeaderCell.setCellStyle(headerStyle);
    statisticsSheet.setColumnWidth(col, 14 * 256);
    col++;

    for (Select category : categoryList) {
        Cell categoryCell = getCell(headerRow, col);
        categoryCell.setCellValue(category.getValue());
        categoryCell.setCellStyle(headerStyle);
        statisticsSheet.setColumnWidth(col, 21 * 256);
        col++;
    }

    Cell avgHeaderCell = getCell(headerRow, col);
    avgHeaderCell.setCellValue(bundle.getString("lb_avg_score"));
    avgHeaderCell.setCellStyle(headerStyle);
    statisticsSheet.setColumnWidth(col, 13 * 256);

    HashMap<String, Integer> categoyAvgSum = new HashMap<String, Integer>();
    HashMap<String, Integer> categoyAvgNum = new HashMap<String, Integer>();
    int totalSum = 0;
    int totalNum = 0;
    List<GlobalSightLocale> scortLocales = sortLocales(new ArrayList<GlobalSightLocale>(p_targetLocales));
    //segment
    for (GlobalSightLocale locale : scortLocales) {
        col = 0;
        row++;
        String tl = locale.toString();

        Row scoreRow = getRow(statisticsSheet, row);
        Cell localeCell = getCell(scoreRow, col);
        localeCell.setCellValue(tl);
        localeCell.setCellStyle(contentStyle);
        col++;

        int avgSum = 0;
        int avgNum = 0;
        for (Select category : categoryList) {
            String cv = category.getValue();
            if (socreSum.get(tl).get(cv) != 0) {
                Cell scoreCell = getCell(scoreRow, col);
                double avgScore = (double) socreSum.get(tl).get(cv) / socreNum.get(tl).get(cv);
                scoreCell.setCellValue(Double.parseDouble(numFormat.format(avgScore)));
                scoreCell.setCellStyle(getScoreCellStyle(p_workbook, avgScore));
                col++;
                //Statistics for locale
                avgSum = avgSum + socreSum.get(tl).get(cv);
                avgNum = avgNum + socreNum.get(tl).get(cv);

                //Statistics for category
                if (categoyAvgSum.get(cv) != null) {
                    categoyAvgSum.put(cv, categoyAvgSum.get(cv) + socreSum.get(tl).get(cv));
                    categoyAvgNum.put(cv, categoyAvgNum.get(cv) + socreNum.get(tl).get(cv));
                } else {
                    categoyAvgSum.put(cv, socreSum.get(tl).get(cv));
                    categoyAvgNum.put(cv, socreNum.get(tl).get(cv));
                }

                //Statistics for total
                totalSum = totalSum + socreSum.get(tl).get(cv);
                totalNum = totalNum + socreNum.get(tl).get(cv);
            } else {
                Cell scoreCell = getCell(scoreRow, col);
                scoreCell.setCellValue("");
                scoreCell.setCellStyle(contentStyle);
                col++;
            }
        }

        Cell avgCell = getCell(scoreRow, col);
        if (avgNum > 0) {
            double avgScore = (double) avgSum / avgNum;
            avgCell.setCellValue(Double.parseDouble(numFormat.format(avgScore)));
            avgCell.setCellStyle(getScoreCellStyle(p_workbook, avgScore));
        } else {
            avgCell.setCellValue("");
            avgCell.setCellStyle(contentStyle);
        }
        col++;
    }

    // avg 
    row++;
    col = 0;
    Row avgRow = getRow(statisticsSheet, row);

    Cell avgCell = getCell(avgRow, col);
    if (totalNum != 0) {
        avgCell.setCellValue(bundle.getString("lb_avg_score"));
        avgCell.setCellStyle(contentStyle);
    }
    col++;

    for (Select category : categoryList) {
        Cell categoryAvgCell = getCell(avgRow, col);
        if (categoyAvgSum.get(category.getValue()) != null) {
            double avgScore = (double) categoyAvgSum.get(category.getValue())
                    / categoyAvgNum.get(category.getValue());
            categoryAvgCell.setCellValue(Double.parseDouble(numFormat.format(avgScore)));
            categoryAvgCell.setCellStyle(getScoreCellStyle(p_workbook, avgScore));
        }
        col++;
    }

    Cell totalAvgCell = getCell(avgRow, col);
    double avgScore = 0;
    if (totalNum != 0) {
        avgScore = (double) totalSum / totalNum;
        totalAvgCell.setCellValue(Double.parseDouble(numFormat.format(avgScore)));
        totalAvgCell.setCellStyle(getScoreCellStyle(p_workbook, avgScore));
    }
}

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

License:Apache License

/**
 * Creates Monthly Sheet/*from   w w w  . j a  v  a  2 s . c om*/
 */
private void createMonthlySheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options,
        Map<String, ReportWordCount> p_wordCounts) throws Exception {
    List<String> searchMonths = p_options.getMonths();

    CellStyle style = getHeaderStyle(p_workbook);

    CellStyle style1 = getHeaderStyle(p_workbook, null, null, null, null);

    CellStyle styleTB = getHeaderStyle(p_workbook, CellStyle.BORDER_THIN, null, CellStyle.BORDER_THIN, null);

    CellStyle styleTB2 = getHeaderStyle(p_workbook, CellStyle.BORDER_THIN, null, CellStyle.BORDER_THIN, null);
    styleTB2.setAlignment(CellStyle.ALIGN_RIGHT);

    CellStyle styleLR = getHeaderStyle(p_workbook, null, CellStyle.BORDER_THIN, null, CellStyle.BORDER_THIN);

    int row = ROWNUMBER, column = 0;

    p_sheet.setColumnWidth(0, 12 * 256);

    Cell cell_A_Header = getCell(getRow(p_sheet, row), column++);
    cell_A_Header.setCellValue(bundle.getString("lb_sumOfTotal"));
    cell_A_Header.setCellStyle(style);
    p_sheet.addMergedRegion(new CellRangeAddress(row, row, column, column + searchMonths.size()));
    setRegionStyle(p_sheet, new CellRangeAddress(row, row, column, column + searchMonths.size()), style);
    Cell cell_B_Header = getCell(getRow(p_sheet, row), column);
    cell_B_Header.setCellValue(bundle.getString("lb_month"));
    cell_B_Header.setCellStyle(style);

    row++;
    column = 0;
    Cell cell_A = getCell(getRow(p_sheet, row), column++);
    cell_A.setCellValue(bundle.getString("lb_lang"));
    cell_A.setCellStyle(style);
    for (String yearAndMonth : searchMonths) {
        Cell cell_Month = getCell(getRow(p_sheet, row), column++);
        cell_Month.setCellValue(Double.valueOf(yearAndMonth.substring(4)));
        cell_Month.setCellStyle(styleTB);
    }
    p_sheet.setColumnWidth(column, 10 * 256);
    Cell cell_LocaleTotal = getCell(getRow(p_sheet, row), column++);
    cell_LocaleTotal.setCellValue(bundle.getString("lb_grandTotal"));
    cell_LocaleTotal.setCellStyle(style);

    // Adds a hidden column, for Excel Sum Check Error.
    Row hiddenRow = getRow(p_sheet, ++row);
    hiddenRow.setZeroHeight(isHidden);
    getRow(p_sheet, row - 1).setHeight(p_sheet.getDefaultRowHeight());

    int dataRow = ++row;
    column = 0;
    double totalWordCount = 0;
    Set<String> locales = getLocals(p_wordCounts);
    for (String locale : locales) {
        Cell cell_A_Locale = getCell(getRow(p_sheet, row), column++);
        cell_A_Locale.setCellValue(locale);
        cell_A_Locale.setCellStyle(styleLR);
        for (String yearAndMonth : searchMonths) {
            ReportWordCount reportWordCount = p_wordCounts.get(getWordCountMapKey(locale, yearAndMonth));
            if (reportWordCount != null) {
                totalWordCount = reportWordCount.getTradosTotalWordCount();
            }
            addNumberCell(p_sheet, column++, row, totalWordCount, style1);
            totalWordCount = 0;
        }
        Cell cell_LocaleTotal_Month = getCell(getRow(p_sheet, row), column);
        cell_LocaleTotal_Month.setCellFormula(getSumOfRow(1, column - 1, row));
        cell_LocaleTotal_Month.setCellStyle(styleLR);
        row++;
        column = 0;
    }

    if (row > (ROWNUMBER + 3)) {
        column = 0;
        Cell cell_GrandTotal = getCell(getRow(p_sheet, row), column++);
        cell_GrandTotal.setCellValue(bundle.getString("lb_grandTotal"));
        cell_GrandTotal.setCellStyle(style);
        for (int i = 0; i < searchMonths.size(); i++) {
            Cell cell_MonthTotal = getCell(getRow(p_sheet, row), column);
            cell_MonthTotal.setCellFormula(getSumOfColumn(dataRow, row - 1, column));
            cell_MonthTotal.setCellStyle(styleTB);
            column++;
        }
        Cell cell_Total = getCell(getRow(p_sheet, row), column);
        cell_Total.setCellFormula(getSumOfColumn(dataRow, row - 1, column));
        cell_Total.setCellStyle(style);
    }
}

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

License:Apache License

private void addLeveragingSheetHeader(Workbook p_workbook, Sheet p_sheet) throws Exception {
    int row = ROWNUMBER, column = 0;

    Row thirRow = getRow(p_sheet, row);//from w  ww  .  jav a  2 s  . c  om
    Cell cell_A = getCell(thirRow, column++);
    cell_A.setCellValue(bundle.getString("lb_lang"));
    cell_A.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_B = getCell(thirRow, column++);
    cell_B.setCellValue(bundle.getString("jobinfo.tradosmatches.invoice.per100matches"));
    cell_B.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_C = getCell(thirRow, column++);
    cell_C.setCellValue(bundle.getString("lb_95_99"));
    cell_C.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_D = getCell(thirRow, column++);
    cell_D.setCellValue(bundle.getString("lb_85_94"));
    cell_D.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_E = getCell(thirRow, column++);
    cell_E.setCellValue(bundle.getString("lb_75_84"));
    cell_E.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_F = getCell(thirRow, column++);
    cell_F.setCellValue(bundle.getString("lb_no_match"));
    cell_F.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 10 * 256);
    Cell cell_G = getCell(thirRow, column++);
    cell_G.setCellValue(bundle.getString("lb_repetition_word_cnt"));
    cell_G.setCellStyle(getHeaderOrangeStyle(p_workbook));

    if (headers[0] != null) {
        p_sheet.setColumnWidth(column, 10 * 256);
        Cell cell_InContext = getCell(thirRow, column++);
        cell_InContext.setCellValue(bundle.getString("lb_in_context_tm"));
        cell_InContext.setCellStyle(getHeaderOrangeStyle(p_workbook));
    }
    if (headers[1] != null) {
        p_sheet.setColumnWidth(column, 10 * 256);
        Cell cell_Context = getCell(thirRow, column++);
        cell_Context.setCellValue(bundle.getString("lb_context_matches"));
        cell_Context.setCellStyle(getHeaderOrangeStyle(p_workbook));
    }

    Cell cell_Total = getCell(thirRow, column++);
    cell_Total.setCellValue(bundle.getString("lb_total"));
    cell_Total.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 13 * 256);
    Cell cell_Leveraging = getCell(thirRow, column++);
    cell_Leveraging.setCellValue(bundle.getString("lb_leveraging"));
    cell_Leveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));
}

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

License:Apache License

private void addLeveragingSheetData(Workbook p_workbook, Sheet p_sheet,
        Map<String, ReportWordCount> p_wordCounts) throws Exception {
    // Prepare report data
    Map<String, ReportWordCount> sumWordCounts = new HashMap<String, ReportWordCount>();
    for (String key : p_wordCounts.keySet()) {
        ReportWordCount monthWordCount = p_wordCounts.get(key);
        String sumKey = key.substring(0, 5);
        ReportWordCount sumWordCount = sumWordCounts.get(sumKey);
        if (sumWordCount == null) {
            sumWordCounts.put(sumKey, monthWordCount.clone());
        } else {//w w w . ja v  a  2  s.  com
            sumWordCount.addTradosWordCount(monthWordCount);
        }
    }

    // Display report data.
    int row = ROWNUMBER + 1;
    int column = 0;
    List<String> sumKeyList = new ArrayList<String>(sumWordCounts.keySet());
    SortUtil.sort(sumKeyList);
    for (String sumKey : sumKeyList) {
        ReportWordCount sumWordCount = sumWordCounts.get(sumKey);
        if (sumWordCount != null) {
            Cell cell_A_Lang = getCell(getRow(p_sheet, row), column++);
            cell_A_Lang.setCellValue(sumKey);
            cell_A_Lang.setCellStyle(getHeaderStyle(p_workbook));

            addNumberCell(p_sheet, column++, row, sumWordCount.getTrados100WordCount(),
                    getHeaderStyle(p_workbook));

            addNumberCell(p_sheet, column++, row, sumWordCount.getTrados95to99WordCount(),
                    getHeaderStyle(p_workbook));

            addNumberCell(p_sheet, column++, row, sumWordCount.getTrados85to94WordCount(),
                    getHeaderStyle(p_workbook));

            addNumberCell(p_sheet, column++, row, sumWordCount.getTrados75to84WordCount(),
                    getHeaderStyle(p_workbook));

            addNumberCell(p_sheet, column++, row,
                    sumWordCount.getTradosNoMatchWordCount() + sumWordCount.getTrados50to74WordCount(),
                    getHeaderStyle(p_workbook));

            addNumberCell(p_sheet, column++, row, sumWordCount.getTradosRepsWordCount(),
                    getHeaderStyle(p_workbook));

            if (headers[0] != null) {
                addNumberCell(p_sheet, column++, row, sumWordCount.getTradosInContextMatchWordCount(),
                        getHeaderStyle(p_workbook));
            }
            if (headers[1] != null) {
                addNumberCell(p_sheet, column++, row, sumWordCount.getTradosContextMatchWordCount(),
                        getHeaderStyle(p_workbook));
            }

            addNumberCell(p_sheet, column++, row, sumWordCount.getTradosTotalWordCount(),
                    getHeaderStyle(p_workbook));
            String leveraging = "(1-F" + (row + 1) + "/" + getColumnName(column - 1) + (row + 1) + ")*100";

            Cell cell_TotalLeveraging = getCell(getRow(p_sheet, row), column++);
            cell_TotalLeveraging.setCellFormula(leveraging);
            cell_TotalLeveraging.setCellStyle(getFloatStyle(p_workbook));

            row++;
            column = 0;
        }
    }

    addLeveragingSheetTotal(p_workbook, p_sheet, row);
}

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

License:Apache License

private void addLeveragingSheetTotal(Workbook p_workbook, Sheet p_sheet, int row) throws Exception {
    // Total Row//from  w  w  w  .ja  va  2s .  c o  m
    if (row > (ROWNUMBER + 1)) {
        int column = 0;
        Cell cell_GrandTotal = getCell(getRow(p_sheet, row), column++);
        cell_GrandTotal.setCellValue(bundle.getString("lb_grandTotal"));
        cell_GrandTotal.setCellStyle(getHeaderOrangeStyle(p_workbook));
        while (column < (p_sheet.getRow(row - 1).getPhysicalNumberOfCells() - 1)) {
            Cell totalCell = getCell(getRow(p_sheet, row), column);
            totalCell.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column));
            totalCell.setCellStyle(getHeaderOrangeStyle(p_workbook));
            column++;
        }
        String leveraging = "(1-F" + (row + 1) + "/" + getColumnName(column - 1) + (row + 1) + ")*100";

        Cell cell_TotalLeveraging = getCell(getRow(p_sheet, row), column++);
        cell_TotalLeveraging.setCellFormula(leveraging);
        cell_TotalLeveraging.setCellStyle(getFloatSumStyle(p_workbook));
    }
}