Java POI

前言

使用Web專案開發,難免會遇到要使用各種Microsoft 文件,處理Excel及其他Microsoft文件常用的就是Apache POI。

環境

     載點  http://www.apache.org/dyn/closer.cgi/poi/

       POI 3.2-FINAL Released (2008-10-19)是穩定的版本，支援office 2000以下格式

 

       POI 3.5 beta 6, and Office Open XML Support (2009-06-22)，支援更新的office及open xml格式

 

介紹與說明

在此介紹插入圖片有兩種方式

1.插入圖片至座標

public class TestExcel{

 

    public void insertImage throws IOException, WriterException {

 

        OutputStream outStream = null;

        BufferedImage bufferImg = null;

        ByteArrayOutputStream outByteStream = null;

        //excel名稱

        String excelName = "pic.xls";

        

            //在excel中建立活頁簿

            HSSFWorkbook workbook = new HSSFWorkbook();

            //建立新的sheet

            HSSFSheet sheet = workbook.createSheet("圖片測試");

            //建立輸出流stream

            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();  

            bufferImg =ImageIO.read(new File("/Users/zhangjiaxiang/Desktop/cat.jpg"));

            //寫入圖片

            ImageIO.write(bufferImg, "png", byteArrayOut);

            byteArrayOut.flush();

            byteArrayOut.close();

            //圖畫的管理器,一個sheet只能獲取一個

            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            //指定圖片位置

            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)0, (short)0, (short) 4, (short)4);   

            //插入圖片  

            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

            outByteStream = new ByteArrayOutputStream();

            outStream = response.getOutputStream();response.setContentType("application/ms-excel");

           

            workbook.write(outStream);

    }

}

 

Demo畫面

2.將圖片插入至單元格

   

   // 插入圖片至Excel

    String fileName = /Users/zhangjiaxiang/Desktop/cat.jpg;

 

    InputStream is = new FileInputStream(fileName);

    byte[] bytes = IOUtils.toByteArray(is);

 

    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);

 

    CreationHelper helper = workbook.getCreationHelper();

    Drawing drawing = sheet.createDrawingPatriarch();

    ClientAnchor anchor = helper.createClientAnchor();

 

    // 圖片插入座標

    anchor.setCol1(0);

    anchor.setRow1(0);

    // 插入圖片

    Picture pict = drawing.createPicture(anchor, pictureIdx);

    //圖片重新定義大小

    pict.resize();