Java Code Examples for org.apache.poi.hssf.usermodel.HSSFDateUtil#isCellDateFormatted()

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFDateUtil#isCellDateFormatted() . 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 File: ExcelFileReader.java    From birt with 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 2
Source File: XlsUtils.java    From data-prep with 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 File: GenerateDoc.java    From danyuan-application with 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 4
Source File: PoiCell.java    From hop with 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 File: ExcelUtil.java    From RuoYi with 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 File: XlsSchemaParser.java    From data-prep with 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 7
Source File: PoiCell.java    From pentaho-kettle with 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 8
Source File: ExcelUtil.java    From albedo with 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 9
Source File: ExcelUtil.java    From javautils with 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 10
Source File: Wrapper.java    From ExcelReads with 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 11
Source File: XlsTable.java    From Leo with 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 12
Source File: ExcelUtil.java    From supplierShop with 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 13
Source File: ExcelUtil.java    From jeewx with 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 14
Source File: JavaToExcel.java    From hy.common.report with 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 15
Source File: ExcelHelp.java    From hy.common.report with 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 16
Source File: GenerateDoc.java    From danyuan-application with 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 17
Source File: ExcelUtil.java    From LuckyFrameWeb with 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 18
Source File: ExcelUtil.java    From ruoyiplus with 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 19
Source File: ExcelUtil.java    From RuoYi-Vue with 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 20
Source File: ExcelUtils.java    From ssm-Online_Examination with 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;
}