SpringMVC 导入excel表格获取内容，操作后输入页面

所需要的插件： uploadify（上传文件），POI（JAVA对excel的操作）

TIP：要想对本地文件读取，必须将本地文件上传到服务器。因为当其他电脑连接服务器时，不会获得路径

    （ps） <input type="file">控件往往不能获得其绝对路径，因为安全问题

首先，前台：

这里引入uploadify所需文件，js中添加参数因为防止uploadify导致浏览器因缓存问题奔溃

<link href="${base}/resources/admin/uploadify/uploadify.css" rel="stylesheet" type="text/css" />

<script type="text/javascript" src="${base}/resources/admin/uploadify/jquery.uploadify.min.js?time=${.now?date}${.now?time}"></script>

html 中：第二个input用来存放查询出来的表中的值

<input type="file" id="batchImportProduct"/>

<input type="hidden" id="batchImportContent"/>

js里：

     页面初始化时：

     <!-- start 批量导入允许参与商品 -->

    $("#batchImportProduct").uploadify({ 

        'successTimeout' : 50000,

        'height'        : 27,  

        'width'         : 80,   

        'buttonText'    : '浏览', 

        'swf'           : '/MOShop/resources/admin/uploadify/uploadify.swf', 

        'uploader'      : '/MOShop/admin/promotion/batchImportProduct.jhtml?fileType=file', 

        'auto'          : true,

        'multi'          : true, //是否支持多文件上传 

         'simUploadLimit' : 1, //一次同步上传的文件数目    

         'sizeLimit'      : 19871202, //设置单个文件大小限制

         'queueSizeLimit' : 1, //队列中同时存在的文件个数限制

         'fileObjName'    :  'file',

         'fileTypeDesc'  :  '*.xls', //图片选择描述 

        'fileTypeExts'  :  '*.xls',//允许的格式     

        'formData'      : {'token' : getCookie("token") },

        //上传成功 

        'onUploadSuccess' : function( data,response) { 

             alert(data);

             // 这里返回的值data是object，因为没有dataType属性设置json。所以这步就相当于将它转为json型

             data = eval('('+response+')');

             alert(data);

            if(data.length == 0){

                    alert('请选择有效xls文件');

                    return;

               }

               else{

                    var content='';

                    for(var i = 0;i<data.length;i++){

                         content=content+data[i]+',';

                    }

                    alert(content);

                    $batchImportContent.val(content);

                    excelImport(content);

               }

        },

         onComplete: function (event, queueID, fileObj, response, data) {

               

         }, 

         onError: function(event, queueID, fileObj) {    

             alert("文件:" + fileObj.name + "上传失败");    

         }

    });

      <!-- end 批量导入允许参与商品 -->

这里就进入了后台，后台代码：

参数中的file就是获得的文件，

file.getInputStream()获得文件数据流

将单元格中的值循环放入list中

 
/**

 * 批量导入允许参与商品

*

* @param filePath

* @return

*/

@RequestMapping(value = "/batchImportProduct", method = RequestMethod.POST)

 public@ResponseBody 

List < String > batchImportProduct(FileType fileType, MultipartFile file, HttpServletResponse response) {

    List < String > list = new ArrayList < String > ();

    try {

        InputStream is = file.getInputStream();

        HSSFWorkbook wbs = new HSSFWorkbook(is);

        HSSFSheet childSheet = wbs.getSheetAt(0);

        System.out.println("有行数" + childSheet.getLastRowNum());

        for (int j = 0; j <= childSheet.getLastRowNum(); j++) {

            HSSFRow row = childSheet.getRow(j);

            if (null != row) {

                for (int k = 0; k < row.getLastCellNum(); k++) {

                    HSSFCell cell = row.getCell(k);

                    if (null != cell) {

                        switch (cell.getCellType()) {

                        case HSSFCell.CELL_TYPE_NUMERIC:

                            // 数字

                            System.out.print(cell.getNumericCellValue() + "   ");

                            break;

                        case HSSFCell.CELL_TYPE_STRING:

                            // 字符串

                            list.add(cell.getStringCellValue());

                            System.out.print(cell.getStringCellValue() + "   ");

                            break;

                        case HSSFCell.CELL_TYPE_BOOLEAN:

                            // Boolean

                            System.out.println(cell.getBooleanCellValue() + "   ");

                            break;

                        case HSSFCell.CELL_TYPE_FORMULA:

                            // 公式

                            System.out.print(cell.getCellFormula() + "   ");

                            break;

                        case HSSFCell.CELL_TYPE_BLANK:

                            // 空值

                            System.out.println(" ");

                            break;

                        case HSSFCell.CELL_TYPE_ERROR:

                            // 故障

                            System.out.println(" ");

                            break;

                        default:

                            System.out.print("未知类型   ");

                            break;

                        }

                    } else {

                        System.out.print("-   ");

                    }

                }

            }

            System.out.println();

             list.remove(list.get(0));

             //去除重复

       if(list.size()>=2){           for  ( int  i  =   0 ; i  <  list.size()  -   1 ; i ++ )   {            for  ( int  j  =  list.size()  -   1 ; j  ]] >   i; j -- )   {                if  (list.get(j).equals(list.get(i)))   {                 list.remove(j);                }             }           } 

        }

        }

    } catch(Exception e) {

        logger.error(e.getMessage());

    }

   

    return list;

}

POI读取完后台返回了值，执行uploadify的success，其中excelImport函数如下：

这里也是通过ajax对页面进行操作

function excelImport(content){

     var $productTable = $("#productTable");

     var $productTitle = $("#productTitle");

     var productIds = new Array();

     var $batchImportContent = $("#batchImportContent");

     var newContent = $batchImportContent.val();

     $.ajax({

          url: 'excel_product_select.jhtml',

          type: "GET",

          data: {q: newContent},

          dataType: "json",

          success: function(data){

               for(var i = 0; i<data.length;i++){

                    var item = data[i];

var trHtml = '<tr class="productTr"> <th> <input type="hidden" name="productIds" value="' + item.id + '" \/> &nbsp; <\/th> <td> <span title="' + item.fullName + '">' + item.fullName.substring(0, 50) + '<\/span> <\/td> <td> <a href="/MOShop' + item.path + '" target="_blank">[查看]<\/a> <a href="javascript:;" class="deleteProduct">[删除]<\/a> <\/td> <\/tr>';                    $productTitle.show();

                    $productTable.append(trHtml);

                    productIds.push(item.id);

               }

          },

          error: function(){

              

          }

     });

}

     

ajax请求的excel_product_select方法如下：

/**
* 商品选择_导入
*/
@RequestMapping(value = "/excel_product_select", method = RequestMethod.GET)
public@ResponseBody
List<Map<String, Object>> excelProductSelect(String q) { List<Map<String, Object>> data = new ArrayList<Map<String, Object>>(); try{ String [] contents= q.split( ",");  //注意分隔符是需要转译滴... for(String content:contents){    if (StringUtils.isNotEmpty(content)) { List<Product> products = productService.search(content, false, 20); for (Product product : products) { Map<String, Object> map = new HashMap<String, Object>(); map.put( "id", product.getId()); map.put( "sn", product.getSn()); map.put( "fullName", product.getFullName()); map.put( "path", product.getPath()); data.add(map); } } } } catch(Exception e){ logger.error(e.getMessage()); } return data; 

}

返回data 到页面，执行ajax的success的餐作