jsp中导入导出excel，ssh框架

导入Excel：jsp中

<form action="user_importTradingMoney" enctype="multipart/form-data" method="post">
<label>导入数据
            <input type="file" name="tradingMoneyFile" value="选择文件">
            <input type="submit" value="提交数据">
            </label>
</form>

action中：

action最前面肯定要有下面这几句，而且要有其对应的set、get方法

private File tradingMoneyFile;// 实际上传文件
private String tradingMoneyFileContentType; // 文件的内容类型
private String tradingMoneyFileFileName; // 上传文件名

 这是为了通过struts2自动获得上传的文件，当然struts.xml中肯定要有这几句：

<constant name="struts.i18n.reload" value="true" />
    <constant name="struts.configuration.xml.reload" value="true" />
    <constant name="struts.devMode" value="true" />
    <constant name=" struts.action.extension" value="action,," />
    <constant name="struts.multipart.maxSize" value="102400000000000" />

文件大小什么的设定自己看着办。。。。

然后action中的importTradingMoney方法如下：

//导入交易金额的Excel表
    public String importTradingMoney(){
        HttpServletRequest request = ServletActionContext.getRequest();
        Calendar cal = Calendar.getInstance();
        int month = cal.get(Calendar.MONTH);
        int year = cal.get(Calendar.YEAR);
         jxl.Workbook wb=null;
         try {
            //InputStream is=new FileInputStream(tradingMoneyFile);//读取存放数据的excel表格在电脑中的路径
            InputStream is=new FileInputStream(tradingMoneyFile);
            wb =Workbook.getWorkbook(is);//创建workbook对象，我的理解就是这个整个的excel
            Sheet s=wb.getSheet(0);//创建sheet，其实就是excel中的每一页，其中的每一张表，他的下标是从0开始的//这里也就是取得第一张表
            int rsRows=s.getRows();//得到所有的行
            int rsColumns=s.getColumns();//得到所有的列
            for(int i=1;i<rsRows;i++){
                String userName=s.getCell(0, i).getContents();//(列，行)
                int tradingMoney=Integer.parseInt(s.getCell(1, i).getContents().toString());
                                TradingMoney tradingMoneyClass=new TradingMoney();
                tradingMoneyClass.setUserName(userName);
                tradingMoneyClass.setTradingMoney(tradingMoney);
                tradingMoneyClass.setYear(year);
                tradingMoneyClass.setMonth(month);
                userService.addTradingMoney(tradingMoneyClass);
            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IndexOutOfBoundsException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
         List<User> userList = null;
        try {
            userList = userService.selectUser(null, null);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        request.setAttribute("userList", userList);
        return "selectUser";
    }

---

至于excel的导出，我只是简单地把当前页面复制到了excel中：

<%@page import="java.text.SimpleDateFormat"%>  
  <%
      SimpleDateFormat sf = new SimpleDateFormat("yyyyMMdd");
      String filename = new String(("佣金明细-"+sf.format(new Date())).getBytes("utf8"),"ISO-8859-1"); 
      response.setHeader("Content-disposition","attachment; filename="+filename+".xls"); 
  %>

因为我要复制a.jsp页面的内容到excel中，而a.jsp中的内容又是便利list而出来，如果直接把上面这段代码加入到a.jsp中，那么a.jsp中的内容你就会看不到，这个页面一打开就会让你下载，所以就没有那种先预览再下载的效果了。。。。所以就在a.jsp中加了一个按钮

<input type="button" value="结果导出为Excel" style="width:120px" class="button-action" onclick="toExcel(<s:property value='#request.owerUser.id' />,'${request.owerUser.userAccount }')"/>

var userIdvar,userAccountvar;
        function toExcel(userIdvar,userAccountvar){
//脚本运行后，将在新窗体newwindow中打开，宽为100，高为400，距屏顶0象素，屏左0象素，无工具条，无菜单条，无滚动条，不可调整大小，无地址栏，无状态栏。
window.open('reportUser_selectDetailReportUser?notice=1&userId='+userIdvar+'&userAccount='+userAccountvar,'newwindow','height=600,width=1200,top=80,left=80,toolbar=no,menubar=no,scrollbars=yes, resizable=yes,location=no, status=no');
        };

就是重新发一遍请求到b.jsp中，其内容与a.jsp相同，所以这个b.jsp就不需要打开了。。。一切ok！

 

 

刚刚新增了不是简单地copy页面，而是从action导出excel表：

//导出Excel表
    public void exportForm(){
        HttpServletRequest request = ServletActionContext.getRequest();
        try {
        request.setCharacterEncoding("utf8");

        Date now = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String sDate = sdf.format(now);
        String fileName = new String(("用户-"+sDate).getBytes("utf8"),"ISO-8859-1"); 
        String file = fileName+".xls";

        HttpServletResponse response = ServletActionContext.getResponse();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition" ,"attachment;filename="+file+"");
        OutputStream os = response.getOutputStream();
        String[] title = {"姓名","账号","角色","电话","邮箱","发展人账号","备注"};
        int i=0;

        WritableWorkbook wwb = Workbook.createWorkbook(os);
        WritableSheet ws = wwb.createSheet("用户表", 0);
        ws.setColumnView(0,15);
        ws.setColumnView(1,15);
        ws.setColumnView(2,20);
        ws.setColumnView(3,15);
        ws.setColumnView(4,18);
        ws.setColumnView(5,15);
        ws.setColumnView(6,35);
        ws.setColumnView(7,15);
        

        for( i=0;i<title.length;i++){
              WritableFont wf = new WritableFont(WritableFont.TIMES, 11, WritableFont.BOLD, false);
              WritableCellFormat wcfF = new WritableCellFormat(wf);
              Label labelCF = new Label(i, 0, title[i],wcfF);
              ws.addCell(labelCF);
        }

        i=1;
        String  userName = null;
        String  userAccount = null;
        String  userRole = null;
        String  userPhone = null;
        String  userEmail = null;
        String  higherAgentAccount = null;
        String  userNote = null;

        //根据selectedIds，生成一个List


        //List list = this.pageBean.getList();

        List list = userService.selectUser(null, null);
        Iterator it  = list.iterator(); // 获得一个迭代子

        while (it.hasNext()) {
            User v = (User)it.next(); // 得到下一个元素
                            
            userName = v.getUserName();
            Label labelC0 = new Label(0,i,userName);
            ws.addCell(labelC0);
    
            userAccount = v.getUserAccount();
            Label labelC1 = new Label(1,i,userAccount);
            ws.addCell(labelC1);
    
            userRole = v.getRole().getRoleName();
            Label labelC2 = new Label(2,i,userRole);
            ws.addCell(labelC2);
    
    
            userPhone = v.getUserPhone();
            Label labelC3 = new Label(3,i,userPhone);
            ws.addCell(labelC3);
    
            userEmail = v.getUserEmail();
            Label labelC4 = new Label(4,i,userEmail);
            ws.addCell(labelC4);
    
            higherAgentAccount = v.getHigherAgentAccount();
            Label labelC5 = new Label(5,i,higherAgentAccount);
            ws.addCell(labelC5);
    
            userNote = v.getUserNote();
            Label labelC6 = new Label(6,i,userNote);
            ws.addCell(labelC6);
    
            i++;   
        }
        //写入Exel工作表
        os.flush();
           wwb.write();
           //关闭Excel工作薄对象
           wwb.close();
        os.close();
        } catch (Exception e) {

        e.printStackTrace();
        }
    }

 

//如果想打开并写入一个已经存在的excel，只能通过副本操作
//Excel获得文件 
Workbook wb2=Workbook.getWorkbook(excelFile); 
//打开一个文件的副本，并且指定数据写回到原文件 
WritableWorkbook wwb2= Workbook.createWorkbook(excelFile,wb2); 
WritableSheet ws = wwb2.getSheet(0);