java/excel上传，解析，导入数据库

来源：互联网发布：有源码怎么做网站编辑：程序博客网时间：2024/05/22 17:22

这篇文章是介绍java中的poi技术读取Excel数据，然后保存到MySQL数据中。

------------------------------jsp页面-----------------------------

//点击上传时判断是否选择文件
var fileSizeValidate=function()
{
  var p1 = document.getElementById("file").value;
  if(p1==""){
   return false;
  }
  return true;
}

function input(){

   if(!fileSizeValidate()){
   alert("请先选择文件！");
   return ;
  }else{
  document.getElementById("inputFile").submit();

  }

}

</script>

<s:form action="upload_upExcel.action" method="post" enctype="multipart/form-data" id="inputFile">
  <table >
   <tr>
    
    <td class="font_colors03"><label>指定导入文件： </label> <s:file
      name="file" id="file"></s:file></td>
    <td><label>
      <input id="upsubmit" type="button" value="导入" onclick="input();"
      style="width:50px;height:25px" />

      </label>
      </td>
   </tr>
  </table>
</s:form>

---------------------------------------------------action-----------------------------------------------

// 上传文件集合
  private File file;
  // 上传文件名集合
  private List<String> fileFileName;

public String upExcel() throws LogisticException {

               try {
                 if (file != null) {
         InputStream in = new FileInputStream(file);
        String pathString = "D:/upload";

        String fileNameString = peopleCode
      + "-"
      + this.getFileFileName().get(0);

File upLoadFile = new File(pathString, fileNameString);
System.out.println("导入文件：" + fileNameString);

    OutputStream out = new FileOutputStream(upLoadFile);

    byte[] buffer = new byte[1024 * 1024];
    int len = 0;
    while ((len = in.read(buffer)) > 0) {
     out.write(buffer, 0, len);
    }

jieXiExcel(fileNameString);

    in.close();
    out.close();
                 }
           } catch (Exception e) {
   e.printStackTrace();
  }

/**
   * 解析文件方法
   *
   * @param fileName
   * @return map
   */

  public Map<String, String> jieXiExcel(String fileName) {
   Map<String, String> map = new HashMap<String, String>();
   try {
    // 通过Workbook的静态方法getWorkbook选取Excel文件
    String parhString = "D:/upload";
    Workbook workbook = Workbook.getWorkbook(new File(parhString + "/"
      + fileName));
    // 通过Workbook的getSheet方法选择第一个工作簿（从0开始）
    Sheet sheet = workbook.getSheet(0);
    int rows = sheet.getRows();
    int cols = sheet.getColumns();
    Cell cells[][] = new Cell[cols][rows];
    Date date = null;

    for (int r = 0; r < rows; r++) {
     for (int c = 1; c < cols - 1; c++) {
      cells[c][r] = sheet.getCell(c, r);
      // String value = "";
      String cellVal = "";

cellVal = cells[c][r].getContents();

map.put("cells[" + c + "][" + r + "]", cellVal);

     }
    }
    for (int rr = 0; rr < rows; rr++) {
     Backdetail backdetail = new Backdetail();
     backdetail.setSceneType(0);
    ..............

for (int cc = 1; cc < cols - 1; cc++) {
String val = map.get("cells[" + cc + "][" + rr + "]");

      if (cc == 1) {
            backdetail.setBackdate(val);
      }
      if (cc == 2) {
       backdetail.setBackscene(val);
      }
      if (cc == 3) {
       backdetail.setInscene(val);
      }
      .............      }

// System.out.println(map.get("cell["+cc+"]["+rr+"]"));

}

// 保存数据到库
backDetailImpl.save(backdetail);

}

System.out.println("导入数据完成!");
workbook.close();

   } catch (BiffException e) {
    e.printStackTrace();
   } catch (IOException e) {
    e.printStackTrace();
   } catch (Exception e) {
    e.printStackTrace();
   }
   return map;
  }

0 0