java——POI学习——1

创建workbook

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

Workbook wb1 = new XSSFWorkbook();  
FileOutputStream fileOut1 = new FileOutputStream("workbook.xlsx");
wb.write(fileOut);
eOut);
fileOut.close();

创建sheet

Workbook wb = new HSSFWorkbook();// or new XSSFWorkbook();
//sheet名不能超过31个字符；不能含有以下字符
//0x0000,0x0003,:,*,?,/,[,]
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

创建单元格（cell）

Workbook wb = new HSSFWorkbook();// or new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
CreationHelper createHelper = wb.getCreationHelper();
// 创建一行，行以下标0开始。
Row row = sheet.createRow(0);
// 创建一个单元格并设值
Cell cell = row.createCell(0);
cell.setCellValue(1);

// or do it on one line.
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();

创建日期单元格（date cell）

Workbook wb = new HSSFWorkbook();// or new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
CreationHelper createHelper = wb.getCreationHelper();
// 创建一行，行以下标0开始。
Row row = sheet.createRow(0);
// 创建一个单元格并设值
Cell cell = row.createCell(0);
cell.setCellValue(new Date());

CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyyMMdd"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

// you can also set date as java.util.Calendar.
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());

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

不同类型的单元格（different types of cells）

Workbook wb = new HSSFWorkbook();// or new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(2);
row.createCell(1).setCellValue(1.1);
row.createCell(2).setCellValue(new Date());
row.createCell(3).setCellValue(Calendar.getInstance());
row.createCell(4).setCellValue(true);
row.createCell(5).setCellType(CellType.ERROR);
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

当打开一个workbook（.xls、.xlsx）时，这个workbook可以被加载为file或者inputSream。使用一个文件对象可以消耗更低的内存，inputStream需要更多的内存空间去缓存整个文件

// Workbook wb = WorkbookFactory.create(new File("workbook.xls"));
// 使用InputStream，需要更多的内存空间
// Workbook wb = WorkbookFactory.create(new
// FileInputStream("workbook.xls"));
// 如果直接使用HSSFWorkbook 或者 XSSFWorkbook对象，
// 通常应该使用NPOIFSFileSystem或者OPCPackage，充分的控制生命周期（包括对于文件的关闭）
// NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("workbook.xls"));
// HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
OPCPackage pkg = OPCPackage.open(new File("workbook.xls"));
XSSFWorkbook wb = new XSSFWorkbook(pkg);

Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0);
System.out.println(row.getCell(0).getNumericCellValue());
pkg.close();

演示各种对齐选项

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, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
    createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.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("Align It");
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cell.setCellStyle(cellStyle);
}