如题:文件信息的批量导入......
项目中经常会遇到客户的一些单表信息的数据批量导入,也就是提供定制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文件的读取以及数据库的写入就实现了!