导入相关的依赖
(该博文的版本比较低)
gradle
//POI 用于office文件处理
compile group: 'org.apache.poi', name: 'poi', version: '3.17'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17'
maven
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.1.7</version>
</dependency>
这里需要注意版本号,之前使用了4.0+的版本出了一点问题
Excel表格的读取
Excel表格格式
注意文件的后缀的xlsx(xls我没试过,作者有点懒)
Controller
@PostMapping("/excel_read")
public Result registerByExcel(MultipartFile file){
return fileService.registerByExcel(file);
}
Service实现
@Override
public Result registerByExcel(MultipartFile file) {
//现将文件上传到本地
if (file==null){
//自定义异常类
throw new MyException(CodeMsg.FILE_EMPTY_ERROR);
}
String pathFile = registerFilePath+file.getOriginalFilename();
try{
byte[] bytes = file.getBytes();
Path path = Paths.get(pathFile);
Files.write(path,bytes);
} catch (IOException e) {
e.printStackTrace();
log.error("注册文档上传失败");
throw new MyException(CodeMsg.FILE_UPLOAD_FAILED);
}
//读取Excel表格存入数据库
List<RegisterUser> list = ExcelUtil.getUsersByExcel(pathFile);
List<User> users = new ArrayList<>();
for (RegisterUser registerUser:list
) {
User user = new User(registerUser);
//如果用户已经存在则不需要加入List当中在进行注册 -- 检测用户在数据库中是否存在
if (userFormMapper.selectUserByUserId(user.getUserId())==null){
users.add(user);
}else {
log.error("工号为{}的用户已经存在",registerUser.getUserId());
}
}
//将用户数组插入数据库
if (users.size()==0){
return Result.error(CodeMsg.ALL_USERS_HAS_EXISTED);
}else {
userFormMapper.registerMultiUser(users);
}
return Result.successData(users);
}
Util类,读取Excel,将表格转化为对象集合
public static List<RegisterUser> getUsersByExcel(String fileName){
ArrayList<RegisterUser> list = new ArrayList<>();
try{
//1.获取文件输入流
InputStream inputStream = new FileInputStream(fileName);
//2、获取Excel工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheetAt = workbook.getSheetAt(0);
//4、循环读取表格数据
for (Row row : sheetAt) {
//首行（即表头）不读取
if (row.getRowNum() == 0) {
continue;
}
//从Excel中读取值
Long userId = Long.valueOf(row.getCell(0).getStringCellValue());
String username = row.getCell(1).getStringCellValue();
String position = row.getCell(2).getStringCellValue();
String office = row.getCell(3).getStringCellValue();
//
RegisterUser registerUser = new RegisterUser();
registerUser.setUserId(userId);
registerUser.setUsername(username);
registerUser.setPosition(position);
registerUser.setOffice(office);
list.add(registerUser);
}
workbook.close();
} catch (IOException e) {
e.printStackTrace();
throw new MyException(CodeMsg.XLS_FILE_READ_ERROR);
} catch (IllegalStateException | NumberFormatException e){
e.printStackTrace();
throw new MyException(CodeMsg.XLS_FILE_FORMAT_ERROR);
}catch (Exception e) {
throw new MyException(CodeMsg.XLS_FILE_FORMAT_ERROR);
}
return list;
}
Excel转化对象
@Data
public class RegisterUser {
private Long userId;
private String username;
/**
* 教师职称
*/
private String position;
/**
* 教师所属教研室
*/
private String office;
}
文件导出
Controller
@GetMapping("/file/getAllocationExcel")
public Result getAllocationExcel(HttpServletResponse response){
return fileService.getAllocationExcel(response);
}
Service
/**
* 导出Excel 项目分配信息
* @param response
*/
@Override
public Result getAllocationExcel(HttpServletResponse response) {
//管理员获取到所有的项目分配信息
List<AllocationInfo2> list = allocationInfoMapper.getAllAllocationInfo();
// 1.创建HSSFWorkbook，一个HSSFWorkbook对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
// 2.在workbook中添加一个sheet,对应Excel文件中的sheet(工作栏)
XSSFSheet sheet = wb.createSheet("sheet1");
//3.设置格式 基本单位是1/256个字符
sheet.setColumnWidth(0,256*10);
sheet.setColumnWidth(1,256*14);
sheet.setColumnWidth(2,256*19);
sheet.setColumnWidth(3,256*15);
sheet.setColumnWidth(4,156*50);
//3.1设置字体居中
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
// 4.设置表头，即每个列的列名
String[] title = {"项目编号","项目类别","项目负责人","项目说明","项目分配的成员信息"};
// 4.1创建第一行
XSSFRow row = sheet.createRow(0);
// 此处创建一个序号列
row.createCell(0).setCellValue("序号");
for (int i = 0; i < title.length; i++) {
// 给列写入数据,创建单元格，写入数据
row.createCell(i+1).setCellValue(title[i]);
}
//写入正式数据
for (int i = 0; i < list.size(); i++) {
//创建行
row = sheet.createRow(i+1);
//设置行高
row.setHeight((short) (16*180));
// 序号
row.createCell(0).setCellValue(i+1);
//Excel读入
row.createCell(1).setCellValue(list.get(i).getProjectId());
row.createCell(2).setCellValue(list.get(i).getCategory());
row.createCell(3).setCellValue(list.get(i).getLeader());
row.createCell(4).setCellValue(list.get(i).getInstruction());
}
//文件传输
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=AllocationInfo.xlsx");
try {
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
return null;
}catch (IOException e){
throw new MyException(CodeMsg.FILE_DOWNLOAD_ERROR);
}
}
VO
@Data
public class AllocationInfo2 {
//@ApiModelProperty(value = "项目编号")
private String projectId;
// @ApiModelProperty(value = "项目类别")
private String category;
//@ApiModelProperty(value = "项目负责人")
private String leader;
// @ApiModelProperty(value = "项目说明")
private String instruction;
}
有问题欢迎指点
参考博客:https://www.jianshu.com/p/03cc99279d9e
文件读取忘了是哪位老哥的博客了,对不住了,有问题请私信
