Java三方---->excel框架之POI的使用一

　　Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。pdf框架之IText的使用，参见我的博客：Java三方---->pdf框架之IText的使用。今天我们开始POI中Excel部分的学习。

 

POI框架的简单实例

我们通过案例来学习POI，项目结构如下：

一、 创建工作薄

@Test
public void createExcel() throws Exception {
    // 建立xls文件，只需要引入poi-3.14-20160307.jar
    Workbook wb1 = new HSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream("excel/workbook.xls");
    wb1.write(fileOut);
    fileOut.close();
    wb1.close();

    // 需要额外引入poi-ooxml-3.14-20160307.jar， xmlbeans-2.6.0.jar， poi-ooxml-schemas-3.14-20160307.jar
    Workbook wb2 = new XSSFWorkbook();
    FileOutputStream fileOut2 = new FileOutputStream("excel/workbook.xlsx");
    wb2.write(fileOut2);
    fileOut2.close();
    wb2.close();
}

 二、 创建工作表

@Test
public void createExce2() throws Exception {
    Workbook wb = new HSSFWorkbook();
    wb.createSheet("new sheet");
    wb.createSheet("second sheet");
    String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]s"); // returns " O'Brien's sales   "
    wb.createSheet(safeName);

    FileOutputStream fileOut = new FileOutputStream("excel/workbook1.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

注意: 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 (' ')

三、 创建单元格

@Test
public void createExce3() throws Exception {
    Workbook wb = new HSSFWorkbook();
    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("excel/workbook2.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

四、 创建时间单元格

@Test
    public void createExce4() throws Exception {
        Workbook wb = new HSSFWorkbook();
        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("excel/workbook3.xls");
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }

五、 不同类型的单元格

@Test
public void createExce5() 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(Cell.CELL_TYPE_ERROR);

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

六、 边框的使用

@Test
public void createExce6() throws Exception {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    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("excel/workbook5.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

七、合并单元格

@Test
public void createExce7() throws Exception {
    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("excel/workbook6.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

八、 字体的设置

@Test
public void createExce8() throws Exception {
    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("excel/workbook7.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

九、 读或重写工作表

@Test
public void createExce9() throws Exception {
    InputStream inp = new FileInputStream("excel/workbook4.xls");

    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("excel/workbook8.xls");
    wb.write(fileOut);
    fileOut.close();
}

十、 单元格换行

@Test
public void createExce10() throws Exception {
    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);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("excel/workbook9.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

 

友情链接

• 具体的学习见官方文档：             http://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook
• 本文实例代码：               http://pan.baidu.com/s/1bpG4Lm3
• pdf框架之IText的使用:        Java三方---->pdf框架之IText的使用