利用poi实现数据库的数据导入导出excel文件
在这里首先我要将自己遇到的各种问题,以及需求记录下来,做一个备忘,便于以后查看:
需求:主要实现两个功能,将oracle数据库里的数据导出为excel,同时需要将excel表格的数据导入到数据库
环境:springmvc + spring + mybatis + jdk1.7 + poi3.8 + easyui + oracle
在开始的时候,我就各种找jar包搭建环境,搭建环境时候所有的jar包都没有,只能去各种找,去下载,话不多说,直接上jar包,
然后就是各种配置文件,将配置文件现在直接贴出来:
《 applicationContext-dao.xml 》:
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" 4 xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 5 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 6 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 7 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd 8 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd 9 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd"> 10 11 <!-- 加载配置文件 --> 12 <context:property-placeholder location="classpath:db.properties" /> 13 14 <!-- 数据库连接池 --> 15 <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> 16 <!-- 驱动 --> 17 <property name="driverClassName" value="${jdbc.driver}" /> 18 <!-- url --> 19 <property name="url" value="${jdbc.url}" /> 20 <!-- 用户名 --> 21 <property name="username" value="${jdbc.username}" /> 22 <!-- 密码 --> 23 <property name="password" value="${jdbc.password}" /> 24 </bean> 25 26 <!-- mapper配置 --> 27 <!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 --> 28 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 29 <!-- 数据库连接池 --> 30 <property name="dataSource" ref="dataSource" /> 31 <property name="typeAliasesPackage" value="com.sword.dataprocess.pojo"></property> 32 </bean> 33 <!-- 配置Mapper扫描器 --> 34 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 35 <property name="basePackage" value="com.sword.dataprocess.mapper"/> 36 </bean> 37 38 </beans> 39 40
《 applicationContext-service.xml 》:
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" 4 xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 5 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 6 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 7 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd 8 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd 9 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd"> 10 11 <context:component-scan base-package="com.sword.dataprocess.service"/> 12 13 <!-- 事务管理器 --> 14 <bean id="transactionManager" 15 class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 16 <!-- 数据源 --> 17 <property name="dataSource" ref="dataSource" /> 18 </bean> 19 <!-- 通知 --> 20 <tx:advice id="txAdvice" transaction-manager="transactionManager"> 21 <tx:attributes> 22 <!-- 传播行为 --> 23 <tx:method name="save*" propagation="REQUIRED" /> 24 <tx:method name="insert*" propagation="REQUIRED" /> 25 <tx:method name="delete*" propagation="REQUIRED" /> 26 <tx:method name="update*" propagation="REQUIRED" /> 27 <tx:method name="find*" propagation="SUPPORTS" read-only="true" /> 28 <tx:method name="get*" propagation="SUPPORTS" read-only="true" /> 29 </tx:attributes> 30 </tx:advice> 31 <!-- 切面 --> 32 <aop:config> 33 <aop:advisor advice-ref="txAdvice" 34 pointcut="execution(* com.sword.dataprocess.service*.*.*(..))" /> 35 </aop:config> 36 </beans>
数据库的连接信息:
《 db.properties 》:
1 jdbc.dbType=oracle 2 jdbc.driver=oracle.jdbc.driver.OracleDriver 3 jdbc.url=jdbc:oracle:thin:@远程的连接ip:orcl 4 jdbc.username=xxx 5 jdbc.password=xxx
日志文件:
《log4j.properties 》: 这里不再贴出
springmvc的xml:
《springmvc.xml》:
1 <beans xmlns="http://www.springframework.org/schema/beans" 2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc="http://www.springframework.org/schema/mvc" 3 xmlns:context="http://www.springframework.org/schema/context" 4 xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 5 xmlns:task="http://www.springframework.org/schema/task" 6 xsi:schemaLocation="http://www.springframework.org/schema/beans 7 http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 8 http://www.springframework.org/schema/mvc 9 http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd 10 http://www.springframework.org/schema/context 11 http://www.springframework.org/schema/context/spring-context-4.2.xsd 12 http://www.springframework.org/schema/aop 13 http://www.springframework.org/schema/aop/spring-aop-4.2.xsd 14 http://www.springframework.org/schema/tx 15 http://www.springframework.org/schema/tx/spring-tx-4.2.xsd 16 http://www.springframework.org/schema/task 17 http://www.springframework.org/schema/task/spring-task-4.2.xsd"> 18 <!-- 加载属性文件 --> 19 <!-- <context:property-placeholder location="classpath:resource.properties"/> --> 20 <!-- 配置扫描 器 --> 21 <context:component-scan base-package="com.sword.dataprocess.controller" /> 22 <!-- 配置处理器映射器 适配器 --> 23 <mvc:annotation-driven /> 24 25 <!-- 配置不拦截静态文件 --> 26 <mvc:resources location="/css/" mapping="/css/**" /> 27 <mvc:resources location="/js/" mapping="/js/**" /> 28 <mvc:resources location="/plugins/" mapping="/plugins/**" /> 29 30 <!-- 配置视图解释器 jsp --> 31 <bean id="jspViewResolver" 32 class="org.springframework.web.servlet.view.InternalResourceViewResolver"> 33 <property name="prefix" value="/WEB-INF/jsp/" /> 34 <property name="suffix" value=".jsp" /> 35 </bean> 36 37 <!-- 配置文件解析器 上传文件 --> 38 <bean id="multipartResolver" 39 class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> 40 <property name="defaultEncoding" value="UTF-8"/> 41 <!-- 设置上传文件的最大尺寸为5MB --> 42 <property name="maxUploadSize"> 43 <value>5242880</value> 44 </property> 45 </bean> 46 47 </beans>
最后最重要的就是web.xml :
《web.xml 》:
1 <?xml version="1.0" encoding="UTF-8"?> 2 <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5"> 3 <display-name>DataProcess</display-name> 4 <welcome-file-list> 5 <welcome-file>index.html</welcome-file> 6 <welcome-file>index.htm</welcome-file> 7 <welcome-file>index.jsp</welcome-file> 8 <welcome-file>default.html</welcome-file> 9 <welcome-file>default.htm</welcome-file> 10 <welcome-file>default.jsp</welcome-file> 11 </welcome-file-list> 12 <!-- 配置监听器 --> 13 <listener> 14 <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> 15 </listener> 16 <context-param> 17 <param-name>contextConfigLocation</param-name> 18 <param-value>classpath:applicationContext-*.xml</param-value> 19 </context-param> 20 21 <!-- 配置servlet --> 22 <servlet> 23 <servlet-name>springmvc</servlet-name> 24 <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> 25 26 <init-param> 27 <param-name>contextConfigLocation</param-name> 28 <param-value>classpath:springmvc.xml</param-value> 29 </init-param> 30 </servlet> 31 <servlet-mapping> 32 <servlet-name>springmvc</servlet-name> 33 <url-pattern>/</url-pattern> 34 </servlet-mapping> 35 </web-app>
前端主要使用的是easyui里面的datagried :大致页面如下:
然后就是把jsp页面的代码贴出来:
《index.jsp》:
1 <%@ page language="java" contentType="text/html; charset=UTF-8" 2 pageEncoding="UTF-8"%> 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 4 <html> 5 <head> 6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> 7 <title>MRP导入导出</title> 8 <!-- 导入jquery核心类库 --> 9 <script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery-1.8.3.js"></script> 10 <!-- 导入easyui类库 --> 11 <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/js/easyui/themes/default/easyui.css"> 12 <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/js/easyui/themes/icon.css"> 13 <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/js/easyui/ext/portal.css"> 14 <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/css/default.css"> 15 <script type="text/javascript" src="${pageContext.request.contextPath}/js/easyui/jquery.easyui.min.js"></script> 16 <script type="text/javascript" src="${pageContext.request.contextPath}/js/easyui/ext/jquery.portal.js"></script> 17 <script type="text/javascript" src="${pageContext.request.contextPath}/js/easyui/ext/jquery.cookie.js"></script> 18 <script src="${pageContext.request.contextPath}/js/easyui/locale/easyui-lang-zh_CN.js" type="text/javascript"></script> 19 <script src="${pageContext.request.contextPath}/js/jquery.serializejson.min.js" type="text/javascript"></script> 20 <script src="${pageContext.request.contextPath}/js/jquery.ocupload-1.1.2.js" type="text/javascript"></script> 21 <script type="text/javascript"> 22 23 function doExport() { 24 location.href="${pageContext.request.contextPath}/export"; 25 } 26 27 function doImport() { 28 $("#button-import").upload({ 29 name:'myFile', 30 action:'${pageContext.request.contextPath}/import"', 31 onComplete:function(data){ 32 alert(data); 33 if(data == "success"){ 34 $.messager.alert('友情提示','恭喜你,导入成功'); 35 } 36 if(data == "error"){ 37 $.messager.alert('友情提示','导入失败,请按正确的模板数据导入!'); 38 } 39 $('#grid').datagrid('load'); 40 } 41 }); 42 43 } 44 45 46 //工具栏 47 var toolbar = [{ 48 id : 'button-import', 49 text : '导入', 50 iconCls : 'icon-redo', 51 handler : doImport 52 }, { 53 id : 'button-export', 54 text : '导出', 55 iconCls : 'icon-undo', 56 handler : doExport 57 } ]; 58 // 定义列 59 var columns = [ [ { 60 field : 'p_id', 61 title : '料件编号', 62 width : 120, 63 align : 'center', 64 }, { 65 field : 'p_name', 66 title : '品名', 67 width : 120, 68 align : 'center', 69 }, { 70 field : 'p_guige', 71 title : '规格', 72 width : 120, 73 align : 'center', 74 }, { 75 field : 'p_xdata', 76 title : '行动日期', 77 width : 120, 78 align : 'center' 79 }, { 80 field : 'p_jdate', 81 title : '交货日期', 82 width : 100, 83 align : 'center' 84 }, { 85 field : 'p_descCount', 86 title : '排产数量', 87 width : 100, 88 align : 'center' 89 } ] ]; 90 91 $(function() { 92 /* daoru fenqu */ 93 // 先将body隐藏,再显示,不会出现页面刷新效果 94 $("body").css({ 95 visibility : "visible" 96 }); 97 98 // 管理数据表格 99 $('#grid').datagrid({ 100 iconCls : 'icon-forward', 101 fit : true, 102 border : true, 103 rownumbers : true, 104 striped : true, 105 pageList : [ 30, 50, 100 ], 106 pagination : true, 107 toolbar : toolbar, 108 url : "${pageContext.request.contextPath}/show", 109 idField : 'p_id', 110 columns : columns, 111 }); 112 113 var pager = $('#grid').datagrid('getPager'); // get the pager of datagrid 114 pager.pagination({ 115 showPageList:false, 116 }); 117 118 }); 119 120 </script> 121 </head> 122 <body class="easyui-layout" style="visibility: hidden;"> 123 <div region="center" border="false"> 124 <table id="grid"></table> 125 </div> 126 </body> 127 </html>
接下来就是代码的实现:
《controller层》:
1 import java.io.File; 2 import java.io.FileInputStream; 3 import java.util.List; 4 import java.util.Map; 5 6 import javax.servlet.ServletOutputStream; 7 import javax.servlet.http.HttpServletRequest; 8 import javax.servlet.http.HttpServletResponse; 9 10 import org.apache.commons.fileupload.disk.DiskFileItem; 11 import org.springframework.beans.factory.annotation.Autowired; 12 import org.springframework.stereotype.Controller; 13 import org.springframework.ui.Model; 14 import org.springframework.web.bind.annotation.RequestMapping; 15 import org.springframework.web.bind.annotation.ResponseBody; 16 import org.springframework.web.context.request.RequestContextHolder; 17 import org.springframework.web.context.request.ServletRequestAttributes; 18 import org.springframework.web.multipart.MultipartFile; 19 import org.springframework.web.multipart.MultipartHttpServletRequest; 20 import org.springframework.web.multipart.commons.CommonsMultipartFile; 21 22 import com.sword.dataprocess.pojo.DataProcess; 23 import com.sword.dataprocess.service.DataService; 24 import com.sword.dataprocess.utils.FileUtils; 25 26 @Controller 27 public class DataController { 28 29 @Autowired 30 private DataService dataService; 31 32 @RequestMapping(value={"/index","/index.html","/index.htm"}) 33 public String index(){ 34 return "index"; 35 } 36 37 @RequestMapping("/show") 38 @ResponseBody 39 public List<DataProcess> show(Model model){ 40 List<DataProcess> list = dataService.findAll(); 41 model.addAttribute("list", list); 42 return list; 43 } 44 45 // 文件导出 46 @RequestMapping("/export") 47 public void exportXls(HttpServletRequest request,HttpServletResponse response) throws Exception{ 48 // 一个流 49 // 两个头 50 // 下载文件的mime类型 51 response.setContentType("application/vnd.ms-excel"); // 常见的文件 可以省略 52 53 // 文件的打开方式 inline在线打开 attachment 54 String agent = request.getHeader("User-Agent"); 55 String filename = FileUtils.encodeDownloadFilename("data.xlsx", agent); 56 response.setHeader("content-disposition", "attachment;fileName="+filename); 57 ServletOutputStream outputStream = response.getOutputStream(); 58 59 // 获取模板 在当前项目 60 ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.currentRequestAttributes(); 61 String templatePath = request.getServletContext().getRealPath(File.separator)+"temp"+File.separator+"data.xlsx"; 62 System.out.println(templatePath); 63 FileInputStream fileInputStream = new FileInputStream(templatePath); 64 65 dataService.exportAls(fileInputStream, outputStream); 66 } 67 68 69 // 文件导入 70 //接收页面传来的文件 71 @RequestMapping("/import") 72 @ResponseBody 73 public String importXlsx(HttpServletRequest request){ 74 System.out.println(111); 75 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; 76 MultipartFile myFile = multipartRequest.getFile("myFile"); // 通过参数名获取指定文件 文件本身 变量名和文件上传时的名称保持一致 77 String myFileFileName = myFile.getOriginalFilename();//文件的名字 78 String myFileContentType = myFile.getContentType(); //文件的mime类型 79 80 CommonsMultipartFile cf= (CommonsMultipartFile)myFile; 81 DiskFileItem fi = (DiskFileItem)cf.getFileItem(); 82 83 File f = fi.getStoreLocation(); 84 String msg = null; 85 86 Boolean flag = dataService.importXls(f,myFileContentType); 87 if(flag){ 88 msg = "success"; 89 }else{ 90 msg = "error"; 91 } 92 return msg; 93 } 94 95 96 }
《service层》:
1 import java.io.File; 2 import java.io.FileInputStream; 3 import java.io.IOException; 4 import java.text.SimpleDateFormat; 5 import java.util.Date; 6 import java.util.List; 7 8 import javax.servlet.ServletOutputStream; 9 10 import org.apache.commons.lang3.StringUtils; 11 import org.apache.poi.hssf.usermodel.HSSFRow; 12 import org.apache.poi.hssf.usermodel.HSSFSheet; 13 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 14 import org.apache.poi.xssf.usermodel.XSSFCell; 15 import org.apache.poi.xssf.usermodel.XSSFRow; 16 import org.apache.poi.xssf.usermodel.XSSFSheet; 17 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 18 import org.springframework.beans.factory.annotation.Autowired; 19 import org.springframework.stereotype.Service; 20 21 import com.sword.dataprocess.mapper.DataMapper; 22 import com.sword.dataprocess.pojo.DataProcess; 23 import com.sword.dataprocess.service.DataService; 24 25 @Service 26 public class DataServiceImpl implements DataService{ 27 @Autowired 28 private DataMapper dataMapper; 29 30 @Override 31 public int dataCount() { 32 return dataMapper.dataCount(); 33 } 34 35 @Override 36 public void exportAls(FileInputStream fileInputStream, ServletOutputStream outputStream) { 37 // Workbook工作簿 38 XSSFWorkbook book = null; 39 try { 40 book = new XSSFWorkbook(fileInputStream); 41 } catch (IOException e) { 42 e.printStackTrace(); 43 } 44 45 // 工作表 sheet 46 XSSFSheet sheet = book.getSheetAt(0); 47 // 获取第二个sheet中的第一行第一列的样式 及边框 48 // XSSFCellStyle cellStyle = book.getSheetAt(1).getRow(0).getCell(0).getCellStyle(); 49 List<DataProcess> list = dataMapper.findAll(); 50 System.out.println(list.size()); 51 int rowIndex = 1; // 让表格从第二行开始导入 52 XSSFCell cell = null; 53 for (DataProcess dataProcess : list) { 54 // 新建一行 55 XSSFRow row = sheet.createRow(rowIndex); 56 cell = row.createCell(0); // 第一个单元格 57 // 设定已经准备好单元格的样式 58 // cell.setCellStyle(cellStyle); 59 String id = dataProcess.getP_id(); 60 if(id != null){ 61 cell.setCellValue(id); 62 } 63 64 cell = row.createCell(1); // 第一个单元格 65 String name = dataProcess.getP_name(); 66 if(name != null){ 67 cell.setCellValue(name); 68 } 69 70 cell = row.createCell(2); // 第二个单元格 71 String guige = dataProcess.getP_guige(); 72 if(guige != null){ 73 cell.setCellValue(guige); 74 } 75 76 cell = row.createCell(3); // 第三个单元格 77 String xdata = dataProcess.getP_xdata(); 78 if(xdata != null){ 79 cell.setCellValue(xdata); 80 } 81 82 cell = row.createCell(4); // 第四个单元格 83 String jdate = dataProcess.getP_jdate(); 84 if(jdate != null){ 85 cell.setCellValue(jdate); 86 } 87 88 /*cell = row.createCell(5); // 第五个单元格 89 Integer sourceCount = dataProcess.getP_sourceCount(); 90 if(sourceCount != null){ 91 cell.setCellValue(sourceCount); 92 }*/ 93 cell = row.createCell(6); // 第六个单元格 94 Integer descCount = dataProcess.getP_descCount(); 95 if (descCount != null) { 96 cell.setCellValue(descCount); 97 } 98 99 rowIndex++; 100 } 101 // 把工作簿放在输出流中 102 try { 103 book.write(outputStream); 104 } catch (IOException e) { 105 e.printStackTrace(); 106 } 107 } 108 109 // 导入数据 110 @Override 111 public Boolean importXls(File myFile, String myFileContentType) { 112 113 if ("application/vnd.ms-excel".equals(myFileContentType)) { 114 System.out.println(123); 115 try { 116 // 获取workbook工作簿 117 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(myFile)); 118 // 获取sheet 工作表 119 HSSFSheet sheet = hssfWorkbook.getSheetAt(0); 120 // 获取工作表的最后一行索引 121 int lastRowNum = sheet.getLastRowNum(); 122 for (int i = 1; i <= lastRowNum; i++) { 123 DataProcess dataProcess = new DataProcess(); 124 HSSFRow row = sheet.getRow(i); 125 // 料件编号 特征码(8个0)行动日期 交货日期 排产数量 版本号(一次导入只用设置一个相同的值就行) 已执行步骤为0 126 127 // 料件编号 128 String p_id = row.getCell(0).getStringCellValue(); 129 dataProcess.setP_id(p_id); 130 // 行动日期 131 String p_xdata = row.getCell(3).getStringCellValue(); 132 dataProcess.setP_xdata(p_xdata);; 133 // 交货日期 134 String p_jdate = row.getCell(4).getStringCellValue(); 135 dataProcess.setP_jdate(p_jdate); 136 /*// 需求数量 137 Integer p_sourceCount = (int) row.getCell(5).getNumericCellValue(); 138 dataProcess.setP_sourceCount(p_sourceCount);*/ 139 // 排产数量 140 Integer p_descCount = (int) row.getCell(5).getNumericCellValue(); 141 dataProcess.setP_descCount(p_descCount); 142 // 版本号(一次导入只用设置一个相同的值就行) 143 SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-dd"); 144 String datetime = tempDate.format(new Date()); 145 String p_version = "MRPVERNO"+datetime; 146 dataProcess.setP_version(p_version); 147 // 向tc_aau_file表插入数据 148 dataMapper.insertdata(dataProcess); 149 // 向tc_aat_file表插入数据 150 if(i==lastRowNum){ 151 dataMapper.insertToAAT(p_version); 152 } 153 } 154 } catch (Exception e) { 155 e.printStackTrace(); 156 return false; 157 } 158 159 } else if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(myFileContentType)) { 160 try { 161 // 获取workbook工作簿 162 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(myFile)); 163 // 获取sheet 工作表 164 XSSFSheet sheet = xssfWorkbook.getSheetAt(0); 165 // 获取工作表的最后一行索引 166 int lastRowNum = sheet.getLastRowNum(); 167 for (int i = 1; i <= lastRowNum; i++) { 168 DataProcess dataProcess = new DataProcess(); 169 XSSFRow row = sheet.getRow(i); 170 // 料件编号 特征码(8个0)行动日期 交货日期 排产数量 版本号(一次导入只用设置一个相同的值就行) 已执行步骤为0 171 172 // 料件编号 173 String p_id = row.getCell(0).getStringCellValue(); 174 dataProcess.setP_id(p_id); 175 // 行动日期 176 String p_xdata = row.getCell(3).getStringCellValue(); 177 dataProcess.setP_xdata(p_xdata); 178 // 交货日期 179 String p_jdate = row.getCell(4).getStringCellValue(); 180 dataProcess.setP_jdate(p_jdate); 181 182 /*// 需求数量 183 Integer p_sourceCount = (int) row.getCell(5).getNumericCellValue(); 184 dataProcess.setP_sourceCount(p_sourceCount);*/ 185 // 排产数量 186 Integer p_descCount = (int) row.getCell(5).getNumericCellValue(); 187 dataProcess.setP_descCount(p_descCount); 188 // 版本号(一次导入只用设置一个相同的值就行) 189 SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-dd"); 190 String datetime = tempDate.format(new Date()); 191 String p_version = "MRPVERNO"+datetime; 192 dataProcess.setP_version(p_version); 193 194 // 向tc_aau_file表插入数据 195 dataMapper.insertdata(dataProcess); 196 // 向tc_aat_file表插入数据 197 if(i==lastRowNum){ 198 dataMapper.insertToAAT(p_version); 199 } 200 } 201 }catch (Exception e) { 202 e.printStackTrace(); 203 return false; 204 } 205 } // elseif 结束 206 return true; 207 } 208 209 210 // 查询所有数据 211 @Override 212 public List<DataProcess> findAll() { 213 List<DataProcess> result = dataMapper.findAll(); 214 return result; 215 } 216 }
《mapper 层》:
1 import java.util.List; 2 3 import org.apache.ibatis.annotations.Param; 4 5 import com.sword.dataprocess.pojo.DataProcess; 6 7 public interface DataMapper { 8 public int dataCount(); 9 10 public List<DataProcess> findAll(); 11 12 public void insertdata(@Param("dataProcess")DataProcess dataProcess); 13 14 public void insertToAAT(@Param("p_version")String p_version); 15 }
《对应的xml:》:
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.sword.dataprocess.mapper.DataMapper" > 4 5 <select id="dataCount" resultType="int"> 6 select count(1) from tc_aau_file 7 </select> 8 9 10 <resultMap type="dataProcess" id="datdProcessMap"> 11 <id column="tc_aau01" property="p_id"/> 12 <result column="IMA02" property="p_guige"/> 13 <result column="ima021" property="p_name"/> 14 <result column="TC_AAU06" property="p_xdata"/> 15 <result column="TC_AAU07" property="p_jdate"/> 16 <result column="TC_AAU09" property="p_descCount"/> 17 </resultMap> 18 19 <select id="findAll" resultMap="datdProcessMap"> 20 select t.TC_AAU01 , 21 i.IMA02 , 22 i.ima021 , 23 t.TC_AAU06 , 24 t.TC_AAU07 , 25 t.TC_AAU09 26 from SWORD.IMA_FILE i, SWORD.TC_AAU_FILE t where i.ima01 = t.tc_aau01 27 </select> 28 29 <insert id="insertdata" parameterType="dataProcess"> 30 INSERT INTO SWORD.TC_AAU_FILE ("TC_AAU01", "TC_AAU03", "TC_AAU06", "TC_AAU07", "TC_AAU09", "TC_AAU13", "TC_AAU14") VALUES (#{dataProcess.p_id}, '00000000', TO_DATE(#{dataProcess.p_xdata}, 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE(#{dataProcess.p_jdate}, 'SYYYY-MM-DD HH24:MI:SS'),#{dataProcess.p_descCount}, #{dataProcess.p_version}, '0') 31 </insert> 32 33 <insert id="insertToAAT" parameterType="string"> 34 INSERT INTO SWORD.TC_AAT_FILE ("TC_AAT01") VALUES (#{p_version}) 35 </insert> 36 37 </mapper>
用到了一个工具类(fileutils):
1 import java.io.IOException; 2 import java.net.URLEncoder; 3 4 import sun.misc.BASE64Encoder; 5 6 public class FileUtils { 7 /** 8 * 下载文件时,针对不同浏览器,进行附件名的编码 9 * 10 * @param filename 11 * 下载文件名 12 * @param agent 13 * 客户端浏览器 14 * @return 编码后的下载附件名 15 * @throws IOException 16 */ 17 public static String encodeDownloadFilename(String filename, String agent) 18 throws IOException { 19 if (agent.contains("Firefox")) { // 火狐浏览器 20 filename = "=?UTF-8?B?" 21 + new BASE64Encoder().encode(filename.getBytes("utf-8")) 22 + "?="; 23 filename = filename.replaceAll("\r\n", ""); 24 } else { // IE及其他浏览器 25 filename = URLEncoder.encode(filename, "utf-8"); 26 filename = filename.replace("+"," "); 27 } 28 return filename; 29 } 30 }
遇到的问题:
1.在springmvc.xml配置了前端静态资源不拦截之后,在显示前端界面时候,总是报一个错:不能够找到各种静态文件,无论我怎么设置,就是获取不到,而且在项目启动之后,直接访问对应的页面,是可以正常显示的,然后一通过视图解析器就获取不到静态资源,不能够正常显示,最终各种查资料,都显示的是没有配置忽略前端静态资源文件,解决不了问题,最后我处理了很久,终于发现自己犯了一个大错,就是将静态资源的导入时候,写的是相对路径,找不到对应的文件,最终解决办法就是写的动态获取的全路径。
2.遇到的第二个问题:我在导入的时候封装了一个对象,在mapper那里传入了一个对象,但是在对应的xml里面我取不到对象里面的属性值,最终查阅资料,需要在传的对象前面添加一个@param(”xxx“)注解,问题得以解决。
GitHub源码地址:https://github.com/Dingzhaoming/DataProcess
后续会继续补充,未经允许不得转载,欢迎大家多多指正!