上篇中我们提到了关于解析doc文本文档的方法,那么这篇文章主要来说如果通过Excel批量导入
需求介绍:
在任务模块我们不光可以单个添加任务,还可以批量添加任务,于是需要使用Excel来实现
实现步骤:
一、同样导入POI依赖
<!-- 引入poi -->
<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>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
二、Controller层
//导入
@RequestMapping(value = "/activity/upload", method = RequestMethod.POST)
public Map<String, Object> importExcel(@RequestParam("file") MultipartFile file) throws IOException, Exception{
Map<String, Object> map = new HashMap<String, Object>();
try {
map = groupConfigService.importExcel(file);
} catch (Exception e) {
map.put("status",-1);
map.put("data", "导入异常");
}
return map;
}
三、ServiceImpl层
/**
* 批量导入(批量添加)
*/
private static final String XLS = "xls";
private static final String XLSK = "xlsx"; //定义全局的常量值
public Map<String, Object> importExcel(MultipartFile file) throws Exception {
List<TblFixChange> list = new ArrayList<TblFixChange>();
Map<String, Object> rsultMap = new HashMap<String, Object>();
Workbook workbook = null;
String fileName = file.getOriginalFilename();
if(fileName.endsWith(XLS)) {
//2003
try {
workbook = new HSSFWorkbook(file.getInputStream());
} catch (Exception e) {
e.printStackTrace( );
}
}else if(fileName.endsWith(XLSK)) {
try {
//2007
workbook = new XSSFWorkbook(file.getInputStream());
} catch (Exception e) {
e.printStackTrace( );
}
}else {
throw new Exception("文件不是Excel文件");
}
Sheet sheet = workbook.getSheet("Sheet1");
//指定行数。一共多少+
int rows = sheet.getLastRowNum();
if(rows==0) {
throw new Exception("请填写行数");
}
for (int i = 1; i < rows+1; i++) {
//读取左上端单元格
Row row = sheet.getRow(i);
//行不为空
if(row != null) {
//读取cell
TblFixChange tblFixChange = new TblFixChange();
//手机号
String phone = getCellValue(row.getCell(0));
tblFixChange.setPreferentialPhone(phone);
//车牌号
String catNumber = getCellValue(row.getCell(1));
tblFixChange.setPreferentialCarNumber(catNumber);
//组的id
String groupId = getCellValue(row.getCell(2));
tblFixChange.setGroupingId(groupId);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String time = sdf.format(new Date());
tblFixChange.setCreateTime(time);
tblFixChange.setModifyTime(time);
list.add(tblFixChange); //把实数据放入集合里
}
}
try {
groupConfigDao.addBatchMembers(list); //批量添加 (执行sql语句批量增加)
rsultMap.put("status", 1);
rsultMap.put("data", "导入数据成功");
} catch (Exception e) {
rsultMap.put("status", -1);
rsultMap.put("data", "导入数据异常");
}
return rsultMap;
}
- getCellValue方法:
//获取Cell内容
private String getCellValue(Cell cell) {
String value = "";
if(cell != null) {
//以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC://数字
value = cell.getNumericCellValue() + "";
if(HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if(date != null) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else {
value = "";
}
}else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: //字符串
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: //boolean
value = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: //公式
value = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: //空值
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR: //故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
}
return value.trim();
}
注意:如果你的getCellType不能使用 ,是因为poi依赖版本你的问题,在新版本中getCellType已经被弃用了,可以用
getCellTypeEnum
来替代。
四、需要导入的实体内容:
package ********.entity;
import java.io.Serializable;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 批量导入的实体类
* @author
*
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TblFixChange implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
/**手机号**/
private String preferentialPhone;
/**车牌号**/
private String preferentialCarNumber;
/**-- 优惠分组的id --**/
private String groupingId;
/**-- 创建时间 --**/
private String createTime;
/**-- 修改时间 --**/
private String modifyTime;
}