你的位置:首页 > Java教程

[Java教程]按照TYPE的文件导入导出功能


/**

* 导入文件Action;
*/
private File excelFile;
// 保存原始文件名
private String excelFileFileName;
// 保存原始文件名
private String importResult;

// 将Excel文件解析完毕后信息存放到这个User对象中
private ExcelWorkSheet<TabUser> excelUserSheet;

/**
* 文件导入

* @return
*/
@Action(value = "importStuUser", results = { @Result(name = "success", location = "/view/student/queryStu/stuGrid.jsp") })
public String importStuUser() {
  try {
    Workbook workbook = createWorkBook(new FileInputStream(excelFile));

    if (roleType.equals("a")) {
      Sheet sheet = workbook.getSheetAt(0);

      excelUserSheet = new ExcelWorkSheet<TabUser>();

      Row firstRow = sheet.getRow(0);
      Iterator<Cell> iterator = firstRow.iterator();

      List<String> cellNames = new ArrayList<String>();
      while (iterator.hasNext()) {
        cellNames.add(iterator.next().getStringCellValue());
      }
      excelUserSheet.setColumns(cellNames);
      for (int i = 0; i < sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        TabRole tabRole = studentService.queryRoleByType(roleType);
        TabUser tabUser = new TabUser();
      if (row.getCell(0).getNumericCellValue() != 0) {
        tabUser.setUserId((int) row.getCell(0).getNumericCellValue());
      } else {
        throw new Exception();
      }
      tabUser.setUserCode(row.getCell(1).getStringCellValue());
      tabUser.setUserName(row.getCell(2).getStringCellValue());
      tabUser.setUserPassword(row.getCell(3).getStringCellValue());

      excelUserSheet.getData().add(tabUser);
      studentService.saveUser(tabUser);

      studentService.saveUserAndRole(tabUser, tabRole);
      }
    } else if (roleType.equals("t")) {
      Sheet sheet = workbook.getSheetAt(0);

      excelUserSheet = new ExcelWorkSheet<TabUser>();

      Row firstRow = sheet.getRow(0);
      Iterator<Cell> iterator = firstRow.iterator();

      List<String> cellNames = new ArrayList<String>();
    while (iterator.hasNext()) {
      cellNames.add(iterator.next().getStringCellValue());
    }
      excelUserSheet.setColumns(cellNames);
    for (int i = 0; i < sheet.getLastRowNum(); i++) {
      Row row = sheet.getRow(i);
      TabRole tabRole = studentService.queryRoleByType(roleType);
      TabUser tabUser = new TabUser();


      if (row.getCell(0).getNumericCellValue() != 0) {
        tabUser.setUserId((int) row.getCell(0).getNumericCellValue());
      } else {
        throw new Exception();
      }
        tabUser.setUserCode(row.getCell(1).getStringCellValue());
        tabUser.setUserName(row.getCell(2).getStringCellValue());
        tabUser.setUserPassword(row.getCell(3).getStringCellValue());

        excelUserSheet.getData().add(tabUser);
        studentService.saveUser(tabUser);
        studentService.saveUserAndRole(tabUser, tabRole);
      }
    } else if (roleType.equals("s")) {
      Sheet sheet = workbook.getSheetAt(0);

      excelUserSheet = new ExcelWorkSheet<TabUser>();

      Row firstRow = sheet.getRow(0);
      Iterator<Cell> iterator = firstRow.iterator();

      List<String> cellNames = new ArrayList<String>();
    while (iterator.hasNext()) {
      cellNames.add(iterator.next().getStringCellValue());
    }
    excelUserSheet.setColumns(cellNames);
    for (int i = 0; i < sheet.getLastRowNum(); i++) {
      Row row = sheet.getRow(i);
      TabRole tabRole = studentService.queryRoleByType(roleType);
      TabUser tabUser = new TabUser();


      if (row.getCell(0).getNumericCellValue() != 0) {
        tabUser.setUserId((int) row.getCell(0).getNumericCellValue());
      } else {
        throw new Exception();
      }
      tabUser.setUserCode(row.getCell(1).getStringCellValue());
      tabUser.setUserName(row.getCell(2).getStringCellValue());
      tabUser.setUserPassword(row.getCell(3).getStringCellValue());

      excelUserSheet.getData().add(tabUser);
      studentService.saveUser(tabUser);
      studentService.saveUserAndRole(tabUser, tabRole);
      }
    }
    importResult = "ok";
    versionService.updateVersionInformation("user");
    } catch (Exception e) {
      importResult = "fail";
      e.printStackTrace();
    }

   return SUCCESS;
}

private String format = "xls"; 
private String fileName = "导出数据.xls";

/** 导出数据 */
private void exportExcel(OutputStream os) {
  Workbook book = new HSSFWorkbook();
  Sheet sheet = book.createSheet("导出信息");
  Row row = sheet.createRow(0);
  row.createCell(0).setCellValue("userId");
  row.createCell(1).setCellValue("userCode");
  row.createCell(2).setCellValue("userName");
  row.createCell(3).setCellValue("userPassword");

  if (roleType.equals("a")) {
    reports = studentService.queryUserByType(roleType);

    for (int i = 0; i < reports.size(); i++) {
      TabUser tabUser = reports.get(i);
      row = sheet.createRow(i);
      row.createCell(0).setCellValue(tabUser.getUserId());
      row.createCell(1).setCellValue(tabUser.getUserCode());
      row.createCell(2).setCellValue(tabUser.getUserName());
      row.createCell(3).setCellValue(tabUser.getUserPassword());
    }

   } else if (roleType.equals("t")) {
   reports = studentService.queryUserByType(roleType);

   for (int i = 0; i < reports.size(); i++) {
    TabUser tabUser = reports.get(i);
    row = sheet.createRow(i);
    row.createCell(0).setCellValue(tabUser.getUserId());
    row.createCell(1).setCellValue(tabUser.getUserCode());
    row.createCell(2).setCellValue(tabUser.getUserName());
    row.createCell(3).setCellValue(tabUser.getUserPassword());
   }

  } else if (roleType.equals("s")) {
    reports = studentService.queryUserByType(roleType);

    for (int i = 0; i < reports.size(); i++) {
      TabUser tabUser = reports.get(i);
      row = sheet.createRow(i);
      row.createCell(0).setCellValue(tabUser.getUserId());
      row.createCell(1).setCellValue(tabUser.getUserCode());
      row.createCell(2).setCellValue(tabUser.getUserName());
      row.createCell(3).setCellValue(tabUser.getUserPassword());
    }
  }

  try {
    book.write(os);
  } catch (Exception ex) {
    ex.printStackTrace();
  }
}

@Action(value = "exportStuUser")
public String exportStuUser() throws Exception {

  setResponseHeader();
  try {
    exportExcel(response.getOutputStream());
    response.getOutputStream().flush();
    response.getOutputStream().close();
  } catch (IOException e) {
    e.printStackTrace();
  }
  return null;
}

/** 设置响应头 */
public void setResponseHeader() {
  try {
    response.setContentType("application/msexcel;charset=UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(this.fileName, "UTF-8"));
    // 客户端不缓存
    response.addHeader("Pargam", "no-cache");
    response.addHeader("Cache-Control", "no-cache");
  } catch (Exception ex) {
    ex.printStackTrace();
  }
}

/**
* 判断导入文件格式;
*/
private Workbook createWorkBook(FileInputStream fileInputStream)throws Exception {
  if (excelFileFileName.toLowerCase().endsWith("xls")) {
    return new HSSFWorkbook(fileInputStream);
  }
  if (excelFileFileName.toLowerCase().endsWith("xlsx")) {
    return new XSSFWorkbook(fileInputStream);
  }
  return null;
}

 

js部分;

<div >
  <div >
    <div >
      <%-- 记住这里需要设置enctype="multipart/form-data"--%> 
      <s:form id = "importForm" action="/student/importStuUser.action" method="post" enctype="multipart/form-data"> 
        <input type="hidden" name="roleType" id="roleType" value="a"/>
          导入Excel文件:<s:file id="excelFile" name="excelFile"></s:file> <br/>
        <s:submit value="导入"></s:submit>
      </s:form>
      <s:form name="form1" action="/student/exportStuUser.action" method="post">
        <input type="hidden" name="format" value="xls" />
        <input type="hidden" name="roleType" id="roleTypeSecond" value="a"/>
        <s:submit name="sub" value="导出数据"></s:submit>
      </s:form>

      <div >
        <button roleType="a">admin</button>&nbsp;&nbsp;&nbsp;&nbsp;

        <button roleType="t">teacher</button>&nbsp;&nbsp;&nbsp;&nbsp;

        <button roleType="s">student</button>
      </div>

    </div>

  </div>

</div>

<s:if test="importResult=='ok'">
  <script>
    alert("文件导入成功!");
  </script>
</s:if>
<s:elseif test="importResult=='fail'">
  <script>
    alert("导入文件存在错误或空,请核对后再导入!");
  </script>
</s:elseif>

<script type="text/javascript">
  var roleType = 'a';
  $(function(){

    $(".ats").click(function(){
      roleType = $(this).attr("roleType");
    $("#roleType").val(roleType);
    $("#roleTypeSecond").val(roleType);

    if(!$("#gridTable_ilcancel").hasClass('ui-state-disabled')){
      $("#gridTable_ilcancel").trigger("click");
    }
    $("#gridTable").jqGrid("clearGridData");
    $.ajax({
      type: 'POST',
      url: "${pageContext.request.contextPath }/student/queryStu.action",
      data: {
        type: 'json',
        roleType:roleType
      },
      success: function(data) {
        for ( var i = 0; i <= data.length; i++){
          $("#gridTable").jqGrid('addRowData', i + 1, data[i]);
        }

      }
    });

  });

});