项目是在SSH框架内实现:
使用的jar包
CDSN地址:http://download.csdn.net/download/lg_xulei/10148892
使用Maven:
pom.xml配置:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
项目maven做了限制导入,可以使用
<repositories> <repository> <id>central</id> <name>Maven Central Repository</name> <url>http://repo1.maven.org/maven2</url> <snapshots> <enabled>true</enabled> </snapshots> </repository> </repositories>
具体JAVA实现:
包引用:
// 导入Excel public String ExcelInto() throws Exception { String directory = "/file"; String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
//定义成员变量 File target = UploadFileInfo.Upload(uploadFile, uploadFileFileName,targetDirectory); List<DangerLevel> sList = new ArrayList<DangerLevel>(); /*Workbook wb = new HSSFWorkbook(excelFile);*/ Workbook wb=null;
//这个地方在第一次定义局部变量时,后台报了一个stream关闭错误,是因为在try{}catch(){}时,
//第一次格式化数据没通过,流通道关闭了,所以只能使用如下的方式进行数据导入 try { wb = new HSSFWorkbook(new FileInputStream(target)); } catch (OfficeXmlFileException e) { try { wb = new XSSFWorkbook(new FileInputStream(target)); } catch (Exception e2) { e2.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } Sheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum() + 1; for (int i = 3; i < rowNum; i++) { DangerLevel dangerLevel = new DangerLevel(); Row row = sheet.getRow(i); int cellNum = row.getLastCellNum(); for (int j = 0; j < cellNum; j++) { SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd"); Cell cell = row.getCell(j); String cellValue = null; switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库 case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else {// 日期 sdf = new SimpleDateFormat("yyyy-MM-dd"); } Date date = cell.getDateCellValue(); cellValue = sdf.format(date); } else if (cell.getCellStyle().getDataFormat() == 58) { // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58) sdf = new SimpleDateFormat("yyyy-MM-dd"); double value = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(value); cellValue = sdf.format(date); } else { double value = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } cellValue = format.format(value); } break; case HSSFCell.CELL_TYPE_STRING:// String类型 cellValue = cell.getRichStringCellValue().toString(); break; case HSSFCell.CELL_TYPE_BLANK: cellValue = ""; default: cellValue = ""; break; } switch (j) {// 通过列数来判断对应插如的字段 case 0: dangerLevel.setNote1(cellValue); break; case 1: dangerLevel.setRummager(cellValue); break; case 2: dangerLevel.setCheckTime(sdf.parse(cellValue)); break; case 3: dangerLevel.setCheckType(cellValue); break; case 4: dangerLevel.setSpotName(cellValue); break; case 5: dangerLevel.setDangerDesc(cellValue); break; case 6: dangerLevel.setClassification(cellValue); break; case 7: dangerLevel.setDangerLevel(cellValue); break; case 8: dangerLevel.setDangerType(cellValue); break; case 9: dangerLevel.setIssuedTime(sdf.parse(cellValue)); break; case 10: dangerLevel.setIssuedMan(cellValue); break; case 11: dangerLevel.setRectifyPlan(cellValue); break; case 12: dangerLevel.setPostponedDeadLine(sdf.parse(cellValue)); break; case 13: dangerLevel.setRectifyUserName(cellValue); break; case 14: dangerLevel.setRectifyCompany(cellValue); break; case 15: dangerLevel.setRectifyResult(cellValue); break; case 16: dangerLevel.setRectifyTime(sdf.parse(cellValue)); break; case 17: dangerLevel.setRecheckerCompany(cellValue); break; case 18: dangerLevel.setRecheckerResult(cellValue); break; case 19: dangerLevel.setRecheckerName(cellValue); break; case 20: dangerLevel.setRecheckerTime(sdf.parse(cellValue)); break; case 21: dangerLevel.setClearName(cellValue); break; case 22: dangerLevel.setClearTime(sdf.parse(cellValue)); break; case 23: dangerLevel.setRecheckerName(cellValue); break; case 24: dangerLevel.setDangerLevelStatus(cellValue); break; case 25: dangerLevel.setFileName(cellValue); break; case 26: dangerLevel.setFilePath(cellValue); break; } } sList.add(dangerLevel); } dangerLevelService.addDangerLevel(sList); return "dangerLevel_middle"; }
package com.hfky.crs.busi.domain; import java.io.File; import java.io.Serializable; import org.apache.commons.io.FileUtils; public class UploadFileInfo implements Serializable { private static final long serialVersionUID = -3076586641759098763L; /** * 待上传文件 */ private File upload; /** * 待上传文件名 */ private String uploadFileName; /** * 待上传文件的MIME类型 */ private String uploadContentType; /** * 成功上传的文件相对与根目录的文件名 */ private String uploadRelName; public File getUpload() { return upload; } public void setUpload(File upload) { this.upload = upload; } public String getFileName() { return uploadFileName; } public void setUploadFileName(String uploadFileName) { this.uploadFileName = uploadFileName; } public String getContentType() { return uploadContentType; } public void setUploadContentType(String uploadContentType) { this.uploadContentType = uploadContentType; } public String getUploadRelName() { return uploadRelName; } public void setUploadRelName(String uploadRelName) { this.uploadRelName = uploadRelName; } public String getUploadFileName() { return uploadFileName; } public String getUploadContentType() { return uploadContentType; } public static File Upload(File uploadFile, String uploadFileFileName,String targetDirectory)throws Exception { File target = new File(targetDirectory, uploadFileFileName); // 如果文件已经存在,则删除原有文件 if (target.exists()) { target.delete(); } // 复制file对象,实现上传 FileUtils.copyFile(uploadFile, target); return target; } }
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ include file="/resources/pages/system/inc-easyui.jsp"%> <html> <head> <base href="<%=basePath %>"/> <title>数据导入</title> <link href="styles/css/blue.css" type="text/css" rel="stylesheet" /> </head> <body class="crs_ep_tablebodybg"> <form action="danger!ExcelInto.action" enctype="multipart/form-data" method="post"> <table border="0" cellspacing="0" cellpadding="0" class="crs_ep_tablestyle"> <tr> <td colspan="99" id="more"> <input type="file" name="uploadFile" id="uploadFile"/> <input type="submit" value="上传"/> <input type="reset" value="重置"/> </td> </tr> </table> </form> </body> </html>
这里使用的是from表单提交,为了效果,在List页面中使用的是Iframe,当Action导入完成return的是一个 result地址,所以关键问题是:上传后不能关闭iframe页面;
这里使用一个非常不优雅的方式解决【JQuery版本低,用户群使用IE8.9.10】:使用一个跳转过度页面,在这个过度页面使用js关闭ifarem,和刷新List页面。如下:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ include file="/resources/pages/system/inc-easyui.jsp"%> <html> <head> <base href="<%=basePath %>"/> <title>隐患数据导入</title> <link href="styles/css/blue.css" type="text/css" rel="stylesheet" /> <script type="text/javascript"> $(function(){
//获取父也页面对象 parent.document.getElementById('cancelBtn').click();
//获取父页面的id="cancelBtn".使用click()鼠标触发事件。
parent.document.location.reload(); });
</script>
</head>
<body>
</body
</html>