Excel导出

前端代码：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
      
       <a href="${pageContext.request.contextPath }/excelPortss?name=张三">报表导出</a>
      
</body>
</html>

后端代码：

package controller;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
/**
 * 导出excel
 * @author jiyiyuan
 *
 */
@Controller
public class ExcleController {
    
     @RequestMapping(value = "/excelPort")
     public String login() {
        
            /**
             * login代表逻辑视图名称，需要根据Spring MVC配置
             * 文件中internalResourceViewResolver的前缀和后缀找到对应的物理视图
             */
            return "excelPort";
      }
        
    


    //这里直接new了
    ExcleImpl  excleImpl=new ExcleImpl();

    @RequestMapping(value="/excelPortss")    
    @ResponseBody
    //获取url链接上的参数
    public  String dowm(HttpServletResponse response,@RequestParam String name){

        response.setContentType("application/binary;charset=UTF-8");
        try{
            ServletOutputStream out=response.getOutputStream();
            try {
                //设置文件头：最后一个参数是设置下载文件名(这里我们叫：张三.pdf)
                response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(name+".xls", "UTF-8"));
            } catch (UnsupportedEncodingException e1) {
                e1.printStackTrace();
            }

            String[] titles = { "用户id", "用户姓名", "用户密码", "用户年龄" }; 
            excleImpl.export(titles, out);      
            return "success";
        } catch(Exception e){
            e.printStackTrace();
            return "导出信息失败";
        }
    }

}
package controller;

import java.util.ArrayList;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import entity.Person;


public class ExcleImpl {

    public void export(String[] titles, ServletOutputStream out) throws Exception{
        try{
                         // 第一步，创建一个workbook，对应一个Excel文件
                         HSSFWorkbook workbook = new HSSFWorkbook();
                         
                         // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
                         HSSFSheet hssfSheet = workbook.createSheet("sheet1");
                         
                         // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                         
                         HSSFRow row = hssfSheet.createRow(0);
                        // 第四步，创建单元格，并设置值表头 设置表头居中
                         HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
                         
                         //居中样式
                         hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
             
                         HSSFCell hssfCell = null;
                         for (int i = 0; i < titles.length; i++) {
                             hssfCell = row.createCell(i);//列索引从0开始
                             hssfCell.setCellValue(titles[i]);//列名1
                             hssfCell.setCellStyle(hssfCellStyle);//列居中显示                
                         }
                         
                         // 第五步，写入实体数据 
                          Person  person1=new Person("1","张三","123","26");
                          Person  person2=new Person("2","李四","123","18");
                          Person  person3=new Person("3","王五","123","77");
                          Person  person4=new Person("4","徐小筱","123","1");
                          
                          //这里我把list当做数据库啦
                          ArrayList<Person>  list=new ArrayList<Person>();
                          list.add(person1);
                          list.add(person2);
                          list.add(person3);
                          list.add(person4);
                         
                             for (int i = 0; i < list.size(); i++) {
                                 row = hssfSheet.createRow(i+1);                
                                 Person person = list.get(i);
                                 
                                 // 第六步，创建单元格，并设置值
                                 String  id = null;
                                 if(person.getId() != null){
                                         id = person.getId();
                                 }
                                row.createCell(0).setCellValue(id);
                                 String name = "";
                                 if(person.getName() != null){
                                     name = person.getName();
                                 }
                                row.createCell(1).setCellValue(name);
                                 String password = "";
                                 if(person.getPassword() != null){
                                     password = person.getPassword();
                                 }
                                 row.createCell(2).setCellValue(password);
                                 String age=null;
                                 if(person.getAge() !=null){
                                     age = person.getAge();
                                 }
                                 row.createCell(3).setCellValue(age);
                             }
        
                         // 第七步，将文件输出到客户端浏览器
                         try {
                             workbook.write(out);
                             out.flush();
                             out.close();
             
                         } catch (Exception e) {
                             e.printStackTrace();
                         }
                     }catch(Exception e){
                         e.printStackTrace();
                        throw new Exception("导出信息失败！");
                        
                        }
                     }        
}
  <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.9</version>
     </dependency>

 

posted @ 2020-05-29 15:22  一场屠夫的战争  阅读(160)  评论(0)    收藏  举报