java中导入Excel表
需要的jar包:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
实体类:
public class student{
@Column
private String name;//姓名
@Column
private String gender;//性别
@Column
private int age;//年龄
@Column
private String date;//出生日期
@Column
private String familyAddress;//家庭地址
}
业务层接口:
Student eximpsave(Student plan);
业务层实现类:
@Override
@Transactional(rollbackFor = Exception.class)
public Student eximpsave(Student plan) {
return studentRepository.saveAndFlush(plan); //jpa中的保存方法
}
工具:
/**
* 系统常量
*/
public class SysConstant {
public final static String EXCEL2003L =".xls"; //2003- 版本的excel
public final static String EXCEL2007U =".xlsx"; //2007+ 版本的excel
public final static String SAVE_INFORMATION="saveInformation";
public final static String SAVE_MAJOR="saveMajor";
public final static String UNPAID="0";
}
控制层:
/**
* 导入excel
*/
@RequestMapping("/importExcel")
@ResponseBody
public JSONObject importGrade(@RequestParam("file") MultipartFile file){
JSONObject jsonObject=new JSONObject();
try {
String fileName = file.getOriginalFilename();
//创建Excel工作薄
Workbook wb;
//根据文件后缀,自适应上传文件的版本
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(SysConstant.EXCEL2003L.equals(fileType)){
wb = new HSSFWorkbook(file.getInputStream()); //2003-
}else if(SysConstant.EXCEL2007U.equals(fileType)){
wb = new XSSFWorkbook(file.getInputStream()); //2007+
}else{
throw new Exception("导入的文件格式有误!");
}
if(null == wb){
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = wb.getSheetAt(0);
if(sheet==null){
throw new Exception("导入的文件格式有误!");
}
Row row;
for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++){ //遍历行
row = sheet.getRow(j+1);
int i;
if(row==null){
continue;
}
String name=(String) getCellValue(row.getCell(1));//姓名
String gender=(String)getCellValue(row.getCell(2));//性别
String age=(String)getCellValue(row.getCell(3));//年龄
String date=(String)getCellValue(row.getCell(4));//出生日期
String familyAddress = (String) getCellValue(row.getCell(5));//家庭地址
Info plan=new Info();
plan.setId(i++);
plan.setName(name);
plan.setGender(gender);
plan.setAge(age);
plan.setDate(date);
plan.setFamilyAddress(familyAddress);
infoService.eximpsave(plan);
}
jsonObject.put("success",true);
jsonObject.put("msg","导入成功");
}catch (Exception e){
e.printStackTrace();
jsonObject.put("success",false);
jsonObject.put("msg","服务器发生异常");
}
return jsonObject;
}
/**
* 描述:对表格中数值进行格式化
*/
public static Object getCellValue(Cell cell){
Object value = null;
//格式化数字
DecimalFormat df2 = new DecimalFormat("0");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
value = df2.format(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
前端页面:
<span class="input-group-btn">
<button type="button" class="btn waves-effect waves-light btn-warning" id="eximport">导入</button>
</span>
其它暂无
表格样式: