凝香旋&涡

程序代码

;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;


/**
*
* 使用JExcelAPI读取Excel文件
* @author ZIZZ.
* @version Create Date: 2007-12-3 下午02:33:45.
*/
public class ExcelReader {
    
    private static final String FILE_NAME = "c:/工程教师名册print.xls";
    
    public static void main(String[] args){
        ExcelReader reader = new ExcelReader();
        try {
            //获取导入文件的输入流
            InputStream is = new FileInputStream(ExcelReader.FILE_NAME);
            //解析Excel文件
            reader.readExcel(is);
            //关闭输入流
            is.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 读取Excel文件
     * @param is
     */
    public void readExcel(InputStream is){
        try {
            //得到Excel工作薄
            Workbook book = Workbook.getWorkbook(is);
            //得到工作表
            Sheet sheet = book.getSheet(0);
            //得到工作表的列数
            int columns = sheet.getColumns();
            //得到工作表的行数
            int rows = sheet.getRows();
            //读取工作表内容
            for(int row=0;row<rows;row++){
                for(int col=0;col<columns;col++){
                    //得到单元格数据,第一个参数表示列,第二个参数表示行
                    Cell cell = sheet.getCell(col,row);
                    //得到单元格的内容
                    System.out.print(cell.getContents() + "\t");
                }
                System.out.println();
            }            
            //关闭工作簿
            book.close();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

程序代码

;

import java.util.Date;

/**
*
* User模型对象，该对像对应着数据库ZIZZ_USER表当中的一行数据.
* @author ZIZZ.
* @version Create Date: 2007-12-3 下午03:08:24.
*/
public class User {
    /**
     * 映射为表的ID列
     */
    private String id;
    
    /**
     * 映射为表的NAME列
     */
    private String name;
    
    /**
     * 映射为表的LOGINID列
     */
    private String loginId;
    
    /**
     * 映射为表的PASSWORD列
     */
    private String password;
    
    /**
     * 映射为表的BIRTHDAY列
     */
    private Date birthday;
    
    /**
     * 映射为表的Create_TIME列
     */
    private Date createTime;

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getLoginId() {
        return loginId;
    }

    public void setLoginId(String loginId) {
        this.loginId = loginId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }    

}

程序代码

;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
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.ArrayList;
import java.util.Iterator;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.DateTime;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import jxl.write.DateFormat;
import jxl.write.WritableCellFormat;

/**
*
* 该类实现了对Excel的操作.
* @author ZIZZ.
* @version Create Date: 2007-12-3 下午03:09:21.
*/
public class ExcelWriter {
    
    private static final String DRVIER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/hibernate";
    private static final String USER = "root";
    private static final String PASSWORD = "admin";
    
    private static final String EXPORT_FILE_NAME = "c:/用户信息.xls";
    
    static{
        try {
            Class.forName(DRVIER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args){
        ExcelWriter writer = new ExcelWriter();
        List users = writer.queryUser();
        writer.exportToExcel(users);        
    }
    
    /**
     * 得到数据库连接.
     * @return 数据库连接
     */
    private Connection getConnection(){
        Connection result = null;
        try {
            result = DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
    
    /**
     * 查询用户表.
     * @return 所有用户集合
     */
    private List queryUser(){
        List<User> result = new ArrayList<User>();
        Connection conn = this.getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("Select * FROM ZIZZ_USER");
            while(rs.next()){
                User user = new User();
                user.setId(rs.getString("ID"));
                user.setName(rs.getString("NAME"));
                user.setLoginId(rs.getString("LOGINID"));
                user.setPassword(rs.getString("PASSWORD"));
                user.setBirthday(rs.getTimestamp("BIRTHDAY"));
                user.setCreateTime(rs.getTimestamp("Create_TIME"));
                result.add(user);
            }    
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally{
            try{
                if(rs != null)rs.close();
                if(stmt != null)stmt.close();
                if(conn != null)conn.close();
            }catch(SQLException ex){
                ex.printStackTrace();
            }
        }
        return result;
    }
    
    /**
     * 导出到Excel文件当中.
     * @param users
     */
    private void exportToExcel(List users){
        OutputStream os = null;
        try {
            //得到导出文件的输出流
            os = new FileOutputStream(EXPORT_FILE_NAME);
            //得到可写的Excel工作薄
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            //创建一张新的工作表，工作表表名为用户数据，工作表为最大的65535行
            WritableSheet sheet = workbook.createSheet("sheet1", 0/65534);
            //填充表头数据
            String[] heads = new String[]{"编号","姓名","登录号","密码","生日","建立时间"};
            for(int col=0;col<heads.length;col++){
                //WritableCell有多种实现，包括Blank, Boolean, jxl.write.biff.CellValue, DateTime, Formula, Label, Number
                WritableCell cell = new Label(col,0,heads[col]);
                sheet.addCell(cell);
            }
            //设置时间的显示格式，显示格式为年-月-日，假如需要加上小时，分钟可以加上HH:mm:ss等
            DateFormat dateFormat = new DateFormat("yyyy-MM-dd");
            WritableCellFormat cellDateFormat = new WritableCellFormat(dateFormat);
            //jxl还提供共了比较数字的格式化，单元格的格式化等等功能，非常好用，用法很类似
            //填充表格数据
            if(users != null){
                Iterator iter = users.iterator();
                int row = 1;
                while(iter.hasNext()){
                    User user = (User)iter.next();
                    //设置单元格数据
                    //WritableCell有多种实现，包括Blank, Boolean, jxl.write.biff.CellValue, DateTime, Formula, Label, Number
                    //创建ID的单元格
                    WritableCell idCell = new Label(0,row,user.getId());
                    //创建名字的单元格
                    WritableCell nameCell = new Label(1,row,user.getName());
                    //创建登录号的单元格
                    WritableCell loginIdCell = new Label(2,row,user.getLoginId());
                    //创建密码的单元格
                    WritableCell passwordCell = new Label(3,row,user.getPassword());
                    //创建生日的单元格
                    WritableCell birthdayCell = new DateTime(4,row,user.getBirthday(),cellDateFormat);
                    //创建用户建立时间的单元格
                    WritableCell createTimeCell = new DateTime(5,row,user.getCreateTime(),cellDateFormat);
                    //将单元格加入到工作表
                    sheet.addCell(idCell);
                    sheet.addCell(nameCell);
                    sheet.addCell(loginIdCell);
                    sheet.addCell(passwordCell);
                    sheet.addCell(birthdayCell);
                    sheet.addCell(createTimeCell);
                    row++;
                }
            }        
            //将表格数据写入磁盘
            workbook.write();
            //关闭相应的资源
            workbook.close();
            os.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
        
    }
}