今天给大家分享的是JAVA如何导入EXCEL表格,如有不对之处,敬请指正。
在之前的poi导出中讲解了pom.xml配置,BaseController,ExportExcel工具类的配置,
poi导入时还是这些配置
配置springmvc.xml
<!--上文件上传解析-->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding">
<value>UTF-8</value>
</property>
<property name="maxUploadSize">
<value>32505856</value><!-- 上传文件大小限制为31M,31*1024*1024 -->
</property>
<property name="uploadTempDir">
<value>/upload</value><!--上传后的目录名-->
</property>
<property name="maxInMemorySize">
<value>4096</value>
</property>
</bean>
controller层
@RequestMapping("uploadExcel")
public Result uploadExcel(@RequestParam("file") CommonsMultipartFile file) {
if(!file.isEmpty()) {
//获得本地存储路径
String realPath = this.servletContext.getRealPath("/upload/");
//获取文件名
String fileName = file.getOriginalFilename();
//获取文件名类型
String fileType = fileName.substring(fileName.lastIndexOf("."));
//获取新文件名
String fileNewName = new Date().getTime() + fileType;
//获取新文件
File file2 = new File(realPath, fileNewName);
//获取新文件路径
String newFilePath = realPath + fileNewName;
try {
//将上传的文件写入到新建的文件中
file.getFileItem().write(file2);
//判断excel类型
if (fileType.equals(".xls")) {
//获得工作簿
HSSFWorkbook hw = new HSSFWorkbook(new FileInputStream(newFilePath));
ArrayList<TEquipment> arrayList = new ArrayList<TEquipment>();
//获得sheet页
for (int i = 0; i < hw.getNumberOfSheets(); i++) {
//获得sheet页
HSSFSheet sheet = hw.getSheetAt(i);
TEquipment te = null;
//获得sheet中的行信息
for(int j = 3; j < sheet.getPhysicalNumberOfRows(); j++) {
HSSFRow row = sheet.getRow(j);
te=new TEquipment();
te.setEquipmentnumber(EquipmentController.getCellValue(row.getCell(0)));
te.setEquipmentname(EquipmentController.getCellValue(row.getCell(1)));
te.setEquipmentstatus(EquipmentController.getCellValue(row.getCell(2)));
arrayList.add(te);
}
//将arrayList集合中的数据增加到数据库
boolean good = equipmentService.addte(arrayList);
if (good == true) {
return new Result(true, "导入成功!");
}else {
return new Result(false, "导入失败!");
}
}else if (fileType.equals(".xlsx")) {
}else {
return new Result(false,"上传文件类型不属于Excel文件,请重新选择!");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return new Result(false,"上传文件异常");
}
}
return new Result(false,"上传文件失败!");
}
service层
public boolean addte(ArrayList<TEquipment> arrayList) {
try {
//使用加强for循环增加
for (TEquipment tEquipment : arrayList) {
tEquipment.setEquipmentid(UUID.randomUUID().toString());
tequipmentMapper.insert(tEquipment);
}
return true;
} catch (Exception e) {
// TODO: handle exception
}
return false;
}
接下来传入到mapper层写增加sql语句就可以了