教你一分钟解锁新技能(上传Excel,提取excel展示)

最近在项目(Maven项目)中遇到一个需求: 上传Excel文件,提取其中的内容进行展示.你是不是也曾遇到过这样的问题呢?那么接下来我简单说一下我的解决方案吧,整理如下,仅供参考:
首先在配置文件中引入jar包

  <dependency>
     <groupId>net.sf.jxls</groupId>
     <artifactId>jxls-core</artifactId>
     <version>1.0.3</version>
  </dependency>
  <dependency>
     <groupId>org.jxls</groupId>
     <artifactId>jxls-reader</artifactId>
     <version>2.0.3</version>
  </dependency>
  <dependency>
     <groupId>net.sourceforge.jexcelapi</groupId>
     <artifactId>jxl</artifactId>
     <version>2.6.12</version>
  </dependency>

然后写后台方法,正式环境中涉及文件加密,如无加密,可以不用解密

 public  List<ContractProjectChildVO> readExcel(String fileName) throws  BiffException, IOException  {
  System.setProperty("user.dir", "/opt/weichai/Users/jiami");
  InteKey mInteKey = new InteKey();
  System.out.println("开始验证");
  int ia = mInteKey.Ia(Global.excel_read+fileName);
  if(ia==0){
   //加密文件，需要做解密处理
   System.out.println("开始解密");
   int da = mInteKey.Da(Global.excel_read+fileName, Global.excel_read+fileName);
   System.out.println("解密结果："+da);
  }
  File xlsFile = new File(Global.excel_read+fileName);
  // 获得工作簿对象
  Workbook workbook = Workbook.getWorkbook(xlsFile);
  // 获得所有工作表
  Sheet[] sheets = workbook.getSheets();
  List<ContractProjectChildVO> queryProjectChildList = new ArrayList<ContractProjectChildVO>();
  // 遍历工作表
  if (sheets != null) {
   for (Sheet sheet : sheets) {
    // 获得行数
    int rows = sheet.getRows();
    // 获得列数
    int cols = sheet.getColumns();
    if(cols!=5) break;  //格式判断,避免随便上传excel
    // 读取数据
    for (int row = 0; row < rows; row++) {
     ContractProjectChildVO co = new ContractProjectChildVO();
     for (int col = 0; col < cols; col++) {
      Cell cell = sheet.getCell(col, row);  
      if(row>0){
       switch (col) {
           case 0:
        co.setProjectChildCode(cell.getContents());
        break;
        case 1:
         co.setProjectChildContent(cell.getContents());
         break;
        case 2:
         //处理金额,表格内容为空时,默认赋值0
         if(!DataCheck.isNull(cell.getContents())){
          co.setProjectChildReckon(new BigDecimal(cell.getContents()));
         }else{
          co.setProjectChildReckon(new BigDecimal("0"));
         }
         break;
        case 3:
         co.setRemark(cell.getContents());
         break;
        case 4:
         co.setContractNames(cell.getContents());
         break;
         }
      }
     }
     if(row>0){
      queryProjectChildList.add(co);
     }
    }
   }
  }
  workbook.close();
  return queryProjectChildList;
 }

最后前端js调用

 function readExcel() {
  if(!$('.importExcel .cxqx.l_id').find('a')) {
   myApp.showMsg("提示","请上传excel!");
  } else {
    // var code = $('.cxqx.l_id').find('a').parent().prop('id'); // excel的id
    var code = $('.cxqx.l_id').find('td:last-child').prop('id'); // excel的id
    //var code = "1565962724814";
    http.synchroAjax('/law/contract/project/readExcel.htm', {fileId:code}, function(data) {
     if(data=='0'){
      myApp.showMsg("提示","请删除当前excel,并上传正确格式的excel!");
     }else if(data=='1'){
      myApp.showMsg("提示","请上传excel!");
     }else{
      var mm = data.readExcel;
         $('#info1 tr').remove();
          $('.importExcel .cxqx').children().remove();
          $('.importExcel .cxqx').empty();
         for (var i = 0; i < mm.length; i++) {
          var m = mm[i];
          var n = i+1;
          $('#info1').append('<tr class="overWidth">'
            +'<td width="5%px"><span data-name="id">'+ n + '</span></td>'
            +'<td><input class="form-control" type="text" value="'+ m.projectChildCode + '" placeholder="请输入子项目编码" data-len="0-255" data-label="子项目编码"></td>'
            +'<td><input class="form-control" type="text" value="'+ m.projectChildContent + '" placeholder="请输入子项目内容" data-len="0-255" data-label="子项目内容"></td>'
            +'<td><input class="form-control" type="number" value="'+ m.projectChildReckon + '" placeholder="请输入子项目金额" data-len="0-255" data-label="子项目金额"></td>'
            +'<td><input class="form-control" type="text" value="'+ m.remark + '" placeholder="请输入备注" data-len="0-255" data-label="备注"></td>'
            +' <td><input class="form-control" type="text" value="'+ m.contractNames + '" placeholder="请输入子项目合同" data-len="0-255" data-label="子项目合同"></td>'
            +'<td class="control" width="5%px"><span type="button" class="btn btn-size btn-blueLight">删除</span></td>'
            +' </tr>');
         }
     }
           }); 
  }
 }

上传excel测试

点击抓取, 这里做了个处理,抓取完成后自动删除上传的excel文件

你学会了吗?如果有什么问题可以私聊也可以给我留言哦!