Example usage for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getStringCellValue

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue.

Prototype

public String getStringCellValue()

Source Link

Document

get the value of the cell as a string - for numeric cells we throw an exception.

Usage

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeIndividualTime() {
    Map userHours = getHoursInfo();
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage");
    String lastUserName = null;/*from   w ww  . ja v  a2s.  co m*/
    List months = new ArrayList();
    for (int i = 5; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        }
        HSSFCell cell = row.getCell((short) 2);
        if (cell != null) {
            if (lastUserName == null) {
                lastUserName = cell.getStringCellValue();
            } else {
                String newUserName = cell.getStringCellValue();
                if ("Month".equals(newUserName.trim()) || "User Name".equals(newUserName.trim())) {
                    continue;
                }
                if ("Normal Working day of a Month".equals(newUserName.trim())) {
                    months = new ArrayList();
                    row = templateSheet.getRow(i - 1);
                    for (int j = 3; j < 1000; j++) {
                        HSSFCell monthCell = row.getCell((short) j);
                        if (monthCell == null) {
                            if (row.getCell((short) (j + 1)) == null) {
                                break;
                            }
                        } else {
                            String monthValue = monthCell.getStringCellValue();
                            if (monthValue != null && !monthValue.equals("")) {
                                months.add(monthCell.getStringCellValue());
                            }
                        }
                    }
                    continue;
                }
                if (newUserName != null) {
                    if (newUserName.equals(lastUserName)) {
                        Region region = new Region();
                        region.setRowTo(i);
                        region.setRowFrom(i - 1);
                        region.setColumnFrom((short) 2);
                        region.setColumnTo((short) 2);
                        templateSheet.addMergedRegion(region);
                        templateSheet.getRow(i - 1).getCell((short) 2).getCellStyle()
                                .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                        HSSFCell activityCell = row.getCell((short) 3);
                        HSSFCell oldActivityCell = templateSheet.getRow(i - 1).getCell((short) 3);
                        if (activityCell.getStringCellValue().equals(oldActivityCell.getStringCellValue())) {
                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 3);
                            region.setColumnTo((short) 3);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                        }

                        for (int j = 4, k = 0; j < 1000;) {
                            HSSFCell detailCell = row.getCell((short) (j));
                            if (detailCell == null) {
                                if (row.getCell((short) (j + 1)) == null) {
                                    break;
                                }
                            } else {
                                percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                        .getCellStyle().getFont(templateWbk));
                                HSSFCell precOfType = row.getCell((short) (j + 2));
                                String prec = precOfType.getStringCellValue().trim();
                                String key = newUserName + "." + months.get(k);
                                TimeUsage timeUsage = (TimeUsage) userHours.get(key);
                                if (!prec.equals("")) {
                                    if (timeUsage.getHours() > 0) {
                                        precOfType.setCellStyle(percentageStyle);
                                        precOfType.setCellValue(
                                                (new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                                    }
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 4));
                                region.setColumnTo((short) (j + 4));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getHours() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 4))
                                            .setCellValue(timeUsage.getHours());
                                }

                                region = new Region();
                                region.setRowTo(i);
                                region.setRowFrom(i - 1);
                                region.setColumnFrom((short) (j + 5));
                                region.setColumnTo((short) (j + 5));
                                templateSheet.addMergedRegion(region);
                                if (timeUsage.getPercentage() > 0) {
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellStyle(percentageStyle);
                                    templateSheet.getRow(i - 1).getCell((short) (j + 5))
                                            .setCellValue(timeUsage.getPercentage());
                                }
                                j += 6;
                                k++;
                                continue;
                            }
                            j++;
                        }
                    } else {
                        lastUserName = newUserName;
                        writePrec(templateSheet, months, newUserName, userHours, row, i);
                    }
                } else {
                    lastUserName = newUserName;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writePrec(HSSFSheet templateSheet, List months, String newUserName, Map userHours,
        HSSFRow row, int rowIndex) {
    for (int j = 4, k = 0; j < 1000;) {
        HSSFCell detailCell = row.getCell((short) (j));
        if (detailCell == null) {
            if (row.getCell((short) (j + 1)) == null) {
                break;
            }/*from   w  w  w.j  av  a 2  s .  c om*/
        } else {
            percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle()
                    .getFont(templateWbk));
            HSSFCell precOfType = row.getCell((short) (j + 2));
            String prec = precOfType.getStringCellValue().trim();
            String key = newUserName + "." + months.get(k);
            TimeUsage timeUsage = (TimeUsage) userHours.get(key);
            if (!prec.equals("")) {
                if (timeUsage.getHours() > 0) {
                    precOfType.setCellStyle(percentageStyle);
                    precOfType.setCellValue((new BigDecimal(prec)).doubleValue() / timeUsage.getHours());
                }
            }
            j += 6;
            k++;
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static Map getHoursInfo() {
    HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage (1)");
    boolean fillMap = false;
    Map userHours = new HashMap();
    List months = new ArrayList();
    for (int i = 4; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            } else {
                fillMap = false;/*from  ww w .  ja  va 2s. co m*/
            }
        } else {
            HSSFCell userNameCell = row.getCell((short) 2);
            if (userNameCell == null) {
                continue;
            }
            String userNameCellValue = userNameCell.getStringCellValue();
            if ("Month".equals(userNameCellValue.trim())) {
                months = new ArrayList();
                for (int j = 3; j < 1000; j++) {
                    HSSFCell monthCell = row.getCell((short) j);
                    if (monthCell == null) {
                        if (row.getCell((short) (j + 1)) == null) {
                            break;
                        }
                    } else {
                        String monthValue = monthCell.getStringCellValue();
                        if (monthValue != null && !monthValue.equals("")) {
                            months.add(monthCell.getStringCellValue());
                        }
                    }
                }
                continue;
            }
            if ("Normal Working day of a Month".equals(userNameCellValue.trim())) {
                continue;
            }
            if ("User Name".equals(userNameCellValue)) {
                fillMap = true;
            } else if (fillMap) {
                for (int j = 3, k = 0; j < 1000; j++) {
                    HSSFCell detailCell = row.getCell((short) j);
                    if (detailCell == null) {
                        break;
                    } else {
                        if (j % 2 == 1) {
                            TimeUsage timeUsage = new TimeUsage();
                            String hoursStr = detailCell.getStringCellValue().trim();
                            String month = (String) months.get(k);
                            userHours.put(userNameCellValue + "." + month, timeUsage);
                            if (hoursStr != null && !hoursStr.equals("")) {
                                double hours = (new BigDecimal(hoursStr)).doubleValue();
                                timeUsage.setHours(hours);
                            }
                        } else {
                            String month = (String) months.get(k);
                            TimeUsage timeUsage = (TimeUsage) userHours.get(userNameCellValue + "." + month);
                            timeUsage.setPercentage(detailCell.getNumericCellValue());
                            k++;
                        }
                    }
                }
            }
        }
    }
    return userHours;
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

private static void writeEffortTotal() {
    HSSFSheet templateSheet = templateWbk.getSheet("Effort Total");
    String lastProjectName = null;
    Date startDateValue = null;//from  w ww . ja v  a  2s  . co  m
    Date endDateValue = null;
    double totalDaysValue = 0;
    for (int i = 4; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell projectNameCell = row.getCell((short) 1);
            HSSFCell startDateCell = row.getCell((short) 2);
            HSSFCell endDateCell = row.getCell((short) 3);
            HSSFCell totalDaysCell = row.getCell((short) 4);
            if (totalDaysCell == null) {
                break;
            }
            String newProjectName = projectNameCell.getStringCellValue();
            Date _startDateValue = startDateCell.getDateCellValue();
            Date _endDateValue = endDateCell.getDateCellValue();
            double _totalDaysValue = totalDaysCell.getNumericCellValue();
            if (lastProjectName == null) {
                lastProjectName = newProjectName;
                startDateValue = _startDateValue;
                endDateValue = _endDateValue;
                totalDaysValue = _totalDaysValue;
            } else {
                if (newProjectName.equals(lastProjectName)) {
                    totalDaysValue += _totalDaysValue;
                    templateSheet.getRow(i - 1).getCell((short) 4).setCellValue(totalDaysValue);
                    if (startDateValue.compareTo(_startDateValue) > 0) {
                        startDateValue = _startDateValue;
                        templateSheet.getRow(i - 1).getCell((short) 2).setCellValue(startDateValue);
                    }
                    if (endDateValue.compareTo(_endDateValue) < 0) {
                        endDateValue = _endDateValue;
                        templateSheet.getRow(i - 1).getCell((short) 3).setCellValue(endDateValue);
                    }
                    templateSheet.removeRow(row);
                    templateSheet.shiftRows(i + 1, 109, -1);
                    i--;
                } else {
                    lastProjectName = newProjectName;
                    startDateValue = _startDateValue;
                    endDateValue = _endDateValue;
                    totalDaysValue = _totalDaysValue;
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

public static void writeEffortSummary() {
    HSSFSheet templateSheet = templateWbk.getSheet("Effort Summary");
    String lastCellValue = null;//from w  w  w  .j  a  va  2 s.c  o m
    double days = 0;
    int count = 1;
    for (int i = 6; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell cell = row.getCell((short) 2);
            HSSFCell dayCell = row.getCell((short) 6);
            if (cell != null) {
                if (lastCellValue == null) {
                    lastCellValue = cell.getStringCellValue();
                    count = 1;
                    String dayStr = dayCell.getStringCellValue().trim();
                    days = new BigDecimal(dayStr).doubleValue();
                    templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count);
                    templateSheet.getRow(i).getCell((short) 4).setCellValue(days);
                } else {
                    String newCellValue = cell.getStringCellValue();
                    if ("Project Name".equals(newCellValue)) {
                        continue;
                    }
                    if (newCellValue != null) {
                        if (newCellValue.equals(lastCellValue)) {
                            Region region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 2);
                            region.setColumnTo((short) 2);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 3);
                            region.setColumnTo((short) 3);
                            templateSheet.addMergedRegion(region);
                            count++;
                            templateSheet.getRow(i - count + 1).getCell((short) 3).setCellValue((double) count);

                            region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) 4);
                            region.setColumnTo((short) 4);
                            templateSheet.addMergedRegion(region);

                            String dayStr = dayCell.getStringCellValue().trim();
                            days += new BigDecimal(dayStr).doubleValue();
                            templateSheet.getRow(i - count + 1).getCell((short) 4).setCellValue(days);
                        } else {
                            lastCellValue = newCellValue;
                            count = 1;
                            templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count);

                            String dayStr = dayCell.getStringCellValue().trim();
                            days = new BigDecimal(dayStr).doubleValue();
                            templateSheet.getRow(i).getCell((short) 4).setCellValue(days);
                        }
                    } else {
                        lastCellValue = newCellValue;
                        count = 1;
                        templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count);

                        String dayStr = dayCell.getStringCellValue().trim();
                        days = new BigDecimal(dayStr).doubleValue();
                        templateSheet.getRow(i).getCell((short) 4).setCellValue(days);
                    }
                }
            }
        }
    }
}

From source file:cn.fql.template.poi.MergeInvoiceSheet.java

License:Open Source License

public static void writeEffort(int index, String sheetName) {
    HSSFSheet templateSheet = templateWbk.getSheet(sheetName);
    String lastCellValue = null;//from w  ww  .j  av  a 2  s . c om
    for (int i = 0; i < 10000; i++) {
        HSSFRow row = templateSheet.getRow(i);
        if (row == null) {
            if (templateSheet.getRow(i + 1) == null) {
                break;
            }
        } else {
            HSSFCell cell = row.getCell((short) index);
            if (cell != null) {
                if (lastCellValue == null && cell.getStringCellValue() != null
                        && !cell.getStringCellValue().trim().equals("")) {
                    lastCellValue = cell.getStringCellValue();
                } else {
                    String newCellValue = cell.getStringCellValue();
                    if (newCellValue != null && !newCellValue.trim().equals("")) {
                        if (newCellValue.equals(lastCellValue)) {
                            Region region = new Region();
                            region.setRowTo(i);
                            region.setRowFrom(i - 1);
                            region.setColumnFrom((short) index);
                            region.setColumnTo((short) index);
                            templateSheet.addMergedRegion(region);
                            templateSheet.getRow(i - 1).getCell((short) index).getCellStyle()
                                    .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                        } else {
                            lastCellValue = newCellValue;
                        }
                    } else {
                        lastCellValue = newCellValue;
                    }
                }
            }
        }
    }
}

From source file:com.afrisoftech.lib.ExportData.java

public static Vector read(String fileName) {
    Vector cellVectorHolder = new Vector();
    try {//from w  ww.  ja  v a 2  s. c  om

        FileInputStream myInput = new FileInputStream(fileName);
        POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
        HSSFSheet mySheet = myWorkBook.getSheetAt(0);
        Iterator rowIter = mySheet.rowIterator();
        while (rowIter.hasNext()) {
            HSSFRow myRow = (HSSFRow) rowIter.next();
            Iterator cellIter = myRow.cellIterator();
            Vector cellStoreVector = new Vector();
            while (cellIter.hasNext()) {
                HSSFCell myCell = (HSSFCell) cellIter.next();
                //Object obj=myCell.getStringCellValue();
                System.out.print(myCell.getCellType() + " -");
                if (myCell.getCellType() == 0) {
                    cellStoreVector.addElement(myCell.getNumericCellValue());

                } else if (myCell.getCellType() == 1) {
                    cellStoreVector.addElement(myCell.getStringCellValue());

                }
                //cellStoreVector.addElement(myCell.getStringCellValue());
            }
            System.out.println();
            cellVectorHolder.addElement(cellStoreVector);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return cellVectorHolder;
}

From source file:com.ah.ui.actions.admin.LicenseMgrAction.java

License:Open Source License

private void setEntitlementKeyCellValue(int cellcount, HSSFCellStyle cs, HSSFCellStyle cs1, HSSFRow r,
        OrderHistoryInfo lsInfo, String keyTitle) {
    for (int i = 0; i < cellcount; i++) {
        HSSFCell c = r.createCell(i);
        c.setCellStyle(cs);//from  w ww.ja v  a2 s . c  om
        switch (i) {
        case 0:
            c.setCellValue(null == lsInfo ? keyTitle : lsInfo.getOrderKey());
            break;
        case 1:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.type")
                    : lsInfo.getLicenseTypeStr());
            break;
        case 2:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.ap")
                    : String.valueOf(lsInfo.getNumberOfAps()));
            if (null != lsInfo) {
                cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                c.setCellStyle(cs1);
            }
            break;
        case 3:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(
                        null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.subscription.end")
                                : lsInfo.getSubEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.cvg")
                        : String.valueOf(lsInfo.getNumberOfCvgs()));
            }
            if (null != lsInfo) {
                if (!NmsUtil.isHostedHMApplication()) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                }
                if (!"N/A".equals(c.getStringCellValue())) {
                    c.setCellStyle(cs1);
                }
            }
            break;
        case 4:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.cvg")
                        : String.valueOf(lsInfo.getNumberOfCvgs()));
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.vhm")
                        : String.valueOf(lsInfo.getNumberOfVhms()));
            }
            if (null != lsInfo) {
                cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                c.setCellStyle(cs1);
            }
            break;
        case 5:
            if (NmsUtil.isHMForOEM()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            } else if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(
                        null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.subscription.end")
                                : lsInfo.getCvgSubEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.end")
                        : lsInfo.getSupportEndTimeStr());
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 6:
            if (NmsUtil.isHMForOEM()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                        : lsInfo.getActiveTimeStr());
            } else if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.end")
                        : lsInfo.getSupportEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 7:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                        : lsInfo.getActiveTimeStr());
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 8:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                    : lsInfo.getActiveTimeStr());
            if (null != lsInfo) {
                c.setCellStyle(cs1);
            }
            break;
        default:
            break;
        }
    }
}

From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java

License:Open Source License

/**
 * Reads the excel file row by row. Fills the excel import internal structure which is column wise.<p>
 * //  w w  w .j  a  va  2 s.  c  om
 * @param in the document input stream
 * 
 * @throws IOException if something goes wring
 */
private void readExcelColumnContents(InputStream in) throws IOException {

    HSSFWorkbook excelWb = new HSSFWorkbook(in);
    HSSFSheet sheet = excelWb.getSheetAt(0);
    int rowsNumber = sheet.getPhysicalNumberOfRows();
    if (rowsNumber > 0) {

        // loop over all rows from excel
        // do not read first column, because here are only user raw names
        for (int rowCounter = 1; rowCounter < sheet.getPhysicalNumberOfRows(); rowCounter++) {
            HSSFRow row = sheet.getRow(rowCounter);

            if ((row != null)) {
                // get number of rows in excel
                if ((rowCounter) > m_rowNumber) {
                    m_rowNumber = rowCounter;
                }
                // loop over all columns in this row
                for (int columnCounter = 0; columnCounter < row.getLastCellNum(); columnCounter++) {
                    CmsExcelColumn cmsExcelCol = (CmsExcelColumn) m_colContents.get(new Integer(columnCounter));
                    if (cmsExcelCol != null) {
                        // read cell
                        HSSFCell cell = row.getCell((short) columnCounter);
                        if (cell != null) {
                            String text = null;
                            try {
                                // read cell content from excel
                                switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_BLANK:
                                case Cell.CELL_TYPE_ERROR:
                                    // ignore all blank or error cells
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    // check for date
                                    if (DateUtil.isCellDateFormatted(cell)
                                            || DateUtil.isValidExcelDate(cell.getNumericCellValue())) {
                                        // valid date
                                        Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                                        text = new Long(date.getTime()).toString();
                                    } else {
                                        // no valid date
                                        text = Double.toString(cell.getNumericCellValue());
                                    }
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    text = Boolean.toString(cell.getBooleanCellValue());
                                    break;
                                case Cell.CELL_TYPE_STRING:
                                default:
                                    text = cell.getStringCellValue();
                                    break;
                                }
                                // add to column list
                                cmsExcelCol.addNewCellValue(rowCounter, text);
                                m_colContents.put(new Integer(columnCounter), cmsExcelCol);
                            } catch (Exception e) {
                                if (LOG.isErrorEnabled()) {
                                    LOG.error(e.toString());
                                }
                            }
                        } else {
                            // add to column list
                            cmsExcelCol.addNewCellValue(rowCounter, "");
                            m_colContents.put(new Integer(columnCounter), cmsExcelCol);
                        }
                    }
                }
            }
        }
    }
}

From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java

License:Open Source License

/**
 * Creates mapping between excel column names set by user and excel column names from excel internal.<p>
 * // w  ww  .  j a  v  a 2s  . c  o m
 * @param in the document input stream
 * 
 * @throws IOException if something goes wring
 */
private void readExcelColumnMappings(InputStream in) throws IOException {

    HSSFWorkbook excelWb = new HSSFWorkbook(in);
    HSSFSheet sheet = excelWb.getSheetAt(0);
    int numberOfRows = sheet.getPhysicalNumberOfRows();
    if (numberOfRows > 0) {

        HSSFRow firstRow = sheet.getRow(0);
        // loop over all columns in first excel row
        Iterator rowIter = firstRow.cellIterator();
        while (rowIter.hasNext()) {
            // get cell
            HSSFCell cell = (HSSFCell) rowIter.next();
            if (cell != null) {
                // get user column name
                String userColName = cell.getStringCellValue();
                // get excel column name
                int excelColName = cell.getCellNum();
                CmsExcelColumn excelCol = new CmsExcelColumn(userColName, excelColName);
                m_colNames.put(userColName, new Integer(excelColName));
                m_colContents.put(new Integer(excelColName), excelCol);
            }
        }
    }
}