注意：上传的excel文件的模板是否正确
jsp页面:
<s:form namespace="user" enctype="multipart/form-data">
<s:token name="user_excelcreate"/>
<div class="navigate_header">当前位置:excel导入</div>
<table class="table_frame" width="100%">
<thead>
<tr><th colspan="4">信息上传</th></tr>
</thead>
<tr>
<td><s:file label="上传" theme="simple" name="upload" id="upload"/></td>
<td><s:submit name="action:user_doExcelSwitch" value="上传"/></td>
</tr>
</table>
<font color="red">注：请确认上传文件的格式为excel文件，且后缀为.xls</font>
</s:form>
action:
public class User implements ServletRequestAware{
private SimpleDateFormat sdf ;
// 封装单个上传文件域的属性
private File upload;
// 封装单个上传文件名的属性
private String uploadFileName;
public String doExcelSwitch(){
List<User> userList = new ArrayList<User>();
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String createtime = sdf.format(new Date());
//读取数据，放入列表
readFromExcel(UploadUtil.upload(uploadFileName, upload), userList);
//以下是判断数据库中是否已经存在此用户，若存在则不导入，若不存在则导入
//判断读取excel有没有遇到异常
if(getActionErrors().isEmpty()){
//用户学号number
String number= request.getParameter("number");
for (int i = 0; i < userList.size(); i++) {
userList.get(i).setNumber(number);
}
//读取所有用户列表
List<String> allUserList = userService.selectNumberByParams(params);
//这里需要调用dao层的方法selectNumberByParams（params）查询出所有数据
//需要变更的用户列表
List<User> updateList = new ArrayList<User>();
for (int i = 0; i < userList.size(); i++) {
//如果数据库存在该用户则为变更用户
if(allUserList.contains(userList.get(i).getNumber())){
updateList.add(userList.get(i));
userList.remove(i);
i --;
}
}
int flag1 = 0;
int flag2 = 0;
if(flag1 == -3 || flag2 == -3){
addActionError("信息导入失败！请联系管理员");
}else{
addActionMessage("信息导入成功！共导入" + userList.size() + "条，更新" + updateList.size() + "条");
}
}else{
addActionError("文件内数据有问题请修正后再上传！");
}
return "success";
}
//处理excel数据
private void readFromExcel(String path, List<User> userList){
if(path == null || "".equals(path.trim())){
addActionError("文件不能为空！");
}else if(!getUploadFileName().substring(getUploadFileName().lastIndexOf(".")+1).equals("xls")){
addActionError("请上传excel格式文件！");
}else{
InputStream is = null;
Workbook workbook;
Sheet sheet;
try {
path = path.replace("\\", "/");
is = new FileInputStream(path);
try {
workbook = WorkbookFactory.create(is);
for (int j = 0; j < workbook.getNumberOfSheets(); j++) { //这里可以通过设置j的初始值来设定从第几行开始读取
sheet = workbook.getSheetAt(j);
int curRowIndex = 0;
for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
curRowIndex++;
if (curRowIndex < 2) {
rit.next();
continue;
}
Row row = (Row) rit.next();
if(row.getCell(4).toString().contains("合计")){
break;
}
//设置user的字段值
User user = new User();
//这里的row.getCell()是获得这一行的第几列的数据，注：列从零开始
user.setNumber(row.getCell(0).toString());
user.setName(row.getCell(1).toString());
user.setAge(new BigDecimal(row.getCell(2).toString()).intValue());
String s = row.getCell(3).toString();
if("男".equals(s.trim())){
user.setSex("1");
}else {
user.setSex("2");
}
userList.add(user);
}
}
} catch (InvalidFormatException e) {
addActionError("数据类型匹配错误！");
}
} catch (IOException e) {
addActionError("io 异常");
}
//关闭流，并删除临时文件
finally{
workbook = null;
sheet = null;
if(is != null){
try {
is.close();
} catch (IOException e) {
}
File file = new File(path.replace("\\", "/"));
file.delete();
}
}
}
}
public SimpleDateFormat getSdf() {
return sdf;
}
public void setSdf(SimpleDateFormat sdf) {
this.sdf = sdf;
}
public File getUpload() {
return upload;
}
public void setUpload(File upload) {
this.upload = upload;
}
public String getUploadFileName() {
return uploadFileName;
}
public void setUploadFileName(String uploadFileName) {
this.uploadFileName = uploadFileName;
}
UploadUtil:
public class UploadUtil {
// 上传单个文件
public static String upload(String uploadFileName, File upload) {
String fullPathName = "";
try {
// 以服务器的文件保存地址和原文件名建立上传文件输出流
String url = ServletActionContext.getServletContext().getRealPath(
"\\upload")
+ "\\temp\\";
String fileName = new SimpleDateFormat("MMddHHmmssSSS")
.format(new Date())
+ uploadFileName.substring(uploadFileName.lastIndexOf(".") - 0);
fullPathName = url + fileName;
File file = new File(url);
if (!file.exists()) {
file.mkdirs();
}
FileOutputStream fos = new FileOutputStream( fullPathName );
// 以上传文件建立一个文件上传流
FileInputStream fis = new FileInputStream( upload );
// 将上传文件的内容写入服务器
byte[] buffer = new byte[1024];
int len = 0;
while ((len = fis.read(buffer)) > 0) {
fos.write(buffer, 0, len);
}
} catch (Exception e) {
e.printStackTrace();
}
return fullPathName;
}
}
|
