（一）文件的上传：在这一部分，我要将execl文件的内容上传到数据库中，完成这一功能。需要分为两步：
1：将文件上传到tomcat下
文件格式如下：
2：读取execl表中的内容到数据库中
首先：下载jar包：
commons-fileupload-1.3.2.jar
commons-io-2.5.jar
前台html和js:
1 <input type="hidden" readonly name="src" id="fsrc" value="0" size="60"/> 2 <input type='text' readonly name='textfield' id='textfield' class='txt' style="height:30px;30%;"/> 3 <input type='button' class='btn' onClick="findFile()" value='浏览...' style="height:30px;background-color:#ff9900;outline:none;border:none;10%;" /> 4 <input type="file" name="file" class="file" id="file" size="28" style="display:none;" onchange="document.getElementById('textfield').value=this.value" /> 5 <input type="button" class="btn2" value="上传" onclick="upload()"/> 6 <a class="btn btn-success" href="lmxx.do?downLoad&fileName='栏目列表.xls'">下载</a>
1 function upload(){ 2 3 var file="file"; 4 var picElement=document.getElementById(file).value; 5 6 var picFormat=picElement.substring(picElement.lastIndexOf('.')+1,picElement.length); 7 if(picFormat!="xls"&&picFormat!="xlsx"){ 8 alert("请选择 excel 2003 (*.xls) 格式的文件!"); 9 return; 10 } 11 12 $.ajaxFileUpload({ 13 url:"lmxx.do?upLoadExcel", 14 secureuri: false, 15 fileElementId:file, 16 dataType: 'JSON', 17 success:function(data){ 18 data=decodeURIComponent(data);//解码 19 var arr=JSON.parse(data);//JSON.parse() 方法解析一个JSON字符串 20 $("#fsrc").val(arr.rPath); 21 $.post( 22 "lmxx.do?excelToDb&src="+$("#fsrc").val(), 23 function(data){ 24 //data=decodeURIComponent(data); 25 //var arr=JSON.parse(data); 26 if(data==1){ 27 alert("上传成功！"); 28 }else{ 29 alert("上传失败！"); 30 } 31 32 } 33 ); 34 }, 35 error: function (){ 36 alert("上传失败!当前时间为:"+new Date().toLocaleTimeString()); 37 38 } 39 }); 40 }; 41 42 function findFile(){ 43 document.getElementById("file").click(); 44 }
后台操作方法：
1 @RequestMapping(params="upLoadExcel") 2 @ResponseBody 3 public String addFilemsgPic(MultipartFile file,HttpServletRequest request) throws Exception{ 4 String path="/uploadExcelFiles"; 5 path=request.getSession().getServletContext().getRealPath(path);//当前运行文件在服务器上的绝对路径 6 System.out.println(path); 7 //创建文件 8 File dir=new File(path); 9 if(!dir.exists()){ 10 dir.mkdirs(); 11 } 12 String uuid=UUID.randomUUID().toString(); 13 //返回原来在客户端的文件系统的文件名 14 String fileName=file.getOriginalFilename(); 15 //uuid+后缀名 16 String img=uuid+fileName.substring(fileName.lastIndexOf("."));//lastIndexOf报告指定 Unicode 字符在此实例中的最后一个匹配项的索引位置（即为后缀）。String.Substring (Int32)从此实例检索子字符串 17 FileOutputStream imgOut=new FileOutputStream(new File(dir,img));//根据 dir 抽象路径名和 img 路径名字符串创建一个新 File 实例。 18 imgOut.write(file.getBytes());//返回一个字节数组文件的内容 19 imgOut.close(); 20 Map<String, String> map=new HashMap<String, String>(); 21 22 map.put("picPath",img);//8b4aff41-420c-419a-b0ab-c18ca36198c6.xls 23 map.put("picName",fileName);//lanmu.xls 24 String rpath = path+"\"+img; 25 26 map.put("rPath",rpath); 27 JSONObject jsonObject = JSONObject.fromObject(map);//将json字符串转换为json对象 28 String r=jsonObject.toString(); 29 String s=URLEncoder.encode(r, "utf-8");//加密 30 return s; 31 } 32 33 34 @RequestMapping(params="excelToDb") 35 @ResponseBody 36 public int excelToDB(String src){ 37 38 39 String newSrc = src.replace(" ", "+"); 40 List<Lanmu> lmList=ReadeExcel.taPCL(newSrc); 41 int flag=ConnOrcl.connToTable(lmList); 42 if(flag==1) 43 return 1; 44 else 45 return 2; 46 }
ReadeExcel
1 public class ReadeExcel { 2 public static List<Lanmu> taPCL(String file) 3 { 4 List<Lanmu> LTPC = new ArrayList<Lanmu>(); 5 try 6 { 7 //选取excel文件 8 Workbook rwb=Workbook.getWorkbook(new File(file));//获得工作薄（Workbook） 9 //选取excel文件中的第一个工作薄 10 Sheet sheet = rwb.getSheet(0); 11 //得到excel表行数 12 int RowNum = sheet.getRows(); 13 for(int i=1;i<RowNum;i++) 14 { 15 //获得excel表中第1列第i行单元格 16 Cell cell0 = sheet.getCell(0, i); 17 //获得excel表中第2列第i行单元格 18 Cell cell1 = sheet.getCell(1, i); 19 //获得cell0单元格内容 20 String name = cell0.getContents(); 21 //获得cell1单元格内容 22 String state = cell1.getContents(); 23 Lanmu lanmu = new Lanmu(); 24 lanmu.setName(name); 25 lanmu.setState(state); 26 LTPC.add(lanmu); 27 } 28 } 29 catch(Exception e) 30 { 31 e.printStackTrace(); 32 } 33 return LTPC; 34 } 35 }
ConnOrcl
1 public class ConnOrcl { 2 3 private static Connection conn; 4 private static Statement stat; 5 private static String driver = "oracle.jdbc.driver.OracleDriver"; 6 private static int SUCCESS=1; 7 private static int FILE=2; 8 public static int connToTable(List<Lanmu> lanmus) 9 { 10 try 11 { 12 //数据库连接 13 Class.forName(driver); 14 conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "GKBMFZXT", "GKBMFZXT"); 15 stat = conn.createStatement(); 16 int flag=0; 17 //遍历list，插入数据库 18 for(Lanmu lanmu : lanmus) 19 { 20 StringBuffer sql = new StringBuffer(); 21 //构造sql语句 22 sql.append("insert into lanmu_table (id,name,state) values ('"); 23 String id=UUID.randomUUID().toString().replace("-",""); 24 String name = lanmu.getName(); 25 String state = lanmu.getState(); 26 sql.append(id).append("','"); 27 sql.append(name).append("','"); 28 sql.append(state).append("')"); 29 //执行sql语句 30 stat.execute(sql.toString()); 31 flag++; 32 } 33 34 if(flag==lanmus.size()){ 35 return SUCCESS; 36 } 37 } 38 catch(ClassNotFoundException e) 39 { 40 e.printStackTrace(); 41 } 42 catch(SQLException e) 43 { 44 e.printStackTrace(); 45 } 46 finally 47 { 48 if(null != stat) 49 { 50 try 51 { 52 stat.close(); 53 } 54 catch (SQLException e) 55 { 56 e.printStackTrace(); 57 } 58 } 59 if(null != conn) 60 { 61 try 62 { 63 conn.close(); 64 } 65 catch (SQLException e) 66 { 67 e.printStackTrace(); 68 } 69 } 70 } 71 return FILE; 72 } 73 74 }
（二）文件的下载
1 @RequestMapping(params="downLoad") 2 public String downLoad(HttpServletRequest request,HttpServletResponse response)throws Exception{ 3 4 5 String fileName=request.getParameter("fileName"); 6 File f=new File("C:\Users\ztl\Desktop\lanmu.xls"); 7 8 BufferedInputStream buffer=null; 9 OutputStream out=null; 10 try{ 11 if(!f.exists()){ 12 13 response.sendError(404,"File not find!"); 14 return "File not find!"; 15 16 } 17 buffer = new BufferedInputStream(new FileInputStream(f)); 18 byte[] buf = new byte[1024]; 19 int len=0; 20 response.reset(); 21 response.setContentType("application/vnd.ms-excel"); 22 response.setHeader("Content-Disposition","attachment;filename="+fileName); 23 out = response.getOutputStream(); 24 while((len = buffer.read(buf))>0){ 25 out.write(buf,0,len); 26 } 27 out.flush(); 28 response.flushBuffer(); 29 30 }catch(Throwable e){ 31 e.printStackTrace(); 32 }finally{ 33 34 if(out!=null){ 35 out.close(); 36 } 37 } 38 39 return "view/luntan/lanmu_list"; 40 41 } 42