用户信息批量导出至EXCEL
来源：互联网 发布：软件教育 编辑：程序博客网 时间：2024/05/22 17:19
用户信息批量导出至EXCEL
说明：批量导出信息就是将页面查询到的结果，写出到EXCEL文件中。
操作步骤：
一、预备工作
0、引入包
XLS文件解析：poi-3.7-20101029.jar
1、建表users
create table users(id int primary key auto_increment,name varchar(20) not null,password varchar(32) not null,sex varchar(2) not null,birthday date not null,hobby varchar(50) not null,telephone varchar(15) not null,address varchar(50) not null,type int not null -- 1 admin 2 common user);插入测试数据：
insert into users values(1,'admin','202CB962AC59075B964B07152D234B70','男','1980-1-1','体育,旅游','13953311099','山东淄博',1);insert into users values(2,'abc','202CB962AC59075B964B07152D234B70','女','1990-1-1','体育,音乐','1895330000','山东济南',2);…………效果如下： 
2、建立相应的JavaBean:
package cn.sdut.po;public class User {private int id;private String name;private String password;private String sex;private String birthday;private String hobby;private String telephone;private String address;private int type;public User() {super();}public User(int id, String name, String password, String sex,String birthday, String hobby, String telephone, String address,int type) {super();this.id = id;this.name = name;this.password = password;this.sex = sex;this.birthday = birthday;this.hobby = hobby;this.telephone = telephone;this.address = address;this.type = type;}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 getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getBirthday() {return birthday;}public void setBirthday(String birthday) {this.birthday = birthday;}public String getHobby() {return hobby;}public void setHobby(String hobby) {this.hobby = hobby;}public String getTelephone() {return telephone;}public void setTelephone(String telephone) {this.telephone = telephone;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public int getType() {return type;}public void setType(int type) {this.type = type;}@Overridepublic String toString() {return "User [id=" + id + ", name=" + name + ", password=" + password+ ", sex=" + sex + ", birthday=" + birthday + ", hobby="+ hobby + ", telephone=" + telephone + ", address=" + address+ ", type=" + type + "]";}}二、查询用户信息并写出至EXCEL文件中
1、准备前端页面——userMain.jsp，内容如下。
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%><%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>My JSP 'userMain.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"><meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">--><script src="js/calendar.js"></script><script>function selectUser(){form1.action="<%=path%>/UserServlet?method=bycond";form1.submit();}function exportexcel(){form1.action="<%=path%>/UserServlet?method=exportExcel";form1.submit();}</script></head><body><center><h1>用 户 管 理</h1></center><br><form action="" method="post" name="form1"><p>查询条件： <input type="text" name="condition" id="condition" value="${param.condition}"> 出生日期： <input name="datefrom" type="text" id="datefrom" size="10" value="${param.datefrom }"onclick="new Calendar().show(this)"> ~ <input name="dateto"type="text" id="dateto" size="10"onclick="new Calendar().show(this)" value="${param.dateto }"> <input type="button" name="select"id="select" value=" 查 询 " onclick="selectUser()"></p><table width="899" border="1"><tr><td width="67" align="center">选择</td><td width="68" align="center">序号</td><td width="82" align="center">姓名</td><td width="70" align="center">性别</td><td width="99" align="center">出生日期</td><td width="88" align="center">爱好</td><td width="108" align="center">电话</td><td width="108" align="center">地址</td><td width="200" align="center">操作</td></tr><c:forEach items="${userList}" var="user" varStatus="status"><tr><td align="center"><input type="checkbox" name="id" id="id"value="${user.id}"></td><td align="center">${status.count}</td><td align="center">${user.name }</td><td align="center">${user.sex }</td><td align="center">${user.birthday }</td><td>${user.hobby }</td><td>${user.telephone }</td><td>${user.address}</td><td> <a href="<%=path%>/UserServlet?method=del&id=${user.id}">删除</a> 修改 密码复位</td></tr></c:forEach><tr> <td height="47" colspan="9" align="center"><input type="submit" name="addUser" id="addUser" value=" 增加用户 "> <input type="submit" name="batchDelete" id="batchDelete" value=" 批量删除 "> <input name="batchResetPassword" type="submit" id="batchResetPassword"value="批量密码复位"> <input name="exportExcel" type="submit" id="exportExcel" value="导出文件"onclick="exportexcel()"></td></tr></table></form><tr><td height="47" colspan="9" align="left"><form name="form2" method="post"action="<%=path%>/UserServlet?method=uploadExcel"enctype="multipart/form-data"><p><h3> 导入用户</h3><p> 选择导入文件： <input type="file" name="file" id="file"> <input type="submit" name="ok" id="ok" value="确定"> <a href="<%=path%>/UserServlet?method=downloadExcel&filename=user.xls"><img src="<%=path%>/images/excel.png" alt="" width="30" height="30"> 下载EXCEL样例</a> </p><p> </p></form><p> </p><p><br><br></p></td></tr><p> </p>${msg }</body></html>效果为：
其中：
（1）页面中的关于查询条件设置的相关代码：
<p>查询条件：<input type="text" name="condition" id="condition" value="${param.condition}"> 出生日期：<input name="datefrom" type="text" id="datefrom" size="10" value="${param.datefrom }" onclick="new Calendar().show(this)"> ~<input name="dateto" type="text" id="dateto" size="10" onclick="new Calendar().show(this)" value="${param.dateto }"> <input type="button" name="select"id="select" value=" 查 询 " onclick="selectUser()"></p>其中的
（2）关于导出按钮及其单击响应函数：
<input name="exportExcel" type="submit" id="exportExcel" value="导出文件"onclick="exportexcel()"></td>function exportexcel(){form1.action="<%=path%>/UserServlet?method=exportExcel";form1.submit();}2. BaseDao中查询数据的方法
//数据库查询public List query(String sql,Class clazz,Object... param){List list=null;Connection conn=getConn(); //得到连接QueryRunner runner=new QueryRunner(); //得到运行对象try {list=runner.query(conn,sql,new BeanListHandler(clazz),param); //进行数据库操作} catch (SQLException e) {e.printStackTrace();}finally{DbUtils.closeQuietly(conn); //关闭数据库连接}return list;}3、建立UserDao，继承BaseDao类，定义带条件的查询数据的方法
//查询符合条件的用户（从姓名、性别、等非空字段模糊查询数据）public List<User> queryUserByCondition(String condition){String sql="select * from users where 1=1 ";if(condition!=null&&!"".equals(condition)){sql+=condition;}return query(sql, User.class, null);}4、处理导出任务的Servlet
建立一个UserServlet，映射路径为: /UserServlet
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {request.setCharacterEncoding("utf-8");response.setCharacterEncoding("utf-8");String method = request.getParameter("method");// 得到客户端要进行何种操作switch (method) {case "bycond":queryUserByCondition(request, response);break;case "del":delUser(request, response);break;case "downloadExcel":downloadExcel(request, response);break;case "uploadExcel":uploadExcel(request, response);break;case "exportExcel":exportExcel(request, response);break;}//主要步骤：//1、查询符合条件的数据存入一个链表中List<User>// 2、将数据写至EXCEL文件中——预设文件名user.xls,用户可修改private void exportExcel(HttpServletRequest request,HttpServletResponse response) throws IOException {System.out.println(request.getAttribute("userList"));//第一步，根据条件查询用户信息，准备写出到EXCEL中的数据UserDao userDao = new UserDao();StringBuffer condBuffer = new StringBuffer();// 拼接查询条件的缓冲区//获取客户端传来的查询信息——查询条件和起止日期String condition = request.getParameter("condition");String date1 = request.getParameter("datefrom");String date2 = request.getParameter("dateto");// 模糊查询条件做进一步处理，若有输入，就拼接进模糊查询值中if (condition != null) {condition = condition.trim();condBuffer.append("and concat(name,hobby,telephone,address) like '%").append(condition).append("%'");}// 若起始时间文本框内未输入任何数据，就置为nullif (date1 != null) {date1 = date1.trim();if (date1.length() == 0) {date1 = null;}}// 若结束时间文本框内未输入任何数据，就置为nullif (date2 != null) {date2 = date2.trim();if (date2.length() == 0) {date2 = null;}}// 拼接按时间进行查询的查询条件if (date1 != null && date2 == null) { //只有开始时间condBuffer.append(" and birthday>'" + date1 + "'");}if (date1 == null && date2 != null) { //只有结束时间condBuffer.append(" and birthday<'" + date2 + "'");}if (date1 != null && date2 != null) //开始时间和结束时间都不为空{condBuffer.append(" and birthday between '" + date1 + "' and '"+ date2 + "'");}//按条件进行查询，得到用户列表List<User> userList =userDao.queryUserByCondition(condBuffer.toString());// 第二步，将数据写出到EXCEL文件中response.setContentType("application/vnd.ms-excel"); //文件类型response.setHeader("Content-disposition","attachment;filename=user.xls" ); //预设写出的文件名称HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("sheet1");HSSFRow row;HSSFCell cell;// 写出标题行，行号从0开始计算row = sheet.createRow(0);String[] titles = { "序号", "姓名", "性别", "出生日期","爱好", "联系方式", "地址", "类型" };for (int i = 0; i < titles.length; i++) { // 创建一个单元格，设置单元格的值cell = row.createCell(i);cell.setCellValue(titles[i]);}// 从第1行开始写数据for (int i = 0; i < userList.size(); i++) {// 创建表格的第i+1行row = sheet.createRow(i + 1);// 写该行的各列数据User user = userList.get(i);int j = 0;cell = row.createCell(j++);cell.setCellValue(i + 1); // 序号cell = row.createCell(j++);cell.setCellValue(user.getName()); // 姓名cell = row.createCell(j++);cell.setCellValue(user.getSex()); // 性别cell = row.createCell(j++);cell.setCellValue(user.getBirthday()); // 出生年月cell = row.createCell(j++);cell.setCellValue(user.getHobby()); // 爱好cell = row.createCell(j++);cell.setCellValue(user.getTelephone()); // 联系方式cell = row.createCell(j++);cell.setCellValue(user.getAddress()); // 地址cell = row.createCell(j++);cell.setCellValue(user.getType()); // 用户类型}// 写入输出结果OutputStream out = response.getOutputStream();wb.write(out);out.close();}5、执行：http://localhost:8080/Shoping/UserServlet?method=bycond
数据已经写出了，但格式不美观。
如果要设置其格式，可以参考网上资料。
荐读：HSSFWorkBooK用法 —Excel表的导出和设置
http://blog.csdn.net/abcjiecba/article/details/6620254
public ActionResult excelPrint() { HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件 HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet sheet.createFreezePane(1, 3);// 冻结 // 设置列宽 sheet.setColumnWidth(0, 1000); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 6500); sheet.setColumnWidth(4, 6500); sheet.setColumnWidth(5, 6500); sheet.setColumnWidth(6, 6500); sheet.setColumnWidth(7, 2500); // Sheet样式 HSSFCellStyle sheetStyle = workbook.createCellStyle(); // 背景色的设定 sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // 前景色的设定 sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 填充模式 sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS); // 设置列的样式 for (int i = 0; i <= 14; i++) { sheet.setDefaultColumnStyle((short) i, sheetStyle); } // 设置字体 HSSFFont headfont = workbook.createFont(); headfont.setFontName("黑体"); headfont.setFontHeightInPoints((short) 22);// 字体大小 headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 // 另一个样式 HSSFCellStyle headstyle = workbook.createCellStyle(); headstyle.setFont(headfont); headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 headstyle.setLocked(true); headstyle.setWrapText(true);// 自动换行 // 另一个字体样式 HSSFFont columnHeadFont = workbook.createFont(); columnHeadFont.setFontName("宋体"); columnHeadFont.setFontHeightInPoints((short) 10); columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 列头的样式 HSSFCellStyle columnHeadStyle = workbook.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色 columnHeadStyle.setBorderLeft((short) 1);// 边框的大小 columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色 columnHeadStyle.setBorderRight((short) 1);// 边框的大小 columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色 // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式） columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = workbook.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 10); // 普通单元格样式 HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中 style.setWrapText(true); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderLeft((short) 1); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderRight((short) 1); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色． style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色． // 另一个样式 HSSFCellStyle centerstyle = workbook.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色． centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色． try { // 创建第一行 HSSFRow row0 = sheet.createRow(0); // 设置行高 row0.setHeight((short) 900); // 创建第一列 HSSFCell cell0 = row0.createCell(0); cell0.setCellValue(new HSSFRichTextString("中非发展基金投资项目调度会工作落实情况对照表")); cell0.setCellStyle(headstyle); /** * 合并单元格 * 第一个参数：第一个单元格的行数（从0开始） * 第二个参数：第二个单元格的行数（从0开始） * 第三个参数：第一个单元格的列数（从0开始） * 第四个参数：第二个单元格的列数（从0开始） */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7); sheet.addMergedRegion(range); // 创建第二行 HSSFRow row1 = sheet.createRow(1); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue(new HSSFRichTextString("本次会议时间：2009年8月31日 前次会议时间：2009年8月24日")); cell1.setCellStyle(centerstyle); // 合并单元格 range = new CellRangeAddress(1, 2, 0, 7); sheet.addMergedRegion(range); // 第三行 HSSFRow row2 = sheet.createRow(3); row2.setHeight((short) 750); HSSFCell cell = row2.createCell(0); cell.setCellValue(new HSSFRichTextString("责任者")); cell.setCellStyle(columnHeadStyle); cell = row2.createCell(1); cell.setCellValue(new HSSFRichTextString("成熟度排序")); cell.setCellStyle(columnHeadStyle); cell = row2.createCell(2); cell.setCellValue(new HSSFRichTextString("事项")); cell.setCellStyle(columnHeadStyle); cell = row2.createCell(3); cell.setCellValue(new HSSFRichTextString("前次会议要求/n/新项目的项目概要")); cell.setCellStyle(columnHeadStyle); cell = row2.createCell(4); cell.setCellValue(new HSSFRichTextString("上周工作进展")); cell.setCellStyle(columnHeadStyle); cell = row2.createCell(5); cell.setCellValue(new HSSFRichTextString("本周工作计划")); cell.setCellStyle(columnHeadStyle); cell = row2.createCell(6); cell.setCellValue(new HSSFRichTextString("问题和建议")); cell.setCellStyle(columnHeadStyle); cell = row2.createCell(7); cell.setCellValue(new HSSFRichTextString("备 注")); cell.setCellStyle(columnHeadStyle); // 访问数据库，得到数据集 List<DeitelVO> deitelVOList = getEntityManager().queryDeitelVOList(); int m = 4; int k = 4; for (int i = 0; i < deitelVOList.size(); i++) { DeitelVO vo = deitelVOList.get(i); String dname = vo.getDname(); List<Workinfo> workList = vo.getWorkInfoList(); HSSFRow row = sheet.createRow(m); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(dname)); cell.setCellStyle(centerstyle); // 合并单元格 range = new CellRangeAddress(m, m + workList.size() - 1, 0, 0); sheet.addMergedRegion(range); m = m + workList.size(); for (int j = 0; j < workList.size(); j++) { Workinfo w = workList.get(j); // 遍历数据集创建Excel的行 row = sheet.getRow(k + j); if (null == row) { row = sheet.createRow(k + j); } cell = row.createCell(1); cell.setCellValue(w.getWnumber()); cell.setCellStyle(centerstyle); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(w.getWitem())); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(new HSSFRichTextString(w.getWmeting())); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(new HSSFRichTextString(w.getWbweek())); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(new HSSFRichTextString(w.getWtweek())); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(new HSSFRichTextString(w.getWproblem())); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue(new HSSFRichTextString(w.getWremark())); cell.setCellStyle(style); } k = k + workList.size(); } // 列尾 int footRownumber = sheet.getLastRowNum(); HSSFRow footRow = sheet.createRow(footRownumber + 1); HSSFCell footRowcell = footRow.createCell(0); footRowcell.setCellValue(new HSSFRichTextString(" 审 定：XXX 审 核：XXX 汇 总：XX")); footRowcell.setCellStyle(centerstyle); range = new CellRangeAddress(footRownumber + 1, footRownumber + 1, 0, 7); sheet.addMergedRegion(range); HttpServletResponse response = getResponse(); HttpServletRequest request = getRequest(); String filename = "未命名.xls";//设置下载时客户端Excel的名称 // 请见：http://zmx.javaeye.com/blog/622529 filename = Util.encodeFilename(filename, request); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { e.printStackTrace(); } return null; } 0 0
- 用户信息批量导出至EXCEL
- C#通过Excel导出用户信息
- POI批量导出Excel JAVA
- EXCEL 批量导出图片
- Excel批量导入(导出同理)
- ibatis批量查询（excel批量导出）
- 批量导出大数据量到EXCEL
- java poi 实现Excel的批量导出
- asp.net excel 选择文件夹 批量导出
- 数据库数据批量导出到excel
- 数据批量导出为excel格式文件
- jxl操作excel进行批量导入 、导出
- excel数据批量导出为sql脚本
- Swing批量重命名并导出excel
- 报表开发之批量导入导出Excel
- 报表开发之批量导入导出Excel
- 以后自己用的Excel批量导出
- Excel批量导入导出功能开发
- 手工kill掉VNC进程的故障处理
- 跪求大神指导错误点。
- PAT 1017. Queueing at Bank
- Java加载Class文件的原理机制
- 剑指offer经典编程（十一）
- 用户信息批量导出至EXCEL
- pat 1108. Finding Average (20)
- [NSBundle mainBundle] infoDictionary
- android studio新建项目后出现bug finished with non-zero exit value 1
- 面试中经常被问到的单例类
- dom4j解析xml
- __weak & __unsafe_unretained的用法以及区别
- hihoCoder 1142 三分求极值
- HTML之认识标签(二)
