一篇文章搞定Java处理Excel表格的各种疑难杂症

小咪咪 2023-03-14 05:24 497阅读 0赞

### 文章目录 ###

*   *  简介
     *  结构
     *  Maven
     *  Gradle
     *  扩展组件知识
     *  快速体验
     *   *  创建Excel
         *  读取Excel
     *  开发指南
     *   *  读取Excel方法对比
         *  读取Excel的所有Sheet
         *   *  方式一
             *  方式二
         *  读取单元格的值
         *  提取Excel表所有单元格内容
         *  Cell支持的基础数据类型
         *  日期格式化
         *  单元格对齐
         *  设置单元格边框样式
         *  设置单元格的属性
         *  单元格填充
         *  合并单元格
         *  自定义字体样式
         *  特殊设置
         *   *  sheet放大或缩小
             *  冻结窗口
             *  切割窗口
         *  添加图片
         *  调整列宽适应内容
         *  读取超链接内容
         *  创建超链接
         *  限制单元格的输入值
         *   *  方式一
             *  方式二：下拉选项框

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70]  
文章教程源码可以关注上方微信公众号： ***程序员小兔*** 回复关键字： **poi**，即可免费获取。

## 简介 ##

Apache POI是[Apache软件基金会][Apache]的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。官网网站：[http://poi.apache.org/index.html][http_poi.apache.org_index.html]

## 结构 ##

<table> 
 <thead> 
  <tr> 
   <th>对象</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>HSSF</td> 
   <td>提供读写[Microsoft Excel](https://baike.baidu.com/item/Microsoft Excel) <a href="https://baike.baidu.com/item/XLS" rel="nofollow">XLS</a>格式档案的功能</td> 
  </tr> 
  <tr> 
   <td>XSSF</td> 
   <td>提供读写Microsoft Excel OOXML <a href="https://baike.baidu.com/item/XLSX" rel="nofollow">XLSX</a>格式档案的功能</td> 
  </tr> 
  <tr> 
   <td>HWPF</td> 
   <td>提供读写[Microsoft Word](https://baike.baidu.com/item/Microsoft Word) <a href="https://baike.baidu.com/item/DOC" rel="nofollow">DOC</a>格式档案的功能</td> 
  </tr> 
  <tr> 
   <td>HSLF</td> 
   <td>提供读写Microsoft <a href="https://baike.baidu.com/item/PowerPoint" rel="nofollow">PowerPoint</a>格式档案的功能</td> 
  </tr> 
  <tr> 
   <td>HDGF</td> 
   <td>提供读写[Microsoft Visio](https://baike.baidu.com/item/Microsoft Visio)格式档案的功能</td> 
  </tr> 
  <tr> 
   <td>HPBF</td> 
   <td>提供读写[Microsoft Publisher](https://baike.baidu.com/item/Microsoft Publisher)格式档案的功能</td> 
  </tr> 
  <tr> 
   <td>HSMF</td> 
   <td>提供读写[Microsoft Outlook](https://baike.baidu.com/item/Microsoft Outlook)格式档案的功能</td> 
  </tr> 
 </tbody> 
</table>

## Maven ##

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    
    
    
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>

## Gradle ##

// Excel的2003版本Jar
    // https://mvnrepository.com/artifact/org.apache.poi/poi
    compile group: 'org.apache.poi', name: 'poi', version: '4.1.2'
    
    // Excel的2007版本Jar
    // https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
    compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'

## 扩展组件知识 ##

Apache POI项目是用于基于Microsoft的OLE 2复合文档格式开发的纯Java端口的主项目。该项目发行版本还包括对于许多文档文件格式的支持。下表展示了POI组件，Maven存储库标签和项目的jar文件之间的关系。

<table> 
 <thead> 
  <tr> 
   <th>组件</th> 
   <th>应用类型</th> 
   <th>Maven artifactId</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>POIFS</td> 
   <td>OLE2 Filesystem</td> 
   <td><em>poi</em></td> 
  </tr> 
  <tr> 
   <td>HPSF</td> 
   <td>OLE2 Property Sets</td> 
   <td><em>poi</em></td> 
  </tr> 
  <tr> 
   <td>HSSF</td> 
   <td>Excel XLS</td> 
   <td><em>poi</em></td> 
  </tr> 
  <tr> 
   <td>HSLF</td> 
   <td>PowerPoint PPT</td> 
   <td><em>poi-scratchpad</em></td> 
  </tr> 
  <tr> 
   <td>HWPF</td> 
   <td>Word DOC</td> 
   <td><em>poi-scratchpad</em></td> 
  </tr> 
  <tr> 
   <td>HDGF</td> 
   <td>Visio VSD</td> 
   <td><em>poi-scratchpad</em></td> 
  </tr> 
  <tr> 
   <td>HPBF</td> 
   <td>Publisher PUB</td> 
   <td><em>poi-scratchpad</em></td> 
  </tr> 
  <tr> 
   <td>HSMF</td> 
   <td>Outlook MSG</td> 
   <td><em>poi-scratchpad</em></td> 
  </tr> 
  <tr> 
   <td>DDF</td> 
   <td>Escher common drawings</td> 
   <td><em>poi</em></td> 
  </tr> 
  <tr> 
   <td>HWMF</td> 
   <td>WMF drawings</td> 
   <td><em>poi-scratchpad</em></td> 
  </tr> 
  <tr> 
   <td>OpenXML4J</td> 
   <td>OOXML</td> 
   <td><em>poi-ooxml-schemas</em> <strong>or</strong> <em>ooxml-schemas</em> <strong>and</strong> <em>ooxml-security</em></td> 
  </tr> 
  <tr> 
   <td>XSSF</td> 
   <td>Excel XLSX</td> 
   <td><em>poi-ooxml</em></td> 
  </tr> 
  <tr> 
   <td>XSLF</td> 
   <td>PowerPoint PPTX</td> 
   <td><em>poi-ooxml</em></td> 
  </tr> 
  <tr> 
   <td>XWPF</td> 
   <td>Word DOCX</td> 
   <td><em>poi-ooxml</em></td> 
  </tr> 
  <tr> 
   <td>XDGF</td> 
   <td>Visio VSDX</td> 
   <td><em>poi-ooxml</em></td> 
  </tr> 
  <tr> 
   <td>Common SL</td> 
   <td>PowerPoint PPT and PPTX</td> 
   <td><em>poi-scratchpad</em> **and ** <em>poi-ooxml</em></td> 
  </tr> 
  <tr> 
   <td>Common SS</td> 
   <td>Excel XLS and XLSX</td> 
   <td><em>poi-ooxml</em></td> 
  </tr> 
 </tbody> 
</table>

## 快速体验 ##

### 创建Excel ###

// 创建工作簿
            Workbook wb = new HSSFWorkbook(); //new XSSFWorkbook(); 分别是07和03对象
            // 创建工作表,在工作簿对象中创建才可以，这里创建2个
            Sheet sheet1 = wb.createSheet("工作表1");
            Sheet sheet2 = wb.createSheet("工作表2");
            sheet1.createRow(0).createCell(0).setCellValue("第1行，第1个单元格数据");
            sheet1.createRow(1).createCell(0).setCellValue("第2行，第1个单元格数据");
            try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                // 将创建好的Excel表格写出
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][20200515204017913.jpg]

### 读取Excel ###

这里使用上面 *创建Excel* 输出的Excel表格作为数据源读取数据。

try (InputStream fileInput = new FileInputStream("workbook.xls")) { 
                // 根据输入流创建工作簿
                Workbook wb = new HSSFWorkbook(fileInput);
                // 读取第一个工作表
                Sheet sheet1 = wb.getSheetAt(0);
                // 读取第一行第一个单元格的信息
                String value1 = sheet1.getRow(0).getCell(0).getStringCellValue();
                System.out.println(MessageFormat.format("第一行第一个单元格信息:{0}", value1));
                // 读取第二行第一个单元格的信息
                String value2 = sheet1.getRow(1).getCell(0).getStringCellValue();
                System.out.println(MessageFormat.format("第二行第一个单元格信息:{0}", value2));
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][2020051520404323.jpg]

## 开发指南 ##

### 读取Excel方法对比 ###

打开工作簿时，无论是.xls还是.xlsx格式都可以从File或InputStream加载工作簿。使用File对象可以降低内存消耗，而InputStream则需要更多的内存，因为它必须缓冲整个Excel文件。

读取Excel的时候建议使用`WorkbookFactory`对象，这个对象可以识别.xls和.xlsx两种格式的Excel。

// 使用 File 方法加载Excel
            Workbook wb1 = WorkbookFactory.create(new File("workbook.xls"));
    
            // 使用 InputStream 方法加载Excel,这种方式需要更多的内存,如果Excel表很大的时候将占用服务器性能
            Workbook wb2 = WorkbookFactory.create(new FileInputStream("workbook.xls"));

上面的方式加载Excel是无法控制生命周期，所以建议使用一下方法加载Excel。`POIFSFileSystem`和`OPCPackage`对象来控制生命周期。

// HSSFWorkbook File方式
            POIFSFileSystem fs1 = new POIFSFileSystem(new File("workbook.xls"));
            Workbook wb1 = new HSSFWorkbook(fs1.getRoot(), true);
    		// do something
            // 关闭
            fs1.close();
    
            // HSSFWorkbook InputStream方式
            POIFSFileSystem fs2 = new POIFSFileSystem(new FileInputStream("workbook.xls"));
            Workbook wb2 = new HSSFWorkbook(fs2.getRoot(), true);
    		// do something
            // 关闭
    
            // XSSFWorkbook File方式
            OPCPackage open1 = OPCPackage.open(new File("workbook.xls"));
            Workbook wb3 = new XSSFWorkbook(open1);
    		// do something
            // 关闭
            open1.close();
    
            // XSSFWorkbook InputStream方式
            OPCPackage open2 = OPCPackage.open(new FileInputStream("workbook.xls"));
            Workbook wb4 = new XSSFWorkbook(open2);
    		// do something
            // 关闭
            open2.close();

`WorkbookFactory`对象的`create`方法也支持`POIFSFileSystem`和`OPCPackage`参数。

### 读取Excel的所有Sheet ###

#### 方式一 ####

第一种方式是使用自带的`sheetIterator`/`rowIterator`/`cellIterator`迭代器的方式获得数据。这种方式会跳过空行和空单元格。

try (Workbook wb = WorkbookFactory.create(new File("workbook.xls"))) { 
                Iterator<Sheet> iterator = wb.sheetIterator();
                // 遍历多个Shell
                while (iterator.hasNext()) { 
                    Iterator<Row> rowIterator = iterator.next().rowIterator();
                    // 遍历多个Row
                    while (rowIterator.hasNext()) { 
                        // 方式一：拿到row对象
                        Row row = rowIterator.next();
                        String value = row.getCell(0).getStringCellValue();
    
                        // 方式二：获取每一个单元格的值
                        Iterator<Cell> cellIterator = rowIterator.next().cellIterator();
                        // 遍历每一列
                        while (cellIterator.hasNext()) { 
                            Cell cell = cellIterator.next();
                        }
                    }
                }
            } catch (InvalidFormatException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

#### 方式二 ####

第二种方式是使用`for-each`的方式隐式遍历。这种方式会跳过空行和空单元格。

try (Workbook wb = WorkbookFactory.create(new File("workbook.xls"))) { 
                for (Sheet sheet : wb) { 
                    for (Row row : sheet) { 
                        for (Cell cell : row) { 
                            
                        }
                    }
                }
            } catch (InvalidFormatException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

### 读取单元格的值 ###

获取单元格的内容，首先需要知道单元格的类型。如果向 *字符串* 单元格获取 *数值* 的内容。那么就会抛出`NumberFormatException`异常。因此需要先确定单元格的类型，才通过`getter`方法获取单元格的内容。

// 创建一个数据格式化对象
            DataFormatter formatter = new DataFormatter();
            try (Workbook wb = new HSSFWorkbook(new FileInputStream("workbook.xls"))) { 
                Sheet sheet = wb.getSheetAt(0);
                for (Row row : sheet) { 
                    for (Cell cell : row) { 
                        // 获取单元格的编号如A1,B1,A2,B2
                        CellReference cellReference = new CellReference(row.getRowNum(), cell.getColumnIndex());
                        System.out.print(cellReference.formatAsString());
                        System.out.print(" - ");
    
                        // 格式单元格的值，作为字符串返回，不要用这个方法读取值，因为精度只保留4位
                        String text = formatter.formatCellValue(cell);
                        System.out.println(text);
                        // 区分不同类型的单元格数值格式，分别处理
                        switch (cell.getCellTypeEnum()) { 
                            case STRING: // 字符串类型
                                System.out.println(cell.getRichStringCellValue().getString());
                                break;
                            case NUMERIC: // 数值类型
                                // 如果是日期类型的双精度，另外判断是否为日期类型
                                if (DateUtil.isCellDateFormatted(cell)) { 
                                    System.out.println(cell.getDateCellValue());
                                } else { 
                                    System.out.println(cell.getNumericCellValue());
                                }
                                break;
                            case BOOLEAN: // 布尔值类型
                                System.out.println(cell.getBooleanCellValue());
                                break;
                            case FORMULA: // 公式类型
                                System.out.println(cell.getCellFormula());
                                break;
                            case BLANK: // 空字符串
                                System.out.println();
                                break;
                            default:
                                System.out.println();
                        }
                    }
                }
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

### 提取Excel表所有单元格内容 ###

对于Excel表的单元格内容提取，之前的做法就是遍历`Row`和`Cell`。POI提供了方法一次性读取整个Excel表的所有内容。

try (InputStream inp = new FileInputStream("workbook.xls");
                 HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp))) { 
                ExcelExtractor extractor = new ExcelExtractor(wb);
                extractor.setFormulasNotResults(true);
                extractor.setIncludeSheetNames(false);
                String text = extractor.getText();
                System.out.println(text);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

### Cell支持的基础数据类型 ###

cell除了支持基础的数据类型外，还支持输出公式、图片、下拉菜单等特殊的单元格样式。下面会持续讲解。对于Cell中的内容如果需要换行，可以使用`\n`符号。

// 创建工作簿
            Workbook wb = new HSSFWorkbook(); //new XSSFWorkbook(); 分别是07和03对象
            // 创建工作表,在工作簿对象中创建才可以，这里创建2个
            Sheet sheet1 = wb.createSheet("工作表1");
            Row row1 = sheet1.createRow(0);
            // 数值类型
            row1.createCell(0).setCellValue(100.10);
            // 日期类型，将日期的值设置为等价的Excel表的值。
            // 如果不设置日期格式，那么就是日期将变为数值
            row1.createCell(1).setCellValue(new Date());
            // 设置单元格的日期值
            row1.createCell(2).setCellValue(Calendar.getInstance());
            // 字符串类型
            row1.createCell(3).setCellValue("string text");
            // 布尔值类型
            row1.createCell(4).setCellValue(true);
            try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                // 将创建好的Excel表格写出
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][20200515204135335.jpg]

### 日期格式化 ###

// 创建工作簿
            Workbook wb = new HSSFWorkbook(); //new XSSFWorkbook(); 分别是07和03对象
            // 创建工作表
            Sheet sheet1 = wb.createSheet("工作表1");
            // 日期不格式化输出默认样式
            Row row1 = sheet1.createRow(0);
            row1.createCell(0).setCellValue(new Date());
    
            // --------------------------S 自定义单元格格式[方式1]--------------------------
            // 创建一个单元格样式，那个单元格需要这个样式就设置到那个单元格
            CellStyle cellStyle1 = wb.createCellStyle();
            // 从workbook中创建DateFormat对象
            DataFormat dataFormat = wb.createDataFormat();
            // 给cellStyle表格样式设置好日期格式类型
            cellStyle1.setDataFormat(dataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
    
            // 使用上面创建好的cellStyle样式格式化时间
            Cell cell1 = row1.createCell(1);
            cell1.setCellStyle(cellStyle1);
            cell1.setCellValue(new Date());
            // --------------------------E 自定义单元格格式[方式1]--------------------------
    
            // --------------------------S 自定义单元格格式[方式2]--------------------------
            // 获取一个创建者助手，这个对象可以处理WorkBook的所需的各种类实例化
            CreationHelper creationHelper = wb.getCreationHelper();
            // 创建一个日期格式对象
            DataFormat dfm = creationHelper.createDataFormat();
            // 创建一个单元格格式化对象
            CellStyle cellStyle2 = wb.createCellStyle();
            cellStyle2.setDataFormat(dfm.getFormat("m/d/yy h:mm"));
    
            // 设置日期
            Cell cell2 = row1.createCell(2);
            cell2.setCellStyle(cellStyle2);
            cell2.setCellValue(new Date());
            // --------------------------E 自定义单元格格式[方式2]--------------------------
            try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                // 将创建好的Excel表格写出
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][20200515204157382.jpg]

### 单元格对齐 ###

单元格样式主要由`CellStyle`对象来控制。其中单元格对齐主要由`setAlignment` *水平对齐* 和`setVerticalAlignment` *垂直对齐* 两个参数控制。

// 抽取公共方法
        private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) { 
            Cell cell = row.createCell(column);
            cell.setCellValue("Vertical And Align It");
            // 创建单元格样式
            CellStyle cellStyle = wb.createCellStyle();
            // 设置水平对齐方式
            cellStyle.setAlignment(halign);
            // 设置垂直对齐方式
            cellStyle.setVerticalAlignment(valign);
            cell.setCellStyle(cellStyle);
        }

Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row row = sheet.createRow(1);
            // 将行高设置为50
            row.setHeightInPoints(50);
            // 创建不同的对齐方式，具体的组合以及字段含义可以查询官方API,下面是列出了常用的组合
            createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
            createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
            createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
            createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
            createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
            createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
            createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
            createCell(wb, row, 7, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
            // Write the output to a file
            try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }
            wb.close();

**结果**  
![在这里插入图片描述][20200515204223986.jpg]

### 设置单元格边框样式 ###

Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row row = sheet.createRow(1);
            Cell cell = row.createCell(1);
            cell.setCellValue("string text");
            // 单元格的边框CellStyle支持的方法有8个
            // 分别是“上“”下“”左“”右”的边框类型和颜色
            // 创建一个单元格样式
            CellStyle cellStyle = wb.createCellStyle();
            // 左 边框的类型 破折号+圆点
            cellStyle.setBorderLeft(BorderStyle.DASH_DOT);
            // 右 边框的类型 破折号
            cellStyle.setBorderRight(BorderStyle.DASHED);
            // 上 边框的类型 圆点
            cellStyle.setBorderTop(BorderStyle.DOTTED);
            // 下 边框的类型 双线条
            cellStyle.setBorderBottom(BorderStyle.DOUBLE);
            // 左 边框的颜色
            cellStyle.setLeftBorderColor(IndexedColors.AQUA.index);
            // 右 边框的颜色
            cellStyle.setRightBorderColor(IndexedColors.AUTOMATIC.index);
            // 上 边框的颜色
            cellStyle.setTopBorderColor(IndexedColors.BLUE.index);
            // 下 边框的颜色
            cellStyle.setBottomBorderColor(IndexedColors.DARK_BLUE.index);
    
            cell.setCellStyle(cellStyle);
            try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }
            wb.close();

**结果**  
![在这里插入图片描述][20200515204237723.jpg]

### 设置单元格的属性 ###

设置单元格的属性其实就是另外一种方法给单元格添加样式。

Workbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            Map<String, Object> properties = new HashMap<String, Object>();
            // 单元格周边的边框样式
            properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM);
            properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM);
            properties.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM);
            properties.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM);
            // 单元格边框的颜色
            properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex());
            properties.put(CellUtil.BOTTOM_BORDER_COLOR, IndexedColors.RED.getIndex());
            properties.put(CellUtil.LEFT_BORDER_COLOR, IndexedColors.RED.getIndex());
            properties.put(CellUtil.RIGHT_BORDER_COLOR, IndexedColors.RED.getIndex());
            // 将B2设置为这个样式
            Row row = sheet.createRow(1);
            Cell cell = row.createCell(1);
            cell.setCellValue("String text");
            CellUtil.setCellStyleProperties(cell, properties);
            try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                // 将创建好的Excel表格写出
                workbook.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 1]

### 单元格填充 ###

单元格的填充两种：背景填充，前景填充。其中的填充风格可以看`FillPatternType`类。填充的颜色可以看`IndexedColors`类。

try (Workbook wb = new HSSFWorkbook();
                 OutputStream out = new FileOutputStream("workbook.xls")) { 
                Sheet sheet = wb.createSheet("sheet 0");
                Row row = sheet.createRow(1);
                // 创建单元格背景填充样式
                CellStyle cellStyle1 = wb.createCellStyle();
                // 设置填充颜色
                cellStyle1.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
                // 设置填充样式
                cellStyle1.setFillPattern(FillPatternType.BIG_SPOTS);
                Cell cell1 = row.createCell(1);
                cell1.setCellStyle(cellStyle1);
                cell1.setCellValue("填充背景颜色");
    
                // 创建单元格前景色填充样式
                CellStyle cellStyle2 = wb.createCellStyle();
                // 设置填充颜色
                cellStyle2.setFillForegroundColor(IndexedColors.AQUA.getIndex());
                // 设置填充样式
                cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                Cell cell2 = row.createCell(2);
                cell2.setCellStyle(cellStyle2);
                cell2.setCellValue("填充前景颜色");
    
                // 生成Excel表格
                wb.write(out);
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][20200515204325474.jpg]

### 合并单元格 ###

合并单元格需要操作多行多列，而包含多行多列的对象就是`Sheet`。所有合并单元格是对`Sheet`操作。

try (Workbook wb = new HSSFWorkbook();
                 OutputStream out = new FileOutputStream("workbook.xls")) { 
                Sheet sheet = wb.createSheet("sheet 0");
                Row row = sheet.createRow(1);
                Cell cell = row.createCell(1);
                cell.setCellValue("Merging Cell");
    
                // 合并单元格，需要4个参数，分别是上下左右 4个坐标，坐标都是从0开始计算
                // 合并单元格操作是在Sheet对象中操作的，因为只有Sheet才有多个行和多个列
                // 创建单元格合并地址，4个参数依次：开始行，结束行，开始列，结束列
                CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 1, 2);
                // 给Sheet添加合并单元格对象
                sheet.addMergedRegion(cellRangeAddress);
    
                // 更加便捷的方式合并单元格，只需要对角的坐标就可以
                CellRangeAddress cellRangeAddress1 = CellRangeAddress.valueOf("D1:E4");
                sheet.addMergedRegion(cellRangeAddress1);
    
                // 对于合并了的单元格，有工具类很方便的设置边框样式和颜色
                RegionUtil.setBorderBottom(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet);
                RegionUtil.setBorderTop(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet);
                RegionUtil.setBorderLeft(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet);
                RegionUtil.setBorderRight(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet);
                RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet);
                RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet);
                RegionUtil.setLeftBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet);
                RegionUtil.setRightBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet);
    
                // 生成Excel表格
                wb.write(out);
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][2020051520434175.jpg]

### 自定义字体样式 ###

字体的设置主要是根据Excel表中存在的属性去配置。

try (Workbook wb = new HSSFWorkbook();
                 OutputStream out = new FileOutputStream("workbook.xls")) { 
                Sheet sheet = wb.createSheet("sheet 0");
                Row row = sheet.createRow(1);
                Cell cell = row.createCell(1);
    
                // 创建一个新的字体,并添加到工作簿的字体中
                Font font = wb.createFont();
                // 颜色
                font.setColor(IndexedColors.BLUE.getIndex());
                // 高度
                font.setFontHeightInPoints((short) 32);
                // 给字体一个名字
                font.setFontName("chun qiu");
                // 加粗
                font.setBold(true);
                // 斜体
                font.setItalic(true);
    
                // 所有的单元格样式都要通过这个对象配置
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFont(font);
    
                cell.setCellStyle(cellStyle);
                cell.setCellValue("Create Cell Fonts");
    
                // 生成Excel表格
                wb.write(out);
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 2]  
**扩展：自定义颜色**

`HSSFWorkbook`中`getCustomPalette`方法可以获取到一个调色板类`HSSFColor`，这个类的`addColor`方法接受三个参数，分别是*红*、*绿*、*蓝*三个色调。

`XSSFWorkbook`中则是通过`XSSFFont`的`setColor(XSSFColor xssfColor)`方法定义*红*、*绿*、*蓝*三个色调。

### 特殊设置 ###

#### sheet放大或缩小 ####

sheet1.setZoom(75); // 缩放为75%

#### 冻结窗口 ####

// 参数一：分割的水平位置
    // 参数二：分割的垂直位置
    sheet1.createFreezePane(3, 2);
    // 参数一：分割的水平位置
    // 参数二：分割的垂直位置
    // 参数三：左列在右窗口中可见
    // 参数四：顶部在底部窗口中可见
    sheet1.createFreezePane(3, 2, 3, 2);

#### 切割窗口 ####

// 将一个Excel切割为2个一样的，数据同步
    // 参数一：切割的水平位置
    // 参数二：切割的垂直位置
    // 参数三：顶部在做底部可见
    // 参数四：左边在右边可见
    // 参数五：活动面板。可选值：PANE_LOWER_RIGHT, PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
    sheet2.createSplitPane(2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT);

### 添加图片 ###

添加图片是绘画工具支持的，添加图片使用`createPicture`方法。目前支持的图片类型有：PNG/JPG/DIB。

图片读取使用`workbook.getAllPictures`方法。

try (Workbook wb = new HSSFWorkbook();
                 InputStream is = new FileInputStream("fill-cell.jpg");
                 OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                // 添加图片数据到工作簿中
                int picture = wb.addPicture(IOUtils.toByteArray(is), Workbook.PICTURE_TYPE_JPEG);
    
                CreationHelper helper = wb.getCreationHelper();
                Sheet sheet = wb.createSheet();
    
                // 创建一个绘画工具
                Drawing drawing = sheet.createDrawingPatriarch();
                // 添加一个绘画的区域
                ClientAnchor anchor = helper.createClientAnchor();
    
                // 图片左上角坐标（第二行，第三列开始）
                anchor.setRow1(2);
                anchor.setCol1(3);
    
                Picture picture1 = drawing.createPicture(anchor, picture);
    
                // 自动调整图片左上角大小
                picture1.resize();
    
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 3]

### 调整列宽适应内容 ###

Sheet sheet = workbook.getSheetAt(0);
    sheet.autoSizeColumn(0); // 调整第一类宽度自适应内容
    sheet.autoSizeColumn(1); // 调整第二类宽度自适应内容

### 读取超链接内容 ###

Sheet sheet = workbook.getSheetAt(0);
    Cell cell = sheet.getRow(0).getCell(0);
    Hyperlink link = cell.getHyperlink();
    if(link != null){ 
        System.out.println(link.getAddress());
    }

### 创建超链接 ###

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    
    // 创建URL超链接
    cell = sheet.createRow(0).createCell(0);
    cell.setCellValue("URL Link");
    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("https://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    
    // 创建链接到本地目录文件的链接
    cell = sheet.createRow(1).createCell(0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    
    // 邮箱的链接
    cell = sheet.createRow(2).createCell(0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    
    // 笔记，如果有文本请编码
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    
    // 链接到工作簿中的一个位置
    // 创建一个工作表
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell(0).setCellValue("Target Cell");
    cell = sheet.createRow(3).createCell(0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);
    try (OutputStream out = new FileOutputStream("hyperinks.xlsx")) { 
        wb.write(out);
    }
    wb.close();

### 限制单元格的输入值 ###

#### 方式一 ####

try (HSSFWorkbook wb = new HSSFWorkbook();
                 OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                HSSFSheet sheet = wb.createSheet("数据校验");
                // 校验的单元格范围
                /** * 参数1：起始行 * 参数2：结束行 * 参数3：起始列 * 参数4：结束列 */
                CellRangeAddressList addressList = new CellRangeAddressList(
                        0, 0, 0, 0);
                // 创建校验单元格的取值范围
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[]{ "10", "20", "30"});
                // 设置校验的单元格和取值范围的关系
                DataValidation dataValidation = new HSSFDataValidation
                        (addressList, dvConstraint);
                // 用于列表校验对象
                dataValidation.setSuppressDropDownArrow(true);
                // 设置错误的提示样式,STOP一旦填错就不允许操作其他单元格，只能继续填写正确
                dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                // 设置错误提示表格内容
                dataValidation.createErrorBox("错误提示标题", "你的输入有误");
                // 鼠标出现再单元格的时候出现这个提示
                dataValidation.createPromptBox("内容输入的值提示标题：", "你只可以输入[10,20,30]");
                // 允许提示框出现
                dataValidation.setShowPromptBox(true);
                sheet.addValidationData(dataValidation);
    
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

**结果**  
![在这里插入图片描述][20200515204458634.jpg]  
![在这里插入图片描述][20200515204510785.jpg]

#### 方式二：下拉选项框 ####

限制单元格输入的值，并且提供一个下拉选项框给用户选择。

try (HSSFWorkbook wb = new HSSFWorkbook();
                 OutputStream fileOut = new FileOutputStream("workbook.xls")) { 
                HSSFSheet sheet = wb.createSheet("下拉列表");
                // 校验的单元格范围
                /** * 参数1：起始行 * 参数2：结束行 * 参数3：起始列 * 参数4：结束列 */
                CellRangeAddressList addressList = new CellRangeAddressList(
                        0, 0, 0, 0);
                // 创建校验单元格的取值范围
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[]{ "10", "20", "30"});
                // 用于列表校验对象
                DataValidation dataValidation = new HSSFDataValidation
                        (addressList, dvConstraint);
                // 设置错误的提示样式,STOP一旦填错就不允许操作其他单元格，只能继续填写正确
                dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);
    
                wb.write(fileOut);
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70]  
文章教程源码可以关注上方微信公众号：***程序员小兔*** 回复关键字：**poi**，即可免费获取。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70]: /images/20230312/0536075b85304d11bfb33f413d01be1e.png
[Apache]: https://baike.baidu.com/item/Apache%E8%BD%AF%E4%BB%B6%E5%9F%BA%E9%87%91%E4%BC%9A
[http_poi.apache.org_index.html]: http://poi.apache.org/components/index.html
[20200515204017913.jpg]: /images/20230312/6d104e1b90cd40ae873c36d3287a6400.png
[2020051520404323.jpg]: /images/20230312/912f2dee986742e4b80bacabbd644baf.png
[20200515204135335.jpg]: /images/20230312/140c123b471d492e89ea3aad494f9411.png
[20200515204157382.jpg]: /images/20230312/250029641b994a5bb833cb77511532c9.png
[20200515204223986.jpg]: /images/20230312/0fa2fae000f6452b9e9e31b7164d6f6a.png
[20200515204237723.jpg]: /images/20230312/f2b9be98570a4348a788ba2875d9d495.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 1]: /images/20230312/55da2c24d74343f7887096fd211725e9.png
[20200515204325474.jpg]: /images/20230312/f94c830274b44a3e8e0228fc1452dad0.png
[2020051520434175.jpg]: /images/20230312/33aa95bce9704f74b2d5bde5d73a76f0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 2]: /images/20230312/500dfc2a619345e5b8fd11ab2f147fdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzMyMzY2MzI5_size_16_color_FFFFFF_t_70 3]: /images/20230312/b11dbaa766db42b7a2fb46d1f8e33f3b.png
[20200515204458634.jpg]: /images/20230312/f09606f293e146bfbf2b81c1773d4feb.png
[20200515204510785.jpg]: /images/20230312/28d33376a8674ef5b513400149229d2d.png