(www.aimeiba.org/blog韩威编辑)在看之前,需要准备jxl.jar, mysql 的驱动包, log4j的jar包
sql.properties 将此文件放入src目录下
select.excel=select * from employee 是sql.properties文件中的内容
开始正文
index.jsp
<%@ page language="java" import="java.util.*,com.han.service.*,java.io.*,jxl.format.*,jxl.write.*,jxl.*,com.han.entity.*" pageEncoding="gbk"%>
<%
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
List list =ImportExcel.getList("select.excel"); //从数据库得到数据
OutputStream os = response.getOutputStream();//取出输出流
response.reset(); //空输出流
response.setHeader("Content-disposition","attachment; filename=fine.xls");//设定输出文件头
response.setContentType("application/msexcel");//定义输出类型
WritableWorkbook wbook = Workbook.createWorkbook(os);//建立excel文件
String title="报表";
WritableSheet wsheet =wbook.createSheet(title,0) ;//sheet名称
//设置excel标题
WritableFont font = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD,
false,UnderlineStyle.NO_UNDERLINE,jxl.write.Colour.BLACK);
WritableCellFormat wcfFC = new WritableCellFormat(font);
try {
wcfFC.setBackground(jxl.write.Colour.AQUA);
wsheet.addCell(new Label(1,0,title,wcfFC));
font = new
WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.write.Colour.BLACK);
wcfFC = new WritableCellFormat(font);
//开始生成主体内容
String sheetTitle[] = new String[]{"员工编号","员工姓名","员工所在部门","员工性别","员工职位"};
for(int j=0;j<sheetTitle.length;j++){
wsheet.addCell(new Label(j,0,sheetTitle[j]));
}
for(int i=0;i<list.size();i++){
Employee employee = (Employee)list.get(i);
wsheet.addCell(new Label(0,i+1,employee.getEmployeeId()+""));//员工编号
wsheet.addCell(new Label(1,i+1,employee.getEmployeeName()));//员工姓名
wsheet.addCell(new Label(2,i+1,employee.getEmployeeDepartment()));//员工所在部门
wsheet.addCell(new Label(3,i+1,employee.getEmployeeSex()));//员工性别
wsheet.addCell(new Label(4,i+1,employee.getEmployeePosition()));//员工职位
}
wbook.write();//写入文件
wbook.close();
os.flush();
out.clear();
out=pageContext.pushBody();
os.close();//关闭流
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
%>
ImportExcel.java 获取数据库数据的类
package com.han.service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.han.dao.DaoSupport;
import com.han.dao.help.Helper;
import com.han.entity.Employee;
public class ImportExcel {
/***
* 获得employee数据
* @param key
* @return
*/
public static List getList(String key){
List list = new ArrayList();
String sql=Helper.getInstance().getSqlCode(key);
Connection con = DaoSupport.getcon();
Statement st;
try {
st = con.createStatement();
ResultSet rs= st.executeQuery(sql);
while(rs.next()){
Employee employee = new Employee();
employee.setEmployeeId(rs.getInt(1));
employee.setEmployeeName(rs.getString(2));
employee.setEmployeePosition(rs.getString(3));
employee.setEmployeeSex(rs.getString(4));
employee.setEmployeeDepartment(rs.getString(5));
list.add(employee);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
Employee.java
员工实体类
package com.han.entity;
public class Employee {
private int employeeId;
private String employeeName;
private String employeePosition;
private String employeeSex;
private String employeeDepartment;
public String getEmployeeDepartment() {
return employeeDepartment;
}
public void setEmployeeDepartment(String employeeDepartment) {
this.employeeDepartment = employeeDepartment;
}
public int getEmployeeId() {
return employeeId;
}
public void setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public String getEmployeePosition() {
return employeePosition;
}
public void setEmployeePosition(String employeePosition) {
this.employeePosition = employeePosition;
}
public String getEmployeeSex() {
return employeeSex;
}
public void setEmployeeSex(String employeeSex) {
this.employeeSex = employeeSex;
}
}
Helper.java
是一个本人用单利写的一个工具类
package com.han.dao.help;
import java.io.IOException;
import java.util.Properties;
import org.apache.log4j.Logger;
import com.han.exception.DataAccessException;
import com.han.util.StringUtil;
/****
* 通过此类从sql.properties文件中sql语句
* @author Administrator
*
*/
public class Helper {
private Logger log = Logger.getLogger(Helper.class);
private String sqlFile="sql.properties";
private Properties sqlproperties = new Properties();
private static Helper helper = new Helper();
private Helper(){
try {
sqlproperties.load(this.getClass().getClassLoader().getResourceAsStream(sqlFile));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Helper getInstance(){
return helper;
}
/**
* 通过key获得sql语句
* @param key
* @return调用此方法获得sql语句
*/
public String getSqlCode(String key){
String sqlString =null;
try {
sqlString = getSql(key);
} catch (DataAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sqlString;
}
/***
* 通过键值在sql.properties文件中读取sql语句
* @param key
* @return
* @throws DataAccessException
*/
public String getSql(String key) throws DataAccessException{
if(sqlproperties.containsKey(key)){
String sqlString = StringUtil.getGBKStr(sqlproperties.getProperty(key).toString());
//sqlString = sqlString.replace("%20", " ");// 如果你的文件路径中包含空格,是必定会报错的
return sqlString;
}else{
log.fatal((new StringBuffer("faild to get sql code"))
.append(key).append("at").append(sqlFile).toString());
throw new DataAccessException((new StringBuffer("faild to get sql code"))
.append(key).append("at").append(sqlFile).toString());
}
}
}
StringUtil.java 是一个处理中文字符的类
package com.han.util;
import java.io.UnsupportedEncodingException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class StringUtil {
/***
* 中文处理
* @param str
* @return
*/
public static String getGBKStr(String str){
if(str == null && str.length()<0){
return "";
}
try {
byte[] strbytes = str.getBytes("ISO8859-1");
String strGBK = new String(strbytes,"gbk");
return strGBK;
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "";
}
//构造sql语句
public static String constructSql(String getSqlCode,String[] params){
String key="//?";
Pattern p = Pattern.compile(key);
Matcher m = p.matcher(getSqlCode);
StringBuffer sb = new StringBuffer();
int i=0;
boolean result = m.find();
while(result){
m.appendReplacement(sb, params[i]);
result = m.find();
i++;
}
return String.valueOf(m.appendTail(sb));
}
}
DataAccessException.java
自定义的一个异常类
package com.han.exception;
/***
* 数据访问异常类
* @author Administrator
*
*/
public class DataAccessException extends Exception {
public DataAccessException(){
super();
}
public DataAccessException(String message,Throwable cause){
super(message,cause);
}
public DataAccessException(String message){
super(message);
}
public DataAccessException(Throwable cause){
super(cause);
}
}
DaoSupport.java 也是一个工具类,连接数据库的
package com.han.dao;
import java.sql.*;
import com.han.service.ImportExcel;
public class DaoSupport {
private static final String DRIVER_URL = "jdbc:mysql://127.0.0.1:3306/employee?characterEncoding=utf-8";
private static final String USER_NAME = "root";
private static final String PASSWORD = "root";
private static Connection conn;
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
*
* @return
*/
public static Connection getcon(){
try {
conn =DriverManager.getConnection(DRIVER_URL,USER_NAME,PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void main(String args[]){
System.out.println(ImportExcel.getList("select.excel").size());
}
}
我的百度博客地址: http://hi.baidu.com/554768191/