java excel导入解析_java实现的Excel批量导入

导入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