java操作Excel示例

示例代码：

package com.is.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.is.form.MoneyManageXlsVO;
import com.is.util.MoneyConstants;

/*
 * operate account item data in excel
 */
public class MoneyManageXlsDAO {

	/**
	 * insert a record with data from a list
	 *
	 * @param list
	 * @return
	 */
	public int insertDataBase(ArrayList<Object> list){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int iAccountId = 0;
		int iResult = 0;

		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String strWebPath = MoneyConstants.getXlsPath();
			String dburl = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=" + strWebPath +";ReadOnly=0;";
			conn = DriverManager.getConnection(dburl,"","");

			String vsql = "select max(cint(account_id))+1 from [account_manage$]";
			pstmt = (PreparedStatement)conn.prepareStatement(vsql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			iAccountId = rs.getInt(1);
			if(iAccountId == 0){
				iAccountId = 1;
			}
			rs.close();
			pstmt.close();
			vsql = "insert into [account_manage$] ( ";
			vsql += "account_id,account_name,account_type,account_parent_id";
			vsql += ") values(?,?,?,?)";

			pstmt = (PreparedStatement)conn.prepareStatement(vsql);
			pstmt.setString(1, String.valueOf(iAccountId));
			pstmt.setString(2, list.get(0).toString());
			pstmt.setString(3, list.get(1).toString());
			pstmt.setString(4, list.get(2).toString());

			iResult = pstmt.executeUpdate();
			conn.close();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (conn != null && (conn.isClosed())) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(iResult == 0){
			return 0;
		}else{
			return iAccountId;
		}
	}

	/**
	 * query records by conditions
	 *
	 * @param strSQL
	 * @return
	 */
	public List<MoneyManageXlsVO> queryAccountList(String strSQL) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet result = null;
		List<MoneyManageXlsVO> listMoneyManageVO = new ArrayList<MoneyManageXlsVO>();
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String strWebPath = MoneyConstants.getXlsPath();
			String dburl = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=D:\\moneyrecord.xls";
			conn = DriverManager.getConnection(dburl,"","");
			stmt = conn.createStatement();
			result = stmt.executeQuery(strSQL);
			while(result.next()){
				listMoneyManageVO.add(this.receiveVO(result));
			}
			result.close();
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (conn != null && (conn.isClosed())) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listMoneyManageVO;
	}

	/**
	 * execute update sql
	 *
	 * @param strSQL
	 * @return influenced rows count
	 */
	public int operateDataBase(String strSQL){
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String strWebPath = MoneyConstants.getXlsPath();
			String dburl = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=" + strWebPath +";ReadOnly=0;";
			Connection conn = DriverManager.getConnection(dburl,"","");
			Statement stmt = conn.createStatement();
			int iResult = stmt.executeUpdate(strSQL);
			stmt.close();
			conn.close();
			return iResult;
		} catch(ClassNotFoundException ex){
			ex.printStackTrace();
			return 0;
		} catch(SQLException ex){
			ex.printStackTrace();
			return 0;
		}
	}

	private MoneyManageXlsVO receiveVO(ResultSet rs) throws SQLException {
		MoneyManageXlsVO objMoneyManageVO = new MoneyManageXlsVO();
		objMoneyManageVO.setAccountId(rs.getString("ACCOUNT_ID"));
		objMoneyManageVO.setAccountName(rs.getString("ACCOUNT_NAME"));
		objMoneyManageVO.setAccountType(rs.getString("ACCOUNT_TYPE"));
		objMoneyManageVO.setAccountParentId(rs.getString("ACCOUNT_PARENT_ID"));
		return objMoneyManageVO;
	}

}

注：我使用的是1997-2003版Excel文件，测试通过；

对xls进行修改操作时，需在连接符中设置ReadOnly为0，详细原因可见mocrosoft帮助文档：http://smallbusiness.support.microsoft.com/zh-cn/kb/316475，若不加该参数会报错： [Microsoft] [ODBC Excel 驱动程序] 操作必须使用一个可更新的查询；

odbc操作excle不支持delete命令，详细原因可见mocrosoft帮助文档介绍：http://office.microsoft.com/zh-cn/help/HA001173951.aspx（在文档末尾部分，建议在页面搜关键字ISAM），也可参考另一文章：http://blog.csdn.net/tennychen/article/details/6332042；