java导入Excle无非说的通俗一点就是将Excel中的数据上传到程序中,让程序代替手动输入,节省时间的一种方式
1.pom文件中加入以下依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.控制层代码如下:
public Map<String, Object> inputExcel(
@RequestParam MultipartFile file) throws IOException {
Map<String, Object> map = new HashMap<>();
Workbook workbook = null;
File dfile = null;
try {
dfile = File.createTempFile("prefix","_" + file.getOriginalFilename());
file.transferTo(dfile);
}catch (IOException e){
e.printStackTrace();
}
String fileSuffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));//获取文件后缀名
if(".xlsx".equals(fileSuffix)){
workbook = new XSSFWorkbook(FileUtils.openInputStream(dfile));
}else if(".xls".equals(fileSuffix)){
workbook = new HSSFWorkbook(FileUtils.openInputStream(dfile));
}
int x = tngineeringSmallBomService.insert(workbook);
if(x == -1){
map.put("start","200");
map.put("mes","上传成功...");
}else if(x == -2){
map.put("start","201");
map.put("mes","名称不能为空...");
}else if(x == 0){
map.put("start","201");
map.put("mes","请勿上传空模板...");
}
return map;
}
3.实现类如下:
@Override
public int insert(Workbook workbook) {
int x = 0;
Sheet sheet = null;
for(int i = 0;i< workbook.getNumberOfSheets();i++){
sheet = workbook.getSheetAt(i);
int a = sheet.getPhysicalNumberOfRows();//总行数
for(int j = 0;j < a-2 ; j++){ //因为我的Excel模板是从第三行开始取值 所以总循环次数 -2
Row row = sheet.getRow(j+2);//从第几行开始
Map<String,Object> map = new HashMap<>();
//获取每列
map.put("studentName",row.getCell(0).toString());
map.put("studentCode",row.getCell(1).toString());
map.put("teacherName",row.getCell(2).toString());
map.put("teacherCode",row.getCell(3).toString());
map.put("numberCount",row.getCell(4).toString());
//获取到值之后就可以对这些数据进行随意操作了
//这里就可以写新增的方法了 具体的代码看自己需求
x = -1;
}
}
return x;
}