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 | 内容 |