flex 上传excel 导入数据库

flex 上传excel 导入数据库

前端用flex上传excel，因为此例子用的是flex3， 所以通过 servlet 进行处理（如果是flex4的话 可以用remoteobject处理），servlet 可以利用jxl 处理excel，并报存到数据库。

----------》前端代码

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
	layout="vertical" verticalAlign="middle" horizontalAlign="center">

	<mx:Style>
		global {
			fontSize : 12;
		}
	</mx:Style>

	<mx:Script>
		<![CDATA[

			private var file:FileReference = new FileReference();

			// 上传状态指示, 和下面的文本框绑定
			[Bindable]
			private var stateText:String = "请选择一个文件上传";

			// createChildren 比 creationComplete 事件更早发生, 省的注册事件侦听, 直接在这里写了
			protected override function createChildren():void {
				super.createChildren();
				file.addEventListener(Event.SELECT, file_select);
				file.addEventListener(Event.COMPLETE, file_complete);
				file.addEventListener(ProgressEvent.PROGRESS, file_progress);
			}

			// 选择 1 个文件的事件
			private function file_select (e:Event):void {
				stateText = "选择了文件 " + file.name;
			}

			// 上传完毕后的事件
			private function file_complete (e:Event):void {
				stateText = "上传完毕";
			}

			private function file_progress (e:ProgressEvent):void {
				stateText = "已上传 " + Math.round(100 * e.bytesLoaded / e.bytesTotal) + "%";
			}
			// 先判断一下文件大小, 再上传, FileService.aspx 就是上传地址
			private function upload ():void {
				if (file.size > 0) {
					stateText = "正在上传 " + file.name;
					var request:URLRequest = new URLRequest("http://localhost:8080/jxlDemo/uploadFile");
					file.upload(request);
				}
			}


		]]>
	</mx:Script>

	<mx:Panel width="250" height="112" layout="vertical" title="上传示例"
		verticalAlign="middle" horizontalAlign="center" >
		<mx:HBox>
			<mx:TextInput text="{stateText}" width="160" editable="false"/>
			<mx:Button label="浏览" click="file.browse();"/>
		</mx:HBox>
		<mx:HBox>
			<mx:Button label="上传" click="upload();"/>
		</mx:HBox>
	</mx:Panel>
</mx:Application>

----------》servlet代码

package com.xx;

import java.io.IOException;
import java.io.InputStream;
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 javax.servlet.ServletException;
import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class UploadFileServlet extends HttpServlet {

	private String createTableSql = "";// 创建数据库的sql
	private String colType = "varchar2(255)";// 字段类型
	private String key = "id";// 主键
	private String charSet = "utf8";// 表格字符类型
	private String ENGINE = "InnoDB";// 表格类型
	private String tableName = "tempExcelToH2";// 表名称
	private String colName = "col";// 默认字段名
	private Connection conn = null;

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		doPost(req, resp);
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		ServletInputStream is = req.getInputStream();

//		int i = is.read();
//		while(i != -1){
//			System.out.print((char)i);
//			i = is.read();
//		}
        /*过滤HTTPHeader,否则jxl会报无法识别文件结尾的错误*/  
		byte[] junk = new   byte[1024];   

        for(int i = 0; i < 8; i++)   
        {   
        	 is.readLine(junk, 0,junk.length);   
        }  


		excelToDB(is);
	}

	private void excelToDB(InputStream is){
		try {
			jxl.Workbook rwb = Workbook.getWorkbook(is);
			Sheet rs = rwb.getSheet(0); // 读取第一个sheet
			int colNum = rs.getColumns();// 列数
			int rowNum = rs.getRows();// 行数

			System.out.println("colNum rowNum------------------" + rowNum + ","
					+ colNum);
			System.out.println("start create ｂａｓｅ-------------------------");

			getConntion();

			String tableSql = getCreateTableSql(rowNum, colNum);
			Statement st = conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			st.execute(tableSql);
			st.close();

			System.out.println("create ｂａｓｅ end -------------------------");

			String sql = getColName(rowNum, colNum);
			PreparedStatement ps = null;
			String strValue = "";
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < rowNum; i++) {
				strValue = "";
				for (int j = 0; j < colNum; j++) {
					Cell c = rs.getCell(j, i);
					strValue = c.getContents();
					ps.setString(j + 1, strValue);
				}
				ps.addBatch();
			}

			ps.executeBatch();
			conn.commit();

			if (ps != null) {
				ps.close();
			}

			System.out.println(" insert end-------------------------");
			close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private String getCreateTableSql(int rowNum, int colNum) {
		// 可以做成可配置文件

		createTableSql = "create table " + tableName + "( `" + key
				+ "` bigint generated by default as identity, ";
		String temp = "";

		for (int j = 0; j < colNum; j++) {
			temp = temp + "`" + colName + j + "` " + colType + " DEFAULT NULL,";
		}

		createTableSql = createTableSql + " " + temp + " PRIMARY KEY (`" + key
				+ "`)" + ");";

		return createTableSql;
	}

	private String getColName(int rowNum, int colNum) {
		// 可以做成可配置文件
		String colSql = "";
		String colValue = "";

		for (int j = 0; j < colNum; j++) {
			colSql = colSql + "`" + colName + j + "`,";
			colValue = colValue + "" + "?,";

		}

		return "insert into " + tableName + " ("
				+ colSql.substring(0, colSql.lastIndexOf(",")) + ")values("
				+ colValue.substring(0, colValue.lastIndexOf(",")) + ")";
	}

	private void getConntion() {

		try {
			String driver_class = "org.h2.Driver";
			String connection_url = "jdbc:h2:tcp://localhost/jxltest";
			String user_name = "sa";
			String db_password = "";

			Class.forName(driver_class);
			conn = DriverManager.getConnection(connection_url, user_name,
					db_password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void close() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}	
}

其中就是jxl处理流时，必须先过滤http header，看到了个解决方案（http://blog.youkuaiyun.com/sdrzths/archive/2010/04/09/5467876.aspx）还是报jxl.read.biff.BiffException: Unable to recognize OLE stream 的错，其实解决是打印上传的excel ，发现结果是
------------ae0cH2Ef1Ij5Ij5ae0ei4ei4Ef1cH2
Content-Disposition: form-data; name="Filename"

users.xls
------------ae0cH2Ef1Ij5Ij5ae0ei4ei4Ef1cH2
Content-Disposition: form-data; name="Filedata"; filename="users.xls"
Content-Type: application/octet-stream

??à?±á

所以http 头应该是8行，得以解决。

感谢下面博主
Flex 利用 Blazeds上传文件 http://blog.youkuaiyun.com/chuangxin/archive/2010/09/13/5881758.aspx
如何在jsp中读取客户端的excel文件中的数据 http://blog.youkuaiyun.com/sdrzths/archive/2010/04/09/5467876.aspx
JXL读Excel文件到数据库
http://hi.baidu.com/liupeng_cn/blog/item/74140eef714e1cfcb2fb95cb.html