将.xls文件中数据导入到数据库相应的表中

使用了jxl.jar解析xls文件

xls文件通过smartupload从前台获取

后台处理的方法：

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.kula.toolkits.xls2db.Xls2dbBean;

public class Xls2dbBeanImpl implements Xls2dbBean{
	
	/**
	 * 通过excel向数据库中导入数据
	 * excel中第一行为字段名
	 * 表必须已经存在，并且excel中字段名必须与表中字段名对应
	 * @param in			文件流
	 * @param sheetName		要导入数据所在excel文件中的标签页名
	 * @param tableName		要导入数据库中的表名
	 * @param db			数据源
	 * @return				成功导入记录的条数
	 */
	@Override
	public int importData(InputStream in, String sheetName, String tableName,Connection conn){
		Workbook workbook = null;
		String sql = "INSERT INTO " + tableName + "(";
		PreparedStatement preStatement = null;
		int result = 0;
		try {
			//获取一个excel文件对象
			workbook = Workbook.getWorkbook(in);
			//获取一个sheet对象
			Sheet sheet = workbook.getSheet(sheetName);
			//获取表的行数和列数
			int columnNum = sheet.getColumns();
			int rowsNum = sheet.getRows() - 1;
			//获取表头
			Cell[] colNames = sheet.getRow(0);
			//拼凑sql
			for(int i=0; i<columnNum; i++){
				sql += colNames[i].getContents() + ",";
			}
			sql = sql.substring(0, sql.length()-1) + ") VALUES(";
			for(int i=0; i<columnNum; i++){
				sql += "?,";
			}
			sql = sql.substring(0,sql.length()-1) + ")";
			//采用preparedStatement批量插入
			preStatement = conn.prepareStatement(sql);
			//开启transaction
			conn.setAutoCommit(false);
			for(int i=0; i<rowsNum; i++){
				Cell[] rows = sheet.getRow(i+1);
				for(int j=0; j<columnNum; j++){
					preStatement.setString(j+1, rows[j].getContents());
				}
				result++;
				//将查询语句添加到批处理中
				preStatement.addBatch();
			}
			//执行批处理操作
			preStatement.executeBatch();
			//提交事务
			conn.commit();
			System.out.println(result + "个记录已经被插入数据库！");
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			try {
				System.out.println("=======transaction rollback!=======");
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}  finally{
			if(workbook != null){
				System.out.println("======workbook close!======");
				workbook.close();
			}
			if(preStatement != null){
				try {
					System.out.println("=======stat close!=======");
					preStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
}

Servlet中读取上传文件

		Connection conn = null;
		try {
			conn = XXXXXXX.getConnection();
			// 新建一个SmartUpload对象
			SmartUpload su = new SmartUpload();
			// 上传初始化
			su.initialize(this.getServletConfig(),request,response);
			// 设定允许上传的文件（通过扩展名限制）,仅允许doc,txt文件。
			su.setAllowedFilesList("xls");
			// 上传文件
			su.upload();
			Files files = su.getFiles();
			File file = files.getFile(0);
			//文件转换成字节数组
			byte [] buff = new byte[file.getSize()];
			for(int i=0; i<file.getSize(); i++){
				buff[i] = file.getBinaryData(i);
			}
			//取得输入流
			InputStream in = new ByteArrayInputStream(buff);
			//释放缓存
			buff = null;
			if(in != null){
				Xls2dbBean bean = new Xls2dbBeanImpl();
				bean.importData(in, "Sheet1", "UserInfo", conn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (SmartUploadException e) {
			e.printStackTrace();
		} finally{
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}