Java的poi技术遍历Excel时进行空Cell,空row,判断
来源:互联网 发布:中异软件好用吗 编辑:程序博客网 时间:2024/05/15 17:31
/** * 导入信息 */@Overridepublic List<Object> add(HttpServletRequest request) {// TODO Auto-generated method stubList<Object> num=new ArrayList<Object>();MultipartHttpServletRequest multipartRequest =(MultipartHttpServletRequest) request;CommonsMultipartFile file = (CommonsMultipartFile)multipartRequest.getFile("zlUpload");if(file!=null){ try {num = save(file.getInputStream());} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();} }returnnum; } /** * 保存上传的Excel信息 */private List<Object> save(InputStream inputStream) throwsIOException {// TODO Auto-generated method stubList<Object> error_num = new ArrayList<Object>();List<Object> temp =(List<Object>)readXls(inputStream,error_num);System.out.println(temp.get(0).getClass().getName());if(temp.get(0).getClass().getName().equals("org.apache.poi.hssf.usermodel.HSSFCell")){return error_num;}else{TStudentNo student = null;List<TStudentNo> studentList = newArrayList<TStudentNo>();for(int i=0;i<temp.size();i++){student = (TStudentNo)temp.get(i);studentList.add(student);}try {//在插入数据前进行判断,看数据库中是否有不允许重复的字段出现,以打断保存进度int repeat = 0;for(int j = 0;j<studentList.size();j++){TStudentNo Studenttemp =studentMapper.findByStudentNo(studentList.get(j).getStudent_no());//如果查到了,重复数加一,然后跳过不保存if(Studenttemp!=null){repeat++;}} if(repeat==0){for(int z=0;z<studentList.size();z++){studentMapper.saveStudent(studentList.get(z));}}else{error_num.add("数据库中有相同的数据,请检查学号等不允许重复的部分!");return error_num;} } catch (Exception e) {//判断Excel中是否有重复数据,如果有重复跳过保存异常error_num.add("数据库中有相同的数据,请检查学号等不允许重复的部分!");return error_num;} return temp; }} /** * 逐行遍历其Excel */ private Object readXls(InputStream inputStream,List<Object>error_num) throws IOException {InputStream is = new BufferedInputStream(inputStream);HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);TStudentNo student = null;List<TStudentNo> list = new ArrayList<TStudentNo>();//循环工作表Sheetfor(int numSheet =0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);if(hssfSheet == null){continue;} for(int rowNum =2;rowNum<=hssfSheet.getLastRowNum();rowNum++){System.out.println(hssfSheet.getLastRowNum()); HSSFRow hssfRow = hssfSheet.getRow(rowNum);//检查每行的空格数,如果小于4证明有一个或多个空格,但不是整行if(CheckRowNull(hssfRow)<4){student = new TStudentNo();HSSFCell name = hssfRow.getCell(0);HSSFCell student_no = hssfRow.getCell(1);HSSFCell phone = hssfRow.getCell(2);HSSFCell class_no = hssfRow.getCell(3);HSSFCell subject_category = hssfRow.getCell(4);List<HSSFCell> temp = new ArrayList<HSSFCell>();temp.add(0, name);temp.add(1, student_no);temp.add(2, phone);temp.add(3, class_no);temp.add(4, subject_category);int temp1 = 0;//用于跳出双层for循环for(int i=0;i<5;i++){//为记录前台进行提示某行某列出错temp1 = CheckRowError(temp.get(i),error_num,rowNum,i);if(temp1==-1){break;}}if(temp1==-1){return temp;}student.setName(getCellValue(name));student.setPhone(getCellValue(phone));student.setStudent_no(getCellValue(student_no));student.setClass_no(getCellValue(class_no));student.setSubject_category(Integer.parseInt(getCellValue(subject_category)));list.add(student);}else{continue;}} }return list;} /** * 对Excel的各个单元格的格式进行判断并转换 */private String getCellValue(HSSFCell cell) { String cellValue = ""; DecimalFormat df = newDecimalFormat("#"); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: cellValue =cell.getRichStringCellValue().getString().trim(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue =df.format(cell.getNumericCellValue()).toString(); break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue =String.valueOf(cell.getBooleanCellValue()).trim(); break; case HSSFCell.CELL_TYPE_FORMULA: cellValue =cell.getCellFormula(); break; default: cellValue = ""; } return cellValue; } //判断某行某列有问题private int CheckRowError(HSSFCell cell,List<Object>error_num,int rowNum,int cell_num){//判断各个单元格是否为空if(cell==null||cell.equals("")||cell.getCellType() ==HSSFCell.CELL_TYPE_BLANK){error_num.add("出错啦!请检查第"+(rowNum+1)+"行第"+(cell_num+1)+"列。"+"如果您在该行没有数据,建议您选择删除该行,重试!");return -1;}return 0;} //判断行为空private int CheckRowNull(HSSFRow hssfRow){int num = 0;Iterator<Cell> cellItr =hssfRow.iterator();while(cellItr.hasNext()){ Cell c =cellItr.next(); if(c.getCellType() ==HSSFCell.CELL_TYPE_BLANK){ num++; }}return num;}
0 0
- Java的poi技术遍历Excel时进行空Cell,空row,判断
- POI操作Excel时遇到空Cell
- Java POI 读取带有空单元格或者缺失单元格的excel文件
- js判断空与Java判断空
- POI读取Excel时报空指针错误
- Java中判断空
- JAVA 判断空
- java空 null 判断
- Java非空判断
- Java 空判断
- JAVA list 判断空
- java中判断空字符串时避免出现空指针的方法
- POI拆分单元格,并设置拆分后第一个cell的值为空cell的值
- 关于java的非空判断
- JAVA判断不等于空的情况
- Java关于空字符串的正确判断
- java判断list为空的方法
- 【Java】对于空值null的判断
- 约瑟夫环(c语言程序完整版)
- java类变量不需要先声明吗?
- 基础总结篇之六:ContentProvider之读写联系人
- python爬虫时报错
- 2016 CocosPods安装教程
- Java的poi技术遍历Excel时进行空Cell,空row,判断
- 基础总结篇之七:ContentProvider之读写短消息
- Different Ways to Add Parentheses
- Data Import Handler - DIH相关命令
- cocoapods 导入SDK时podfile文件内容更新
- iOS8以iOS8以下的系统present一个半透明的ViewController的方法
- STL vector 函数
- 基础总结篇之八:创建及调用自己的ContentProvider
- java的poi技术读取Excel[2003-2007,2010]