POI实战-java开发excel详解之常用操作-注释

4.常用操作

4.1注释

4.1.1 单表注释

POI支持Excel单元格加注释功能。创建注释需要通过sheet表去创建类org.apache.poi.hssf.usermodel.HSSFPatriarch，HSSFPatriarch实例再通过HSSFClientAnchor去创建HSSFComment。

HSSFClientAnchor类需要设置大小与位置、注释内容、作者等，以下为HSSFComment参数说明：

参数	说明
dx1	第1个单元格中x轴的偏移量
dy1	第1个单元格中y轴的偏移量
dx2	第2个单元格中x轴的偏移量
dy2	第2个单元格中y轴的偏移量
col1	第1个单元格的列号
row1	第1个单元格的行号
col2	第2个单元格的列号
row2	第2个单元格的行号

 

下面在之前输出的Excel上创建注释，以D列、F列、K列、M列、O列的各列序号加入注释。

代码片段：

public static HSSFWorkbook write(InputStream inputStream) throws IOException, ClassNotFoundException{             //初始一个workbook             HSSFWorkbook workbook = new HSSFWorkbook(inputStream);             List<Student> list = Conn.getData();             //创建一个sheet             HSSFSheet sheet = workbook.getSheetAt(0);             //创建本sheet的HSSFPatriarch             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();             for(int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++){                   HSSFRow row = sheet.getRow(rowIndex);                   for(int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++){                         HSSFCell cell = row.getCell(cellnum);                         if(cell == null){                               continue;                         }                         switch (cellnum) {                         case 3:                               addComment(patriarch, cell, cellnum);                               break;                         case 5:                               addComment(patriarch, cell, cellnum);                               break;                         case 10:                               addComment(patriarch, cell, cellnum);                               break;                         case 12:                               addComment(patriarch, cell, cellnum);                               break;                         case 14:                               addComment(patriarch, cell, cellnum);                               break;                                                 }                   }             }             return workbook;                   }             /**        * 设置单元格注释        * @param patriarch        * @param cell        * @param cellnum        */       public static void addComment(HSSFPatriarch patriarch, HSSFCell cell, int cellnum){             HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)1, 2, (short)4, 4));             comment.setString(new HSSFRichTextString(String.valueOf(cellnum)));             cell.setCellComment(comment);       }

输入结果：

图16

 

4.1.2 多表注释

在此多表注释开发中需要注意：一个sheet表应共用一个HSSFPatriarch实例，通过HSSFPatriarch可以创建出sheet上的注释，如果一个Excel表中有多个sheet，那么根据每个sheet去分别实例化该sheet上的HSSFPatriarch，然后分别去创建各个sheet上的注释。

将上面的excel数据再复制出2个sheet，然后按照上面的方式加注释。

代码片段：

public static HSSFWorkbook write(InputStream inputStream) throws IOException, ClassNotFoundException{             //初始一个workbook             HSSFWorkbook workbook = new HSSFWorkbook(inputStream);             List<Student> list = Conn.getData();             //循环创建sheet             for(int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++){                   HSSFSheet sheet = workbook.getSheetAt(sheetIndex);                   //创建本个sheet的HSSFPatriarch                   HSSFPatriarch patriarch = sheet.createDrawingPatriarch();                   for(int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++){                         HSSFRow row = sheet.getRow(rowIndex);                         for(int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++){                               HSSFCell cell = row.getCell(cellnum);                               if(cell == null){                                     continue;                               }                               switch (cellnum) {                               case 3:                                     addComment(patriarch, cell, cellnum);                                     break;                               case 5:                                     addComment(patriarch, cell, cellnum);                                     break;                               case 10:                                     addComment(patriarch, cell, cellnum);                                     break;                               case 12:                                     addComment(patriarch, cell, cellnum);                                     break;                               case 14:                                     addComment(patriarch, cell, cellnum);                                     break;                                                             }                         }                   }             }             return workbook;                   }

输出结果如下：

图17

4.1.3 空单元格注释

空单元格加注释，需要先创建一个单元格实例，可以设置成CELL_TYPE_BLANK或者不设置，然后再加上注释。

代码片段：

public static HSSFWorkbook write(InputStream inputStream) throws IOException, ClassNotFoundException{             //初始一个workbook             HSSFWorkbook workbook = new HSSFWorkbook(inputStream);             //获取第一张sheet             HSSFSheet sheet = workbook.getSheetAt(0);             //创建此表的HSSFPatriarch实例             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();             //创建一行             HSSFRow row = sheet.createRow(0);             //创建一个空单元格             HSSFCell cell = row.createCell(0,HSSFCell.CELL_TYPE_BLANK);             //添加注释             addComment(patriarch, cell, 0);             return workbook;       }

输出结果:

图18