前段時(shí)間做一個(gè)小項(xiàng)目，為了同時(shí)存儲(chǔ)多條數(shù)據(jù)，其中有一個(gè)功能是解析Excel并把其中的數(shù)據(jù)存入對(duì)應(yīng)數(shù)據(jù)庫(kù)中。花了兩天時(shí)間，不過(guò)一天多是因?yàn)橛昧?quot;upload"關(guān)鍵字作為URL從而導(dǎo)致總報(bào)同一個(gè)錯(cuò)，最后在同學(xué)的幫助下順利解決，下面我把自己用"POI"解析的方法總結(jié)出來(lái)供大家參考（我用的是SpingMVC和hibernate框架）。
1.web.xml中的配置文件
web.xml中的配置文件就按照這種方式寫，只需要把"application.xml"換成你的配置文件名即可
|
1
2
3
4
5
6
7
8
|
<!--文件上傳對(duì)應(yīng)的配置文件--> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:application.xml</param-value> </context-param> |
2.application.xml的配置文件（固定寫發(fā)）
在這個(gè)配置文件中你還可以規(guī)定上傳文件的格式以及大小等多種屬性限制
|
1
2
3
4
|
<!-- 定義文件上傳解析器 --> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> </bean> |
3.文件上傳的前端HTML
注意：
1.enctype="multipart/form-data" 必須寫，封裝表單
2.method="post"，提交方式必須為"post"提交
3.action="${text}/uploadfile"， "uploadfile"切記不要寫成"upload"，否則你找到世界末日也不會(huì)找到哪里有問(wèn)題（本人因?yàn)檫@個(gè)折騰了一天多時(shí)間）。
|
1
2
3
4
5
6
7
8
|
<form name="fileupload" enctype="multipart/form-data" action="${text}/uploadfile" method="post"><p style="font-size:16px;">請(qǐng)選擇正確的excel文件上傳</p><input id="txt" class="input" type="text" disabled="disabled" value="文件域" name="txt"> <input class="liulan" type="button" onclick="file.click()" size="30" value="上傳文件" onmousemove="file.style.pixelLeft=event.x-60;file.style.pixelTop=this.offsetTop;"><input id="file1" class="files" type="file" hidefocus="" size="1" style="height:26px;" name="file" onchange="txt.value=this.value"><br/><input type="button" onclick="checkSuffix();" value="提交上傳" style="height:26px;width:100px"> <p style="color:red;">支持的excel格式為：xls、xlsx、xlsb、xlsm、xlst！</p></form> |
4.驗(yàn)證上傳文件的格式
|
1
2
3
4
5
6
7
8
9
10
11
12
|
//用于驗(yàn)證文件擴(kuò)展名的正則表達(dá)式function checkSuffix(){ var name = document.getElementById("txt").value; var strRegex = "(.xls|.xlsx|.xlsb|.xlsm|.xlst)$"; var re=new RegExp(strRegex); if (re.test(name.toLowerCase())){ alert("上傳成功"); document.fileupload.submit(); } else{ alert("文件名不合法"); }} |
5.dao層的接口和實(shí)現(xiàn)類
|
1
2
3
4
|
package com.gxxy.team1.yyd.dao;public interface IFileUploadDao { public void save(Object o); } |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
package com.gxxy.team1.yyd.dao.impl;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Repository;import com.gxxy.team1.yyd.dao.IFileUploadDao;@Repositorypublic class FileUploadDaoImpl implements IFileUploadDao { @Autowired private SessionFactory sessionFactory; private Session getSession() { Session session = sessionFactory.getCurrentSession(); return session; } @Override public void save(Object o) { getSession().save(o); }} |
6.service層的接口和實(shí)現(xiàn)類
|
1
2
3
4
5
6
7
8
|
package com.gxxy.team1.yyd.service;import java.util.List;public interface IFileUploadService { public List<String[]> readExcel(String path); public void save(Object o);} |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
package com.gxxy.team1.yyd.service.impl;import java.io.File;import java.io.FileInputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import com.gxxy.team1.yyd.dao.IFileUploadDao;import com.gxxy.team1.yyd.service.IFileUploadService;@Servicepublic class FileUploadServiceImpl implements IFileUploadService { @Autowired private IFileUploadDao fileDao; @Override public List<String[]> readExcel(String path) { SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); List<String[]> list = null; try { //同時(shí)支持Excel 2003、2007 File excelFile = new File(path); //創(chuàng)建文件對(duì)象 FileInputStream is = new FileInputStream(excelFile); //文件流 Workbook workbook = WorkbookFactory.create(is); //這種方式 Excel 2003/2007/2010 都是可以處理的 int sheetCount = workbook.getNumberOfSheets(); //Sheet的數(shù)量 //存儲(chǔ)數(shù)據(jù)容器 list = new ArrayList<String[]>(); //遍歷每個(gè)Sheet for (int s = 0; s < sheetCount; s++) { Sheet sheet = workbook.getSheetAt(s); int rowCount = sheet.getPhysicalNumberOfRows(); //獲取總行數(shù) //遍歷每一行 for (int r = 0; r < rowCount; r++) { Row row = sheet.getRow(r); int cellCount = row.getPhysicalNumberOfCells(); //獲取總列數(shù) //用來(lái)存儲(chǔ)每行數(shù)據(jù)的容器 String[] model = new String[cellCount-1]; //遍歷每一列 for (int c = 0; c < cellCount; c++) { Cell cell = row.getCell(c); int cellType = cell.getCellType(); if(c == 0) continue;//第一列ID為標(biāo)志列，不解析 String cellValue = null; switch(cellType) { case Cell.CELL_TYPE_STRING: //文本 cellValue = cell.getStringCellValue(); //model[c-1] = cellValue; break; case Cell.CELL_TYPE_NUMERIC: //數(shù)字、日期 if(DateUtil.isCellDateFormatted(cell)) { cellValue = fmt.format(cell.getDateCellValue()); //日期型 //model[c-1] = cellValue; } else { cellValue = String.valueOf(cell.getNumericCellValue()); //數(shù)字 //model[c-1] = cellValue; } break; case Cell.CELL_TYPE_BOOLEAN: //布爾型 cellValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_BLANK: //空白 cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_ERROR: //錯(cuò)誤 cellValue = "錯(cuò)誤"; break; case Cell.CELL_TYPE_FORMULA: //公式 cellValue = "錯(cuò)誤"; break; default: cellValue = "錯(cuò)誤"; } System.out.print(cellValue + " "); model[c-1] = cellValue; } //model放入list容器中 list.add(model); System.out.println(); } } is.close(); } catch (Exception e) { e.printStackTrace(); } return list; } @Override public void save(Object o) { fileDao.save(o); } } |
7.controller層實(shí)現(xiàn)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
//文件上傳方法 @RequestMapping("/uploadfile") public String upload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model,Model mod) throws Exception { String path = request.getSession().getServletContext().getRealPath("upload"); System.out.println("文件路徑："+path); String originalFilename = file.getOriginalFilename(); String type = file.getContentType(); //originalFilename = UUID.randomUUID().toString()+originalFilename; System.out.println("目標(biāo)文件名稱："+originalFilename+",目標(biāo)文件類型："+type); File targetFile = new File(path,originalFilename ); if (!targetFile.getParentFile().exists()) { targetFile.getParentFile().mkdirs(); }else if (!targetFile.exists()) { targetFile.mkdirs(); } // 獲得上傳文件的文件擴(kuò)展名 String subname = originalFilename.substring(originalFilename.lastIndexOf(".")+1); System.out.println("文件的擴(kuò)展名："+subname); try { file.transferTo(targetFile); } catch (Exception e) { e.printStackTrace(); } FileUploadServiceImpl fileUp = new FileUploadServiceImpl(); String rootpath = path + File.separator + originalFilename; List<String[]> excellist = fileUp.readExcel(rootpath); int len = excellist.size(); System.out.println("集合的長(zhǎng)度為："+len); for (int i = 0; i < len; i++) { String[] fields = excellist.get(i); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); String sampleNo = fields[0]; Double valueOf = Double.valueOf(fields[1]); int sampleType = valueOf.intValue(); //double轉(zhuǎn)int String createTime = fields[2]; Date createTime1 = format.parse(createTime); String name = fields[3]; String pId = fields[4]; String hospitalName = fields[5]; String cellPhone = fields[6]; Sample sample = new Sample(sampleNo, sampleType, createTime1, name, pId); Patient patient = new Patient(hospitalName, cellPhone); fileService.save(sample); fileService.save(patient); } //model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+originalFilename); String username = (String) request.getSession().getAttribute("username"); List<List<Menu>> power = powerService.power(username); mod.addAttribute("list", power); return "redirect:/ yyd"; } |
以上這7個(gè)部分就是我實(shí)現(xiàn)解析excel文件并存入數(shù)據(jù)庫(kù)的全部代碼。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持服務(wù)器之家。
原文鏈接：http://www.cnblogs.com/sushu-yaya/p/6838135.html?utm_source=tuicool&utm_medium=referral
