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

Introduction

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

Prototype

void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;

Source Link

Document

Sets formula for this cell.

Usage

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

License:Apache License

/**
 * add Comment for each row of each sheet
 * /*w w w .  j a  v a 2 s  .c  om*/
 * @exception Exception
 */
private void addComment(HttpServletRequest p_request, Job j, Workbook p_workbook, Sheet p_sheet, IntHolder row,
        Comment comment, int flag) throws Exception {
    SimpleDateFormat dateFormat = new SimpleDateFormat(p_request.getParameter("dateFormat"));

    String jobPrefix = "Job Comment ";
    String taskPrefix = "Activity Comment ";
    int c = 0;
    int r = row.getValue();

    Row p_row = getRow(p_sheet, r);
    // 2.3 Job ID column. Insert GlobalSight job number here.
    Cell cell_A = getCell(p_row, c++);
    cell_A.setCellValue(j.getJobId());
    cell_A.setCellStyle(getContentStyle(p_workbook));

    // 2.4 Job: Insert Job name here.
    Cell cell_B = getCell(p_row, c++);
    cell_B.setCellValue(j.getJobName());
    cell_B.setCellStyle(getContentStyle(p_workbook));

    //Insert comment type
    Cell cell_C = getCell(p_row, c++);
    if (JOB_FLAG == flag) {
        cell_C.setCellValue(jobPrefix);
        cell_C.setCellStyle(getContentStyle(p_workbook));
    } else if (TASK_FLAG == flag) {
        cell_C.setCellValue(taskPrefix);
        cell_C.setCellStyle(getContentStyle(p_workbook));
    }

    // 2.5 Lang: Insert each target language identifier for each workflow
    // in the retrieved Job on a different row.
    Cell cell_D = getCell(p_row, c++);
    if ((this.targetPageVar != null) && (this.targetPageVar.getGlobalSightLocale() != null)) {
        cell_D.setCellValue(this.targetPageVar.getGlobalSightLocale().getDisplayName(uiLocale));
        cell_D.setCellStyle(getContentStyle(p_workbook));
    } else {
        cell_D.setCellValue("");
        cell_D.setCellStyle(getContentStyle(p_workbook));
    }

    // 2.6 Word count: Insert Segement Number for the job.
    Cell cell_E = getCell(p_row, c++);
    cell_E.setCellValue("");
    cell_E.setCellStyle(getContentStyle(p_workbook));

    // by who
    Cell cell_F = getCell(p_row, c++);
    cell_F.setCellValue(UserUtil.getUserNameById(comment.getCreatorId()));
    cell_F.setCellStyle(getContentStyle(p_workbook));

    // 2.7 Comment create date: Insert Comment creation date.
    Cell cell_G = getCell(p_row, c++);
    cell_G.setCellValue(dateFormat.format(comment.getCreatedDateAsDate()));
    cell_G.setCellStyle(getContentStyle(p_workbook));

    // 2.8 add Comment Status
    if (showStatus) {
        Cell cell_Status = getCell(p_row, c++);
        cell_Status.setCellValue("");
        cell_Status.setCellStyle(getContentStyle(p_workbook));
    }
    // 2.9 add Comment priority
    if (showPriority) {
        Cell cell_Priority = getCell(p_row, c++);
        cell_Priority.setCellValue("");
        cell_Priority.setCellStyle(getContentStyle(p_workbook));
    }

    // 2.10 add Comment Category
    if (showCategory) {
        Cell cell_Category = getCell(p_row, c++);
        cell_Category.setCellValue("");
        cell_Category.setCellStyle(getContentStyle(p_workbook));
    }
    //add source and target segment
    if (showSourAndTar) {
        Cell cell_Source = getCell(p_row, c++);
        cell_Source.setCellValue("");
        cell_Source.setCellStyle(getContentStyle(p_workbook));

        Cell cell_Target = getCell(p_row, c++);
        cell_Target.setCellValue("");
        cell_Target.setCellStyle(getContentStyle(p_workbook));
    }

    // 2.11 add Comment comment
    Cell cell_CommentHeader = getCell(p_row, c++);
    cell_CommentHeader.setCellValue("");
    cell_CommentHeader.setCellStyle(getContentStyle(p_workbook));

    Cell cell_CommentBody = getCell(p_row, c++);
    cell_CommentBody.setCellValue(comment.getComment());
    cell_CommentBody.setCellStyle(getContentStyle(p_workbook));

    Cell cell_Link = getCell(p_row, c++);
    if (flag != SEGMENT_FLAG) {
        cell_Link.setCellValue("");
    } else {
        cell_Link.setCellFormula("HYPERLINK(\"" + pageUrl + "\",\"" + externalPageId + "\")");
    }
    cell_Link.setCellStyle(getContentStyle(p_workbook));

    row.inc();

}

---

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

License:Apache License

/**
 * add Comment for each row, for segment comment usage, each row contains a
 * comment history/*from   ww w  .  ja  v a2 s . c  om*/
 * 
 * @exception Exception
 */
private void addCommentHistory(HttpServletRequest p_request, Job j, Workbook p_workbook, Sheet p_sheet,
        IntHolder row, Comment comment, IssueHistory issueHistory, int flag) throws Exception {
    String segmentPrefix = "Segment Comment";
    SimpleDateFormat dateFormat = new SimpleDateFormat(p_request.getParameter("dateFormat"));
    int c = 0;
    int r = row.getValue();
    // 2.3.2 Job ID column. Insert GlobalSight job number here.
    Row p_row = getRow(p_sheet, r);
    Cell cell_A = getCell(p_row, c++);
    cell_A.setCellValue(j.getJobId());
    cell_A.setCellStyle(getContentStyle(p_workbook));

    // 2.4.2 Job: Insert Job name here.
    Cell cell_B = getCell(p_row, c++);
    cell_B.setCellValue(j.getJobName());
    cell_B.setCellStyle(getContentStyle(p_workbook));

    Cell cell_C = getCell(p_row, c++);
    cell_C.setCellValue(segmentPrefix);
    cell_C.setCellStyle(getContentStyle(p_workbook));

    // 2.5.2 Lang: Insert each target language identifier for each workflow
    // in the retrieved Job on a different row.
    Cell cell_D = getCell(p_row, c++);
    if ((this.targetPageVar != null) && (this.targetPageVar.getGlobalSightLocale() != null)) {
        cell_D.setCellValue(this.targetPageVar.getGlobalSightLocale().getDisplayName(uiLocale));
        cell_D.setCellStyle(getContentStyle(p_workbook));
    } else {
        cell_D.setCellValue("");
        cell_D.setCellStyle(getContentStyle(p_workbook));
    }

    // 2.6.2 Insert Segement Number for the job.
    Cell cell_E = getCell(p_row, c++);
    Integer segmentNum = Integer
            .valueOf(CommentComparator.getSegmentIdFromLogicalKey(((Issue) comment).getLogicalKey()));
    cell_E.setCellValue(segmentNum);
    cell_E.setCellStyle(getContentStyle(p_workbook));

    // by who
    Cell cell_F = getCell(p_row, c++);
    cell_F.setCellValue(UserUtil.getUserNameById(issueHistory.reportedBy()));
    cell_F.setCellStyle(getContentStyle(p_workbook));

    // 2.7.2 Comment create date: Insert Comment creation date.\
    Cell cell_G = getCell(p_row, c++);
    cell_G.setCellValue(dateFormat.format(issueHistory.dateReportedAsDate()));
    cell_G.setCellStyle(getContentStyle(p_workbook));

    // 2.8.2 add Comment Status
    if (showStatus) {
        Cell cell_Status = getCell(p_row, c++);
        cell_Status.setCellValue(((Issue) comment).getStatus());
        cell_Status.setCellStyle(getContentStyle(p_workbook));
    }
    // 2.9.2 add Comment priority
    if (showPriority) {
        Cell cell_Priority = getCell(p_row, c++);
        cell_Priority.setCellValue(((Issue) comment).getPriority());
        cell_Priority.setCellStyle(getContentStyle(p_workbook));
    }

    // 2.10.2 add Comment Category
    if (showCategory) {
        Cell cell_Category = getCell(p_row, c++);
        cell_Category.setCellValue(((Issue) comment).getCategory());
        cell_Category.setCellStyle(getContentStyle(p_workbook));
    }

    if (showSourAndTar) {
        long targetLocalId = this.targetPageVar.getGlobalSightLocale().getId();
        long sourceLocalId = j.getSourceLocale().getId();
        Tuv sourceTuv = SegmentTuvUtil.getTuvByTuIdLocaleId(segmentNum, sourceLocalId, j.getId());

        Tuv targetTuv = SegmentTuvUtil.getTuvByTuIdLocaleId(segmentNum, targetLocalId, j.getId());
        PseudoData pData = new PseudoData();
        pData.setMode(PseudoConstants.PSEUDO_COMPACT);

        Cell cell_SourceSegment = getCell(p_row, c++);
        cell_SourceSegment.setCellValue(getSegment(pData, sourceTuv, j.getId()));
        cell_SourceSegment.setCellStyle(getContentStyle(p_workbook));

        Cell cell_TargetSegment = getCell(p_row, c++);
        cell_TargetSegment.setCellValue(getSegment(pData, targetTuv, j.getId()));
        cell_TargetSegment.setCellStyle(getContentStyle(p_workbook));
    }

    // 2.11.2 add comment header
    Cell cell_CommentHeader = getCell(p_row, c++);
    cell_CommentHeader.setCellValue(comment.getComment());
    cell_CommentHeader.setCellStyle(getContentStyle(p_workbook));

    // 2.12.2 add comment body
    Cell cell_CommentBody = getCell(p_row, c++);
    cell_CommentBody.setCellValue(issueHistory.getComment());
    cell_CommentBody.setCellStyle(getContentStyle(p_workbook));

    // 2.13.2 add link
    Cell cell_Link = getCell(p_row, c++);
    cell_Link.setCellFormula("HYPERLINK(\"" + pageUrl + "\",\"" + externalPageId + "\")");
    cell_Link.setCellStyle(getContentStyle(p_workbook));

    row.inc();
}

---

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

License:Apache License

private void addTotalsPerLang(Workbook p_workbook, Sheet p_sheet, final int p_sheetCategory, IntHolder p_row)
        throws Exception {
    Font subTotalFont = p_workbook.createFont();
    subTotalFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    subTotalFont.setColor(IndexedColors.BLACK.getIndex());
    subTotalFont.setUnderline(Font.U_NONE);
    subTotalFont.setFontName("Arial");
    subTotalFont.setFontHeightInPoints((short) 10);

    CellStyle subTotalStyle = p_workbook.createCellStyle();
    subTotalStyle.setFont(subTotalFont);

    String title = m_bundle.getString("lb_total_cost_per_lang");
    int row = p_row.getValue() + 4; // skip a row
    ArrayList<String> locales = new ArrayList<String>(totalCost.keySet());
    SortUtil.sort(locales);/*from www  . jav  a  2 s .c  o m*/

    int col = m_data.getSumStartCol();
    Row theRow = getRow(p_sheet, row);

    Cell cell_Title = getCell(theRow, col - 3);
    cell_Title.setCellValue(title);
    cell_Title.setCellStyle(subTotalStyle);

    File imgFile = File.createTempFile("GSJobChart", ".png");
    JfreeCharUtil.drawPieChart2D("", totalCostDate, imgFile);
    Drawing patriarch = p_sheet.createDrawingPatriarch();
    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 15, row - 1, 22, row + 24);
    ByteArrayOutputStream img_bytes = new ByteArrayOutputStream();
    InputStream is = imgFile.toURI().toURL().openStream();
    int b;
    while ((b = is.read()) != -1) {
        img_bytes.write(b);
    }
    is.close();
    int i = p_workbook.addPicture(img_bytes.toByteArray(), SXSSFWorkbook.PICTURE_TYPE_PNG);
    patriarch.createPicture(anchor, i);
    for (String locale : locales) {
        List<Integer> indexs = totalCost.get(locale);
        StringBuffer values = new StringBuffer();
        for (Integer index : indexs) {
            if (values.length() == 0) {
                values.append("SUM(");
            } else {
                values.append("+");
            }

            values.append(totalCol).append(index);
        }
        values.append(")");

        Row perRow = getRow(p_sheet, row);

        Cell cell_Locale = getCell(perRow, col - 1);
        cell_Locale.setCellValue(locale);
        cell_Locale.setCellStyle(getContentStyle(p_workbook));
        Cell cell_Total = getCell(perRow, col);
        cell_Total.setCellFormula(values.toString());
        cell_Total.setCellStyle(getMoneyStyle(p_workbook));
        row++;
    }

    // Reset total column number for every sheet.
    totalCol = null;
}

---

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

License:Apache License

/**
 * Add totals row//from ww  w . j  av a  2  s  .co  m
 * 
 * @param p_sheet
 * @param p_sheetCategory
 * @param p_row
 * @param p_data
 * @param bundle
 * @throws Exception
 */
private void addTotals(Workbook p_workbook, Sheet p_sheet, final int p_sheetCategory, IntHolder p_row)
        throws Exception {
    int row = p_row.getValue() + 1; // skip a row

    String title = m_bundle.getString("lb_totals");
    java.text.NumberFormat.getCurrencyInstance(Locale.US);
    // Get Summary Start Column
    int c = m_data.getSumStartCol();
    String sumStartCol = getColumnName(c);
    Row theRow = getRow(p_sheet, row);

    Cell cell_A = getCell(theRow, 0);
    cell_A.setCellValue(title);
    cell_A.setCellStyle(getSubTotalStyle(p_workbook));
    // modify the number 3 to "sumStartCellCol-B" for "Add Job Id into
    // online job report" issue
    p_sheet.addMergedRegion(new CellRangeAddress(row, row, 0, sumStartCol.charAt(0) - 'B'));
    setRegionStyle(p_sheet, new CellRangeAddress(row, row, 0, sumStartCol.charAt(0) - 'B'),
            getSubTotalStyle(p_workbook));
    int lastRow = p_row.getValue() - 2;

    // add in word count totals
    // word counts
    Cell cell_B = getCell(theRow, c++);
    cell_B.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_B.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_C = getCell(theRow, c++);
    cell_C.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_C.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_D = getCell(theRow, c++);
    cell_D.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_D.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_E = getCell(theRow, c++);
    cell_E.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_E.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_F = getCell(theRow, c++);
    cell_F.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_F.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    if (m_data.isTradosStyle()) {
        Cell cell_G = getCell(theRow, c++);
        cell_G.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_G.setCellStyle(getSubTotalStyle(p_workbook));
        sumStartCol = getColumnName(c);

        Cell cell_H = getCell(theRow, c++);
        cell_H.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_H.setCellStyle(getSubTotalStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    Cell cell_MT = getCell(theRow, c++);
    cell_MT.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_MT.setCellStyle(getSubTotalStyle(p_workbook));
    sumStartCol = getColumnName(c);

    if (m_data.useInContext) {
        Cell cell_InContext = getCell(theRow, c++);
        cell_InContext.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_InContext.setCellStyle(getSubTotalStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    Cell cell_Score = getCell(theRow, c++);
    cell_Score.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_Score.setCellStyle(getSubTotalStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_MTEWC = getCell(theRow, c++);
    cell_MTEWC.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_MTEWC.setCellStyle(getSubTotalStyle(p_workbook));
    sumStartCol = getColumnName(c);

    // word count costs
    Cell cell_K = getCell(theRow, c++);
    cell_K.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_K.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_L = getCell(theRow, c++);
    cell_L.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_L.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_M = getCell(theRow, c++);
    cell_M.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_M.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_N = getCell(theRow, c++);
    cell_N.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_N.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_O = getCell(theRow, c++);
    cell_O.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_O.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_P = getCell(theRow, c++);
    cell_P.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_P.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_Q = getCell(theRow, c++);
    cell_Q.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_Q.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    if (m_data.isTradosStyle()) {
        Cell cell_R = getCell(theRow, c++);
        cell_R.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_R.setCellStyle(getTotalMoneyStyle(p_workbook));
        sumStartCol = getColumnName(c);

        Cell cell_S = getCell(theRow, c++);
        cell_S.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_S.setCellStyle(getTotalMoneyStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    if (m_data.useInContext) {
        Cell cell_InContext = getCell(theRow, c++);
        cell_InContext.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_InContext.setCellStyle(getTotalMoneyStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    // add an extra column for Dell Tracking Use
    Cell cell_V = getCell(theRow, c++);
    cell_V.setCellValue("");
    cell_V.setCellStyle(getTotalMoneyStyle(p_workbook));
}

---

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

License:Apache License

/**
 * Add totals row/*from   w  ww.ja  v  a2s  . c o m*/
 * 
 * @param p_sheet
 * @param p_sheetCategory
 * @param p_row
 * @param p_data
 * @param bundle
 * @throws Exception
 */
private void addTotals(Workbook p_workbook, Sheet p_sheet, final int p_sheetCategory, IntHolder p_row)
        throws Exception {
    int row = p_row.getValue() + 1; // skip a row

    String title = m_bundle.getString("lb_totals");
    java.text.NumberFormat.getCurrencyInstance(Locale.US);
    // Get Summary Start Column
    int c = m_data.getSumStartCol();
    String sumStartCol = getColumnName(c);
    Row theRow = getRow(p_sheet, row);

    Cell cell_A = getCell(theRow, 0);
    cell_A.setCellValue(title);
    cell_A.setCellStyle(getSubTotalStyle(p_workbook));
    // modify the number 3 to "sumStartCellCol-B" for "Add Job Id into
    // online job report" issue
    p_sheet.addMergedRegion(new CellRangeAddress(row, row, 0, sumStartCol.charAt(0) - 'B'));
    setRegionStyle(p_sheet, new CellRangeAddress(row, row, 0, sumStartCol.charAt(0) - 'B'),
            getSubTotalStyle(p_workbook));
    int lastRow = p_row.getValue() - 2;

    // add in word count totals
    // word counts
    Cell cell_B = getCell(theRow, c++);
    cell_B.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_B.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_C = getCell(theRow, c++);
    cell_C.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_C.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_D = getCell(theRow, c++);
    cell_D.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_D.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_E = getCell(theRow, c++);
    cell_E.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_E.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    Cell cell_F = getCell(theRow, c++);
    cell_F.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_F.setCellStyle(getSubTotalStyle(p_workbook));

    sumStartCol = getColumnName(c);
    if (m_data.isTradosStyle()) {
        Cell cell_G = getCell(theRow, c++);
        cell_G.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_G.setCellStyle(getSubTotalStyle(p_workbook));
        sumStartCol = getColumnName(c);

        Cell cell_H = getCell(theRow, c++);
        cell_H.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_H.setCellStyle(getSubTotalStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    if (m_data.useInContext) {
        Cell cell_InContext = getCell(theRow, c++);
        cell_InContext.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_InContext.setCellStyle(getSubTotalStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    // word count costs
    Cell cell_K = getCell(theRow, c++);
    cell_K.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_K.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_L = getCell(theRow, c++);
    cell_L.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_L.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_M = getCell(theRow, c++);
    cell_M.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_M.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_N = getCell(theRow, c++);
    cell_N.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_N.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_O = getCell(theRow, c++);
    cell_O.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_O.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_P = getCell(theRow, c++);
    cell_P.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_P.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    Cell cell_Q = getCell(theRow, c++);
    cell_Q.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
    cell_Q.setCellStyle(getTotalMoneyStyle(p_workbook));
    sumStartCol = getColumnName(c);

    if (m_data.isTradosStyle()) {
        Cell cell_R = getCell(theRow, c++);
        cell_R.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_R.setCellStyle(getTotalMoneyStyle(p_workbook));
        sumStartCol = getColumnName(c);

        Cell cell_S = getCell(theRow, c++);
        cell_S.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_S.setCellStyle(getTotalMoneyStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    if (m_data.useInContext) {
        Cell cell_InContext = getCell(theRow, c++);
        cell_InContext.setCellFormula("SUM(" + sumStartCol + "5:" + sumStartCol + lastRow + ")");
        cell_InContext.setCellStyle(getTotalMoneyStyle(p_workbook));
        sumStartCol = getColumnName(c);
    }

    // add an extra column for Dell Tracking Use
    Cell cell_V = getCell(theRow, c++);
    cell_V.setCellValue("");
    cell_V.setCellStyle(getTotalMoneyStyle(p_workbook));
}

---

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

License:Apache License

/**
 * Creates Monthly Sheet/* w  w  w  .  j  ava2s  .  c o  m*/
 */
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 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 {/*  www . j  ava 2 s.c o  m*/
            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 ww w  .j  av a2  s  . 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));
    }
}

---

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

License:Apache License

/**
 * Creates Leveraging Sheet/*from w  w w.j  av  a  2 s  . com*/
 */
private void createCostsSheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options,
        Map<String, ReportWordCount> p_wordCounts) throws Exception {
    int rowLen = p_sheet.getPhysicalNumberOfRows();
    int colLen = p_sheet.getRow(2).getPhysicalNumberOfCells();
    int wordTotalCol = colLen - 2;
    int row = ROWNUMBER, column = colLen - 1;
    int costCol;
    Map<String, Double> p_ratesMap = null;
    for (int r = 2; r < rowLen + ROWNUMBER; r++) {
        Row theRow = getRow(p_sheet, r);
        theRow.removeCell(getCell(theRow, column));
    }
    p_sheet.removeColumnBreak(column);
    // Rates Columns
    for (int dis = column - 1; column < colLen + dis - 2; column++) {
        Cell cell_From = p_sheet.getRow(row).getCell(column - dis);
        Cell cell_To = getCell(p_sheet.getRow(row), column);
        cell_To.setCellValue(cell_From.getStringCellValue());
        cell_To.setCellStyle(cell_From.getCellStyle());
        p_sheet.setColumnWidth(column, p_sheet.getColumnWidth(column - dis));
        // Adds Rates for Match Type
        for (int rateRow = row + 1; rateRow <= rowLen; rateRow++) {
            String matchType = p_sheet.getRow(ROWNUMBER).getCell(column).getStringCellValue();
            String targetLocale = p_sheet.getRow(rateRow).getCell(0).getStringCellValue();
            double rate = getRate(matchType, targetLocale, p_ratesMap);
            addNumberCell(p_sheet, column, rateRow, rate, getMoneyStyle(p_workbook));
        }
    }

    // Cost Columns Head
    costCol = column;
    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostWithLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostWithLeveraging.setCellValue(bundle.getString("lb_report_costWithLeveraging"));
    cell_CostWithLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostNoLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostNoLeveraging.setCellValue(bundle.getString("lb_report_costNoLeveraging"));
    cell_CostNoLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 15 * 256);
    Cell cell_Savings = getCell(getRow(p_sheet, row), column++);
    cell_Savings.setCellValue(bundle.getString("lb_savings"));
    cell_Savings.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_Percent = getCell(getRow(p_sheet, row), column++);
    cell_Percent.setCellValue("%");
    cell_Percent.setCellStyle(getHeaderOrangeStyle(p_workbook));
    // Cost Columns Data
    for (row = ROWNUMBER + 1; row < (rowLen + ROWNUMBER); row++) {
        String leveragingForm = getCostWithLeveraging(1, wordTotalCol - 1, wordTotalCol, (row + 1));
        String noLeveragingForm = getColumnName(wordTotalCol) + (row + 1) + "*"
                + getColumnName(wordTotalCol + 5) + (row + 1);
        String savingForm = getColumnName(costCol + 1) + (row + 1) + "-" + getColumnName(costCol) + (row + 1);
        String percent = getColumnName(costCol + 2) + (row + 1) + "/" + getColumnName(costCol + 1) + (row + 1);

        Row theRow = getRow(p_sheet, row);
        Cell cell_Leveraging = getCell(theRow, costCol);
        cell_Leveraging.setCellFormula(leveragingForm);
        cell_Leveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_NoLeveraging = getCell(theRow, costCol + 1);
        cell_NoLeveraging.setCellFormula(noLeveragingForm);
        cell_NoLeveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_Saving = getCell(theRow, costCol + 2);
        cell_Saving.setCellFormula(savingForm);
        cell_Saving.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_PercentData = getCell(theRow, costCol + 3);
        cell_PercentData.setCellFormula(percent);
        cell_PercentData.setCellStyle(getPercentStyle(p_workbook));
    }

    if (rowLen > 1) {
        row = rowLen + 1;
        column = 1;
        for (; column < colLen - 1; column++) {
            Cell cell_Total = getCell(getRow(p_sheet, row), column);
            cell_Total.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column));
            cell_Total.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }
        for (; column < costCol; column++) {
            Cell cell = getCell(getRow(p_sheet, row), column);
            cell.setCellValue("");
            cell.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }

        // Summary Cost Columns
        Cell cell_SumLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumNoLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumNoLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumNoLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumSaving = getCell(getRow(p_sheet, row), column);
        cell_SumSaving.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumSaving.setCellStyle(getMoneySumStyle(p_workbook));

        String percent = getColumnName(column - 1) + (row + 1) + "/" + getColumnName(column - 2) + (row + 1);
        Cell cell_AvgPercent = getCell(getRow(p_sheet, row), column);
        cell_AvgPercent.setCellFormula(percent);
        cell_AvgPercent.setCellStyle(getPercentSumStyle(p_workbook));
    }
}

---

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

License:Apache License

private void writeTotal() throws Exception {
    Font totalFont = workbook.createFont();
    totalFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    totalFont.setColor(IndexedColors.BLACK.getIndex());
    totalFont.setUnderline(Font.U_NONE);
    totalFont.setFontName("Arial");
    totalFont.setFontHeightInPoints((short) 9);

    CellStyle totalStyle = workbook.createCellStyle();
    totalStyle.setFont(totalFont);/*from ww  w  .j a  v a2  s  .c  om*/

    Set<Integer> keys = totalCells.keySet();

    if (keys.size() > 0) {
        row++;
        Cell cell_B = getCell(getRow(row), 1);
        cell_B.setCellValue(bundle.getString("lb_total"));
        cell_B.setCellStyle(totalStyle);

        for (Integer key : keys) {
            Cell cell = getCell(getRow(row), key - 1);
            cell.setCellFormula(getTotalFormula(key, totalCells.get(key)));
            cell.setCellStyle(totalStyle);
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• »