POI解析Excel

最近做的一个功能是上传Excel解析Excel，众所周知，excel文件有两个扩展名（xls，xlsx），是因为Microsoft Excel分两个大版本，2003以前和2007以后。

功能并不复杂，但做的过程中却是遇到很多坎坷。

目前解析Excel有两种方式，一种是jxl，一种就是POI，起初项目中只有jxl相关的资源包，就想jxl能不能解析07版以后的excel，事实证明，不可行。后来又改成POI，在官网http://poi.apache.org/ 下载了最新的jar包，起初只导入了poi自身包，后来发现此包中找不到XSSFWorkbook，才知道要导入

等包，还有包，并将这些jar上传至nexus私服。

上传Excel，起初想着用form表单呈ajax post提交，即

<!--<form  method="post" action="/purchase/batchSave" id="uploadExcelForm" enctype="multipart/form-data">-->
                        <input type="text" class="text-sty2 text-2 col_333" id="filePath" value="" datatype="fileType"  nullmsg="请选择正确的模版文件" />
                        <span class="relative dis-in-bk file ml10"><input type="file" value="选择文件" id="excelFile" name="excelFile"  />选择文件</span>
                        <input type="button" value="发布" class="send ml10" id="batchPub" />
                        <span class="Validform_checktip" id="tips"></span>
                      <!--</form>-->

但后台获取的file老有问题，后来改用jquery的ajaxFileUpload上传

$.ajaxFileUpload({
url:'/purchase/batchSave', 
secureuri:false,
fileElementId:'excelFile',
dataType: 'json',
success: function (data, status)
{
var ok =data.ok;
  var msg = data.msg;
  if(ok){
    $('#sucMsg').html(msg);
   
    $('.popup-box').show();
            bgHiu();
           
            //关闭层，刷新页面
   $('.close').on('click',function(){
           $('.popup-box').hide();
           $('.bghui').remove();
           window.location.replace(data.url);
        });
   }
   else{
    tips(msg);
   }
},
error: function (data, status, e)
{
      tips(data.msg);
}
     });
    });

后台Controller代码：

@RequestMapping(value="/batchSave")
@ResponseBody
public Map<String,Object> uploadExcel(@RequestParam("excelFile") MultipartFile file,HttpServletRequest request,HttpServletResponse response){
Map<String,Object> map = new HashMap<String,Object>();

InputStream fis =  file.getInputStream();
 //XSSFWorkbook、HSSFWorkbook都实现了Workbook接口，通过此方法可以自适应两种Excel版本
org.apache.poi.ss.usermodel.Workbook wb =  WorkbookFactory.create(fis); 
org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);

}

Service关键代码：

 for(int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++ ){  
org.apache.poi.ss.usermodel.Row row = sheet.getRow( rowNum);  
       if(row == null){  
         continue;  
       }  
     //如果品种名称就为空，则不保存
   if(row.getCell(0)==null || getCellValue(row.getCell(0))==null || StringUtils.isBlank(getCellValue(row.getCell(0)).toString()))
    continue;

}

//从Excel读取的手机号码呈科学计数法显示，因此采用DecimalFormat 格式化

private Object getCellValue(org.apache.poi.ss.usermodel.Cell cell){
Object obj =null;
switch(cell.getCellType()){
//字符串
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
obj = cell.getStringCellValue();
break;
//数字 包括日期
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)){
obj=cell.getDateCellValue();
}else{
DecimalFormat df = new DecimalFormat("0");  
obj=df.format(cell.getNumericCellValue());
}
break;
//布尔
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
obj = cell.getBooleanCellValue();
break;

case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
break;
//方程式
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
obj = cell.getCellFormula();
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
break;
default:
break;

}
return obj;

}

此处截一下WorkbookFactory.create的源代码：

 /**
     * Creates the appropriate HSSFWorkbook / XSSFWorkbook from
     *  the given InputStream, which may be password protected.
     * <p>Your input stream MUST either support mark/reset, or
     *  be wrapped as a {@link PushbackInputStream}! Note that
     *  using an {@link InputStream} has a higher memory footprint
     *  than using a {@link File}.</p>
     *
     * <p>Note that in order to properly release resources the
     *  Workbook should be closed after use. Note also that loading
     *  from an InputStream requires more memory than loading
     *  from a File, so prefer {@link #create(File)} where possible.</p>
     *
     *  @param inp The {@link InputStream} to read data from.
     *  @param password The password that should be used or null if no password is necessary.
     *
     *  @return The created Workbook
     *
     *  @throws IOException if an error occurs while reading the data
     *  @throws InvalidFormatException if the contents of the file cannot be parsed into a {@link Workbook}
     *  @throws EncryptedDocumentException If the wrong password is given for a protected file
     *  @throws EmptyFileException If an empty stream is given
     */
    public static Workbook create(InputStream inp, String password) throws IOException, InvalidFormatException, EncryptedDocumentException {
        // If clearly doesn't do mark/reset, wrap up
        if (! inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }


        // Ensure that there is at least some data there
        byte[] header8 = IOUtils.peekFirst8Bytes(inp);


        // Try to create
        if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
            NPOIFSFileSystem fs = new NPOIFSFileSystem(inp);
            return create(fs, password);
        }
        if (POIXMLDocument.hasOOXMLHeader(inp)) {
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
    }

开发过程中一直遇到抛出"Your InputStream was neither an OLE2 stream, nor an OOXML stream" 这个异常，后来才知道产品给的模板Excel是用wps搞的，后来让他换成Excel就好了