开始准备
搭建一个SpringBoot+MyBatisPlus 的项目工程。
1.SpringBoot
2.MyBatisPlus
3.Swagger2(接口测试)
引入相关的依赖包
pom.xml
这里我只说一下关键的依赖
<!--Mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>2.3</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>2.3</version>
</dependency>
<!--excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--Swagger2-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.9.3</version>
</dependency>
1、Entity层
这是里使用了lombok，省略了getset方法。
public class Profession {
@TableId(value = "profession_id",type = IdType.AUTO)
private Integer professionId;
private String professionName;
private String apartment;
private String school;
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
private Date createTime;
@Override
public String toString() {
return "Profession{" +
"professionId=" + professionId +
", professionName='" + professionName + '\'' +
", apartment='" + apartment + '\'' +
", school='" + school + '\'' +
", createTime=" + createTime +
'}';
}
}
2、Dao层
@Mapper
public interface ProfessionMapper extends BaseMapper<Profession> {
}
3、Service层（稍微偷了点懒，嘘~）
@Service
public class ProfessionService {
@Resource
private ProfessionMapper professionMapper;
/**
* 批量导入专业信息
*
* @param file
*/
public Map<String, Object> importProfession(MultipartFile file) {
Map<String, Object> map = new HashMap<>();
// 获取文件的名称
String fileName = file.getOriginalFilename();
System.out.println(fileName);
// 获取文件的后缀名
String pattern = fileName.substring(fileName.lastIndexOf(".") + 1);
System.out.println(pattern);
List<List<String>> listContent = new ArrayList<>();
String message = "导入成功";
try {
if (file != null) {
//文件类型判断
if (!ExcelUtil.isEXCEL(file)) {
message = "该文件不是excel文件";
} else {
listContent = ExcelUtil.readExcelContents(file, pattern);
//文件内容判断
if (listContent.isEmpty()) {
message = "表格内容为空";
} else {
// 循环遍历
for (int i = 0; i < listContent.size(); i++) {
Profession profession = new Profession();
// 读取excel表格中的数据
String professionName = listContent.get(i).get(0);
String apartment = listContent.get(i).get(1);
String school = listContent.get(i).get(2);
//赋值
profession.setProfessionName(professionName);
profession.setApartment(apartment);
profession.setSchool(school);
// 插入数据
professionMapper.insertSelective(profession);
}
}
}
} else {
message = "未选择文件";
}
} catch (Exception e) {
e.printStackTrace();
}
map.put("code", 200);
map.put("msg", message);
map.put("data", fileName);
return map;
}
}
4、Controller层
@Api(tags = "上传接口")
@Controller
@RequestMapping("uploadApi")
public class UploadController {
@Resource
private ProfessionService professionService;
@ApiOperation(value = "读取excel")
@PostMapping("/uploadExcel")
@ResponseBody
public Map<String, Object> uploadExcel(@RequestParam("file") MultipartFile file) {
return professionService.importProfession(file);
}
}
5、ExcelUtil工具类
/**
* 根据文档格式返回相应的文档对象
* @param file
* @param pattern 文档格式: xls, xlsx
* @return
*/
public static Workbook readExcel(MultipartFile file, String pattern) {
//文档对象
Workbook workbook = null;
if (file != null) {
try {
//获取输入流
InputStream is = file.getInputStream();
if ("xls".equals(pattern)) {
//2003版格式-xls
return workbook = new HSSFWorkbook(is);
} else if ("xlsx".equals(pattern)) {
//2007及以上版本格式-xlsx
return workbook = new XSSFWorkbook(is);
} else {
return null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
return workbook;
}
/**
* 判断上传的文件是否是EXCEL文件
* @param file
* @return
*/
public static Boolean isEXCEL(MultipartFile file) {
if (file != null) {
//文件名
String fileName = file.getOriginalFilename();
//文件后缀
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
//转小写
suffix = suffix.toLowerCase();
if ("xls".equals(suffix) || "xlsx".equals(suffix)) {
return true;
}
}
return false;
}
public static List<List<String>> readExcelContents(MultipartFile file, String pattern) {
List<List<String>> listRow = new ArrayList<>();;
//文档对象
Workbook workbook = null;
//表格对象
Sheet sheet = null;
//非空和文件格式判断
if (isEXCEL(file)) {
workbook = readExcel(file, pattern);
}
if (workbook != null) {
//获取文档首个表格
sheet = workbook.getSheetAt(0);
//获取最大行数
int rowNum = sheet.getPhysicalNumberOfRows();
//行对象
Row row = null;
//单元格数据
String cellData = null;
//跳过第一行标题栏
for (int i = 1; i < rowNum; i++) {
row = sheet.getRow(i);
List<String> listCell = new ArrayList<>();
if (StringUtils.isBlank(row.getCell(0).toString())){
break;
}
// ------------------------这边需要修改------------------------
//遍历列 --- --- 3列数据
for (int j = 0; j < 3; j++){
cellData = row.getCell(j).toString();
listCell.add(cellData);
}
// --------------------------------------------------------
listRow.add(listCell);
}
}
return listRow;
}
6、测试截图
6.1 Excel文件内容
Excel文件内容.png
6.2 测试
测试图片.png
6.3 数据库表截图
数据库表截图.png
