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

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

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

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

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

Usage

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

private static void insertFooter(Sheet sheet, CellStyle footerStyle, int index, String packageNumber,
        String deliveryAddress, String client) {
    String mergeString;//from w ww  . ja v a  2s  .  co  m

    index += 2;
    Row transportNumberRow = sheet.createRow(index);
    Cell transportNumberCell = transportNumberRow.createCell(1);

    transportNumberCell.setCellValue("Num Tpt: " + packageNumber);
    transportNumberCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 2;
    Row addressLabelRow = sheet.createRow(index);
    Cell addressLabelCell = addressLabelRow.createCell(1);
    addressLabelCell.setCellValue("A livrer chez: ");
    addressLabelCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 1;
    Row addressRow = sheet.createRow(index);
    Cell addressCell = addressRow.createCell(1);
    addressCell.setCellValue(deliveryAddress);
    addressCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 3;
    Row clientLabelRow = sheet.createRow(index);
    Cell clientLabelCell = clientLabelRow.createCell(1);
    clientLabelCell.setCellValue("Pour le compte des Editions: ");
    clientLabelCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 1;
    Row clientRow = sheet.createRow(index);
    Cell clientCell = clientRow.createCell(1);
    clientCell.setCellValue(client);
    clientCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 4;
    Row dateRow = sheet.createRow(index);
    Cell dateCell = dateRow.createCell(1);
    dateCell.setCellValue("Date: ................");
    dateCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 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);
    row2.setHeightInPoints(25);/* w  w  w . ja v a 2  s .c o  m*/

    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);
    Row row7 = sheet.getRow(6);/*from w  w w .  j a  v a 2s.  c o m*/

    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);//www . j  a  v  a  2 s.co  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));
    }/*  ww w  .  ja va 2 s.  c  o  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);
    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 .j  ava2 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_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  ww  .  ja  va  2  s.c o m*/
    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;
    }

}

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

private boolean transferToDbf(String inputFilePath, String outFileDir) throws DBFException {

    //DBF,?/*www.  j av  a 2  s .c o m*/
    DBFField[] fields = new DBFField[6];

    fields[0] = new DBFField();
    fields[0].setName("ACCOUNTID");
    fields[0].setDataType(DBFField.FIELD_TYPE_C);
    fields[0].setFieldLength(6);

    fields[1] = new DBFField();
    fields[1].setName("PARTID");
    fields[1].setDataType(DBFField.FIELD_TYPE_C);
    fields[1].setFieldLength(4);

    fields[2] = new DBFField();
    fields[2].setName("CLIENTID");
    fields[2].setDataType(DBFField.FIELD_TYPE_C);
    fields[2].setFieldLength(8);

    fields[3] = new DBFField();
    fields[3].setName("AMOUNT");
    fields[3].setDataType(DBFField.FIELD_TYPE_N);
    fields[3].setFieldLength(23);
    fields[3].setDecimalCount(2);

    fields[4] = new DBFField();
    fields[4].setName("MONEYTYPE");
    fields[4].setDataType(DBFField.FIELD_TYPE_C);
    fields[4].setFieldLength(4);

    fields[5] = new DBFField();
    fields[5].setName("TYPEMEMO");
    fields[5].setDataType(DBFField.FIELD_TYPE_C);
    fields[5].setFieldLength(40);
    DBFWriter writer = new DBFWriter();
    try {
        writer.setFields(fields);
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    //excel??
    File inputFile = new File(inputFilePath);
    String inputFileName = inputFile.getName();
    String extension = inputFileName.lastIndexOf(".") == -1 ? ""
            : inputFileName.substring(inputFileName.lastIndexOf(".") + 1);
    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();
            for (int i = 1; i < rsRows; i++) {
                if (rs.getCell(5, i).getContents().equals("")) {
                    Object[] rowData = new Object[6];
                    rowData[0] = "000101";
                    rowData[1] = "0001";
                    rowData[4] = "A999";
                    String tradeCode = rs.getCell(4, i).getContents();
                    Double amount = null;
                    if (rs.getCell(6, i).getType() == CellType.NUMBER) {
                        NumberCell numberCell = (NumberCell) rs.getCell(6, i);
                        amount = numberCell.getValue();
                    } else {
                        if (rs.getCell(6, i).getContents().length() == 0) {
                            amount = null;
                        } else {
                            amount = new DecimalFormat("0.00").parse(rs.getCell(6, i).getContents())
                                    .doubleValue(); //String?Double 
                        }

                    }
                    String typeMemo = rs.getCell(7, i).getContents();
                    rowData[2] = tradeCode;
                    rowData[3] = amount;
                    rowData[5] = typeMemo;
                    writer.addRecord(rowData);
                } 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);
            //wb = new XSSFWorkbook(inputFilePath);
            XSSFSheet sheet = wb.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rows; i++) {

                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(5).getStringCellValue().equals("")) {
                    Object[] rowData = new Object[6];
                    rowData[0] = "000101";
                    rowData[1] = "0001";
                    rowData[4] = "A999";
                    if (row.getCell(4) == null) {
                        row.createCell(4);
                        row.getCell(4).setCellValue("");
                    }
                    if (row.getCell(6) == null) {
                        row.createCell(6);
                        row.getCell(6).setCellValue("");
                    }
                    if (row.getCell(7) == null) {
                        row.createCell(7);
                        row.getCell(7).setCellValue("");
                    }
                    String tradeCode = row.getCell(4).getStringCellValue();
                    Double amount = null;
                    if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) {
                        amount = row.getCell(6).getNumericCellValue();
                    } else {
                        row.getCell(6).setCellType(CELL_TYPE_STRING);
                        if (row.getCell(6).getStringCellValue().length() == 0) {
                            amount = null;
                        } else {
                            amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue())
                                    .doubleValue();
                        }
                    }
                    String typeMemo = row.getCell(7).getStringCellValue();
                    rowData[2] = tradeCode;
                    rowData[3] = amount;
                    rowData[5] = typeMemo;
                    writer.addRecord(rowData);
                } else {
                    continue;
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    try {
        //
        String fileName = "0001_SG01_" + frame.getDate().getText() + "_1_ClientCapitalDetail";
        String outFile = outFileDir + "\\" + fileName + ".dbf";
        File file = new File(outFile);
        if (!file.exists()) {
            file.createNewFile();
        }
        OutputStream os = new FileOutputStream(file);
        writer.write(os); //? 
        os.close();
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "???",
                "?", JOptionPane.ERROR_MESSAGE);
        return false;
    }
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Return the session report as an Excel file
 * /*from w  w w  . j  a  v  a2s .  com*/
 * @param sessionData
 * @return
 */
private ByteArrayOutputStream getSessionReportAsExcel(CompanyInfo company, AppUserInfo user,
        SessionDataInfo sessionData) throws AgileBaseException, IOException, SQLException {
    BaseReportInfo report = sessionData.getReport();
    if (report == null) {
        throw new ObjectNotFoundException("No report found in the session");
    }
    // create Excel spreadsheet
    Workbook workbook = new SXSSFWorkbook();
    // the pane 2 report
    String reportName = report.getReportName();
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    reportName = reportName.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    Sheet reportSheet;
    try {
        reportSheet = workbook.createSheet(reportName);
    } catch (IllegalArgumentException iaex) {
        reportSheet = workbook.createSheet(reportName + " " + report.getInternalReportName());
    }
    int rowNum = 0;
    // header
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Row row = reportSheet.createRow(rowNum);
    int columnNum = 0;
    Set<ReportFieldInfo> reportFields = report.getReportFields();
    for (ReportFieldInfo reportField : reportFields) {
        Cell cell = row.createCell(columnNum);
        cell.setCellValue(reportField.getFieldName());
        cell.setCellStyle(boldCellStyle);
        BaseField field = reportField.getBaseField();
        if (field.equals(field.getTableContainingField().getPrimaryKey())) {
            reportSheet.setColumnHidden(columnNum, true);
        }
        columnNum++;
    }
    // data
    rowNum++;
    DataManagementInfo dataManagement = this.databaseDefn.getDataManagement();
    List<DataRowInfo> reportDataRows = dataManagement.getReportDataRows(company, report,
            sessionData.getReportFilterValues(), false, sessionData.getReportSorts(), -1, QuickFilterType.AND,
            false);
    String fieldValue = "";
    boolean defaultReport = (report.equals(report.getParentTable().getDefaultReport()));
    for (DataRowInfo dataRow : reportDataRows) {
        Map<BaseField, DataRowFieldInfo> dataRowFieldMap = dataRow.getDataRowFields();
        row = reportSheet.createRow(rowNum);
        columnNum = 0;
        for (ReportFieldInfo reportField : reportFields) {
            BaseField field = reportField.getBaseField();
            if (field instanceof TextField) {
                fieldValue = dataRowFieldMap.get(field).getKeyValue();
            } else {
                fieldValue = dataRowFieldMap.get(field).getDisplayValue();
            }
            if (!fieldValue.equals("")) {
                Cell cell;
                DatabaseFieldType dbFieldType = field.getDbType();
                if ((defaultReport) && (field instanceof RelationField)) {
                    dbFieldType = ((RelationField) field).getDisplayField().getDbType();
                }
                switch (dbFieldType) {
                case FLOAT:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Double.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                    }
                    break;
                case INTEGER:
                case SERIAL:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Integer.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        logger.debug(nfex.toString() + ": value " + fieldValue.replace(",", ""));
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                        logger.debug("Successfully set string instead");
                    }
                    break;
                case VARCHAR:
                default:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(Helpers.unencodeHtml(fieldValue));
                    break;
                }
            }
            columnNum++;
        }
        rowNum++;
    }
    // Export info worksheet
    addReportMetaDataWorksheet(company, user, sessionData, report, workbook);
    // one worksheet for each of the report summaries
    for (ChartInfo savedChart : report.getSavedCharts()) {
        this.addSummaryWorksheet(company, sessionData, savedChart, workbook);
    }
    // the default summary
    ChartInfo reportSummary = report.getChart();
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    if ((groupings.size() > 0) || (aggregateFunctions.size() > 0)) {
        this.addSummaryWorksheet(company, sessionData, reportSummary, workbook);
    }
    // write to output
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    workbook.write(outputStream);
    return outputStream;
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Add a sheet with export information to the workbook
 *//*from  w  w w.ja  v a  2s  .co  m*/
private static void addReportMetaDataWorksheet(CompanyInfo company, AppUserInfo user,
        SessionDataInfo sessionData, BaseReportInfo report, Workbook workbook) {
    String title = "Export information";
    Sheet infoSheet;
    try {
        infoSheet = workbook.createSheet(title);
    } catch (IllegalArgumentException iaex) {
        // Just in case there happens to be a report called 'Export
        // information'.
        // The sheet name must be unique
        infoSheet = workbook.createSheet(title + " " + report.getInternalReportName());
    }
    Row row = infoSheet.createRow(0);
    Cell cell = row.createCell(1);
    cell.setCellValue("Export from www.agilebase.co.uk");
    row = infoSheet.createRow(2);
    cell = row.createCell(0);
    cell.setCellValue("Company");
    cell = row.createCell(1);
    cell.setCellValue(company.getCompanyName());
    row = infoSheet.createRow(3);
    cell = row.createCell(0);
    cell.setCellValue("Module");
    cell = row.createCell(1);
    ModuleInfo module = report.getModule();
    if (module != null) {
        cell.setCellValue(report.getModule().getModuleName());
    } else {
        cell.setCellValue("");
    }
    row = infoSheet.createRow(4);
    cell = row.createCell(0);
    cell.setCellValue("Report");
    cell = row.createCell(1);
    cell.setCellValue(report.getReportName());
    row = infoSheet.createRow(5);
    cell = row.createCell(0);
    cell.setCellValue("Exported by");
    cell = row.createCell(1);
    cell.setCellValue(user.getForename() + " " + user.getSurname());
    DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    Date date = new Date();
    String now = dateFormat.format(date);
    row = infoSheet.createRow(6);
    cell = row.createCell(0);
    cell.setCellValue("Export time");
    cell = row.createCell(1);
    cell.setCellValue(now);
}