用Java读取Excel表数据和将数据导出成Excel表

　　文章转载自 http://blog.csdn.net/monitor1394/article/details/6091272

---

　　最近的项目需要使用大量的Excel表，策划把数据都做成Excel表，通过编辑器把Excel表的数据导入到数据库，编辑器提供各种编辑操作，还要提供将数据库导出成Excel表的功能。借助于Java Excel API，这个问题就很简单了。

---

一：史上最简单的方法

　　对于简单的表格（纯文本），其实可以不借助Java Excel API而有更简单的方法！用制表符/t隔开每个域，用换行符/n隔开每一行，将文件后缀名改为”.xls”搞定！只是这样弄出来的Excel表无法指定格式（如颜色，边框，对齐方式等等）。

---

二：Java Excel API

　　Java Excel是一个开源项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件等，在项目中需要导入名为jxl.jar的包。在这里只是示例它的基本用法，其他高级的功能（图片、公式、格式等）请参考JavaExcel的帮助文档，这里是关于它的资料：http://jexcelapi.sourceforge.net/。

　　如有一个用户资料的Excel表，包含ID、用户名、性别、邮件等信息，定义一个用户JavaBean：

package com.monitor1394.excel;

/**
 * 用户
 * @author monitor
 * Created on 2010-12-22, 9:57:58
 */
public class User {
    /**
     * ID
     */
    private int id;
    /**
     * 用户名
     */
    private String name;
    /**
     * 性别 1:男 2:女
     */
    private int sex;
    /**
     * 邮件
     */
    private String email;

    public User() {
    }

    public User(int id, String name, int sex, String email) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.email = email;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return id + ":" + name;
    }
}

　　提供的Excel表操作类如下，某些单元格的格式可按自己意愿指定：

package com.monitor1394.excel;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Excel表操作
 *
 * @author monitor
 * Created on 2010-12-22, 9:50:28
 */
public class Excel {
    /**
     * 标题单元格格式
     */
    private static WritableCellFormat titleFormat = null;
    /**
     * 主题内容单元格格式
     */
    private static WritableCellFormat bodyFormat = null;
    /**
     * 注释单元格格式
     */
    private static WritableCellFormat noteFormat = null;
    /**
     * 浮点型数据的单元格格式
     */
    private static WritableCellFormat floatFormat = null;
    /**
     * 整型数据的单元格格式
     */
    private static WritableCellFormat intFormat = null;
    /**
     * 初始化数据
     */
    private static boolean init = false;

    /**
     * 私有构造方法，防止错误使用Excel类
     */
    private Excel() {
    }

    /**
     * 初始化各单元格格式
     *
     * @throws WriteException 初始化失败
     */
    private static void init() throws WriteException {
        WritableFont font1, font2, font3, font4;
        //Arial字体，9号，粗体，单元格黄色，田字边框，居中对齐  
        font1 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
        titleFormat = new WritableCellFormat(font1);
        titleFormat.setBackground(Colour.YELLOW);
        titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        titleFormat.setAlignment(Alignment.CENTRE);
        //Arial字体，9号，粗体，单元格黄色，田字边框，左右居中对齐，垂直居中对齐，自动换行  
        font2 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
        noteFormat = new WritableCellFormat(font2);
        noteFormat.setBackground(Colour.YELLOW);
        noteFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        noteFormat.setAlignment(Alignment.CENTRE);
        noteFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        noteFormat.setWrap(true);
        //Arial字体，9号，非粗体，单元格淡绿色，田字边框  
        font3 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);
        bodyFormat = new WritableCellFormat(font3);
        bodyFormat.setBackground(Colour.LIGHT_GREEN);
        bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        //Arial字体，9号，非粗体，单元格淡绿色，田字边框  
        font4 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);
        floatFormat = new WritableCellFormat(font4, NumberFormats.FLOAT);
        floatFormat.setBackground(Colour.LIGHT_GREEN);
        floatFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        //Arial字体，9号，非粗体，单元格淡绿色，田字边框  
        font4 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);
        intFormat = new WritableCellFormat(font4, NumberFormats.INTEGER);
        intFormat.setBackground(Colour.LIGHT_GREEN);
        intFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

        init = true;
    }

    public static void createUserExcelFile(List<User> userList, File destFile) throws WriteException, IOException {
        if (init == false) init();
        int index, row;
        WritableSheet sheet = null;
        WritableWorkbook book = null;
        book = Workbook.createWorkbook(destFile);
        sheet = book.createSheet("用户表", 0);
        sheet.setColumnView(0, 15);
        sheet.setColumnView(1, 15);
        sheet.setColumnView(2, 15);
        sheet.setColumnView(3, 40);
        //字段变量名  
        index = 0;
        sheet.addCell(new Label(index++, 0, "id", titleFormat));
        sheet.addCell(new Label(index++, 0, "name", titleFormat));
        sheet.addCell(new Label(index++, 0, "sex", titleFormat));
        sheet.addCell(new Label(index++, 0, "email", titleFormat));
        //字段名  
        index = 0;
        sheet.addCell(new Label(index++, 1, "ID", titleFormat));
        sheet.addCell(new Label(index++, 1, "用户名", titleFormat));
        sheet.addCell(new Label(index++, 1, "性别", titleFormat));
        sheet.addCell(new Label(index++, 1, "邮件", titleFormat));
        //字段注释  
        index = 0;
        sheet.addCell(new Label(index++, 2, null, noteFormat));
        sheet.addCell(new Label(index++, 2, null, noteFormat));
        sheet.addCell(new Label(index++, 2, "1:男/n2:女", noteFormat));
        sheet.addCell(new Label(index++, 2, null, noteFormat));
        row = 3;
        for (User user : userList) {
            if (user == null) continue;
            index = 0;
            sheet.addCell(new Number(index++, row, user.getId(), bodyFormat));
            sheet.addCell(new Label(index++, row, user.getName(), bodyFormat));
            sheet.addCell(new Number(index++, row, user.getSex(), bodyFormat));
            sheet.addCell(new Label(index++, row, user.getEmail(), bodyFormat));
            row++;
        }
        book.write();
        if (book != null) book.close();
    }

    public static List<User> readUserExcelFile(File file) throws IOException, BiffException {
        if (file == null) return null;
        int row, column;
        String temp = null;
        Workbook book = null;
        Sheet sheet = null;
        List<User> userList = new ArrayList<User>();
        book = Workbook.getWorkbook(file);
        sheet = book.getSheet(0);
        row = 3;
        while (row < sheet.getRows()) {
            column = 0;
            User user = new User();
            //id  
            temp = sheet.getCell(column++, row).getContents().trim();
            if (temp != null && !temp.equals("") && temp.matches("//d+")) user.setId(Integer.parseInt(temp));
            else break;
            //名称  
            temp = sheet.getCell(column++, row).getContents().trim();
            if (temp != null && !temp.equals("")) user.setName(temp);
            //性别  
            temp = sheet.getCell(column++, row).getContents().trim();
            if (temp != null && !temp.equals("") && temp.matches("//d+")) user.setSex(Integer.parseInt(temp));
            //邮件  
            temp = sheet.getCell(column++, row).getContents().trim();
            if (temp != null && !temp.equals("")) user.setEmail(temp);

            userList.add(user);
            row++;
        }
        if (book != null) book.close();
        return userList;
    }
}  

---

要导入的Excel表格式如下：

---

导出后的Excel表如下：