1、前台
首先在列表页面会有一个button
<button type="button" class="btn btn-warning" id="export" οnclick="exportExcel();">excel导出</button>
前台页面
前台方法:
function exportExcel(){
var tempStart = $("#startTime").val();
var tempEnd = $("#endTime").val();
var paramStart;
var paramEnd;
if(tempStart==""){
paramStart = '';
}else{
paramStart = tempStart+" 00:00:00";
}
if(tempEnd==""){
paramEnd = "";
}else{
paramEnd = tempEnd+" 23:59:59";
}
var param = $.param({
vc_title:$("#title").val(),
vc_dictionary_id:$("#typeId").val(),
vc_mail_state:$("#dealState").val(),
vc_sysid:$("#sysId").val(),
startTime:paramStart,
endTime:paramEnd
});
window.location.href = bp() + '/mailCenter/exportMailStatistics.do?' + param;
}
为了防止中文乱码,js还可以通过表单这样写,不过表单的提交方式要为post
<form method="post" id="formx">
.....省略表单
</form>
function exportXls(){
$("#formx").attr("action",bp() + '/statistics/exportProjectStatistics.do');
$("#formx").attr("method","post");
$("#formx").submit();
}
2、后台
controller层
/**
* excel导出信件统计
* @param request
* @param response
* @param session
* @param modelMap
*/
@RequestMapping("/exportMailStatistics")
public void exportMailStatistics(HttpServletRequest request,HttpServletResponse response,HttpSession session,ModelMap modelMap){
Map<String,Object> paramMap = ParamsUtil.requestParamMap(request);
User user = (User)request.getSession().getAttribute("user");
paramMap.put("userId",user.getId());
DataGrid<MailInfoEntity> result = fdCorrelationService.queryHdMailInfoByParamForExport(paramMap);
String excelFilepath = System.getProperty("user.dir");
excelFilepath = excelFilepath+"\\";
// excel标题
String title = "信件统计表.xls";
toExcel(paramMap,result,new File(excelFilepath+title),title,request,response);
}
private void toExcel(Map<String, Object> paramMap,DataGrid<MailInfoEntity> result, File file, String title,HttpServletRequest request, HttpServletResponse response){
try {
List<MailInfoEntity> data = result.getData();
response.addHeader("content-type", "application/x-msdownload;");
String encodedfileName = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE")) {// IE
encodedfileName = java.net.URLEncoder.encode(title, "UTF-8");
} else if (null != agent && -1 != agent.indexOf("Mozilla")) {
encodedfileName = new String(title.getBytes("UTF-8"), "iso-8859-1");
} else {
encodedfileName = java.net.URLEncoder.encode(title, "UTF-8");
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedfileName + "\"");
// 创建工作簿
WritableWorkbook workbook = Workbook.createWorkbook(file);
// 创建sheet
WritableSheet sheet = workbook.createSheet("第1页", 0);
//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
WritableFont bold = new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.NO_BOLD);
//生成一个单元格样式控制对象
WritableCellFormat titleFormate = new WritableCellFormat(bold);
//单元格中的内容水平方向居中
titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
//单元格的内容垂直方向居中
titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//生成一个单元格样式控制对象
WritableCellFormat titleFormate2 = new WritableCellFormat(bold);
//单元格中的内容水平方向居右
titleFormate2.setAlignment(jxl.format.Alignment.RIGHT);
//单元格的内容垂直方向居中
titleFormate2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
sheet.addCell(new Label(0, 0, "信件项目统计表", titleFormate));
//sheet.mergeCells(int m,int n,int p,int q);作用是从(m,n)到(p,q)的单元格全部合并[我的理解:m列、n行和p列、q行合并]
sheet.mergeCells(0, 0, 12, 1);//它合并的是第1行第1列和第1行列第10列
sheet.addCell(new Label(0, 2, "序号", titleFormate));//第3行1列
sheet.addCell(new Label(1, 2, "办件编号", titleFormate));//第3行2列
sheet.addCell(new Label(2, 2, "办件标题", titleFormate));//第3行2列
sheet.addCell(new Label(3, 2, "类别", titleFormate));//第3行2列
sheet.addCell(new Label(4, 2, "姓名", titleFormate));//第3行2列
sheet.addCell(new Label(5, 2, "性别", titleFormate));//第3行2列
sheet.addCell(new Label(6, 2, "年龄", titleFormate));//第3行2列
sheet.addCell(new Label(7, 2, "手机号码", titleFormate));//第3行2列
sheet.addCell(new Label(8, 2, "E-mail", titleFormate));//第3行2列
sheet.addCell(new Label(9, 2, "信箱名称", titleFormate));//第3行2列
sheet.addCell(new Label(10, 2, "是否公开", titleFormate));//第3行2列
sheet.addCell(new Label(11, 2, "处理状态", titleFormate));//第3行2列
sheet.addCell(new Label(12, 2, "提交时间", titleFormate));//第3行2列
sheet.setColumnView(1, 30);
sheet.setColumnView(2, 30);
sheet.setColumnView(7, 30);
sheet.setColumnView(8, 30);
sheet.setColumnView(12, 30);
if(data != null && data.size()>0){
for(int i = 0; i < data.size(); i++){
//序号
String cell_0 = ParamsUtil.nullToEmpty((i+1));
// 办件编号
String cell_1 = ParamsUtil.nullToEmpty(data.get(i).getVc_mail_number());
//办件标题
String cell_2 = ParamsUtil.nullToEmpty(data.get(i).getVc_title());
//类别
String cell_3 = ParamsUtil.nullToEmpty(data.get(i).getVc_dictionary_id_name());
//用户名
String cell_4 = ParamsUtil.nullToEmpty(data.get(i).getVc_user_name());
//性别
String cell_5 = "";
if("m".equals(ParamsUtil.nullToEmpty(data.get(i).getVc_sex()))){
cell_5 = "男";
}else{
cell_5 = "女";
}
//年龄
String cell_6 = "";
if("".equals(ParamsUtil.nullToEmpty(data.get(i).getI_age()))){
cell_6 = "";
}if("0".equals(ParamsUtil.nullToEmpty(data.get(i).getI_age()))){
cell_6 = "";
}else{
cell_6 = ParamsUtil.nullToEmpty(data.get(i).getI_age());
}
//电话
String cell_7 = ParamsUtil.nullToEmpty(data.get(i).getVc_mobile_phone());
//邮件
String cell_8 = ParamsUtil.nullToEmpty(data.get(i).getVc_email());
//
String cell_9 = ParamsUtil.nullToEmpty(data.get(i).getVc_sysname());
String cell_10 = "";//是否公开
if("1".equals(data.get(i).getB_person_ispublic())){
cell_10 = "是";
}else{
cell_10 = "否";
}
String cell_11 = "";
String mail_state = data.get(i).getVc_mail_state();
if("10".equals(mail_state)){
cell_11 = "待受理";
}else if("20".equals(mail_state)){
cell_11 = "办理中";
}else if("30".equals(mail_state)){
cell_11 = "处理中";
}else if("50".equals(mail_state)){
cell_11 = "已反馈";
}else if("70".equals(mail_state)){
cell_11 = "未通过";
}else if("90".equals(mail_state)){
cell_11 = "已办理";
}
ParamsUtil.nullToEmpty(data.get(i).getVc_mail_number());
String cell_12 = ParamsUtil.nullToEmpty(data.get(i).getDt_sendtime());
sheet.addCell(new Label(0, 3+i, cell_0,titleFormate));
sheet.addCell(new Label(1, 3+i, cell_1,titleFormate));
sheet.addCell(new Label(2, 3+i, cell_2,titleFormate));
sheet.addCell(new Label(3, 3+i, cell_3,titleFormate));
sheet.addCell(new Label(4, 3+i, cell_4,titleFormate));
sheet.addCell(new Label(5, 3+i, cell_5,titleFormate));
sheet.addCell(new Label(6, 3+i, cell_6,titleFormate));
sheet.addCell(new Label(7, 3+i, cell_7,titleFormate));
sheet.addCell(new Label(8, 3+i, cell_8,titleFormate));
sheet.addCell(new Label(9, 3+i, cell_9,titleFormate));
sheet.addCell(new Label(10, 3+i, cell_10,titleFormate));
sheet.addCell(new Label(11, 3+i, cell_11,titleFormate));
sheet.addCell(new Label(12, 3+i, cell_12,titleFormate));
}
}
workbook.write();
workbook.close();
FileInputStream fis = new FileInputStream(file);
BufferedInputStream fin = new BufferedInputStream(fis);
byte[] b = new byte[20];
int length;
OutputStream out = response. getOutputStream();
while ((length = fin.read(b)) > 0) {
out.write(b, 0, length);
}
}catch (Exception e) {
e.printStackTrace();
}
}
service层
public DataGrid<MailInfoEntity> queryHdMailInfoByParamForExport( Map<String, Object> paramMap ){
DataGrid<MailInfoEntity> dataGrid = null;
List<MailInfoEntity> resultListTmp = new ArrayList<MailInfoEntity>();
try{
dataGrid = new DataGrid<MailInfoEntity>();
String zpdateBegin = ParamsUtil.nullToEmpty( paramMap.get( "startTime" ) );
if(!"".equals( zpdateBegin )){
paramMap.put( "zpdateBegin", zpdateBegin);
}
String zpdateEnd = ParamsUtil.nullToEmpty( paramMap.get( "endTime" ) );
if(!"".equals( zpdateEnd )){
paramMap.put( "zpdateEnd", zpdateEnd);
}
resultListTmp = fdCorrelationDao.queryHdMailInfoByParamForExport(paramMap);
dataGrid.setData( resultListTmp );
}
catch ( Exception e ){
logger.error( e.getMessage(), e );
}
return dataGrid;
}
mapper.xml
<select id="queryHdMailInfoByParamForExport" parameterType="Map" resultMap="MailInfoMap">
select
vc_id,
dt_sendtime as id,
vc_user_id,
vc_user_name,
vc_mail_number,
vc_mailpass,
vc_delete_state,
t.vc_handle_mode,
t.vc_mail_state,
t.vc_title,
t.vc_content,
t.vc_dictionary_id,
(select vc_name from hd_sys_dictionary where vc_id=vc_dictionary_id) as vc_dictionary_id_name,
t.dt_sendtime,
t.vc_sendip,
t.vc_phone,
t.vc_mobile_phone,
t.vc_email,
t.vc_address,
t.vc_zipcode,
t.vc_parpers_type,
t.vc_papers_number,
t.vc_sex,
t.i_age,
t.vc_target_id,
t.vc_target,
t.b_person_ispublic,
t.b_mail_ispublic,
t.vc_receiver_id,
t.vc_receiver_name,
t.vc_receiver_opinion,
t.vc_receive_time,
t.vc_sysid,
(select hd.vc_sysname from HD_SYSTEM hd where hd.i_id = t.vc_sysid)as vc_sysname,
t.b_recommend,
t.vc_accept_state,
t.vc_handle_units
FROM hd_mail_info t
<where>
<if test="vc_title != null and vc_title != ''">
and t.vc_title like '%${vc_title}%'
</if>
<if test="vc_mail_state != null and vc_mail_state != ''">
and t.vc_mail_state = ${vc_mail_state}
</if>
<if test="vc_dictionary_id != null and vc_dictionary_id != ''">
and t.vc_dictionary_id = '${vc_dictionary_id}'
</if>
<if test="vc_sysid != null and vc_sysid != ''">
and t.vc_sysid = '${vc_sysid}'
</if>
<if test="zpdateBegin != null and zpdateBegin != ''">
<![CDATA[and to_date(t.dt_sendtime,'yyyy-mm-dd,hh24:mi:ss') >= to_date('${zpdateBegin}','yyyy-mm-dd,hh24:mi:ss')]]>
</if>
<if test="zpdateEnd != null and zpdateEnd != ''">
<![CDATA[and to_date(t.dt_sendtime,'yyyy-mm-dd,hh24:mi:ss') <= to_date('${zpdateEnd}','yyyy-mm-dd,hh24:mi:ss')]]>
</if>
</where>
order by t.dt_sendtime desc
</select>