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

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

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

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

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

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

    currentRow++;/*from w ww. ja  v  a2s  .c om*/
    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++;/* w ww.  j  a  va2 s.c  o 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  va 2  s.c om
    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/*from  w  w  w .  j a va2 s. co  m*/
 * @param leftStyle
 * @param middleStyle
 * @param rightStyle
 * @param footerStyle
 * @return index of the last table row created
 */
private static int insertTableBody(Sheet sheet, CellStyle leftStyle, CellStyle middleStyle,
        CellStyle rightStyle, CellStyle footerStyle, List<BookBoxModel> bookBoxModels) {
    Integer index = 18;
    Integer currentBookTitleIndex = 1;
    Set<Integer> rowsToSum = new HashSet<Integer>();
    Set<Integer> totalsToSum = new HashSet<Integer>();
    String cellMergeString;
    String cellFormula;

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

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

            BookBoxModel currentModel = bookBoxModels.get(i);

            Row row = sheet.createRow(index);

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

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

                row = sheet.createRow(index);
            }

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

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

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

            sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));
            titleCell.setCellStyle(leftStyle);
            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;
}

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

private static void insertFooter(Sheet sheet, CellStyle footerStyle, int index, String packageNumber,
        String deliveryAddress, String client) {
    String mergeString;/*  w  w  w.jav  a  2s .c om*/

    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 += 5;
    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.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);//  w ww.j  a v a  2s .  c om
    row2.setHeightInPoints(25);

    Row row3 = sheet.getRow(2);
    row3.setHeightInPoints(25);

    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);
    row12.setHeightInPoints(25);

    Row row13 = sheet.getRow(12);
    row13.setHeightInPoints(25);

    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.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"));

    Row row6 = sheet.getRow(5);// w w  w.  j a  v  a2  s  .c o  m
    Row row7 = sheet.getRow(6);

    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(bookLabelModel.getTitle());

    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(bookLabelModel.getTitle());

    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(bookLabelModel.getTitle());

    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(bookLabelModel.getTitle());

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

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

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

    Cell CMRCell = headerRow.createCell(3);
    CMRCell.setCellValue(HEADING_CMR);//from w w w  .jav  a  2  s.c  o m
    CMRCell.setCellStyle(styles.get(DEFAULT_STYLE));

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

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

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

From source file:com.griffinslogistics.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  www.j  av  a2 s.  c om

    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);
    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.excel.CMRGenerator.java

private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        Pulsiodetails pulsiodetails) {/*from ww w .j  a  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_JPEG);
    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);

    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize();

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