java excel导入
导入方法，拿暂时做的项目做参考，大致相同
jsp页面简单构建：
<div class="body">
<div class="daoru">
<div class="drmbxz-left">
信息导入：
</div>
<div class="drmbxz">
<a href="/jgpt/operationSetting/downloadExcel.do">导入模板下载</a>
</div>
</div>
<div class="importform">
<form action="/jgpt/operationSetting/importProportions.do" method="post" enctype="multipart/form-data">
<div class="xzwj">选择文件：<input type="file" name="excelFile"></div>
<div class="drsub"><input type="submit" value="导入" style="width: 100px;color: darkslategray;"/></div>
</form>
</div>
</div>
Controller层方法：需要判断时2003还是2007，创建的excel格式不同,adduser()导入方法就不写了，extendedUserDtos 就可以将导出的数据拿出来
@RequestMapping(value = "/importProportions", method = RequestMethod.POST)
@ResponseBody
public CResult<String> importProportions(@RequestParam(value="excelFile",required = false) MultipartFile file,HttpServletRequest request,HttpServletResponse response
) throws IOException {
List<ExtendedUserDto> extendedUserDtos = new ArrayList<>();
try {
/*String filePath = "F:\\导入信息模板.xlsx";
File file = new File(filePath);*/
InputStream is= file.getInputStream();/*new FileInputStream(file)*/
String fileName = file.getOriginalFilename();
boolean isExcel2003 = true;
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
try{
extendedUserDtos = adminService.importuser(is,isExcel2003);
}catch (Exception e){
CResult<String> result = new CResult<>();
result.setKey("error.exception");
result.setMsg(e.getMessage());
return result;
}
if(extendedUserDtos != null && !extendedUserDtos.isEmpty()){
try{
CResult<String> rst = new CResult<>();
int size = extendedUserDtos.size();
for(int i=0;i<size;i++){
rst = addUser(JSON.toJSONString(extendedUserDtos.get(i)), request);
if(rst.getKey()!=MsgKey.SUCCESS_OPERATION){
if(StringUtils.isBlank(rst.getKey())){
if((i+2)>2){
rst.setMsg("前"+(i+1)+"行数据导入成功,第"+(i+2)+"行数据有问题,"+rst.getResult());
}else{
rst.setMsg("第"+(i+2)+"行数据有问题,"+rst.getResult());
}
}else{
if((i+2)>2){
rst.setMsg("前"+(i+1)+"行数据导入成功,第"+(i+2)+"行数据有问题,"+rst.getMsg());
}else{
rst.setMsg("第"+(i+2)+"行数据有问题,"+rst.getMsg());
}
}
return rst;
}
}
return new CResult<>(MsgKey.SUCCESS_OPERATION);
}catch (Exception e){
return new CResult<>(MsgKey.ERROR_OPERATION_EXCEPTION);
}
}
return new CResult<>(MsgKey.SUCCESS_OPERATION);
} catch (Exception e) {
logger.error("importProportions :" , e.getMessage());
return new CResult<>(MsgKey.ERROR_OPERATION_EXCEPTION);
}
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
Service层方法 导入的excel分析
/**
* 导入
* @param is
* @param isExcel2003
* @return
*/
@Override
public List<ExtendedUserDto> importuser(InputStream is,boolean isExcel2003){
List<ExtendedUserDto> extendedUserDtos = new ArrayList<>();
Workbook wb = null;
try {
if(isExcel2003){
// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
}else{
// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel行数
int rows = sheet.getLastRowNum();
// 得到excel的列数（前提示有行数）
int cells = 0;
if (rows >=1 && sheet.getRow(0) != null) {
cells = sheet.getRow(0).getPhysicalNumberOfCells();
if (cells != 7) {
throw new RuntimeException("导入文件格式不正确");
}
}
// 循环Excel行数
for (int i = 1; i <= rows; i++) {
Row row = sheet.getRow(i);
// 创建对象
ExtendedUserDto extendedUserDto = new ExtendedUserDto();
Cell cell = row.getCell((short)0);
String account;
if(cell!=null){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
account = String.valueOf(cell.getNumericCellValue());
}else{
account = cell.getStringCellValue();
}
int accountlength = length(account);
if(accountlength>100){
throw new RuntimeException("第"+(i+1)+"行数据账号（" + account + "）长度过长！");
}else{
extendedUserDto.setAccount(account);
}
}
cell = row.getCell((short)1);
String name;
if(cell!=null){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
name = String.valueOf(cell.getNumericCellValue());
}else{
name = cell.getStringCellValue();
}
int namelength = length(name);
if(namelength>100){
throw new RuntimeException("第"+(i+1)+"行数据用户姓名（" + name + "）长度过长！");
}else{
extendedUserDto.setName(name);
}
}
// 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
cell = row.getCell((short)2);
if(cell!=null){
String phone;
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
DecimalFormat df = new DecimalFormat("#");
phone = df.format(cell.getNumericCellValue());
}else{
phone = cell.getStringCellValue();
}
if(phone.length()!=11){
throw new RuntimeException("第"+(i+1)+"行数据手机号" + phone + "不正确！");
}
if(!phone.matches("^1[3|4|5|7|8][0-9]\\d{4,8}$")){
throw new RuntimeException("第"+(i+1)+"行数据输入的手机号" + phone + "是错误格式！！！");
}
extendedUserDto.setPhoneNumber(phone);
}
cell = row.getCell((short)3);
if(cell!=null){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
if( "内网".equals(String.valueOf(cell.getNumericCellValue()))){
extendedUserDto.setNetType("0");
}else{
extendedUserDto.setNetType("1");
}
}else{
if( "内网".equals(cell.getStringCellValue())){
extendedUserDto.setNetType("0");
}else{
extendedUserDto.setNetType("1");
}
}
}
cell = row.getCell((short)4);
if(cell!=null){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
if( "是".equals(String.valueOf(cell.getNumericCellValue()))){
extendedUserDto.setFieldAccount("1");
}else{
extendedUserDto.setFieldAccount("0");
}
}else{
if( "是".equals(cell.getStringCellValue())){
extendedUserDto.setFieldAccount("1");
}else{
extendedUserDto.setFieldAccount("0");
}
}
}
String rolename = "";
cell = row.getCell((short)5);
if(cell!=null){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
rolename = String.valueOf(cell.getNumericCellValue());
}else{
rolename = cell.getStringCellValue();
}
}
cell = row.getCell((short)6);
if(cell!=null){
String schoolNames;
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
schoolNames = String.valueOf(cell.getNumericCellValue());
}else{
schoolNames = cell.getStringCellValue();
}
List<NewRoleDto> newRoleDtos = new ArrayList<>();
//学校名称按逗号分隔
List<String> schoolName = new ArrayList<>();
if(schoolNames.contains(",")){
schoolName = Arrays.asList(schoolNames.split(","));
}else{
schoolName.add(schoolNames);
}
List<UserSingleAreaDto> userSingleAreaDtos = new ArrayList<>();
try {
userSingleAreaDtos = extendedUserMapper.findSchoolsByNames(schoolName);
if(userSingleAreaDtos.size()!=schoolName.size()){
throw new RuntimeException("第"+(i+1)+"行数据学校信息有误，请核对学校！确认学校英文逗号分隔！");
}
} catch (SQLException e) {
e.printStackTrace();
}
//学校信息
List<School> schools = new ArrayList<>();
List<SchoolInfoJsonDto> schoolInfoJsonDtos = new ArrayList<>();
if(userSingleAreaDtos!=null && userSingleAreaDtos.size()>0){
for(UserSingleAreaDto userSingleAreaDto:userSingleAreaDtos){
createSchoolInfoJson(schoolInfoJsonDtos, userSingleAreaDto);
School school = new School();
school.setSchoolId(userSingleAreaDto.getSchoolId());
school.setSchoolName(userSingleAreaDto.getSchoolName());
/*school.setProvinceId(Integer.valueOf(userSingleAreaDto.getProvinceId())); school.setProvinceName(userSingleAreaDto.getProvinceName()); school.setCityId(Integer.valueOf(userSingleAreaDto.getCityId())); school.setCityName(userSingleAreaDto.getCityName());
school.setDistrictId(Integer.valueOf(userSingleAreaDto.getDistrictId()));
school.setDistrictName(userSingleAreaDto.getDistrictName());*/
schools.add(school);
}
}
//处理角色信息---校园代理角色
List<Role> roles = new ArrayList<>();
List<String> roleNames = new ArrayList<>();
if(StringUtils.isNotBlank(rolename)){
roleNames.add(rolename);
roles = roleMapper.getRolesByNames(roleNames);
}
NewRoleDto newRoleDto = new NewRoleDto();
if(roles!=null && roles.size()>0){
for(Role role:roles){
newRoleDto.setId(role.getId());
newRoleDto.setRoleTypeCode(role.getRoleTypeCode());
newRoleDto.setRoleName(role.getRoleName());
}
}else{
throw new RuntimeException("第"+(i+1)+"行数据角色信息有误！");
}
//处理学校信息
newRoleDto.setSchoolInfoList(schools);
//处理学校Json
newRoleDto.setSchoolInfoJson(JSON.toJSONString(schoolInfoJsonDtos));
//对应角色----只处理学校
newRoleDtos.add(newRoleDto);
extendedUserDto.setRoles(newRoleDtos);
}
extendedUserDtos.add(extendedUserDto);
}
} catch (IOException e) {
e.printStackTrace();
}
return extendedUserDtos;
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
浙公网安备 33010602011771号