List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:achmad.rifai.admin.ui.Saver.java
private void suplier(Db d) throws Exception { org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); XSSFSheet s = w.createSheet("Pemasok"); XSSFRow r1 = s.createRow(0);//from w w w . j a va 2s . com title(0, r1, "Kode"); title(1, r1, "Nama Pemasok"); title(2, r1, "Alamat"); title(3, r1, "Telepon"); title(4, r1, "Hapus"); int x = 1; for (achmad.rifai.erp1.entity.Suplier su : new achmad.rifai.erp1.entity.dao.DAOSuplier(d).all()) { int y = x, z = x, c; for (String st : su.getAlamat()) { XSSFRow r = s.createRow(y); konten(2, r, st); y++; } for (String st : su.getTelp()) { XSSFRow r = s.createRow(z); konten(3, r, st); z++; } if (y > z) c = y; else c = z; XSSFRow r = s.createRow(x); s.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(x, c, 0, 0)); konten1(0, r, su.getKode()); s.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(x, c, 1, 1)); konten1(1, r, su.getNama()); s.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(x, c, 4, 4)); konten1(4, r, "" + su.isDeleted()); x = c + 1; } java.io.FileOutputStream o = new java.io.FileOutputStream(f); w.write(o); o.close(); prog.setValue((13 * 100) / 17); }
From source file:com.accounting.accountMBean.DifferentAccReports.java
public void simplePostProcessXLSX(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); XSSFWorkbook wb = (XSSFWorkbook) document; XSSFCellStyle headerCellStyle = wb.createCellStyle(); XSSFCellStyle headerCellStyle1 = wb.createCellStyle(); XSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); XSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(2).getLastCellNum(); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i);/*from w ww . j a v a 2s . c o m*/ } sheet.shiftRows(0, sheet.getLastRowNum(), 4); XSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue("SALES AGEING REPORT"); firstRow.getCell(0).setCellStyle(headerCellStyle); XSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); // XSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); XSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7)); }
From source file:com.accounting.reportMBean.ReportCommonBean.java
public void simplePostProcessXLSXOfStock(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); XSSFWorkbook wb = (XSSFWorkbook) document; XSSFCellStyle headerCellStyle = wb.createCellStyle(); XSSFCellStyle headerCellStyle1 = wb.createCellStyle(); XSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); XSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(2).getLastCellNum(); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i);//from w ww. ja v a 2s. com } sheet.shiftRows(0, sheet.getLastRowNum(), 4); XSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue("STOCK VALUE WITH VALUES"); firstRow.getCell(0).setCellStyle(headerCellStyle); XSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); // XSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); XSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7)); }
From source file:com.accounting.reportMBean.ReportCommonBean.java
public void simplePostProcessXLSX(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); XSSFWorkbook wb = (XSSFWorkbook) document; XSSFCellStyle headerCellStyle = wb.createCellStyle(); XSSFCellStyle headerCellStyle1 = wb.createCellStyle(); XSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); XSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(2).getLastCellNum(); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i);//from w w w. j av a2s. c o m } sheet.shiftRows(0, sheet.getLastRowNum(), 4); XSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue(pageName); firstRow.getCell(0).setCellStyle(headerCellStyle); XSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); // XSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); XSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7)); }
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;/*from w ww . ja v a2s.c o 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 . j a v a 2 s.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++;//from w w w . j a va 2 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++;// ww w. ja v a2s . 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) {//from www.j a v a 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.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)); }/* w ww. j av a 2 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; }