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

最新推荐文章于 2019-09-16 17:24:58 发布

xiaoquanjunjun

最新推荐文章于 2019-09-16 17:24:58 发布

阅读量4.1k

点赞数 1

本文链接：https://blog.csdn.net/u013887451/article/details/40406595

版权

页面设置：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 {

	@Autowired
	private 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配置：

@Service
public class RecruithighstuService2 {

	
	Recruithighstu stu=new Recruithighstu();

	@Autowired
	private ExcelUploadDAO excelUploadDAO;
	@Autowired
	private RecruitstudentDAO rsDao;

	@Autowired
	private 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 block
			e.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.xsd
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd
	http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
	http://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>