导出数据到excel文件并下载到当前浏览器中

短命女 2022-06-03 09:39 301阅读 0赞

1、pom.xml添加依赖：

<dependency>
            <groupId>org.apache.poi</groupId>
             <artifactId>poi</artifactId>
             <version>3.10-FINAL</version>
         </dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>3.10-FINAL</version>
    		</dependency>

并添加jxl的jar包:

<dependency>
    	 <groupId>net.sourceforge.jexcelapi</groupId>
    	 <artifactId>jxl</artifactId>
    	 <version>2.6.12</version>
    </dependency>

2、前台：

<%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>稿费统计表</title>
    
    		
    		<script type="text/javascript" src="../assets/jquery.min.js"></script>
    		
    </head>
    
    <script>
    
    
    
    function fun() {
      
       
        var excelForm = document.getElementById("excelForm");
        excelForm.submit();            
    
    }
    
    </script>
    </head>
    <body>
    <button onclick="fun()">导出</button>
    <form id="excelForm"  action="tj" method="post" target="excelIFrame">
            <input type="hidden" name="columns" id="excelData" />
        </form>
        <iframe id="excelIFrame" name="excelIFrame" style="display:none;"></iframe>
    
    
    			
     
    </body>
    
    
    </html>

3、后台：

import jxl.Workbook;
    import jxl.write.Label;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;

//稿费统计表
    	@PostMapping("/tj")
    	@ResponseBody
    	public Message gftj(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		System.out.println("导出");
    		request.setCharacterEncoding("UTF-8");
    		OutputStream out = response.getOutputStream();
    		String fname = "稿费统计表";
    		response.reset();// 清空输出流
    
    		response.setCharacterEncoding("UTF-8");// 设置相应内容的编码格式
    		fname = java.net.URLEncoder.encode(fname, "UTF-8");
    		response.setHeader("Content-Disposition",
    				"attachment;filename=" + new String(fname.getBytes("UTF-8"), "GBK") + ".xls");
    		response.setContentType("application/ms-excel");// 定义输出类型
    		
    		// 获得表头的数据
    		 String rows[] = new String []{"标题","单位","作者","栏目","期数","稿酬","签名"};
    		try {
    			
    			
    			//获取数据
    			Map<String, Object> map = new HashMap<>();
    			List<MsgFxb> list = service.Gfcymx(map); // 获得需要的数据
    		
    			int c=rows.length;//列数
    			int r=list.size();//行数
    			String data[][]=new String[r][c];//
    			for(int i=0;i<r;i++){
    				for(int j=0;j<c;j++){
    					if(j==0){
    						data[i][j]=list.get(i).getXxbt();
    					}
    					if(j==1){
    						data[i][j]=list.get(i).getSbdwmc();
    					}
    					if(j==2){
    						data[i][j]=list.get(i).getZz();
    					}
    					if(j==3){
    						data[i][j]=list.get(i).getLmmc();
    					}
    					if(j==4){
    						data[i][j]=list.get(i).getQs();
    					}
    					if(j==5){
    						data[i][j]=list.get(i).getGc().stripTrailingZeros().toPlainString();
    					}
    				}
    			}
    			
    			// 创建Excel工作薄
    			WritableWorkbook workbook = Workbook.createWorkbook(out); // 创建excel文件
    			// 添加第一个工作表并设置第一个Sheet的名字
    			WritableSheet sheet = workbook.createSheet("grid1", 0); // 创建sheet单元
    			Label label;
    
    			for (int i = 0; i < rows.length; i++) {      // 写入表头到sheet中
    				
    				//Iterator iterator = hm.keySet().iterator();
    				
    				label = new Label(i, 0, rows[i]);
    				
    				sheet.addCell(label);
    			}
    			
    
    			for (int i =1; i <=data.length; i++) {   // 写入数据到sheet中
    				
    				
    				for (int j = 0; j < rows.length-1; j++) {//最后一列为签名，手写
    					System.out.print(data[i-1][j]);
    					label = new Label(j, i, data[i-1][j]);//列,行,值
    					
    					sheet.addCell(label);
    				}
    				System.out.println();
    			}
    
    			workbook.write();    //sheet写入到excel中
    			// 关闭文件
    			workbook.close();
    			out.close();
    			message.setCode(200);
    			message.setMsg("导出成功");
    			System.out.println("导出成功");
    		} catch (Exception e) {
    			message.setCode(300);
    			message.setMsg("服务器繁忙");
    			e.printStackTrace();
    			System.out.println("导出失败");
    		}
    		return message;
    }

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

还可以这样做，对上面的方法稍做改进：

@RequestMapping("/export")
    	@ResponseBody
    	public String getCourseUserInfo(TeacherAdminQueryParam param, Boolean desc,
    			Integer sort,HttpServletRequest request,HttpServletResponse response) throws IOException {
    		
    		
    		if(param ==  null){
    			return JSON.toJSONString(ResultJSONUtil.getFailedInstance("参数缺失"));
    		}
    		if(StringUtils.isEmpty(param.getCourseId())){
    			return JSON.toJSONString(ResultJSONUtil.getFailedInstance("参数缺失"));
    		}
    		request.setCharacterEncoding("UTF-8");
    		OutputStream out = response.getOutputStream();
    		String fname = "课程详情统计表";
    		
    		// 清空输出流
    		response.reset();
    		response.setCharacterEncoding("UTF-8");
    		fname = java.net.URLEncoder.encode(fname, "UTF-8");
    		response.setHeader("Content-Disposition",
    				"attachment;filename=" + new String(fname.getBytes("UTF-8"), "GBK") + ".xls");
    		response.setContentType("application/ms-excel");// 定义输出类型
    		
    		// 定义表头的数据
    		 String rows[] = new String []{"序号","用户id","用户姓名","角色","出勤率","作业完成率","同级续报","同科续报","电话号码"};
    		try {		
    			
    			//获取数据
    			Map<String, Object> map = new  HashMap<String, Object>();
    			List<TeacherAdminCourseDetail> details = teacherAdminCourseDetailService
    					.getTeacherAdminCourseDetailsList(param);
    			//列数
    			int c=rows.length;
    			//行数
    			int r=details.size();
    			
    			
    			// 创建Excel工作薄
    			WritableWorkbook workbook = Workbook.createWorkbook(out); 
    			// 添加第一个工作表并设置第一个Sheet的名字
    			WritableSheet sheet = workbook.createSheet("grid1", 0); 
    			Label label;
    			 // 写入表头到sheet中
    			for (int i = 0; i < rows.length; i++) {     
    				
    				label = new Label(i, 0, rows[i]);
    				
    				sheet.addCell(label);
    			}
    			
    			// 写入数据到sheet中
    			for (int i =1; i <=details.size(); i++) {   
    				TeacherAdminCourseDetail detail = details.get(i-1);
    				
    				for (int j = 0; j < rows.length; j++) {
    					String data="";
    					if( j == 0){
    						//序号
    						data = String.valueOf(i);
    					}
    					if(j == 1){
    						//用户id
    						data = detail.getUserId();
    					}
    					if(j == 2){
    						//用户姓名
    						data = detail.getUserName();
    					}
    					//.......
    					//列,行,值
    					label = new Label(j, i,data);
    					
    					sheet.addCell(label);
    				}
    				//System.out.println();
    			}
     
    			workbook.write();   
    			// 关闭文件
    			workbook.close();
    			
    			out.close();
    			System.out.println("导出成功");
    			return JSON.toJSONString(ResultJSONUtil.getSuccessInstance());
    		} catch (Exception e) {
    			 e.printStackTrace();
    			return JSON.toJSONString(ResultJSONUtil.getFailedInstance("请稍后再试"));
    		}
    		
    }

前端：

js:

export(){
       
       location.href="/demo/teacher/admin/export?courseId="+this.courseId+"&&sort="+this.sort+
        "&&desc="+this.desc+"&&tjxb="+this.tjxb+"&&tkxb="+this.tkxb;
           
            
            
        }