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

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

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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.globalsight.util.ExcelUtil.java

License:Apache License

public static String getCellValue(Sheet sheet, int row, int col) {
    String value = "";
    if (sheet == null || row < 0 || col < 0)
        return "";

    Row rowData = sheet.getRow(row);
    if (rowData == null)
        return "";
    Cell cell = rowData.getCell(col);
    if (cell == null)
        return "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        value = String.valueOf((int) cell.getNumericCellValue());
        break;//ww w .ja va  2  s.c o  m
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;

    default:
        value = cell.toString();
    }

    return value;
}

From source file:com.glodon.tika.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to test whether or not the embedded workbook was correctly
 * updated. This method simply recovers the first cell from the first row
 * of the first workbook and tests the value it contains.
 * <p/>//from ww  w  . ja  v a2s .co  m
 * Note that execution will not continue up to the assertion as the
 * embedded workbook is now corrupted and causes an IllegalArgumentException
 * with the following message
 * <p/>
 * <em>java.lang.IllegalArgumentException: Your InputStream was neither an
 * OLE2 stream, nor an OOXML stream</em>
 * <p/>
 * to be thrown when the WorkbookFactory.createWorkbook(InputStream) method
 * is executed.
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void checkUpdatedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                assertEquals(cell.getNumericCellValue(), NEW_VALUE, 0.0001);
            }
        }
    }
}

From source file:com.googlecode.testcase.annotation.handle.toexcel.strategy.ToHtmlWithExcel.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads(sheet);//from   www.ja v  a 2s . com

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

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

private static int generatePoint15Till19(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow) {
    currentRow++;//from w  w  w . j av 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 .co m*/
    currentRow++;

    String mergeString;

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

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

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

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

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

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

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

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

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

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

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

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

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

    currentRow++;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    return currentRow;
}

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

private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        Pulsiodetails pulsiodetails) {/*from w w  w  . j  a va2s .  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  .  j a  v  a 2 s .com*/
    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,?/*from  ww  w  . j  av a  2  s.  c om*/
    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.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

/**
 * Adds the ministry per year to map./*from  w w  w . j a  v  a  2  s.  com*/
 *
 * @param name
 *            the name
 * @param map
 *            the map
 * @param mySheet
 *            the my sheet
 */
private static void addMinistryPerYearToMap(final String name,
        final Map<Integer, List<GovernmentBodyAnnualSummary>> map, final HSSFSheet mySheet) {
    if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {

        final int year = Integer.valueOf(mySheet.getSheetName());

        final List<GovernmentBodyAnnualSummary> yearList = new ArrayList<>();
        final Iterator<Row> rowIterator = mySheet.iterator();

        rowIterator.next();

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            final short maxColIx = row.getLastCellNum();

            if (maxColIx == 10) {
                final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = new GovernmentBodyAnnualSummary(
                        year, row.getCell(0).toString(), getInteger(row.getCell(1).toString()),
                        row.getCell(2).toString(), row.getCell(3).toString(), row.getCell(4).toString(),
                        row.getCell(5).toString(), getInteger(row.getCell(6).toString()),
                        getInteger(row.getCell(7).toString()), row.getCell(8).toString(),
                        row.getCell(9).toString());
                row.getCell(9).toString();

                if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getMinistry())) {
                    yearList.add(governmentBodyAnnualSummary);
                }

            }

        }
        map.put(year, yearList);
    }
}

From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

/**
 * Adds the data for year to map.//  w w w  . j av  a 2s. c  o  m
 *
 * @param name
 *            the name
 * @param map
 *            the map
 * @param mySheet
 *            the my sheet
 */
private static void addDataForYearToMap(String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
        final HSSFSheet mySheet) {
    if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {

        final int year = Integer.valueOf(mySheet.getSheetName());

        final Iterator<Row> rowIterator = mySheet.iterator();

        rowIterator.next();

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            final short maxColIx = row.getLastCellNum();

            if (maxColIx == 10) {
                final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = new GovernmentBodyAnnualSummary(
                        year, row.getCell(0).toString(), getInteger(row.getCell(1).toString()),
                        row.getCell(2).toString(), row.getCell(3).toString(), row.getCell(4).toString(),
                        row.getCell(5).toString(), getInteger(row.getCell(6).toString()),
                        getInteger(row.getCell(7).toString()), row.getCell(8).toString(),
                        row.getCell(9).toString());
                row.getCell(9).toString();

                if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getName())) {
                    map.put(year, governmentBodyAnnualSummary);
                }
            }
        }
    }
}