Apache POI 官方文档解析和趣谈

我希望看到这篇文章大家不要转载

 

在这里我想说明一下，我接触POI也是不太时间，有句话大家都会说，“如果有什么不对的地方，希望大家包涵”，哈哈

我这里也引用一下，如果有错误的地方，希望大家指出来，我非常高兴

 

我这篇文章不想长篇大论的讲POI是什么，题目为趣谈，那么当然是开开心心的学习一门新技术，哈哈，好吧，废话就不说了，现在开始

 

Apache POI - the Java API for Microsoft Documents

官方的大题目，哇，好大，哈哈，很明显POI是什么 就是java API ，什么API，是为了解析微软文档文件的，哈哈我说了一堆废话，哈哈，简直就是在翻译吗。。。翻译也罢，反正POI就是这个意思

 

 

官网大篇幅的讲述POI是做什么的，这里我不想当interpreter，所以这里就不翻译了，我建议大家读读，为了更加了解POI，这里我提供链接

 

官网： http://poi.apache.org/index.html

 

至于下载吗，哈哈，我想做技术的，不用我叫怎么下载吧，你们懂的

 

既然POI是解析MD(Microsoft Document)，那么我们对MD分别分析

 

Excel 

 

我把官网给的图图做了处理，让大家看的更加清晰：

 

上面的图已经很清晰的说明Excel2003和Excel2003的基本区别和相同点，POI对Excel2010其实和2007出不多，但有的时候会出现bug。我没有做尝试，这里暂不说明

 

基本操作：

 

1.创建Excel文件

 

 

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

 

 Workbook wb = new XSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("excel.xlsx");
    wb.write(fileOut);
    fileOut.close();

 

noite： 第一HSSF只能创建后缀名为.xls文件，XSSF只能创建xlsx文件 互换的，生成的excel打不开

 

2.创建sheet

 

 

   Workbook wb = new HSSFWorkbook();  // or new XSSFWorkbook();
    Sheet sheet1 = wb.createSheet("new sheet");
    Sheet sheet2 = wb.createSheet("second sheet");

    // Note that sheet name is Excel must not exceed 31 characters
    // and must not contain any of the any of the following characters:
    // 0x0000
    // 0x0003
    // colon (:)
    // backslash (\)
    // asterisk (*)
    // question mark (?)
    // forward slash (/)
    // opening square bracket ([)
    // closing square bracket (])

    // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
    // for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
    String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
    Sheet sheet3 = wb.createSheet(safeName);

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

 

 

这是官网给力例子，我这里直接写出，嘿嘿，趣谈来了，要注意哦名字就有限制的，所以最好用createSafeSheetName

 

3.创建cell

 

 

 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((short)0);
    // Create a cell and put a value in it.
    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);

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

 

 

官网给的小例子真是刚猛，太经典了

 

4.创建时间类型的cell

 

 

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("m/d/yy h:mm"));
    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());
    cell.setCellStyle(cellStyle);

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

 

 

这里要注意cellStyle的运用，它可是POI的小画家啊，特别爱涂色

 

5.创建不同的行的cell

 

 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(Cell.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, 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);
    }

 

 

这个很少用到，很少有人在单元格内设定位置。。。

 

7.给单元格加边框

 

 

Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(1);

    // Create a cell and put a value in it.
    Cell cell = row.createCell(1);
    cell.setCellValue(4);

    // Style the cell with borders all around.
    CellStyle style = wb.createCellStyle();
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
    style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cell.setCellStyle(style);

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

 

 8.遍历每个单元格

 

 

sheet sheet = wb.getsheetat(0);
    for (iterator<row> rit = sheet.rowiterator(); rit.hasnext(); ) {
      row row = rit.next();
      for (iterator<cell> cit = row.celliterator(); cit.hasnext(); ) {
        cell cell = cit.next();
        // do something here
      }
    }

 

 

 

 

Sheet sheet = wb.getSheetAt(0);
    for (Row row : sheet) {
      for (Cell cell : row) {
        // Do something here
      }
    }

 

 

9.填涂单元格颜色

 

 

Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);

    // Aqua background
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(CellStyle.BIG_SPOTS);
    Cell cell = row.createCell((short) 1);
    cell.setCellValue("X");
    cell.setCellStyle(style);

    // Orange "foreground", foreground being the fill foreground not the font color.
    style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row.createCell((short) 2);
    cell.setCellValue("X");
    cell.setCellStyle(style);

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

 

 

10.合并单元格

 

 Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    Row row = sheet.createRow((short) 1);
    Cell cell = row.createCell((short) 1);
    cell.setCellValue("This is a test of merging");

    sheet.addMergedRegion(new CellRangeAddress(
            1, //first row (0-based)
            1, //last row  (0-based)
            1, //first column (0-based)
            2  //last column  (0-based)
    ));

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

 

 

11.改变文字

 

 

Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow(1);

    // Create a new font and alter it.
    Font font = wb.createFont();
    font.setFontHeightInPoints((short)24);
    font.setFontName("Courier New");
    font.setItalic(true);
    font.setStrikeout(true);

    // Fonts are set into a style so create a new one to use.
    CellStyle style = wb.createCellStyle();
    style.setFont(font);

    // Create a cell and put a value in it.
    Cell cell = row.createCell(1);
    cell.setCellValue("This is a test of fonts");
    cell.setCellStyle(style);

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

 

 12.读excel文件

 

 

InputStream inp = new FileInputStream("workbook.xls");
    //InputStream inp = new FileInputStream("workbook.xlsx");

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    Row row = sheet.getRow(2);
    Cell cell = row.getCell(3);
    if (cell == null)
        cell = row.createCell(3);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue("a test");

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

 

 13.添加新行

 

 

 Workbook wb = new XSSFWorkbook();   //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(2);
    cell.setCellValue("Use \n with word wrap on to create a new line");

    //to enable newlines you need set a cell styles with wrap=true
    CellStyle cs = wb.createCellStyle();
    cs.setWrapText(true);
    cell.setCellStyle(cs);

    //increase row height to accomodate two lines of text
    row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));

    //adjust column width to fit the content
    sheet.autoSizeColumn((short)2);

    FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");
    wb.write(fileOut);
    fileOut.close();

 

 以上是基本的常用的操作，我也给出代码出处：

 

http://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook