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.