要求：
代码：
JSP页面代码：
<body>
<div id="div1">
<fieldset style="border: 0; margin-top: 20px;">
<legend>
<span style="font-weight: bold; font-size: 15px;">讲师津贴明细查询导出</span>
</legend>
<hr>
</fieldset>
//这个请求千万不要忘记！！！！我会写在最后，这是动态拼Excel表格样式的代码
<form action="${ctx}/teacherManage/exportMXExcel.do" id="queryTeacherForm" name="fm" class="form-horizontal">
<table id="manageUserForm1" style="width: 85%;">
<tr>
开始日期：<input name="start_date"
id="start_date" class="mini-datepicker" format="yyyy-MM-dd"
timeFormat="H:mm:ss" showTime="false" allowInput="false" />
</tr>
<tr>
结束日期：<input name="end_date"
id="end_date" class="mini-datepicker" format="yyyy-MM-dd"
timeFormat="H:mm:ss" showTime="false" allowInput="false" />
</tr>
</table>
</form>
<br/>
<div class="mini-button" onclick="checkOutMX()" style="width:150px; margin-right: 32px;">导出讲师津贴明细</div>
</body>
JS中代码：
mini.parse();
var ctx = mini.get("ctx").getValue();
var form = new mini.Form("#exportForm");
function resetF() {
form.reset();
}
//津贴明细导出
function checkOutMX() {
var start_date = mini.get("start_date").getText();
var end_date = mini.get("end_date").getText();
if((start_date!=null&&start_date!='') && (end_date != null&&end_date!='')){
if(start_date>end_date){
mini.alert("开始时间不能大于结束时间！");
return;
}
}
if(!exportExcelCheckMX()){
return;
}
$("#queryTeacherForm").submit();
}
//津贴明细导出前校验
function exportExcelCheckMX() {
var flag = false;
var start_date = mini.get("start_date").getText();
var end_date = mini.get("end_date").getText();
$.ajax({
type : 'POST',
async : false,
url : ctx + "/teacherManage/exportExcelCheckMX.do",
data : {
start_date:start_date,
end_date:end_date
},
dataType : "json",
success : function(data) {
if(data.flag == "0"){
flag = true;
}else{
mini.alert(data.msg);
}
},
error : function(e) {
mini.alert("系统异常，请稍后再试！");
}
});
return flag;
}
Controller中代码：
/**
* 讲师津贴明细报表导出前校验
* @param trainee
* @return
* @throws ParseException
*/
@RequestMapping(value = "/exportExcelCheckMX.do")
public @ResponseBody ComResultDTO CReportExportExcelCheckMX(HttpServletRequest request,HttpSession session,PageDTO pageDTO){
logger.info("============讲师津贴明细导出前校验============");
TeacherManagerDto req = new TeacherManagerDto();
String start_date = request.getParameter("start_date");
String end_date = request.getParameter("end_date");
req.setStart_date(start_date);
req.setEnd_date(end_date);
String manage_com =SessionUtil.getUser().getManageCom();
req.setManage_com(manage_com);
pageDTO.setParameters(req);
int startNum = (pageDTO.getPageIndex()-1)*pageDTO.getPageSize();
pageDTO.setStartNum(startNum);
List<TeacherManagerDto> queryList = teacherManagerService.selectTeacherChargeByPageDTO(pageDTO);
ComResultDTO comResultDTO = new ComResultDTO();
if(queryList != null && queryList.size() > 50000){
comResultDTO.setFlag("1");
comResultDTO.setMsg("导出报表上限为50000条，数据量过大，导出失败！");
logger.info("============导出报表上限为50000条，数据量过大，导出失败！============");
}else if(queryList != null && queryList.size() == 0){
comResultDTO.setFlag("1");
comResultDTO.setMsg("导出数据为空，导出失败！");
logger.info("============导出数据为空，导出失败！============");
}else{
comResultDTO.setFlag("0");
}
return comResultDTO;
}
service中代码：
@Override
public List<TeacherManagerDto> selectTeacherChargeByPageDTO(PageDTO pageDTO) {
return teacherManagerMapper.selectTeacherChargeByPageDTO(pageDTO);
}
mapper.xml中代码：
<!-- 查询讲师报表导出数据 -->
<select id="selectTeacherChargeByPageDTO" resultMap="teacherManage" parameterType="com.hualife.bit.util.PageDTO" >
select
a.teacher_id,
(select b.short_name from managecom_info b where b.manage_com=SUBSTRING(a.manage_com,1,4)) fen_gongsi,
(select b.short_name from managecom_info b where b.manage_com=SUBSTRING(a.manage_com,1,6)) zhong_zhi,
(select b.manage_com from managecom_info b where b.manage_com=a.manage_com) manage_com,
a.name,
(select c.value from def_code_info c where c.code=a.if_employee and c.code_type='if_employee') if_employee,
(select e.value from def_code_info e where e.code=a.teacher_type and e.code_type='teacher_type') teacher_type,
(select e.value from def_code_info e where e.code=a.teacher_grade and e.code_type='teacher_grade') teacher_grade,
DATE_FORMAT(a.created_time,'%Y-%m-%d %H:%i') created_time,
case when tc.charge_type='1' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end shou_ke,
case when tc.charge_type='2' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end yan_fa,
case when tc.charge_type='3' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end xiang_mu,
case when tc.charge_type='4' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end zhu_jiao,
IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) zongji
from teacher a LEFT JOIN teacher_charge tc on a.teacher_id=tc.teacher_id where a.created_time >= #{parameters.start_date} and a.created_time <= #{parameters.end_date} and a.manage_com like concat(#{parameters.manage_com},'%')
GROUP BY a.teacher_id
limit #{startNum,jdbcType=DECIMAL},#{pageSize,jdbcType=DECIMAL}
</select>
动态拼Excel表格样式的代码：
Controller中代码:
/**
* 讲师津贴明细报表导出功能
* @param requestDTO
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(value = "/exportMXExcel.do")
public void CReportExportExcelMX(TeacherManagerDto requestDTO,HttpServletRequest request,
HttpServletResponse response,PageDTO pageDTO)
throws Exception {
String manage_com =SessionUtil.getUser().getManageCom();
requestDTO.setManage_com(manage_com);
pageDTO.setParameters(requestDTO);
//封装搜索引擎参数
//Map<String, Object> map = new HashMap<String, Object>();
/*map =*/
teacherChargeQueryService.reportExcelMX(request,response,pageDTO);
/*ExcelView viewExcel = new ExcelView();
HSSFWorkbook arg1= new HSSFWorkbook();
viewExcel.buildExcelDocument1(map, arg1, request, response);*/
}
Service中代码：
public Map<String, Object> reportExcelMX(HttpServletRequest request, HttpServletResponse response,PageDTO pageDTO) throws Exception {
// 表头
//String[] tableHeader = { "分公司", "中支", "构编编码", "姓名", "人员类别（前线、后线）","讲师类别（专职讲师、督训、兼职讲师)","岗位职级 （xx级督训）", "研发津贴",
// "授课津贴","助教津贴","项目津贴","金额合计"};
// 表名
//String tableName = "讲师津贴明细报表";
//Map<String, Object> map = new HashMap<String, Object>();
//map.put("teacherList", teacherList);
//map.put("tableHeader", tableHeader);
//map.put("tableName", tableName);
//return map;
String filename = "TeacherCharge";
// 获得输出流，该输出流的输出介质是客户端浏览器
OutputStream output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xls");
response.setContentType("application/msexcel");
// 创建可写入的Excel工作薄，且内容将写入到输出流，并通过输出流输出给客户端浏览
WritableWorkbook wk = Workbook.createWorkbook(output);
/// 创建可写入的Excel工作表
WritableSheet sheet = wk.createSheet("讲师津贴明细报表", 0);
sheet.setColumnView(0, 10);
sheet.setColumnView(1, 15);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 13);
sheet.setColumnView(4, 13);
sheet.setColumnView(5, 15);
sheet.setColumnView(6, 15);
sheet.setColumnView(7, 15);
sheet.setColumnView(8, 15);
sheet.setColumnView(9, 15);
sheet.setColumnView(10, 15);
sheet.setColumnView(11, 15);
sheet.setColumnView(12, 15);
sheet.setColumnView(13, 15);
sheet.setColumnView(14, 15);
sheet.setColumnView(15, 15);
sheet.setColumnView(16, 15);
// 把单元格（column, row）到单元格（column1, row1）进行合并。
// mergeCells(column, row, column1, row1);
//sheet.mergeCells(0, 0, 5, 0);// 单元格合并方法
// 创建WritableFont 字体对象，参数依次表示黑体、字号12、粗体、非斜体、不带下划线、亮蓝色
WritableFont titleFont0 = new WritableFont(WritableFont.createFont("宋体"), 18, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont titleFont1 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont titleFont2 = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
// 创建WritableCellFormat对象，将该对象应用于单元格从而设置单元格的样式
WritableCellFormat titleFormat0 = new WritableCellFormat();
titleFormat0.setFont(titleFont0);
titleFormat0.setAlignment(Alignment.CENTRE);
titleFormat0.setVerticalAlignment(VerticalAlignment.CENTRE);
titleFormat0.setBorder(Border.ALL, BorderLineStyle.THIN);
titleFormat0.setWrap(true);
WritableCellFormat titleFormat1 = new WritableCellFormat();
// 设置字体格式
titleFormat1.setFont(titleFont1);
// 设置文本水平居中对齐
titleFormat1.setAlignment(Alignment.CENTRE);
// 设置文本垂直居中对齐
titleFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
// 设置背景颜色
titleFormat1.setBackground(Colour.YELLOW);
titleFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
// 设置自动换行
titleFormat1.setWrap(true);
WritableCellFormat titleFormat2 = new WritableCellFormat();
titleFormat2.setFont(titleFont2);
titleFormat2.setAlignment(Alignment.CENTRE);
titleFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
titleFormat2.setWrap(true);
titleFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
/*ClassInfo classInfo = classInfoMapper.selectByPrimaryKey(Long.valueOf(class_id));
List<TraineeSignInfo> signlist = classInfoHelperMapper.querySignInTraineeInfoByClassId(Long.valueOf(class_id));*/
//String[] tableHeader = { "分公司", "中支", "机构编码", "姓名", "人员类别（前线、后线）","讲师类别（专职讲师、督训、兼职讲师)",
//"岗位职级 （xx级督训）", "研发津贴",
// "授课津贴","助教津贴","项目津贴","金额合计"};
//sheet.addCell(new Label(0, 0, "讲师津贴明细报表", titleFormat0));
sheet.addCell(new Label(0, 0, "序号", titleFormat1));
sheet.addCell(new Label(1, 0, "分公司", titleFormat1));
sheet.addCell(new Label(2, 0, "中支", titleFormat1));
sheet.addCell(new Label(3, 0, "机构编码", titleFormat1));
sheet.addCell(new Label(4, 0, "姓名", titleFormat1));
sheet.addCell(new Label(5, 0, "人员类别（前线、后线）", titleFormat1));
sheet.addCell(new Label(6, 0, "讲师类别（专职讲师、督训、兼职讲师)", titleFormat1));
sheet.addCell(new Label(7, 0, "岗位职级 （xx级督训）", titleFormat1));
sheet.mergeCells(8, 0, 9, 0);// 单元格合并方法
sheet.addCell(new Label(8, 0, "研发津贴", titleFormat1));
/*sheet.addCell(new Label(9, 1, "研发津贴", titleFormat1));*/
sheet.mergeCells(10, 0, 11, 0);
sheet.addCell(new Label(10, 0, "授课津贴", titleFormat1));
/*sheet.addCell(new Label(11, 1, "授课津贴", titleFormat1));*/
sheet.mergeCells(12, 0, 13, 0);
sheet.addCell(new Label(12, 0, "助教津贴", titleFormat1));
/*sheet.addCell(new Label(13, 1, "助教津贴", titleFormat1));*/
sheet.mergeCells(14, 0, 15, 0);
sheet.addCell(new Label(14, 0, "项目津贴", titleFormat1));
/*sheet.addCell(new Label(15, 1, "项目津贴", titleFormat1));*/
/*sheet.addCell(new Label(16, 1, "金额合计", titleFormat1));*/
sheet.addCell(new Label(16, 0, "金额合计", titleFormat1));
sheet.addCell(new Label(8, 1, "项目", titleFormat1));
sheet.addCell(new Label(9, 1, "金额", titleFormat1));
sheet.addCell(new Label(10, 1, "项目", titleFormat1));
sheet.addCell(new Label(11, 1, "金额", titleFormat1));
sheet.addCell(new Label(12, 1, "项目", titleFormat1));
sheet.addCell(new Label(13, 1, "金额", titleFormat1));
sheet.addCell(new Label(14, 1, "项目", titleFormat1));
sheet.addCell(new Label(15, 1, "金额", titleFormat1));
sheet.mergeCells(0, 0, 0, 1);
sheet.mergeCells(1, 0, 1, 1);
sheet.mergeCells(2, 0, 2, 1);
sheet.mergeCells(3, 0, 3, 1);
sheet.mergeCells(4, 0, 4, 1);
sheet.mergeCells(5, 0, 5, 1);
sheet.mergeCells(6, 0, 6, 1);
sheet.mergeCells(7, 0, 7, 1);
sheet.mergeCells(16, 0, 16, 1);
List<TeacherManagerDto> teacherList = TeacherManagerMapper.selectTeacherChargeByPageDTO(pageDTO);
if(teacherList.size()!=0){
int flag2 = 0 ;
for (int i = 0; i < teacherList.size(); i++) {
int flag1 = 0 ;
Long teacher_id =teacherList.get(i).getTeacher_id();
List<TeacherChargeDto> list1 =TeacherManagerMapper.selectChargeType1(teacher_id);
for (int j = 0; j < list1.size(); j++) {
sheet.addCell(new Label(8, 2 + j+flag2, list1.get(j).getCourse_name(), titleFormat2));
sheet.addCell(new Label(9, 2 + j+flag2, list1.get(j).getJin_e(), titleFormat2));
}
flag1=list1.size();
List<TeacherChargeDto> list2 =TeacherManagerMapper.selectChargeType2(teacher_id);
for (int j = 0; j < list2.size(); j++) {
sheet.addCell(new Label(10, 2 + j+flag2, list2.get(j).getCourse_name(), titleFormat2));
sheet.addCell(new Label(11, 2 + j+flag2, list2.get(j).getJin_e(), titleFormat2));
}
if(list2.size() > flag1){
flag1=list2.size();
}
List<TeacherChargeDto> list3 =TeacherManagerMapper.selectChargeType3(teacher_id);
for (int j = 0; j < list3.size(); j++) {
sheet.addCell(new Label(12, 2 + j+flag2, list3.get(j).getCourse_name(), titleFormat2));
sheet.addCell(new Label(13, 2 + j+flag2, list3.get(j).getJin_e(), titleFormat2));
}
if(list3.size() >flag1 ){
flag1=list3.size();
}
List<TeacherChargeDto> list4 =TeacherManagerMapper.selectChargeType2(teacher_id);
for (int j = 0; j < list4.size(); j++) {
sheet.addCell(new Label(14, 2 + j+flag2, list4.get(j).getCourse_name(), titleFormat2));
sheet.addCell(new Label(15, 2 + j+flag2, list4.get(j).getJin_e(), titleFormat2));
}
if(list4.size() > flag1){
flag1=list4.size();
}
sheet.mergeCells(0,2+flag2+i, 0, 2+flag2+flag1-1+i);
sheet.mergeCells(1,2+flag2+i, 1, 2+flag2+flag1-1+i);
sheet.mergeCells(2,2+flag2+i, 2, 2+flag2+flag1-1+i);
sheet.mergeCells(3,2+flag2+i, 3, 2+flag2+flag1-1+i);
sheet.mergeCells(4,2+flag2+i, 4, 2+flag2+flag1-1+i);
sheet.mergeCells(5,2+flag2+i, 5, 2+flag2+flag1-1+i);
sheet.mergeCells(6,2+flag2+i, 6, 2+flag2+flag1-1+i);
sheet.mergeCells(7,2+flag2+i, 7, 2+flag2+flag1-1+i);
sheet.mergeCells(16,2+flag2+i,16, 2+flag2+flag1-1+i);
sheet.addCell(new Label(0, 2+flag2, String.valueOf(i + 1), titleFormat2));
sheet.addCell(new Label(1, 2+flag2, teacherList.get(i).getFen_gongsi(), titleFormat2));
sheet.addCell(new Label(2, 2+flag2, teacherList.get(i).getZhong_zhi(), titleFormat2));
sheet.addCell(new Label(3, 2+flag2, teacherList.get(i).getManage_com(), titleFormat2));
sheet.addCell(new Label(4, 2+flag2, teacherList.get(i).getName(), titleFormat2));
sheet.addCell(new Label(5, 2+flag2, teacherList.get(i).getIf_employee(), titleFormat2));
sheet.addCell(new Label(6, 2+flag2, teacherList.get(i).getTeacher_type(), titleFormat2));
sheet.addCell(new Label(7, 2+flag2, teacherList.get(i).getTeacher_grade(), titleFormat2));
sheet.addCell(new Label(16,2+flag2, teacherList.get(i).getZongji(), titleFormat2));
flag2=flag2+(flag1==0?1:flag1);
}
}
// 添加Label对象，参数依次表示在第一列，第一行，内容，使用的格式
// Label lab_00=new Label(0,0,"学员考试成绩一览表",titleFormat1);
// 将定义好的Label对象添加到工作表上，这样工作表的第一列第一行的内容为‘学员考试成绩一览表’并应用了titleFormat定义的样式
// sheet.addCell(lab_00);
//
// WritableCellFormat cloumnTitleFormat=new WritableCellFormat();
//
// cloumnTitleFormat.setFont(new
// WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD,false));
//
// cloumnTitleFormat.setAlignment(Alignment.CENTRE);
//
// Label lab_01=new Label(0,1,"姓名",cloumnTitleFormat);
//
// Label lab_11=new Label(1,1,"班级",cloumnTitleFormat);
//
// Label lab_21=new Label(2,1,"笔试成绩",cloumnTitleFormat);
//
// Label lab_31=new Label(3,1,"上机成绩",cloumnTitleFormat);
//
// Label lab_41=new Label(4,1,"考试日期",cloumnTitleFormat);
//
// sheet.addCell(lab_01);
//
// sheet.addCell(lab_11);
//
// sheet.addCell(lab_21);
//
// sheet.addCell(lab_31);
//
// sheet.addCell(lab_41);
//
// sheet.addCell(new Label(0,2,"李明"));
//
// sheet.addCell(new Label(1,2,"As178"));
// 定义数字格式
// NumberFormat nf=new NumberFormat("0.00");
//
// WritableCellFormat wcf=new WritableCellFormat(nf);
// 类似于Label对象，区别Label表示文本数据，Number表示数值型数据
// Number numlab_22=new Number(2,2,78,wcf);
//
// sheet.addCell(numlab_22);
//
// sheet.addCell(newNumber(3,2,87,new WritableCellFormat(new
// NumberFormat("#.##") )));
// 定义日期格式
//
// DateFormat df=new DateFormat("yyyy-MM-dd hh:mm:ss");
// 创建WritableCellFormat对象
// WritableCellFormat datewcf=new WritableCellFormat(df);
// 类似于Label对象，区别Label表示文本数据，DateTime表示日期型数据
// DateTime dtLab_42=new DateTime(4,2,new Date(),datewcf);
//
// sheet.addCell(dtLab_42);
// 将定义的工作表输出到之前指定的介质中（这里是客户端浏览器）
wk.write();
// 操作完成时，关闭对象，释放占用的内存空间
wk.close();
// 加下划线这部分代码是B/S模式中采用的输出方式，而不是输出到本地指定的磁盘目录。该代码表示将temp.xls的Excel文件通过应答实体（response）输出给请求的客户端浏览器，下载到客户端本地（保存或直接打开）。若要直接输出到磁盘文件可采用下列代码替换加下划线这部分代码
// File file=new File("D://temp.xls");
// WritableWorkbook wwb = Workbook.createWorkbook(file);
return null;
}
四个红色下划线需要的SQL语句查询：
<!-- 查询讲师报表导出数据 -->
<select id="selectTeacherChargeByPageDTO" resultMap="teacherManage" parameterType="com.hualife.bit.util.PageDTO" >
select
a.teacher_id,
(select b.short_name from managecom_info b where b.manage_com=SUBSTRING(a.manage_com,1,4)) fen_gongsi,
(select b.short_name from managecom_info b where b.manage_com=SUBSTRING(a.manage_com,1,6)) zhong_zhi,
(select b.manage_com from managecom_info b where b.manage_com=a.manage_com) manage_com,
a.name,
(select c.value from def_code_info c where c.code=a.if_employee and c.code_type='if_employee') if_employee,
(select e.value from def_code_info e where e.code=a.teacher_type and e.code_type='teacher_type') teacher_type,
(select e.value from def_code_info e where e.code=a.teacher_grade and e.code_type='teacher_grade') teacher_grade,
DATE_FORMAT(a.created_time,'%Y-%m-%d %H:%i') created_time,
case when tc.charge_type='1' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end shou_ke,
case when tc.charge_type='2' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end yan_fa,
case when tc.charge_type='3' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end xiang_mu,
case when tc.charge_type='4' then IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) end zhu_jiao,
IFNULL(SUM(tc.charge*tc.charge_total*tc.rate),0) zongji
from teacher a LEFT JOIN teacher_charge tc on a.teacher_id=tc.teacher_id where a.created_time >= #{parameters.start_date} and a.created_time <= #{parameters.end_date} and a.manage_com like concat(#{parameters.manage_com},'%')
GROUP BY a.teacher_id
limit #{startNum,jdbcType=DECIMAL},#{pageSize,jdbcType=DECIMAL}
</select>
<!-- 查询讲师津贴详细信息报表导出 -->
<select id="selectChargeType1" resultMap="teacherCharge" parameterType="java.lang.Long" >
select
a.id,
(select course_name from class_course c where a.course_id = c.course_id )course_name,
IFNULL(SUM(a.charge*a.charge_total*a.rate),0) jin_e
from teacher_charge a where a.teacher_id = #{parameters.teacher_id} and a.charge_type = '1'
GROUP BY a.id
</select>
<select id="selectChargeType2" resultMap="teacherCharge" parameterType="java.lang.Long" >
select
a.id,
(select class_name from class_info c where a.class_id = c.class_id )course_name,
IFNULL(SUM(a.charge*a.charge_total*a.rate),0) jin_e
from teacher_charge a where a.teacher_id = #{parameters.teacher_id} and a.charge_type = '2'
GROUP BY a.id
</select>
<select id="selectChargeType3" resultMap="teacherCharge" parameterType="java.lang.Long" >
select
a.id,
(select project_name from project_manage c where a.project_id = c.project_id )course_name,
IFNULL(SUM(a.charge*a.charge_total*a.rate),0) jin_e
from teacher_charge a where a.teacher_id = #{parameters.teacher_id} and a.charge_type = '3'
GROUP BY a.id
</select>
2797
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
