• 热门专题

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

作者:什么向往  发布日期:2011-09-20 13:29:56
Tag标签: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);
        }
    }

    excel内容如下:

    运行结果:
    共有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);
        }
    
    }
About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规