创建一个xls文件，并写入内容
1 public void doPost(HttpServletRequest request, HttpServletResponse response) 2 throws ServletException, IOException { 3 4 try{ 5 request.setCharacterEncoding("utf-8"); 6 response.setCharacterEncoding("utf-8"); 7 response.setContentType("text/html"); 8 //1. 获取目标文件路径 9 String targerPath = "d:" + File.separator + "Jxl01.xls"; 10 //2. 根据1获取的路径，创建一个可以写入的工作簿 11 WritableWorkbook workbook = Workbook.createWorkbook(new File(targerPath)); 12 //3. 为2创建的工作簿创建sheet表，名字为‘第一页’，索引从‘0’开始 13 WritableSheet sheet = workbook.createSheet("第一页", 0); 14 //4. 为3表创建Label,例（第一列，第一行，姓名）（第一列，第二行，张三） 15 Label label = new Label(1, 1, "姓名"); 16 Label labe2 = new Label(2, 1, "年龄"); 17 Label labe3 = new Label(3, 1, "职业"); 18 Label labe4 = new Label(1, 2, "张三"); 19 Label labe5 = new Label(2, 2, "18"); 20 Label labe6 = new Label(3, 2, "学生"); 21 //5. 将4.Label对象加入到3表对应的单元格中 22 sheet.addCell(label); 23 sheet.addCell(labe2); 24 sheet.addCell(labe3); 25 sheet.addCell(labe4); 26 sheet.addCell(labe5); 27 sheet.addCell(labe6); 28 //6. 在2工作簿中写入 29 workbook.write(); 30 //7. 关闭工作簿 31 workbook.close(); 32 33 PrintWriter out = response.getWriter(); 34 out.print("插入成功"); 35 } catch(Exception e) { 36 e.printStackTrace(); 37 } 38 39 }
读取xls文件中表中的内容
1 public void doPost(HttpServletRequest request, HttpServletResponse response) 2 throws ServletException, IOException { 3 4 try{ 5 request.setCharacterEncoding("utf-8"); 6 response.setCharacterEncoding("utf-8"); 7 response.setContentType("text/html"); 8 PrintWriter out = response.getWriter(); 9 // 1.获取目标文件路径 10 String targerPath = "d:" + File.separator + "Jxl01.xls"; 11 // 2.获取目标路径的文件 12 Workbook book = Workbook.getWorkbook(new File(targerPath)); 13 // 3.获取目标文件中指定的表（索引从0开始） 14 Sheet sheet = book.getSheet(0); 15 // 4.将表中字段与其对应的内容组合成Jeson格式 16 StringBuffer sb = new StringBuffer(); 17 sb.append("{"); 18 // 行循环 19 for( int i=1; i<sheet.getRows(); i++) { 20 // 列循环 21 for(int j=1; j<sheet.getColumns(); j++) { 22 String key = sheet.getCell(j,1).getContents(); 23 String value = sheet.getCell(j,2).getContents(); 24 sb.append(" ""+key+"" : ""+value+"", "); 25 sb.append(""); 26 } 27 } 28 sb.deleteCharAt(sb.length()-1); 29 sb.append("}"); 30 // 5.关闭工作簿 31 book.close(); 32 out.print(sb.toString()); 33 } catch( Exception e){ 34 e.printStackTrace(); 35 } 36 37 }
页面
1 <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> 2 3 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 4 <html> 5 <head> 6 7 <title>带键值的Excel数据导入导出</title> 8 9 <meta http-equiv="pragma" content="no-cache"> 10 <meta http-equiv="cache-control" content="no-cache"> 11 <meta http-equiv="expires" content="0"> 12 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> 13 <meta http-equiv="description" content="This is my page"> 14 <!-- 15 <link rel="stylesheet" type="text/css" href="styles.css"> 16 --> 17 <script type="text/javascript" src ="${pageContext.request.contextPath }/js/jquery-1.11.1.js"></script> 18 <script type="text/javascript"> 19 var xmlHttpRequest = null; 20 function getXmlHttpRequest(){ 21 if(window.XMLHttpRequest) { 22 xmlHttpRequest = new XMLHttpRequest(); 23 } else { 24 xmlHttpRequest = new ActiceXOject("Microsofit,XMLHTTP"); 25 } 26 } 27 28 function ajaxRequyest1(){ 29 getXmlHttpRequest(); 30 if(xmlHttpRequest) { 31 xmlHttpRequest.open("post","../Jxl01Servlet",true); 32 xmlHttpRequest.onreadystatechange = CallBack1; 33 xmlHttpRequest.setRequestHeader("Content-Type","application/x-www-form-urlencoded"); 34 xmlHttpRequest.send(); 35 } 36 } 37 38 function CallBack1() { 39 if(xmlHttpRequest.readyState == 4 && xmlHttpRequest.status == 200 ){ 40 var result = xmlHttpRequest.responseText; 41 alert(result); 42 } 43 } 44 45 /******************************************************************************************/ 46 47 function ajaxRequyest2(){ 48 getXmlHttpRequest(); 49 if(xmlHttpRequest) { 50 xmlHttpRequest.open("post","../OJxl01Servlet",true); 51 xmlHttpRequest.onreadystatechange = CallBack2; 52 xmlHttpRequest.setRequestHeader("Content-Type","application/x-www-form-urlencoded"); 53 xmlHttpRequest.send(); 54 } 55 } 56 57 function CallBack2() { 58 if(xmlHttpRequest.readyState == 4 && xmlHttpRequest.status == 200 ){ 59 var result = xmlHttpRequest.responseText; 60 alert(result); 61 } 62 } 63 64 $(function(){ 65 $("#InputEmpbtn").click(function(){ 66 ajaxRequyest1(); 67 }); 68 $("#ExputEmpbtn").click(function(){ 69 ajaxRequyest2(); 70 }); 71 }) 72 </script> 73 </head> 74 75 <body> 76 <input id="InputEmpbtn" type="button" value="员工导入" /> 77 <input id="ExputEmpbtn" type="button" value="员工导出" /> 78 </body> 79 </html>