POI 操作Excel表格系列2 --- 读取Excel表格

     这个只操作类只针对Excel 2007+版本（处理类为XSSF），97-2003版本处理类似（处理类为HSSF）。

     采取的操作过程：  由于是从web页面上传Excel表格后进行读取的，所有是先从web 页面上传到一个临时文件夹，然后从临时文件夹读取里面的内容，将里面的内容存储到Document（MongoDB）里面，返回到处理类对数据进行操作。

      web页面上传到临时文件夹 --->  临时文件夹读取内容 ---> 对内容进行处理。

jsp代码

 <inputtype="file"id="excel"name="excel"/>


JavaScript代码
$('#excel' ).change(function(){
    if(!$( this).val()){
        alert(<span style="font-family: Arial, Helvetica, sans-serif;">'please select one excel'</span>);
    } else if(!/\.(xlsx)$/.test($( this).val())){
        alert( 'please select one excel'); 
    } else{
        //---upload  exel
        var formData = new FormData();
        formData.append( 'excel', this.files[0]);
        $.ajax({
            url: 'uploadExcel',
            type: 'post',
            data: formData,
            cache: false,
            dataType: 'json',
            contentType: false,
            processData: false,
            success: function(data){
                if(data.status == 'fail'){
                    alert('fail');
                } else{
                   alert('success');
                }
            }
          
        });
    }
});

controller代码

@RequestMapping(value = "/uploadExcel", method = RequestMethod.POST, produces = "application/json; charset=utf-8")
@ResponseBody
public Map<String, Object> uploadExcel(MultipartHttpServletRequest request)
        throws IllegalStateException, IOException {
    Map<String, Object> result = new HashMap<String, Object>();
    MultipartFile excel = request.getFile("excel");
    String tempPath = request.getServletContext().getInitParameter("tempPath");
    File dir = new File(tempPath);
    if (!dir.exists()) {
        dir.mkdirs();
    }
    File excelFile = null;
    if (excel != null && !excel.isEmpty()) {
        int index = excel.getOriginalFilename().indexOf('.');
        String fileName = new Date().getTime() + + excel.getOriginalFilename().substring(index);
        excelFile = new File(dir, fileName);
        excel.transferTo(excelFile);
    }

    result = ExcelXSSFUtils.readExcel(new FileInputStream(excelFile));

    if (result.get("status").equals("success")) {
        @SuppressWarnings("unchecked")
        List<Document> docs = (List<Document>) result.get("message");

        // ---insert data
        for (Document doc : docs) {
            // --- 数据处理操作---
        }
        result.clear();
        result.put("status", "success");
    }

    return result;
}

Excel 读取操作工具类

private static XSSFWorkbook wb;
private static XSSFSheet xssfSheet;
private static XSSFRow xssfRow;

/**

 *
 * @return 验证不通过 {"status":"fail","message":"this is fail reason"}
 *         验证通过 读取无误 值存在Document {"status":"success","message":List<Document>}
 */
public static Map<String, Object> readExcel(InputStream is) {
    Map<String, Object> result = new HashMap<String, Object>();
    try {
        wb = new XSSFWorkbook(is);
    } catch (IOException e) {
        result.put("status", "fail");
        result.put("message", "Please make sure the Excel version >= 2007");
        return result;
    }
    xssfSheet = wb.getSheetAt(0);
    // 得到总行数
    int rowNum = xssfSheet.getLastRowNum();
    // 得到总列数 根据第一行
    xssfRow = xssfSheet.getRow(0);
    int colNum = xssfRow.getPhysicalNumberOfCells();

    List<Document> docs = new ArrayList<Document>();

    for (int i = 1; i <= rowNum; i++) {
        xssfRow = xssfSheet.getRow(i);
        Document doc = new Document();

        for (int j = 0; j < colNum; j++) {
            XSSFCell cell = xssfRow.getCell(j);

            if (j == 0) {
                result = cellFormat(cell, XSSFCell.CELL_TYPE_STRING, i, j);
                if (result.get("status").equals("fail")) {
                    return result;
                }
                XSSFCellStyle cellStyle = cell.getCellStyle();
                XSSFColor color = cellStyle.getFillForegroundXSSFColor();
                doc.put(xssfSheet.getRow(0).getCell(j).getStringCellValue(), cell.getStringCellValue());
                doc.put("color",
                        color == null ? "FFFFFF" : color.getARGBHex().substring(2,
                                color.getARGBHex().length()));

                continue;
            }

            if (j == 1 || j == 2) {
                result = cellFormat(cell, XSSFCell.CELL_TYPE_STRING, i, j);
                if (result.get("status").equals("fail")) {
                    return result;
                }
                doc.put(xssfSheet.getRow(0).getCell(j).getStringCellValue(), cell.getStringCellValue());
                continue;
            }

            if (j == 3 || j == 4 || j == 5) {
                result = cellFormat(cell, XSSFCell.CELL_TYPE_NUMERIC, i, j);
                if (result.get("status").equals("fail")) {
                    return result;
                }
                doc.put(xssfSheet.getRow(0).getCell(j).getStringCellValue(), cell.getNumericCellValue());
                continue;
            }

            if (j == 6 || j == 7 || j == 8 || j == 9 || j == 10 || j == 11) {
                result = cellFormat(cell, XSSFCell.CELL_TYPE_BOOLEAN, i, j);
                if (result.get("status").equals("fail")) {
                    return result;
                }
                doc.put(xssfSheet.getRow(0).getCell(j).getStringCellValue(), cell.getBooleanCellValue());
                continue;
            }

        }
        docs.add(doc);
    }
    result.put("status", "success");
    result.put("message", docs);
    return result;
}

/**
 *
 * @param cell
 *            验证cell的类型是否符合规范 不符合 返回result =
 *            {"status":"fail","message":"this is fail reason"}
 *            符合规定类型 返回result =
 *            {"status":"success"}
 */
public static Map<String, Object> cellFormat(XSSFCell cell, int validateType, int rowNum, int colNum) {
    Map<String, Object> result = new HashMap<String, Object>();
    if (cell == null || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
        result.put("status", "fail");
        result.put("message", (rowNum + 1) + " row " + (colNum + 1) + "cell is empty");
        return result;
    }
    if (cell.getCellType() != validateType) {
        result.put("status", "fail");
        result.put("message", (rowNum + 1) + " row " + (colNum + 1) + "cell's format wrong");
        return result;
    }
    result.put("status", "success");
    return result;
}