Java上传Excel并解析

文件上传

public String uploadFile(CommonsMultipartFile file,String uploadPath,String realUploadPath){
		InputStream is = null;
		OutputStream os = null;
		Calendar calendar = Calendar.getInstance();//获取时间
		long excelName = calendar.getTime().getTime();
		
		try {
			is = file.getInputStream();
			String des = realUploadPath + "/"+Long.toString(excelName)+file.getOriginalFilename();
			os = new FileOutputStream(des);
			
			byte[] buffer = new byte[1024];
			int len = 0;
			
			while((len = is.read(buffer))>0){
				os.write(buffer);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(is!=null){
				try{
					is.close();
				}catch (Exception e2){
					e2.printStackTrace();
				}
			}
			
			if(os!=null){
				try{
					os.close();
				}catch (Exception e2){
					e2.printStackTrace();
				}
			}
		}
		//返回路径
		return uploadPath + "/"+Long.toString(excelName)+file.getOriginalFilename();
	}
	

常用的Excel解析方式有两种JXL,POI

jxl用起来相对简单，但只支持Excel2003版本，也就是说jxl无法解析.xlsx的Excel文件，而POI会识别Excel版本进行解析，所以大部分人更倾向于POI

jxl使用时需要在项目中导入jxl.jar包

poi需要导入

poi-3.14-20160307.jar

poi-ooxml-3.14-20160307.jar

poi-ooxml-schemas-3.14-20160307.jar

commons-io-1.4.jar

commons-fileupload-1.2.1.jar

jxl解析代码

public String readExcel(CommonsMultipartFile file,HttpServletRequest request)throws IOException, WriteException{
		
		StringBuffer sb = new StringBuffer();//将读取的内容存入StringBUffer中
		try {
			Workbook book = Workbook.getWorkbook(file.getInputStream());
			try{
	            Sheet sheet = book.getSheet(0);
	            for(int i = 0 ; i < 3 ; i++){//i表示行数
	                for(int j = 0 ; j < 4 ; j++){//j表示列数
	                    sb.append(sheet.getCell(j, i).getContents()+"\t");
	                }
	                sb.append("\n");
	            }
	            System.out.println(sb);
	        }finally{
	            if(book != null){
	                book.close();
	            }
	        }
		} catch (BiffException e) {
	        System.err.println(e+"");
	    } catch (IOException e) {
	        System.err.println(e+"文件读取错误");
	    }
		return "";
	}

poi代码

    private POIFSFileSystem fs;
    private HSSFWorkbook wb;	
    private HSSFSheet sheet;	
    private HSSFRow row;
    /*读取标题excel第一行内容*/
	public String[] readExcelTitle(InputStream is) {
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            //title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = getCellFormatValue(row.getCell((short) i));
        }
        return title;
    }
	/*读取内容*/
	 public void readExcelContent(InputStream is) {
        Map<Integer, ModelCourse> content = new HashMap<Integer, ModelCourse>();
        ModelCourse model=new ModelCourse();
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        
        // 正文内容从第二行开始,第一行为表头的标题
        for (int i = 1; i <=rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
           
           
            while (j < colNum) {
               
             if(j==1){
            		model.setCourse_id(getCellFormatValue(row.getCell((short) j)));
            	}
            	else if(j==2){
            		model.setCourse_name(getCellFormatValue(row.getCell((short) j)));
            	}
            	else if(j==3){
            		model.setCourse_time(getCellFormatValue(row.getCell((short) j)));
            	}
            	else if(j==4){
            		model.setCourse_place(getCellFormatValue(row.getCell((short) j)));
            	}
                j++;
               
            }
            content.put(i, model);
            addCourse(model);           
        }   
    }