POI学习札记（一）

POI学习笔记（一）

????? 项目中经常要解析和生成Excel文件，最常用的开源组件有poi与jxl。jxl是韩国人开发的，发行较早，但是更新的很慢，目前似乎还不支持excel2007。poi是apache下的一个子项目，poi应该是处理ms的office系列文档最好的组件了。poi3.6版本已经开始支持excel2007了。但是由于excel2007底层的实现似乎变成xml与excel2003底层存储发生了本质的变化，因此poi解析excel的类就存在差异了。

????? 现在简单的介绍下poi常用的接口。

????? 经常用的类一般都在org.apache.poi.hssf.usermodel（excel2003）或org.apache.poi.xssf.usermodel
（excel2007）。

工作薄：? WorkBook是操作Excel的入口，HSSFWorkbook, XSSFWorkbook实现了该接口。页：Sheet表示工作薄的分页。HSSFSheet， XSSFChartSheet, XSSFDialogsheet, XSSFSheet实现了该接口。 Row：表示页中的一行。HSSFRow, XSSFRow实现了该接口。Cell：行中的一个单元格。HSSFCell, XSSFCell实现了该接口。

从上面的介绍得知：页是通过工作薄对象创建的，行是通过页对象创建的，单元格是通过行对象创建的。接下来，我们就开始发掘poi的强大功能吧。

创建一个空白的工作薄

import java.io.FileOutputStream;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;//2003版本Workbook wb = new HSSFWorkbook();FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut); fileOut.close();//2007版本// Workbook wb = new XSSFWorkbook();// FileOutputStream fileOut = new FileOutputStrea("workbook.xlsx");// wb.write(fileOut);// fileOut.close();

?注意：Workbook 是org.apache.poi.ss.usermodel包下的一个接口，注意与以前版本的不同，HSSFWorkbook和XSSFWorkbook实现了该接口，这样就达到了面前接口编程。下面的excel工具类中要用到此点知识。

?2. 创建两个空白页

?

?

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

?

3. 创建单元格

?

public void createRow() throws Exception {Workbook wb = new HSSFWorkbook();// Workbook wb = new XSSFWorkbook();CreationHelper createHelper = wb.getCreationHelper();Sheet sheet = wb.createSheet("new sheet");//创建一行并放一些单元格到该行中，行的索引是以0开始的Row row = sheet.createRow((short) 0);// 创建一个单元格并填充一个整数的值Cell cell = row.createCell(0);cell.setCellValue(1);//链式写法row.createCell(1).setCellValue(1.2);row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));row.createCell(3).setCellValue(true);//输出文件FileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();}

?

?4. 创建日期单元格

?

public void createDateCell() throws Exception {Workbook wb = new HSSFWorkbook();// Workbook wb = new XSSFWorkbook();CreationHelper createHelper = wb.getCreationHelper();Sheet sheet = wb.createSheet("new sheet");// Create a row and put some cells in it. Rows are 0 based.Row row = sheet.createRow(0);// Create a cell and put a date value in it. The first cell is not// styled// as a date.Cell cell = row.createCell(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.CellStyle cellStyle = wb.createCellStyle();cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/MM/dd hh:mm"));cell = row.createCell(1);// cell.setCellValue(new Date());Date date = new Date();cell.setCellValue(createHelper.createRichTextString(date.toString()));cell.setCellStyle(cellStyle);// you can also set date as java.util.Calendarcell = row.createCell(2);cell.setCellValue(Calendar.getInstance());cell.setCellStyle(cellStyle);// Write the output to a fileFileOutputStream fileOut = new FileOutputStream("workbook.xls");wb.write(fileOut);fileOut.close();}

?

5.? 创建不同的单元格样式

?

public void createCellType() throws Exception{Workbook wb = new HSSFWorkbook();    Sheet sheet = wb.createSheet("new sheet");    Row row = sheet.createRow((short)2);    row.createCell(0).setCellValue(1.1);    row.createCell(1).setCellValue(new Date());    row.createCell(2).setCellValue(Calendar.getInstance());    row.createCell(3).setCellValue("a string");    row.createCell(4).setCellValue(true);    row.createCell(5).setCellType(HSSFCell.CELL_TYPE_ERROR);    // Write the output to a file    FileOutputStream fileOut = new FileOutputStream("workbook.xls");    wb.write(fileOut);    fileOut.close();}

?

6.? 设置单元格水平垂直对齐方式

?

public static void main(String[] args)  throws Exception {        Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();        Sheet sheet = wb.createSheet();        Row row = sheet.createRow((short) 2);        row.setHeightInPoints(30);        createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);        createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);        createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);        createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);        createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);        createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);        createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);        // Write the output to a file        FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");        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 halign the horizontal alignment for the cell.     */    private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {        Cell cell = row.createCell(column);        cell.setCellValue(new XSSFRichTextString("Align It"));        CellStyle cellStyle = wb.createCellStyle();        cellStyle.setAlignment(halign);        cellStyle.setVerticalAlignment(valign);        cell.setCellStyle(cellStyle);    }

?

本篇写的都是poi基本的知识，POI学习笔记二将会继续学习poi一些高级的东西。

查看更多 下一篇