Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

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

License:Apache License

/** Adds totals */
private void addTotalsForDellMatches(Workbook p_workbook, IntHolder p_row) throws Exception {
    Sheet theSheet = data.dellSheet;/*from   w ww  . j a  va  2 s .co m*/
    // Totals
    int totalsRow = p_row.getValue() + 1; // skip a row
    Row totalRow = getRow(theSheet, totalsRow);
    Cell cell_A = getCell(totalRow, 0);
    cell_A.setCellValue(bundle.getString("lb_totals"));
    cell_A.setCellStyle(getSubTotalStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(totalsRow, totalsRow, 0, 7));
    setRegionStyle(theSheet, new CellRangeAddress(totalsRow, totalsRow, 0, 7), getSubTotalStyle(p_workbook));

    int lastRow = p_row.getValue() - 2;
    int c = 8;
    // Word Count
    Cell cell_I = getCell(totalRow, c++);
    cell_I.setCellFormula("SUM(I5" + ":I" + lastRow + ")");
    cell_I.setCellStyle(getSubTotalStyle(p_workbook));
    // Invoice
    Cell cell_J = getCell(totalRow, c++);
    cell_J.setCellFormula("SUM(J5" + ":J" + lastRow + ")");
    cell_J.setCellStyle(getTotalMoneyStyle(p_workbook));
    // add an extra column for Dell Tracking Use
    Cell cell_K = getCell(totalRow, c++);
    cell_K.setCellValue("");
    cell_K.setCellStyle(getTotalMoneyStyle(p_workbook));
}

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

License:Apache License

/** Adds the totals and sub-total formulas */
private void addTotalsForTradosMatches(Workbook p_workbook, IntHolder p_row) throws Exception {
    Sheet theSheet = data.tradosSheet;//from  w  ww .  j  av a2 s  .c  o  m
    int row = p_row.getValue() + 1; // skip a row
    String title = bundle.getString("lb_totals");

    Row totalRow = getRow(theSheet, row);
    Cell cell_A = getCell(totalRow, 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;
    // word counts
    if (data.headers[0] != null) {
        Cell cell_G = getCell(totalRow, c++);
        cell_G.setCellFormula("SUM(G5:G" + lastRow + ")");
        cell_G.setCellStyle(getSubTotalStyle(p_workbook));

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

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

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

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

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

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

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

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

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

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

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

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

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

        Cell cell_V = getCell(totalRow, c++);
        cell_V.setCellFormula("SUM(V5:V" + lastRow + ")");
        cell_V.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_W = getCell(totalRow, c++);
        cell_W.setCellFormula("SUM(W5:W" + lastRow + ")");
        cell_W.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_X = getCell(totalRow, c++);
        cell_X.setCellFormula("SUM(X5:X" + lastRow + ")");
        cell_X.setCellStyle(getTotalMoneyStyle(p_workbook));
    } else {
        Cell cell_G = getCell(totalRow, c++);
        cell_G.setCellFormula("SUM(G5:G" + lastRow + ")");
        cell_G.setCellStyle(getSubTotalStyle(p_workbook));

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

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

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

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

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

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

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

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

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

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

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

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

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

        Cell cell_V = getCell(totalRow, c++);
        cell_V.setCellFormula("SUM(V5:V" + lastRow + ")");
        cell_V.setCellStyle(getTotalMoneyStyle(p_workbook));
    }

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

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

License:Apache License

/**
 * Adds the table header for the Dell Matches sheet
 * //from   w  w w .ja va  2  s . com
 */
private void addHeader(Workbook p_workbook) throws Exception {
    Sheet theSheet = data.generalSheet;
    int c = 0;
    Row headerRow = getRow(theSheet, 2);
    Cell cell_A = getCell(headerRow, c);
    cell_A.setCellValue(bundle.getString("lb_job_id"));
    cell_A.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 7 * 256);
    c++;
    Cell cell_B = getCell(headerRow, c);
    cell_B.setCellValue(bundle.getString("lb_job_name"));
    cell_B.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 40 * 256);
    c++;
    Cell cell_C = getCell(headerRow, c);
    cell_C.setCellValue(bundle.getString("lb_workflow"));
    cell_C.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 25 * 256);
    c++;
    Cell cell_D = getCell(headerRow, c);
    cell_D.setCellValue(bundle.getString("lb_language"));
    cell_D.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 12 * 256);
    c++;
    Cell cell_E = getCell(headerRow, c);
    cell_E.setCellValue(bundle.getString("lb_word_count"));
    cell_E.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 7 * 256);
    c++;
    Cell cell_F = getCell(headerRow, c);
    cell_F.setCellValue(bundle.getString("lb_current_activity"));
    cell_F.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 14 * 256);
    c++;
    Cell cell_G = getCell(headerRow, c);
    cell_G.setCellValue(bundle.getString("lb_translation_start_date"));
    cell_G.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 28 * 256);
    c++;
    Cell cell_H = getCell(headerRow, c);
    cell_H.setCellValue(bundle.getString("lb_translation_due_date"));
    cell_H.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 28 * 256);
    c++;
    Cell cell_I = getCell(headerRow, c);
    cell_I.setCellValue(bundle.getString("lb_translation_finish_date"));
    cell_I.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 28 * 256);
    c++;
    Cell cell_J = getCell(headerRow, c);
    cell_J.setCellValue(bundle.getString("lb_on_time"));
    cell_J.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 28 * 256);
    c++;
    Cell cell_K = getCell(headerRow, c);
    cell_K.setCellValue(bundle.getString("lb_leadtime"));
    cell_K.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 12 * 256);
    c++;
    Cell cell_L = getCell(headerRow, c);
    cell_L.setCellValue(bundle.getString("lb_actual_performance"));
    cell_L.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 14 * 256);
}

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

License:Apache License

/**
 * Adds the table header for the Dell Matches sheet
 * /*from   w ww  .j a v  a  2s.  co  m*/
 */
private void addHeaderForDellMatches(Workbook p_workbook, MyData p_data) throws Exception {
    Sheet theSheet = p_data.dellSheet;
    int c = 0;
    Row thirRow = getRow(theSheet, 2);
    Row fourRow = getRow(theSheet, 3);

    Cell cell_A = getCell(thirRow, c);
    cell_A.setCellValue(bundle.getString("lb_job_id"));
    cell_A.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_B = getCell(thirRow, c);
    cell_B.setCellValue(bundle.getString("lb_job"));
    cell_B.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_C = getCell(thirRow, c);
    cell_C.setCellValue(bundle.getString("lb_po_number_report"));
    cell_C.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 15 * 256);
    c++;
    Cell cell_D = getCell(thirRow, c);
    cell_D.setCellValue(bundle.getString("lb_description"));
    cell_D.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 15 * 256);
    c++;
    Cell cell_E = getCell(thirRow, c);
    cell_E.setCellValue(bundle.getString("lb_creation_date"));
    cell_E.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_F = getCell(thirRow, c);
    cell_F.setCellValue(bundle.getString("lb_lang"));
    cell_F.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_G_Header = getCell(thirRow, c);
    cell_G_Header.setCellValue(bundle.getString("lb_word_counts"));
    cell_G_Header.setCellStyle(getHeaderStyle(p_workbook));

    if (p_data.headers[0] != null) {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 5));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 5), getHeaderStyle(p_workbook));
    } else {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 4));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 4), getHeaderStyle(p_workbook));
    }

    Cell cell_G = getCell(fourRow, c++);
    cell_G.setCellValue(bundle.getString("jobinfo.tmmatches.wordcounts.internalreps"));
    cell_G.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_H = getCell(fourRow, c++);
    cell_H.setCellValue(bundle.getString("jobinfo.tmmatches.wordcounts.exactmatches"));
    cell_H.setCellStyle(getHeaderStyle(p_workbook));
    if (p_data.headers[0] != null) {
        Cell cell_InContext = getCell(fourRow, c++);
        cell_InContext.setCellValue(bundle.getString("jobinfo.tmmatches.wordcounts.incontextmatches"));
        cell_InContext.setCellStyle(getHeaderStyle(p_workbook));
    }
    Cell cell_FuzzyMatches = getCell(fourRow, c++);
    cell_FuzzyMatches.setCellValue(bundle.getString("jobinfo.tmmatches.wordcounts.fuzzymatches"));
    cell_FuzzyMatches.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_Newwords = getCell(fourRow, c++);
    cell_Newwords.setCellValue(bundle.getString("jobinfo.tmmatches.wordcounts.newwords"));
    cell_Newwords.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_Total = getCell(fourRow, c++);
    cell_Total.setCellValue(bundle.getString("lb_total"));
    cell_Total.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_Invoice = getCell(thirRow, c);
    cell_Invoice.setCellValue(bundle.getString("jobinfo.tmmatches.invoice"));
    cell_Invoice.setCellStyle(getHeaderStyle(p_workbook));

    if (p_data.headers[0] != null) {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 5));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 5), getHeaderStyle(p_workbook));
    } else {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 4));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 4), getHeaderStyle(p_workbook));
    }

    Cell cell_InternalReps = getCell(fourRow, c++);
    cell_InternalReps.setCellValue(bundle.getString("jobinfo.tmmatches.invoice.internalreps"));
    cell_InternalReps.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_ExactMatches = getCell(fourRow, c++);
    cell_ExactMatches.setCellValue(bundle.getString("jobinfo.tmmatches.invoice.exactmatches"));
    cell_ExactMatches.setCellStyle(getHeaderStyle(p_workbook));
    if (p_data.headers[0] != null) {
        Cell cell_InContext = getCell(fourRow, c++);
        cell_InContext.setCellValue(bundle.getString("jobinfo.tmmatches.invoice.incontextmatches"));
        cell_InContext.setCellStyle(getHeaderStyle(p_workbook));
    }

    Cell cell_FuzzyMatches_Invoice = getCell(fourRow, c++);
    cell_FuzzyMatches_Invoice.setCellValue(bundle.getString("jobinfo.tmmatches.invoice.fuzzymatches"));
    cell_FuzzyMatches_Invoice.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_NewWords_Invoice = getCell(fourRow, c++);
    cell_NewWords_Invoice.setCellValue(bundle.getString("jobinfo.tmmatches.invoice.newwords"));
    cell_NewWords_Invoice.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_Total_Invoice = getCell(fourRow, c++);
    cell_Total_Invoice.setCellValue(bundle.getString("lb_total"));
    cell_Total_Invoice.setCellStyle(getHeaderStyle(p_workbook));

    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getContentStyle(p_workbook));
}

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

License:Apache License

/**
 * Adds the table header for the Trados Matches sheet
 * /*from  w  w  w . ja v  a  2s .co m*/
 */
private void addHeaderForTradosMatches(Workbook p_workbook, MyData p_data) throws Exception {
    Sheet theSheet = p_data.tradosSheet;
    int c = 0;
    Row secRow = getRow(theSheet, 1);
    Row thirRow = getRow(theSheet, 2);
    Row fourRow = getRow(theSheet, 3);

    Cell cell_Ldfl = getCell(secRow, c);
    cell_Ldfl.setCellValue(bundle.getString("lb_desp_file_list"));
    cell_Ldfl.setCellStyle(getContentStyle(p_workbook));

    Cell cell_A = getCell(thirRow, c);
    cell_A.setCellValue(bundle.getString("lb_job_id"));
    cell_A.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_B = getCell(thirRow, c);
    cell_B.setCellValue(bundle.getString("lb_job"));
    cell_B.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_C = getCell(thirRow, c);
    cell_C.setCellValue(bundle.getString("lb_po_number_report"));
    cell_C.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_D = getCell(thirRow, c);
    cell_D.setCellValue(bundle.getString("reportDesc"));
    cell_D.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    theSheet.setColumnWidth(c, 15 * 256);
    c++;
    Cell cell_E = getCell(thirRow, c);
    cell_E.setCellValue(bundle.getString("lb_creation_date"));
    cell_E.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_F = getCell(thirRow, c);
    cell_F.setCellValue(bundle.getString("lb_lang"));
    cell_F.setCellStyle(getHeaderStyle(p_workbook));
    theSheet.addMergedRegion(new CellRangeAddress(2, 3, c, c));
    setRegionStyle(theSheet, new CellRangeAddress(2, 3, c, c), getHeaderStyle(p_workbook));
    c++;
    Cell cell_G_Header = getCell(thirRow, c);
    cell_G_Header.setCellValue(bundle.getString("jobinfo.tmmatches.wordcounts"));
    cell_G_Header.setCellStyle(getHeaderStyle(p_workbook));

    if (p_data.headers[0] != null) {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 7));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 7), getHeaderStyle(p_workbook));
    } else {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 6));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 6), getHeaderStyle(p_workbook));
    }

    Cell cell_G = getCell(fourRow, c++);
    cell_G.setCellValue(bundle.getString("jobinfo.tradosmatches.invoice.per100matches"));
    cell_G.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_H = getCell(fourRow, c++);
    cell_H.setCellValue(bundle.getString("lb_95_99"));
    cell_H.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_I = getCell(fourRow, c++);
    cell_I.setCellValue(bundle.getString("lb_85_94"));
    cell_I.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_J = getCell(fourRow, c++);
    cell_J.setCellValue(bundle.getString("lb_75_84") + "*");
    cell_J.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_K = getCell(fourRow, c++);
    cell_K.setCellValue(bundle.getString("lb_no_match"));
    cell_K.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_L = getCell(fourRow, c++);
    cell_L.setCellValue(bundle.getString("lb_repetition_word_cnt"));
    cell_L.setCellStyle(getHeaderStyle(p_workbook));
    if (p_data.headers[0] != null) {
        Cell cell_InContext = getCell(fourRow, c++);
        cell_InContext.setCellValue(bundle.getString("lb_in_context_tm"));
        cell_InContext.setCellStyle(getHeaderStyle(p_workbook));
    }
    Cell cell_Total = getCell(fourRow, c++);
    cell_Total.setCellValue(bundle.getString("lb_total"));
    cell_Total.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_Invoice = getCell(thirRow, c);
    cell_Invoice.setCellValue(bundle.getString("jobinfo.tmmatches.invoice"));
    cell_Invoice.setCellStyle(getHeaderStyle(p_workbook));
    if (p_data.headers[0] != null) {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 7));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 7), getHeaderStyle(p_workbook));
    } else {
        theSheet.addMergedRegion(new CellRangeAddress(2, 2, c, c + 6));
        setRegionStyle(theSheet, new CellRangeAddress(2, 2, c, c + 6), getHeaderStyle(p_workbook));
    }

    Cell cell_Per100Matches = getCell(fourRow, c++);
    cell_Per100Matches.setCellValue(bundle.getString("jobinfo.tradosmatches.invoice.per100matches"));
    cell_Per100Matches.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_95_99 = getCell(fourRow, c++);
    cell_95_99.setCellValue(bundle.getString("lb_95_99"));
    cell_95_99.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_85_94 = getCell(fourRow, c++);
    cell_85_94.setCellValue(bundle.getString("lb_85_94"));
    cell_85_94.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_75_84 = getCell(fourRow, c++);
    cell_75_84.setCellValue(bundle.getString("lb_75_84") + "*");
    cell_75_84.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_NoMatch = getCell(fourRow, c++);
    cell_NoMatch.setCellValue(bundle.getString("lb_no_match"));
    cell_NoMatch.setCellStyle(getHeaderStyle(p_workbook));

    Cell cell_Repetition = getCell(fourRow, c++);
    cell_Repetition.setCellValue(bundle.getString("lb_repetition_word_cnt"));
    cell_Repetition.setCellStyle(getHeaderStyle(p_workbook));
    if (p_data.headers[0] != null) {
        Cell cell_InContext = getCell(fourRow, c++);
        cell_InContext.setCellValue(bundle.getString("lb_in_context_match"));
        cell_InContext.setCellStyle(getHeaderStyle(p_workbook));
    }
    Cell cell_Total_Invoice = getCell(fourRow, c++);
    cell_Total_Invoice.setCellValue(bundle.getString("lb_total"));
    cell_Total_Invoice.setCellStyle(getHeaderStyle(p_workbook));
}

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

License:Apache License

/** Adds the totals and sub-total formulas */
private void addTotalsForDellMatches(Workbook p_workbook, MyData p_data, IntHolder p_row, ResourceBundle bundle)
        throws Exception {
    Sheet theSheet = p_data.dellSheet;//from   w  w  w  .j a  v  a 2s. c  om
    int row = p_row.getValue() + 1; // skip a row

    String title = bundle.getString("lb_totals");
    Row theRow = getRow(theSheet, row);
    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));
        // word count costs
        Cell cell_M = getCell(theRow, c++);
        cell_M.setCellFormula("SUM(M5:M" + lastRow + ")");
        cell_M.setCellStyle(getTotalMoneyStyle(p_workbook));

        Cell cell_N = getCell(theRow, c++);
        cell_N.setCellFormula("SUM(N5:N" + lastRow + ")");
        cell_N.setCellStyle(getTotalMoneyStyle(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));
    } 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));
        // word count costs
        Cell cell_L = getCell(theRow, c++);
        cell_L.setCellFormula("SUM(L5:L" + lastRow + ")");
        cell_L.setCellStyle(getTotalMoneyStyle(p_workbook));

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

        Cell cell_N = getCell(theRow, c++);
        cell_N.setCellFormula("SUM(N5:N" + lastRow + ")");
        cell_N.setCellStyle(getTotalMoneyStyle(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));
    }
}

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 av  a2  s .  c om*/
    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.haulmont.mp2xls.writer.LocalizationBatchExcelWriter.java

License:Apache License

public static void exportToXls(LocalizationsBatch localizations, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet("localizations");

    HSSFCellStyle systemStyle = workbook.createCellStyle();
    systemStyle.setFillForegroundColor(HSSFColor.RED.index);
    systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFRow row = worksheet.createRow(0);
    row.createCell(0).setCellValue("Project path");
    row.createCell(1).setCellValue(localizations.getProjectDirectory());

    HSSFRow headLine = worksheet.createRow(5);
    headLine.createCell(0).setCellValue("Path to File");
    headLine.createCell(1).setCellValue("Parameter Name");

    Map<String, Integer> localeColumn = new HashMap<>();
    int colCount = 1;
    for (String localeId : localizations.getLocalizationIds()) {
        if (localizations.getScanLocalizationIds().contains(localeId == null ? "en" : localeId)) {
            String id = localeId == null ? "default" : localeId;
            headLine.createCell(++colCount).setCellValue(id);
            localeColumn.put(localeId, colCount);
        }/*  w  w  w  . j a  va  2  s .  c om*/
    }

    Integer currentRow = headLine.getRowNum();
    for (String folder : localizations.getMessagesLocalizations().keySet()) {
        Set<MessagesLocalization> locales = localizations.getMessagesLocalizations().get(folder);

        Set<String> parameters = new HashSet<>();
        for (MessagesLocalization locale : locales) {
            if (localizations.getScanLocalizationIds()
                    .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId()))
                parameters.addAll(locale.getMessages().keySet());
        }

        for (String parameter : parameters) {
            row = worksheet.createRow(++currentRow);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(folder);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
                row.setZeroHeight(true);
            }

            cell = row.createCell(1);
            cell.setCellValue(parameter);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
            }

            for (MessagesLocalization locale : locales) {
                if (localizations.getScanLocalizationIds()
                        .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId())) {
                    Integer columnNum = localeColumn.get(locale.getLocaleId());
                    cell = row.createCell(columnNum);
                    cell.setCellValue(locale.getMessages().get(parameter));
                }
            }

        }
    }

    worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, colCount));
    worksheet.createFreezePane(0, headLine.getRowNum() + 1);
    /*
            for (int i = 0; i < colCount; i++){
    worksheet.autoSizeColumn(i);
    worksheet.setColumnWidth(i, worksheet.getColumnWidth(i) + 100);
            }
    */
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java

License:Apache License

public static void exportToXls(List<LocalizationLog> differences, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {// w  w w  .  j  ava 2s  .  c om
        HSSFSheet worksheet = workbook.createSheet("localizations");

        HSSFCellStyle systemStyle = workbook.createCellStyle();
        systemStyle.setFillForegroundColor(HSSFColor.RED.index);
        systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        Integer currentRow = 0;
        HSSFRow headLine = worksheet.createRow(++currentRow);
        headLine.createCell(0).setCellValue("File");
        headLine.createCell(1).setCellValue("Property");
        headLine.createCell(2).setCellValue("Source Value");
        headLine.createCell(3).setCellValue("Excel Value");

        HSSFRow row;

        for (LocalizationLog.Type type : LocalizationLog.Type.values()) {
            List<LocalizationLog> logs = getLogsByType(differences, type);
            if (logs.size() > 0) {
                row = worksheet.createRow(++currentRow);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(LogHelper.getMessageByType(type));
                cell.setCellStyle(LogHelper.getStyleByType(workbook, type));
                for (int i = 1; i < 4; i++) {
                    row.createCell(i);
                }

                CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);

                for (LocalizationLog log : logs) {
                    createNewLogRow(worksheet, ++currentRow, log);
                }

                row = worksheet.createRow(++currentRow);
                for (int i = 0; i < 4; i++) {
                    row.createCell(i);
                }

                region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);
            }
        }

        worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, 3));
        worksheet.createFreezePane(0, headLine.getRowNum() + 1);

        for (int i = 0; i < worksheet.getLastRowNum(); i++) {
            worksheet.autoSizeColumn(i);
        }
    } finally {
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

private void initialize(String title, List<String> headerList) {
    this.wb = new SXSSFWorkbook(500);
    this.sheet = wb.createSheet("Export");
    this.styles = createStyles(wb);
    // Create title
    if (StringUtils.isNotBlank(title)) {
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(30);/*from  w  w w  .j  ava2s. c  o m*/
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(styles.get("title"));
        titleCell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(),
                titleRow.getRowNum(), headerList.size() - 1));
    }
    // Create header
    if (headerList == null) {
        throw new RuntimeException("headerList not null!");
    }
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(styles.get("header"));
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch()
                    .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
    log.debug("Initialize success.");
}