1,ExcelReportHandler类
package com.poi.excel;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
/**
* 导出excel数据
* @author Administrator
*
*/
public abstract class ExcelReportHandler {
/**
* 默认的头部样式
*/
protected HSSFCellStyle defaultHeadCellStyle;
/**
* 默认的体部样式
*/
protected HSSFCellStyle defaultBodyCellStye;
protected HSSFFont defaultHeadFont;
protected HSSFFont defaultBodyFont;
/**
* 导出excel文件
* @param response
* @param headTitle 标题列
* @param fileName 文件名称
* @param list 数据集合
* @throws Exception
*/
public void exportExcel(HttpServletResponse response, String[] headTitle, String fileName, List list) throws Exception {
response.reset();
response.setHeader("Content-disposition", "attachment; filename=\"" + fileName + ".xls\"");// 设置response的Header
ServletOutputStream out = response.getOutputStream();// 得到输出流
HSSFWorkbook wb = new HSSFWorkbook();// 实例化工作薄对象
defaultHeadCellStyle = wb.createCellStyle();
defaultBodyCellStye = wb.createCellStyle();
defaultHeadFont = wb.createFont();
defaultBodyFont = wb.createFont();
HSSFSheet sheet = wb.createSheet();// 创建sheet
createHead(sheet, headTitle);// 创建execl头
createBody(sheet, list);// 创建execl体
wb.write(out);// 把工作薄写入到输出流
out.flush();// 刷新流
out.close();// 关闭流
}
/**
* 创建标题列
* @param sheet
* @param headTitle
* @throws IOException
*/
protected void createHead(HSSFSheet sheet, String[] headTitle) throws IOException {
Row row = sheet.createRow(0);
Cell cell = null;
for (int i = 0; i < headTitle.length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(headTitle[i]);
cell.setCellStyle(getHeadCellStyle());
}
}
/**
* 默认的excel头样式
* @return
*/
protected HSSFCellStyle getHeadCellStyle() {
defaultHeadCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中
defaultHeadFont.setFontHeightInPoints((short) 13);// 设置字体样式
defaultHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
defaultHeadCellStyle.setFont(defaultHeadFont);
return defaultHeadCellStyle;
}
/**
* 获取默认的excel体样式
* @return
*/
protected HSSFCellStyle getBodyCellStyle() {
defaultBodyCellStye.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中
defaultBodyFont.setFontHeightInPoints((short) 12);// 设置字体样式
defaultBodyCellStye.setFont(defaultBodyFont);
return defaultBodyCellStye;
}
/**
* 导出excel体内容
* @param sheet
* @param list
*/
public abstract void createBody(HSSFSheet sheet, List list);
}
2,ExportUserListExcel类
package com.poi.excel;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
/**
* 导出excel数据
* @author Administrator
*
*/
public class ExportUserListExcel extends ExcelReportHandler{
@Override
public void createBody(HSSFSheet sheet, List list) {
Row row = null;
Cell cell = null;
Integer rowIndex = 0;
for (int i = 0; i < list.size(); i++) {
User user = (User)list.get(i);
rowIndex ++;
row = sheet.createRow(rowIndex); //创建行
cell = row.createCell((short) 0); //创建单元格
cell.setCellValue(i+1);
cell.setCellStyle(getBodyCellStyle());
cell = row.createCell((short) 1);
cell.setCellValue(user.getName());
cell.setCellStyle(getBodyCellStyle());
cell = row.createCell((short) 2);
cell.setCellValue(user.getEmail());
cell.setCellStyle(getBodyCellStyle());
cell = row.createCell((short) 3);
cell.setCellValue(user.getPhone());
cell.setCellStyle(getBodyCellStyle());
cell = row.createCell((short) 4);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(sdf.format(new Date()));
cell.setCellStyle(getBodyCellStyle());
}
}
}
3,ExcelControler类
package com.poi.excel;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class ExcelControler
*/
@WebServlet("/ExcelControler")
public class ExcelControler extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public ExcelControler() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html; charset=UTF-8");
doPost(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
List<User> list = new ArrayList<User>();
User user = null;
for(int i=0;i<100;i++){
user = new User();
user.setId(i);
user.setAddress("中国广东深圳宝安区");
user.setName("护腕"+i);
user.setEmail("sldhflksj@dd.com.cn");
user.setPhone("125859067906798"+i);
user.setSex((i%2==0? "男" : "女"));
list.add(user);
}
ExportUserListExcel execl = new ExportUserListExcel();
execl.exportExcel(response,new String[] {"序号","名称","邮箱","电话","时间"},"user list",list);
} catch (Exception e) {
response.getWriter().print("导出excel有误!");
}
}
}
第三个类为servlet,导入项目之后直接调用:http://localhost:8080/demo/ExcelControler
请自行下载POI jar包 本项目中用的是: poi-3.9.jar