选择excel文件并写入数据库

最新推荐文章于 2024-05-31 10:46:10 发布

听听相赫怎么说

最新推荐文章于 2024-05-31 10:46:10 发布

阅读量121

点赞数

文章标签： java

本文链接：https://blog.csdn.net/weixin_46969401/article/details/120994553

版权

package com.example.excel.controller;

import com.example.excel.entity.Student;
import com.example.excel.service.StudentService;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.util.UUID;

@Controller
@RequestMapping("/stu")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping("/html")
    public String go() {
        return "/upload";
    }

    @RequestMapping("/upload")
    public String upload(@RequestParam("test") MultipartFile file) throws IOException, InvalidFormatException {
        if (file.getOriginalFilename().isEmpty()) {
            return "/fail";
        }
        String originalFilename = file.getOriginalFilename();
        String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));
        String name = UUID.randomUUID().toString().replaceAll("-", "");
        String fileName = name + suffix;
        String newPath = StudentController.class.getResource("/").getPath();
        this.uploadFile(file, newPath.substring(1), fileName);
        File file1 = new File(newPath + fileName);
        Workbook sheets = WorkbookFactory.create(file1);
        Sheet sheet = sheets.getSheetAt(0);
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            Student student = new Student();
            String[] str = new String[row.getLastCellNum()];
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);//取得第j列数据
                cell.setCellType(CellType.STRING);
                str[j] = cell.getStringCellValue().trim();
            }
            student.setSName(str[0]);
            student.setSAge(Integer.parseInt(str[1]));
            student.setSSex(Integer.parseInt(str[2]));
            this.studentService.insert(student);
        }
        sheets.close();
        if (file1.exists()) {
            file1.delete();
        }
        return "/success";
    }

    public String uploadFile(MultipartFile file, String path, String fileName) throws IOException {
        // 判断路径是否存在，不存在则创建
        File filePath = new File(path);
        if (!filePath.exists()) {
            filePath.mkdirs();
        }

        String newFilePath = path + "\\" + fileName; //新文件的路径
        file.transferTo(new File(newFilePath));  //将传来的文件写入新建的文件
        return newFilePath;
    }

}

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>文件上传</title>
</head>
<body>

    <form action="/stu/upload" method="POST" enctype="multipart/form-data">
        文件：<input type="file" name="test" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"/>
        <input type="submit"/>
    </form>

</body>

</html>

听听相赫怎么说

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
选择excel文件并写入数据库

package com.example.excel.controller;import com.example.excel.entity.Student;import com.example.excel.service.StudentService;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.*;import org.springframe
复制链接

扫一扫