导入表格添加档案信息一键导入数据库

最新推荐文章于 2022-12-02 10:55:35 发布

FairytailRose

最新推荐文章于 2022-12-02 10:55:35 发布

阅读量423

点赞数

分类专栏： JAVA 文章标签：导入表格

本文链接：https://blog.csdn.net/FairytailRose/article/details/93887476

版权

JAVA 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

导入表格添加档案信息一键导入数据库

与导出工作表使用相同的依赖项

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

由于页面要上传文件,所以使用form表单提交
Controller层代码

package com.nec.controller;
import com.nec.bean.Archives;
import com.nec.service.ArchivesService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Controller
public class ArchivesController {
    @Autowired
    private ArchivesService archivesService;

    @RequestMapping(value = "/archivesadd")
    public String archivesadd(@RequestParam(value = "files") MultipartFile file , HttpServletRequest request ) throws IOException {
        if(file.getSize()>0){
            //先将上传的文件储存到本地
            String originalFilename = file.getOriginalFilename();
            String realPath = request.getServletContext().getRealPath("upload");
            String filePath = realPath+"/"+originalFilename;
            File file1 = new File(filePath);
            file.transferTo(file1);
            //创建字节输入流,将file1读取到内存中
            InputStream inputStream = new FileInputStream(file1);
            //创建工作表
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            //创建sheet
            for (int i = 0; i < hssfWorkbook.getNumberOfSheets() ; i++) {
                //获取sheet
                HSSFSheet sheet = hssfWorkbook.getSheetAt(i);
                if (sheet==null){
                    continue;
                }
                //通过sheet获取每一行的记录
                List<Archives> list = new ArrayList<>();

                for (int j = 0; j < sheet.getLastRowNum() ; j++) {
                    //读取每一行
                    HSSFRow row = sheet.getRow(j);
                    if(row==null){
                        continue;
                    }
                    //将每一行的属性赋值给Archives对象
                    Archives archives = new Archives();
                    archives.setDnum(row.getCell(0).getStringCellValue());
                    archives.setLandline(row.getCell(1).getStringCellValue());
                    archives.setSchool(row.getCell(2).getStringCellValue());
                    archives.setZhuanye(row.getCell(3).getStringCellValue());
                    archives.setSosperson(row.getCell(4).getStringCellValue());
                    archives.setBiyedate(row.getCell(5).getDateCellValue());
                    archives.setZzmm(row.getCell(6).getStringCellValue());
                    archives.setMinzu(row.getCell(7).getStringCellValue());
                    archives.setXueli(row.getCell(8).getStringCellValue());
                    archives.setEmail(row.getCell(9).getStringCellValue());
                    archives.setEmpFk((int)row.getCell(10).getNumericCellValue());
                    archives.setRemark(row.getCell(11).getStringCellValue());
                    archives.setHirdate(row.getCell(12).getDateCellValue());
                    list.add(archives);
                }
                if(list.size()>0){
                    int a = archivesService.archivesadd(list);
                    return "archives";
                }
            }
        }
        return "archives-add";
    }
}