excel表格导出（根据查询）：

controller：

第一步

//定义一个导出数据的方法

private static Workbook exportUserImpl(List<UserInfoDto> list) throws Exception {

// 创建excel工作簿

Workbook wb = new HSSFWorkbook();

// 创建第一个sheet（页），并命名

Sheet sheet = wb.createSheet("用户信息");

// 创建第一行

Row row = sheet.createRow((short) 0);

// 设置头样式

CellStyle style = wb.createCellStyle();

style.setAlignment(CellStyle.ALIGN_LEFT);

style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

style.setWrapText(true);

Font font = wb.createFont();

font.setFontName("宋体");

style.setFont(font);

font.setFontHeightInPoints((short) 11);

DataFormat format = wb.createDataFormat();

style.setDataFormat(format.getFormat("@"));

//设置列名

int jj = 0;

Cell cell ;

cell = row.createCell(jj++);

cell.setCellValue("姓名");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

cell = row.createCell(jj++);

cell.setCellValue("工作单位");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

cell = row.createCell(jj++);

cell.setCellValue("职位");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

cell = row.createCell(jj++);

cell.setCellValue("手机号码");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

cell = row.createCell(jj++);

cell.setCellValue("用户类型");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

cell = row.createCell(jj++);

cell.setCellValue("注册时间");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

cell = row.createCell(jj++);

cell.setCellValue("到期时间");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

cell = row.createCell(jj++);

cell.setCellValue("状态");

sheet.setColumnWidth(jj, 24*256);

cell.setCellStyle(style);

//设置每行每列的值

Row dataRow;

UserInfoDto dto;

int cellIndex;

for (short i = 0; i < list.size(); i++) {

cellIndex = 0;

dataRow = sheet.createRow((short) i +1);

dto = list.get(i);

//姓名

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getUserName());

//工作单位

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getCompanyName());

//职位

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getPosition());

//手机号码

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getUserPhone());

//用户类型

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getRemark());

//注册时间

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getRegDate());

//到期时间

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getExpireDateStr());

//状态

cell = dataRow.createCell(cellIndex++);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellStyle(style);

cell.setCellValue(dto.getStatusDesc());

}

return wb;

}

第二步：

@RequestMapping(value = "/exportUser" )//用户信息导出excel表格

public void exportUser(HttpServletRequest request,HttpServletResponse response){

InputStream is = null;

ServletOutputStream out = null;

BufferedInputStream bis = null;

BufferedOutputStream bos = null;

// 生成EXCEL workbook对象并写入OS流

ByteArrayOutputStream os=new ByteArrayOutputStream();

try {

String userName = RiskUtils.toStrNull(request.getParameter("userName"));

String userPhone = RiskUtils.toStrNull(request.getParameter("userPhone"));

String userType = RiskUtils.toStrNull(request.getParameter("userType"));

int goPageNum = RiskUtils.toIntZero(request.getParameter("goPageNum"));

UserInfoDto dto = new UserInfoDto();

dto.setUserName(userName);

dto.setUserPhone(userPhone);

dto.setUserType(userType);

dto.setPageSize(pageSize);

dto.setStartIndex((goPageNum-1)*pageSize);

ClassPathXmlApplicationContext context = ContextUtils.getInstance().getUserCenterContext();

UserCenterServ service = (UserCenterServ) context.getBean("userCenterServ");

List<UserInfoDto> listUser = service.exportUser(dto);

exportUserImpl(listUser).write(os);

byte[] content = os.toByteArray();

is = new ByteArrayInputStream(content);

// 设置response参数，可以打开下载页面

SimpleDateFormat sdf=new SimpleDateFormat( "yyyyMMddHHmmss");

String fileName="用户信息列表"+sdf.format(new Date());

response.reset();

response.setContentType("application/vnd.ms-excel;charset=utf-8");

response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));

out = response.getOutputStream();

bis = new BufferedInputStream(is);

bos = new BufferedOutputStream(out);

byte[] buff = new byte[2048];

int bytesRead;

// Simple read/write loop.

while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {

bos.write(buff, 0, bytesRead);

}

} catch (Exception e) {

log.error(e.getMessage(),e);

} finally {

if (bis != null)

try {

bis.close();

} catch (IOException e) {

log.error(e.getMessage(),e);

}

if (bos != null)

try {

bos.flush();

bos.close();

} catch (IOException e) {

log.error(e.getMessage(),e);

}

}

}

servimpl：

//用户信息导出

@Override

public List<UserInfoDto> exportUser(UserInfoDto dto) throws Exception {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

List<UserInfoDto> list = userCenterMapper.exportUser(dto);//数据查询

if(null != list && list.size() >=0){

Date date;

for(UserInfoDto o : list){

date = o.getExpireDate();

if(null != date){//判断日期格式是否为空

o.setExpireDateStr(sdf.format(date));//设置日期格式

}

date = o.getCreateDate();

if(null != date){

o.setRegDate(sdf1.format(date));

}

date = new Date();

if("00".equals(o.getStatus())){//判断用户状态

o.setStatusDesc("停用");

} else if(null != o.getExpireDate() && o.getExpireDate().getTime() < date.getTime()){

o.setStatusDesc("过期");

} else {

o.setStatusDesc("启用");

}

}

}

return list;

}

sql语句（mapper.xml）

<select id="exportUser" resultMap="UserInfoResultMap" parameterType="com.arec.risk.riskapi.dto.exclusive.UserInfoDto">

SELECT i.remark,u.*

FROM user_info u ,invite_code_info i

WHERE i.USER_TYPE = u.USER_TYPE

<if test="userName != null ">

and user_name = #{userName}

</if>

<if test="userPhone != null">

and user_phone = #{userPhone}

</if>

<if test="userType != null and userType != '09'">

and u.user_type = #{userType}

</if>

<if test="userType != null and userType == '09'">

<![CDATA[ and u.user_type <= '09' ]]>

</if>

</select>

mapper.java

List<UserInfoDto> exportUser(UserInfoDto dto);//用户信息导出excel表格

jsp：

<form action="<%=basePath%>/administrator/exportUser" method="post">

<div class="module-title header-line">

<label class="header-label">姓名：</label><input id="userName" name="userName" type="text" placeholder="姓名"/>

<laber class="header-label">手机号：</laber><input id="userPhone" name="userPhone" type="text" placeholder="手机号" />

<laber class="header-label">用户类型：</laber><select id="userType" name="userType" ></select>

<input type="button" class="header-btn" id="queryBtn" value="查询">

</div>

<div class="module-title">

<!-- <input type="button" class="header-btn" value="添加"> -->

<input type="button" class="header-btn" id="changeStatusBtn" value="停用/启用">

<input type="button" class="header-btn" id="modifyUser" value="修改">

<input type="button" class="header-btn" id="searchUser" value="查看">

<input type="submit" class="header-btn" id="excelUser" value="导出">

<!-- <input type="button" class="header-btn" value="重置密码"> -->

</div>

</form>