导入导出Excel文件的方法

jsp页面(table)：

<table id="table">
	<thead>
		<tr>
			<td align="center" nowrap="nowrap">
				流水号
			</td>

			<td align="center" nowrap="nowrap">
				<span style="font-size: 10pt">姓名</span>
			</td>
			<td align="center" nowrap="nowrap">
				<span style="font-size: 10pt">电话</span>
			</td>
			<td align="center" nowrap="nowrap">
				<span style="font-size: 10pt">手机</span>
			</td>
			<td align="center" nowrap="nowrap">
				<span style="font-size: 10pt">宅电</span>
			</td>
			<td align="center" nowrap="nowrap">
				<span style="font-size: 10pt">邮箱</span>
			</td>
			<td align="center" nowrap="nowrap">
				<span style="font-size: 10pt">联系组</span>
			</td>
			<td align="center" nowrap="nowrap">
				操作
			</td>
		</tr>
	</thead>
	<tbody>
		<%
			int i = 1;
		%>
		<logic:present name="stafflist">
			<logic:iterate id="staff" name="stafflist">
				<tr>
					<td align="center" nowrap="nowrap" style="height: 28px">
						<%=i++%></td>

					<td align="center" nowrap="nowrap" style="height: 28px">
						<bean:write name="staff" property="name" />
					</td>
					<td align="center" nowrap="nowrap" style="height: 28px">
						<bean:write name="staff" property="phone" />
					</td>
					<td align="center" nowrap="nowrap" style="height: 28px">
						<bean:write name="staff" property="tel" />
					</td>
					<td align="center" nowrap="nowrap" style="height: 28px">
						<bean:write name="staff" property="homephone" />
					</td>
					<td align="center" nowrap="nowrap" style="height: 28px">
						<bean:write name="staff" property="email" />
					</td>
					<td align="center" nowrap="nowrap" style="height: 28px">
						<bean:write name="staff" property="typecode" />
					</td>
				</tr>
			</logic:iterate>
		</logic:present>
	</tbody>
</table>

 导入导出事件：

<td align="center" nowrap="nowrap">
	<input type="file" id="importfile" name="importfile"
		onchange="importbook()"
		style="position: absolute; filter: alpha(opacity = 0); width: 80px; ime-mode: disabled;"
		onkeydown="return false" />
	<input type="button" value="导入联系人" style="width: 70px;">
</td>
<td align="center" nowrap="nowrap">
	<input type="button" value="导出联系人" onClick="exportbook()"
		style="width: 70px;">
</td>

 

function exportbook(){
	try  
	{
		var table=document.getElementById("table");
		var oXL = new ActiveXObject("Excel.Application");
		var oWB=oXL.Workbooks.Add();
		var oSheet=oWB.ActiveSheet;
		for (i=0; i < table.rows.length; i++)
	    {
		    for (j=0; j < table.rows(i).cells.length-2; j++)
		    {
		     oSheet.Cells(i+1,j+1).Value = table.rows(i).cells(j+1).innerText;
		    }
		    oXL.Visible = true;
		    oXL.UserControl = true;
	    }

	}     
	catch(e)   
	{   
	alert("您的电脑没有安装Microsoft Excel软件！")   
	return false  
	}   
}

  

function importbook(){
	
	var filePath = escape(encodeURIComponent($('importfile').value));
	var pattern=/^.+(\.xls[a-z]?)$|^.+(\.XLS[A-Z]?)$/;
	if(pattern.test(filePath)){
		var url = '/env2.0/importBookAction.do';
		var pars = 'method=importBook&filePath='+filePath;
		var ajax = new Ajax.Request(url,
	   		{method:'post',parameters:pars,onComplete:showcomplex}); 
	}else{
		alert("请选择excel文件！");
	}
}
function showcomplex(dataResponse){
	var data = eval('(' + dataResponse.responseText + ')');
	var issave = data.issave;
	if(issave){
		alert("导入成功");
	} else {
		alert("导入失败");
	}
	//页面刷新
	window.location.href="treelinktypeAction.do?method=findlinktype";
}

 importBookAction中的方法：

public ActionForward importBook(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		String filePath = StringUtils.decodeStringByUTF8(request
				.getParameter("filePath"));
		System.out.println(filePath);
		// // 构造 HSSFWorkbook 对象，filePath 传入文件路径
		// HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));
		// // 读取文件中的第一张表格
		// HSSFSheet sheet = wb.getSheetAt(0);
		// // 定义 row、cell
		// HSSFRow row;
		// // HSSFCell cell;
		// HSSFCell cell;
		// // 循环输出表格中的内容
		// System.out.println("+++" + sheet.getFirstRowNum()
		// +"+++"+sheet.getPhysicalNumberOfRows());
		// for (int i = sheet.getFirstRowNum() + 1; i <
		// sheet.getPhysicalNumberOfRows(); i++) {
		// row = sheet.getRow(i);
		// for (short j = row.getFirstCellNum(); j <
		// row.getPhysicalNumberOfCells(); j++) {
		// // 推荐通过 row.getCell(j).toString() 获取单元格内容，
		// cell = row.getCell(j);
		// if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
		// System.out.println((int) cell.getNumericCellValue());
		// } else if (cell.getCellType() == cell.CELL_TYPE_STRING) {
		// System.out.println(cell.getStringCellValue());
		// }
		// }
		// }

		List l = new ArrayList();
		Workbook book = Workbook.getWorkbook(new File(filePath));
		// 获得第一个工作表对象
		for (int i = 0; i < book.getNumberOfSheets(); i++) {
			Sheet sheet = book.getSheet(i);
			for (int j = 1; j < sheet.getRows(); j++) {
				StringBuffer sb = new StringBuffer("");
				for (int k = 0; k < sheet.getColumns(); k++) {
					Cell cell = sheet.getCell(k, j);
					String result = cell.getContents();
					sb.append(result + ",");
				}
				l.add(sb.toString());
			}
		}
		
		List<Temailbook> booklist = new ArrayList<Temailbook>();
		Integer no = temailbookDao.findbookAllNo();
		Tusers user = (Tusers) request.getSession().getAttribute("user");
		Temailbook temailbook = null;
		System.out.print(l.size());
		for (int i = 0; i < l.size(); i++) {
			String s = (String) l.get(i);
			String[] ss = s.split(",");
			String typecode = tlinktypeDao.findcodeByname(ss[5]);
			
			temailbook = new Temailbook();
			temailbook.setCode(no.toString());
			temailbook.setNo(Long.valueOf(no));
			temailbook.setStaffcode(user.getCode());
			temailbook.setName(ss[0]);
			temailbook.setTypecode(typecode);
			temailbook.setPhone(ss[1]);
			temailbook.setTel(ss[2]);
			temailbook.setHomephone(ss[3]);
			temailbook.setEmail(ss[4]);
			temailbook.setMemo(null);
			temailbook.setIsdel(Long.valueOf(SingleObjectCollect.isdel_new));
			
			booklist.add(temailbook);
			no++;
		}
		
		boolean issave = temailbookDao.savebook(booklist);
		response.setContentType("text/html; charset=GBK");
		try {
			PrintWriter out = response.getWriter();
			JSONObject obj = new JSONObject();
			obj.put("issave", issave);
			out.print(obj.toString());
		} catch (Exception e) {
			e.printStackTrace();
			System.out.print("异常");
		}
		return null;
	}