ioutils.java

JAVA EXCEL操作API

		if (Type.TIME.equals(srcType)) //java.sql.Time
		{
			if (Type.VARCHAR.equals(dstType))
			{
				return DateUtils.toString((java.sql.Time) value);
			}
			else if (Type.INTEGER.equals(dstType))
			{
				java.sql.Time date = (java.sql.Time) value;
				return String.valueOf(date.getTime()); //milliseconds
			}
			else if (Type.FLOAT.equals(dstType))
			{
				java.sql.Time date = (java.sql.Time) value;
				return String.valueOf(date.getTime()); //milliseconds
			}
		}
		if (Type.TIMESTAMP.equals(srcType)) //java.sql.Timestamp
		{
			if (Type.VARCHAR.equals(dstType))
			{
				return DateUtils.toString((java.sql.Timestamp) value);
			}
			else if (Type.INTEGER.equals(dstType))
			{
				java.sql.Timestamp date = (java.sql.Timestamp) value;
				return String.valueOf(date.getTime()); //milliseconds
			}
			else if (Type.FLOAT.equals(dstType))
			{
				java.sql.Timestamp date = (java.sql.Timestamp) value;
				return String.valueOf(date.getTime()); //milliseconds
			}
			else if (Type.TIME.equals(dstType))
			{
				return DateUtils.toTime((java.sql.Timestamp) value);
			}
			else if (Type.DATE.equals(dstType))
			{
				return DateUtils.toDate((java.sql.Timestamp) value);
			}
		}
		//--------------------------------------------------   
		if (!formatted)
		{
			throw new RuntimeException(
				"Can not foramt "
					+ value
					+ " from '"
					+ srcType
					+ "' to '"
					+ dstType
					+ "'");
		}
		return value;
	}
	public static Object formatDefaultValue(String value, Type dstType)
	{
		Object defValue = null;
		if (Type.VARCHAR.equals(dstType)
			|| Type.INTEGER.equals(dstType)
			|| Type.FLOAT.equals(dstType))
		{
			defValue = value;
		}
		else if (Type.DATE.equals(dstType))
		{
			defValue = DateUtils.toDate(value);
		}
		else if (Type.TIME.equals(dstType))
		{
			defValue = DateUtils.toTime(value);
		}
		else if (Type.TIMESTAMP.equals(dstType))
		{
			defValue = DateUtils.toTimestamp(value + ".000");
			;
		}
		return defValue;
	}
	//-------------------------------------------------------------------------------------------------
	//-------------------------------------------------------------------------------------------------
	public static void writeTitle(HSSFRow row, int cellIndex, String title)
	{
		HSSFCell cell = row.createCell((short) cellIndex);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		//cell.setEncoding(HSSFCell.ENCODING_COMPRESSED_UNICODE);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(title);
	}
	public static void writeCell(
		HSSFWorkbook workBook,
		HSSFRow row,
		int cellIndex,
		Cell cellObject)
	{
		Schema schema = cellObject.getSchema();
		Column column = schema.getColumn(cellObject.getColumnIndex());
		Type dstType = column.getOutType();
		Type colType = column.getType();
		Object value = cellObject.getValue();
		if (value == null)
		{
			return;
		}
		//@see SQLReader
		//value = (defValue == null) ? value : defValue;
		//Std Type --->Out Type
		value = IoUtils.formatValue(value, colType, dstType);
		int xlsType = TypeUtils.column2Xls(dstType);
		HSSFCell cell = row.createCell((short) cellIndex);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		switch (xlsType)
		{
			case HSSFCell.CELL_TYPE_NUMERIC :
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				writeXlsCell(workBook, cell, value, dstType, xlsType);
				break;
			case HSSFCell.CELL_TYPE_FORMULA :
				cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
				writeXlsCell(workBook, cell, value, dstType, xlsType);
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN :
				cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
				writeXlsCell(workBook, cell, value, dstType, xlsType);
				break;
			case HSSFCell.CELL_TYPE_ERROR :
				cell.setCellType(HSSFCell.CELL_TYPE_ERROR);
				writeXlsCell(workBook, cell, value, dstType, xlsType);
				break;
			default :
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				writeXlsCell(workBook, cell, value, dstType, xlsType);
				break;
		}
	}
	private static void writeXlsCell(
		HSSFWorkbook workBook,
		HSSFCell cell,
		Object value,
		Type outType,
		int xlsType)
	{
		HSSFCellStyle cs = null;
		boolean formatted = false;
		if (Type.VARCHAR.equals(outType)
			|| Type.INTEGER.equals(outType)
			|| Type.FLOAT.equals(outType))
		{
			switch (xlsType)
			{
				case HSSFCell.CELL_TYPE_NUMERIC :
					cell.setCellValue(Double.parseDouble(valueOf(value, "0")));
					return;
				case HSSFCell.CELL_TYPE_BOOLEAN :
					cell.setCellValue(
						(Integer.parseInt(valueOf(value, "0")) == 0) ? false : true);
					return;
				case HSSFCell.CELL_TYPE_STRING :
					cell.setCellValue(valueOf(value, ""));
					return;
			}
		}
		else if (Type.DATE.equals(outType))
		{
			switch (xlsType)
			{
				case HSSFCell.CELL_TYPE_NUMERIC :
					cs = workBook.createCellStyle();
					cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
					cell.setCellStyle(cs);
					cell.setCellValue(((java.util.Date) value));
					return;
				case HSSFCell.CELL_TYPE_STRING :
					cell.setCellValue(DateUtils.toString((Date) value));
					return;
			}
		}
		else if (Type.TIME.equals(outType))
		{
			switch (xlsType)
			{
				case HSSFCell.CELL_TYPE_NUMERIC :
					cs = workBook.createCellStyle();
					cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
					cell.setCellStyle(cs);
					cell.setCellValue((java.util.Date) value);
					return;
				case HSSFCell.CELL_TYPE_STRING :
					cell.setCellValue(DateUtils.toString((Time) value));
					return;
			}
		}
		else if (Type.TIMESTAMP.equals(outType))
		{
			switch (xlsType)
			{
				case HSSFCell.CELL_TYPE_NUMERIC :
					cs = workBook.createCellStyle();
					cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
					cell.setCellStyle(cs);
					cell.setCellValue(((java.util.Date) value));
					return;
				case HSSFCell.CELL_TYPE_STRING :
					cell.setCellValue(DateUtils.toString((Timestamp) value));
					return;
			}
		}
		if (!formatted)
		{
			throw new RuntimeException(
				"Can not foramt "
					+ value
					+ " from '"
					+ outType
					+ "' to '"
					+ xlsType
					+ "'");
		}
	}
	public static void writeCell(
		PreparedStatement pstmt,
		Cell cell,
		int paramIndex)
		throws SQLException, CoreException
	{
		//the same with this.schema
		Schema cellSchema = cell.getSchema();
		int colNum = cell.getColumnIndex() + 1;
		Column column = cellSchema.getColumn(cell.getColumnIndex());
		if (cellSchema.isValidating())
		{
			if (!cell.validate())
			{
				throw new CoreException("validation falied.{" + cell + "}");
			}
		}
		Type colType = column.getType();
		Type dstType = column.getOutType();
		Object defValue = column.getDefaultValue();
		Object value = cell.getValue();
		//@see XlsReader
		//value = (defValue == null) ? value : defValue;
		//Std Type --->Out Type
		value = IoUtils.formatValue(value, colType, dstType);
		if (Type.INTEGER.equals(dstType))
		{
			pstmt.setInt(paramIndex, Integer.parseInt(valueOf(value, "0")));
		}
		else if (Type.FLOAT.equals(dstType))
		{
			pstmt.setFloat(paramIndex, Float.parseFloat(valueOf(value, "0")));
		}
		else if (Type.DATE.equals(dstType))
		{
			pstmt.setDate(paramIndex, (Date) value);
		}
		else if (Type.TIME.equals(dstType))
		{
			pstmt.setTime(paramIndex, (Time) value);
		}
		else if (Type.TIMESTAMP.equals(dstType))
		{
			pstmt.setTimestamp(paramIndex, (Timestamp) value);
		}
		else
		{
			pstmt.setString(paramIndex, valueOf(value, ""));
		}
	}
	public static String valueOf(Object obj, String defValue)
	{
		if (obj == null || obj.toString().length() == 0)
		{
			return defValue;
		}
		else
		{
			return obj.toString();
		}
	}
}