java用poi.jar操作Excel

poi,与jxl一样都可以实现java操作excel文件;下载地址(http://apache.mirror.phpchina.com/jakarta/poi/release/src/ )

poi介绍:Jakarta POI - Java API To Access Microsoft Format Files

有以下几个包:

POIFS: for OLE 2 Documents ,POIFS is the oldest and most stable part of the project. It is our port of the OLE 2 Compound Document Format to pure Java.

HSSF :for Excel Documents,HSSF is our port of the Microsoft Excel 97(-2002) file format (BIFF8) to pure Java.

HWPF :for Word Documents ,HWPF is our port of the Microsoft Word 97 file format to pure Java.

HPSF :for Document Properties,HPSF is our port of the OLE 2 property set format to pure Java

下面介绍其应用(hssf):

创建工作簿(workbook):

    HSSFWorkbook wb = new HSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

创建表单(sheet):

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    HSSFSheet sheet2 = wb.createSheet("second sheet");
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

创建单元格(cell):

HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    // 创建一行并向其中添加一些cell,Rows 下标从0开始(Create a row and put some cells in it. Rows are 0 based.)
    HSSFRow row = sheet.createRow((short)0);
    // 创建一个Cell,并向其中添加值(Create a cell and put a value in it.)
    HSSFCell cell = row.createCell((short)0);
    cell.setCellValue(1);

    //或者直接用一行(java语句)完成 (Or do it on one line.)
    row.createCell((short)1).setCellValue(1.2);
    row.createCell((short)2).setCellValue("This is a string");
    row.createCell((short)3).setCellValue(true);

    // 输出到文件(Write the output to a file)
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

创建Date类型单元格

。。。  

HSSFCell cell = row.createCell((short)0);
    cell.setCellValue(new Date());
    // we style the second cell as a date (and time). It is important to
    // create a new cell style from the workbook otherwise you can end up
    // modifying the built in style and effecting not only this cell but other cells.
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    cell = row.createCell((short)1);
    cell.setCellValue(new Date());
    cell.setCellStyle(cellStyle);

。。。

创建不同类型单元格:

   HSSFRow row = sheet.createRow((short)2);
    row.createCell((short) 0).setCellValue(1.1);
    row.createCell((short) 1).setCellValue(new Date());
    row.createCell((short) 2).setCellValue("a string");
    row.createCell((short) 3).setCellValue(true);
    row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);

指定align:

public static void main(String[] args)
            throws IOException
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");
        HSSFRow row = sheet.createRow((short) 2);
        createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);
        createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);
        createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);
        createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);
        createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);
        createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);
        createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();

    }

    /**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb        the workbook
     * @param row       the row to create the cell in
     * @param column    the column number to create the cell in
     * @param align     the alignment for the cell.
     */
    private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)
    {
        HSSFCell cell = row.createCell(column);
        cell.setCellValue("Align It");
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(align);
        cell.setCellStyle(cellStyle);
    }

合并单元格:

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    HSSFRow row = sheet.createRow((short) 1);
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue("This is a test of merging");
//public Region(int rowFrom, short colFrom, int rowTo, short colTo)
    sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
   读取并修改文件:                  
   POIFSFileSystem fs      =   new POIFSFileSystem(new FileInputStream("workbook.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = sheet.getRow(2);
    HSSFCell cell = row.getCell((short)3);
    if (cell == null)
        cell = row.createCell((short)3);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue("a test");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();

...................................太多了,请大家到poi自带文档中看吧。
下载地址(http://apache.mirror.phpchina.com/jakarta/poi/release/src/ )

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值