最近有做一个类似百度网盘的项目,其中有用到excel文件上传和下载的功能,使用的poi来实现文件的上传和下载的,
先列一下实现的功能,再放代码.
1.模板类的导出;
2.excel文件的导出;
3.excel文件的导入;
pom.xml引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>20.0</version>
</dependency>
//用户excel模板导出
@ResponseBody
@RequestMapping(value = "/exportExcelTemplet", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
public Result exportTemplet(HttpServletRequest request) throws IOException, BusinessException {
String url = userService.exportTemplet(request);
return ResultGenerator.genSuccessResult(url);
}
//用户excel批量导出
@ResponseBody
@RequestMapping(value = "/exportUserToExcel", method = RequestMethod.POST)
public Result exportUserToExcel(HttpServletRequest request) throws BusinessException {
String url = userService.exportUserToExcel(request);
return ResultGenerator.genSuccessResult(url);
}
//导入Excle表批量新建用户
@ResponseBody
@RequestMapping(value = "/addExcelUser", consumes = "multipart/form-data", method = RequestMethod.POST)
public Result addExcelUser(@RequestParam MultipartFile file, HttpServletRequest request) throws IOException, BusinessException {
List<String> messageList = userService.importExcelUser(file, request);
return ResultGenerator.genSuccessResult(messageList);
}
//用户导入模板导出
@Override
public String exportTemplet(HttpServletRequest request) throws BusinessException {
String fileName = "UserTemplet_" + DateUtils.getTimeStringForFileName() + ".xls";
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet(Constant.USER_EXPORT_TEMPLET_SHEETNAME);
// 设置单元格的格式
setCellStyle(hssfWorkbook);
HSSFRow headRow = sheet.createRow(0);
// 设置表头
this.setHeadRowData2(headRow);
// 将表格导出
for (int i = 1; i < 6; i++) {
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
//用户名称
dataRow.createCell(0).setCellValue("username" + i);
//用户别名
dataRow.createCell(1).setCellValue("别名" + i);
//邮箱
dataRow.createCell(2).setCellValue("test" + i + "@example.com");
//密码
dataRow.createCell(3).setCellValue("123456");
//部门
dataRow.createCell(4).setCellValue("department" + i);
}
//设置列宽
this.setAutoSizeColumn2(sheet);
this.exportFile(hssfWorkbook, fileName, request);
return "/temp/" + fileName;
}
//用户excel批量导入
@Override
public List<String> importExcelUser(MultipartFile file, HttpServletRequest request) throws IOException, BusinessException {
String filename = "WrongUser_" + DateUtils.getTimeStringForFileName() + ".xls";
if (file.isEmpty()) {
throw new BusinessException("请选择需要上传的文件;");
}
boolean flag = true;
int rowCount = 0;
int count = 0;
String fileName = file.getOriginalFilename();
String postfix = fileName.substring(fileName.lastIndexOf("."), fileName.length());
InputStream fileInputStream = file.getInputStream();
Workbook book = null;
if (postfix.equals(".xls")) {
book = new HSSFWorkbook(new POIFSFileSystem(fileInputStream));
} else {
book = new XSSFWorkbook(fileInputStream);
}
Sheet sheet = book.getSheetAt(0);
if (sheet.getLastRowNum() <= 0) {
throw new BusinessException("传入Excel文件没有用户信息!");
}
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet2 = hssfWorkbook.createSheet(Constant.ADD_EXCEL_USER_WRONG_SHEETNAME);
// 设置单元格的格式
setCellStyle(hssfWorkbook);
HSSFRow headRow = sheet2.createRow(0);
// 设置表头
setHeadRowData2(headRow);
// 在基本信息后增加一列“错误信息”
headRow.createCell(5).setCellValue("错误信息");
StringBuffer sb = null;
//分析获取数据
List<Map<String, String>> dataList = this.buildDataList(sheet);
//上传不能超过最大上传最大条数
if (dataList.size() > Constant.MAX_PROCESSING_LINE) {
throw new BusinessException("传入Excel文件数据超过" + Constant.MAX_PROCESSING_LINE + "!请分批导入");
}
String nickname = "";
String anotherName = "";
String email = "";
String password = "";
String department = "";
UserVo userVo = new UserVo();
for (Map<String, String> row : dataList) {
count++;
flag = true;
sb = new StringBuffer();
Map<String, String> map = new HashMap<String, String>();
nickname = MapUtils.getString(row, "nickname");
anotherName = MapUtils.getString(row, "anotherName");
email = MapUtils.getString(row, "email");
password = MapUtils.getString(row, "password");
department = MapUtils.getString(row, "department");
// String lowerUserName = ""; 判断大小写名字
//用户名判断
if (!StringUtils.isEmpty(nickname)) {
Boolean b = nickname.matches("^[a-zA-Z0-9!\"\"#$%&'()*+,-./:;<=>?@\\[\\]\\^_`{}|~\\\\]{4,25}$");
if (!b) {
flag = false;
sb.append("请按规则填写名称(字母数字非特殊符号长度4-25位);");
}
map.put("name", nickname);
userVo.setNickname(nickname);
} else {
flag = false;
sb.append("用户名称不能为空;");
}
//别名校验
if (!StringUtils.isEmpty(anotherName)) {
Boolean b = nickname.matches("^[a-zA-Z0-9!\"\"#$%&'()*+,-./:;<=>?@\\[\\]\\^_`{}|~\\\\]{4,25}$");
if (!b) {
flag = false;
sb.append("请按规则填写名称(字母数字非特殊符号长度4-25位);");
}
map.put("anotherName", anotherName);
userVo.setAnotherName(anotherName);
Integer rows = userDao.selectUserByAnotherNameOrEmail(map);
if (rows != 0) {
flag = false;
sb.append("用户别名已经存在,请更换别名;");
}
} else {
flag = false;
sb.append("别名不能为空;");
}
//邮箱校验
if (StringUtils.isEmpty(email)) {
flag = false;
sb.append("邮箱不能为空;");
} else if (email.length() > 200) {
flag = false;
sb.append("邮箱长度不能超过200;");
} else {
Boolean b = email.matches("^\\w+([-+.]\\w+)*@\\w+([-.]\\w+)*\\.\\w+([-.]\\w+)*$");
if (!b) {
flag = false;
sb.append("请按照邮箱格式正确填写;");
}
map.put("email", email);
userVo.setEmail(email);
Integer rows = userDao.selectUserByAnotherNameOrEmail(map);
if (rows != 0) {
flag = false;
sb.append("邮箱地址已经注册,请更改邮箱;");
}
}
//校验密码
if (StringUtils.isEmpty(password)) {
flag = false;
sb.append("用户密码不能为空;");
} else {
map.put("password", password);
userVo.setPassword(MD5Util.getMD5(password));
}
//部门设置可以为空(不设置)
if (!StringUtils.isEmpty(department)) {
map.put("department", department);
userVo.setDepartment(department);
}
if (flag != true) {
rowCount++;
HSSFRow dataRow = sheet2.createRow(sheet2.getLastRowNum() + 1);
if (!StringUtils.isEmpty(nickname)) {
dataRow.createCell(0).setCellValue(nickname);
} else {
dataRow.createCell(0).setCellValue("");
}
if (!StringUtils.isEmpty(anotherName)) {
dataRow.createCell(1).setCellValue(anotherName);
} else {
dataRow.createCell(1).setCellValue("");
}
if (!StringUtils.isEmpty(email)) {
dataRow.createCell(2).setCellValue(email);
} else {
dataRow.createCell(2).setCellValue("");
}
if (!StringUtils.isEmpty(password)) {
dataRow.createCell(3).setCellValue(password);
} else {
dataRow.createCell(3).setCellValue("");
}
if (!StringUtils.isEmpty(department)) {
dataRow.createCell(4).setCellValue(department);
} else {
dataRow.createCell(4).setCellValue("");
}
// 将错误信息写入
dataRow.createCell(5).setCellValue(sb.toString());
sb = null;
continue;
} else {
//给seafile发送添加用户请求
String url = Constant.USER_URL + email + "/";
try {
Object result = httpService.sendTokenPut(url, map);
log.info("返回数据:" + result);
} catch (Exception e) {
log.error("新增用户错误,参数:" + JSONObject.toJSONString(userVo));
}
userVo.setTotalStorage(Constant.DEFAULT_SHORTAGE);
userDao.createUser(userVo);
}
}
List<String> messageList = new ArrayList<>();
if (rowCount >= 1) {
// 将错误结果导出到结果excel文件中
this.exportFile(hssfWorkbook, filename, request);
// 组装结果显示信息
messageList.add("上传成功" + (count - rowCount) + "条数据");
messageList.add("上传失败" + rowCount + "条数据,详情请查阅上传失败表");
String url = "/temp/" + filename;
messageList.add(url);
} else {
// 组装结果显示信息
messageList.add(count + "条数据全部上传成功");
}
return messageList;
}
//用户excel导出
@Override
public String exportUserToExcel(HttpServletRequest request) throws BusinessException {
String fileName = "User_" + DateUtils.getTimeStringForFileName() + ".xls";
//查询所有的数据(不分页)
List<UserVo> users = userDao.getAllUsers();
if (CollectionUtils.isEmpty(users)) {
throw new BusinessException("用户列表没有数据");
}
this.alterUserVos(users);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet(Constant.DEFAULT_EXPORT_NAME);
// 设置单元格的格式
this.setCellStyle(hssfWorkbook);
// 设置表头
HSSFRow headRow = sheet.createRow(0);
this.setHeadRowData(headRow);
for (UserVo user : users) {
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
//用户名称
if (!StringUtils.isEmpty(user.getNickname())) {
dataRow.createCell(0).setCellValue(user.getNickname());
}
//用户别名
if (!StringUtils.isEmpty(user.getAnotherName())) {
dataRow.createCell(1).setCellValue(user.getAnotherName());
}
//邮箱
if (!StringUtils.isEmpty(user.getEmail())) {
dataRow.createCell(2).setCellValue(user.getEmail());
}
//状态
if (user.getIsActive() != null) {
if (user.getIsActive() == Constant.DEFAULT_CODE) {
dataRow.createCell(3).setCellValue("激活");
} else {
dataRow.createCell(3).setCellValue("锁定");
}
}
//角色
if (user.getIsStaff() != null) {
if (user.getIsActive() == Constant.DEFAULT_CODE) {
dataRow.createCell(4).setCellValue("管理员");
} else {
dataRow.createCell(4).setCellValue("普通用户");
}
}
//已用空间
if (!StringUtils.isEmpty(user.getUsedStorage())) {
dataRow.createCell(5).setCellValue(user.getUsedStorage());
}
//容量
if (!StringUtils.isEmpty(user.getTotalStorage())) {
dataRow.createCell(6).setCellValue(user.getTotalStorage());
}
//创建时间
if (!StringUtils.isEmpty(user.getCreateTime())) {
dataRow.createCell(7).setCellValue(user.getCreateTime());
}
//上次登陆时间
if (!StringUtils.isEmpty(user.getLastTime())) {
dataRow.createCell(8).setCellValue(user.getLastTime());
}
}
//设置列宽
this.setAutoSizeColumn(sheet);
//将表格导出
this.exportFile(hssfWorkbook, fileName, request);
return "/temp/" + fileName;
//设置Excel单元格的风格
private void setCellStyle(HSSFWorkbook hssfWorkbook) {
CellStyle cellStyle = hssfWorkbook.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}
//设置excel头信息
public void setHeadRowData(HSSFRow headRow) {
headRow.createCell(0).setCellValue("用户名称");
headRow.createCell(1).setCellValue("用户别名");
headRow.createCell(2).setCellValue("邮箱");
headRow.createCell(3).setCellValue("状态");
headRow.createCell(4).setCellValue("角色");
headRow.createCell(5).setCellValue("已用空间(MB)");
headRow.createCell(6).setCellValue("容量(MB)");
headRow.createCell(7).setCellValue("创建时间");
headRow.createCell(8).setCellValue("上次登陆时间");
}
//设置excel模板头信息
public void setHeadRowData2(HSSFRow headRow) {
headRow.createCell(0).setCellValue("用户名称");
headRow.createCell(1).setCellValue("用户别名");
headRow.createCell(2).setCellValue("邮箱");
headRow.createCell(3).setCellValue("密码");
headRow.createCell(4).setCellValue("部门");
}
//设置每列宽度
public void setAutoSizeColumn(HSSFSheet sheet) {
sheet.autoSizeColumn((short) 0); //调整第一列宽度
sheet.autoSizeColumn((short) 1); //调整第二列宽度
sheet.autoSizeColumn((short) 2); //调整第三列宽度
sheet.autoSizeColumn((short) 3); //调整第四列宽度
sheet.autoSizeColumn((short) 4); //调整第四列宽度
sheet.autoSizeColumn((short) 5); //调整第四列宽度
sheet.autoSizeColumn((short) 6); //调整第四列宽度
sheet.autoSizeColumn((short) 7); //调整第四列宽度
sheet.autoSizeColumn((short) 8); //调整第四列宽度
}
//设置每列宽度
public void setAutoSizeColumn2(HSSFSheet sheet) {
sheet.autoSizeColumn((short) 0); //调整第一列宽度
sheet.autoSizeColumn((short) 1); //调整第二列宽度
sheet.autoSizeColumn((short) 2); //调整第三列宽度
sheet.autoSizeColumn((short) 3); //调整第四列宽度
sheet.autoSizeColumn((short) 4); //调整第四列宽度
}
//将数据导出到excel表格中
private void exportFile(HSSFWorkbook hssfWorkbook, String fileName, HttpServletRequest request)
throws BusinessException {
//本地测试使用
//String realPath = "D:\\/temp";
String realPath = request.getSession().getServletContext().getRealPath("/temp");
if (StringUtils.isEmpty(realPath)) {
throw new BusinessException("服务器下载路劲获取失败");
}
File filePackage = new File(realPath);
if (!filePackage.exists()) {
filePackage.mkdirs();
}
String excelPath = realPath + "/" + fileName;
File file = new File(excelPath);
FileOutputStream fout = null;
try {
fout = new FileOutputStream(file);
if (null != fout) {
hssfWorkbook.write(fout);
fout.close();
}
} catch (Exception e) {
if (null != fout) {
try {
fout.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
throw new BusinessException(e.getMessage());
}
}
//文件上传数据分析
private List<Map<String, String>> buildDataList(Sheet sheet) {
List<Map<String, String>> dataList = new ArrayList<>();
Long currentStartTime = System.currentTimeMillis();
for (Row row : sheet) {
if (null == row) {
continue;
}
log.info("开始遍历行信息。。。。。");
int rowNum = row.getRowNum();
if (rowNum == 0) {
continue;
}
int num = 0;
Cell cell0 = row.getCell(0);
Cell cell1 = row.getCell(1);
Cell cell2 = row.getCell(2);
Cell cell3 = row.getCell(3);
Cell cell4 = row.getCell(4);
List<Cell> cells = new ArrayList<Cell>();
cells.add(cell0);
cells.add(cell1);
cells.add(cell2);
cells.add(cell3);
cells.add(cell4);
for (Cell c : cells) {
if (null == c || c.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
num++;
}
}
// 跳过空行,如果某行前4列都是空,则直接跳出该行
if (num == 4) {
continue;
}
String nickname = "";
String anotherName = "";
String email = "";
String password = "";
String department = "";
if (null != cell0) {
cell0.setCellType(Cell.CELL_TYPE_STRING);
nickname = cell0.getStringCellValue();
}
if (null != cell1) {
cell1.setCellType(Cell.CELL_TYPE_STRING);
anotherName = cell1.getStringCellValue();
}
if (null != cell2) {
cell2.setCellType(Cell.CELL_TYPE_STRING);
email = cell2.getStringCellValue();
}
if (null != cell3) {
cell3.setCellType(Cell.CELL_TYPE_STRING);
password = cell3.getStringCellValue();
}
if (null != cell4) {
cell4.setCellType(Cell.CELL_TYPE_STRING);
department = cell4.getStringCellValue();
}
Map<String, String> dataMap = new HashMap<>();
dataMap.put("nickname", nickname);
dataMap.put("anotherName", anotherName);
dataMap.put("email", email);
dataMap.put("password", password);
dataMap.put("department", department);
dataList.add(dataMap);
}
Long currentEndTime = System.currentTimeMillis();
log.info("解析数据时间:" + (currentEndTime - currentStartTime));
return dataList;
}