导入Maven
1
2 org.jeecg
3 easypoi-base
4 2.3.1
5
本人的Excle表
定义一个实体类用于接收解析Excle里的数据
package********.entity;importjava.io.Serializable;importlombok.AllArgsConstructor;importlombok.Data;importlombok.NoArgsConstructor;/*** 批量导入的实体类
*@author**/@Data
@NoArgsConstructor
@AllArgsConstructorpublic class TblFixChange implementsSerializable{/****/
private static final long serialVersionUID = 1L;/**手机号**/
privateString preferentialPhone;/**车牌号**/
privateString preferentialCarNumber;/**-- 优惠分组的id --**/
privateString groupingId;/**-- 创建时间 --**/
privateString createTime;/**-- 修改时间 --**/
privateString modifyTime;
}
Controller
//导入
@RequestMapping(value = "/im/activity/upload", method =RequestMethod.POST)public Map importExcel(@RequestParam("myFile") MultipartFile myFile) throwsIOException, Exception{
Map map = new HashMap();try{
map=groupConfigService.importExcel(myFile);
}catch(Exception e) {
map.put("status",-1);
map.put("data", "导入异常");
}returnmap;
}
serviceImpl
/*** 批量导入(批量添加)*/
private static final String XLS = "xls";
private static final String XLSK = "xlsx"; //定义全局的常量值
@SuppressWarnings("resource")public Map importExcel(MultipartFile myFile) throwsException {
List list = new ArrayList();
Map rsultMap = new HashMap();
Workbook workbook= null;
String fileName=myFile.getOriginalFilename();if(fileName.endsWith(XLS)) {//2003
try{
workbook= newHSSFWorkbook(myFile.getInputStream());
}catch(Exception e) {
e.printStackTrace( );
}
}else if(fileName.endsWith(XLSK)) {try{//2007
workbook = newXSSFWorkbook(myFile.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 = newTblFixChange();//手机号
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", "导入数据异常");
}returnrsultMap;
}
//获取Cell内容
privateString 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;
}
}returnvalue.trim();
}
sql语句的批量增加(本人的id是自增长的)
INSERT INTO activity_prefere (preferential_car_number ,
grouping_id ,
create_time ,
modify_time ,
preferential_phone) VALUES(
#{item.preferentialCarNumber} ,
#{item.groupingId} ,
#{item.createTime} ,
#{item.modifyTime} ,
#{item.preferentialPhone}
)
原文:https://www.cnblogs.com/huigee/p/10820741.html