上一篇 有人反映檔案輸出時會有點問題
若對上一篇有興趣請參照 >>>
若對ArrayList 不熟請參照 >>>
紅色部分為關鍵!!
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"
import="java.io.*,jxl.*,jxl.write.*,jxl.format.*,jxl.write.biff.RowsExceededException,java.util.*,java.sql.*,java.util.*, java.text.* "%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
</head>
<body>
<jsp:useBean id="DB" scope="session" class="Database.datebase"></jsp:useBean>
<%!
private void putRow(WritableSheet ws, int rowNum, ArrayList cells) throws RowsExceededException, WriteException {
for(int j=0; j<cells.size(); j++) {
Label cell = new Label(j, rowNum, ""+cells.get(j));
ws.addCell(cell);
}
}
%>
<%
java.util.Date date = new java.util.Date();//
SimpleDateFormat formatter2 = new SimpleDateFormat("yyyy/MM/dd");//日期
//所需宣告----
response.setCharacterEncoding("UTF-8");//response設定字元為UTF-8
request.setCharacterEncoding("UTF-8");//request設定字元為UTF-8
String date_serch="2015/06";
if(request.getParameter("da")!=null)date_serch=request.getParameter("da");
String SQL=".....";
ResultSet rs=DB.getData(SQL, 1, 2);
//System.out.println(SQL);
out.clear();
out = pageContext.pushBody();
response.reset();
response.setHeader("Content-disposition","attachment; filename=overtime_"+date_serch.replace("/", "_")+".xls");
OutputStream os=response.getOutputStream();
os.flush();
jxl.Workbook rw = jxl.Workbook.getWorkbook(new File("D:\\Download\\jxl\\overtime.xls"));// D:\\.... 為你原本檔案路徑
WritableWorkbook workbook = Workbook.createWorkbook(os,rw);
WritableSheet sheet = workbook.getSheet(0);
sheet.setName("加班單列表-"+date_serch.replace("/", "_"));
//WritableSheet sheet2 = workbook.getSheet(1);//第二頁
//sheet2.setName("總覽表-"+date_serch.replace("/", "_"));
if(!rs.next()){
ArrayList list = new ArrayList();
list.add("");
list.add("");
list.add("");
list.add("本月無加班資訊");
putRow(sheet, 1, list);
}else{
rs.beforeFirst();
int rowNum = 1;
String old_emp_name="";
while(rs.next()){
String ov_no=rs.getString("ov_no");
String emp_name=rs.getString("emp_name");
String emp_depno=rs.getString("emp_depno");
String s_time=rs.getString("s_time");
String e_time=rs.getString("e_time");
String mins=rs.getString("mins");
if(rowNum>=2){
if(!old_emp_name.equals(emp_name)){
ArrayList list2= new ArrayList();
list2.add("");
rowNum++;
putRow(sheet, rowNum, list2);
}
}
ArrayList list = new ArrayList();
list.add(ov_no);
list.add(DB.getDepname(emp_depno));
list.add(emp_name);
list.add(s_time);
list.add(e_time);
list.add(mins);
putRow(sheet, rowNum, list);
rowNum++;
old_emp_name=emp_name;
}
}
workbook.write();
workbook.close();
os.flush();
os.close();
%>
</body>
</html>