Excel报表导出
废话少说，先来看看需要实现的报表格式：
看看我的源代码：
1 package com.cattsoft.mdsp.sm.dom;
2
3 import java.io.ByteArrayOutputStream;
4 import java.text.SimpleDateFormat;
5 import java.util.Calendar;
6 import java.util.List;
7
8 import javax.servlet.ServletOutputStream;
9 import javax.servlet.http.HttpServletResponse;
10
11 import jxl.Workbook;
12 import jxl.format.Alignment;
13 import jxl.format.Border;
14 import jxl.format.BorderLineStyle;
15 import jxl.format.Colour;
16 import jxl.format.UnderlineStyle;
17 import jxl.format.VerticalAlignment;
18 import jxl.write.Label;
19 import jxl.write.WritableCellFormat;
20 import jxl.write.WritableFont;
21 import jxl.write.WritableSheet;
22 import jxl.write.WritableWorkbook;
23
24 import org.apache.log4j.Logger;
25 import org.springframework.beans.BeanWrapper;
26 import org.springframework.beans.BeanWrapperImpl;
27
28 import com.cattsoft.mdsp.data.DownExcelUseStaticsMVO;
29
30 /**
31 * 下载excel通用类
32 * @author sunny
33 *
34 */
35 public class DownLoadByExcel {
36
37 private final static String CONTENT_TYPE = "application/vnd.ms-excel;charset=gb2312";
38
39 private final static String HEADER = "CONTENT-DISPOSITION";
40
41 private final static String FILENAME = "attachment; filename=downloadexcelfile.xls";
42
43 private final static Logger logger =Logger.getLogger(DownLoadByExcel.class);
44 /**
45 * 通用下载Excel方法.
46 * @param response
47 * @param columns 下载的Excel文件头标题
48 * @param fields 下载List 中包含的Bean中要显示的字段
49 * @param list 下载内容集合
50 * @param title 报表名称
51 * @param dateTime 统计时间段
52 * @param company_person 统计公司与统计员
53 */
54 public static void download(final HttpServletResponse response,
55 final String[] columns, final String[] fields,
56 final List<?> list, String title,
57 String dateTime, String company_person) {
58 ByteArrayOutputStream baos = new ByteArrayOutputStream();
59 response.setContentType(CONTENT_TYPE);// 定义输出类型
60
61 //获取公司与登录员
62 String[] strs = company_person.split(",");
63
64 try{
65 WritableWorkbook book = Workbook.createWorkbook(baos);
66 WritableSheet sheet = book.createSheet(title, 0);// 创建Excel工作表//"0"表示代表创建第一个sheet
67
68 //设置logo文件样式
69 WritableFont font_logo = new WritableFont(WritableFont.createFont("黑体"), 16, WritableFont.BOLD);
70 WritableCellFormat format_logo = new WritableCellFormat(font_logo);
71 format_logo.setAlignment(Alignment.LEFT);
72 format_logo.setVerticalAlignment(VerticalAlignment.CENTRE);
73
74 //设置日期的样式
75 WritableFont font_date = new WritableFont(WritableFont.createFont("宋体"), 14,WritableFont.NO_BOLD);
76 WritableCellFormat format_date = new WritableCellFormat(font_date);
77 format_date.setAlignment(Alignment.LEFT);
78 format_date.setVerticalAlignment(VerticalAlignment.CENTRE);
79
80 //设置一般标题以及内容的样式
81 WritableFont font_title = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD);
82 WritableCellFormat format_title = new WritableCellFormat(font_title);
83 format_title.setBorder(Border.ALL, BorderLineStyle.THIN);
84 format_title.setAlignment(Alignment.CENTRE);
85 format_title.setWrap(true);//该方法的作用是设置自动换行
86 format_title.setVerticalAlignment(VerticalAlignment.CENTRE);
87
88 //设置特殊标题以及内容的样式
89 WritableFont font_content = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD);
90 WritableCellFormat format_content = new WritableCellFormat(font_content);
91 format_content.setBackground(Colour.GREEN);//设置背景颜色
92 format_content.setBorder(Border.ALL, BorderLineStyle.THIN);
93 format_content.setAlignment(Alignment.CENTRE);//该方法的作用是设置表格的对齐方式
94 format_content.setVerticalAlignment(VerticalAlignment.CENTRE);//该方法的作用是设置单元格的垂直对齐方式
95 format_content.setWrap(true);//该方法的作用是设置自动换行
96
97 //设置备注的样式
98 //字体、字体的大小、字体的粗细、是否斜体、下划线、颜色
99 WritableFont font_remarks = new WritableFont(WritableFont.createFont("宋体"), 10,
100 WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
101 WritableCellFormat format_remarks = new WritableCellFormat(font_remarks);
102 format_remarks.setAlignment(Alignment.LEFT);//该方法的作用是设置表格的对齐方式,即文本对齐方式
103 format_remarks.setVerticalAlignment(VerticalAlignment.CENTRE);//该方法的作用是设置单元格的垂直对齐方式
104
105 //添加标题
106 Label logotitle = new Label(0,0, title, format_logo);//Label(列号,行号 ,内容,格式 )
107 sheet.addCell(logotitle);
108 sheet.mergeCells(0,0,columns.length-7,0);// 合并第一列第一行最后一列第一行的所有单元格
109
110 //添加日期
111 //日期
112 sheet.mergeCells(0, 1, 9, 1);//合并列
113 String dates1 = "统计周期："+dateTime;
114 Label date1 = new Label(0,1,dates1, format_date);
115 sheet.addCell(date1);
116
117 sheet.mergeCells(10, 1, columns.length-7, 1);
118 String dates2 = "统计单位："+strs[0];
119 Label date2 = new Label(10,1,dates2, format_date);
120 sheet.addCell(date2);
121
122 /*
123 * 添加列标题
124 */
125 //第一列
126 sheet.mergeCells(0, 2, 0, 3);
127 Label label1 = new Label(0, 2, columns[0],format_title);//Label(列号,行号 ,内容,格式 )
128 sheet.setColumnView(0, 15);//（列号、宽度）
129 sheet.addCell(label1);
130 //第二列
131 sheet.mergeCells(1, 2, 1, 3);
132 Label label2 = new Label(1, 2, columns[1],format_title);//Label(列号,行号 ,内容,格式 )
133 sheet.setColumnView(1, 15);//（列号、宽度）
134 sheet.addCell(label2);
135 //第三列第三行===报价
136 sheet.mergeCells(2, 2, 5, 2);//第3列第3行~第6列第3行
137 Label label33 = new Label(2, 2, columns[2],format_title);//Label(列号,行号 ,内容,格式 )
138 sheet.addCell(label33);
139 //第四列第三行===承保
140 sheet.mergeCells(6, 2, 8, 2);//第7列第三行~第9列第3行
141 Label label43 = new Label(6, 2, columns[3], format_title);
142 sheet.addCell(label43);
143 //第五列第三行===新保
144 sheet.mergeCells(9, 2, 10,2);//第10列第三行~第11列第3行
145 Label label53 = new Label(9, 2, columns[4], format_content);
146 sheet.addCell(label53);
147 //第六列第三行===续保
148 sheet.mergeCells(11, 2, 12,2);//第12列第三行~第13列第3行
149 Label label63 = new Label(11, 2, columns[5], format_content);
150 sheet.addCell(label63);
151 //第七列第三行===销售管理
152 sheet.mergeCells(13, 2, 16, 2);//第14列第三行~第17列第3行
153 Label label173 = new Label(13, 2, columns[6],format_title);
154 sheet.addCell(label173);
155 //第八列第三行===辅助功能
156 sheet.mergeCells(17, 2, 18, 2);//第18列第3行~第19列第3行
157 Label label183 = new Label(17, 2, columns[7],format_title);
158 sheet.addCell(label183);
159
160 /*
161 * 子标题
162 */
163 int jj=2;
164 for(int i=8; i<columns.length; i++){
165 Label label = new Label(jj, 3, columns[i], format_title);//Label(列号,行号 ,内容,格式 )
166 sheet.setColumnView(jj, 7);//（列号、宽度）
167 sheet.addCell(label);
168 jj++;
169 }
170
282 int row = 4;
283 if(list!=null && list.size()>0){
284 for(int i = 0;i < list.size()-1;i ++){
285
286 DownExcelUseStaticsMVO object = (DownExcelUseStaticsMVO) list.get(i);
287
288 Label lbl = null;
289 for(int j = 0;j < fields.length;j ++){
290 BeanWrapper wrapper = new BeanWrapperImpl(object);
291 Object content = wrapper.getPropertyValue(fields[j]);
292 lbl = new Label(j, row, String.valueOf(content), format_title);
293 sheet.addCell(lbl);
294 }
295 row ++;
296 }
297 }
298
299 //汇总
300 sheet.mergeCells(0,row, 1, row);//合并列
301 Label stat = new Label(0,row,"汇总",format_title);
302 sheet.addCell(stat);
303 DownExcelUseStaticsMVO object = (DownExcelUseStaticsMVO) list.get(list.size()-1);
304
305 Label lbl = null;
306 for(int j = 2;j < fields.length;j ++){
307 BeanWrapper wrapper = new BeanWrapperImpl(object);
308 Object content = wrapper.getPropertyValue(fields[j]);
309 lbl = new Label(j, row, String.valueOf(content), format_title);
310 sheet.addCell(lbl);
311 }
312 row ++;
313
314 //获取当前日期
315 Calendar c = Calendar.getInstance();
316 SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
317 String today = s.format(c.getTime());
318
319 sheet.mergeCells(0, row, 9, row);//合并列
320 String lastlabel1 = "制表人："+strs[1];
321 Label personlabel1 = new Label(0,row,lastlabel1);
322 sheet.addCell(personlabel1);
323
324 sheet.mergeCells(10, row, columns.length-7, row);
325 String lastlabel2 = "制表日期："+today;
326 Label personlabel2 = new Label(10,row,lastlabel2);
327 sheet.addCell(personlabel2);
328
329 sheet.mergeCells(0, row+1, columns.length-7, row+1);//合并列
330 String str="";
331 Label beizhu = new Label(0,row+1,str,format_remarks);
332 sheet.addCell(beizhu);
333
334 book.write();
335 book.close();
336 ServletOutputStream out = response.getOutputStream();
337 response.setHeader(HEADER,FILENAME);//设定输出文件头
338 response.setContentLength(baos.size());
339 baos.writeTo(out);//弹出文档
340 out.flush();
341 out.close();
342 baos.close();
343 }catch(Exception ex){
344 logger.error("download list with excel was error:",ex);
345 }
346 }
347 }
在做这个报表导出时，那个“周初日期”与“周末日期”当时我分了两种情况讨论：
注：
日期格式：
Timestamp beginTime = form.getBeginTime();// 开始时间
Timestamp endTime = form.getEndTime();// 结束时间
bDate、eDate也是Timestamp格式，bTime、eTime为String格式
一、按每七天算一周进行分配，我的做法是：
①根据你所输入的两个日期来求出它们的天数差：
long dif = (endTime.getTime()/1000-beginTime.getTime()/1000)/(24*60*60); // 相差天数
②求出这相差天数中的周数与余数
w=(int) (dif/7);//周数
ww=(int) (dif%7);//余数
③算出整个日期段
bDate = beginTime;
for(int i=0; i<w;i++){
eDate = new Timestamp((bDate.getTime()/1000+24*60*60*7)*1000);//从你选定的初始日期开始，每隔七天算做一周，即报表中的“周末日期”
weekList.add(eDate);//将尾值填入list中
bDate = eDate;//将上一轮的“周末日期”算做新一轮的“周初日期”
}
if(ww>0){
eDate = new Timestamp((bDate.getTime()/1000+24*60*60*ww)*1000);//根据余数算出最后一轮的日期
weekList.add(eDate);//将尾值填入list中
}
二、按正常的周期(即现实生活中的周期)
①先算出你所选择的两个日期的是星期几
Calendar calendar = Calendar.getInstance();
calendar.setTime(beginTime);
int beginWeek = calendar.get(Calendar.DAY_OF_WEEK)-1; // 得出开始日期是周几
calendar.setTime(endTime);
int endWeek = calendar.get(Calendar.DAY_OF_WEEK)-1;// 得出结束日期是周几
②算出开始日期与结束日期的相差天数
bTime = beginTime.toString().substring(0,10);
eTime = endTime.toString().substring(0, 10);
long dif = (endTime.getTime()/1000-beginTime.getTime()/1000)/(24*60*60); // 相差天数
③算出日期间的周数
int i=6-beginWeek;//因为算出的周数是从0(周日)开始的，所以，用“i”表示开始日期里周末(周六)还有几天
bDate = beginTime;//把开始日期作为第一轮的“周初日期”
if (dif <= 6 && endWeek>beginWeek) {
weekList.add(endTime);
} else {
// 第一周的数据
if (beginWeek == 6) {
weekList.add(bDate);// 周末日期==周初日期
} else {
eDate = new Timestamp(
(bDate.getTime() / 1000 + 24 * 60 * 60 * (6 - beginWeek)) * 1000);
weekList.add(eDate);// 将周末日期填入list中
}
// 获取第二周的第一天
bDate = new Timestamp(
(bDate.getTime() / 1000 + 24 * 60 * 60 * (7 - beginWeek)) * 1000);
int j = (int) (dif - endWeek - 1);
while (i < j) {
eDate = new Timestamp(
(bDate.getTime() / 1000 + 24 * 60 * 60 * 6) * 1000);// 本周的周末日期
weekList.add(eDate);// 将尾值填入list中
bDate = new Timestamp(
(bDate.getTime() / 1000 + 24 * 60 * 60 * 7) * 1000);// 下周的周初日期
i += 7;
}
weekList.add(endTime);// 最后一天
}
下面，分享一下我的SQL：
1 SELECT
2 c.module_name,
3 count(*) num
4 FROM
5 client_use_statistics c,
6 staff s
7 WHERE
8 1 = 1
9 and c.module_name = '110101100200'
10 and c.login_name = s.login_name
11 and c.login_name in
12 (
13 SELECT
14 s.login_name
15 FROM
16 (
17 SELECT
18 b.staff_id staff_id
19 FROM
20 (
21 SELECT
22 dept_id
23 FROM
24 org_dept connect by prior dept_id = parent_dept_id start
25 WITH
26 dept_id= '44030000'
27 )
28 a,
29 dept_staff b
30 WHERE
31 a.dept_id= b.dept_id
32 )
33 c,
34 staff s
35 WHERE
36 c.staff_id= s.staff_id
37 )
38 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
39 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
40 GROUP BY
41 c.module_name
42 UNION ALL
43 SELECT
44 c.module_name,
45 count(*) num
46 FROM
47 client_use_statistics c,
48 staff s
49 WHERE
50 1 = 1
51 and c.module_name = '110101100202'
52 and c.login_name = s.login_name
53 and c.login_name in
54 (
55 SELECT
56 s.login_name
57 FROM
58 (
59 SELECT
60 b.staff_id staff_id
61 FROM
62 (
63 SELECT
64 dept_id
65 FROM
66 org_dept connect by prior dept_id = parent_dept_id start
67 WITH
68 dept_id= '44030000'
69 )
70 a,
71 dept_staff b
72 WHERE
73 a.dept_id= b.dept_id
74 )
75 c,
76 staff s
77 WHERE
78 c.staff_id= s.staff_id
79 )
80 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
81 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
82 GROUP BY
83 c.module_name
84 UNION ALL
85 SELECT
86 c.module_name,
87 count(*) num
88 FROM
89 client_use_statistics c,
90 staff s
91 WHERE
92 1 = 1
93 and c.module_name = '110101100301'
94 and c.login_name = s.login_name
95 and c.login_name in
96 (
97 SELECT
98 s.login_name
99 FROM
100 (
101 SELECT
102 b.staff_id staff_id
103 FROM
104 (
105 SELECT
106 dept_id
107 FROM
108 org_dept connect by prior dept_id = parent_dept_id start
109 WITH
110 dept_id= '44030000'
111 )
112 a,
113 dept_staff b
114 WHERE
115 a.dept_id= b.dept_id
116 )
117 c,
118 staff s
119 WHERE
120 c.staff_id= s.staff_id
121 )
122 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
123 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
124 GROUP BY
125 c.module_name
126 UNION ALL
127 SELECT
128 c.module_name,
129 count(*) num
130 FROM
131 client_use_statistics c,
132 staff s
133 WHERE
134 1 = 1
135 and c.module_name = '110101100500'
136 and c.login_name = s.login_name
137 and c.login_name in
138 (
139 SELECT
140 s.login_name
141 FROM
142 (
143 SELECT
144 b.staff_id staff_id
145 FROM
146 (
147 SELECT
148 dept_id
149 FROM
150 org_dept connect by prior dept_id = parent_dept_id start
151 WITH
152 dept_id= '44030000'
153 )
154 a,
155 dept_staff b
156 WHERE
157 a.dept_id= b.dept_id
158 )
159 c,
160 staff s
161 WHERE
162 c.staff_id= s.staff_id
163 )
164 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
165 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
166 GROUP BY
167 c.module_name
168 UNION ALL
169 SELECT
170 c.module_name,
171 count(*) num
172 FROM
173 client_use_statistics c,
174 staff s
175 WHERE
176 1 = 1
177 and c.module_name = '110101100700'
178 and c.login_name = s.login_name
179 and c.login_name in
180 (
181 SELECT
182 s.login_name
183 FROM
184 (
185 SELECT
186 b.staff_id staff_id
187 FROM
188 (
189 SELECT
190 dept_id
191 FROM
192 org_dept connect by prior dept_id = parent_dept_id start
193 WITH
194 dept_id= '44030000'
195 )
196 a,
197 dept_staff b
198 WHERE
199 a.dept_id= b.dept_id
200 )
201 c,
202 staff s
203 WHERE
204 c.staff_id= s.staff_id
205 )
206 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
207 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
208 GROUP BY
209 c.module_name
210 UNION ALL
211 SELECT
212 c.module_name,
213 count(*) num
214 FROM
215 client_use_statistics c,
216 staff s
217 WHERE
218 1 = 1
219 and c.module_name = '110101100900'
220 and c.login_name = s.login_name
221 and c.login_name in
222 (
223 SELECT
224 s.login_name
225 FROM
226 (
227 SELECT
228 b.staff_id staff_id
229 FROM
230 (
231 SELECT
232 dept_id
233 FROM
234 org_dept connect by prior dept_id = parent_dept_id start
235 WITH
236 dept_id= '44030000'
237 )
238 a,
239 dept_staff b
240 WHERE
241 a.dept_id= b.dept_id
242 )
243 c,
244 staff s
245 WHERE
246 c.staff_id= s.staff_id
247 )
248 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
249 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
250 GROUP BY
251 c.module_name
252 UNION ALL
253 SELECT
254 c.module_name,
255 count(*) num
256 FROM
257 client_use_statistics c,
258 staff s
259 WHERE
260 1 = 1
261 and c.module_name = '110101100800'
262 and c.login_name = s.login_name
263 and c.login_name in
264 (
265 SELECT
266 s.login_name
267 FROM
268 (
269 SELECT
270 b.staff_id staff_id
271 FROM
272 (
273 SELECT
274 dept_id
275 FROM
276 org_dept connect by prior dept_id = parent_dept_id start
277 WITH
278 dept_id= '44030000'
279 )
280 a,
281 dept_staff b
282 WHERE
283 a.dept_id= b.dept_id
284 )
285 c,
286 staff s
287 WHERE
288 c.staff_id= s.staff_id
289 )
290 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
291 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
292 GROUP BY
293 c.module_name
294 UNION ALL
295 SELECT
296 c.module_name,
297 count(*) num
298 FROM
299 client_use_statistics c,
300 staff s
301 WHERE
302 1 = 1
303 and c.module_name = '110101101000'
304 and c.login_name = s.login_name
305 and c.login_name in
306 (
307 SELECT
308 s.login_name
309 FROM
310 (
311 SELECT
312 b.staff_id staff_id
313 FROM
314 (
315 SELECT
316 dept_id
317 FROM
318 org_dept connect by prior dept_id = parent_dept_id start
319 WITH
320 dept_id= '44030000'
321 )
322 a,
323 dept_staff b
324 WHERE
325 a.dept_id= b.dept_id
326 )
327 c,
328 staff s
329 WHERE
330 c.staff_id= s.staff_id
331 )
332 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
333 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
334 GROUP BY
335 c.module_name
336 UNION ALL
337 SELECT
338 c.module_name,
339 count(*) num
340 FROM
341 client_use_statistics c,
342 staff s
343 WHERE
344 1 = 1
345 and c.module_name = '110101101100'
346 and c.login_name = s.login_name
347 and c.login_name in
348 (
349 SELECT
350 s.login_name
351 FROM
352 (
353 SELECT
354 b.staff_id staff_id
355 FROM
356 (
357 SELECT
358 dept_id
359 FROM
360 org_dept connect by prior dept_id = parent_dept_id start
361 WITH
362 dept_id= '44030000'
363 )
364 a,
365 dept_staff b
366 WHERE
367 a.dept_id= b.dept_id
368 )
369 c,
370 staff s
371 WHERE
372 c.staff_id= s.staff_id
373 )
374 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
375 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
376 GROUP BY
377 c.module_name
378 UNION ALL
379 SELECT
380 c.module_name,
381 count(*) num
382 FROM
383 client_use_statistics c,
384 staff s
385 WHERE
386 1 = 1
387 and c.module_name = '110101100600'
388 and c.login_name = s.login_name
389 and c.login_name in
390 (
391 SELECT
392 s.login_name
393 FROM
394 (
395 SELECT
396 b.staff_id staff_id
397 FROM
398 (
399 SELECT
400 dept_id
401 FROM
402 org_dept connect by prior dept_id = parent_dept_id start
403 WITH
404 dept_id= '44030000'
405 )
406 a,
407 dept_staff b
408 WHERE
409 a.dept_id= b.dept_id
410 )
411 c,
412 staff s
413 WHERE
414 c.staff_id= s.staff_id
415 )
416 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
417 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
418 GROUP BY
419 c.module_name
420 UNION ALL
421 SELECT
422 c.module_name,
423 count(*) num
424 FROM
425 client_use_statistics c,
426 staff s
427 WHERE
428 1 = 1
429 and c.module_name = '110101100400'
430 and c.login_name = s.login_name
431 and c.login_name in
432 (
433 SELECT
434 s.login_name
435 FROM
436 (
437 SELECT
438 b.staff_id staff_id
439 FROM
440 (
441 SELECT
442 dept_id
443 FROM
444 org_dept connect by prior dept_id = parent_dept_id start
445 WITH
446 dept_id= '44030000'
447 )
448 a,
449 dept_staff b
450 WHERE
451 a.dept_id= b.dept_id
452 )
453 c,
454 staff s
455 WHERE
456 c.staff_id= s.staff_id
457 )
458 and c.enter_time BETWEEN trunc(to_date('2011-10-30 14:24:35','YYYY-MM-DD HH24:MI:ss') )
459 and trunc(to_date('2011-10-31 14:24:38','YYYY-MM-DD HH24:MI:ss') )+1
460 GROUP BY
461 c.module_name
另外，再分享一下如果把左边的“周初日期”与“周末日期”换为“用户名”和“姓名”的SQL：
1 SELECT
2 r.login_name login_name,
3 l.name name,
4 r.baof baof,
5 r.shis shis,
6 r.zhuantb zhuantb,
7 r.toubd toubd,
8 r.xub xub,
9 r.yej yej,
10 r.lip lip,
11 r.keh keh,
12 r.chengb chengb,
13 r.yanc yanc,
14 r.xinx xinx
15 FROM
16 staff l,
17 (
18 SELECT
19 t.login_name,
20 sum
21 (
22 CASE
23 WHEN t.module_name= '110101100200'
24 THEN 1
25 ELSE 0
26 END
27 )
28 baof,
29 sum
30 (
31 CASE
32 WHEN t.module_name= '110101100202'
33 THEN 1
34 ELSE 0
35 END
36 )
37 shis,
38 sum
39 (
40 CASE
41 WHEN t.module_name= '110101100301'
42 THEN 1
43 ELSE 0
44 END
45 )
46 zhuantb,
47 sum
48 (
49 CASE
50 WHEN t.module_name= '110101100500'
51 THEN 1
52 ELSE 0
53 END
54 )
55 toubd,
56 sum
57 (
58 CASE
59 WHEN t.module_name= '110101100700'
60 THEN 1
61 ELSE 0
62 END
63 )
64 xub,
65 sum
66 (
67 CASE
68 WHEN t.module_name= '110101100900'
69 THEN 1
70 ELSE 0
71 END
72 )
73 yej,
74 sum
75 (
76 CASE
77 WHEN t.module_name= '110101100800'
78 THEN 1
79 ELSE 0
80 END
81 )
82 lip,
83 sum
84 (
85 CASE
86 WHEN t.module_name= '110101101000'
87 THEN 1
88 ELSE 0
89 END
90 )
91 keh,
92 sum
93 (
94 CASE
95 WHEN t.module_name= '110101101100'
96 THEN 1
97 ELSE 0
98 END
99 )
100 chengb,
101 sum
102 (
103 CASE
104 WHEN t.module_name= '110101100600'
105 THEN 1
106 ELSE 0
107 END
108 )
109 yanc,
110 sum
111 (
112 CASE
113 WHEN t.module_name= '110101100400'
114 THEN 1
115 ELSE 0
116 END
117 )
118 xinx
119 FROM
120 client_use_statistics t
121 WHERE
122 1 = 1
123 and login_name in
124 (
125 SELECT
126 s.login_name
127 FROM
128 (
129 SELECT
130 b.staff_id staff_id
131 FROM
132 (
133 SELECT
134 dept_id
135 FROM
136 org_dept connect by prior dept_id = parent_dept_id start
137 WITH
138 dept_id= '44030000'
139 )
140 a,
141 dept_staff b
142 WHERE
143 a.dept_id= b.dept_id
144 )
145 c,
146 staff s
147 WHERE
148 c.staff_id= s.staff_id
149 )
150 and enter_time BETWEEN trunc(to_date('2011-07-01 13:12:33','YYYY-MM-DD HH24:MI:ss') )
151 and trunc(to_date('2011-11-30 13:12:34','YYYY-MM-DD HH24:MI:ss') )+1
152 GROUP BY
153 t.login_name
154 )
155 r
156 WHERE
157 l.login_name = r.login_name
上面的SQL语句中，START WITH ... CONNECT BY PRIOR...是重点，可以去：http://www.cnblogs.com/loveJava/archive/2011/11/04/2236200.html进行查看！
至此，上面的报表实现功能就已完成！
浙公网安备 33010602011771号