springmvc之Excel文件上传并写入数据库

来源：互联网发布：北京政务数据资源网编辑：程序博客网时间：2024/06/06 03:16

页面设置：uploadexcel.jsp

<body>     <form action="excel.do?method=uploadfile" method="post"  enctype="multipart/form-data">     <input type="file" value="文件" name="file" >      <input type="submit" value="提交">     </form>  </body>

spring注解，接收文件

@Controller@RequestMapping("/excel.do")public class ExcelUpLoadAction {@Autowiredprivate RecruithighstuService2 reService;@RequestMapping(params = "method=uploadfile", method = RequestMethod.POST)public void doUpload(HttpServletRequest request,HttpServletResponse response) throws IOException {// 文件上传 上传到哪里 MultipartHttpServletRequest mr = (MultipartHttpServletRequest) request; // 获取上传的文件MultipartFile multiFile = (MultipartFile) mr.getFile("file");// 获得文件全名String fname = multiFile.getOriginalFilename(); String pathfile = "D:/Tomcat6.0/webapps/sunjob_management_system/excels"+"/"+fname; <span style="white-space:pre"></span>File file = new File(pathfile);try {

<span style="white-space:pre"></span>//文件提交multiFile.transferTo(file); } catch (IllegalStateException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}reService.getExcelData(pathfile);} }

server配置：

@Servicepublic class RecruithighstuService2 {Recruithighstu stu=new Recruithighstu();@Autowiredprivate ExcelUploadDAO excelUploadDAO;@Autowiredprivate RecruitstudentDAO rsDao;@Autowiredprivate RecruithighstuDAO dao;public void writeInMYSQL(String path, List readList) {// 循环一次得到一行的数组for (int i = 0; i < readList.size()-2; i++) {System.out.println(readList.size());String[] colArr = (String[]) readList.get(i);rsDao.findById(Integer.parseInt(colArr[0]));stu.setRecruitstudent(rsDao.findById(Integer.parseInt(colArr[0])));stu.setRhName(colArr[1]);stu.setRhSex(Integer.parseInt(colArr[2]));stu.setRhAddress(colArr[3]);stu.setRhSchool(colArr[4]);stu.setRhPhone(colArr[5]); stu.setRhFphone(colArr[6]);stu.setRhMphone(colArr[7]);stu.setRhQq(colArr[8]); System.out.println(colArr[8]); System.out.println(colArr[9]);stu.setRhNote(colArr[9]);//dao.save(stu);excelUploadDAO.save(stu);System.out.println(dao.findAll());}}public List getExcelData(String path) {// 流操作// InputStream此抽象类是表示字节输入流的所有类的超类。// FileInputStream 从文件系统中的某个文件中获得输入字节InputStream inputStream = null;List readList = new ArrayList();Workbook workbook = null;// 得到工作薄对象// System.out.println(path);try {inputStream = new FileInputStream(path);workbook = Workbook.getWorkbook(inputStream);// 通过工作表名字获得工作表对象。Sheet sheet = workbook.getSheet("Sheet1");// 获得行数int rows = sheet.getRows();// 得到当前行所有的单元格for (int i = 1; i < rows; i++) {// 得到当前行所有的单元格Cell[] cell = sheet.getRow(i);// 定义数组String[] colArr = new String[cell.length];// 循环当前单元格for (int j = 0; j < cell.length; j++) {// 取得当前单元格的内容colArr[j] = cell[j].getContents().trim();// System.out.println(colArr[j]);}readList.add(colArr);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {if (inputStream != null) {workbook.close();try {inputStream.close();} catch (IOException e) {e.printStackTrace();}}}writeInMYSQL(path, readList);return readList;}}

dao层

public class ExcelUploadDAO extends HibernateDaoSupport{ public void save(Object object){getHibernateTemplate().save(object);getHibernateTemplate().flush();//一定要清空缓存，不然只能会保存最后一条数据，其他数据保存不了getHibernateTemplate().clear();}  }

web.xml配置

<?xml version="1.0" encoding="UTF-8"?><web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee   http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <context-param>  <param-name>contextConfigLocation</param-name>  <param-value>classpath:app*.xml</param-value> </context-param> <filter>  <filter-name>ChineseFilter</filter-name>  <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>  <init-param>   <param-name>encoding</param-name>   <param-value>utf-8</param-value>  </init-param> </filter> <filter>  <filter-name>opensession</filter-name>  <filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class> </filter> <filter-mapping>  <filter-name>ChineseFilter</filter-name>  <url-pattern>/*</url-pattern> </filter-mapping> <filter-mapping>  <filter-name>opensession</filter-name>  <url-pattern>/*</url-pattern> </filter-mapping> <listener>  <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <servlet>  <servlet-name>springMVC</servlet-name>  <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> </servlet> <servlet-mapping>  <servlet-name>springMVC</servlet-name>  <url-pattern>*.do</url-pattern> </servlet-mapping>  <welcome-file-list>  <welcome-file>login.jsp</welcome-file> </welcome-file-list> <login-config>  <auth-method>BASIC</auth-method> </login-config> <!--  <servlet> <servlet-name>excelUpload</servlet-name> <servlet-class>com.action.ExcelUpLoadAction</servlet-class> </servlet> <servlet-mapping> <servlet-name>excelUpload</servlet-name> <url-pattern>/excel.do</url-pattern> </servlet-mapping>  --></web-app>

springMVC-servlet.xml配置

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:util="http://www.springframework.org/schema/util"xmlns:p="http://www.springframework.org/schema/p"xmlns:aop="http://www.springframework.org/schema/aop"xmlns:tx="http://www.springframework.org/schema/tx"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsdhttp://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsdhttp://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd  http://www.springframework.org/schema/context     http://www.springframework.org/schema/context/spring-context-2.5.xsd"><context:component-scan base-package="com.action"></context:component-scan><!-- 视图解析器 --><bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"><property name="prefix" value="/WEB-INF/"/>          <property name="suffix" value=".jsp"/>  </bean> <!-- SpringMVC上传文件时，需要配置MultipartResolver处理器 --><bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"><property name="defaultEncoding" value="UTF-8"/><!-- 指定所上传文件的总大小不能超过200KB。注意maxUploadSize属性的限制不是针对单个文件，而是所有文件的容量之和 --><property name="maxUploadSize" value="2000000"/></bean><!-- SpringMVC在超出上传文件限制时，会抛出org.springframework.web.multipart.MaxUploadSizeExceededException --><!-- 该异常是SpringMVC在检查上传的文件信息时抛出来的，而且此时还没有进入到Controller方法中 --><bean id="exceptionResolver" class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver"><property name="exceptionMappings"><props><!-- 遇到MaxUploadSizeExceededException异常时，自动跳转到/WEB-INF/jsp/error_fileupload.jsp页面 --><prop key="org.springframework.web.multipart.MaxUploadSizeExceededException">error_fileupload</prop></props></property></bean></beans>

0 1