β

Struts2实现文件上传,POI实现Excel文件读取并写入数据库技术

暗影部落 3656 阅读

如题:文件信息的批量导入......

项目中经常会遇到客户的一些单表信息的数据批量导入,也就是提供定制Excel表,再把Excel表中的数据提取到数据库的操作,其实实现起来很简单。以下是小菜鸟我的实现思路:

1、JSP页面+Struts2实现文件上传,把定制Excel上传到服务器指定目录下,以供读取;

2、POI读取Excel表格,把数据封装为持久化实体List

3、批量导入数据库

这是TT的开发环境:

Struts2.1.6 + Spring2.5.6 + Hibernate3.3.1 + MyEclipse8.5 + tomcat5.5 + Oracle11g

需要的Jar包:SSH的就不需要说了

主要就是一个POI的包:

我这里是 poi-3.7-beta-20100620.jar,这一个jar包可以去apach的官网上下载

还有一个就是struts的IO包:

commons-io-1.3.2.jar,顾名思义:IO工具类

第一步:文件上传

这一步,网上有很多的例子:

JSP页面:

<body>
    <s:form id="form1" name="form1" action="roleFileUpload.action" method="post" enctype="multipart/form-data" theme="simple">
    	<div align="center" id="div1" style="width: 80%">
	<table width="80%" border="1" align="center" class="DB_table">
	  <tr>
	    <td colspan="99" align="left">文件上传</td>
	  </tr>
	  <tr>
	    <td colspan="99" id="more">
	      <s:file name="uploadFile" label="文件位置" size="80"/>
	    </td>
	  </tr>
	  <tr>
	    <td colspan="99" align="right">
		<s:submit value="上传"></s:submit>
		<s:reset value="重置"></s:reset>
	    </td>
	  </tr>
        </table>
	</div>
    </s:form>
  </body>
Struts.xml配置文件:这里可以定制Filter,指定文件类型和最大长度,就不列举了
<action name="roleFileUpload" class="roleFileAction" method="loadRoleFile">
    <result name="success">/page/role/roleallinput.jsp</result>
    <result name="input">/page/role/roleallinput.jsp</result>
</action>
对应的applicationContext.xml配置
<bean id="roleFileAction" class="com.adtec.datacenter.action.manager.RoleFileUploadAction"
		scope="prototype">
		<property name="fileLoadDao" ref="fileLoadDao"></property>
	</bean>
	<bean id="fileLoadDao"
		class="com.adtec.datacenter.dao.manager.FileLoadDaoImpl">
		<property name="sessionFactory" ref="sessionFactory"></property>
	</bean>

RoleFileUploadAction实现

这里有几个点需要注意:

上传文件的文件如果为uploadFile,那么文件名一定要为uploadFileFileName,也就是在文件后加入FileName,不然不能识别

package com.adtec.datacenter.action.manager;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;

import com.adtec.datacenter.dao.manager.FileLoadDao;
import com.adtec.datacenter.dao.manager.RoleDao;
import com.adtec.datacenter.entity.manager.PtRoleInfo;
import com.adtec.datacenter.vo.manager.RoleInfo;
import com.opensymphony.xwork2.ActionSupport;

public class RoleFileUploadAction extends ActionSupport{
	
	private File uploadFile;
	private String uploadFileFileName;
	private FileLoadDao fileLoadDao;
	private RoleDao roleDao;
	
	public File getUploadFile() {
		return uploadFile;
	}
	public void setUploadFile(File uploadFile) {
		this.uploadFile = uploadFile;
	}
	public String getUploadFileFileName() {
		return uploadFileFileName;
	}
	public void setUploadFileFileName(String uploadFileFileName) {
		this.uploadFileFileName = uploadFileFileName;
	}
	
	public FileLoadDao getFileLoadDao() {
		return fileLoadDao;
	}
	public void setFileLoadDao(FileLoadDao fileLoadDao) {
		this.fileLoadDao = fileLoadDao;
	}
	
	public RoleDao getRoleDao() {
		return roleDao;
	}
	public void setRoleDao(RoleDao roleDao) {
		this.roleDao = roleDao;
	}
	@Override
	public void validate() {
		super.validate();
	}
	
	public String loadRoleFile(){
		
		String directory = "/upload/role";
		String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
		//生成上传的文件对象
		File target = new File(targetDirectory,uploadFileFileName);
		//如果文件已经存在,则删除原有文件
		if(target.exists()){
			target.delete();
		}
		//复制file对象,实现上传
		try {
			FileUtils.copyFile(uploadFile, target);
			
			//out = response.getWriter();
			//out.print("文件上传成功!");
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		loadRoleInfo(uploadFileFileName);
		return SUCCESS;
	}
	
	/**
	 * 把Excele表读出的数据,组装成一个List,统一导入数据库
	 * @param uploadFileFileName
	 */
	public void loadRoleInfo(String uploadFileFileName){
		
		String directory = "/upload/role"; 
		String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
		File target = new File(targetDirectory,uploadFileFileName);
		
		List roleList = new ArrayList();
		try{
			FileInputStream fi = new FileInputStream(target);
			Workbook wb = new HSSFWorkbook(fi);
			Sheet sheet = wb.getSheetAt(0);
			
			int rowNum = sheet.getLastRowNum()+1;
			for(int i=1;i<rowNum;i++){
				PtRoleInfo ptRoleInfo = new PtRoleInfo();
				Row row = sheet.getRow(i);
				int cellNum = row.getLastCellNum();
				for(int j=0;j<cellNum;j++){
					Cell cell = row.getCell(j);
					String cellValue = null;
					switch(cell.getCellType()){ //判断excel单元格内容的格式,并对其进行转换,以便插入数据库
						case 0 : cellValue = String.valueOf((int)cell.getNumericCellValue()); break;
						case 1 : cellValue = cell.getStringCellValue(); break;
						case 2 : cellValue = String.valueOf(cell.getDateCellValue()); break;
						case 3 : cellValue = ""; break;
						case 4 : cellValue = String.valueOf(cell.getBooleanCellValue()); break;
						case 5 : cellValue = String.valueOf(cell.getErrorCellValue()); break;
					}
					
					switch(j){//通过列数来判断对应插如的字段
						case 0 : ptRoleInfo.setRoleId(cellValue);break;
						case 1 : ptRoleInfo.setRoleName(cellValue);break;
						case 2 : ptRoleInfo.setDeil(cellValue);break;
					}
				}
				roleList.add(ptRoleInfo);
			}
			fileLoadDao.roleInfotoDB(roleList);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	
}

roleDao接口实现

这里接口类就不贴出来了,直接贴实现类,把持久化实体List循环写入数据库

package com.adtec.datacenter.dao.manager;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.adtec.datacenter.entity.manager.PtRoleInfo;
import com.adtec.datacenter.vo.manager.RoleInfo;
/**
 * 文件工具类
 * @author Tony
 *
 */
public class FileLoadDaoImpl extends HibernateDaoSupport implements FileLoadDao {
	/**
	 * 把从excel表读出的数据写入到数据库
	 */
	public void roleInfotoDB(List roleList){
		Session session = this.getHibernateTemplate().getSessionFactory().openSession();
		Transaction tx = null;
		
		try{
			tx = session.beginTransaction();
			if(roleList.size() > 0){
				
				int roleNum = roleList.size();
				for(int i=0;i<roleNum;i++){
					session.save(roleList.get(i));
				}
				/*for(Iterator iterator = roleList.iterator();iterator.hasNext();){
					session.save(iterator.next());
				}*/
			}
			tx.commit();
		}catch(HibernateException e){
			e.printStackTrace();
			tx.rollback();
		}finally{
			session.close();
		}
		//this.getHibernateTemplate().saveOrUpdateAll(roleList);
		return;
	}
}

这里循环写入数据库,可以使用Spring自动注入的事务管理,更方便简洁,可惜我这里的框架不知道为什么,只要注入事务管理就报错,TT正在查找问题所在!哭........

这样Excel文件的读取以及数据库的写入就实现了!

作者:暗影部落
专注于软件技术和互联网安全

发表评论