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