前往顾页
以后地位: 主页 > 收集编程 > Jsp实例教程 >

利用servlet完成导出excel 法度代码

时候:2012-05-06 02:07来源:知行网www.zhixing123.cn 编辑:麦田守望者

这是完成导出excel的servlet:ExportAsExcel.java文件
此中还需求的文件是:LinkSqlServer.java和StrFormatter.java
package servelt;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import jxl.*;//重视引入这个jar文件
import jxl.write.*;
import beans.*;//这里存放着连接数据库,字符串编码的转化等等。
public class ExportAsExcel extends HttpServlet {
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws IOException, ServletException
{
HttpSession session = request.getSession(true);
String fileName =(String)session.getAttribute("fileName");//前台需求把这些参数写到session中。
String querySql =(String)session.getAttribute("querySql");
//session.removeAttribute("fileName");
// session.removeAttribute("querySql");
try
{
WritableFont arial15font = newWritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD);
arial15font.setColour(jxl.format.Colour.LIGHT_BLUE);
WritableCellFormat arial15format = new WritableCellFormat(arial15font);
arial15format.setAlignment(jxl.format.Alignment.CENTRE);
arial15format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
arial15format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
File file = new File("output.xls");
WritableWorkbook workbook = Workbook.createWorkbook(file);//建立事情簿
WritableSheet sheet = workbook.createSheet("Sheet 1", 0);//建立sheet
sheet.addCell(new Label(0, 0, fileName, arial15format));
sheet.setName(fileName);
LinkSqlServer linker = new LinkSqlServer();
ResultSet rs = linker.executeQuery(querySql);
if(rs != null) {
WritableFont arial11font = newWritableFont(WritableFont.ARIAL, 11,WritableFont.BOLD);
WritableCellFormat arial11format = new WritableCellFormat(arial11font);
arial11format.setAlignment(jxl.format.Alignment.CENTRE);
arial11format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
arial11format.setBackground(jxl.format.Colour.RED);
int row = 0;
int col = 1;
// 写表头信息
ResultSetMetaDatarsmd = rs.getMetaData();
int []validColumn = new int[rsmd.getColumnCount()];
for(int i=0; i<rsmd.getColumnCount(); i++){
String colName = rsmd.getColumnName(i+1);
if(colName.indexOf("NextIsURL") != -1) {
validColumn[i] = -1;
}
else if(colName.indexOf("ThisIsCheckBox") != -1){
validColumn[i] = -2;
}
else {
sheet.addCell(new Label(row, col, colName,arial11format));
validColumn[i] = getStrLen(colName) + 4;
//sheet.setColumnView(row, validColumn[i]);
row++;
}
}
col++;
if(row>1) {
sheet.mergeCells(0, 0, row-1, 0);
}
WritableFont arial9font = newWritableFont(WritableFont.ARIAL, 9);
WritableCellFormat arial9format = newWritableCellFormat(arial9font);
//arial9format.setAlignment(Alignment.CENTRE);
arial9format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
DateFormat dateFormat = new DateFormat ("yyyy-MM-dd");
WritableCellFormat dateCellFormat = newWritableCellFormat(arial9font, dateFormat);
//dateCellFormat.setAlignment(Alignment.CENTRE);
dateCellFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
DateFormat timeFormat = new DateFormat("hh:mm:ss");

WritableCellFormat timeCellFormat = newWritableCellFormat(arial9font, timeFormat);
//timeCellFormat.setAlignment(Alignment.CENTRE);
timeCellFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
// 循环写所有记录

while (rs.next()) {
row = 0;
for(int i=0; i<rsmd.getColumnCount(); i++){
// 如果是链接列,则不写入文件中
if(validColumn[i] == -1 || validColumn[i] == -2) {
continue;
}
// 读取每列的范例
int columnType = rsmd.getColumnType(i+1);
switch (columnType)
{
case Types.BIT:
case Types.BIGINT:
case Types.BOOLEAN:
case Types.NUMERIC:
case Types.REAL:
case Types.SMALLINT:
case Types.TINYINT:
case Types.DECIMAL:
case Types.FLOAT:
case Types.INTEGER :
float number = rs.getFloat(i+1);
sheet.addCell(new jxl.write.Number(row, col, number,arial9format));
break;
case Types.DATE:

case Types.TIMESTAMP:
Date date = rs.getDate(i+1);
if(date == null) {
sheet.addCell(new jxl.write.Blank(row, col));
}
else {
sheet.addCell(new jxl.write.DateTime(row, col, date,dateCellFormat ));
}
break;
case Types.TIME:

Date time = rs.getDate(i+1);
if(time == null) {
sheet.addCell(new jxl.write.Blank(row, col));
}
else {
sheet.addCell(new jxl.write.DateTime(row, col, time,timeCellFormat ));
}
break;
default:

String str = rs.getString(i+1);
if(str == null) {
sheet.addCell(new jxl.write.Blank(row, col,arial9format));
}
else {
str = str.trim();
sheet.addCell(new Label(row, col, str, arial9format));
int len = getStrLen(str);
if(len > validColumn[i]) {
validColumn[i] = len;
}
}
break;
}
row++;
}
col++;
}
row = 0;

for(int i=0; i<rsmd.getColumnCount(); i++){
if(validColumn[i] > 0){
sheet.setColumnView(row, validColumn[i]);
row++;
}
}
}
linker.closeConStmt();

workbook.write();

workbook.close();
response.setContentType("application/octet-stream");

fileName = new String(fileName.getBytes("gb2312"),"ISO8859_1");
response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xls" + "\"");
int len = (int)file.length();

byte []buf = new byte[len];
FileInputStream fis = new FileInputStream(file);
OutputStream out = response.getOutputStream();
len = fis.read(buf);
out.write(buf, 0, len);
out.flush();
fis.close();
file.delete();
}
catch (Exception e) {
System.out.println("[Info: ] User canceled - " +e.getMessage());
}
}


public int getStrLen(String str) {
if(str == null) {
return 0;
}
byte []buf = str.getBytes();
return buf.length;
}


public void doPost(HttpServletRequest request,HttpServletResponse response)
throws IOException, ServletException
{
doGet(request, response);
}
}
=======
前台的利用体例:
<%@ page language="java"contentType="text/html; charset=GB18030"
pageEncoding="GB18030"%>
<html>
<head>
</head>
<body>
<%
session.setAttribute("querySql", "selectsname,idcard,phonenum,worktime from StudentInfo");
session.setAttribute("fileName","hope");
session.setAttribute("title","hope");
%>
<form action="ExportAsExcel"method="post" name="form">
<input type="submit" name="ok"value="ok">
</form>
</body>
</html>

顶一下
(0)
0%
踩一下
(0)
0%
------分开线----------------------------
标签(Tag):Java JAVA实例教程 JAVA根本教程 Java源代码 Java技能
------分开线----------------------------
颁发评论
请自发遵循互联网相关的政策法规,严禁公布色情、暴力、革命的谈吐。
评价:
神色:
考证码:点击我更换图片
猜你感兴趣