Java操作EXCEL文件，利用POI，在EXCEL指定SHEET页中指定文字位置处插入批注
第一种：会覆盖原来的备注
package excel;
import java.io.FileInputStream;
import java.io.FileOutputStream;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellReference;public class ExcelUtils {
public static void main(String[] args) { String fin = "E:\\ceshi\\hello.xls"; String fout = "E:\\ceshi\\ceshi.xls"; String sheetname = "火龙果"; String cellmsg = "AAA"; WriteLetter(fin, fout, sheetname, cellmsg); } /** * * fin原始文件位置 * fout 输出文件位置 * sheetname SHEET页名称 * cellmsg 要查询的文字位置 */ public static void WriteLetter(String fin, String fout, String sheetname, String cellmsg) { try (FileInputStream in = new FileInputStream(fin); FileOutputStream out = new FileOutputStream(fout);) { HSSFWorkbook workbook = new HSSFWorkbook(in);// 拿到文件转化为javapoi可操纵类型 HSSFSheet sheet = null; Cell cellnew = null; Integer rownum = null; Integer colnum = null; for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表 sheet = workbook.getSheetAt(i); if (sheetname.equals(sheet.getSheetName())) { Boolean flag = false; for (Row row : sheet) { if (flag) { break;// 结束行循环 } for (Cell cell : row) { // 单元格的参照 ,根据行和列确定某一个单元格的位置 CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); String cellname = ""; // 得到单元格类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING:// String类型单元格 // 输出文本 cellname = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC:// 数字类型 // 检查单元格是否包含一个Date类型 // 仅仅只检查Excel内部的日期格式, if (DateUtil.isCellDateFormatted(cell)) { // 输出日期 cellname = cell.getDateCellValue().toString(); } else { // 输出数字 cellname = cell.getNumericCellValue() + ""; } break; case Cell.CELL_TYPE_BOOLEAN:// Boolean类型 cellname = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA:// 公式 // 输出公式 cellname = cell.getCellFormula().toString(); break; default: } if (cellmsg.equals(cellname)) { System.out.println("找到指定位置了"); // 打印单元格的位置 System.out.print(cellRef.formatAsString()); rownum = 4;// TODO 计算要加批注的位置，行号和列号 colnum = 4; flag = true; break; } } } break;// 结束sheet循环 } } // 创建绘图对象 HSSFPatriarch p = sheet.createDrawingPatriarch(); // 创建单元格对象,批注插入到4行,1列,B5单元格 // HSSFCell cell=sheet.createRow(4).createCell(1); // 获取指定单元格 // cell=sheet.getRow(cell.getRowIndex()).getCell(6); if (rownum == null || colnum == null) { throw new RuntimeException("查询不到定位文字位置"); } if (sheet.getRow(rownum) == null) { cellnew = sheet.createRow(rownum).createCell(colnum); } else { if (sheet.getRow(rownum).getCell(colnum) == null) { cellnew = sheet.getRow(rownum).createCell(colnum); } else { cellnew = sheet.getRow(rownum).getCell(colnum); } } // 插入单元格内容 // cell.setCellValue(new HSSFRichTextString("批注")); // 获取批注对象 // (int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int // row2) // 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小. HSSFComment comment = p.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); // 输入批注信息 comment.setString(new HSSFRichTextString("插件批注成功!插件批注成功!")); // 添加作者,选中B5单元格,看状态栏 comment.setAuthor("系统自动添加"); // 将批注添加到单元格对象中 cellnew.setCellComment(comment); workbook.write(out); } catch (Exception e) { e.printStackTrace(); } } }
第二种：xlsx格式的文件，不会覆盖原来的备注，xls格式文件会覆盖原来的备注
package excel;
import java.io.FileInputStream; import java.io.FileOutputStream;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; public class CellCommentCeshi { public static void main(String[] args) throws Exception { FileInputStream in = new FileInputStream("E://ceshi//hello.xls"); //1.创建一个工作簿对象 Workbook wb = WorkbookFactory.create(in); //2.得到一个POI的工具类 CreationHelper factory = wb.getCreationHelper(); //3. 创建一个工作表 Sheet sheet = wb.getSheetAt(0); //4.得到一个换图的对象 Drawing drawing = sheet.createDrawingPatriarch(); //5. ClientAnchor是附属在WorkSheet上的一个对象， 其固定在一个单元格的左上角和右下角. ClientAnchor anchor = factory.createClientAnchor(); //6. 创建一个单元格（2A单元格） Cell cell0 = sheet.createRow(1).createCell(0); //6.1. 对这个单元格设置值 cell0.setCellValue("Test"); //6.2. 对这个单元格加上注解 Comment comment0 = drawing.createCellComment(anchor); RichTextString str0 = factory.createRichTextString("Hello, World!"); comment0.setString(str0); comment0.setAuthor("Apache POI"); cell0.setCellComment(comment0); //7. 创建一个单元格（4F单元格） Cell cell1 = sheet.createRow(3).createCell(5); //7.1. 对这个单元格设置值 cell1.setCellValue("F4"); //7.2. 对这个单元格加上注解 Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); //8. 创建一个单元格（4F单元格） Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //9。为注解设置字体 Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short)14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setColumn(2); comment2.setRow(2); //10. 保存成Excel文件 String fname = "E://ceshi//hello2.xls"; FileOutputStream out = new FileOutputStream(fname); wb.write(out); out.close(); } }