对Excle的行和列进行检查单元格类型转换代码；

对Excle的行和列进行检查转换代码；
  1 ** 
  2  * 导入信息 
  3  */  
  4 @Override  
  5 public List<Object> add(HttpServletRequest request) {  
  6 // TODO Auto-generated method stub  
  7 List<Object> num=new ArrayList<Object>();  
  8 MultipartHttpServletRequest multipartRequest =(MultipartHttpServletRequest) request;  
  9 CommonsMultipartFile file = (CommonsMultipartFile)multipartRequest.getFile("zlUpload");  
 10 if(file!=null){          
 11 try {  
 12 num = save(file.getInputStream());  
 13 } catch (IOException e) {  
 14 // TODO Auto-generated catch block  
 15 e.printStackTrace();  
 16 }          
 17 }  
 18 returnnum;                  
 19 }  
 20    
 21 /** 
 22  * 保存上传的Excel信息 
 23  */  
 24 private List<Object> save(InputStream inputStream) throwsIOException {  
 25 // TODO Auto-generated method stub  
 26 List<Object> error_num = new ArrayList<Object>();  
 27 List<Object> temp =(List<Object>)readXls(inputStream,error_num);  
 28 System.out.println(temp.get(0).getClass().getName());  
 29 if(temp.get(0).getClass().getName().equals("org.apache.poi.hssf.usermodel.HSSFCell")){  
 30 return error_num;  
 31 }else{  
 32 TStudentNo student = null;  
 33 List<TStudentNo> studentList = newArrayList<TStudentNo>();  
 34 for(int i=0;i<temp.size();i++){  
 35 student = (TStudentNo)temp.get(i);  
 36 studentList.add(student);  
 37 }  
 38 try {  
 39 //在插入数据前进行判断，看数据库中是否有不允许重复的字段出现，以打断保存进度  
 40 int repeat = 0;  
 41 for(int j = 0;j<studentList.size();j++){  
 42 TStudentNo Studenttemp =studentMapper.findByStudentNo(studentList.get(j).getStudent_no());  
 43 //如果查到了，重复数加一，然后跳过不保存  
 44 if(Studenttemp!=null){  
 45 repeat++;  
 46 }  
 47 }          
 48 if(repeat==0){  
 49 for(int z=0;z<studentList.size();z++){  
 50 studentMapper.saveStudent(studentList.get(z));  
 51 }  
 52 }else{  
 53 error_num.add("数据库中有相同的数据，请检查学号等不允许重复的部分！");  
 54 return error_num;  
 55 }                                  
 56 } catch (Exception e) {  
 57 //判断Excel中是否有重复数据，如果有重复跳过保存异常  
 58 error_num.add("数据库中有相同的数据，请检查学号等不允许重复的部分！");  
 59 return error_num;  
 60 }  
 61    
 62 return temp;          
 63 }  
 64 }  
 65    
 66 /** 
 67  * 逐行遍历其Excel 
 68  */  
 69    
 70 private Object readXls(InputStream inputStream,List<Object>error_num) throws IOException {  
 71 InputStream is = new BufferedInputStream(inputStream);  
 72 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);  
 73 TStudentNo student = null;  
 74 List<TStudentNo> list = new ArrayList<TStudentNo>();  
 75 //循环工作表Sheet  
 76 for(int numSheet =0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){  
 77 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);  
 78 if(hssfSheet == null){  
 79 continue;  
 80 }  
 81    
 82 for(int rowNum =2;rowNum<=hssfSheet.getLastRowNum();rowNum++){  
 83 System.out.println(hssfSheet.getLastRowNum());          
 84 HSSFRow hssfRow = hssfSheet.getRow(rowNum);  
 85 //检查每行的空格数，如果小于4证明有一个或多个空格，但不是整行  
 86 if(CheckRowNull(hssfRow)<4){  
 87 student = new TStudentNo();  
 88 HSSFCell name = hssfRow.getCell(0);  
 89 HSSFCell student_no = hssfRow.getCell(1);  
 90 HSSFCell phone = hssfRow.getCell(2);  
 91 HSSFCell class_no = hssfRow.getCell(3);  
 92 HSSFCell subject_category = hssfRow.getCell(4);  
 93 List<HSSFCell> temp = new ArrayList<HSSFCell>();  
 94 temp.add(0, name);  
 95 temp.add(1, student_no);  
 96 temp.add(2, phone);  
 97 temp.add(3, class_no);  
 98 temp.add(4, subject_category);  
 99 int temp1 = 0;//用于跳出双层for循环  
100 for(int i=0;i<5;i++){  
101 //为记录前台进行提示某行某列出错  
102 temp1 = CheckRowError(temp.get(i),error_num,rowNum,i);  
103 if(temp1==-1){  
104 break;  
105 }  
106 }  
107 if(temp1==-1){  
108 return temp;  
109 }  
110 student.setName(getCellValue(name));  
111 student.setPhone(getCellValue(phone));  
112 student.setStudent_no(getCellValue(student_no));  
113 student.setClass_no(getCellValue(class_no));  
114 student.setSubject_category(Integer.parseInt(getCellValue(subject_category)));  
115 list.add(student);  
116 }else{  
117 continue;  
118 }  
119 }  
120    
121 }  
122 return list;  
123 }  
124    
125 /** 
126  * 对Excel的各个单元格的格式进行判断并转换 
127  */  
128 private String getCellValue(HSSFCell cell) {   
129         String cellValue = "";   
130         DecimalFormat df = newDecimalFormat("#");   
131         switch (cell.getCellType()) {   
132         case HSSFCell.CELL_TYPE_STRING:   
133             cellValue =cell.getRichStringCellValue().getString().trim();   
134             break;   
135         case HSSFCell.CELL_TYPE_NUMERIC:   
136             cellValue =df.format(cell.getNumericCellValue()).toString();   
137             break;   
138         case HSSFCell.CELL_TYPE_BOOLEAN:   
139             cellValue =String.valueOf(cell.getBooleanCellValue()).trim();   
140             break;   
141         case HSSFCell.CELL_TYPE_FORMULA:   
142             cellValue =cell.getCellFormula();   
143             break;   
144         default:   
145             cellValue = "";   
146         }   
147         return cellValue;   
148     }   
149    
150 //判断某行某列有问题  
151 private int CheckRowError(HSSFCell cell,List<Object>error_num,int rowNum,int cell_num){  
152 //判断各个单元格是否为空  
153 if(cell==null||cell.equals("")||cell.getCellType() ==HSSFCell.CELL_TYPE_BLANK){  
154 error_num.add("出错啦！请检查第"+(rowNum+1)+"行第"+(cell_num+1)+"列。"+"如果您在该行没有数据，建议您选择删除该行，重试！");  
155 return -1;  
156 }  
157 return 0;  
158 }  
159    
160 //判断行为空  
161 private int CheckRowNull(HSSFRow hssfRow){  
162 int num = 0;  
163 Iterator<Cell> cellItr =hssfRow.iterator();  
164 while(cellItr.hasNext()){  
165  Cell c =cellItr.next();                          
166  if(c.getCellType() ==HSSFCell.CELL_TYPE_BLANK){  
167  num++;  
168  }  
169 }  
170 return num;  
171 }
View Code
对Excle的行和列进行检查 单元格类型转换代码 ；

对Excle的行和列进行检查单元格类型转换代码；
