Struts2实现Excel导入

最新推荐文章于 2019-10-25 16:46:54 发布

iteye_16140

最新推荐文章于 2019-10-25 16:46:54 发布

阅读量331

点赞数

分类专栏： javaweb 文章标签： excel struts

javaweb 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

除Struts2必须的jar包外，还需要jar包：poi-3.7-beta-20100620.jar

首先是jsp代码实现上传：
<body>
<s:form id="form1" name="form1" action="roleFileUpload.action" method="post" enctype="multipart/form-data" theme="simple">
<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>
[/align]
</s:form>
</body>

其次是Struts.xml配置文件：
<action name="roleFileUpload" class="roleFileAction" method="loadRoleFile">
<result name="success">/page/role/roleallinput.jsp</result>
<result name="input">/page/role/roleallinput.jsp</result>
</action>

然后是Action类实现：
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";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
//生成上传的文件对象
File target = new File(targetDirectory,uploadFileFileName);
//如果文件已经存在，则删除原有文件
if(target.exists()){
target.delete();
}
//复制file对象，实现上传
try {
FileUtils.copyFile(uploadFile, target);

} catch (IOException e) {
e.printStackTrace();
}
loadRoleInfo();
return SUCCESS;
}

/**
* 把Excele表读出的数据，组装成一个List,统一导入数据库
* @param uploadFileFileName
*/
public void loadRoleInfo(){

String directory = "/upload";
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);
}
for(PtRoleInfo pt:roleList){
fileLoadDao.roleInfotoDB(pt);

}
}catch(IOException e){
e.printStackTrace();
}
}

}
最后在roleInfoToDB方法中需要使用‘事务Transaction’来进行批量插入，确保插入失败的能完全回滚。这样Excel文件的读取以及数据库的写入就实现了