Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

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

private static void insertContacts(Sheet sheet, CellStyle pulsioNameStyle, CellStyle contactsStyle,
        Pulsiodetails pulsiodetails) {/*from w  w w . j  a  va  2s .  c  o  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);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Bon de livraison");
    titleCell.setCellStyle(style);// ww w  .j  ava2  s.com
    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   ww w  .j  av a 2  s  .  c  om*/

    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 www  . jav  a2  s  . com
 * @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(",");
    }/*from  w w w  .  jav  a 2s .  co  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;/* w w  w  .  j a v a  2s . 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));
}

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

private static void generateHeaders(Sheet sheet, Map<String, CellStyle> styles) {
    Row row0 = sheet.getRow(0);
    Row row11 = sheet.getRow(10);//from   w w  w .  jav a  2 s  . c  o m

    Cell a1 = row0.createCell(0);
    a1.setCellStyle(styles.get(STYLE_SMALL_FONT));
    Cell b1 = row0.createCell(1);
    b1.setCellStyle(styles.get(STYLE_SMALL_FONT));
    b1.setCellValue(PULSIO_START);

    Cell d1 = row0.createCell(3);
    d1.setCellStyle(styles.get(STYLE_SMALL_FONT));
    Cell e1 = row0.createCell(4);
    e1.setCellStyle(styles.get(STYLE_SMALL_FONT));
    e1.setCellValue(PULSIO_START);

    Cell a11 = row11.createCell(0);
    a11.setCellStyle(styles.get(STYLE_SMALL_FONT));
    Cell b11 = row11.createCell(1);
    b11.setCellStyle(styles.get(STYLE_SMALL_FONT));
    b11.setCellValue(PULSIO_START);

    Cell d11 = row11.createCell(3);
    d11.setCellStyle(styles.get(STYLE_SMALL_FONT));
    Cell e11 = row11.createCell(4);
    e11.setCellStyle(styles.get(STYLE_SMALL_FONT));
    e11.setCellValue(PULSIO_START);
}

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

private static void generateAddress(Sheet sheet, Map<String, CellStyle> styles, BookLabelModel bookLabelModel) {
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A2:A3"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A12:A13"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D2:D3"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D12:D13"));

    Row row2 = sheet.getRow(1);
    Row row3 = sheet.getRow(2);// www.  j  av  a2s.  c o  m

    Cell a2 = row2.createCell(0);
    a2.setCellValue(ADDRESS);
    a2.setCellStyle(styles.get(STYLE_LABEL));

    Cell a3 = row3.createCell(0);
    a3.setCellStyle(styles.get(STYLE_LABEL));

    Cell d2 = row2.createCell(3);
    d2.setCellValue(ADDRESS);
    d2.setCellStyle(styles.get(STYLE_LABEL));

    Cell d3 = row3.createCell(3);
    d3.setCellStyle(styles.get(STYLE_LABEL));

    Cell b2 = row2.createCell(1);
    b2.setCellValue(bookLabelModel.getAddress());
    b2.setCellStyle(styles.get(STYLE_CONTENT));

    Cell b3 = row3.createCell(1);
    b3.setCellValue(bookLabelModel.getPostalCode());
    b3.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e2 = row2.createCell(4);
    e2.setCellValue(bookLabelModel.getAddress());
    e2.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e3 = row3.createCell(4);
    e3.setCellValue(bookLabelModel.getPostalCode());
    e3.setCellStyle(styles.get(STYLE_CONTENT));

    Row row12 = sheet.getRow(11);

    Row row13 = sheet.getRow(12);

    Cell a12 = row12.createCell(0);
    a12.setCellValue(ADDRESS);
    a12.setCellStyle(styles.get(STYLE_LABEL));

    Cell a13 = row13.createCell(0);
    a13.setCellStyle(styles.get(STYLE_LABEL));

    Cell d12 = row12.createCell(3);
    d12.setCellValue(ADDRESS);
    d12.setCellStyle(styles.get(STYLE_LABEL));

    Cell d13 = row13.createCell(3);
    d13.setCellStyle(styles.get(STYLE_LABEL));

    Cell b12 = row12.createCell(1);
    b12.setCellValue(bookLabelModel.getAddress());
    b12.setCellStyle(styles.get(STYLE_CONTENT));

    Cell b13 = row13.createCell(1);
    b13.setCellValue(bookLabelModel.getPostalCode());
    b13.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e12 = row12.createCell(4);
    e12.setCellValue(bookLabelModel.getAddress());
    e12.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e13 = row13.createCell(4);
    e13.setCellValue(bookLabelModel.getPostalCode());
    e13.setCellStyle(styles.get(STYLE_CONTENT));
}

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

private static void generateClient(Sheet sheet, Map<String, CellStyle> styles, BookLabelModel bookLabelModel) {
    Row row4 = sheet.getRow(3);

    Cell a4 = row4.createCell(0);
    a4.setCellStyle(styles.get(STYLE_LABEL));
    a4.setCellValue(CLIENT);/*  w  ww . j  a v a 2 s  .  co  m*/

    Cell b4 = row4.createCell(1);
    b4.setCellStyle(styles.get(STYLE_CONTENT));
    b4.setCellValue(bookLabelModel.getClient());

    Cell d4 = row4.createCell(3);
    d4.setCellStyle(styles.get(STYLE_LABEL));
    d4.setCellValue(CLIENT);

    Cell e4 = row4.createCell(4);
    e4.setCellStyle(styles.get(STYLE_CONTENT));
    e4.setCellValue(bookLabelModel.getClient());

    Row row14 = sheet.getRow(13);

    Cell a14 = row14.createCell(0);
    a14.setCellStyle(styles.get(STYLE_LABEL));
    a14.setCellValue(CLIENT);

    Cell b14 = row14.createCell(1);
    b14.setCellStyle(styles.get(STYLE_CONTENT));
    b14.setCellValue(bookLabelModel.getClient());

    Cell d14 = row14.createCell(3);
    d14.setCellStyle(styles.get(STYLE_LABEL));
    d14.setCellValue(CLIENT);

    Cell e14 = row14.createCell(4);
    e14.setCellStyle(styles.get(STYLE_CONTENT));
    e14.setCellValue(bookLabelModel.getClient());
}

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

private static void generateTransportation(Sheet sheet, Map<String, CellStyle> styles,
        BookLabelModel bookLabelModel) {
    Row row5 = sheet.getRow(4);

    Cell a5 = row5.createCell(0);
    a5.setCellValue(TRANSPORT_NUMBER);/*w w w .j  a v a  2  s.c  om*/
    a5.setCellStyle(styles.get(STYLE_LABEL));

    Cell b5 = row5.createCell(1);
    b5.setCellValue(bookLabelModel.getPackageNumber());
    b5.setCellStyle(styles.get(STYLE_CONTENT));

    Cell d5 = row5.createCell(3);
    d5.setCellValue(TRANSPORT_NUMBER);
    d5.setCellStyle(styles.get(STYLE_LABEL));

    Cell e5 = row5.createCell(4);
    e5.setCellValue(bookLabelModel.getPackageNumber());
    e5.setCellStyle(styles.get(STYLE_CONTENT));

    Row row15 = sheet.getRow(14);

    Cell a15 = row15.createCell(0);
    a15.setCellValue(TRANSPORT_NUMBER);
    a15.setCellStyle(styles.get(STYLE_LABEL));

    Cell b15 = row15.createCell(1);
    b15.setCellValue(bookLabelModel.getPackageNumber());
    b15.setCellStyle(styles.get(STYLE_CONTENT));

    Cell d15 = row15.createCell(3);
    d15.setCellValue(TRANSPORT_NUMBER);
    d15.setCellStyle(styles.get(STYLE_LABEL));

    Cell e15 = row15.createCell(4);
    e15.setCellValue(bookLabelModel.getPackageNumber());
    e15.setCellStyle(styles.get(STYLE_CONTENT));
}