ioutils.java

JAVA EXCEL操作API

/*
 * Created on 2004-3-22
 *
 * To change the template for this generated file go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
package com.zosatapo.xls.util;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.zosatapo.xls.core.Cell;
import com.zosatapo.xls.core.Column;
import com.zosatapo.xls.core.CoreException;
import com.zosatapo.xls.core.Schema;
import com.zosatapo.xls.core.Type;
/**
 * @author zosatapo
 *
 * To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public class IoUtils
{
	/**
	 * XLS Type<--------->Standard Type
	 * 
	 * public final static Type VARCHAR    = new Type("VARCHAR");//字符串保存
	 * public final static Type INTEGER    = new Type("INTEGER");//字符串保存
	 * public final static Type FLOAT      = new Type("FLOAT");//字符串保存
	 * public final static Type DATE       = new Type("DATE");
	 * public final static Type TIME       = new Type("TIME");
	 * public final static Type TIMESTAMP  = new Type("TIMESTAMP");
	 * 
	 * @see Type
	 */
	public static Object formatXlsValue(Object value, Type dstType)
	{
		if (value == null)
		{
			// System.err.println("[formatXlsValue] "+dstType+",value=null");
			return value;
		}
		//System.err.println("[formatXlsValue] "+dstType+",value.class="+value.getClass().getName());
		if (Type.VARCHAR.equals(dstType)
			|| Type.INTEGER.equals(dstType)
			|| Type.FLOAT.equals(dstType))
		{
			return value;
		}
		else if (Type.DATE.equals(dstType))
		{
			java.util.Date javaDate =
				HSSFDateUtil.getJavaDate(Float.parseFloat(value.toString()));
			return DateUtils.toDate(DateUtils.toString(javaDate));
		}
		else if (Type.TIME.equals(dstType))
		{
			java.util.Date javaDate =
				HSSFDateUtil.getJavaDate(Float.parseFloat(value.toString()));
			return new java.sql.Time(javaDate.getTime());
		}
		else if (Type.TIMESTAMP.equals(dstType))
		{
			java.util.Date javaDate =
				HSSFDateUtil.getJavaDate(Float.parseFloat(value.toString()));
			return new java.sql.Timestamp(javaDate.getTime());
		}
		return value;
	}
	public static Object readCell(HSSFCell cell, Type srcType, Type dstType)
	{
		//System.err.println("[Parameter] column="+cell.getCellNum()+",srcType="+srcType+",dstType="+dstType);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		int type = TypeUtils.column2Xls(srcType);
		Object value = null;
		try
		{
			switch (type)
			{
				case HSSFCell.CELL_TYPE_STRING :
					value = cell.getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_NUMERIC :
					if (Type.DATE.equals(dstType)
						|| Type.DATE.equals(dstType)
						|| Type.DATE.equals(dstType))
					{
						value =
							String.valueOf(
								DateUtils.toDate(cell.getNumericCellValue()).getTime());
					}
					else
					{
						value = String.valueOf(cell.getNumericCellValue());
					}
					break;
				default :
					break;
			}
		}
		catch (NumberFormatException ex)
		{
			//1.试着解析数据来源本身的类型设置
			type = cell.getCellType();
			// 2.试着修改schema提供的类型设置
			//srcType = srcType.copy();
			srcType.reset(TypeUtils.xls2Column(type));
			switch (type)
			{
				case HSSFCell.CELL_TYPE_ERROR :
					value = String.valueOf(cell.getErrorCellValue());
					break;
				case HSSFCell.CELL_TYPE_NUMERIC :
					if (Type.DATE.equals(dstType)
						|| Type.TIME.equals(dstType)
						|| Type.TIMESTAMP.equals(dstType))
					{
						value =
							String.valueOf(
								DateUtils.toDate(cell.getNumericCellValue()).getTime());
					}
					else
					{
						value = String.valueOf(cell.getNumericCellValue());
					}
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN :
					value = String.valueOf(cell.getBooleanCellValue() ? "1" : "0");
					break;
				default :
					value = cell.getStringCellValue();
					break;
			}
		}
		Object xlsObject = formatXlsValue(value, srcType);
		//System.err.println("[Result] column="+cell.getCellNum()+",srcType="+srcType+",dstType="+dstType);   
		//System.err.print("[XlsValue] value="+xlsObject);
		Object stdObject = formatValue(xlsObject, srcType, dstType);
		//System.err.println(",[stdValue] value="+stdObject);
		return stdObject;
	}
	public static Object readCell(
		ResultSet rs,
		int colNum,
		Type srcType,
		Type dstType)
		throws SQLException
	{
		Object value = null;
		if (Type.INTEGER.equals(srcType))
		{
			value = String.valueOf(rs.getInt(colNum));
		}
		if (Type.FLOAT.equals(srcType))
		{
			value = String.valueOf(rs.getFloat(colNum));
		}
		else if (Type.DATE.equals(srcType))
		{
			value = rs.getDate(colNum);
		}
		else if (Type.TIMESTAMP.equals(srcType))
		{
			value = rs.getTimestamp(colNum);
		}
		else
		{
			value = rs.getString(colNum);
		}
		return formatValue(value, srcType, dstType);
	}
	/**
	 * Standard Type<--------->Standard Type
	 * 
	 * public final static Type VARCHAR    = new Type("VARCHAR");//字符串保存
	 * public final static Type INTEGER    = new Type("INTEGER");//字符串保存
	 * public final static Type FLOAT      = new Type("FLOAT");//字符串保存
	 * public final static Type DATE       = new Type("DATE");
	 * public final static Type TIME       = new Type("TIME");
	 * public final static Type TIMESTAMP  = new Type("TIMESTAMP");
	 * 
	 * @see Type
	 */
	public static Object formatValue(Object value, Type srcType, Type dstType)
	{
		if (value == null)
		{
			return value;
		}
		if (srcType.equals(dstType))
		{
			return value;
		}
		boolean formatted = false;
		//--------------------------------------------------
		if (Type.VARCHAR.equals(srcType)) //java.lang.String
		{
			if (Type.INTEGER.equals(dstType) || Type.FLOAT.equals(dstType))
			{
				return value;
			}
			else if (Type.DATE.equals(dstType))
			{
				return DateUtils.toDate(value.toString());
			}
			else if (Type.TIME.equals(dstType))
			{
				return DateUtils.toTime(value.toString());
			}
			else if (Type.TIMESTAMP.equals(dstType))
			{
				return DateUtils.toTimestamp(value.toString());
			}
		}
		if (Type.INTEGER.equals(srcType)) //java.lang.String
		{
			if (Type.VARCHAR.equals(dstType) || Type.FLOAT.equals(dstType))
			{
				return value;
			}
			else if (Type.DATE.equals(dstType))
			{
				return DateUtils.toDate(value.toString());
			}
			else if (Type.TIME.equals(dstType))
			{
				return DateUtils.toTime(value.toString());
			}
			else if (Type.TIMESTAMP.equals(dstType))
			{
				return DateUtils.toTimestamp(value.toString());
			}
		}
		if (Type.FLOAT.equals(srcType)) //java.lang.String
		{
			if (Type.VARCHAR.equals(dstType) || Type.INTEGER.equals(dstType))
			{
				return value;
			}
			else if (Type.DATE.equals(dstType))
			{
				return new java.sql.Date((long) Float.parseFloat(value.toString()));
			}
			else if (Type.TIME.equals(dstType))
			{
				return new java.sql.Time((long) Float.parseFloat(value.toString()));
			}
			else if (Type.TIMESTAMP.equals(dstType))
			{
				return new java.sql.Timestamp(
					(long) Float.parseFloat(value.toString()));
			}
		}
		if (Type.DATE.equals(srcType)) //java.sql.Date
		{
			if (Type.VARCHAR.equals(dstType))
			{
				return DateUtils.toString((java.sql.Date) value);
			}
			else if (Type.INTEGER.equals(dstType))
			{
				java.sql.Date date = (java.sql.Date) value;
				return String.valueOf(date.getTime()); //milliseconds
			}
			else if (Type.FLOAT.equals(dstType))
			{
				java.sql.Date date = (java.sql.Date) value;
				return String.valueOf(date.getTime()); //milliseconds
			}
			else if (Type.TIMESTAMP.equals(dstType))
			{
				return DateUtils.toTimestamp(
					DateUtils.toString((java.sql.Date) value) + " 00:00:00.000");
			}
		}