目的：访问url（http://localhost:8080/POIOutputExcel/outputexcel.do）实现excel导出，效果图如下：
文件目录（配置文件就不多说了，跟前面的随笔一样一样的）
User.java
public class User {
private Integer id;
private String name;
private Double cash;
//此处省略getter/setter方法
}
Datalist.java
public class Datalist {
//这里不走数据库，直接模拟数据库查询结果
public List<User> getUsers(int n){
List<User> list = new ArrayList<User>();
for (int i = 0; i < n; i++) {
User user = new User();
user.setId(i);
user.setName("xiaoming"+i);
user.setCash(1000.0+i);
list.add(user);
}
return list;
}
}
BeanToMap.java
public class BeanToMap<T> {
public Map<String,Object> getMap(T entity){
Field[] fields = entity.getClass().getDeclaredFields();
Map<String,Object> map = new HashMap<String,Object>();
for (int i = 0; i < fields.length; i++) {
String methodName = getMethodName(fields[i].getName());
try {
Method method = entity.getClass().getMethod(methodName);
map.put(fields[i].getName(),method.invoke(entity));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return map;
}
public String getMethodName(String fieldName){
byte[] buffer = fieldName.getBytes();
buffer[0] = (byte)(buffer[0]-32);
String name = new String(buffer);
return "get"+name;
}
}
PoiExcelExport.java
public class PoiExcelExport<T> {
// excle导出名称
private String fileName;
// excel 表头
private String[] heads;
// excel 列
private String[] cols;
// 设置数值型的列 从0开始计数
private int[] numerics;
//list集合
private List<T> list;
// 输出流
private OutputStream out;
// 构造函数
public PoiExcelExport(String fileName, String[] heads, String[] cols, List<T> list, OutputStream out) {
this.fileName = fileName;
this.heads = heads;
this.cols = cols;
this.list = list;
this.out = out;
}
// 构造函数 带数字类型
public PoiExcelExport(String fileName, String[] heads, String[] cols, List<T> list, int[] numerics, OutputStream out) {
this.fileName = fileName;
this.heads = heads;
this.cols = cols;
this.list = list;
this.numerics = numerics;
this.out = out;
}
public void exportExcel() {
HSSFWorkbook hssfworkbook = new HSSFWorkbook(); // 创建一个excel对象
for (int i = 0; i <= (list.size() / 65535); i++) {
HSSFSheet hssfsheet = hssfworkbook.createSheet(); // 工作表
// 工作表名称
hssfworkbook.setSheetName(i, fileName.replace(".xls", "") + "(第" + (i + 1) + "页)");
int beginRows = 65535 * i;
int endRows = (list.size() > 65535 * (i + 1)) ? 65535 * (i + 1) - 1 : list.size() - 1;
HSSFRow hssfrowHead = hssfsheet.createRow(0);
// 输出excel 表头
if (heads != null && heads.length > 0) {
for (int h = 0; h < heads.length; h++) {
HSSFCell hssfcell = hssfrowHead.createCell(h,Cell.CELL_TYPE_STRING);
hssfcell.setCellValue(heads[h]);
}
}
// 要设置数值型 列表
HSSFCellStyle cellstyle = hssfworkbook.createCellStyle();
cellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("##0"));
// 是否是数值型
boolean isnum = false;
// 输出excel 数据
for (int curRow = beginRows; curRow <= endRows; curRow++) {
// 获取数据
BeanToMap<T> btm = new BeanToMap<T>();
Map<String,Object> hm = btm.getMap(list.get(curRow));
// 创建excel行 表头1行 导致数据行数 延后一行
HSSFRow hssfrow = hssfsheet.createRow(curRow % 65535 + 1);
// 读取数据值
for (int k = 0; k < cols.length; k++) {
HSSFCell hssfcell = hssfrow.createCell(k);
// hssfcell.setCellValue(hm.get(cols[k])==null?"":hm.get(cols[k]).toString());
isnum = false;
for (int z = 0; z < numerics.length; z++) {
if (numerics[z] == k) {
isnum = true;
break;
}
}
if (isnum) {
if (hm.get(cols[k]) == null || hm.get(cols[k]).equals("")) {
} else {
hssfcell.setCellStyle(cellstyle);
hssfcell.setCellValue(Double.parseDouble(
hm.get(cols[k]) == null ? "" : hm.get(cols[k]).toString().replace(",", "")));
}
} else {
hssfcell.setCellValue(hm.get(cols[k]) == null ? "" : hm.get(cols[k]).toString());
}
}
}
}
// excel生成完毕，写到输出流
try {
hssfworkbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
ServletUtil.java
public class ServletUtil {
private String fileName;
private HttpServletRequest req;
private HttpServletResponse resp;
public OutputStream getOut(){
try {
return resp.getOutputStream();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
public ServletUtil(HttpServletResponse resp){
this.resp = resp;
}
public ServletUtil(String fileName,
HttpServletRequest req,
HttpServletResponse resp){
this.fileName = fileName;
this.req = req;
this.resp = resp;
}
public void poiExcelServlet(){
resp.setContentType("application/vnd.ms-excel");
String contentDisposition = "";
try {
if (req.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
contentDisposition = "attachment; filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1")
+ "\"";// firefox浏览器
} else {
contentDisposition = "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"";// IE浏览器
}
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
resp.setHeader("Content-Disposition", contentDisposition);
resp.setCharacterEncoding("UTF-8");
}
}
UserController.java
@Controller
public class UserController { @RequestMapping(value = "/outputexcel.do")
public void showImgCode(HttpServletRequest req,
HttpServletResponse resp) {
String fileName = "springMVC导出excel小练习"; ServletUtil su = new ServletUtil(fileName, req, resp);
su.poiExcelServlet(); String[] heads = {"id号","姓名","身价(元)"};
String[] cols = {"id","name","cash"};
int[] numerics = {2};
List<User> list = new Datalist().getUsers(70000);//获取1000条样例数据 ServletUtil suresp = new ServletUtil(resp);
PoiExcelExport<User> pee = new PoiExcelExport<User>(fileName, heads, cols, list, numerics, suresp.getOut());
pee.exportExcel();
} }