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

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

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

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

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

License:Apache License

public void writeProjectDataForTradosMatches(Workbook p_workbook, HashMap p_projectMap, IntHolder p_row,
        MyData p_data) throws Exception {
    Sheet theSheet = p_data.tradosSheet;
    ArrayList projects = new ArrayList(p_projectMap.keySet());
    SortUtil.sort(projects);//  w  w  w . j a va2s .  c o  m
    Iterator projectIter = projects.iterator();

    while (projectIter.hasNext()) {
        String jobName = (String) projectIter.next();
        boolean isWrongJob = p_data.wrongJobNames.contains(jobName);
        HashMap localeMap = (HashMap) p_projectMap.get(jobName);
        ArrayList locales = new ArrayList(localeMap.keySet());
        SortUtil.sort(locales);
        Iterator localeIter = locales.iterator();
        BigDecimal projectTotalWordCountCost = new BigDecimal(BIG_DECIMAL_ZERO_STRING);
        while (localeIter.hasNext()) {
            int row = p_row.getValue();
            int col = 0;
            Row theRow = getRow(theSheet, row);
            String localeName = (String) localeIter.next();
            ProjectWorkflowData data = (ProjectWorkflowData) localeMap.get(localeName);

            CellStyle temp_dateStyle = getDateStyle(p_workbook);
            CellStyle temp_moneyStyle = getMoneyStyle(p_workbook);
            CellStyle temp_normalStyle = getContentStyle(p_workbook);
            // WritableCellFormat temp_wordCountValueRightFormat =
            // wordCountValueRightFormat;
            if (data.wasExportFailed) {
                temp_dateStyle = getFailedDateStyle(p_workbook);
                temp_moneyStyle = getFailedMoneyStyle(p_workbook);
                temp_normalStyle = getRedCellStyle(p_workbook);
            }

            Cell cell_A = getCell(theRow, col++);
            cell_A.setCellValue(data.jobId);

            Cell cell_B = getCell(theRow, col++);
            cell_B.setCellValue(data.jobName);
            if (isWrongJob) {
                cell_A.setCellStyle(getWrongJobStyle(p_workbook));
                cell_B.setCellStyle(getWrongJobStyle(p_workbook));
            } else {
                cell_A.setCellStyle(temp_normalStyle);
                ;
                cell_B.setCellStyle(temp_normalStyle);
            }
            theSheet.setColumnWidth(col - 2, 5 * 256);
            theSheet.setColumnWidth(col - 1, 50 * 256);
            Cell cell_C = getCell(theRow, col++);
            cell_C.setCellValue(data.poNumber);
            cell_C.setCellStyle(temp_normalStyle); // PO number

            Cell cell_D = getCell(theRow, col++);
            cell_D.setCellValue(data.projectDesc);
            cell_D.setCellStyle(temp_normalStyle);
            theSheet.setColumnWidth(col - 1, 22 * 256);
            /* data.creationDate.toString())); */
            Cell cell_E = getCell(theRow, col++);
            cell_E.setCellValue(data.creationDate);
            cell_E.setCellStyle(temp_dateStyle);
            theSheet.setColumnWidth(col - 1, 15 * 256);

            Cell cell_F = getCell(theRow, col++);
            cell_F.setCellValue(data.targetLang);
            cell_F.setCellStyle(temp_normalStyle);

            Cell cell_G = getCell(theRow, col++);
            cell_G.setCellValue(data.trados100WordCount);
            cell_G.setCellStyle(temp_normalStyle);
            int numwidth = 10;
            theSheet.setColumnWidth(col - 1, numwidth * 256);

            Cell cell_H = getCell(theRow, col++);
            cell_H.setCellValue(data.trados95to99WordCount);
            cell_H.setCellStyle(temp_normalStyle);
            theSheet.setColumnWidth(col - 1, numwidth * 256);

            Cell cell_I = getCell(theRow, col++);
            cell_I.setCellValue(data.trados85to94WordCount);
            cell_I.setCellStyle(temp_normalStyle);
            theSheet.setColumnWidth(col - 1, numwidth * 256);

            Cell cell_J = getCell(theRow, col++);
            cell_J.setCellValue(data.trados75to84WordCount);
            cell_J.setCellStyle(temp_normalStyle);
            theSheet.setColumnWidth(col - 1, numwidth * 256);

            Cell cell_K = getCell(theRow, col++);
            cell_K.setCellValue(data.tradosNoMatchWordCount + data.trados50to74WordCount);
            cell_K.setCellStyle(temp_normalStyle);
            theSheet.setColumnWidth(col - 1, numwidth * 256);

            Cell cell_L = getCell(theRow, col++);
            cell_L.setCellValue(data.tradosRepsWordCount);
            cell_L.setCellStyle(temp_normalStyle);
            theSheet.setColumnWidth(col - 1, numwidth * 256);
            if (p_data.headers[0] != null) {
                Cell cell_InContext = getCell(theRow, col++);
                cell_InContext.setCellValue(data.tradosInContextMatchWordCount);
                cell_InContext.setCellStyle(temp_normalStyle);
                theSheet.setColumnWidth(col - 1, numwidth * 256);
            }

            Cell cell_Total = getCell(theRow, col++);
            cell_Total.setCellValue(data.tradosTotalWordCount);
            cell_Total.setCellStyle(temp_normalStyle);
            theSheet.setColumnWidth(col - 1, numwidth * 256);

            int moneywidth = 12;
            Cell cell_100Cost = getCell(theRow, col++);
            cell_100Cost.setCellValue(asDouble(data.trados100WordCountCost));
            cell_100Cost.setCellStyle(temp_moneyStyle);
            theSheet.setColumnWidth(col - 1, moneywidth * 256);

            Cell cell_95_99 = getCell(theRow, col++);
            cell_95_99.setCellValue(asDouble(data.trados95to99WordCountCost));
            cell_95_99.setCellStyle(temp_moneyStyle);
            theSheet.setColumnWidth(col - 1, moneywidth * 256);

            Cell cell_85_94 = getCell(theRow, col++);
            cell_85_94.setCellValue(asDouble(data.trados85to94WordCountCost));
            cell_85_94.setCellStyle(temp_moneyStyle);
            theSheet.setColumnWidth(col - 1, moneywidth * 256);

            Cell cell_75_84 = getCell(theRow, col++);
            cell_75_84.setCellValue(asDouble(data.trados75to84WordCountCost));
            cell_75_84.setCellStyle(temp_moneyStyle);
            theSheet.setColumnWidth(col - 1, moneywidth * 256);

            Cell cell_NoMatch = getCell(theRow, col++);
            cell_NoMatch.setCellValue(asDouble(data.tradosNoMatchWordCountCost));
            cell_NoMatch.setCellStyle(temp_moneyStyle);
            theSheet.setColumnWidth(col - 1, moneywidth * 256);

            Cell cell_Reps = getCell(theRow, col++);
            cell_Reps.setCellValue(asDouble(data.tradosRepsWordCountCost));
            cell_Reps.setCellStyle(temp_moneyStyle);
            theSheet.setColumnWidth(col - 1, moneywidth * 256);

            if (p_data.headers[0] != null) {
                Cell cell_InContext = getCell(theRow, col++);
                cell_InContext.setCellValue(asDouble(data.tradosInContextWordCountCost));
                cell_InContext.setCellStyle(temp_moneyStyle);
                theSheet.setColumnWidth(col - 1, moneywidth * 256);
            }

            Cell cell_TotalCost = getCell(theRow, col++);
            cell_TotalCost.setCellValue(asDouble(data.tradosTotalWordCountCost));
            cell_TotalCost.setCellStyle(temp_moneyStyle);
            theSheet.setColumnWidth(col - 1, moneywidth * 256);

            p_row.inc();
        }
    }

    p_row.inc();
    p_row.inc();
    addTotalsForTradosMatches(p_workbook, p_data, p_row, bundle);
}

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

License:Apache License

/** Adds the totals and sub-total formulas */
private void addTotalsForTradosMatches(Workbook p_workbook, MyData p_data, IntHolder p_row,
        ResourceBundle bundle) throws Exception {
    Sheet theSheet = p_data.tradosSheet;
    int row = p_row.getValue() + 1; // skip a row

    String title = bundle.getString("lb_totals");

    Row theRow = getRow(theSheet, row);//from w  ww. j a va2  s. c o m
    Cell cell_A = getCell(theRow, 0);
    cell_A.setCellValue(title);
    cell_A.setCellStyle(getSubTotalStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5));
    setRegionStyle(theSheet, new CellRangeAddress(row, row, 0, 5), getSubTotalStyle(p_workbook));

    int lastRow = p_row.getValue() - 2;

    // add in word count totals
    int c = 6;
    if (p_data.headers[0] != null) {
        // word counts
        Cell cell_G = getCell(theRow, c++);
        cell_G.setCellFormula("SUM(G5:G" + lastRow + ")");
        cell_G.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_H = getCell(theRow, c++);
        cell_H.setCellFormula("SUM(H5:H" + lastRow + ")");
        cell_H.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_I = getCell(theRow, c++);
        cell_I.setCellFormula("SUM(I5:I" + lastRow + ")");
        cell_I.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_J = getCell(theRow, c++);
        cell_J.setCellFormula("SUM(J5:J" + lastRow + ")");
        cell_J.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_K = getCell(theRow, c++);
        cell_K.setCellFormula("SUM(K5:K" + lastRow + ")");
        cell_K.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_L = getCell(theRow, c++);
        cell_L.setCellFormula("SUM(L5:L" + lastRow + ")");
        cell_L.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_M = getCell(theRow, c++);
        cell_M.setCellFormula("SUM(M5:M" + lastRow + ")");
        cell_M.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_N = getCell(theRow, c++);
        cell_N.setCellFormula("SUM(N5:N" + lastRow + ")");
        cell_N.setCellStyle(getSubTotalStyle(p_workbook));
        // word count costs
        Cell cell_O = getCell(theRow, c++);
        cell_O.setCellFormula("SUM(O5:O" + lastRow + ")");
        cell_O.setCellStyle(getTotalMoneyStyle(p_workbook));

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

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

        Cell cell_R = getCell(theRow, c++);
        cell_R.setCellFormula("SUM(R5:R" + lastRow + ")");
        cell_R.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_S = getCell(theRow, c++);
        cell_S.setCellFormula("SUM(S5:S" + lastRow + ")");
        cell_S.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_T = getCell(theRow, c++);
        cell_T.setCellFormula("SUM(T5:T" + lastRow + ")");
        cell_T.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_U = getCell(theRow, c++);
        cell_U.setCellFormula("SUM(U5:U" + lastRow + ")");
        cell_U.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_V = getCell(theRow, c++);
        cell_V.setCellFormula("SUM(V5:V" + lastRow + ")");
        cell_V.setCellStyle(getTotalMoneyStyle(p_workbook));
    } else {
        // word counts
        Cell cell_G = getCell(theRow, c++);
        cell_G.setCellFormula("SUM(G5:G" + lastRow + ")");
        cell_G.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_H = getCell(theRow, c++);
        cell_H.setCellFormula("SUM(H5:H" + lastRow + ")");
        cell_H.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_I = getCell(theRow, c++);
        cell_I.setCellFormula("SUM(I5:I" + lastRow + ")");
        cell_I.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_J = getCell(theRow, c++);
        cell_J.setCellFormula("SUM(J5:J" + lastRow + ")");
        cell_J.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_K = getCell(theRow, c++);
        cell_K.setCellFormula("SUM(K5:K" + lastRow + ")");
        cell_K.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_L = getCell(theRow, c++);
        cell_L.setCellFormula("SUM(L5:L" + lastRow + ")");
        cell_L.setCellStyle(getSubTotalStyle(p_workbook));

        Cell cell_M = getCell(theRow, c++);
        cell_M.setCellFormula("SUM(M5:M" + lastRow + ")");
        cell_M.setCellStyle(getSubTotalStyle(p_workbook));
        // word count costs
        Cell cell_N = getCell(theRow, c++);
        cell_N.setCellFormula("SUM(N5:N" + lastRow + ")");
        cell_N.setCellStyle(getSubTotalStyle(p_workbook));

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

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

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

        Cell cell_R = getCell(theRow, c++);
        cell_R.setCellFormula("SUM(R5:R" + lastRow + ")");
        cell_R.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_S = getCell(theRow, c++);
        cell_S.setCellFormula("SUM(S5:S" + lastRow + ")");
        cell_S.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_T = getCell(theRow, c++);
        cell_T.setCellFormula("SUM(T5:T" + lastRow + ")");
        cell_T.setCellStyle(getTotalMoneyStyle(p_workbook));
    }
}

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

License:Apache License

private void writeParamsSheet(Workbook p_workbook, Sheet paramsSheet, MyData p_data,
        HttpServletRequest p_request) throws Exception {
    Row firRow = getRow(paramsSheet, 0);
    Row secRow = getRow(paramsSheet, 1);
    Row thirRow = getRow(paramsSheet, 2);
    Cell cell_A_Title = getCell(firRow, 0);
    cell_A_Title.setCellValue(bundle.getString("lb_report_criteria"));
    cell_A_Title.setCellStyle(getContentStyle(p_workbook));
    paramsSheet.setColumnWidth(0, 50 * 256);

    Cell cell_A_Header = getCell(secRow, 0);
    if (p_data.wantsAllProjects) {
        cell_A_Header.setCellValue(bundle.getString("lb_selected_projects") + " " + bundle.getString("all"));
        cell_A_Header.setCellStyle(getContentStyle(p_workbook));
    } else {//  www.  ja va 2  s  .c o m
        cell_A_Header.setCellValue(bundle.getString("lb_selected_projects"));
        cell_A_Header.setCellStyle(getContentStyle(p_workbook));
        Iterator<Long> iter = p_data.projectIdList.iterator();
        int r = 2;
        while (iter.hasNext()) {
            Long pid = (Long) iter.next();
            String projectName = "??";
            try {
                Project p = ServerProxy.getProjectHandler().getProjectById(pid.longValue());
                projectName = p.getName();
            } catch (Exception e) {
            }
            String v = projectName + " (" + bundle.getString("lb_report_id") + "=" + pid.toString() + ")";
            Row theRow = getRow(paramsSheet, r);
            Cell cell_A = getCell(theRow, 0);
            cell_A.setCellValue(v);
            cell_A.setCellStyle(getContentStyle(p_workbook));
            r++;
        }
    }

    // add the date criteria
    String paramCreateDateStartCount = p_request.getParameter(JobSearchConstants.CREATION_START);
    //        String paramCreateDateStartOpts = p_request
    //                .getParameter(JobSearchConstants.CREATION_START_OPTIONS);
    String paramCreateDateEndCount = p_request.getParameter(JobSearchConstants.CREATION_END);
    //        String paramCreateDateEndOpts = p_request
    //                .getParameter(JobSearchConstants.CREATION_END_OPTIONS);
    Cell cell_B_Header = getCell(secRow, 1);
    cell_B_Header.setCellValue(bundle.getString("lb_from") + ":");
    cell_B_Header.setCellStyle(getContentStyle(p_workbook));
    //        String fromMsg = paramCreateDateStartCount
    //                + " "
    //                + getDateCritieraConditionValue(
    //                        paramCreateDateStartOpts);
    //        String untilMsg = paramCreateDateEndCount
    //                + " "
    //                + getDateCritieraConditionValue(
    //                        paramCreateDateEndOpts);
    Cell cell_B = getCell(thirRow, 1);
    cell_B.setCellValue(paramCreateDateStartCount);
    cell_B.setCellStyle(getContentStyle(p_workbook));

    Cell cell_C_Header = getCell(secRow, 2);
    cell_C_Header.setCellValue(bundle.getString("lb_until") + ":");
    cell_C_Header.setCellStyle(getContentStyle(p_workbook));

    Cell cell_C = getCell(thirRow, 2);
    cell_C.setCellValue(paramCreateDateEndCount);
    cell_C.setCellStyle(getContentStyle(p_workbook));

    // add the target lang criteria
    Cell cell_D_Header = getCell(secRow, 3);
    if (p_data.wantsAllTargetLangs) {
        cell_D_Header.setCellValue(bundle.getString("lb_selected_langs") + " " + bundle.getString("all"));
        cell_D_Header.setCellStyle(getContentStyle(p_workbook));
    } else {
        cell_D_Header.setCellValue(bundle.getString("lb_selected_langs"));
        cell_D_Header.setCellStyle(getContentStyle(p_workbook));
        Iterator<String> iter = p_data.targetLangList.iterator();
        int r = 2;
        LocaleManagerLocal manager = new LocaleManagerLocal();
        while (iter.hasNext()) {
            String lang = iter.next();
            Row theRow = getRow(paramsSheet, r);
            Cell cell_D = getCell(theRow, 3);
            cell_D.setCellValue(manager.getLocaleById(Long.valueOf(lang)).toString());
            cell_D.setCellStyle(getContentStyle(p_workbook));
            r++;
        }
        paramsSheet.setColumnWidth(3, 15 * 256);
    }
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void insertDate(Sheet sheet, CellStyle style) {
    Row dateRow = sheet.createRow(6);//from   w  w  w. j  a  v a2 s.  c om

    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$7:$C$7"));

    Locale frenchLocale = new Locale("fr", "FR");
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd MMMM yyyy", frenchLocale);

    String dateString = dateFormat.format(new Date());
    Cell dateCell = dateRow.createCell(1);
    dateCell.setCellStyle(style);
    dateCell.setCellValue(dateString);
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void insertContacts(Sheet sheet, CellStyle pulsioNameStyle, CellStyle contactsStyle,
        Pulsiodetails pulsiodetails) {//from   www .j a v a 2 s . co m
    Row pulsioNameRow = sheet.createRow(9);
    Cell pulsioNameCell = pulsioNameRow.createCell(1);
    pulsioNameCell.setCellValue("PULSIO");
    pulsioNameCell.setCellStyle(pulsioNameStyle);

    Row firstContactsRow = sheet.createRow(10);
    Cell firstContactsCell = firstContactsRow.createCell(1);
    firstContactsCell.setCellValue("Contact: " + pulsiodetails.getContact1());
    firstContactsCell.setCellStyle(contactsStyle);

    Row secondContactsRow = sheet.createRow(11);
    Cell secondContactsCell = secondContactsRow.createCell(1);
    secondContactsCell.setCellValue(pulsiodetails.getContact2());
    secondContactsCell.setCellStyle(contactsStyle);

    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$11:$C$11"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$12:$C$12"));
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void insertTitle(Sheet sheet, CellStyle style) {
    Row titleRow = sheet.createRow(14);// w  w  w  .ja  va  2s.  c  o  m
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Bon de livraison");
    titleCell.setCellStyle(style);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$15:$J$15"));
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void insertTableHeaders(Sheet sheet, CellStyle headerRowLeftCellStyleStyle,
        CellStyle headerRowMiddleCellStyle, CellStyle headerRowRightCellStyle) {
    Row tableHeadersRow = sheet.createRow(18);
    tableHeadersRow.setHeightInPoints((short) 35);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$19:$E$19"));

    Cell titleCell = tableHeadersRow.createCell(1);
    titleCell.setCellValue("Titre");
    titleCell.setCellStyle(headerRowLeftCellStyleStyle);

    for (int i = 2; i <= 4; i++) {
        tableHeadersRow.createCell(i).setCellStyle(headerRowMiddleCellStyle);
    }//from w  w  w .ja  v a 2s. c o m

    Cell quantityCell = tableHeadersRow.createCell(5);
    quantityCell.setCellValue("Qunatite/carton");
    quantityCell.setCellStyle(headerRowMiddleCellStyle);

    Cell boxesCountCell = tableHeadersRow.createCell(6);
    boxesCountCell.setCellValue("Nbre cartons");
    boxesCountCell.setCellStyle(headerRowMiddleCellStyle);

    Cell totalQuantityCell = tableHeadersRow.createCell(7);
    totalQuantityCell.setCellValue("Quantite totale");
    totalQuantityCell.setCellStyle(headerRowMiddleCellStyle);

    Cell palettesCountCell = tableHeadersRow.createCell(8);
    palettesCountCell.setCellValue("Nbre Palettes");
    palettesCountCell.setCellStyle(headerRowRightCellStyle);

}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

/**
 *
 * @param sheet//from w w  w  .  j a  v a 2  s . c  o m
 * @param leftStyle
 * @param middleStyle
 * @param rightStyle
 * @param footerStyle
 * @return index of the last table row created
 */
private static int insertTableBody(Sheet sheet, CellStyle leftStyle, CellStyle middleStyle,
        CellStyle rightStyle, CellStyle footerStyle, List<BookBoxModel> bookBoxModels) {
    Integer index = 18;
    Integer currentBookTitleIndex = 1;
    Set<Integer> rowsToSum = new HashSet<Integer>();
    Set<Integer> totalsToSum = new HashSet<Integer>();
    String cellMergeString;
    String cellFormula;

    try {
        int currentBookNumber = bookBoxModels.get(0).getBookNumber();

        for (int i = 0; i < bookBoxModels.size(); i++) {
            index++;

            BookBoxModel currentModel = bookBoxModels.get(i);

            Row row = sheet.createRow(index);

            if (currentBookNumber != currentModel.getBookNumber()) {
                for (int j = 2; j <= 4; j++) {
                    row.createCell(j).setCellStyle(footerStyle);
                }

                addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum);
                index++;
                currentBookTitleIndex++;

                row = sheet.createRow(index);
            }

            for (int j = 2; j <= 4; j++) {
                row.createCell(j).setCellStyle(middleStyle);
            }

            //Book Title Row
            Cell titleCell = row.createCell(1);

            cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1);

            sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));
            titleCell.setCellStyle(leftStyle);
            String isbn = currentModel.getISBN() != null ? currentModel.getISBN() : "";
            String cellString = String.format("%s %s", currentModel.getTitle(), isbn);
            titleCell.setCellValue(cellString);

            Cell quantityCell = row.createCell(5);
            quantityCell.setCellStyle(middleStyle);
            quantityCell.setCellValue(Double.parseDouble(currentModel.getBooksCount().toString()));

            Cell boxCountCell = row.createCell(6);
            boxCountCell.setCellStyle(middleStyle);
            boxCountCell.setCellValue(Double.parseDouble(currentModel.getBoxesCount().toString()));

            Cell totalQuantityCell = row.createCell(7);
            totalQuantityCell.setCellStyle(rightStyle);
            cellFormula = String.format("F%s*G%s", index + 1, index + 1);
            totalQuantityCell.setCellFormula(cellFormula);

            Cell palettesCountCell = row.createCell(8);
            palettesCountCell.setCellStyle(rightStyle);

            // excel is not 0-based!
            rowsToSum.add(index + 1);

            currentBookNumber = currentModel.getBookNumber();
        }

        index++;
        Row row = sheet.createRow(index);

        for (int i = 2; i <= 4; i++) {
            row.createCell(i).setCellStyle(footerStyle);
        }

        addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum);

        //Total row
        index++;
        Row footerTotalRow = sheet.createRow(index);
        Cell footerTotalRowTitleCell = footerTotalRow.createCell(1);

        for (int i = 2; i <= 4; i++) {
            footerTotalRow.createCell(i).setCellStyle(footerStyle);
        }

        cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));

        footerTotalRowTitleCell.setCellStyle(footerStyle);
        footerTotalRowTitleCell.setCellValue("Total");

        Cell footerTotalRowQuantityCell = footerTotalRow.createCell(5);
        footerTotalRowQuantityCell.setCellStyle(footerStyle);

        Cell footerTotalRowBoxCountCell = footerTotalRow.createCell(6);
        footerTotalRowBoxCountCell.setCellStyle(footerStyle);

        Cell footerTotalRowTotalQuantityCell = footerTotalRow.createCell(7);
        footerTotalRowTotalQuantityCell.setCellStyle(footerStyle);

        Cell footerTotalRowPalettesCountCell = footerTotalRow.createCell(8);
        footerTotalRowPalettesCountCell.setCellStyle(footerStyle);

        //build cell formulas
        StringBuilder totalBoxesCountformulaBuilder = new StringBuilder();
        StringBuilder totalBooksCountformulaBuilder = new StringBuilder();
        StringBuilder totalPaletsCountformulaBuilder = new StringBuilder();
        // Example: SUM(H22;H25;H28;H31;H34)
        totalBoxesCountformulaBuilder.append("SUM(");
        totalBooksCountformulaBuilder.append("SUM(");
        totalPaletsCountformulaBuilder.append("SUM(");

        for (Integer integer : totalsToSum) {
            totalBoxesCountformulaBuilder.append("G").append(integer).append(",");
            totalBooksCountformulaBuilder.append("H").append(integer).append(",");
            totalPaletsCountformulaBuilder.append("I").append(integer).append(",");
        }

        totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1);
        totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1);
        totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1);

        totalBoxesCountformulaBuilder.append(")");
        totalBooksCountformulaBuilder.append(")");
        totalPaletsCountformulaBuilder.append(")");

        footerTotalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString());
        footerTotalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString());
        footerTotalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString());

    } catch (Exception e) {
        e.printStackTrace();
    }
    return index;
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void addTotalTitleRow(Row row, Integer index, Sheet sheet, CellStyle footerStyle,
        Integer currentBookTitleIndex, Set<Integer> rowsToSum, Set<Integer> totalsToSum) {

    //Total Book Title Row
    Cell totalRowTitleCell = row.createCell(1);

    String cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));

    totalRowTitleCell.setCellStyle(footerStyle);
    totalRowTitleCell.setCellValue("Total Title " + currentBookTitleIndex++);

    Cell totalRowQuantityCell = row.createCell(5);
    totalRowQuantityCell.setCellStyle(footerStyle);

    Cell totalRowBoxCountCell = row.createCell(6);
    totalRowBoxCountCell.setCellStyle(footerStyle);

    Cell totalRowTotalQuantityCell = row.createCell(7);
    totalRowTotalQuantityCell.setCellStyle(footerStyle);

    Cell totalRowPalettesCountCell = row.createCell(8);
    totalRowPalettesCountCell.setCellStyle(footerStyle);

    //build cell formulas
    StringBuilder totalBoxesCountformulaBuilder = new StringBuilder();
    StringBuilder totalBooksCountformulaBuilder = new StringBuilder();
    StringBuilder totalPaletsCountformulaBuilder = new StringBuilder();
    // Example: SUM(H22;H25;H28;H31;H34)
    totalBoxesCountformulaBuilder.append("SUM(");
    totalBooksCountformulaBuilder.append("SUM(");
    totalPaletsCountformulaBuilder.append("SUM(");

    for (Integer integer : rowsToSum) {
        totalBoxesCountformulaBuilder.append("G").append(integer).append(",");
        totalBooksCountformulaBuilder.append("H").append(integer).append(",");
        totalPaletsCountformulaBuilder.append("I").append(integer).append(",");
    }/* w  ww. ja  v a 2s . c o  m*/

    totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1);
    totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1);
    totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1);

    totalBoxesCountformulaBuilder.append(")");
    totalBooksCountformulaBuilder.append(")");
    totalPaletsCountformulaBuilder.append(")");

    totalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString());
    totalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString());
    totalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString());
    // excel is not 0-based                
    totalsToSum.add(index + 1);

    rowsToSum.clear();
}

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static void insertFooter(Sheet sheet, CellStyle footerStyle, int index, String packageNumber,
        String deliveryAddress, String client) {
    String mergeString;//from ww  w  . j  ava 2  s .  c o m

    index += 2;
    Row transportNumberRow = sheet.createRow(index);
    Cell transportNumberCell = transportNumberRow.createCell(1);

    transportNumberCell.setCellValue("Num Tpt: " + packageNumber);
    transportNumberCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 2;
    Row addressLabelRow = sheet.createRow(index);
    Cell addressLabelCell = addressLabelRow.createCell(1);
    addressLabelCell.setCellValue("A livrer chez: ");
    addressLabelCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 1;
    Row addressRow = sheet.createRow(index);
    Cell addressCell = addressRow.createCell(1);
    addressCell.setCellValue(deliveryAddress);
    addressCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 3;
    Row clientLabelRow = sheet.createRow(index);
    Cell clientLabelCell = clientLabelRow.createCell(1);
    clientLabelCell.setCellValue("Pour le compte des Editions: ");
    clientLabelCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 1;
    Row clientRow = sheet.createRow(index);
    Cell clientCell = clientRow.createCell(1);
    clientCell.setCellValue(client);
    clientCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 4;
    Row dateRow = sheet.createRow(index);
    Cell dateCell = dateRow.createCell(1);
    dateCell.setCellValue("Date: ................");
    dateCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 3;
    Row signatureRow = sheet.createRow(index);
    Cell signatureCell = signatureRow.createCell(1);
    signatureCell.setCellValue("Signature et tampon: ...................");
    signatureCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
}