Java POI 创建excel表格，读取excel表格信息

office 2007及以上版本

前提条件，引入jar包

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>

创建excel表格

public class WriteExcel07 {
       @Test  
        public void writeExcel() throws IOException{  
            //创建工作簿  
            XSSFWorkbook workBook = new XSSFWorkbook();  
            //创建工作表  
            XSSFSheet sheet = workBook.createSheet("helloWorld");  
            //创建行  
            XSSFRow row = sheet.createRow(0);  
            //创建单元格，操作第三行第三列  
            XSSFCell cell = row.createCell(0, CellType.STRING);  
            cell.setCellValue("helloWorld");  
              
            FileOutputStream outputStream = new FileOutputStream(new File("C:\\Users\\shay_deng\\Desktop\\test.xlsx"));  
            workBook.write(outputStream);  
              
            workBook.close();//记得关闭工作簿  
        }  
}

读取excel表格信息

public class ReadExcel07 {
    @Test  
    public void readExcel() throws IOException{  
        FileInputStream inputStream = new FileInputStream(new File("C:\\Users\\shay_deng\\Desktop\\test.xlsx"));  
        //读取工作簿  
        XSSFWorkbook workBook = new XSSFWorkbook(inputStream);  
        //读取工作表  
        XSSFSheet sheet = workBook.getSheetAt(0);  
        //读取行  
        XSSFRow row = sheet.getRow(0);  
        //读取单元格  
        XSSFCell cell = row.getCell(0);  
        String value = cell.getStringCellValue();  
          
        System.out.println(value);  
          
        inputStream.close();//关闭工作簿  
        workBook.close();  
    }
}

1.合并单元格，属于工作表，独立创建，应用于工作表

2.样式，属于工作表，由工作簿创建，应用于单元格

3.字体，属于工作表，由工作簿创建，应用于样式

4.设置背景颜色，一定要先设置颜色的填充模式

public class TestPOIExcelStyle {
     @Test  
        public void testExcelStyle() throws IOException{  
            //1.创建工作簿  
            HSSFWorkbook workBook = new HSSFWorkbook();  
              
            //创建合并单元格对象  
            CellRangeAddress rangeAddress = new CellRangeAddress(2, 2, 2, 4);  
            //创建样式  
            HSSFCellStyle style = workBook.createCellStyle();  
            style.setAlignment(HorizontalAlignment.CENTER);  
            style.setVerticalAlignment(VerticalAlignment.CENTER);  
            //创建字体  
            HSSFFont font = workBook.createFont();  
            font.setFontHeightInPoints((short) 16);  
            //font.setFontHeight((short)320); 效果和上面一样。用这个方法设置大小，值要设置为字体大小*20倍，具体看API文档  
            font.setColor(HSSFColor.GREEN.index);  
            font.setBold(true);  
            style.setFont(font);  
            //设置背景  
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);  
            style.setFillForegroundColor(HSSFColor.RED.index);  
              
            //2.创建工作表  
            HSSFSheet sheet = workBook.createSheet("helloWorld");  
            //添加合并区域  
            sheet.addMergedRegion(rangeAddress);  
              
            //3.创建行  
            HSSFRow row = sheet.createRow(2);  
            //4.创建单元格  
            HSSFCell cell = row.createCell(2);  
            cell.setCellValue("helloWorld");  
            cell.setCellStyle(style);  
              
            //输出  
            FileOutputStream outputStream = new FileOutputStream(new File("C:\\Users\\shay_deng\\Desktop\\test.xls"));  
            workBook.write(outputStream);  
              
            workBook.close();  
            outputStream.close();  
        }  
}