📄 outputexcel.jsp
字号:
<%@ page language="java" import="java.sql.*" import="jxl.*" import="java.io.*" pageEncoding="utf-8"%>
<jsp:directive.page import="jxl.read.biff.BiffException"/>
<jsp:directive.page import="jxl.write.*"/>
<jsp:directive.page import="jxl.format.*"/>
<%@ page contentType="text/html; charset=GBK" %>
<html>
<head>
<title>My JSP 'test_excel.jsp' starting page</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" >
</head>
<jsp:useBean id="db" scope="session" class="news.sql_data"/>
<body>
<%
File tempFile=new File("E://1.xls");
WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
WritableSheet sheet = workbook.createSheet("news_table", 2);
//一些临时变量，用于写到excel中
Label l=null;
jxl.write.Number n=null;
jxl.write.DateTime d=null;
//预定义的一些字体和格式，同一个Excel中最好不要有太多格式
WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLUE);
WritableCellFormat headerFormat = new WritableCellFormat (headerFont);
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
WritableCellFormat titleFormat = new WritableCellFormat (titleFont);
WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat detFormat = new WritableCellFormat (detFont);
NumberFormat nf=new NumberFormat("0"); //用于Number的格式
WritableCellFormat priceFormat = new WritableCellFormat (detFont, nf);
DateFormat df=new DateFormat("yyyy-MM-dd");//用于日期的
WritableCellFormat dateFormat = new WritableCellFormat (detFont, df);
//add Title
int column=0;
l=new Label(column++, 0, "序号", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "中文姓名", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "密码", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "曾用外文名", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "性别", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "出生年月", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "籍贯", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "学历", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "民族", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "办公电话", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "移动电话", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "住宅电话", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "证件种类", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "证件号码", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "电子邮箱", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "政治面貌", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "出境时间", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "签证号码", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "所学专业", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "留学国家", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "所在单位", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "留学类别", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "家庭地址", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "单位地址", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "毕业学校", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "备注", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "照片", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "录入时间", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "录入人员代码", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "导入人", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "增加时间", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "修改时间", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "出生年月", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "留学国家（原）", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "预留3", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "预留4", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "预留5", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "注释", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "数据来源", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "中文姓名_拼音", titleFormat);
sheet.addCell(l);
l=new Label(column++, 0, "曾用外文名_拼音", titleFormat);
sheet.addCell(l);
int i=0;
column=0;
String sql="select * from news_table";
ResultSet rs=db.executeQuery(sql);
while(rs.next()){
l=new Label(column++, i+1, rs.getString("id"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("cnname"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("password"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("oldenname"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("sex"), detFormat);
sheet.addCell(l);
n=new jxl.write.Number(column++, i+1, rs.getInt("birthday"), priceFormat);
sheet.addCell(n);
l=new Label(column++, i+1, rs.getString("nativeplace"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("edu"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("nation"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("officetel"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("mobile"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("hometel"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("certificatetype"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("certificateno"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("email"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("politicalstatus"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("aboardtime"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("visano"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("studyabroad_country"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("company"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("studyabroad_type"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("address"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("company_addr"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("finishschool"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("remark"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("photo"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("inputtime"), detFormat);
sheet.addCell(l);
//d=new jxl.write.DateTime(column++, i+1, rs.getDate("inputtime"), dateFormat);
//sheet.addCell(d);
l=new Label(column++, i+1, rs.getString("inputmancode"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("impman"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("addtime"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("edittime"), detFormat);
sheet.addCell(l);
//d=new jxl.write.DateTime(column++, i+1, rs.getDate("addtime"), dateFormat);
// sheet.addCell(d);
// d=new jxl.write.DateTime(column++, i+1, rs.getDate("edittime"), dateFormat);
// sheet.addCell(d);
l=new Label(column++, i+1, rs.getString("preserve1"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("preserve2"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("preserve3"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("preserve4"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("preserve5"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("comments"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("datasrc"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("cnname_pinyin"), detFormat);
sheet.addCell(l);
l=new Label(column++, i+1, rs.getString("oldenname_pinyin"), detFormat);
sheet.addCell(l);
i++;
column=0;
}
//设置列的宽度
workbook.write();
workbook.close();
%>
<center>已经将news_table表保存到E://1.xls</center>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -