JXL将excel的数据导入到数据库

excel数据如下：

数据库的表格如下：

读取Excel的数据，插入到数据库中，访问的方法是insert：

public class TestExcel extends BaseAction implements ModelDriven<Equipment>{

	private Equipment equipment;
	public String insert(){
         try{
        	 Connection connection = con();
        	 String sql = "insert into excel(name,age,date) values(?,?,?)";
        	 PreparedStatement  ps = pre(sql, connection);
        		File file = equipment.getFile();
    			GenerateSqlFromExcel generater = new GenerateSqlFromExcel();
    			ArrayList<String[]> arrayList = generater.generateStationBugSql(file);   
    			for(int i = 0;i < arrayList.size();i++){
    				String str[] = arrayList.get(i);
    				ps.setString(1, str[0]);
    				ps.setInt(2,Integer.valueOf(str[1]));
    				ps.setTimestamp(3,Timestamp.valueOf(str[2]));
    				ps.executeUpdate();
    			}
         }catch (Exception e) {
			// TODO: handle exception
        	 e.printStackTrace();
		}
		return null;
	}
	/**
	 * @param args
	 * @throws Exception 
	 */
	public  String excel()  {
		try{
		Connection connection = con();
        String sql = "select * from equipment";
        PreparedStatement  ps = pre(sql, connection);
       ResultSet rs =  ps.executeQuery();
        String [] string = {"设备编号","学院名称","入库时间","实验室位置","实验箱名称","使用状态"};
       ArrayList listname = new ArrayList();
        for(int i = 0;i< string.length;i++){
        	listname.add(string[i]);
        }
       // ArrayList  listname = (ArrayList) Arrays.asList(string);
 	   ArrayList listData = new ArrayList();
       while(rs.next()){
    	   ArrayList list = new ArrayList();
    	   list.add(rs.getString(1));
    	   list.add(rs.getString(2));
    	   list.add(rs.getTimestamp(3));
    	   list.add(rs.getString(4));
    	   list.add(rs.getString(5));
    	   list.add(rs.getString(6));
    	   listData.add(list);
       }
       OutputStream out = response.getOutputStream();
		//重置输出流
		response.reset();
		//设置导出Excel报表的导出形式
		response.setContentType("application/vnd.ms-excel");
		//设置下载的excel文件名称
		response.setHeader("Content-Disposition", "attachment;filename=zfc.xls");  
		 ExcelFileGenerator ex = new ExcelFileGenerator(listname,listData);
		ex.expordExcel(out);
		//设置输出形式
		System.setOut(new PrintStream(out));
		//刷新输出流
		out.flush();
		//关闭输出流
		if(out!=null){
			out.close();
		}
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}
	public static Connection con() throws Exception{
		Connection connection = null;
		Class.forName("com.mysql.jdbc.Driver");
		connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ems", "root", "123456");
		return connection;
	}
	
	public static PreparedStatement pre(String sql,Connection connection) throws Exception{
		PreparedStatement ps = null;
		ps = connection.prepareStatement(sql);
		return ps;
	}
	@Override
	public Equipment getModel() {
		if(equipment == null){
			equipment = new Equipment();
		}
		return equipment;
	}

}

操作excel的步骤如下：

1、获取文件输入流，将上传的文件转换成流
*  2、传入流对象，创建workbook对象
*  3、通过workbook对象获得多少页sheet,进行循环读取
*       3.1、通过sheet对象，获得sheet[i].getRows行数，进行循环
*               3.1.1、在行循环中建立数组，将每行的数据存入数组中
*                          3.1.1.1、获取列sheet[i].getColumns循环体，进行循环
*                                        3.1.1.1.1、获得列对象sheet[i].getCell();
*                                        3.1.1.1.2、通列对象获取值，对值的类型进行判断，如果是时间的话要减去八个小时
*        3.2、将数组中的数据加入到list集合中  

代码如下：

public class GenerateSqlFromExcel {

	
	/**1、获取文件输入流，将上传的文件转换成流
	 *  2、传入流对象，创建workbook对象
	 *  3、通过workbook对象获得多少页sheet,进行循环读取
	 *       3.1、通过sheet对象，获得sheet[i].getRows行数，进行循环
	 *               3.1.1、在行循环中建立数组，将每行的数据存入数组中
	 *                          3.1.1.1、获取列sheet[i].getColumns循环体，进行循环
	 *                                        3.1.1.1.1、获得列对象sheet[i].getCell();
	 *                                        3.1.1.1.2、通列对象获取值，对值的类型进行判断，如果是时间的话要减去八个小时
	 *        3.2、将数组中的数据加入到list集合中  
	 * 
	 * 导入报表Excel数据，生成用户表的数据库导入语句
	 * @param formFile
	 * @return list ArrayList
	 * @throws Exception
	 */
	public static ArrayList generateStationBugSql(File formFile)
			throws Exception {
		InputStream in = null;
		Workbook wb = null;
		ArrayList list = new ArrayList();
		try {
			if (formFile == null) {
				throw new Exception("文件为空！");
			}
			in = new FileInputStream(formFile);
			wb = Workbook.getWorkbook(in);
			
			Sheet sheet[] = wb.getSheets();
			if (sheet != null) {
				for (int i = 0; i < sheet.length; i++) {
					if (!sheet[i].getName().equalsIgnoreCase("User")) {						
						throw new Exception("指定文件中不包含名称为User的sheet,请重新指定！");
					}
					for (int j = 1; j < sheet[i].getRows(); j++) {
						String[] valStr = new String[8];
						for (int k = 0; k < sheet[i].getColumns(); k++) {
							Cell cell = sheet[i].getCell(k, j);
							String content = "";
							if (cell.getType() == CellType.DATE) {
								DateCell dateCell = (DateCell) cell;
								java.util.Date importdate = dateCell.getDate();/**如果excel是日期格式的话需要减去8小时*/
								long eighthour = 8*60*60*1000;
								SimpleDateFormat simpledate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
								/**在当前日期上减8小时*/
								long str = importdate.getTime();
								long time = importdate.getTime()-eighthour; 
								java.util.Date date = new java.util.Date();
								date.setTime(time);
								content = simpledate.format(date); 
							} else {
								String tempcontent = (cell.getContents() == null ? ""
										: cell.getContents());
								content = tempcontent.trim().replace('\'', ' ');
							}
							valStr[k] = content;
						} 
						list.add(j-1,valStr);
					}
				}
			}
			
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (wb != null) {
				wb.close();
			}
			if (in != null) {
				try {
					in.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

excle.jsp 代码：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'excel.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
    This is my JSP page. <br>
    <form action="system/testexcel_insert.do" method="POST" enctype="multipart/form-data">
    <input type="file" name="file"/>
    <input type="submit" value="导入Excel">
    </form>
  </body>
</html>

struts.xml的配置文件：