import java.io.File;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExcelTools {
/**
*
* 设置excel修改批注信息
*
* @param File file 文件
* @param Workbook book
* @param int col 列坐标
* @param int row 行坐标
* @param String text 批注信息
* @return boolean true|false
*/
public synchronized static boolean updateCmment(File file, Workbook book, int col, int row, String text) {
WritableWorkbook wbe = null;
WritableSheet sheet = null;
WritableCell cell = null;
int height;
try {
wbe = Workbook.createWorkbook(file, book); //创建workbook的副本
sheet = wbe.getSheet(0); //获取第一个sheet
cell =sheet.getWritableCell(col, row); //获取第一个单元格
WritableCellFeatures cellFeatures = new WritableCellFeatures(); //创建空的features
if(null != cell.getCellFeatures()){
cell.getCellFeatures().removeComment();
}else{
cell.setCellFeatures(cellFeatures);
}
height = getCommentRows(text, "\r\n");
cellFeatures.setComment(text, 3, height+2);
cell.setCellFeatures(cellFeatures);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}finally{
try {
wbe.write(); //将修改保存到workbook
wbe.close(); //关闭workbook,释放内存
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
*
* 设置excel追加批注信息,保留原来批注
*
* @param File file 文件
* @param Workbook book
* @param int col 列坐标
* @param int row 行坐标
* @param String text 批注信息
* @return boolean true|false
*/
public synchronized static boolean addCmment(File file, Workbook book, int col, int row, String text) {
WritableWorkbook wbe = null;
WritableSheet sheet = null;
WritableCell cell = null;
String oldmark = null;
int height;
try {
wbe = Workbook.createWorkbook(file, book); //创建workbook的副本
sheet = wbe.getSheet(0); //获取第一个sheet
cell =sheet.getWritableCell(col, row); //获取第一个单元格
WritableCellFeatures cellFeatures = new WritableCellFeatures(); //创建空的features
oldmark = cell.getCellFeatures() == null ? null : cell.getCellFeatures().getComment();//原批注
if(null != oldmark){
cell.getCellFeatures().removeComment();
text = oldmark + "\r\n---------------\r\n" + text;
}else{
cell.setCellFeatures(cellFeatures);
}
height = getCommentRows(text, "\r\n");
cellFeatures.setComment(text, 3, height+3);
cell.setCellFeatures(cellFeatures);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}finally{
try {
wbe.write(); //将修改保存到workbook
wbe.close(); //关闭workbook,释放内存
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
*
* 设置删除批注信息
*
* @param File file 文件
* @param Workbook book
* @param int col 列坐标
* @param int row 行坐标
* @return boolean true|false
*/
public synchronized static boolean removeCmment(File file, Workbook book, int col, int row) {
WritableWorkbook wbe = null;
WritableSheet sheet = null;
WritableCell cell = null;
try {
wbe = Workbook.createWorkbook(file, book); //创建workbook的副本
sheet = wbe.getSheet(0); //获取第一个sheet
cell =sheet.getWritableCell(col, row); //获取第一个单元格
WritableCellFeatures cellFeatures = new WritableCellFeatures(); //创建空的features
if(null != cell.getCellFeatures()){
cell.getCellFeatures().removeComment();
}
cell.setCellFeatures(cellFeatures);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}finally{
try {
wbe.write(); //将修改保存到workbook
wbe.close(); //关闭workbook,释放内存
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static int getCommentRows(String str, String findstr){
String regEx = findstr; //要匹配的子串,可以用正则表达式
Pattern p = Pattern.compile(regEx);
Matcher m = p.matcher(str);
int i = 0;
while(m.find()) {
i++;
}
return i;
}
/**
* 测试
*/
public static void main(String[] args) throws Exception {
//添加批注start
File file = new File("D://001311600.xls");
//File file = new File("D://3333.xls");
Workbook book = Workbook.getWorkbook(file);
Sheet sheet = book.getSheet(0);
System.out.println(sheet.getRow(0)[0].getContents());//打印坐标 0,0 文字信息
ExcelTools.addCmment(file, book, 0, 0, "测试787123456789012");
//ExcelTools.updateCmment(file, book, 0, 0, "测试787123456789012\r\n");
//ExcelTools.removeCmment(file, book, 0, 0);
book.close();
}
}
jxl 操作excle 修改 添加 追加批注 java
最新推荐文章于 2023-02-24 14:30:12 发布