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);
}
/**
*
* @param fin原始文件位置
* @param fout 输出文件位置
* @param sheetname SHEET页名称
* @param 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();
}
}
扫一扫
709
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
