Java利用POI实现数据的Excel导入

maven:

 

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.15</version>
</dependency>

 

 

后端：

 

@RequestMapping(value = "/excel/upload", method = RequestMethod.POST)
public synchronized
@ResponseBody
HttpJsonResult<Boolean> excelUpload(HttpServletRequest request, HttpServletResponse response, @RequestParam("file") MultipartFile file) {
    List<Member> memberList = new ArrayList<Member>();
    HttpJsonResult<Boolean> jsonResult = new HttpJsonResult<Boolean>();
    if (!file.isEmpty()) {
        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        if (suffix.equals(".xls") || suffix.equals(".xlsx")) {
            try {
                memberList = readExcel(file.getInputStream());
            } catch (Exception e) {
                jsonResult.setRows(false);
                jsonResult.setMessage("填写Excel格式有误");
                return jsonResult;
            }
        } else {
            jsonResult.setRows(false);
            jsonResult.setMessage("导入的不是Excel文件");
            return jsonResult;
        }
    } else {
        jsonResult.setRows(false);
        jsonResult.setMessage("请选择文件");
        return jsonResult;
    }
}

 

 

 

 

 

public List<Member> readExcel(InputStream inputStream) throws Exception {
    Workbook workbook = WorkbookFactory.create(inputStream);
    List<Member> memberList = new ArrayList<Member>();
    for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
        Sheet sheet = workbook.getSheetAt(numSheet);
        if (sheet == null) {
            continue;
        }
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                Member member = new Member();
                member.setName(getValue(row.getCell(0)));
                member.setPhone(getValue(row.getCell(1)));
                member.setParentPhone(getValue(row.getCell(2)));
                memberList.add(member);
            }
        }
    }
    return memberList;
}

 

 

 

 

 

    //解决excel类型问题，获得数值
    @SuppressWarnings("static-access")
    public String getValue(Cell cell) {
        String value = "";
        if (null == cell) {
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = format.format(date);
                } else {// 纯数字
                    BigDecimal big = new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                    //解决1234.0  去掉后面的.0
                    if (null != value && !"".equals(value.trim())) {
                        String[] item = value.split("[.]");
                        if (1 < item.length && "0".equals(item[1])) {
                            value = item[0];
                        }
                    }
                }
                break;
            //字符串类型
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue().toString();
                break;
            // 公式类型
            case Cell.CELL_TYPE_FORMULA:
                //读公式计算值
          /*      value = String.valueOf(cell.getNumericCellValue());
                if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getStringCellValue().toString();
                }
*/
                value = cell.getCellFormula().toString();

                break;
            // 布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                value = " " + cell.getBooleanCellValue();
                break;
            // 空值
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            // 故障
            case Cell.CELL_TYPE_ERROR:
                value = "";
                break;
            default:
                value = cell.getStringCellValue().toString();
        }
        if ("null".endsWith(value.trim())) {
            value = "";
        }
        return value;
    }

 

前端：

 

<a id="a-excelUpload" href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true">会员EXCEL导入：</a><input id="file" name="file" type="file" >

 

 

 

 

   var excelUpload= function (formData) {
        return $.ajax({
            url:"/admin/teammigration/excel/upload",
            type: 'POST',
            contentType:false,
            processData:false,
            data: formData
        });
    };

    //excel上传
    $('#a-excelUpload').click(function(){
        $.messager.confirm('确认', '确定要导入吗？', function(r){
            if (r){
                $.messager.progress({text:"导入中..."});
                var formData = new FormData($("#EXUpload_form" )[0]);
                excelUpload(formData).then(function (e) {
                    $.messager.show({
                        title : '提示',
                        msg :e.message,
                        showType : 'show'
                    });
                    $.messager.progress('close');
                    $('#dataGrid').datagrid('reload',queryParamsHandler());
                });
            }
        });
    })
});