POI

JAVA中操作Excel的有两种比较主流的工具包： JXL 和 POI 。

　　JXL只能操作Excel 95, 97, 2000也即以.xls为后缀的excel。JXL的官网为：http://www.andykhan.com/jexcelapi。而poi可以操作Excel 95及以后的版本，即可操作后缀为 .xls 和 .xlsx两种格式的excel。

　　POI全称 Poor Obfuscation Implementation,直译为“可怜的模糊实现”，利用POI接口可以通过JAVA操作Microsoft office 套件工具的读写功能。官网：http://poi.apache.org ，POI支持office的所有版本。

　　对于只操作2003 及以前版本的excel，只需要poi-3.10.1-20140818.jar ，如果需要同时对2007及以后版本进行操作则需要复制poi-ooxml-3.10.1-20140818.jar，poi-ooxml-schemas-3.10.1-20140818.jar，以及复制在ooxml-lib目录下的xmlbeans-2.6.0.jar，dom4j-1.6.1.jar。

@Test
    //输出03版本的excel
    public void test1() throws IOException {
        //创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建工作表
        HSSFSheet sheet = workbook.createSheet("工作簿1");
        //创建行
        HSSFRow row = sheet.createRow(3);
        //创建单元格，第三行第三列
        HSSFCell cell = row.createCell(3);

        cell.setCellValue("HelloWord!");
        //输出到硬盘
        FileOutputStream outputStream = new FileOutputStream("d:\\test.xls");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    @Test
    //读取03版本的excel
    public void test2() throws IOException {
        FileInputStream fileInputStream = new FileInputStream("d:\\test.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFRow row = sheet.getRow(3);
        HSSFCell cell = row.getCell(3);

        System.out.println(cell.getStringCellValue());
        workbook.close();
        fileInputStream.close();
    }
    @Test
    //输出07及以上版本excel
    public void test3() throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("表1");
        XSSFRow row = sheet.createRow(2);
        XSSFCell cell = row.createCell(2);
        cell.setCellValue("helloworld!");
        FileOutputStream outputStream = new FileOutputStream("d:\\test.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

读取各种版本

    public void test4() throws IOException {
        String fileName = "d:\\test.xlsx";
        boolean is03Excel = true;
        FileInputStream fileInputStream = new FileInputStream(fileName);
        if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))
        {
            if(fileName.matches("^.+\\.(?i)(xlsx)$"))
                is03Excel = false;
        }
        Workbook workbook = is03Excel?new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(3);
        Cell cell = row.getCell(3);

        System.out.println(cell.getStringCellValue());
        workbook.close();
        fileInputStream.close();
    }

设置样式

public void test5() throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //合并单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 2, 2, 4);
        HSSFSheet sheet = workbook.createSheet("hello world!");
        sheet.addMergedRegion(cellRangeAddress);
        //设置居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short)20);
        cellStyle.setFont(font);
        //设置背景，先设置背景填充模式
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(HSSFColor.RED.index);

        HSSFRow row = sheet.createRow(2);
        HSSFCell cell = row.createCell(2);
        cell.setCellValue("hello world!");
        cell.setCellStyle(cellStyle);
        FileOutputStream fileOutputStream = new FileOutputStream("d:\\test1.xls");
        workbook.write(fileOutputStream);
        workbook.close();
        fileOutputStream.close();
    }