用POI实现导出Excel表格的后台java代码

工具类：

import java.sql.Connection;
import java.sql.DriverManager;


public class DbUtil {


private String dbUrl="jdbc:mysql://localhost:3306/kuailezhenzhen";
private String dbUserName="root";
private String dbPassword="123456";
private String jdbcName="com.mysql.jdbc.Driver";

public Connection getCon()throws Exception{
Class.forName(jdbcName);
Connection con=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
return con;
}

public void closeCon(Connection con)throws Exception{
if(con!=null){
con.close();
}
}
}

import java.io.InputStream;
import java.sql.ResultSet;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class ExcelUtil {


public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
int rowIndex=0;
Sheet sheet=wb.createSheet();
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
while(rs.next()){
row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
}

public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/kuailezhenzhen/template/"+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt(0);
// 获取列数
int cellNums=sheet.getRow(0).getLastCellNum();
int rowIndex=1;
while(rs.next()){
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<cellNums;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
return wb;
}

public static String formatCell(HSSFCell hssfCell){
if(hssfCell==null){
return "";
}else{
if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
}

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


public class JsonUtil {


/**
* 把ResultSet集合转换成JsonArray数组
* @param rs
* @return
* @throws Exception
*/
public static JSONArray formatRsToJsonArray(ResultSet rs)throws Exception{
ResultSetMetaData md=rs.getMetaData();
int num=md.getColumnCount();
JSONArray array=new JSONArray();
while(rs.next()){
JSONObject mapOfColValues=new JSONObject();
for(int i=1;i<=num;i++){
mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
}
array.add(mapOfColValues);
}
return array;
}
}

import java.io.OutputStream;
import java.io.PrintWriter;


import javax.servlet.http.HttpServletResponse;


import org.apache.poi.ss.usermodel.Workbook;




public class ResponseUtil {


public static void write(HttpServletResponse response,Object o)throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out=response.getWriter();
out.print(o.toString());
out.flush();
out.close();
}

public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}


}

public class StringUtil {


public static boolean isEmpty(String str){
if("".equals(str)||str==null){
return true;
}else{
return false;
}
}

public static boolean isNotEmpty(String str){
if(!"".equals(str)&&str!=null){
return true;
}else{
return false;
}
}

}

实体类 POJO：

public class PageBean {


private int page; // 第几页
private int rows; // 每页的记录数
private int start; // 起始页

public PageBean(int page, int rows) {
super();
this.page = page;
this.rows = rows;
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getRows() {
return rows;
}
public void setRows(int rows) {
this.rows = rows;
}

public int getStart() {
return (page-1)*rows;
}
}

public class User {


private int id;
private String name;
private String phone;
private String email;
private String qq;

public User() {
}

public User(String name, String phone, String email, String qq) {
this.name = name;
this.phone = phone;
this.email = email;
this.qq = qq;
}

public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getQq() {
return qq;
}
public void setQq(String qq) {
this.qq = qq;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}

DAO：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


import com.java1234.model.PageBean;
import com.java1234.model.User;


public class UserDao {


public ResultSet userList(Connection con,PageBean pageBean)throws Exception{
StringBuffer sb=new StringBuffer("select * from t_user");
if(pageBean!=null){
sb.append(" limit ?,?");
}
PreparedStatement pstmt=con.prepareStatement(sb.toString());
if(pageBean!=null){
pstmt.setInt(1, pageBean.getStart());
pstmt.setInt(2, pageBean.getRows());
}
return pstmt.executeQuery();
}

public int userCount(Connection con)throws Exception{
String sql="select count(*) as total from t_user";
PreparedStatement pstmt=con.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
return rs.getInt("total");
}else{
return 0;
}
}

public int userDelete(Connection con,String delId)throws Exception{
String sql="delete from t_user where id=?";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString(1, delId);
return pstmt.executeUpdate();
}

public int userAdd(Connection con,User user)throws Exception{
String sql="insert into t_user values(null,?,?,?,?)";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPhone());
pstmt.setString(3, user.getEmail());
pstmt.setString(4, user.getQq());
return pstmt.executeUpdate();
}

public int userModify(Connection con,User user)throws Exception{
String sql="update t_user set name=?,phone=?,email=?,qq=? where id=?";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPhone());
pstmt.setString(3, user.getEmail());
pstmt.setString(4, user.getQq());
pstmt.setInt(5,user.getId());
return pstmt.executeUpdate();
} 
}

Servlet：

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;


import com.kuailezhenzhen.dao.UserDao;
import com.kuailezhenzhen.model.PageBean;
import com.kuailezhenzhen.model.User;
import com.kuailezhenzhen.util.DbUtil;
import com.kuailezhenzhen.util.ExcelUtil;
import com.kuailezhenzhen.util.JsonUtil;
import com.kuailezhenzhen.util.ResponseUtil;
import com.kuailezhenzhen.util.StringUtil;
import com.opensymphony.xwork2.ActionSupport;


public class UserAction extends ActionSupport {


/**
* 
*/
private static final long serialVersionUID = 1L;


private String page;
private String rows;
private String id;
private User user;
private String delId;

private File userUploadFile;

public String getPage() {
return page;
}
public void setPage(String page) {
this.page = page;
}
public String getRows() {
return rows;
}
public void setRows(String rows) {
this.rows = rows;
}

public String getDelId() {
return delId;
}
public void setDelId(String delId) {
this.delId = delId;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}


public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}






public File getUserUploadFile() {
return userUploadFile;
}
public void setUserUploadFile(File userUploadFile) {
this.userUploadFile = userUploadFile;
}








DbUtil dbUtil=new DbUtil();
UserDao userDao=new UserDao();

public String list()throws Exception{
Connection con=null;
PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows));
try{
con=dbUtil.getCon();
JSONObject result=new JSONObject();
JSONArray jsonArray=JsonUtil.formatRsToJsonArray(userDao.userList(con, pageBean));
int total=userDao.userCount(con);
result.put("rows", jsonArray);
result.put("total", total);
ResponseUtil.write(ServletActionContext.getResponse(),result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}

public String save()throws Exception{
if(StringUtil.isNotEmpty(id)){
user.setId(Integer.parseInt(id));
}
Connection con=null;
try{
con=dbUtil.getCon();
int saveNums=0;
JSONObject result=new JSONObject();
if(StringUtil.isNotEmpty(id)){
saveNums=userDao.userModify(con, user);
}else{
saveNums=userDao.userAdd(con, user);
}
if(saveNums>0){
result.put("success", "true");
}else{
result.put("success", "true");
result.put("errorMsg", "保存失败");
}
ResponseUtil.write(ServletActionContext.getResponse(), result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}

public String delete()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
JSONObject result=new JSONObject();
int delNums=userDao.userDelete(con, delId);
if(delNums==1){
result.put("success", "true");
}else{
result.put("errorMsg", "删除失败");
}
ResponseUtil.write(ServletActionContext.getResponse(), result);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}


public String export()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
Workbook wb=new HSSFWorkbook();
String headers[]={"编号","姓名","电话","Email","QQ"};
ResultSet rs=userDao.userList(con, null);
ExcelUtil.fillExcelData(rs, wb, headers);
ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}

public String export2()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
ResultSet rs=userDao.userList(con, null);
Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}



public String upload()throws Exception{
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(userUploadFile));
HSSFWorkbook wb=new HSSFWorkbook(fs);
HSSFSheet hssfSheet=wb.getSheetAt(0);  // 获取第一个Sheet页
if(hssfSheet!=null){
for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
HSSFRow hssfRow=hssfSheet.getRow(rowNum);
if(hssfRow==null){
continue;
}
User user=new User();
user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
Connection con=null;
try{
con=dbUtil.getCon();
userDao.userAdd(con, user);
}catch(Exception e){
e.printStackTrace();
}finally{
dbUtil.closeCon(con);
}
}
}
JSONObject result=new JSONObject();
result.put("success", "true");
ResponseUtil.write(ServletActionContext.getResponse(), result);
return null;
}



}