测试POI的Excel导出导入

xmlbeans-2.6.0.jar
poi-3.11-20141221.jar
poi-ooxml-3.11-20141221.jar
poi-ooxml-schemas-3.11-20141221.jar

@Test
    public void testCreatePOI03Excel() throws Exception {
        // 1.创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 2.创建工作表
        HSSFSheet sheet = workbook.createSheet("hello world");
        // 3.创建行：创建第三行
        HSSFRow row = sheet.createRow(2);
        // 4.创建单元格：创建第三行第三列
        HSSFCell cell = row.createCell(2);
        cell.setCellValue("hello world");

        // 输出到硬盘
        FileOutputStream outputStream = new FileOutputStream("D:\\buaa\\测试.xls");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    @Test
    public void testReadPOI03Excel() throws Exception {
        FileInputStream inputStream = new FileInputStream("D:\\buaa\\测试.xls");
        // 1.读取工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // 2.读取工作表
        HSSFSheet sheet = workbook.getSheetAt(0);
        // 3.读取行：读取第三行
        HSSFRow row = sheet.getRow(2);
        // 4.读取单元格：读取第三行第三列
        HSSFCell cell = row.getCell(2);
        System.out.println("第三行第三列的内容是：" + cell.getStringCellValue());

        workbook.close();
        inputStream.close();
    }

    @Test
    public void testCreatePOI07Excel() throws Exception {
        // 1.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 2.创建工作表
        XSSFSheet sheet = workbook.createSheet("hello world");
        // 3.创建行：创建第三行
        XSSFRow row = sheet.createRow(2);
        // 4.创建单元格：创建第三行第三列
        XSSFCell cell = row.createCell(2);
        cell.setCellValue("hello world");

        // 输出到硬盘
        FileOutputStream outputStream = new FileOutputStream("D:\\buaa\\测试.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    @Test
    public void testReadPOI07Excel() throws Exception {
        FileInputStream inputStream = new FileInputStream("D:\\buaa\\测试.xlsx");
        // 1.读取工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        // 2.读取工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 3.读取行：读取第三行
        XSSFRow row = sheet.getRow(2);
        // 4.读取单元格：读取第三行第三列
        XSSFCell cell = row.getCell(2);
        System.out.println("第三行第三列的内容是：" + cell.getStringCellValue());

        workbook.close();
        inputStream.close();
    }

    @Test
    public void testReadPOI03And07Excel() throws Exception {
        String fileName = "D:\\buaa\\测试.xls";
        if (fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {// 判断是否Excel文档
            boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");
            FileInputStream inputStream = new FileInputStream(fileName);
            // 1.读取工作簿
            Workbook workbook = is03Excel ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
            // 2.读取工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 3.读取行：读取第三行
            Row row = sheet.getRow(2);
            // 4.读取单元格：读取第三行第三列
            Cell cell = row.getCell(2);
            System.out.println("第三行第三列的内容是：" + cell.getStringCellValue());
            workbook.close();
            inputStream.close();
        }
    }

    @Test
    public void testExcelStyle() throws Exception {
        // 1.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 1.1创建合并单元格，合并第三行的第三列到第五列
        CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 2, 2, 4);
        //1.2创建单元格样式
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); //垂直居中
        //1.3创建字体
        XSSFFont font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); //加粗字体
        font.setFontHeightInPoints((short) 16);
        //加载字体
        style.setFont(font);
        //1.4单元格背景 (此例为07版，03版稍微有些不同)
        //设置背景填充模式
        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        //设置填充背景色
        style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        //设置填充前景色
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        // 2.创建工作表
        XSSFSheet sheet = workbook.createSheet("Hello World");
        // 2.1加载合并单元格对像
        sheet.addMergedRegion(cellRangeAddress);
        // 3.创建行：创建第三行
        XSSFRow row = sheet.createRow(2);
        // 4.创建单元格：创建第三行第三列
        XSSFCell cell = row.createCell(2);
        // 加载样式
        cell.setCellStyle(style);
        cell.setCellValue("Hello World");

        // 输出到硬盘
        FileOutputStream outputStream = new FileOutputStream("D:\\buaa\\测试.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();