Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow.

Prototype

@Override
public XSSFRow getRow(int rownum)

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:com.FuntionLibrary.java

public static void updateExcel(int r, XSSFSheet sheet, String Name, String EmailId, String Password,
        String MobileNo, String MailPassswd) {

    XSSFRow row = sheet.getRow(r);

    //iterating c number of columns
    XSSFCell cell = row.getCell(0);//  w w w. jav  a2  s  . c o m

    cell.setCellValue(Name);
    System.out.println(Name);

    XSSFCell cell1 = row.getCell(1);
    cell1.setCellValue(EmailId);
    System.out.println(EmailId);

    XSSFCell cell2 = row.getCell(2);
    cell2.setCellValue(Password);
    System.out.println(Password);

    XSSFCell cell3 = row.getCell(3);
    cell3.setCellValue(MobileNo);
    System.out.println(MobileNo);

    XSSFCell cell4 = row.getCell(4);
    cell4.setCellValue(MailPassswd);
    System.out.println(MailPassswd);
}

From source file:com.gnadenheimer.ddjjinformativa.FXMLDocumentController.java

@FXML
private void cmdGenerar(ActionEvent event) {
    try {/*ww w .j a  va 2 s. co m*/

        String cabecera = "1\t";

        XSSFSheet sheet = getSheet();

        String[] sFecha = sheet.getRow(1).getCell(0).getStringCellValue().split(" ");

        System.out.print(sheet.getPhysicalNumberOfRows());

        cabecera += sFecha[1].split("/")[2] + sFecha[1].split("/")[1] + "\t";

        cabecera += "1\t";

        if (sheet.getRow(0).getCell(0).getStringCellValue().contains("VENTAS")) {
            cabecera += "921\t221\t";
        } else {
            cabecera += "911\t211\t";
        }

        cabecera += "3605563\t8\tDOROTEHA GABRIELLE AGANETHA TOEWS HARDER\t\t0\t\t";

        Integer totalRows = sheet.getPhysicalNumberOfRows() - 8 + 1;

        cabecera += totalRows.toString() + "\t";

        Integer montoTotal = ((Double) sheet.getRow(sheet.getPhysicalNumberOfRows()).getCell(9)
                .getNumericCellValue()).intValue();

        cabecera += montoTotal.toString() + "\t";

        cabecera += "2\n";

        String detalle = "";

        SimpleDateFormat sm = new SimpleDateFormat("dd/MM/yyyy");

        for (int i = 7; i < totalRows + 7; i++) {
            XSSFRow row = sheet.getRow(i);
            detalle += "2\t";

            detalle += row.getCell(1).getStringCellValue().split("-")[0] + "\t";
            detalle += row.getCell(1).getStringCellValue().split("-")[1] + "\t";

            detalle += row.getCell(2).getStringCellValue() + "\t";

            detalle += "1\t";

            detalle += row.getCell(3).getStringCellValue() + "\t";

            detalle += sm.format(row.getCell(0).getDateCellValue()) + "\t";

            detalle += ((Integer) ((Double) row.getCell(6).getNumericCellValue()).intValue()).toString() + "\t";
            detalle += ((Integer) ((Double) row.getCell(7).getNumericCellValue()).intValue()).toString() + "\t";
            detalle += "0\t0\t0\t";
            detalle += ((Integer) ((Double) row.getCell(9).getNumericCellValue()).intValue()).toString() + "\t";
            detalle += "1\t0\t";
            detalle += row.getCell(4).getStringCellValue() + "\n";
        }

        cabecera += detalle;

        String home = System.getProperty("user.home");
        File file = new File(home + "/Downloads/DJI" + sFecha[1].split("/")[2] + sFecha[1].split("/")[1]
                + "_3605563_921.txt");

        try (BufferedWriter writer = Files.newBufferedWriter(file.toPath())) {
            writer.write(cabecera);
        }

    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

From source file:com.griffinslogistics.book.BookForTransportationJSFController.java

public void postProcessXLS(Object document) {
    XSSFWorkbook wb = (XSSFWorkbook) document;

    try {/*from  www . j a va2 s.c o m*/
        XSSFSheet sheet = wb.getSheetAt(0);

        for (int i = 2; i <= sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            row.setHeightInPoints(30);

            XSSFCell cell = sheet.getRow(i).getCell(0);
            String text = cell.getStringCellValue();
            boolean value = Boolean.parseBoolean(text);

            if (value) {
                cell.setCellValue("");
            } else {
                cell.setCellValue("?");
            }
        }
    } catch (Exception e) {
        String message = e.getMessage();
        e.printStackTrace();
    }
}

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;/*w ww . j  a  v  a 2 s . 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  ww . 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 generatePoint15Till19(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow) {
    currentRow++;//www.  j a v  a 2 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) {//from   www  . ja v a 2 s.  c  o 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.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  ww .  j  a  v 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;
}

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

private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        Pulsiodetails pulsiodetails) {/*w ww.ja v  a  2  s  .com*/
    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;
}

From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java

private boolean transferToTxt(String inputFilePath, String outFileDir) {
    File inputFile = new File(inputFilePath);
    String inputFileName = inputFile.getName();
    String extension = inputFileName.lastIndexOf(".") == -1 ? ""
            : inputFileName.substring(inputFileName.lastIndexOf(".") + 1);
    StringBuffer input = null;//from  w  w  w . j av  a2  s  .c  om
    if ("xls".equals(extension)) {
        //JXL?excel 2003??xlsx?
        try {
            //?excel
            InputStream is = new FileInputStream(inputFilePath);
            jxl.Workbook rwb = jxl.Workbook.getWorkbook(is);
            jxl.Sheet rs = rwb.getSheet(0);
            int rsRows = rs.getRows();
            input = new StringBuffer();
            for (int i = 1; i < rsRows; i++) {
                if (rs.getCell(5, i).getContents().equals("")) {
                    String line = "A999@" + rs.getCell(4, i).getContents() + "@"
                            + rs.getCell(6, i).getContents() + "\r\n";
                    line = line.replaceAll(",", "");
                    input.append(line);
                } else {
                    continue;
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    } else if ("xlsx".equals(extension)) {
        //POI ?excel 2007,??excel 2003
        try {
            InputStream fs = new FileInputStream(inputFilePath);
            XSSFWorkbook wb;
            wb = new XSSFWorkbook(fs);
            XSSFSheet sheet = wb.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            input = new StringBuffer();
            for (int i = 1; i < rows; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(5).getStringCellValue().equals("")) {
                    if (row.getCell(4) == null) {
                        row.createCell(4);
                        row.getCell(4).setCellValue("");
                    }
                    if (row.getCell(6) == null) {
                        row.createCell(6);
                        row.getCell(6).setCellValue("");
                    }

                    String tradeCode = row.getCell(4).getStringCellValue();
                    Double amount = null;
                    String amt = null;
                    if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) {
                        amount = row.getCell(6).getNumericCellValue();
                        amt = new DecimalFormat("0.00").format(amount);
                    } else {
                        if (row.getCell(6).getStringCellValue().length() == 0) {
                            amount = null;
                            amt = "";
                        } else {
                            amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue())
                                    .doubleValue(); //String?Double
                            amt = amount.toString();
                        }
                    }
                    String line = "A999@" + tradeCode + "@" + amt + "\r\n";
                    line = line.replaceAll(",", "");
                    input.append(line);
                } else {
                    continue;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    try {
        //
        String fileName = "0004_00000001_" + frame.getDate().getText() + "_DailyFundChg";
        String outFile = outFileDir + "\\" + fileName + ".txt";
        File file = new File(outFile);
        if (!file.exists()) {
            file.createNewFile();
        }
        //
        OutputStream os = new FileOutputStream(file);
        os.write(input.toString().getBytes());
        os.flush();
        os.close();
        return true;

    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "???",
                "?", JOptionPane.ERROR_MESSAGE);
        return false;
    }

}