java实现导出网页数据到excel
写东西，遇到需要导出所显示的表格内容到excel，研究了一阵子，完成。记录一下！
项目使用的是spring+springMVC+ibatis框架。
首先是在页面根据导出按钮的点击进入js控制代码，去控制层获取excel导出所需要的数据源，
页面代码如下：
jsp页面代码实现:
<input type="button" class="newBtn" onclick="exportTable()" value="导出Excel"> function exportTable(){ var userName = $.trim($("#fuserName").val()); var className = $.trim($("#fclassName").val());
var param = "userName="+userName+"&className="+className; window.open("<c:url value='/ceshi/getListForExcel.do?"+param+"'/>"); }
控制层代码实现：
@RequestMapping("/getListForExcel")
public String getgetListForExcel(HttpServletRequest request,HttpServletResponse response){
try {
//查询的条件......
String userName = RequestHandler.getString(request, "userName");
String className = RequestHandler.getString(request, "className");
Ceshi ceshi = new ceshi();
ceshi.setUserName(userName);
ceshi.setClassName(className);
int total = ceshiService.getceshiCount(ceshi);
List<Ceshi> ceshiList = null;
if(total>0){
//查出数据
ceshiList = ceshiService.getceshiList(ceshi);
}else{
ceshiList = new ArrayList<Ceshi>();
}
request.setAttribute("ceshiList", ceshiList);
//返回页面
return "ceshi/exportCeshi";
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
在上面的控制层代码里面得到了要导出来的所有的数据，通过return "ceshi/exportCeshi";返回这些数据到exportCeshi这个jsp页面，组装并生成excel表格。
exportCeshi页面代码：
<%@ page language="java" contentType="text/html;charset=gb2312" %> <%@ page language="java" import="com.ceshi.domain.Ceshi" %> <%@ page language="java" import="java.util.*, org.apache.poi.hssf.usermodel.HSSFWorkbook, org.apache.poi.hssf.usermodel.HSSFSheet, org.apache.poi.hssf.usermodel.HSSFRow, org.apache.poi.hssf.usermodel.HSSFCell" %> <%@ page language="java" import="org.apache.poi.hssf.usermodel.HSSFCellStyle" %> <%@ page language="java" import="org.apache.poi.hssf.util.*" %> <%@ page language="java" import="org.apache.poi.hssf.usermodel.*" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title></title> <!--清除浏览器中的缓存，它和其它几句合起来用，就可以使你再次进入曾经访问过的页面时， 浏览器必须从服务端下载最新的内容，达到刷新的效果。--> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> </head> <% response.setContentType( "APPLICATION/OCTET-STREAM" ); List<Ceshi> list = (List<Ceshi>)request.getAttribute("ceshiList"); String titleStr = "导出的excel的名称"; String title = new String(titleStr.getBytes("gb2312"),"iso-8859-1"); response.setHeader( "Content-Disposition" ,"attachment;filename=\"" + title + ".xls" + "\"" ); HSSFWorkbook wb = new HSSFWorkbook(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short)14); font.setBoldweight((short)600); HSSFSheet sheet = wb.createSheet("sheet1"); sheet.setColumnWidth((short)0,(short)(1500)); sheet.setColumnWidth((short)1,(short)(4500)); sheet.setColumnWidth((short)2,(short)(4500)); sheet.setColumnWidth((short)3,(short)(4500)); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle styleFont = wb.createCellStyle(); styleFont.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleFont.setFont(font); // 以下以写表头 // 表头为第一行 HSSFRow head = sheet.createRow((short)0); HSSFCell headCell = head.createCell((short)0); sheet.addMergedRegion(new Region(0,(short)0,0,(short)3));//合并单元格 HSSFRow row = sheet.createRow((short)1); HSSFCell cell1 = row.createCell((short)0); HSSFCell cell2 = row.createCell((short)1); HSSFCell cell3 = row.createCell((short)2); HSSFCell cell4 = row.createCell((short)3); headCell.setEncoding(HSSFCell.ENCODING_UTF_16); headCell.setCellType(HSSFCell.CELL_TYPE_STRING); headCell.setCellStyle(styleFont); cell1.setEncoding(HSSFCell.ENCODING_UTF_16); cell1.setCellType(HSSFCell.CELL_TYPE_STRING); cell1.setCellStyle(style); cell2.setEncoding(HSSFCell.ENCODING_UTF_16); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); cell2.setCellStyle(style); cell3.setEncoding(HSSFCell.ENCODING_UTF_16); cell3.setCellType(HSSFCell.CELL_TYPE_STRING); cell3.setCellStyle(style); cell4.setEncoding(HSSFCell.ENCODING_UTF_16); cell4.setCellType(HSSFCell.CELL_TYPE_STRING); cell4.setCellStyle(style); // 定义表头的内容 headCell.setCellValue(titleStr); cell1.setCellValue("序号"); cell2.setCellValue("姓名"); cell3.setCellValue("班级"); cell4.setCellValue("生日"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); for(int i=1; i<list.size()+1; i++){ Ceshi ceshi = list.get(i-1); // 定义数据从第二行开始 row = sheet.createRow((short) i + 1); cell1 = row.createCell((short)0); cell2 = row.createCell((short)1); cell3 = row.createCell((short)2); cell4 = row.createCell((short)3); headCell.setEncoding(HSSFCell.ENCODING_UTF_16); headCell.setCellType(HSSFCell.CELL_TYPE_STRING); headCell.setCellStyle(styleFont); cell1.setEncoding(HSSFCell.ENCODING_UTF_16); cell1.setCellType(HSSFCell.CELL_TYPE_STRING); cell1.setCellStyle(style); cell2.setEncoding(HSSFCell.ENCODING_UTF_16); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); cell2.setCellStyle(style); cell3.setEncoding(HSSFCell.ENCODING_UTF_16); cell3.setCellType(HSSFCell.CELL_TYPE_STRING); cell3.setCellStyle(style); cell4.setEncoding(HSSFCell.ENCODING_UTF_16); cell4.setCellType(HSSFCell.CELL_TYPE_STRING); cell4.setCellStyle(style); // 填充内容 cell1.setCellValue(i); if(ceshi.getUserName() != null){ cell2.setCellValue(ceshi.getUserName()); }else{ cell2.setCellValue(""); } if(ceshi.getClassName() != null){ cell3.setCellValue(ceshi.getClassName()); }else{ cell3.setCellValue(""); } if(ceshi.getBirth() != null){ cell4.setCellValue(sdf.format(ceshi.getBirth())); //转换数据库出来的date类型为简单日期格式 }else{ cell4.setCellValue(""); } } wb.write(response.getOutputStream()); response.getOutputStream().flush(); out.clear(); response.getOutputStream().close(); out.clear(); out = pageContext.pushBody(); %> </html>
如果还有什么疑问，可以评论指出来。描述的不对的地方，还请各位谅解，谢谢！
