导出：将当前页面表格里值传到excel表格中。
一、页面js
//下载excel
$("#download").click(
function() {
var param = $("#form_enterpriseUser").serialize(); //序列化整个表单的值，在ajax里用
var url = "${pageContext.request.contextPath}/enterpriseUser/EnterpriseUserDownLoad?"+ param;
window.location.href = url;
})
二、Controller类：
/**
* 下载excel表格
* @param page
* @param size
* @param userAccount
* @param userName
* @param groupId
* @param instructionOpt
* @param powercutOpt
* @param smsAlarmOpt
* @param smsLoginOpt
* @param roleId
* @param corpId
*/
@RequestMapping(value = "/EnterpriseUserDownLoad" , method = RequestMethod.GET)
public void EnterpriseUserDownLoad (
@RequestParam(required = false) String userAccount,
@RequestParam(required = false) String userName,
@RequestParam(required = false) String groupId,
@RequestParam(required = false) String instructionOpt,
@RequestParam(required = false) String powercutOpt,
@RequestParam(required = false) String smsAlarmOpt,
@RequestParam(required = false) String smsLoginOpt,
@RequestParam(required = false) String roleId,
@RequestParam(required = false) String corpId,
HttpServletResponse response,
HttpServletRequest request) {
final List<CorpUserDTO> resp = corpUserService.queryCorpUsersExl( new CorpUserReq(corpId,userAccount,
userName,groupId,0,0,instructionOpt,powercutOpt,smsAlarmOpt,smsLoginOpt,roleId));
String headerAsString = "用户ID,用户账号,用户姓名,手机号码,角色,职位名称,无线设备设置权限,断油断电设置权限,短信报警权限,信息保密,企业名称,状态";
String propertyAsString = "id,userAccount,userName,telephone,roleValue,job,instructionOpt,powercutOpt,smsAlarmOpt,smsLoginOpt,corpName,statusValue";
ExportFile.exportReport(response, request, resp, headerAsString, propertyAsString, "xls", "to export excel",
null, "下载excel");
}
三、Service类：
List<CorpUserDTO> queryCorpUsersExl(CorpUserReq corpUserReq); //CorpUserReq对DTO实体进行封装
四、实现类：
@Override
public List<CorpUserDTO> queryCorpUsersExl(CorpUserReq corpUserReq) {
try {
Preconditions.checkNotNull(corpUserReq);
final RowBounds rowBounds = new RowBounds();
final List<CorpUserDTO> corpUserList = corpUserDAO.queryCorpUsers(corpUserReq, rowBounds);
return corpUserList;
} catch (final Exception e) {
logger.error("queryCorpUsers:" + e);
throw new ServiceException("查询用户失败");
}
}
五、DAO层
List<CorpUserDTO> queryCorpUsers(CorpUserReq corpUserReq, RowBounds rowBounds);
六、SQL
<select id="queryCorpUsers" resultMap="CorpUserDTOMap"
parameterType="com.rjs.gps.api.dto.corp.user.CorpUserReq">
select cop.* from (
select
<include refid="CorpUserDTOMap" />
,if(cu.status = '0','正常','已停用') as statusValue, gc.corp_name,
(select GROUP_CONCAT(cr.role_name) from gps_corp_user_role_rel_${corpId} cur
left join gps_corp_role_${corpId} cr on cur.corp_id =cr.corp_id and cur.role_id =cr.id
where cu.corp_id = cur.corp_id and
cu.id
= cur.user_id
) as role_value,cg.group_name,
(select if(count(1)<![CDATA[>]]>0 ,1,0) from gps_corp_user_role_rel_${corpId} cur
left join gps_corp_role_${corpId} cr on cur.corp_id =
cr.corp_id and cur.role_id =
cr.id
where cu.corp_id = cur.corp_id and
cu.id
= cur.user_id and cr.is_admin=1
) as is_admin
from gps_corp_user_${corpId} cu
left join gps_corp_group_${corpId} cg on
cu.corp_id = cg.corp_id and cu.group_id =
cg.id
left join gps_corp gc
on gc.id = cu.corp_id
where cu.corp_id =
#{corpId,jdbcType=VARCHAR} and cu.status <![CDATA[<>]]> 2 and cu.channel= 0
<if test="userAccount != null and userAccount != ''">
and cu.user_account like
concat('%',#{userAccount,jdbcType=VARCHAR},'%')
</if>
<if test="userName != null and userName != ''">
and cu.user_name like
concat('%',#{userName,jdbcType=VARCHAR},'%')
</if>
<if test="groupId != null and groupId != ''">
and cu.group_id = #{groupId,jdbcType=VARCHAR}
</if>
<if test="instructionOpt != null and instructionOpt != ''">
and cu.instruction_opt = #{instructionOpt,jdbcType=VARCHAR}
</if>
<if test="powercutOpt != null and powercutOpt != ''">
and cu.powercut_opt = #{powercutOpt,jdbcType=VARCHAR}
</if>
<if test="smsAlarmOpt != null and smsAlarmOpt != ''">
and cu.sms_alarm_opt = #{smsAlarmOpt,jdbcType=VARCHAR}
</if>
<if test="smsLoginOpt != null and smsLoginOpt != ''">
and cu.sms_login_opt = #{smsLoginOpt,jdbcType=VARCHAR}
</if>
) cop
where 1=1
<if test="roleId !=null and roleId !=''">
and cop.role_value=(select role_name from gps_corp_role_${corpId} table2 where table2.id=#{roleId,jdbcType=VARCHAR})
</if>
order by cop.create_time desc
</select>
<include refid="CorpUserDTOMap" /> 字段写在下方：
<sql id="CorpUserDTOMap">
cu.id, cu.corp_id, cu.dept_id, cu.group_id,
cu.user_account, cu.user_name,
cu.password,cu.salt,cu.is_first_login,
(select GROUP_CONCAT(cucr.car_id) from gps_corp_user_car_ref_${corpId} cucr where cucr.user_id = cu.id) as user_level,
cu.telephone,cu.job,
cu.equip_auth,cu.status,cu.last_mod_time,cu.create_time,cu.wifi_auth_pwd,cu.error_day,cu.error_times,cu.last_alert_time,cu.is_alert_timing,
cu.coordinate,cu.instruction_opt,cu.powercut_opt,cu.sms_alarm_opt,cu.sms_login_opt,cu.channel,cu.stop_normal_val,cu.offline_normal_val,cu.secret_opt
</sql>
CorpUserReq 封装层：
public class CorpUserReq extends BaseReq {
private static final long serialVersionUID = -5378994308037645116L;
private String corpId;
private String userId;
private String userAccount;
private String userName;
private String groupId;
private String instructionOpt;
private String powercutOpt;
private String smsAlarmOpt;
private String smsLoginOpt;
private String roleId;
public CorpUserReq(String corpId, String userId, int page, int size) {
super(page, size);
this.corpId = corpId;
this.userId = userId;
}
public CorpUserReq(String corpId, String userAccount, String userName, String groupId, int page, int size) {
super(page, size);
this.corpId = corpId;
this.userAccount = userAccount;
this.userName = userName;
this.groupId = groupId;
}
public CorpUserReq(String corpId, String userAccount, String userName, String groupId, int page, int size,
String instructionOpt, String powercutOpt, String smsAlarmOpt, String smsLoginOpt, String roleId) {
super(page, size);
this.corpId = corpId;
this.userAccount = userAccount;
this.userName = userName;
this.groupId = groupId;
this.instructionOpt = instructionOpt;
this.powercutOpt = powercutOpt;
this.smsAlarmOpt = smsAlarmOpt;
this.smsLoginOpt = smsLoginOpt;
this.roleId = roleId;
}
public String getCorpId() {
return corpId;
}
public String getGroupId() {
return groupId;
}
public String getInstructionOpt() {
return instructionOpt;
}
public String getPowercutOpt() {
return powercutOpt;
}
public String getRoleId() {
return roleId;
}
public String getSmsAlarmOpt() {
return smsAlarmOpt;
}
public String getSmsLoginOpt() {
return smsLoginOpt;
}
public String getUserAccount() {
return userAccount;
}
public String getUserId() {
return userId;
}
public String getUserName() {
return userName;
}
public void setCorpId(String corpId) {
this.corpId = corpId;
}
public void setGroupId(String groupId) {
this.groupId = groupId;
}
public void setInstructionOpt(String instructionOpt) {
this.instructionOpt = instructionOpt;
}
public void setPowercutOpt(String powercutOpt) {
this.powercutOpt = powercutOpt;
}
public void setRoleId(String roleId) {
this.roleId = roleId;
}
public void setSmsAlarmOpt(String smsAlarmOpt) {
this.smsAlarmOpt = smsAlarmOpt;
}
public void setSmsLoginOpt(String smsLoginOpt) {
this.smsLoginOpt = smsLoginOpt;
}
public void setUserAccount(String userAccount) {
this.userAccount = userAccount;
}
public void setUserId(String userId) {
this.userId = userId;
}
public void setUserName(String userName) {
this.userName = userName;
}
}
ExportFile类：
package com.rjs.cloud.web.utils; import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map; import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ExportFile { public static void exportReport(HttpServletResponse response, List<? extends Object> list, String headerAsString,
String propertyAsString, String fileType, String titleName, Map<String, ? extends Object> mapResults) {
String excel = "xls";
if (excel.trim().equalsIgnoreCase(fileType)) {
response.setContentType("application/vnd.ms-xls");
try {
response.setHeader("Content-disposition",
"attachment; filename=" + new String(titleName.getBytes("gb2312"), "ISO8859-1") + ".xls");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
} response.setHeader("Pragma", ""); OutputStream out = null;
try {
out = response.getOutputStream();
exportListToFile(out, list, headerAsString, propertyAsString, fileType, titleName, mapResults); out.close();
} catch (Exception e) {
e.printStackTrace();
}
} public static void exportReport(HttpServletResponse response, HttpServletRequest request,
List<? extends Object> list, String headerAsString, String propertyAsString, String fileType,
String titleName, Map<String, ? extends Object> mapResults, String fileName) {
String excel = "xls";
if (excel.trim().equalsIgnoreCase(fileType)) {
response.setContentType("application/vnd.ms-xls");
try {
String agent = request.getHeader("User-Agent");
boolean isMSIE = (agent != null && agent.indexOf("MSIE") != -1);
if (isMSIE) {// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF-8");
} else {// 其它浏览器
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
}
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} } response.setHeader("Pragma", ""); OutputStream out = null;
try {
out = response.getOutputStream();
exportListToFile(out, list, headerAsString, propertyAsString, fileType, titleName, mapResults); out.close();
} catch (Exception e) {
e.printStackTrace();
}
} public static void exportListToFile(OutputStream out, List<? extends Object> list, String headerAsString,
String propertyAsString, String fileType, String titleName, Map<String, ? extends Object> mapResults) {
String[] header = headerAsString.split(",");
String[] property = propertyAsString.split(",");
ExportColumnInfo[] columnInfo = new ExportColumnInfo[header.length + 1];
String sNo = "S/No";
columnInfo[0] = new ExportColumnInfo(sNo, "", 10);
for (int i = 1; i < header.length + 1; i++) {
columnInfo[i] = new ExportColumnInfo(header[i - 1], property[i - 1], 10);
}
String excel = "XLS";
if (list != null && list.size() > 0) {
try {
doExport(out, titleName, columnInfo, list);
} catch (Exception e) {
e.printStackTrace();
}
} } public static void doExport(OutputStream out, String title, ExportColumnInfo[] columnInfo,
List<? extends Object> values) throws Exception {
int rows = values.size();
int columns = columnInfo.length;
String[][] cells = new String[rows][columns];
for (int i = 0; i < rows; i++) {
Object curr = values.get(i);
for (int j = 0; j < columns; j++) {
if (j == 0) {
cells[i][0] = String.valueOf(i + 1);
} else {
Object val = PropertyUtils.getProperty(curr, columnInfo[j].getProperty());
cells[i][j] = val == null ? "" : "" + val;
}
}
}
doExport(out, title, columnInfo, cells); } public static void doExport(OutputStream out, String title, ExportColumnInfo[] columnInfo, String[][] cellValues)
throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(title); // add head message
int rowIndex = 0; // row1 filled in all title
HSSFRow row = sheet.createRow(rowIndex);
rowIndex++;
HSSFFont f_title = wb.createFont();
f_title.setFontHeightInPoints((short) 11);
f_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
f_title.setFontName("Arial"); HSSFCellStyle style = wb.createCellStyle();
style.setFont(f_title);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i < columnInfo.length; i++) {
sheet.setColumnWidth(Short.valueOf(String.valueOf(i)),
Short.valueOf(String.valueOf(columnInfo[i].getWidth() * 256)));
HSSFCell cell = row.createCell(Short.valueOf(String.valueOf(i)));
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(columnInfo[i].getLabel()));
}
HSSFCellStyle style2 = wb.createCellStyle(); for (int i = 0; i < cellValues.length; i++) {
HSSFRow row1 = sheet.createRow(rowIndex + i);
for (int j = 0; j < cellValues[i].length; j++) {
HSSFCell cell = row1.createCell(Short.valueOf(String.valueOf(j)));
style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cell.setCellStyle(style2);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(cellValues[i][j])); }
} wb.write(out);
out.close();
} }