Java 类org.apache.poi.hssf.usermodel.HSSFDateUtil 实例源码

private String getCellValue(HSSFCell cell){
    if(cell == null) return "";

    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue();
    case HSSFCell.CELL_TYPE_BOOLEAN : return Boolean.toString(cell.getBooleanCellValue());
    case HSSFCell.CELL_TYPE_NUMERIC : 
        if(HSSFDateUtil.isCellDateFormatted(cell))
            return DateUtils.formatDateTime("yyyyMMdd", HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
        else
            return new BigDecimal(cell.getNumericCellValue()).toPlainString();
    case HSSFCell.CELL_TYPE_FORMULA : return "";
    case HSSFCell.CELL_TYPE_BLANK : return "";
    default:return "";
    }
}

private Object getNumericCellValue( Cell cell ) {

        double val = cell.getNumericCellValue();

        if( isCellDateFormatted(cell) ) {
            String dateFormat = cell.getCellStyle().getDataFormatString();
            return new CellDateFormatter(dateFormat).format( HSSFDateUtil.getJavaDate(val) );

        } else {

            long fixedVal = (long) val;
            if( val - fixedVal == 0 ) {
                if( fixedVal < Integer.MAX_VALUE ) {
                    return (int) fixedVal;
                } else {
                    return fixedVal;
                }
            } else {
                return cell.getNumericCellValue();
            }
        }

    }

/**
 * Return the numeric value.
 *
 * @param cell the cell to extract the value from.
 * @return the numeric value from the cell.
 */
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
    // Date is typed as numeric
    if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
        DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
        return sdf.format(cell.getDateCellValue());
    }
    // Numeric type (use data formatter to get number format right)
    DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);

    if (cellValue == null) {
        return formatter.formatCellValue(cell);
    }

    return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}

public String getCellValue(Cell cell) {
    if (cell == null)
        return ExcelODAConstants.EMPTY_STRING;

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        return resolveFormula(cell);
    }

    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        if( HSSFDateUtil.isCellDateFormatted(cell) ){       
            Date myjavadate =  HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
            return sdf.format( myjavadate );
        }
        return ((Double) cell.getNumericCellValue()).toString();
    }

    return cell.toString();
}

@Override
public ValueType getNativeValueType(int columnIndex) throws ParseException {
    Cell cell = getCurrentCell(columnIndex);
    final int type = cell.getCellType();
    if (type == Cell.CELL_TYPE_BLANK) {
        return ValueType.EMPTY;
    } else if (type == Cell.CELL_TYPE_STRING) {
        return ValueType.STRING;
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return ValueType.DATE;
        } else {
            return ValueType.NUMBER;
        }
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        return ValueType.NUMBER;
    } else {
        return ValueType.STRING;
    }
}

/**
 * Get the value of the excel-cell as String.
 * 
 * @param workbook
 *            workbook (excel) for evaluating cell formulas
 * @param cell
 *            cell (excel)
 * 
 * @return the value of the excel-cell as String
 */
static String cellValues2String(XSSFWorkbook workbook, XSSFCell cell) {
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case XSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return new SimpleDateFormat(JExUnitConfig.getStringProperty(JExUnitConfig.ConfigKey.DATE_PATTERN))
                    .format(cell.getDateCellValue());
        } else {
            return String.valueOf(cell.getNumericCellValue());
        }
    case XSSFCell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case XSSFCell.CELL_TYPE_FORMULA:
        return evaluateCellFormula(workbook, cell);
    case XSSFCell.CELL_TYPE_BLANK:
        return cell.getStringCellValue();
    case XSSFCell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case XSSFCell.CELL_TYPE_ERROR:
        return String.valueOf(cell.getErrorCellValue());
    }
    return null;
}

protected CellValue parse_number(Cell in) {
    CellValue rv = null;

    try {
        if(HSSFDateUtil.isCellDateFormatted(in)) {
            rv = new CellValue("datetime", Report.DATEFORMAT_OUT.format(in.getDateCellValue()));
        } else {
            rv = new CellValue("number", Double.toString(in.getNumericCellValue()));                
        }
    } catch(IllegalStateException e) {
        // Siehe Dokumentation getNumericCellValue
        rv = new CellValue("string", in.getStringCellValue());
    }

    return rv;
}

/**
 * 获取单元格值
 *
 * @param row    获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                // val = cell.getNumericCellValue();
                // 当excel 中的数据为数值或日期是需要特殊处理
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    double d = cell.getNumericCellValue();
                    Date date = HSSFDateUtil.getJavaDate(d);
                    SimpleDateFormat dformat = new SimpleDateFormat(
                            "yyyy-MM-dd");
                    val = dformat.format(date);
                } else {
                    NumberFormat nf = NumberFormat.getInstance();
                    nf.setGroupingUsed(false);// true时的格式：1,234,567,890
                    val = nf.format(cell.getNumericCellValue());// 数值类型的数据为double，所以需要转换一下
                }
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                val = cell.getCellFormula();
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}

@Override
public ArrayList<MsgEntity> getMsgListFromExcel(File targetFile) throws IOException {
    ArrayList<MsgEntity> list = new ArrayList<MsgEntity>();
    POIFSFileSystem fs = new POIFSFileSystem(targetFile);
    HSSFWorkbook workBook = new HSSFWorkbook(fs);
    try {
        System.out.println("开始解析");
        HSSFSheet sheet = workBook.getSheetAt(0);
        int rows = sheet.getLastRowNum() + 1;
        for (int r = 1; r < rows; r++) {
            HSSFRow row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            MsgEntity msg = new MsgEntity();
            String number = getStringVal(row.getCell(0));
            if(StringUtils.isEmpty(number)){
                continue;
            }
            msg.setTelNumber(number);

            String templateId = getStringVal(row.getCell(1));
            if (!StringUtils.isEmpty(templateId) && StringUtils.isNumeric(templateId)
                    && templateId.indexOf(".") == -1) {
                msg.setTemplateId(Long.parseLong(templateId));
            }
            msg.setContent(getStringVal(row.getCell(2)));
            if (HSSFDateUtil.isCellDateFormatted(row.getCell(3))) {
                msg.setSendTime(row.getCell(3).getDateCellValue());
            }
            list.add(msg);
        }

    } finally {
        workBook.close();
    }
    return list;

}

private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
    switch (cell.getCellTypeEnum()) {
        case NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return new JsonPrimitive(DateFormatterHolder.formatter.format(cell.getDateCellValue().toInstant()));
            } else {
                return new JsonPrimitive(cell.getNumericCellValue());
            }
        case STRING:
            return new JsonPrimitive(cell.getStringCellValue());
        case FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellTypeEnum()) {
                case NUMERIC:
                    return new JsonPrimitive(cellValue.getNumberValue());
                case STRING:
                    return new JsonPrimitive(cellValue.getStringValue());
                case BLANK:
                    return new JsonPrimitive("");
                case BOOLEAN:
                    return new JsonPrimitive(cellValue.getBooleanValue());
                case ERROR:
                default:
                    return null;
            }
        case BLANK:
            return new JsonPrimitive("");
        case BOOLEAN:
            return new JsonPrimitive(cell.getBooleanCellValue());
        case ERROR:
        default:
            return null;
    }
}

public static String getValue(Cell cell) throws Exception {
    String temp = null;

    if (cell != null) {
        SimpleDateFormat f = new SimpleDateFormat(YYYY_MM_DD);

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            // 셀값이 날짜일경우
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                temp = f.format(cell.getDateCellValue());
                // 셀값이 숫자일경우
            } else {
                temp = String.valueOf(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_STRING:
            temp = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            temp = cell.getCellFormula();
            break;
        /*
         * case Cell.CELL_TYPE_BLANK: temp = ""; break;
         */
        case Cell.CELL_TYPE_BOOLEAN:
            temp = String.valueOf(cell.getBooleanCellValue());
            break;
        default:
            temp = "";
            break;
        }

    }
    return temp;
}

public boolean isNum(Sheet sheet, int rowInt, int column) {
    Row row = sheet.getRow(rowInt);
    Cell cell = row.getCell(column);
    boolean resultBoolean = false;
    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        if (!HSSFDateUtil.isCellDateFormatted(cell)) {
            resultBoolean = true;
        }
    }
    return resultBoolean;
}

/**
 * 根据HSSFCell类型设置数据
 *
 * @param cell
 * @return
 */
private String getCellFormatValue(Cell cell) {
    String cellvalue = "";
    if (cell != null) {
        // 判断当前Cell的Type
        switch (cell.getCellType()) {
        // 如果当前Cell的Type为NUMERIC
        case HSSFCell.CELL_TYPE_NUMERIC:
        case HSSFCell.CELL_TYPE_FORMULA: {
            // 判断当前的cell是否为Date
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                // 如果是Date类型则，转化为Data格式
                // 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                // cellvalue = cell.getDateCellValue().toLocaleString();
                // 方法2：这样子的data格式是不带带时分秒的：2011-10-12
                Date date = cell.getDateCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                cellvalue = sdf.format(date);
            }
            // 如果是纯数字
            else {
                // 取得当前Cell的数值
                cellvalue = String.valueOf(cell.getNumericCellValue());
            }
            break;
        }
            // 如果当前Cell的Type为STRIN
        case HSSFCell.CELL_TYPE_STRING:
            // 取得当前的Cell字符串
            cellvalue = cell.getRichStringCellValue().getString();
            break;
        // 默认的Cell值
        default:
            cellvalue = " ";
        }
    } else {
        cellvalue = "";
    }
    return cellvalue;
}

private String getTypeFromNumericCell(Cell cell) {
    try {
        return HSSFDateUtil.isCellDateFormatted(cell) ? DATE.getName() : NUMERIC.getName();
    } catch (IllegalStateException e) {
        return ANY.getName();
    }
}

public String getHSSFCellValue(HSSFCell cell) {
    String value = "";
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue())
                    .toString();
        } else {
            value = String.valueOf(cell.getNumericCellValue());
        }
        break;
    case HSSFCell.CELL_TYPE_STRING:
        value = cell.getRichStringCellValue().toString();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        value = String.valueOf(cell.getNumericCellValue());
        if (value.equals("NaN")) {
            value = cell.getRichStringCellValue().toString();
        }
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        value = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        value = "";
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        value = "";
        break;
    default:
        value = cell.getRichStringCellValue().toString();
    }
    return value;
}

private Date getDateValueOfCell(HSSFCell cell, int cellType) {
    switch (cellType) {

    case Cell.CELL_TYPE_FORMULA:
        return getDateValueOfCell(cell, cell.getCachedFormulaResultType());

    case Cell.CELL_TYPE_NUMERIC:

        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }
        break;

    case Cell.CELL_TYPE_STRING:

        String cellValue = cell.getStringCellValue();
        if (cellValue == null || cellValue.length() <= 0)
            return null;
        else
            try {
                return ServerTools.dateFormat.parse(cell.getStringCellValue());
            } catch (ParseException e) {
                AtomTools.log(Level.SEVERE, "SimpleDateFormat.getInstance().parse(value) failed, " + e.getMessage(), this);
            }
    }
    AtomTools.log(Level.SEVERE, "unknown celltype: " + cellType + "; content of cell = " + cell.toString(), this);
    return null;
}

public KCellType getType() {
  int type = cell.getCellType();
  if (type==Cell.CELL_TYPE_BOOLEAN) {
    return KCellType.BOOLEAN;
  } else if (type==Cell.CELL_TYPE_NUMERIC) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
      return KCellType.DATE; 
    } else {
      return KCellType.NUMBER;
    }
  } else if (type==Cell.CELL_TYPE_STRING) {
    return KCellType.LABEL;
  } else if (type==Cell.CELL_TYPE_BLANK || type==Cell.CELL_TYPE_ERROR) {
    return KCellType.EMPTY;
  } else if (type==Cell.CELL_TYPE_FORMULA) {
    switch(cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR: return KCellType.EMPTY;
    case Cell.CELL_TYPE_BOOLEAN: return KCellType.BOOLEAN_FORMULA;
    case Cell.CELL_TYPE_STRING: return KCellType.STRING_FORMULA;
    case Cell.CELL_TYPE_NUMERIC: 
      if (HSSFDateUtil.isCellDateFormatted(cell)) {
        return KCellType.DATE_FORMULA; 
      } else {
        return KCellType.NUMBER_FORMULA;
      }
    }
  } 
  return null;
}

/**
 * Get the date or number value from a cell
 *
 * @param myCell
 * @return
 * @throws Exception
 */
private static String getNumericValue(HSSFCell myCell) throws Exception {
    String cellData = "";
    if (HSSFDateUtil.isCellDateFormatted(myCell)) {
        cellData += new SimpleDateFormat(OUTPUT_DATE_FORMAT).format(myCell.getDateCellValue()) + CVS_SEPERATOR_CHAR;
    } else {
        cellData += new BigDecimal(myCell.getNumericCellValue()).toString() + CVS_SEPERATOR_CHAR;
    }
    return cellData;
}

public KCellType getType() {
  int type = cell.getCellType();
  if (type==Cell.CELL_TYPE_BOOLEAN) {
    return KCellType.BOOLEAN;
  } else if (type==Cell.CELL_TYPE_NUMERIC) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
      return KCellType.DATE; 
    } else {
      return KCellType.NUMBER;
    }
  } else if (type==Cell.CELL_TYPE_STRING) {
    return KCellType.LABEL;
  } else if (type==Cell.CELL_TYPE_BLANK || type==Cell.CELL_TYPE_ERROR) {
    return KCellType.EMPTY;
  } else if (type==Cell.CELL_TYPE_FORMULA) {
    switch(cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR: return KCellType.EMPTY;
    case Cell.CELL_TYPE_BOOLEAN: return KCellType.BOOLEAN_FORMULA;
    case Cell.CELL_TYPE_STRING: return KCellType.STRING_FORMULA;
    case Cell.CELL_TYPE_NUMERIC: 
      if (HSSFDateUtil.isCellDateFormatted(cell)) {
        return KCellType.DATE_FORMULA; 
      } else {
        return KCellType.NUMBER_FORMULA;
      }
    }
  } 
  return null;
}

public KCellType getType() {
  int type = cell.getCellType();
  if (type==Cell.CELL_TYPE_BOOLEAN) {
    return KCellType.BOOLEAN;
  } else if (type==Cell.CELL_TYPE_NUMERIC) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
      return KCellType.DATE; 
    } else {
      return KCellType.NUMBER;
    }
  } else if (type==Cell.CELL_TYPE_STRING) {
    return KCellType.LABEL;
  } else if (type==Cell.CELL_TYPE_BLANK || type==Cell.CELL_TYPE_ERROR) {
    return KCellType.EMPTY;
  } else if (type==Cell.CELL_TYPE_FORMULA) {
    switch(cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR: return KCellType.EMPTY;
    case Cell.CELL_TYPE_BOOLEAN: return KCellType.BOOLEAN_FORMULA;
    case Cell.CELL_TYPE_STRING: return KCellType.STRING_FORMULA;
    case Cell.CELL_TYPE_NUMERIC: 
      if (HSSFDateUtil.isCellDateFormatted(cell)) {
        return KCellType.DATE_FORMULA; 
      } else {
        return KCellType.NUMBER_FORMULA;
      }
    }
  } 
  return null;
}

static protected Serializable extractCell(org.apache.poi.ss.usermodel.Cell cell) {
        int cellType = cell.getCellType();
        if (cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA) {
            cellType = cell.getCachedFormulaResultType();
        }
        if (cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR ||
            cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK) {
            return null;
        }

        Serializable value = null;
        if (cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN) {
            value = cell.getBooleanCellValue();
        } else if (cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) {
            double d = cell.getNumericCellValue();

            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                value = HSSFDateUtil.getJavaDate(d);
                // TODO: If we had a time datatype, we could use something like the following
                // to distinguish times from dates (although Excel doesn't really make the distinction)
                // Another alternative would be to look for values < 0.60
//                String format = cell.getCellStyle().getDataFormatString();
//                if (!format.contains("d") && !format.contains("m") && !format.contains("y") ) {
//                    // It's just a time
//                }
            } else {
                value = d;
            }
        } else {
            String text = cell.getStringCellValue();
            if (text.length() > 0) {
                value = text;
            }
        }

        return value;
    }

public KCellType getType() {
  int type = cell.getCellType();
  if ( type == Cell.CELL_TYPE_BOOLEAN ) {
    return KCellType.BOOLEAN;
  } else if ( type == Cell.CELL_TYPE_NUMERIC ) {
    if ( HSSFDateUtil.isCellDateFormatted( cell ) ) {
      return KCellType.DATE;
    } else {
      return KCellType.NUMBER;
    }
  } else if ( type == Cell.CELL_TYPE_STRING ) {
    return KCellType.LABEL;
  } else if ( type == Cell.CELL_TYPE_BLANK || type == Cell.CELL_TYPE_ERROR ) {
    return KCellType.EMPTY;
  } else if ( type == Cell.CELL_TYPE_FORMULA ) {
    switch ( cell.getCachedFormulaResultType() ) {
      case Cell.CELL_TYPE_BLANK:
      case Cell.CELL_TYPE_ERROR:
        return KCellType.EMPTY;
      case Cell.CELL_TYPE_BOOLEAN:
        return KCellType.BOOLEAN_FORMULA;
      case Cell.CELL_TYPE_STRING:
        return KCellType.STRING_FORMULA;
      case Cell.CELL_TYPE_NUMERIC:
        if ( HSSFDateUtil.isCellDateFormatted( cell ) ) {
          return KCellType.DATE_FORMULA;
        } else {
          return KCellType.NUMBER_FORMULA;
        }
      default:
        break;
    }
  }
  return null;
}

private List<List<String>> read(Workbook wb, int sheetIndex) {
    List<List<String>> dataLst = Lists.newArrayList();
    Sheet sheet = wb.getSheetAt(sheetIndex);
    this.totalRows = sheet.getPhysicalNumberOfRows();
    if ((this.totalRows >= 1) && (sheet.getRow(0) != null)) {
        this.totalCells = sheet.getRow(0).getLastCellNum(); // 获取最后一个不为空的列是第几个
    }
    for (int r = 0; r < this.totalRows; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            List<String> rowLst = Lists.newArrayList();
            for (int c = 0; c < getTotalCells(); c++) {
                Cell cell = row.getCell(c);

                String cellValue = "";
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case 0:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();

                                cellValue = DateUtils.dateToStr(date,
                                        "yyyy-MM-dd HH:mm:ss");

                            } else {
                                Integer num = (int) cell.getNumericCellValue();
                                cellValue = String.valueOf(num);
                            }
                            break;
                        case 1:
                            cellValue = cell.getStringCellValue().trim();
                            break;
                        case 4:
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case 2:
                            cellValue = cell.getCellFormula();
                            break;
                        case 3:
                            cellValue = "";
                            break;
                        case 5:
                            cellValue = "非法字符";
                            break;
                        default:
                            cellValue = "未知类型";
                    }
                }
                rowLst.add(cellValue);
            }
            dataLst.add(rowLst);
        }
    }
    return dataLst;
}

private Date getDateValue(Cell cell) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
        return cell.getDateCellValue();
    }
    throw new IllegalArgumentException("Can't get Date because type of cell is not Date");
}

private static Date getCellDate(Cell cell) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
        return cell.getDateCellValue();
    }
    return DateUtil.getJavaDate(cell.getNumericCellValue());
}

public final static PartitionMap<String ,RCell> readDatas(Sheet i_Sheet ,Integer i_BeginRow ,Integer i_EndRow)
{
    PartitionMap<String ,RCell> v_Ret      = new TablePartition<String ,RCell>();
    Sheet                       v_Sheet    = i_Sheet;
    int                         v_BeginRow = 0;
    int                         v_EndRow   = 0;

    if ( i_BeginRow != null )
    {
        v_BeginRow = i_BeginRow.intValue();

        if ( v_BeginRow < 0 )
        {
            v_BeginRow = 0;
        }
    }

    if ( i_EndRow != null )
    {
        v_EndRow = i_EndRow.intValue();
    }
    else
    {
        v_EndRow = v_Sheet.getPhysicalNumberOfRows();
    }

    for (int v_RowNo=v_BeginRow; v_RowNo<=v_EndRow; v_RowNo++)
    {
        Row v_Row = v_Sheet.getRow(v_RowNo);
        if ( v_Row == null )
        {
            continue;
        }

        short v_CellCount = v_Row.getLastCellNum();

        for (int v_ColumnNo=0; v_ColumnNo<v_CellCount; v_ColumnNo++)
        {
            Cell v_Cell = v_Row.getCell(v_ColumnNo);
            if ( v_Cell == null )
            {
                continue;
            }

            if ( v_Cell.getCellTypeEnum() == CellType.STRING )
            {
                String v_Value = v_Cell.getStringCellValue();

                if ( !Help.isNull(v_Value) )
                {
                    RCell        v_RCell    = new RCell(v_RowNo ,v_ColumnNo);
                    List<String> v_Decimals = StringHelp.getString(v_Cell.getCellStyle().getDataFormatString() ,$Decimal);
                    if ( !Help.isNull(v_Decimals) )
                    {
                        v_RCell.setDecimal(v_Decimals.get(0).split("\\.")[1].length());
                    }

                    v_Ret.putRow(v_Value.trim() ,v_RCell);
                }
            }
            else if ( v_Cell.getCellTypeEnum() == CellType.NUMERIC )
            {
                if ( HSSFDateUtil.isCellDateFormatted(v_Cell) ) 
                {
                    if ( v_Cell.getDateCellValue() != null )
                    {
                        v_Ret.putRow((new Date(v_Cell.getDateCellValue())).getFull() ,new RCell(v_RowNo ,v_ColumnNo));
                    }
                } 
                else 
                {
                    v_Ret.putRow(String.valueOf(v_Cell.getNumericCellValue()) ,new RCell(v_RowNo ,v_ColumnNo));
                }
            }
        }
    }

    return v_Ret;
}

/**
 * 复制单位格（空白行的复制，即只复制格式和固定文字，不填充数据）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-07-03
 * @version     v1.0
 *
 * @param i_RTemplate      模板对象
 * @param i_TemplateCell   模板中的单元格对象
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataCell       数据中的单元格对象
 * @param io_RSystemValue 系统变量信息
 * @param i_Datas          本行对应的数据
 * @param io_RValue        小计循环的迭代器
 * @return                 
 */
public final static void copyCellByBlankSpace(RTemplate i_RTemplate ,Cell i_TemplateCell ,RWorkbook i_DataWorkbook ,Cell i_DataCell ,RSystemValue io_RSystemValue)
{
    // 复制样式
    i_DataCell.setCellStyle(i_DataWorkbook.getCellStyle(i_RTemplate ,i_TemplateCell.getCellStyle().getIndex()));

    // 复制评论
    copyComment(i_RTemplate ,i_TemplateCell ,i_DataWorkbook ,i_DataCell);

    // 复制数据类型
    CellType v_CellType = i_TemplateCell.getCellTypeEnum();
    // i_DataCell.setCellType(v_CellType);  不能在此统一设置，原因是：下面代码对类型是有浮动的

    if ( v_CellType == CellType.NUMERIC ) 
    {
        i_DataCell.setCellType(v_CellType);

        if ( HSSFDateUtil.isCellDateFormatted(i_TemplateCell) ) 
        {
            i_DataCell.setCellValue(i_TemplateCell.getDateCellValue());
        } 
        else 
        {
            i_DataCell.setCellValue(i_TemplateCell.getNumericCellValue());
        }
    }
    else if ( v_CellType == CellType.STRING ) 
    {
        RichTextString v_TemplateRichText = i_TemplateCell.getRichStringCellValue();
        String         v_ValueName        = v_TemplateRichText.toString();

        if ( i_RTemplate.isExists(v_ValueName) )
        {
            i_DataCell.setCellType(v_CellType);
            i_DataCell.setCellValue("");
        }
        else 
        {
            i_DataCell.setCellType(v_CellType);
            copyRichTextStyle(i_RTemplate ,v_TemplateRichText ,i_DataWorkbook ,i_DataCell);
        }
    } 
    else if ( v_CellType == CellType.BOOLEAN ) 
    {
        i_DataCell.setCellType(v_CellType);
        i_DataCell.setCellValue(i_TemplateCell.getBooleanCellValue());
    } 
    else if ( v_CellType == CellType.FORMULA) 
    {
        i_DataCell.setCellType(v_CellType);
        i_DataCell.setCellFormula(i_TemplateCell.getCellFormula());
    } 
    else 
    {
        // Nothing.
        i_DataCell.setCellType(v_CellType);
    }
}

public String getCellValue(int row, int column, String dateType) throws Exception {
    String temp = null;

    if (sheet != null) {
        Row rowObj = sheet.getRow(row);

        if (rowObj != null) {
            Cell cell = rowObj.getCell(column);

            if (cell != null) {
                SimpleDateFormat f = null;

                if (dateType != null && dateType != "") {
                    f = new SimpleDateFormat(dateType);
                }

                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC :
                        // 셀값이 날짜일경우
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            temp = f.format(cell.getDateCellValue());
                            // 셀값이 숫자일경우
                        } else {
                            temp = String.valueOf(cell.getNumericCellValue());
                        }
                        break;
                    // 셀값이 문자열이면 ..
                    case Cell.CELL_TYPE_STRING :
                        temp = cell.getStringCellValue();
                        break;
                    // 셀값이 수식이라면 수식을 얻어온다.
                    case Cell.CELL_TYPE_FORMULA :
                        temp = cell.getCellFormula();
                        break;
                    /*
                     * case Cell.CELL_TYPE_BLANK: temp = ""; break;
                     */
                    // 셀값이 boolean형태이면

                    case Cell.CELL_TYPE_BOOLEAN :
                        temp = String.valueOf(cell.getBooleanCellValue());
                        break;
                    default :
                        temp = "";
                        break;
                }
            }
        }
    }
    return temp;
}

public String xlsToCsv(String sourcePath, boolean useBom) throws IOException {
    String destination = sourcePath + ".csv";
    try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            destination), Charsets.UTF_8.name()))) {
        if (useBom) {
            writer.write('\ufeff');
        }
        // Get the workbook object for XLSX file
        try (Workbook wBook = loadWorkbook(sourcePath)) {
            // Get first sheet from the workbook
            Sheet sheet = wBook.getSheetAt(0);
            for (Row row : sheet) {
                boolean hasContent = false;
                String separator = "";
                // For each row, iterate through each column
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        writer.write(separator + "\"" + cell.getBooleanCellValue() + "\"");
                        hasContent = true;
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                    case Cell.CELL_TYPE_FORMULA:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            LocalDateTime localDate = LocalDateTime.ofInstant(cell.getDateCellValue().toInstant(), ZoneId.systemDefault());
                            writer.write(separator + "\"" + formatter.format(localDate) + "\"");
                        } else {
                            // ignore formatting and output raw values. Trim decimal places for integers
                            double value = cell.getNumericCellValue();
                            if (value == Math.floor(value)) {
                                writer.write(separator + "\"" + (int) cell.getNumericCellValue() + "\"");
                            } else {
                                writer.write(separator + "\"" + cell.getNumericCellValue() + "\"");
                            }
                        }
                        hasContent = true;
                        break;
                    case Cell.CELL_TYPE_STRING:
                        writer.write(separator + "\"" + cell.getStringCellValue().replace("\"", "\"\"").trim() + "\"");
                        hasContent = true;
                        break;

                    case Cell.CELL_TYPE_BLANK:
                        writer.write(separator + "\"\"");
                        break;
                    default:
                        writer.write(separator + "\"" + cell.getStringCellValue().replace("\"", "\"\"").trim() + "\"");
                        hasContent = true;

                    }
                    separator = ",";
                }
                // Append new line at the end of each row
                if (hasContent) {
                    writer.write(System.lineSeparator());
                }
            }
        }
    }

    return destination;
}

public static ArrayList<ArrayList<ArrayList<Object>>> readExcel2003(File file){
    try{
        ArrayList<ArrayList<ArrayList<Object>>> sheetArray = new ArrayList<ArrayList<ArrayList<Object>>> ();
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
        for(int sheetNum = 0;sheetNum < wb.getNumberOfSheets();sheetNum++){
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> colList;
            HSSFSheet sheet = wb.getSheetAt(sheetNum);
            HSSFRow row;
            HSSFCell cell;
            Object value;
            for(int i = 0 , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if(row == null){
                    //����ȡ��Ϊ��ʱ
                    if(i != sheet.getPhysicalNumberOfRows()){//�ж��Ƿ������һ��
                        rowList.add(colList);
                    }
                    continue;
                }else{
                    rowCount++;
                }
                for( int j = 0 ; j <= row.getLastCellNum() ;j++){
                    cell = row.getCell(j);
                    if(cell == null ){
                        //���õ�Ԫ��Ϊ��
                        if(j != row.getLastCellNum()){//�ж��Ƿ��Ǹ��������һ����Ԫ��
                            colList.add("");
                        }
                        continue;
                    }
                    switch(cell.getCellType()){
                     case XSSFCell.CELL_TYPE_STRING:  
                            value = cell.getStringCellValue();  
                            break;  
                        case XSSFCell.CELL_TYPE_NUMERIC:  
                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                                value = df.format(cell.getNumericCellValue());  
                            } else if ("General".equals(cell.getCellStyle()  
                                    .getDataFormatString())) {  
                                value = nf.format(cell.getNumericCellValue());  
                            } else {  
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell  
                                        .getNumericCellValue()));  
                            }  
                            break;  
                        case XSSFCell.CELL_TYPE_BOOLEAN:  
                            value = Boolean.valueOf(cell.getBooleanCellValue());
                            break;  
                        case XSSFCell.CELL_TYPE_BLANK:  
                            value = "";  
                            break;  
                        default:  
                            value = cell.toString();  
                    }// end switch
                    colList.add(value);
                }//end for j
                rowList.add(colList);
            }//end for i
            sheetArray.add(rowList);
        }// end sheetNum

        return sheetArray;
    }catch(Exception e){
        return null;
    }
}

public static ArrayList<ArrayList<ArrayList<Object>>>  readExcel2007(File file){
    try{
        ArrayList<ArrayList<ArrayList<Object>>> sheetArray = new ArrayList<ArrayList<ArrayList<Object>>> ();
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
        for(int sheetNum = 0;sheetNum < wb.getNumberOfSheets();sheetNum++){
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> colList;
            XSSFSheet sheet = wb.getSheetAt(sheetNum);
            XSSFRow row;
            XSSFCell cell;
            Object value;
            for(int i = 0 , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if(row == null){
                    //����ȡ��Ϊ��ʱ
                    if(i != sheet.getPhysicalNumberOfRows()){//�ж��Ƿ������һ��
                        rowList.add(colList);
                    }
                    continue;
                }else{
                    rowCount++;
                }
                for( int j = 0 ; j <= row.getLastCellNum() ;j++){
                    cell = row.getCell(j);
                    if(cell == null ){
                        //���õ�Ԫ��Ϊ��
                        if(j != row.getLastCellNum()){//�ж��Ƿ��Ǹ��������һ����Ԫ��
                            colList.add("");
                        }
                        continue;
                    }
                    switch(cell.getCellType()){
                     case XSSFCell.CELL_TYPE_STRING:  
                            value = cell.getStringCellValue();  
                            break;  
                        case XSSFCell.CELL_TYPE_NUMERIC:  
                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                                value = df.format(cell.getNumericCellValue());  
                            } else if ("General".equals(cell.getCellStyle()  
                                    .getDataFormatString())) {  
                                value = nf.format(cell.getNumericCellValue());  
                            } else {  
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell  
                                        .getNumericCellValue()));  
                            }  
                            break;  
                        case XSSFCell.CELL_TYPE_BOOLEAN:  
                            value = Boolean.valueOf(cell.getBooleanCellValue());
                            break;  
                        case XSSFCell.CELL_TYPE_BLANK:  
                            value = "";  
                            break;  
                        default:  
                            value = cell.toString();  
                    }// end switch
                    colList.add(value);
                }//end for j
                rowList.add(colList);
            }//end for i
            sheetArray.add(rowList);
        }// end sheetNum
        return sheetArray;
    }catch(Exception e){
        return null;
    }
}

/**
 * create table by sheet
 *
 * @param sheet
 */
private void createTable(Sheet sheet) {
    StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
    createTableSql.append(sheet.getSheetName());
    createTableSql.append("(");
    Iterator<Row> rit = sheet.rowIterator();
    Row rowHeader = rit.next();
    List<String> columns = new ArrayList<>();
    for (int i = 0; i < rowHeader.getPhysicalNumberOfCells(); i++) {
        createTableSql.append(rowHeader.getCell(i).getStringCellValue());
        if (i == rowHeader.getPhysicalNumberOfCells() - 1) {
            createTableSql.append(" TEXT");
        } else {
            createTableSql.append(" TEXT,");
        }
        columns.add(rowHeader.getCell(i).getStringCellValue());
    }
    createTableSql.append(")");
    database.execSQL(createTableSql.toString());
    while (rit.hasNext()) {
        Row row = rit.next();
        ContentValues values = new ContentValues();
        for (int n = 0; n < row.getPhysicalNumberOfCells(); n++) {
            if (row.getCell(n) == null) {
                continue;
            }
            if (row.getCell(n).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(row.getCell(n))) {
                    if (sdf == null) {
                        values.put(columns.get(n), DateFormat.getDateTimeInstance().format(row.getCell(n).getDateCellValue()));
                    } else {
                        values.put(columns.get(n), sdf.format(row.getCell(n).getDateCellValue()));
                    }
                } else {
                    String value = getRealStringValueOfDouble(row.getCell(n).getNumericCellValue());
                    values.put(columns.get(n), value);
                }
            } else if (row.getCell(n).getCellType() == Cell.CELL_TYPE_STRING) {
                values.put(columns.get(n), row.getCell(n).getStringCellValue());
            }
        }
        if (values.size() == 0)
            continue;
        long result = database.insert(sheet.getSheetName(), null, values);
        if (result < 0) {
            throw new RuntimeException("Insert value failed!");
        }
    }
}

public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);

    int columnIndex = getColumnIndex(column);
    HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle style = cell.getCellStyle();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
            }

    case HSSFCell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

        case HSSFCell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" +
                row + ", column=" + column);

        case HSSFCell.CELL_TYPE_BLANK:
        return null;

        case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

        case HSSFCell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row +
                ", column=" + column);

        default:
        throw new DataTypeException("Unsupported type at row=" + row +
                ", column=" + column);
    }
}

private Cell readCell(XSSFCell poiCell) {
    cell = new Cell();

    int column = poiCell.getColumnIndex();
    cell.setColumnNo(column);
    cell.setRowNo(poiCell.getRowIndex());
    int cellType = poiCell.getCellType();

    switch (cellType) {
    case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: {
        cell.setType(CellValueType.IS_NULL);
        // cell.setValue(StringUtils.EMPTY);
        break;
    }
    case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: {
        cell.setType(CellValueType.IS_NUMERIC);
        if (DateUtil.isCellDateFormatted(poiCell)) {
            cell.setType(CellValueType.IS_DATE_TIME);
            cell.setValue(HSSFDateUtil.getJavaDate(poiCell
                    .getNumericCellValue()));
        } else {
            cell.setValue(poiCell.getNumericCellValue());
        }
        break;
    }
    case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: {
        cell.setType(CellValueType.IS_STRING);
        cell.setValue(poiCell.getStringCellValue());
        break;
    }
    case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: {
        cell.setType(CellValueType.IS_STRING);
        cell.setValue(poiCell.getCellFormula());
        break;
    }
    case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: {
        cell.setType(CellValueType.IS_BOOL);
        cell.setValue(poiCell.getBooleanCellValue());
        break;
    }
    case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: {
        cell.setType(CellValueType.IS_ERROR);
        // cell.setValue(StringUtils.EMPTY);
        break;
    }
    }
    cell.setWidth(cells.getColumnWidth(column));
    cell.setHeight(row.getHeight());

    poiStyle = poiCell.getCellStyle();
    Style style = readStyle(poiStyle);
    style = styleCollection.addStyle(style);
    cell.setStyle(style);

    return cell;
}

/**
 * Get value of given cell.
 * 
 * @param cell
 * @return
 * @throws IllegalArgumentException
 */
private String getCellValue(Cell cell) throws IllegalArgumentException {
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            return null;
        case Cell.CELL_TYPE_BOOLEAN:
            if (cell.getBooleanCellValue()) {
                return "true";
            } else {
                return "false";
            }
        case Cell.CELL_TYPE_ERROR:
        case Cell.CELL_TYPE_FORMULA:
            LOG.info("Formula value: {}", cell.getStringCellValue());
            throw new IllegalArgumentException("Wrong cell type: " + cell.getCellType() +
                    " on row: " + Integer.toString(cell.getRowIndex()) +
                    " column: " + Integer.toString(cell.getColumnIndex()));
        case Cell.CELL_TYPE_NUMERIC:
            if (config.advancedDoubleParser) {
                // Check for Date - https://poi.apache.org/faq.html#faq-N1008D FAQ 8
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    final Calendar cal = new GregorianCalendar();
                    cal.setTime(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    final StringBuilder dateStr = new StringBuilder(10);
                    dateStr.append(cal.get(Calendar.YEAR));
                    dateStr.append("-");
                    dateStr.append(String.format("%02d", cal.get(Calendar.MONTH) + 1));
                    dateStr.append("-");
                    dateStr.append(String.format("%02d", cal.get(Calendar.DAY_OF_MONTH)));
                    return dateStr.toString();
                }
                // Can be double or long/integer.
                final double doubleValue = cell.getNumericCellValue();
                // Check if the value is decimal or not.
                if ((doubleValue % 1) == 0) {
                    // It's integer or long.
                    return Long.toString((long)doubleValue);
                } else {
                    return Double.toString(doubleValue);
                }
            } else {
                return Double.toString(cell.getNumericCellValue());
            }
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        default:
            throw new IllegalArgumentException("Unknown cell type: " + cell.getCellType() +
                    " on row: " + Integer.toString(cell.getRowIndex()) +
                    " column: " + Integer.toString(cell.getColumnIndex()));
    }
}

public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);

    int columnIndex = getColumnIndex(column);
    HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle style = cell.getCellStyle();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
            }

    case HSSFCell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

        case HSSFCell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" +
                row + ", column=" + column);

        case HSSFCell.CELL_TYPE_BLANK:
        return null;

        case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

        case HSSFCell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row +
                ", column=" + column);

        default:
        throw new DataTypeException("Unsupported type at row=" + row +
                ", column=" + column);
    }
}

public void onTransrept (INKFRequestContext aContext) throws Exception {
    IHSSFCellRepresentation aIHSSFCellRepresentation = (IHSSFCellRepresentation)aContext.sourcePrimary(IHSSFCellRepresentation.class);
    HSSFCell vCell = aIHSSFCellRepresentation.getCellReadOnly();
    String vSheetName = vCell.getSheet().getSheetName();

    StringBuilder vCellXML = new StringBuilder();

    vCellXML.append("<cell columnIndex=\"");
    vCellXML.append(vCell.getColumnIndex());
    vCellXML.append("\" rowIndex=\"");
    vCellXML.append(vCell.getRow().getRowNum());
    vCellXML.append("\" sheetIndex=\"");
    vCellXML.append(vCell.getSheet().getWorkbook().getSheetIndex(vSheetName));
    vCellXML.append("\">");

    int vCellType = vCell.getCellType();
    if (vCellType == Cell.CELL_TYPE_FORMULA) {
        vCellType = vCell.getCachedFormulaResultType();
    }

    if (vCellType == Cell.CELL_TYPE_NUMERIC) {
        if (HSSFDateUtil.isCellDateFormatted(vCell)) {
            vCellXML.append(vCell.getDateCellValue());
        }
        else {
            vCellXML.append(vCell.getNumericCellValue());
        }
    }
    else if (vCellType == Cell.CELL_TYPE_STRING) {
        vCellXML.append(XMLUtils.escape(vCell.getStringCellValue()));
    }
    else if (vCellType == Cell.CELL_TYPE_BOOLEAN) {
        vCellXML.append(vCell.getBooleanCellValue());
    }
    else if (vCellType == Cell.CELL_TYPE_BLANK) {
    }
    else if (vCellType == Cell.CELL_TYPE_ERROR) {
        vCellXML.append(vCell.getErrorCellValue());
    }
    //

    vCellXML.append("</cell>");

    INKFResponse vResponse = aContext.createResponseFrom(vCellXML.toString());
    vResponse.setExpiry(INKFResponse.EXPIRY_DEPENDENT);
}

private static String getValueOfCell(HSSFCell cell){
    String value = "";
    if(BeanUtil.isBlank(cell)){
        return value;
    }

    // 注意：一定要设成这个，否则可能会出现乱码
    switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                if (date != null) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                }
                else {
                    value = "";
                }
            }
            else {
                value = new DecimalFormat("0").format(cell.getNumericCellValue());
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            // 导入时如果为公式生成的数据则无值
            if (!cell.getStringCellValue().equals("")) {
                value = cell.getStringCellValue();
            }
            else {
                value = cell.getNumericCellValue() + "";
            }
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            value = "";
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
            break;
        default:
            value = "";
    }
    return value;
}

private String getValueOfCell(Cell cell){
    String value = "";
    if(BeanUtil.isBlank(cell)){
        return value;
    }

    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                if (date != null) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                }
                else {
                    value = "";
                }
            }
            else {
                value = new DecimalFormat("0").format(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_FORMULA:
            // if the value is generated by a formula, we set it to null.
            if (!cell.getStringCellValue().equals("")) {
                value = cell.getStringCellValue();
            }
            else {
                value = cell.getNumericCellValue() + "";
            }
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            value = "";
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
            break;
        default:
            value = "";
    }
    return value;
}

public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException{
            JSONObject jobj=new JSONObject();
                    POIFSFileSystem fs      =
            new POIFSFileSystem(new FileInputStream(filename));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
            HSSFSheet sheet = wb.getSheetAt(sheetNo);

            int startRow=0;
            int maxRow=sheet.getLastRowNum();
            int maxCol=0;
            int noOfRowsDisplayforSample = 20;
            if(noOfRowsDisplayforSample > sheet.getLastRowNum()){
                noOfRowsDisplayforSample = sheet.getLastRowNum();
            }

            JSONArray jArr=new JSONArray();
            try {
                for(int i=0;i <= noOfRowsDisplayforSample;i++) {
                    HSSFRow row = sheet.getRow(i);
                    JSONObject obj=new JSONObject();
                    JSONObject jtemp1 = new JSONObject();
                    if(row==null){
                        continue;
                    }
                    if(i==0) {
                        maxCol=row.getLastCellNum();
                    }
                    for(int cellcount=0; cellcount<maxCol; cellcount++){
                        HSSFCell cell = row.getCell(cellcount);
                        CellReference cref = new CellReference(i, cellcount);
                        String colHeader=cref.getCellRefParts()[2];
                        String val=null;
                        if(cell!=null){
                            switch(cell.getCellType()){
                                case HSSFCell.CELL_TYPE_NUMERIC: 
                                     if(HSSFDateUtil.isCellDateFormatted(cell)){
                                        val=Double.toString(cell.getNumericCellValue());
                                        java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                                        DateFormat sdf = new SimpleDateFormat(df);
                                        val = sdf.format(date1);
                                     }else{
                                        val=dfmt.format(cell.getNumericCellValue());
                                     }
                                     break;
                                case HSSFCell.CELL_TYPE_STRING: val=cleanHTML(cell.getRichStringCellValue().getString()); break;
                            }
                        }

                        if(i==0){ // List of Headers (Consider first row as Headers)
                            if(val!=null){
                                jtemp1 = new JSONObject();
                                jtemp1.put("header", val==null?"":val);
                                jtemp1.put("index", cellcount);
                                jobj.append("Header", jtemp1);
                            }
                        }
                        obj.put(colHeader,val);
                    }
//                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
                        jArr.put(obj);
//                    }
                }
            } catch(Exception ex) {
               Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            }
            jobj.put("startrow", startRow);
            jobj.put("maxrow", maxRow);
            jobj.put("maxcol", maxCol);
            jobj.put("index", sheetNo);
            jobj.put("data", jArr);
            jobj.put("filename", filename);

            jobj.put("msg", "XLS has been successfully uploaded");
            jobj.put("lsuccess", true);
            jobj.put("valid", true);
            return jobj;
    }