springboot导入excel到mysql


@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)$");
    }

}
原文地址:https://www.cnblogs.com/MagicAsa/p/11060014.html