（一）文件的上传：在这一部分，我要将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;width:30%;"/>
3 <input type='button' class='btn' onClick="findFile()" value='浏览...' style="height:30px;background-color:#ff9900;outline:none;border:none;width: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