Skip to content

Java文件解析Excel

发布时间:

第三方提供的有:Apache POI、JXL、Alibaba EasyExcel,本文主要整理Apache POI对Excel文件的解析与生成。 - Apache POI库maven依赖

js
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
<!--        xls-07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
<!--        poi结束-->
<!--        日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.4</version>
        </dependency>
<!--        test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>
   

前端

js
 <form th:action="${submiturl}" method="post" enctype="multipart/form-data">
       <input type="file" name="uploadFile" class="file" id="fileField" size="28" value="请选择文件"/> 
        <input type="submit" class="btn1" value="上传"/> 
 </form>   
   

实体类

js
public class RenYuan {
    public String ShenFenZheng ;
    public String year;
    public String content;
}
   

后端

js
 @PostMapping("/renyuan_upload")
    public String renyuan_upload(MultipartFile uploadFile, HttpServletRequest req, Map<String, Object> map, RedirectAttributes attributes) {
        String fileName = uploadFile.getOriginalFilename();
        if (!fileName.endsWith(".xlsx")) {
            String geshi = fileName.substring(fileName.indexOf("."));
            attributes.addAttribute("fail", "不支持文件格式" + geshi);
            return "redirect:renyuan";
        }

        try {
            //根据路径获取这个操作excel的实例
            HSSFWorkbook wb = new HSSFWorkbook(uploadFile.getInputStream());
            //根据页面index 获取sheet页
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = null;

            //第一行,第二格 获取年份
             row = sheet.getRow(0);
            String year =row.getCell(1).toString();
            //校验年份
             if(!Tools.isYear(year)){
                 attributes.addAttribute("fail", "年份错误");
                 return "redirect: renyuan";
            }
             //导入数据
            ArrayList< RenYuan> renyuanlist=new ArrayList<GanbuKaohejieguo>();
            for (int i = 2; i < sheet.getPhysicalNumberOfRows()-1; i++) {
                RenYuan renyan = new RenYuan();
                renyan.setShenFenZheng(row.getCell(0).toString());
                renyan.setContent(row.getCell(1).toString());
                renyuanlist.add(jieguo);
            }
            //校验数据部分省略
          
          //写数据库
           // DB db= new DB();
           // db.updateGanbuKaohejieguo(jct,jieguos);
           //返回结果
            attributes.addAttribute("sucess", "导入成功"+jieguos+"条" );
            return "redirect: renyuan";
        } catch (Exception e) {
            e.printStackTrace();
        }
   
年份2022
身份证内容
123456789012345678内容