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

private static void generateTitle(Sheet sheet, Map<String, CellStyle> styles, BookLabelModel bookLabelModel) {
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A6:A7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D6:D7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A16:A17"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D16:D17"));

    String isbn = bookLabelModel.getISBN() != null ? bookLabelModel.getISBN() : "";
    String titleISBNString = String.format("%s %s", bookLabelModel.getTitle(), isbn);
    Row row6 = sheet.getRow(5);
    Row row7 = sheet.getRow(6);/*from ww w  .j a va  2  s.c om*/

    Cell a6 = row6.createCell(0);
    a6.setCellStyle(styles.get(STYLE_LABEL));
    a6.setCellValue(TITLE);

    Cell a7 = row7.createCell(0);
    a7.setCellStyle(styles.get(STYLE_LABEL));

    Cell b6 = row6.createCell(1);
    b6.setCellStyle(styles.get(STYLE_CONTENT));
    b6.setCellValue(titleISBNString);

    Cell b7 = row7.createCell(1);
    b7.setCellStyle(styles.get(STYLE_CONTENT));
    b7.setCellValue(bookLabelModel.getBookNumber());

    Cell d6 = row6.createCell(3);
    d6.setCellStyle(styles.get(STYLE_LABEL));
    d6.setCellValue(TITLE);

    Cell d7 = row7.createCell(3);
    d7.setCellStyle(styles.get(STYLE_LABEL));

    Cell e6 = row6.createCell(4);
    e6.setCellStyle(styles.get(STYLE_CONTENT));
    e6.setCellValue(titleISBNString);

    Cell e7 = row7.createCell(4);
    e7.setCellStyle(styles.get(STYLE_CONTENT));
    e7.setCellValue(bookLabelModel.getBookNumber());

    Row row16 = sheet.getRow(15);
    Row row17 = sheet.getRow(16);

    Cell a16 = row16.createCell(0);
    a16.setCellStyle(styles.get(STYLE_LABEL));
    a16.setCellValue(TITLE);

    Cell a17 = row17.createCell(0);
    a17.setCellStyle(styles.get(STYLE_LABEL));

    Cell b16 = row16.createCell(1);
    b16.setCellStyle(styles.get(STYLE_CONTENT));
    b16.setCellValue(titleISBNString);

    Cell b17 = row17.createCell(1);
    b17.setCellStyle(styles.get(STYLE_CONTENT));
    b17.setCellValue(bookLabelModel.getBookNumber());

    Cell d16 = row16.createCell(3);
    d16.setCellStyle(styles.get(STYLE_LABEL));
    d16.setCellValue(TITLE);

    Cell d17 = row17.createCell(3);
    d17.setCellStyle(styles.get(STYLE_LABEL));

    Cell e16 = row16.createCell(4);
    e16.setCellStyle(styles.get(STYLE_CONTENT));
    e16.setCellValue(titleISBNString);

    Cell e17 = row17.createCell(4);
    e17.setCellStyle(styles.get(STYLE_CONTENT));
    e17.setCellValue(bookLabelModel.getBookNumber());
}

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

private static void generateCountPerBox(Sheet sheet, Map<String, CellStyle> styles,
        BookLabelModel bookLabelModel) {
    Row row8 = sheet.getRow(7);

    Cell a8 = row8.createCell(0);
    a8.setCellStyle(styles.get(STYLE_LABEL));
    a8.setCellValue(COUNT_PER_BOX);/*w w  w .  j  a  v a2 s.c  o  m*/

    Cell b8 = row8.createCell(1);
    b8.setCellStyle(styles.get(STYLE_CONTENT));
    b8.setCellValue(EX);

    Cell d8 = row8.createCell(3);
    d8.setCellStyle(styles.get(STYLE_LABEL));
    d8.setCellValue(COUNT_PER_BOX);

    Cell e8 = row8.createCell(4);
    e8.setCellStyle(styles.get(STYLE_CONTENT));
    e8.setCellValue(EX);

    Row row18 = sheet.getRow(17);

    Cell a18 = row18.createCell(0);
    a18.setCellStyle(styles.get(STYLE_LABEL));
    a18.setCellValue(COUNT_PER_BOX);

    Cell b18 = row18.createCell(1);
    b18.setCellStyle(styles.get(STYLE_CONTENT));
    b18.setCellValue(EX);

    Cell d18 = row18.createCell(3);
    d18.setCellStyle(styles.get(STYLE_LABEL));
    d18.setCellValue(COUNT_PER_BOX);

    Cell e18 = row18.createCell(4);
    e18.setCellStyle(styles.get(STYLE_CONTENT));
    e18.setCellValue(EX);
}

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

private static void generateCountPerAddress(Sheet sheet, Map<String, CellStyle> styles,
        BookLabelModel bookLabelModel) {
    Row row9 = sheet.getRow(8);

    Cell a9 = row9.createCell(0);
    a9.setCellStyle(styles.get(STYLE_LABEL));
    a9.setCellValue(COUNT_PER_ADDRESS);//from w w  w  . j a v  a 2s  .com

    Cell b9 = row9.createCell(1);
    b9.setCellStyle(styles.get(STYLE_CONTENT));
    b9.setCellValue(bookLabelModel.getCount().toString() + " " + EX);

    Cell d9 = row9.createCell(3);
    d9.setCellStyle(styles.get(STYLE_LABEL));
    d9.setCellValue(COUNT_PER_ADDRESS);

    Cell e9 = row9.createCell(4);
    e9.setCellStyle(styles.get(STYLE_CONTENT));
    e9.setCellValue(bookLabelModel.getCount().toString() + " " + EX);

    Row row19 = sheet.getRow(18);

    Cell a19 = row19.createCell(0);
    a19.setCellStyle(styles.get(STYLE_LABEL));
    a19.setCellValue(COUNT_PER_ADDRESS);

    Cell b19 = row19.createCell(1);
    b19.setCellStyle(styles.get(STYLE_CONTENT));
    b19.setCellValue(bookLabelModel.getCount().toString() + " " + EX);

    Cell d19 = row19.createCell(3);
    d19.setCellStyle(styles.get(STYLE_LABEL));
    d19.setCellValue(COUNT_PER_ADDRESS);

    Cell e19 = row19.createCell(4);
    e19.setCellStyle(styles.get(STYLE_CONTENT));
    e19.setCellValue(bookLabelModel.getCount().toString() + " " + EX);
}

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

private static void generateHeaderRow(Row headerRow, Map<String, CellStyle> styles, String packageNumber) {
    Cell headingCell = headerRow.createCell(1);
    headingCell.setCellValue(HEADING_INTERNATIONAL_BILL);
    headingCell.setCellStyle(styles.get(DEFAULT_STYLE));

    Cell CMRCell = headerRow.createCell(2);
    CMRCell.setCellValue(HEADING_CMR);/*w  w w.j  a  v  a2s. c o m*/
    CMRCell.setCellStyle(styles.get(DEFAULT_STYLE));

    Cell internationalHeadingCell = headerRow.createCell(3);
    internationalHeadingCell.setCellValue(HEADING_CONSIGNMENT);
    internationalHeadingCell.setCellStyle(styles.get(DEFAULT_STYLE));

    Cell countryHeadingCell = headerRow.createCell(7);
    countryHeadingCell.setCellValue(HEADING_COUNTRY);
    countryHeadingCell.setCellStyle(styles.get(DEFAULT_STYLE));

    Cell numberHeadingCell = headerRow.createCell(9);
    numberHeadingCell.setCellValue(HEADING_NUMBER + packageNumber);
    numberHeadingCell.setCellStyle(styles.get(DEFAULT_STYLE));
}

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

private static int generateSenderAndDriver(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow) {
    // new value is 5
    currentRow = currentRow + 3;/*  www  .j  a  v a2 s.co  m*/

    Row labelRow = sheet.createRow(currentRow);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$6:$C6"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$7:$C7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$8:$C8"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$9:$C9"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$10:$C10"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D$6:$I$10"));

    Cell senderLabelCell = labelRow.createCell(1);
    senderLabelCell.setCellValue(LABEL_SENDER);
    senderLabelCell.setCellStyle(styles.get(LABEL_TOP_STYLE));
    labelRow.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE));

    currentRow++;
    Row addressRow1 = sheet.createRow(currentRow);
    Cell addressCell1 = addressRow1.createCell(1);
    addressCell1.setCellValue(ADDRESS_PART_1);
    addressCell1.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row addressRow2 = sheet.createRow(currentRow);
    Cell addressCell2 = addressRow2.createCell(1);
    addressCell2.setCellValue(ADDRESS_PART_2);
    addressCell2.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row addressRow3 = sheet.createRow(currentRow);
    Cell addressCell3 = addressRow3.createCell(1);
    addressCell3.setCellValue(ADDRESS_PART_3);
    addressCell3.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row addressRow4 = sheet.createRow(currentRow);
    Cell addressCell4 = addressRow4.createCell(1);
    addressCell4.setCellValue(ADDRESS_PART_4);
    addressCell4.setCellStyle(styles.get(CONTENT_BOTTOM_STYLE));
    addressRow4.createCell(2).setCellStyle(styles.get(CONTENT_BOTTOM_STYLE));

    //set styles for driver cells
    for (int i = 5; i <= 9; i++) {
        // the rows are already initialized above
        Row row = sheet.getRow(i);

        for (int j = 3; j <= 8; j++) {
            Cell cell = row.createCell(j);
            cell.setCellStyle(styles.get(LABEL_WHOLE_STYLE));
        }
    }

    Cell driverCell = labelRow.createCell(3);
    driverCell.setCellValue(LABEL_DRIVER);
    driverCell.setCellStyle(styles.get(LABEL_WHOLE_STYLE));

    return currentRow;
}

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

private static int generatePoint2Till9(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        String deliveryAddress) {
    for (int i = 11; i < 27; i++) {
        sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + i + ":$C$" + i));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + i + ":$I$" + i));
    }//from   w  w w .  ja v  a 2s  .  co  m

    currentRow++;
    Row row11 = sheet.createRow(currentRow);
    Cell receiverLabelCell = row11.createCell(1);
    receiverLabelCell.setCellValue(LABEL_RECEIVER);
    receiverLabelCell.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row11.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE));

    currentRow++;
    Row row12 = sheet.createRow(currentRow);
    row12.createCell(1).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row12.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row row13 = sheet.createRow(currentRow);
    Cell receiverCell1 = row13.createCell(1);
    receiverCell1.setCellValue(RECEIVER_PART_1);
    receiverCell1.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row13.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row row14 = sheet.createRow(currentRow);
    Cell receiverCell2 = row14.createCell(1);
    receiverCell2.setCellValue(RECEIVER_PART_2);
    receiverCell2.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row14.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row14.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row15 = sheet.createRow(currentRow);
    Cell receiverCell3 = row15.createCell(1);
    receiverCell3.setCellValue(RECEIVER_PART_3);
    receiverCell3.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row15.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    Cell successiveCarrierCell1 = row11.createCell(3);
    successiveCarrierCell1.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_1);
    successiveCarrierCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row11.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell successiveCarrierCell2 = row12.createCell(3);
    successiveCarrierCell2.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_2);
    successiveCarrierCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row12.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell successiveCarrierCell3 = row13.createCell(3);
    successiveCarrierCell3.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_3);
    successiveCarrierCell3.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row13.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell reservationsCell1 = row15.createCell(3);
    reservationsCell1.setCellValue(RESERVATIONS_PART_1);
    reservationsCell1.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row15.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row16 = sheet.createRow(currentRow);
    Cell reservationsCell2 = row16.createCell(3);
    reservationsCell2.setCellValue(RESERVATIONS_PART_2);
    reservationsCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row16.createCell(1).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row16.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row16.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row17 = sheet.createRow(currentRow);
    row17.createCell(1).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row17.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row17.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row17.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row18 = sheet.createRow(currentRow);
    Cell takingOverCellLabel = row18.createCell(1);
    takingOverCellLabel.setCellValue(LABEL_TAKING_OVER);
    takingOverCellLabel.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row18.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row18.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row18.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row19 = sheet.createRow(currentRow);
    Cell takingOverContentCell = row19.createCell(1);
    takingOverContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    takingOverContentCell.setCellValue(TAKING_OVER_CONTENT);

    row19.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row19.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row19.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row20 = sheet.createRow(currentRow);
    Cell countryLabelCell = row20.createCell(1);
    countryLabelCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    countryLabelCell.setCellValue(LABEL_COUNTRY);

    row20.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row20.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row20.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row21 = sheet.createRow(currentRow);
    Cell countryContentCell = row21.createCell(1);
    countryContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    countryContentCell.setCellValue(ADDRESS_PART_4.trim());

    row21.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row21.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row21.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row22 = sheet.createRow(currentRow);
    Cell dateCell = row22.createCell(1);
    dateCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    dateCell.setCellValue(LABEL_DATE);

    row22.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row22.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row22.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row23 = sheet.createRow(currentRow);
    Cell arrivalTime = row23.createCell(1);
    arrivalTime.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    arrivalTime.setCellValue(LABEL_ARRIVAL_TIME);

    row23.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    for (int i = 3; i < 9; i++) {
        row23.createCell(i).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    }

    currentRow++;
    Row row24 = sheet.createRow(currentRow);
    Cell goodsDeliveryLabelCell = row24.createCell(1);
    goodsDeliveryLabelCell.setCellValue(LABEL_DELIVERY_OF_GOODS);
    goodsDeliveryLabelCell.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row24.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE));

    currentRow++;
    Row row25 = sheet.createRow(currentRow);
    Cell placeLabelCell = row25.createCell(1);
    placeLabelCell.setCellValue(LABEL_PLACE);
    placeLabelCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row25.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row26 = sheet.createRow(currentRow);
    row26.setHeight((short) 1500);
    Cell placeContentCell = row26.createCell(1);
    placeContentCell.setCellValue(deliveryAddress);
    placeContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row26.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row row27 = sheet.createRow(currentRow);
    Cell deliveryCountryLabelCell = row27.createCell(1);
    deliveryCountryLabelCell.setCellValue(LABEL_COUNTRY);
    deliveryCountryLabelCell.setCellStyle(styles.get(LABEL_LEFT_STYLE));
    Cell deliveryCountryContentCell = row27.createCell(2);
    deliveryCountryContentCell.setCellValue(FRANCE);
    deliveryCountryContentCell.setCellStyle(styles.get(CONTENT_RIGHT_STYLE));

    currentRow++;
    Row row28 = sheet.createRow(currentRow);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$28:$C$28"));
    Cell openingHoursCell = row28.createCell(1);
    openingHoursCell.setCellValue(LABEL_OPENING_HOURS);
    openingHoursCell.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    row28.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    currentRow++;
    Row row29 = sheet.createRow(currentRow);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$29:$C$29"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$30:$C$30"));
    Cell senderInstructionsCell1 = row29.createCell(1);
    senderInstructionsCell1.setCellValue(SENDER_INSTRUCTIONS_BULGARIAN);
    senderInstructionsCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row29.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE));

    sheet.addMergedRegion(CellRangeAddress.valueOf("$D$29:$I$29"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D$30:$I$30"));
    Cell carrierDocumentsCell1 = row29.createCell(3);
    carrierDocumentsCell1.setCellValue(CARRIER_DOCUMENTS_BULGARIAN);
    carrierDocumentsCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));

    for (int i = 4; i < 9; i++) {
        row29.createCell(i).setCellStyle(styles.get(LABEL_TOP_STYLE));
    }

    currentRow++;
    Row row30 = sheet.createRow(currentRow);
    Cell senderInstructionsCell2 = row30.createCell(1);
    senderInstructionsCell2.setCellValue(SENDERS_INSTRUCTIONS_ENGLISH);
    senderInstructionsCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    row30.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    Cell carrierDocumentsCell2 = row30.createCell(3);
    carrierDocumentsCell2.setCellValue(CARRIER_DOCUMENTS_ENGLISH);
    carrierDocumentsCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    for (int i = 4; i < 9; i++) {
        row30.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    }

    for (int i = 23; i < 28; i++) {
        sheet.getRow(i).createCell(8).setCellStyle(styles.get(LABEL_RIGHT_STYLE));
    }

    return currentRow;
}

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

private static int generatePoints10Till15(Long totalBoxesCount, double weight, XSSFSheet sheet,
        Map<String, CellStyle> styles, int currentRow) {

    currentRow++;//w w  w  .j a  v a2  s . c o  m
    Row row31 = sheet.createRow(currentRow);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$31:$E$31"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$F$31:$G$31"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$H$31:$I$31"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$32:$E$32"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$F$32:$G$32"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$H$32:$I$32"));

    Cell labels10Till13Cell = row31.createCell(1);
    labels10Till13Cell.setCellValue(LABELS_10_TILL_13_BULGARIAN);
    labels10Till13Cell.setCellStyle(styles.get(LABEL_TOP_STYLE));

    Cell weightLabelCell1 = row31.createCell(5);
    weightLabelCell1.setCellValue(LABEL_WEIGHT_BULGARIAN);
    weightLabelCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));

    Cell volumeLabelCell1 = row31.createCell(7);
    volumeLabelCell1.setCellValue(LABEL_VOLUME);
    volumeLabelCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row31.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row32 = sheet.createRow(currentRow);

    Cell labels10Till13Cel2 = row32.createCell(1);
    labels10Till13Cel2.setCellValue(LABEL_10_TILL_13_ENGLISH);
    labels10Till13Cel2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell weightLabelCell2 = row32.createCell(5);
    weightLabelCell2.setCellValue(LABEL_WEIGHT_ENGLISH);
    weightLabelCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell volumeLabelCell2 = row32.createCell(7);
    volumeLabelCell2.setCellValue(LABEL_VOLUME_ENGLISH);
    volumeLabelCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row32.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    // DYNAMIC CONTENT FOR BOOKS
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D$33:$E$33"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$F$33:$G$33"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$H$33:$I$33"));

    currentRow++;
    Row row33 = sheet.createRow(currentRow);
    Cell packagesCountCell = row33.createCell(1);
    packagesCountCell.setCellValue(totalBoxesCount);
    packagesCountCell.setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_RIGHT_STYLE));

    Cell packagingMethodCell = row33.createCell(2);
    packagingMethodCell.setCellValue("CARTONS");
    packagingMethodCell.setCellStyle(styles.get(CONTENT_MIDDLE_NO_BORDERS_STYLE));

    Cell goodsNatureCell = row33.createCell(3);
    goodsNatureCell.setCellValue("BOOKS");
    goodsNatureCell.setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_CENTER_STYLE));

    Cell grossWeightCell = row33.createCell(5);

    try {
        BigDecimal bd = new BigDecimal(weight);
        bd = bd.setScale(2, RoundingMode.HALF_UP);
        weight = bd.doubleValue();
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    grossWeightCell.setCellValue(weight);
    grossWeightCell.setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_CENTER_STYLE));

    for (int j = 6; j <= 8; j++) {
        row33.createCell(j).setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_CENTER_STYLE));
    }

    currentRow++;

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

    cellMergeString = String.format("$F$%s:$G$%s", currentRow + 1, currentRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));

    cellMergeString = String.format("$H$%s:$I$%s", currentRow + 1, currentRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));

    Cell totalsLabelCell = totalsRow.createCell(1);
    totalsLabelCell.setCellValue(LABEL_TOTALS_10_TILL_15);
    totalsLabelCell.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    for (int i = 2; i <= 4; i++) {
        totalsRow.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    }

    Cell totalWeightCell = totalsRow.createCell(5);
    totalWeightCell.setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE));
    totalWeightCell.setCellFormula("SUM(F33)");
    totalsRow.createCell(6).setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE));

    Cell totalVolumeCell = totalsRow.createCell(7);
    totalVolumeCell.setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE));
    totalVolumeCell.setCellFormula("SUM(H33)");
    totalsRow.createCell(8).setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE));

    return currentRow;

}

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

private static int generatePoint15Till19(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow) {
    currentRow++;/*from w  w w .  j  a  v  a2  s . co  m*/

    String mergeString;

    for (int i = currentRow; i < currentRow + 7; i++) {
        sheet.createRow(i).setHeightInPoints(25);
        mergeString = String.format("$B$%s:$C$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        mergeString = String.format("$D$%s:$E$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        mergeString = String.format("$F$%s:$G$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        mergeString = String.format("$H$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
    }

    Row row35 = sheet.getRow(currentRow);
    Cell agreementsLabelCell1 = row35.createCell(1);
    agreementsLabelCell1.setCellValue(LABEL_AGREEMENTS_BULGARIAN);
    agreementsLabelCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));

    Cell paidByCell1 = row35.createCell(3);
    paidByCell1.setCellValue(LABEL_PAID_BY_BULGARIAN);
    paidByCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));

    Cell SenderCell1 = row35.createCell(5);
    SenderCell1.setCellValue(LABEL_SENDER_17_BULGARIAN);
    SenderCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row35.createCell(6).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell ReceiverCell1 = row35.createCell(7);
    ReceiverCell1.setCellValue(LABEL_RECEIVER_17_BULGARIAN);
    ReceiverCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row35.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row36 = sheet.getRow(currentRow);
    Cell agreementsLabelCell2 = row36.createCell(1);
    agreementsLabelCell2.setCellValue(LABEL_AGREEMENTS_ENGLISH);
    agreementsLabelCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell paidByCell2 = row36.createCell(3);
    paidByCell2.setCellValue(LABEL_PAID_BY_ENGLISH);
    paidByCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    row36.createCell(4).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    Cell SenderCell2 = row36.createCell(5);
    SenderCell2.setCellValue(LABEL_SENDER_17_ENGLISH);
    SenderCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    row36.createCell(6).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    Cell ReceiverCell2 = row36.createCell(7);
    ReceiverCell2.setCellValue(LABEL_RECEIVER_17_ENGLISH);
    ReceiverCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    row36.createCell(8).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    currentRow++;
    Row row37 = sheet.getRow(currentRow);
    currentRow++;
    Row row38 = sheet.getRow(currentRow);
    currentRow++;
    Row row39 = sheet.getRow(currentRow);
    currentRow++;
    Row row40 = sheet.getRow(currentRow);
    currentRow++;
    Row row41 = sheet.getRow(currentRow);

    for (int i = 1; i < 9; i++) {
        row37.createCell(i).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
        row38.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
        row39.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
        row40.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
        row41.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    }

    Cell carriageChargesCell1 = row37.getCell(3);
    carriageChargesCell1.setCellValue(LABEL_CARRIAGE_CHARGES_BULGARIAN);

    Cell carriageChargesCell2 = row38.getCell(3);
    carriageChargesCell2.setCellValue(LABEL_CARRIAGE_CHANGES_ENGLISH);

    Cell supplementaryChargesCell = row39.getCell(3);
    row39.setHeightInPoints(80);
    supplementaryChargesCell.setCellValue(LABEL_SUPPLEMENTARY_CHARGES);

    Cell customsDutiesCell = row40.getCell(3);
    row40.setHeightInPoints(50);
    customsDutiesCell.setCellValue(LABEL_CUSTOMS_DUTIES);

    Cell otherChargesCell = row41.getCell(3);
    row41.setHeightInPoints(50);
    otherChargesCell.setCellValue(LABEL_OTHER_CHARGES);

    currentRow++;

    for (int i = currentRow; i < currentRow + 2; i++) {
        sheet.createRow(i);
        mergeString = String.format("$B$%s:$C$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
    }

    Row row42 = sheet.getRow(currentRow);
    currentRow++;
    Row row43 = sheet.getRow(currentRow);

    for (int i = 1; i < 9; i++) {
        row42.createCell(i).setCellStyle(styles.get(LABEL_TOP_STYLE));
        row43.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    }

    Cell remarksCell1 = row42.getCell(1);
    remarksCell1.setCellValue(LABEL_REMARKS_BULGARIAN);
    Cell cashOnDeliveryCell1 = row42.getCell(3);
    cashOnDeliveryCell1.setCellValue(LABEL_CASH_ON_DELIVERY_BULGARIAN);

    Cell remarksCell2 = row43.getCell(1);
    remarksCell2.setCellValue(LABEL_REMARKS_ENGLISH);
    Cell cashOnDeliveryCell2 = row43.getCell(3);
    cashOnDeliveryCell2.setCellValue(LABEL_CASH_ON_DELIVERY_ENGLISH);

    return currentRow;
}

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

private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        Pulsiodetails pulsiodetails) {/*  w  w w. ja  v a2  s.co  m*/
    currentRow++;

    String mergeString;

    for (int i = currentRow; i < currentRow + 2; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        for (int j = 1; j < 9; j++) {
            row.createCell(j)
                    .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE));
        }
    }

    for (int i = currentRow + 2; i < currentRow + 16; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
        for (int j = 1; j < 9; j++) {
            row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
        }
    }

    currentRow++;
    Row row45 = sheet.getRow(currentRow);
    row45.setHeightInPoints(30);
    row45.getCell(1).setCellValue(LABEL_POINT_20);

    currentRow++;
    Row row46 = sheet.getRow(currentRow);

    Cell establishedInCell = row46.getCell(1);
    establishedInCell.setCellValue(LABEL_ESTABLISHED_IN);

    Cell establishedOnCell = row46.getCell(2);
    establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON);

    Cell goodsDeliveredCell = row46.getCell(3);
    goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED);

    currentRow++;
    Row row47 = sheet.getRow(currentRow);
    row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL);

    currentRow++;
    Row row48 = sheet.getRow(currentRow);
    Cell cityCell = row48.getCell(1);
    cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    cityCell.setCellValue("Sofia, Bulgaria");

    Cell dateCell = row48.getCell(2);
    dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date()));

    currentRow += 2;
    sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20);

    currentRow++;
    Row row51 = sheet.getRow(currentRow);

    currentRow++;

    row51.getCell(1).setCellValue(TWENTY_TWO);
    row51.getCell(2).setCellValue(TWENTY_THREE);

    // Insert signature picture
    Workbook workbook = sheet.getWorkbook();
    byte[] imageBytes = pulsiodetails.getSignature();
    int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
    CreationHelper helper = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();

    //set top-left corner for the image
    anchor.setCol1(1);
    anchor.setRow1(currentRow);

    XSSFPicture pict = (XSSFPicture) drawing.createPicture(anchor, pictureIdx);
    pict.resize(1.01, 5);

    currentRow += 4;
    Row row56 = sheet.getRow(currentRow);
    row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP);

    currentRow += 2;
    Row row58 = sheet.getRow(currentRow);
    Cell signatureLabelCell1 = row58.getCell(1);
    signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN);

    Cell carrierSignatureCell = row58.getCell(2);
    carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCell = row58.getCell(3);
    receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN);

    currentRow++;
    Row row59 = sheet.getRow(currentRow);
    Cell signatureLabelCell2 = row59.getCell(1);
    signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH);

    Cell carrierSignatureCel2 = row59.getCell(2);
    carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCel2 = row59.getCell(3);
    receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH);

    currentRow++;
    Row row60 = sheet.createRow(currentRow);
    mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    for (int i = 1; i < 9; i++) {
        row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE));
    }

    Cell additionalSpaceCell = row60.getCell(1);
    additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE);

    return currentRow;
}

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

/**
 *
 * @param sheet/*w  ww .  j  a v a 2  s  . c  om*/
 * @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);
            titleCell.setCellValue(currentModel.getTitle());

            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;
}