java从数据库中将数据导入到excel中

（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/