多页签报表导出_${lfn:message('')}

作者：从前慢现在也慢 | 2024-03-19 20:38:38

踩

${lfn:message('')}

@TOC多页签报表导出

功能需求

每个部门每月都会发起“办公用品申请及领用流程”，经行政负责人审批后的流程，系统可统计本月内所有已审批的流程，自动将办公用品明细汇总成excel表。

具体实现效果

第一个页签为汇总表，记录该月的所有办公用品的申报信息：
接下来的页签为各部门申请办公用品的明细，页签命名为部门+(流程名) 各流程申请明细

效果展示

jsp页面：
在这里插入图片描述
导出效果：

界面展示：

代码实现

界面的编写

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ include file="/sys/ui/jsp/common.jsp"%>
<template:include ref="default.simple">
	<template:replace name="body">
		<%@ page import="java.util.*"%>
		<%@ page import="com.landray.kmss.km.oitems.model.KmOitemsListing"%>
		<%@ page
			import="com.landray.kmss.km.oitems.model.KmOitemsShoppingTrolley"%>
		<%@ page import="org.apache.commons.lang.StringEscapeUtils"%>
		<script type="text/javascript">
			seajs.use([ 'theme!form' ]);
		</script>
		<script type="text/javascript">
			Com_IncludeFile("docutil.js|calendar.js|dialog.js|doclist.js|optbar.js|list.js");
		</script>
		<script type="text/javascript">
			Com_AddEventListener(document, "keydown", function() {
				var eventObj = Com_GetEventObject();
				var keyCode = eventObj.keyCode;
				if (keyCode == 13) {
					var clickObj = document.getElementById("ok_id");
					clickObj.click();
				}
			});

			seajs.use([ 'lui/dialog' ], function(dialog) {
				window.dialog = dialog;
			});

			function clear_data() {
				alert(1);
				location.href = "${LUI_ContextPath }/km/oitems/km_oitems_listing/kmOitemsListing.do?method=outCount";
			}
			window.onload = function() {
				setTimeout(dyniFrameSize, 100);
			};
			function dyniFrameSize() {
				try {
					// 调整高度
					var arguObj = document.getElementById("contentDiv");
					if (arguObj != null && window.frameElement != null
							&& window.frameElement.tagName == "IFRAME") {
						window.frameElement.style.height = (arguObj.offsetHeight + 40)
								+ "px";
					}
				} catch (e) {
				}
			};
		</script>


		<script type="text/javascript">
			$(function() {
				var dd = new Date();
				var currentYear = dd.getFullYear();
				var size = currentYear - 2002 + 1;
				for (var i = 0; i < size; i++) {
					var yearOld = currentYear - i;
					$("#years").append(
							$("<option value="+yearOld+">" + yearOld + "年"
									+ "</option>"));
				}
			});
			var textSel = null;
			/* 获取选中的下拉框的值 */
			function yearSelected() {
				textSel = $("#years").find("option:selected").val();

			}

			/* 获取选中月份的下拉框的值 */
			var monthText = null;
			function show() {
				monthText = $("#select").find("option:selected").val();
			}
			
			function clear_data() {
				location.href = "${LUI_ContextPath }/km/oitems/km_oitems_listing/kmOitemsListing.do?method=export&years="+textSel+"&month="+monthText;
			}
		</script>
		<html:form action="/km/oitems/km_oitems_listing/kmOitemsListing.do">
			<ui:tabpanel id="kmOitemsOutCountPanel" layout="sys.ui.tabpanel.list">
				<ui:content id="kmOitemsOutCountContent"
					title="${ lfn:message('km-oitems:kmOitems.tree.reporting2') }">
					<div id="contentDiv"
						style="width: 95%; min-height: 500px; padding: 20px">
						
						<center>
							<div style="width: 96%; padding-top: 35px">
								<table width="70%" class="tb_normal">
									<tr>
										<td class="td_normal_title" width="15%">年份</td>
										<td><select id="years" name="years"
											onchange="yearSelected()">
												<option value="-1">---请选择---</option>
										</select></td>

										<td class="td_normal_title" width="15%">月份</td>
										<td><select class="ui-select" name="select" id="select"
											onchange="show()">
												<option value="0">--请选择---</option>
												<option value="1">1月</option>
												<option value="2">2月</option>
												<option value="3">3月</option>
												<option value="4">4月</option>
												<option value="5">5月</option>
												<option value="6">6月</option>
												<option value="7">7月</option>
												<option value="8">8月</option>
												<option value="9">9月</option>
												<option value="10">10月</option>
												<option value="11">11月</option>
												<option value="12">12月</option>
										</select></td>
									</tr>
								</table>
							</div>
							<br /> <span> <ui:button id="ok_id" text="导出" order="2" onclick="clear_data();">
								</ui:button>&nbsp;&nbsp; <ui:button
									text="${lfn:message('km-oitems:kmOitems.button.clear') }"
									order="2" onclick="clear_data();">
								</ui:button>
							</span> <br /> <br />

							<div style="width: 96%"></div>
						</center>
					</div>
				</ui:content>
			</ui:tabpanel>
		</html:form>
		<script type="text/javascript">
			function onRadioClick(obj) {
				var a_deptObj = document.getElementById("a_dept_id");
				var a_personObj = document.getElementById("a_person_id");
				var value = obj.value;
				if (value == 1) {
					a_deptObj.style.display = "";
					a_personObj.style.display = "none";
				} else {
					a_deptObj.style.display = "none";
					a_personObj.style.display = "";
				}
			}
		</script>
	</template:replace>
</template:include>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

后端代码的实现（action层的数据交互）

###导出方法

	/**
	 * 导出
	 */
	public ActionForward export(ActionMapping mapping, ActionForm form, HttpServletRequest request,HttpServletResponse response) throws Exception {
		String years = request.getParameter("years");//获取前端years的值
		String month = request.getParameter("month");//获取月份
		System.out.println("年:" + years);
		System.out.println("月:" + month);
		//调用导出方法
		HSSFWorkbook wb = listArticles(years,month); //将年月传入listArticles(years,month)创建excel表单
		String filename = years+"年"+month+"月"+"办公用品申请表";//excel表单的名字设定
		response.setContentType("application/vnd.ms-excel");
		filename = new String(filename.getBytes("GBK"), "iso8859-1")
				+ ".xls";
		response.setHeader("Cache-Control", "max-age=0");
		response.addHeader("Content-Disposition", "attachment;filename="+ filename);
        wb.write(response.getOutputStream());
		return getActionForward(null, mapping, form, request, response);
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

注意：创建excel表单的文件后缀有两种(.xlsx和.xls)，生成这两种格式表单有对应不同的表单类，千万不要弄错！！

导出.xlsx格式

表单类用：

Workbook workbook = new XSSFWorkbook();
1

response设置：

 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 response.addHeader("Content-Disposition", "attachment;filename=fileName" + ".xlsx");
1
2

导出.xls格式

表单类用：

Workbook workbook = new HSSFWorkbook();
1

response设置：

response.setContentType("application/vnd.ms-excel");
  response.addHeader("Content-Disposition", "attachment;filename=fileName"+".xls");
1
2

---------------------------------------------分割线---------------------------------------------
1

表单创建方法

public HSSFWorkbook listArticles(String years, String month) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;// 结果集
		SystemConnection tem = new SystemConnection();
		String title = years+"年"+month+"月份汇总";
		
		String date ="";
		if(Integer.parseInt(month)<10){
			month = "0"+month;
		}
		date = years+"-"+month;
		
		//查询所有部门的ID
/*		String sql = "select fd_id,fd_suoShuBuMen from ekp_articles_main where fd_shenBaoSuoShuNian='" + years
				+ "' and fd_shenBaoSuoShuYueFen='" + month + "' ";*/
		//查询所有部门
		String sql = "select distinct fd_suoShuBuMen,fd_faQiRen from ekp_articles_main where fd_shenBaoSuoShuNian='" + years
							+ "' and fd_shenBaoSuoShuYueFen='" + month + "' ";
		
		//
		
		String fd_id = null;
		String elmentId =""; //部门Id
		String personId =""; //发起人Id
		
				
		// 创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		wb = createSheet(wb,title);  //汇总页签的创建
		
		try {
			conn = tem.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String elmentName ="";
				elmentId = rs.getString("fd_suoShuBuMen");// 部门id
				if (StringUtil.isNotNull(elmentId)) {
					SysOrgElement element =(SysOrgElement) getSysOrgElementServiceImp().findByPrimaryKey(elmentId);
					elmentName = element.getFdName();
				}
				
				String personName = "";
				personId = rs.getString("fd_faQiRen");
				if (StringUtil.isNotNull(personId)) {
					SysOrgPerson person =(SysOrgPerson) getSysOrgPersonServiceImp().findByPrimaryKey(personId);
					personName = person.getFdName();
				}
				
				
				PreparedStatement ps2 = null;
				ResultSet rs2 = null;// 结果集
				// 根据部门ID查询该部门某人发起了多少流程()
				 String fd_suoShuBuMen = elmentId;
				 String sql2 = "select fd_id from ekp_articles_main where fd_suoShuBuMen='" + fd_suoShuBuMen+ "' and fd_shenBaoSuoShuNian='" + years
							+ "' and fd_shenBaoSuoShuYueFen='" + month + "' "+"and fd_faQiRen='"+personId+"'";
				 String docSubject = "";
				 ps2 = conn.prepareStatement(sql2);
				  rs2 = ps2.executeQuery();
				  while(rs2.next()){
					fd_id = rs2.getString("fd_id"); 
					if (StringUtil.isNotNull(fd_id)) {
						KmReviewMain kmReviewMain = (KmReviewMain) getkmReviewMainService().findByPrimaryKey(fd_id);
						docSubject = kmReviewMain.getDocSubject();
						wb = createDeptSheet(wb,elmentName,date,personName,docSubject); //流程页签的创建
						wb = scheduleExport(fd_id,wb,personName); //表单数据的引入
					}
				}
					//查询完后记得释放资源释放资源
					try {
						if( rs2!= null ) {
							rs2.close();
						}
						   
					} catch (SQLException e) {
						// TODO 自动生成的 catch 块
						e.printStackTrace();
					}finally {
						 rs2 = null;
					}
					
					try {
						if( ps2!= null ) {
							ps2.close();
						}
						   
					} catch (SQLException e) {
						// TODO 自动生成的 catch 块
						e.printStackTrace();
					}finally {
						 ps2 = null;
					}
			}
			//释放资源
			try {
				if( rs!= null ) {
					rs.close();
				}
				   
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}finally {
				 rs = null;
			}
			
			try {
				if( ps!= null ) {
					ps.close();
				}
				   
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}finally {
				 ps = null;
			}
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return wb;
		
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

表单数据引入的方法

public HSSFWorkbook scheduleExport(String fdid,HSSFWorkbook wb,String personName) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;// 结果集

		
		SystemConnection tem = new SystemConnection();
		String sql = "select * from ekp_schedule_expo where fd_parent_id='" + fdid + "' ";
		try {
			conn = tem.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
			//将数据录入汇总表
                for(int i=0;i<wb.getNumberOfSheets();i++){
                	if("汇总".equals(wb.getSheetName(i))){
                		HSSFSheet sheet = wb.getSheetAt(i);
                		int hang = sheet.getLastRowNum()+1;
                		HSSFRow row = sheet.createRow(hang);
                		HSSFCell cell = row.createCell(0);
                		cell.setCellValue(rs.getString("fd_wenJuMingChen"));
                		cell = row.createCell(1);
                		cell.setCellValue(rs.getString("fd_danWei"));
                		cell = row.createCell(2);
                		cell.setCellValue(rs.getString("fd_danJia"));
                		cell = row.createCell(3);
                		cell.setCellValue(rs.getString("fd_shenBaoShuLiang"));
                		cell = row.createCell(4);
                		cell.setCellValue(rs.getString("fd_xiaoJi"));
                	}
                }
                //将数据录入最新建的表单中
                for(int j=1;j<wb.getNumberOfSheets();j++){
                	    		if(j==(wb.getNumberOfSheets()-1)){
                				int hang = wb.getSheetAt(j).getLastRowNum()+1;
                				HSSFRow row = wb.getSheetAt(j).createRow(hang);
                        		HSSFCell cell = row.createCell(0);
                        		cell.setCellValue(rs.getString("fd_wenJuMingChen"));
                        		cell = row.createCell(1);
                        		cell.setCellValue(rs.getString("fd_danWei"));
                        		cell = row.createCell(2);
                        		cell.setCellValue(rs.getString("fd_danJia"));
                        		cell = row.createCell(3);
                        		cell.setCellValue(rs.getString("fd_shenBaoShuLiang"));
                        		cell = row.createCell(4);
                        		cell.setCellValue(rs.getString("fd_xiaoJi"));
                	    		}
                }
			}
			//释放资源
			try {
				if( rs!= null ) {
					rs.close();
				}
				   
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}finally {
				 rs = null;
			}
			
			try {
				if( ps!= null ) {
					ps.close();
				}
				   
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}finally {
				 ps = null;
			}
			conn.close();		
			} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}
	```

## 汇总表页签创建方法
```javascript
//生成汇总sheet
	public HSSFWorkbook createSheet(HSSFWorkbook wb,String title){
		HSSFSheet sheet = null;
		//创建一个sheet
		sheet = wb.createSheet("汇总");
		
		
		// 设置表格宽度
		sheet.setColumnWidth(0, 3766);
		sheet.setColumnWidth(1, 3766);
		sheet.setColumnWidth(2, 4766);
		sheet.setColumnWidth(3, 4766);
		sheet.setColumnWidth(4, 3766);

		// 设置字体
		HSSFFont font = wb.createFont();
		font.setFontName("仿宋_GB2312");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		font.setFontHeightInPoints((short) 20);// 字体大小

		// 创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		// 下边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 左边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// 上边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// 右边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		
       //标题的样式
		HSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		Font ztFont = wb.createFont();  
		ztFont.setFontHeightInPoints((short)16);    // 将字体大小设置为18px   
		ztFont.setFontName("宋体");      
		titleStyle.setFont(ztFont); 
		
		//汇总标题
		// 创建第一行
				HSSFRow row1 = sheet.createRow((int) 0); 
				HSSFCell cell = row1.createCell((short) 0);
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
				cell.setCellValue(title);
				cell.setCellStyle(titleStyle);
				
			   //创建第二行
				HSSFRow row2 = sheet.createRow((int) 1); 
				HSSFCell cell2 = row2.createCell((short) 0); //第二行第一列
				//文件名称
				cell2.setCellValue("文件名称");
				cell2.setCellStyle(style);
				
				//单位
				cell2 = row2.createCell((short) 1);
				cell2.setCellValue("单位");
				cell2.setCellStyle(style);
				
				//单价
				cell2 = row2.createCell((short) 2);
				cell2.setCellValue("单价");
				cell2.setCellStyle(style);
				
				//数量
				cell2 = row2.createCell((short) 3);
				cell2.setCellValue("数量");
				cell2.setCellStyle(style);
				
				//小计
				cell2 = row2.createCell((short) 4);
				cell2.setCellValue("小计");
				cell2.setCellStyle(style);
				
		return wb;
	}
	```
## 部门表单的创建方法
```javascript
	//生成部门sheet的方法 传入参数：部门名 申请日期 申请人 流程名字
	public HSSFWorkbook createDeptSheet(HSSFWorkbook wb,String title,String date,String personName,String docSubject){
			HSSFSheet sheet = null;
			String dept = title;
			title=title+"("+docSubject+")";
			sheet = wb.createSheet(title);
			sheet.setColumnWidth(0, 3766);
			sheet.setColumnWidth(1, 3766);
			sheet.setColumnWidth(2, 4766);
			sheet.setColumnWidth(3, 4766);
			sheet.setColumnWidth(4, 3766);

			// 设置字体
			HSSFFont font = wb.createFont();
			font.setFontName("仿宋_GB2312");
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
			font.setFontHeightInPoints((short) 20);// 字体大小

			// 创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = wb.createCellStyle();
			// 下边框
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			// 左边框
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			// 上边框
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			// 右边框
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			
			HSSFRow row1 = sheet.createRow((int) 0); 
			HSSFCell cell = row1.createCell((short) 0);
			cell.setCellValue("部门");
			cell.setCellStyle(style);
			
			cell = row1.createCell((short) 1);
			cell.setCellValue(dept);
			cell.setCellStyle(style);
			
			cell = row1.createCell((short) 3);
			cell.setCellValue("申报所属月份");
			cell.setCellStyle(style);
			
			cell = row1.createCell((short) 4);
			cell.setCellValue(date);
			cell.setCellStyle(style);
			
			
			HSSFRow row2 = sheet.createRow((int) 1); 
			HSSFCell cell2 = row2.createCell((short) 0);
			cell2.setCellValue("申请人");
			cell2.setCellStyle(style);
			
			cell2 = row2.createCell((short) 1);
			cell2.setCellValue(personName);
			cell2.setCellStyle(style);
			
			HSSFRow row4 = sheet.createRow((int) 3); 
			HSSFCell cell3 = row4.createCell((short) 0);
			
			//文件名称
			cell3.setCellValue("文件名称");
			cell3.setCellStyle(style);
			
			//单位
			cell3 = row4.createCell((short) 1);
			cell3.setCellValue("单位");
			cell3.setCellStyle(style);
			
			//单价
			cell3 = row4.createCell((short) 2);
			cell3.setCellValue("单价");
			cell3.setCellStyle(style);
			
			//数量
			cell3 = row4.createCell((short) 3);
			cell3.setCellValue("数量");
			cell3.setCellStyle(style);
			
			//小计
			cell3 = row4.createCell((short) 4);
			cell3.setCellValue("小计");
			cell3.setCellStyle(style);
			
		return wb;
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/268590