当前位置 博文首页 > 水蓝色的秘密的博客：springboot+mybatis-plus通过excel表导入数
以下为具体代码：
controller类代码
@Autowired
private SzyPanoramaService szyPanoramaService;
@Autowired
private ImportExcelService excelService;
/**
* 通过excel导入数据库
* @param file
* @return
* @throws Exception
*/
@RequestMapping(value = "/upload")
public ResponseUtils excelupload(@RequestParam(value = "file", required = true) MultipartFile file) throws Exception{
//获取文件名
String fileName = file.getOriginalFilename();
//获取输入流
InputStream inputStream = file.getInputStream();
List<Map<String,String>> mapList = excelService.importExcel(inputStream, fileName);
szyPanoramaService.savePanorama(mapList);
return ResponseUtils.successmsg("返回数据成功",null);
}
新建一个ImportExcelService类
@Service
public class ImportExcelService {
public List<Map<String,String>> importExcel(InputStream inputStream, String fileName) throws Exception {
List<Map<String,String>> list = new ArrayList<>();
boolean notNull = false;
List<SzyPanorama> userList = new ArrayList<SzyPanorama>();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new Exception("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
Sheet sheet = wb.getSheetAt(0);
if(sheet!=null){
notNull = true;
}
SzyPanorama panorama;
//定义一个map存放需要保存的字段
HashMap<Integer,String> titleMap = new HashMap<>();
titleMap.put(0,"panoid");
titleMap.put(1,"smx");
titleMap.put(2,"smy");
titleMap.put(3,"panoadderss");
titleMap.put(4,"panotype");
titleMap.put(5,"panorama");
titleMap.put(6,"markerlist");
titleMap.put(7,"panoclickcount");
titleMap.put(8,"panosocount");
//获取总列数
Row firstRow = sheet.getRow(0);
int cells = firstRow.getPhysicalNumberOfCells();
for (int r = 1; r <=sheet.getLastRowNum(); r++) {
// getRow(index)，index是从0开始的，1相当于第二行
Row row = sheet.getRow(r);
if (row == null){
continue;
}
panorama = new SzyPanorama();
//用来存放一行的数据
Map<String,String> map = new HashMap<>();
for (int j = 0; j< cells; j++){
if (row.getCell(j) != null){
row.getCell(j).setCellType(CellType.STRING);
if (row.getCell(j).getStringCellValue() != null && !row.getCell(j).getStringCellValue().equals("")){
String result = row.getCell(j).getStringCellValue();
map.put(titleMap.get(j),result);
}else {
map.put(titleMap.get(j),null);
}
}else {
map.put(titleMap.get(j),null);
}
}
list.add(map);
}
System.out.println(list);
return list;
}
}
SzyPanoramaService的代码
public void savePanorama(List<Map<String,String>> mapList) ;
SzyPanoramaServiceImpl的代码内容
/**
* 导入信息
* @param mapList
*/
@Transactional(rollbackFor = {RuntimeException.class, Error.class})
public void savePanorama(List<Map<String,String>> mapList) {
for (Map<String,String> map : mapList){
// 判断学生是否存在
System.out.println("ID是否存在"+map.get("panoid"));
SzyPanorama result = szyPanoramaService.getById(map.get("panoid"));
if (result != null) {
try {
throw new Exception("panoid"+map.get("panoid")+"已存在,导入失败");
} catch (Exception e) {
e.printStackTrace();
}
}
SzyPanorama szyPanorama = new SzyPanorama();
if(!StringUtils.isEmpty(map.get("panoid"))){
System.out.println("存入id"+map.get("panoid"));
szyPanorama.setPanoid(map.get("panoid"));
}
if(!StringUtils.isEmpty(map.get("smx"))){
System.out.println("存入smx"+map.get("smx"));
szyPanorama.setSmx(map.get("smx"));
}
if(!StringUtils.isEmpty(
