@Controller
@RequestMapping(path = "/excel")
public class ImportController {
@Autowired
private UserRepository userRepository;
@GetMapping(path = "/import")
@ResponseBody
public void Import() throws IOException{
File file = new File("C:\\Users\\zxg\\Desktop\\测试2003.xls"); //实际这个路径由前端传后台
FileInputStream fis = new FileInputStream(file);
Workbook wb = null;
try {
if(isExcel2003(file.getPath())){
System.out.println("2003版本Excel: .xls结尾");
wb = new HSSFWorkbook(fis); //创建2003版本Excel工作簿对象
}else if (isExcel2007(file.getPath())){
System.out.println("2007版本Excel: .xlsx结尾");
wb = new XSSFWorkbook(fis); //创建2007版本Excel工作簿对象
}else {
System.out.println("未知版本的Excel !!!");
}
Sheet sheet = wb.getSheetAt(0); //获取第1个工作表
for(int i=1;i<=sheet.getLastRowNum();i++){//循环Excel文件的i=1行开始
User user = new User();
Row row = sheet.getRow(i); //获取第i行
Cell cell1 = row.getCell(0); //获取第1个单元格的数据
cell1.setCellType(Cell.CELL_TYPE_STRING); //设置Cell的类型为String类型
Cell cell2 = row.getCell(1); //获取第2个单元格的数据
cell2.setCellType(Cell.CELL_TYPE_STRING);
user.setName(cell1.getStringCellValue());
user.setEmail(cell2.getStringCellValue());
System.out.println("第一单元格: " + cell1.getStringCellValue());
System.out.println("第二单元格: " + cell2.getStringCellValue());
userRepository.save(user); //保存
}
} catch (Exception e) {
e.printStackTrace();
}finally {
fis.close();
}
}
public static boolean isExcel2003(String filePath)
{
return filePath.matches("^.+\\.(?i)(xls)$");
}
public static boolean isExcel2007(String filePath)
{
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}