如题,代码如下:[code="java"]package test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import javax.servlet.*;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class TestExcel {
private String driverClass = "net.sourceforge.jtds.jdbc.Driver";
private String url ="jdbc:jtds:sqlserver:/localhost:1433/demo";
private String user = "sa";
private String password = "";
private Connection connection;
public void exportClassroom(OutputStream os) {
try {
WritableWorkbook wbook = Workbook.createWorkbook(os); //建立excel文件
WritableSheet wsheet = wbook.createSheet("报警记录表", 0); //工作表名称
//设置Excel字体
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,
WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(wfont);
String[] title = { "报警记录编号", "报警时间", "报警设备", "报警设备编号","报警事件名称","报警编号"};
//设置Excel表头
for (int i = 0; i < title.length; i++) {
Label excelTitle = new Label(i, 0, title[i], titleFormat);
wsheet.addCell(excelTitle);
}
int c = 1; //用于循环时Excel的行号
Connection con=openConnection();
Statement st=con.createStatement();
String sql="select * from jlbjsj where BJJLSJ between '2010-09-01 08:19:25' and '2010-12-02 08:19:25' and BJSBMC like '%'";
ResultSet rs=st.executeQuery(sql); //这个是从数据库中取得要导出的数据
while (rs.next()){
Label content1 = new Label(0, c, (String)rs.getString("BJJLBH"));
Label content2 = new Label(1, c, (String)rs.getString("BJJLSJ"));
Label content3 = new Label(2, c, (String)rs.getString("BJSBMC"));
Label content4 = new Label(3, c, (String)rs.getString("BJSBBH"));
Label content5 = new Label(4, c, (String)rs.getString("BJBLMC"));
Label content6 = new Label(5, c, (String)rs.getString("BJBLBH"));
//Label content7 = new Label(3, c, (String)rs.getString("BJSBBH"));
wsheet.addCell(content1);
wsheet.addCell(content2);
wsheet.addCell(content3);
wsheet.addCell(content4);
wsheet.addCell(content5);
wsheet.addCell(content6);
c++;
}
wbook.write(); //写入文件
wbook.close();
os.close();
System.out.println("导入成功!");
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection openConnection() throws SQLException {
try {
Class.forName(driverClass).newInstance();
connection = DriverManager.getConnection(url, user, password);
return connection;
} catch (Exception e) {
throw new SQLException(e.getMessage());
}
}
public void closeConnection() {
try {
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args){
TestExcel te=new TestExcel();
File f=new File("c:\\kk.xls");
try{
f.createNewFile();
OutputStream os=new FileOutputStream(f);
te.exportClassroom(os);
}catch(Exception e){
e.printStackTrace();
}
}
}
[/code]