apache poi是apache软件基金会的开放源码函式库,poi提供api给java程序对microsoft office格式档案读和写的功能。
这里的方法支持导出excel至项目所在服务器,或导出至客户端浏览器供用户下载,下面我把两个实例都放出来。
1.下载所需poi的jar包,并导入项目。
2.添加一个user类,用于存放用户实体,类中内容如下:
1 package com.mvc.po;
2
3 public class user {
4 private int id;
5 private string name;
6 private string password;
7 private int age;
8
9 public user() {
10
11 }
12
13 public user(int id, string name, string password, int age) {
14 this.id = id;
15 this.name = name;
16 this.password = password;
17 this.age = age;
18 }
19 public int getid() {
20 return id;
21 }
22 public void setid(int id) {
23 this.id = id;
24 }
25 public string getname() {
26 return name;
27 }
28 public void setname(string name) {
29 this.name = name;
30 }
31 public string getpassword() {
32 return password;
33 }
34 public void setpassword(string password) {
35 this.password = password;
36 }
37 public int getage() {
38 return age;
39 }
40 public void setage(int age) {
41 this.age = age;
42 }
43 }
3.添加一个usercontroller类,类中内容如下:
1 package com.mvc.controller;
2
3 import java.text.simpledateformat;
4 import java.util.date;
5
6 import javax.servlet.servletoutputstream;
7 import javax.servlet.http.httpservletresponse;
8
9 import org.springframework.stereotype.controller;
10 import org.springframework.beans.factory.annotation.autowired;
11 import org.springframework.web.bind.annotation.requestmapping;
12 import org.springframework.web.bind.annotation.responsebody;
13
14 import com.mvc.po.user;
15 import com.mvc.service.userservice;
16
17 @controller
18 public class usercontroller {
19
20 @autowired
21 private userservice userservice;
22
23 @requestmapping("/export.do")
24 public @responsebody string export(httpservletresponse response){
25 response.setcontenttype("application/binary;charset=utf-8");
26 try{
27 servletoutputstream out=response.getoutputstream();
28 string filename=new string(("userinfo "+ new simpledateformat("yyyy-mm-dd").format(new date())).getbytes(),"utf-8");
29 response.setheader("content-disposition", "attachment; filename=" + filename + ".xls");
30 string[] titles = { "用户编号", "用户姓名", "用户密码", "用户年龄" };
31 userservice.export(titles, out);
32 return "success";
33 } catch(exception e){
34 e.printstacktrace();
35 return "导出信息失败";
36 }
37 }
38 }
4.添加一个接口类userservice和实现类userserviceimpl,类中内容如下:
1 package com.mvc.service;
2
3 import javax.servlet.servletoutputstream;
4 import com.mvc.po.user;
5
6 public interface userservice {
7 public void export(string[] titles, servletoutputstream out);
8 }
1 package com.mvc.service.impl;
2
3 import java.text.simpledateformat;
4 import java.util.list;
5
6 import javax.servlet.servletoutputstream;
7
8 import com.mvc.dao.userdao;
9 import com.mvc.po.user;
10 import com.mvc.service.userservice;
11
12 import org.apache.poi.hssf.usermodel.hssfcell;
13 import org.apache.poi.hssf.usermodel.hssfcellstyle;
14 import org.apache.poi.hssf.usermodel.hssfrow;
15 import org.apache.poi.hssf.usermodel.hssfsheet;
16 import org.apache.poi.hssf.usermodel.hssfworkbook;
17 import org.springframework.beans.factory.annotation.autowired;
18 import org.springframework.stereotype.service;
19
20 @service
21 public class userserviceimpl implements userservice {
22
23 @autowired
24 private userdao userdao;
25
26 @override
27 public void export(string[] titles, servletoutputstream out) {
28 try{
29 // 第一步,创建一个workbook,对应一个excel文件
30 hssfworkbook workbook = new hssfworkbook();
31 // 第二步,在webbook中添加一个sheet,对应excel文件中的sheet
32 hssfsheet hssfsheet = workbook.createsheet("sheet1");
33 // 第三步,在sheet中添加表头第0行,注意老版本poi对excel的行数列数有限制short
34 hssfrow hssfrow = hssfsheet.createrow(0);
35 // 第四步,创建单元格,并设置值表头 设置表头居中
36 hssfcellstyle hssfcellstyle = workbook.createcellstyle();
37 //居中样式
38 hssfcellstyle.setalignment(hssfcellstyle.align_center);
39
40 hssfcell hssfcell = null;
41 for (int i = 0; i < titles.length;="" i++)="" {="" 42="" hssfcell="hssfrow.createcell(i);//列索引从0开始" 43="" hssfcell.setcellvalue(titles[i]);//列名1="" 44="" hssfcell.setcellstyle(hssfcellstyle);//列居中显示="" 45="" }="" 46="" 47="" 第五步,写入实体数据="" 48="">
49
50 simpledateformat sdf=new simpledateformat("yyyy-mm-dd");
51 if(users != null && !users.isempty()){
52 for (int i = 0; i < users.size();="" i++)="" {="" 53="" hssfrow="hssfsheet.createrow(i+1);" 54="" user="" user="users.get(i);" 55="" 56="" 第六步,创建单元格,并设置值="" 57="" int="" userid="0;" 58="" if(user.getid()="" !="0){" 59="" userid="user.getid();" 60="" }="" 61="" hssfrow.createcell(0).setcellvalue(userid);="" 62="" string="" username="" ;="" 63="" if(user.getname()="" !="null){" 64="" username="user.getname();" 65="" }="" 66="" hssfrow.createcell(1).setcellvalue(username);="" 67="" string="" password="" ;="" 68="" if(user.getpassword()="" !="null){" 69="" password="user.getpassword();" 70="" }="" 71="" hssfrow.createcell(2).setcellvalue(password);="" 72="" int="" age="0;" 73="" if(user.getage()="" !="0){" 74="" age="user.getage();" 75="" }="" 76="" hssfrow.createcell(3).setcellvalue(age);="" 77="" }="" 78="" }="" 79="" 80="" 第七步,将文件输出到客户端浏览器="" 81="" try="" {="" 82="" workbook.write(out);="" 83="" out.flush();="" 84="" out.close();="" 85="" 86="" }="" catch="" (exception="" e)="" {="" 87="" e.printstacktrace();="" 88="" }="" 89="" }catch(exception="" e){="" 90="" e.printstacktrace();="" 91="" throw="" new="" exception("导出信息失败!");="" 92="" }="" 93="" }="" 94="" }="" 5.添加一个接口类userdao和实现类userdaoimpl,类中内容如下:="" 1="" package="" com.mvc.dao;="" 2="" 3="" import="" java.util.list;="" 4="" import="" com.mvc.po.user;="" 5="" 6="" public="" interface="" userdao="" {="" 7="">
8 }
1 package com.mvc.dao.impl;
2
3 import java.util.list;
4 import java.sql.resultset;
5 import java.sql.sqlexception;
6
7 import com.mvc.dao.userdao;
8 import com.mvc.po.user;
9
10 import org.springframework.stereotype.repository;
11 import org.springframework.beans.factory.annotation.autowired;
12 import org.springframework.jdbc.core.jdbctemplate;
13 import org.springframework.jdbc.core.rowmapper;
14
15 @repository
16 public class userdaoimpl implements userdao {
17
18 @autowired
19 private jdbctemplate jdbctemplate;
20
21 public list
22 return this.jdbctemplate.query("select * from student",
23 new rowmapper
24 public user maprow(resultset rs, int arg1)
25 throws sqlexception {
26 return new user(rs.getint("sid"),
27 rs.getstring("sname"), rs.getstring("spwd"), rs
28 .getint("sage"));
29 }
30 });
31 }
32 }
这样就完成了excel导出至客户端浏览器,当然有时候也会用到导出excel至服务器上。只需要对本文步骤4中的第七步文件输出方式进行修改,如下:
1 // 第七步,将文件存到指定位置
2 try {
3 fileoutputstream fileoutputstream = new fileoutputstream("c:/user.xls");//指定路径与名字和格式
4 workbook.write(fileoutputstream);//讲数据写出去
5 fileoutputstream.close();//关闭输出流
6 } catch (exception e) {
7 e.printstacktrace();
8 }
然后去除controller类中的out参数设置就ok了。也可以看出其实两种方式只是最终保存方式不同,其他步骤是共通的。