xml:
<select id="selectList" resultType="返回的类型_">
SELECT
r.*,o.name orgname
from
v_r r,e_o o
where r.createhospital = o.id
<if test="name!=null and name!=''">
AND r.name like '%${name}%'
</if>
<if test="telphone!=null and telphone!=''">
AND r.telphone like '%${telphone}%'
</if>
<if test="curoperatorOrgid!=null and curoperatorOrgid!=''">
AND r.createhospital = #{curoperatorOrgid}
</if>
<if test="cardno!=null and cardno!=''">
AND r.cardno like '%${cardno}%'
</if>
<if test="status!=null and status!=''">
AND r.status = #{status}
</if>
<if test="code!=null and code!=''">
AND r.code like '%${code}%'
</if>
<if test="beg_date!=null and beg_date!=''">
AND date_format(r.createtime,'%Y-%m-%d') >= #{beg_date}
</if>
<if test="end_date!=null and end_date!=''">
AND date_format(r.createtime,'%Y-%m-%d') <= #{end_date}
</if>
<if test="sex!=null and sex!=''">
AND r.sex = #{sex}
</if>
<if test="nation!=null and nation!=''">
AND r.address like '%${nation}%'
</if>
order by r.orderno
</select>
mapper:
public List<实体类名> selectList(
@Param(value = "name") String name,
@Param(value = "telphone") String telphone,
@Param(value = "curoperatorOrgid") Long cur_orgid,
@Param(value = "cardno") String cardno,
@Param(value = "status") String status,
@Param(value = "code") String code,
@Param(value = "beg_date")String beg_date,
@Param(value = "end_date")String end_date,
@Param(value = "sex")String sex,
@Param(value = "nation")String nation
);
service:
public List<类名> getReceiverList(String name, String telphone, Long cur_operatorOrgid, String cardno,
String status, String code, String beg_date, String end_date, String sex, String nation) {
return receiverMapper.selectList(name, telphone, cur_operatorOrgid, cardno, status, code, beg_date, end_date,sex, nation);
}
controller:
@RequestMapping(params = "method=exportExcel")
public void exportExcel(String name, String telphone, String cardno, String status, String code,
HttpServletRequest request, HttpServletResponse response, String beg_date, String end_date, String sex,
String nation, String org, ModelMap map, HttpSession session) throws Exception {
Long cur_oOrgid = null;
if (org == null || org.isEmpty()) {
cur_operatorOrgid = ConstantUtil.getSession_Manager(session).getOrgid();
} else {
cur_operatorOrgid = Long.parseLong(org);
}
// 查询受捐者列表
List<类名> receiverList = receiverService.getReceiverList(name, telphone, cur_operatorOrgid, cardno,
status, code, beg_date, end_date, sex, nation);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
// 首先引入poi-3.7-20101029.jar包
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("sheet1");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
// 表单头
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("序号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("姓名");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("性别");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("编号");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("身份证号码");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("出生日期");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("联系电话");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("登记时间");
cell.setCellStyle(style);
cell = row.createCell((short) 8);
cell.setCellValue("所在医院");
cell.setCellStyle(style);
cell = row.createCell((short) 9);
cell.setCellValue("状态");
cell.setCellStyle(style);
cell = row.createCell((short) 10);
cell.setCellValue("排队信息");
cell.setCellStyle(style);
// 第四步,创建单元格,并设置值
for (int i = 0; i < receiverList.size(); i++) {
row = sheet.createRow((int) i + 1);
cell = row.createCell((short) 0);
cell.setCellValue(i + 1);
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue(receiverList.get(i).getName());
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue(
receiverList.get(i).getSex() == 0 ? "未知" : (receiverList.get(i).getSex() == 1 ? "男" : "女"));
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue(receiverList.get(i).getCode());
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue(receiverList.get(i).getCardno());
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue(sdf2.format(receiverList.get(i).getBirthday()));
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue(receiverList.get(i).getTelphone());
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue(sdf.format(receiverList.get(i).getCreatetime()));
cell.setCellStyle(style);
cell = row.createCell((short) 8);
cell.setCellValue(receiverList.get(i).getOrgname());
cell.setCellStyle(style);
cell = row.createCell((short) 9);
cell.setCellValue(receiverList.get(i).getStatus() == 0 ? "未接受" : "已接受");
cell.setCellStyle(style);
cell = row.createCell((short) 10);
cell.setCellValue("第" + receiverList.get(i).getOrderno() + "名");
cell.setCellStyle(style);
}
// 第六步,将文件存到指定位置
try {
String filename = ("导出的文件名.xls");
response.setContentType("application/x-msdownload");// 下载设置
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(filename.getBytes("GBK"), "iso8859-1"));// 下载设置
System.out.println("=============受捐者导出excel=================");
OutputStream ous = response.getOutputStream();// 下载输出流
wb.write(ous);
} catch (Exception e) {
e.printStackTrace();
}
}
html:
<button class="btn btn-default btn-sdtheme"
οnclick="exportExcel()">导出excel</button>
js:
function exportExcel() {
var name = $("#search_name").val();
var telphone = $("#search_phone").val();
var cardno = $("#search_cardno").val();
var status = $("#status").val();
var code = $("#search_code").val();
var beg_date = $("#beg_date").val();
var end_date = $("#end_date").val();
var sex = $("#search_sex").val();
var nation = $("#search_nation").val();
var org = "${orgid}" == 0 ? $("#search_org").val() : "";
location.href = "${ctx}/*******/*******.do?method=exportExcel&name=" + name + "&code=" + code + "&cardno=" + cardno + "&telphone="
+ telphone + "&status=" + status + "&beg_date=" + beg_date + "&end_date=" + end_date + "&sex=" + sex + "&nation=" + nation
+ "&org=" + org;
}