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);                   }       }