org.apache.poi.hssf.usermodel.HSSFDateUtil Java Examples

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFDateUtil. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source Project: danyuan-application   Author: 514840279   File: GenerateDoc.java    License: Apache License 2.0 6 votes vote down vote up
@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
}
 
Example #2
Source Project: data-prep   Author: Talend   File: XlsUtils.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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);
}
 
Example #3
Source Project: birt   Author: eclipse   File: ExcelFileReader.java    License: Eclipse Public License 1.0 6 votes vote down vote up
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();
}
 
Example #4
Source Project: hop   Author: project-hop   File: PoiCell.java    License: Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #5
Source Project: RuoYi   Author: lerry903   File: ExcelUtil.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 获取单元格值
 *
 * @param row    获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
private Object getCellValue(Row row, int column) {
    if (row == null) {
        return null;
    }
    Object val = "" ;
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // POI Excel 日期格式转换
                    val = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Double) val);
                } else {
                    if ((Double) val % 1 > 0) {
                        val = new DecimalFormat("0.00").format(val);
                    } else {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            } else if (cell.getCellTypeEnum() == CellType.STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                val = cell.getErrorCellValue();
            }

        }
    } catch (Exception e) {
        return val;
    }
    return val;
}
 
Example #6
Source Project: easyexcel   Author: alibaba   File: StyleTest.java    License: Apache License 2.0 5 votes vote down vote up
private void isDate(Cell cell) {
    System.out.println(
        DateUtil.isADateFormat(cell.getCellStyle().getDataFormat(), cell.getCellStyle().getDataFormatString()));
    System.out.println(HSSFDateUtil.isCellDateFormatted(cell));
    DataFormatter f = new DataFormatter();
    System.out.println(f.formatCellValue(cell));

}
 
Example #7
Source Project: albedo   Author: somowhere   File: ExcelUtil.java    License: GNU Lesser General Public License v3.0 5 votes vote down vote up
/**
 * 获取单元格值
 *
 * @param row    获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column) {
	if (row == null) {
		return row;
	}
	Object val = "";
	try {
		Cell cell = row.getCell(column);
		if (cell != null) {
			if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
				val = cell.getNumericCellValue();
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// POI Excel 日期格式转换
					val = DateUtil.getJavaDate((Double) val);
				} else {
					if ((Double) val % 1 > 0) {
						val = new DecimalFormat("0.00").format(val);
					} else {
						val = new DecimalFormat("0").format(val);
					}
				}
			} else if (cell.getCellTypeEnum() == CellType.STRING) {
				val = cell.getStringCellValue();
			} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
				val = cell.getBooleanCellValue();
			} else if (cell.getCellTypeEnum() == CellType.ERROR) {
				val = cell.getErrorCellValue();
			}

		}
	} catch (Exception e) {
		return val;
	}
	return val;
}
 
Example #8
Source Project: javautils   Author: followwwind   File: ExcelUtil.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 获取cell数据
 * @param cell
 * @return
 */
private static String getCellValue(Cell cell){
    String value = "";
    if(cell != null) {
        switch (cell.getCellTypeEnum()) {
            case FORMULA:
                value += cell.getCellFormula();
                break;

            case NUMERIC:
                double cellValue = cell.getNumericCellValue();
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    Date date = HSSFDateUtil.getJavaDate(cellValue);
                    value += DateUtil.format(date, DateUtil.DATE_TIME);
                }else{
                    value += cell.getNumericCellValue();
                }
                break;

            case STRING:
                value += cell.getStringCellValue();
                break;
            case BLANK:
                break;
            case BOOLEAN:
                value += cell.getBooleanCellValue();
                break;
            case ERROR:
                break;
            default:break;
        }
    }
    return value;
}
 
Example #9
Source Project: ExcelReads   Author: MatrixSeven   File: Wrapper.java    License: Apache License 2.0 5 votes vote down vote up
protected String getCellFormatValue(Cell cell) {
    String cellValue;
    if (cell != null) {
        switch (cell.getCellType()) {
            case NUMERIC:
                cellValue = df.format(cell.getNumericCellValue());
                break;
            case FORMULA: {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(date);
                } else {
                    cellValue = String.valueOf(cell.getStringCellValue());
                }
                break;
            }
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case ERROR:
            case BLANK:
            default:
                cellValue = BLANK;
        }
    } else {
        cellValue = BLANK;
    }
    return cellValue;
}
 
Example #10
Source Project: fredbet   Author: fred4jupiter   File: ExcelImportService.java    License: Creative Commons Attribution Share Alike 4.0 International 5 votes vote down vote up
private Match convertRowToMatch(Row row) {
	if (row == null || row.getCell(0) == null) {
		return null;
	}

	String country1 = row.getCell(0).getStringCellValue();
	String country2 = row.getCell(1).getStringCellValue();
	String group = row.getCell(2).getStringCellValue();
	Date kickOffDate = HSSFDateUtil.getJavaDate(row.getCell(3).getNumericCellValue());
	String stadium = row.getCell(4).getStringCellValue();

	Match match = new Match();

	if (Country.fromName(country1) != null) {
		match.getTeamOne().setCountry(Country.fromName(country1));
	} else {
		match.getTeamOne().setName(country1);
	}

	if (Country.fromName(country2) != null) {
		match.getTeamTwo().setCountry(Country.fromName(country2));
	} else {
		match.getTeamTwo().setName(country2);
	}

	match.setGroup(Group.valueOf(group));
	match.setKickOffDate(DateUtils.toLocalDateTime(kickOffDate));
	match.setStadium(stadium);
	return match;
}
 
Example #11
Source Project: data-prep   Author: Talend   File: XlsSchemaParser.java    License: Apache License 2.0 5 votes vote down vote up
private String getTypeFromNumericCell(Cell cell) {
    try {
        return HSSFDateUtil.isCellDateFormatted(cell) ? DATE.getName() : NUMERIC.getName();
    } catch (IllegalStateException e) {
        return ANY.getName();
    }
}
 
Example #12
Source Project: pentaho-kettle   Author: pentaho   File: PoiCell.java    License: Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #13
Source Project: supplierShop   Author: guchengwuyue   File: ExcelUtil.java    License: MIT License 4 votes vote down vote up
/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (cell != null)
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}
 
Example #14
Source Project: ssm-Online_Examination   Author: agamgn   File: ExcelUtils.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 描述:对表格中数值进行格式化
 * @param cell
 * @return
 */
//解决excel类型问题,获得数值
public  String getValue(Cell cell) {
    String value = "";
    if(null==cell){
        return value;
    }
    switch (cell.getCellType()) {
        //数值型
        case Cell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                //如果是date类型则 ,获取该cell的date值
                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                value = format.format(date);;
            }else {// 纯数字
                BigDecimal big=new BigDecimal(cell.getNumericCellValue());
                value = big.toString();
                //解决1234.0  去掉后面的.0
                if(null!=value&&!"".equals(value.trim())){
                    String[] item = value.split("[.]");
                    if(1<item.length&&"0".equals(item[1])){
                        value=item[0];
                    }
                }
            }
            break;
        //字符串类型
        case Cell.CELL_TYPE_STRING:
            value = cell.getStringCellValue().toString();
            break;
        // 公式类型
        case Cell.CELL_TYPE_FORMULA:
            //读公式计算值
            value = String.valueOf(cell.getNumericCellValue());
            if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                value = cell.getStringCellValue().toString();
            }
            break;
        // 布尔类型
        case Cell.CELL_TYPE_BOOLEAN:
            value = " "+ cell.getBooleanCellValue();
            break;
        default:
            value = cell.getStringCellValue().toString();
    }
    if("null".endsWith(value.trim())){
        value="";
    }
    return value;
}
 
Example #15
Source Project: RuoYi-Vue   Author: yangzongzhuan   File: ExcelUtil.java    License: MIT License 4 votes vote down vote up
/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (StringUtils.isNotNull(cell))
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}
 
Example #16
Source Project: ruoyiplus   Author: kongshanxuelin   File: ExcelUtil.java    License: MIT License 4 votes vote down vote up
/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (cell != null)
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}
 
Example #17
Source Project: jfinal-ext3   Author: E7du   File: TypeKit.java    License: Apache License 2.0 4 votes vote down vote up
public static Object convert(String value, Class<?> type, String format) {
     if (StrKit.notBlank(value)) {
         if (String.class.equals(type)) {
             return value;
         }
         
         if (Integer.class.equals(type) || int.class.equals(type)) {
             return Integer.parseInt(value);
         }
         
         if (Double.class.equals(type) || double.class.equals(type)) {
             return Double.parseDouble(value);
         }
         
         if (Float.class.equals(type) || float.class.equals(type)) {
	return Float.parseFloat(value);
}
         
         if (Boolean.class.equals(type) || boolean.class.equals(type)) {
             String valueLower = value.toLowerCase();
             if (valueLower.equals("true") || valueLower.equals("false")) {
                 return Boolean.parseBoolean(value.toLowerCase());
             }
             Integer integer = Integer.parseInt(value);
             if (integer == 0) {
                 return false;
             } else {
                 return true;
             }
         }
         
         if (Long.class.equals(type) || long.class.equals(type)) {
             return Long.parseLong(value);
         }
         
         if (BigInteger.class.equals(type)) {
	return (new BigInteger(value));
}
         
         if (byte[].class.equals(type)) {
	return value.getBytes();
}
         
         if (Short.class.equals(type)) {
	return Short.parseShort(value);
}
         
         if (Byte.class.equals(type)) {
	return Byte.parseByte(value);
}
         
         if (Date.class.equals(type) 
         		|| java.sql.Date.class.equals(type)
         		|| java.sql.Time.class.equals(type)
         		|| java.sql.Timestamp.class.equals(type)) {
         	Date date = null;
             if (value.contains("-") || value.contains("/") || value.contains(":")) {
                 date = getSimpleDateFormatDate(value, format);
             } else {
                 Double d = Double.parseDouble(value);
                 date = HSSFDateUtil.getJavaDate(d, true);
             }
             if (java.sql.Date.class.equals(type)) {
             	return new java.sql.Date(date.getTime());
	}
             if (java.sql.Time.class.equals(type)) {
		return new java.sql.Time(date.getTime());
	}
             if (java.sql.Timestamp.class.equals(type)) {
		return new java.sql.Timestamp(date.getTime());
	}
             return date;
         }
         
         if (BigDecimal.class.equals(type)) {
             return new BigDecimal(value);
         }
     }
     return null;
 }
 
Example #18
Source Project: LuckyFrameWeb   Author: seagull1985   File: ExcelUtil.java    License: GNU Affero General Public License v3.0 4 votes vote down vote up
/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return null;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (cell != null)
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}
 
Example #19
Source Project: danyuan-application   Author: 514840279   File: GenerateDoc.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * @方法名 copySheet
 * @功能 复制sheet
 * @参数 @param sheetFrom
 * @参数 @param sheetTo
 * @参数 @return
 * @返回 HSSFSheet
 * @author Administrator
 * @throws
 */
@SuppressWarnings("deprecation")
private static HSSFSheet copySheet(HSSFSheet sheetFrom, HSSFSheet sheetTo) {
	
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
	
}
 
Example #20
Source Project: hy.common.report   Author: HY-Org   File: ExcelHelp.java    License: Apache License 2.0 4 votes vote down vote up
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.getLastRowNum();
    }
    
    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 ,v_RCell); // 2018-05-07 修复:不再trim()。因为去空格后,原数据格式会被改变,比如说用户就是想导出空格呢?
                                                    //            其二,当为占位符查询时,也是不去空格的查询,这里去空格后,为查询不到匹配的占位符解析信息的。
                }
            }
            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;
}
 
Example #21
Source Project: hy.common.report   Author: HY-Org   File: JavaToExcel.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 复制单位格(空白行的复制,即只复制格式和固定文字,不填充数据)
 * 
 * @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(ExcelFormula.calcFormulaOffset(i_TemplateCell ,i_DataCell));
    } 
    else 
    {
        // Nothing.
        i_DataCell.setCellType(v_CellType);
    }
}
 
Example #22
Source Project: jeewx   Author: zhangdaiscott   File: ExcelUtil.java    License: Apache License 2.0 4 votes vote down vote up
/**
	 * 读取 Excel文件内容
	 * 
	 * @param excel_name
	 * @return
	 * @throws Exception
	 */
	public static List<String[]> readExcel(String excel_name) throws Exception {
		// 结果集
		List<String[]> list = new ArrayList<String[]>();

		HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
				excel_name));

		// 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

		// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
		for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
			HSSFRow hssfrow = hssfsheet.getRow(j);
			if(hssfrow!=null){
			int col = hssfrow.getPhysicalNumberOfCells();
			// 单行数据
			String[] arrayString = new String[col];
			for (int i = 0; i < col; i++) {
				HSSFCell cell = hssfrow.getCell(i);
				if (cell == null) {
					arrayString[i] = "";
				} else if (cell.getCellType() == 0) {
					// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
					if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { 
						  if (HSSFDateUtil.isCellDateFormatted(cell)) {    
						    Date d = cell.getDateCellValue();    
//						    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");    
						     DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						    arrayString[i] = formater.format(d);   
						   } else {    
						       arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue()+"";    
						}
					}
				} else {// 如果EXCEL表格中的数据类型为字符串型
					arrayString[i] = cell.getStringCellValue().trim();
				}
			}
			list.add(arrayString);
		}
		}
		return list;
	}
 
Example #23
Source Project: SQLiteToExcel   Author: li-yu   File: ExcelToSQLite.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 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!");
        }
    }
}
 
Example #24
Source Project: Leo   Author: QihooTest   File: XlsTable.java    License: Apache License 2.0 4 votes vote down vote up
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);
    }
}
 
Example #25
Source Project: excel-streaming-reader   Author: monitorjbl   File: StreamingCell.java    License: Apache License 2.0 3 votes vote down vote up
/**
 * Get the value of the cell as a date. For strings we throw an exception. For
 * blank cells we return a null.
 *
 * @return the value of the cell as a date
 * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is CELL_TYPE_STRING
 * @throws NumberFormatException if the cell value isn't a parsable <code>double</code>.
 */
@Override
public Date getDateCellValue() {
  if(getCellType() == CellType.STRING){
    throw new IllegalStateException("Cell type cannot be CELL_TYPE_STRING");
  }
  return rawContents == null ? null : HSSFDateUtil.getJavaDate(getNumericCellValue(), use1904Dates);
}