jxl操作excel

1. 导入jxl.jar。

2. 修改JSP页面中<s:form>标签，务必把enctype设为"multipart/form-data"，这样就可以以二进制形式上传Excel文档。

<s:form id="Form1" action="userInfoImport" method="post" enctype="multipart/form-data" theme="simple" namespace="/MainWeb/Admin/UserInfo">
......
  <s:file cssStyle="width:80%" name="excel" id="fileUpload"></s:file>
  <s:submit value="上传" cssClass="btnStyle" id="checkUpload" onclick="return checkExcel();"></s:submit>
</s:form>

3. 修改struts.xml，务必注意第3、8-12、15行，这几行非常重要。

<struts>
  ......
  <constant name="struts.multipart.saveDir" value="/tmp"/>
  ......
  <package name="UserInfo" extends="struts-default" namespace="/MainWeb/Admin/UserInfo">
    ......
    <action name="userInfoImport" class="userInfoAction" method="userInfoImport">
      <interceptor-ref name="fileUpload">
        <param name="allowedTypes">application/vnd.ms-excel</param>
        <param name="maximumSize">409600</param>
      </interceptor-ref>
      <interceptor-ref name="defaultStack" />
      <result name="FAILURE">/MainWeb/Admin/UserInfo/UserInfo_Import.jsp</result>
      <result name="SUCCESS">/MainWeb/Admin/UserInfo/UserInfo_Import.jsp</result>
      <result name="INPUT">/MainWeb/Admin/UserInfo/UserInfo_Import.jsp</result>
    </action>
  </package>
</struts>

导入数据

List<UserInfo> userInfoList = new ArrayList<UserInfo>();
Workbook book = Workbook.getWorkbook(excel);
Sheet sheet = book.getSheet(0);
int row = sheet.getRows();
for (int i = 1; i < row; i++) {
  UserInfo userInfo = new UserInfo();
  
  //获取数据
  userInfo.setUserId(0);
  userInfo.setUserName(sheet.getCell(0, i).getContents());
  userInfo.setUserPassword(sheet.getCell(1, i).getContents());

  //检测是否存在相同的用户名
  //UserId=1  Conflict  Excel内部重复
  if (userInfoList.size() != 0){
    for (int iUserName=0; iUserName<userInfoList.size(); iUserName++){
      if (userInfo.getUserName().toUpperCase().equals(userInfoList.get(iUserName).getUserName().toUpperCase())){
        userInfoList.get(iUserName).setUserId(1);
        userInfo.setUserId(1);
      }
    }
  }        
  //检测是否存在相同的用户名
  //UserId=2  Exist     数据库内部重复
  List<UserInfo> userInfoListExist = new ArrayList<UserInfo>();
  userInfoListExist = userInfoDao.findByUserName(userInfo.getUserName());
  if (userInfoListExist.size() == 0 || userInfo.getUserId() == 1){
    userInfoList.add(userInfo);
  }else{
    userInfo.setUserId(2);
    userInfoList.add(userInfo);
  }        
}

导出数据

try{
  //设置导出文件为Excel格式，文件名为UserInfo+yyyyMMddHHmmss
  HttpServletResponse response = ServletActionContext.getResponse();
  Date dt = new Date();
  DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
  String sFileName = "UserInfo" + df.format(dt).toString();
  OutputStream ops = response.getOutputStream();
  response.reset();
  response.setHeader("Content-Disposition", "attachment; filename="+ sFileName +".xls");
  response.setContentType("application/msexcel");
  //设置写入的数据表为Sheet1
  WritableWorkbook wwb = Workbook.createWorkbook(ops);
  WritableSheet ws = wwb.createSheet("Sheet1", 0);
  //设置单元格格式
  WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
  WritableCellFormat wcf = new WritableCellFormat(wf);
  wcf.setAlignment(jxl.format.Alignment.CENTRE);
  wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
  //设置列宽
  ws.setColumnView(0, 10);
  ws.setColumnView(1, 20);
  //写入第一行（标题行）
  ws.addCell(new Label(0, 0, "UserName", wcf));
  ws.addCell(new Label(1, 0, "UserPassword", wcf));
  //从数据库中读取内容并依次从第二行写入Excel
  List<UserInfo> userInfoList = userInfoDao.findAll();
  if (userInfoList.size() > 0){
    for (int i=0; i<userInfoList.size(); i++){
      ws.addCell(new Label(0, i+1, userInfoList.get(i).getUserName(), wcf));
      ws.addCell(new Label(1, i+1, userInfoList.get(i).getUserPassword(), wcf));
    }
  }else{
    result = "FAILURE";
    return result;
  }
  //正式写入
  wwb.write();
  //关闭连接
  wwb.close();
  ops.close();
  result = "SUCCESS";
}catch(Exception e){
  result = "FAILURE";
}