jxl对Excel的读、写、更新以及插入图片

Java Excel是一开放源码项目，通过它开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件以及插入图片等等。?
? 详细介绍及API查看官方：http://www.andykhan.com/jexcelapi/index.html?
ps:读取结束时注意调用close()方法;释放内存?
写入结束时先调用write()方法，否则得到的是空Excel，因为先前的操作都是存储在缓存中。具体见代码?
一、读取Excel的例子：?
注意对数字、日期等不同CellType的读取?

/** * jxl 读取 * @author Michael sun */public class JxlRead {    /**     * 读取 excel 文件     * @param filePath     * @throws Exception     */    private void readExcel(String filePath) throws Exception {        InputStream is = null;        Workbook workbook = null;        try {            is = new FileInputStream(filePath);            workbook = Workbook.getWorkbook(is);            // sheet row column 下标都是从0开始的            Sheet sheet = workbook.getSheet(0);            int column = sheet.getColumns();            int row = sheet.getRows();            System.out.println("共有" + row + "行，" + column + "列数据");            // A1是字符            Cell cellA1 = sheet.getCell(0, 0);            System.out.println("A1 type:" + cellA1.getType());            if (cellA1.getType().equals(CellType.LABEL)) {                System.out.println("A1 content:" + cellA1.getContents());            }            // B1是数字            Cell cellB1 = sheet.getCell(1, 0);            System.out.println("B1 type:" + cellB1.getType());            if (cellB1.getType().equals(CellType.NUMBER)) {                NumberCell numberCell = (NumberCell) cellB1;                double douval = numberCell.getValue();                System.out.println("B1 value:" + douval);            }            // C1是日期            Cell cellC1 = sheet.getCell(2, 0);            System.out.println("C1 type:" + cellC1.getType());            if (cellC1.getType().equals(CellType.DATE)) {                DateCell dateCell = (DateCell) cellC1;                Date date = dateCell.getDate();                System.out.println("C1 date:" + date);            }            // 操作完成时，关闭对象，释放占用的内存空间            workbook.close();            is.close();        } catch (Exception e) {            e.printStackTrace(System.out);        } finally {            if (is != null) {                is.close();            }        }    }    /**     * @param args     * @throws Exception     */    public static void main(String[] args) throws Exception {        String filePath = "D:\\test\\testjxlread.xls";        JxlRead jxlRead = new JxlRead();        jxlRead.readExcel(filePath);    }}

?

运行结果：?
共有1行，4列数据?
A1 type:Label?
A1 content:字符?
B1 type:Number?
B1 value:123.0?
C1 type:Date?
C1 date:Wed Feb 24 08:00:00 CST 2010?
二、写入Excel的例子：?

/** * 写入excel * @author Michael sun */public class JxlWrite {    /**     * 写入 excel 文件     * @param filePath     * @throws Exception     */    private void writeExcel(String filePath) throws Exception {        OutputStream os = null;        try {            // 构建Workbook对象            os = new FileOutputStream(filePath);            WritableWorkbook wwb = Workbook.createWorkbook(os);            // 构建Excel sheet            WritableSheet sheet = wwb.createSheet("test write sheet", 0);            // 设置标题格式            WritableFont wfTitle = new jxl.write.WritableFont(                    WritableFont.ARIAL, 18, WritableFont.BOLD, true);            WritableCellFormat wcfTitle = new WritableCellFormat(wfTitle);            // 设置水平对齐方式            wcfTitle.setAlignment(Alignment.CENTRE);            // 设置垂直对齐方式            wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);            // 设置是否自动换行            wcfTitle.setWrap(true);            // 合并A1->C2            sheet.mergeCells(0, 0, 2, 1);            Label titleCell = new Label(0, 0, "Title Cell ", wcfTitle);            sheet.addCell(titleCell);            WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,                    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,                    Colour.BLUE);            WritableCellFormat wcf = new WritableCellFormat(wf);            // A3            Label labelCell = new Label(0, 2, "Label Cell ");            sheet.addCell(labelCell);            // B3            Label labelCellFmt = new Label(1, 2,                    "Label Cell with WritableCellFormat ", wcf);            sheet.addCell(labelCellFmt);            // A4 添加jxl.write.Number对象            jxl.write.Number labelN = new jxl.write.Number(0, 3, 3.1415926);            sheet.addCell(labelN);            // B4 添加Number对象 jxl.write.NumberFormat            NumberFormat nf = new NumberFormat("#.##");            WritableCellFormat wcfN = new WritableCellFormat(nf);            jxl.write.Number labelNF = new jxl.write.Number(1, 3, 3.1415926,                    wcfN);            sheet.addCell(labelNF);            // A5 添加jxl.write.Boolean对象            jxl.write.Boolean labelB = new jxl.write.Boolean(0, 4, true);            sheet.addCell(labelB);            // A6 添加 jxl.write.DateTime对象            jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 5,                    new Date());            sheet.addCell(labelDT);            // B6 添加DateTime对象 jxl.write.DateFormat            jxl.write.DateFormat df = new jxl.write.DateFormat(                    "yyyy-MM-dd HH:mm:ss");            WritableCellFormat wcfDF = new WritableCellFormat(df);            jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 5,                    new Date(), wcfDF);            sheet.addCell(labelDTF);            //先调用write();再调用close();            wwb.write();            wwb.close();            os.close();        } catch (Exception e) {            e.printStackTrace();        } finally {            if (null != os) {                os.close();            }        }    }    /**     * @param args     * @throws Exception     */    public static void main(String[] args) throws Exception {        String filePath = "D:\\test\\testjxlwrite.xls";        JxlWrite jxlwrite = new JxlWrite();        jxlwrite.writeExcel(filePath);    }}

?
ps：添加DateTime对象时 如果没有加格式时,出现1900/1/0 不知啥原因？?
三、插入图片?

?

/** * jxl 插入图片(图像格式只支持png) * @author Michael sun */public class JxlWriteImg {    /**     *      * @param filePath     */    private void writeImg(String filePath) throws Exception {        OutputStream os = null;        try {            String imgPath = "d:\\test\\xx.png";            os = new FileOutputStream(filePath);            WritableWorkbook wwb = Workbook.createWorkbook(os);            WritableSheet ws = wwb.createSheet("write img", 0);            File imgFile = new File(imgPath);            // WritableImage(col, row, width, height, imgFile);            WritableImage image = new WritableImage(2, 1, 8, 20, imgFile);            ws.addImage(image);            wwb.write();            wwb.close();        } catch (Exception e) {            System.out.println(e);        } finally {            if (null != os) {                os.close();            }        }    }    /**     * @param args     * @throws Exception     */    public static void main(String[] args) throws Exception {        String filePath = "D:\\test\\testjxlwriteimg.xls";        JxlWriteImg jxlWriteImg = new JxlWriteImg();        jxlWriteImg.writeImg(filePath);    }}

?

运行结果：?
?
四、更新Excel?

?

/** * jxl 更新excel * @author Michael sun */public class JxlUpdate {    /**     *      * @param filePath     */    private void doUpdate(String filePath) {        try {            // 获得原Excel文件            Workbook wb = Workbook.getWorkbook(new File(filePath));            // 打开一个文件的副本，并且指定数据写回到原文件            WritableWorkbook wwb = Workbook.createWorkbook(new File(filePath),                    wb);            // 对第一个工作簿的A1 更新            WritableSheet wsheet0 = wwb.getSheet(0);            WritableCell wc00 = wsheet0.getWritableCell(0, 0);            if (wc00.getType() == CellType.LABEL) {                Label label00 = (Label) wc00;                label00.setString("updata data");            }            // 添加一个工作表            WritableSheet sheet = wwb.createSheet("新增工作簿", 1);            // 写入一些测试数据            sheet.addCell(new Label(0, 0, "test data"));            // 关闭工作薄对象            wwb.write();            wwb.close();            wb.close();        } catch (Exception e) {            System.out.println(e);        }    }    /**     * @param args     */    public static void main(String[] args) {        String filePath = "D:\\test\\testjxlupdate.xls";        JxlUpdate jxlUpdate = new JxlUpdate();        jxlUpdate.doUpdate(filePath);    }}

?

• 
• 大小: 7 KB
  • 
  • 大小: 18 KB
    • 
    • 大小: 35.7 KB