如何使用 POI 将单元格注释添加到 Excel 工作表? [英] How to add Cell Comments to Excel sheet using POI?
本文介绍了如何使用 POI 将单元格注释添加到 Excel 工作表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用以下代码生成excel.
...使用此代码(完整运行示例):
import java.io.File;导入 java.io.FileOutputStream;导入 java.io.IOException;导入 java.util.logging.Level;导入 java.util.logging.Logger;导入 org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.ClientAnchor;导入 org.apache.poi.ss.usermodel.Comment;导入 org.apache.poi.ss.usermodel.CreationHelper；导入 org.apache.poi.ss.usermodel.Drawing;导入 org.apache.poi.ss.usermodel.Row;导入 org.apache.poi.ss.usermodel.Sheet;导入 org.apache.poi.ss.usermodel.Workbook;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；公共类 XlsComments {公共静态无效主(字符串 [] args){尝试 {新的 XlsComments().go();} catch (IOException ex) {Logger.getLogger(XlsComments.class.getName()).log(Level.SEVERE, null, ex);}}private void go() 抛出 IOException {工作簿工作簿 = new XSSFWorkbook();//创建工作簿Sheet sheet = workbook.createSheet();//创建工作表Cell cell = getOrCreateCell(sheet, 5, 2);//创建单元格cell.setCellValue("带注释的单元格");//将文本写入单元格//向单元格C6(第5行，第2列)添加注释:addComment(workbook, sheet, 5, 2, "作者", "评论内容");//写入磁盘并关闭工作簿:workbook.write(new FileOutputStream(new File("c:/temp/comments.xlsx")));workbook.close();}public Cell getOrCreateCell(Sheet sheet, int rowIdx, int colIdx) {行行 = sheet.getRow(rowIdx);如果(行==空){row = sheet.createRow(rowIdx);}Cell cell = row.getCell(colIdx);如果(单元格 == 空){单元格 = row.createCell(colIdx);}返回单元格；}public void addComment(Workbook workbook, Sheet sheet, int rowIdx, int colIdx, String author, String commentText) {CreationHelper factory = workbook.getCreationHelper();//获取现有单元格或以其他方式创建它:Cell cell = getOrCreateCell(sheet, rowIdx, colIdx);ClientAnchor 锚点 = factory.createClientAnchor();//我发现在右下角显示评论框很有用anchor.setCol1(cell.getColumnIndex() + 1);//评论框从这个给定的列开始...anchor.setCol2(cell.getColumnIndex() + 3);//...并在给定的列处结束anchor.setRow1(rowIdx + 1);//单元格下方的一行...anchor.setRow2(rowIdx + 5);//... 4 行高绘图绘图 = sheet.createDrawingPatriarch();评论 comment = drawing.createCellComment(anchor);//设置评论文本和作者comment.setString(factory.createRichTextString(commentText));comment.setAuthor(作者);cell.setCellComment(评论);}}请注意在 c:/temp/comments.xlsx 中创建的文件.选择单元格时，作者显示在excel应用程序的左下角.
I am using the following code to generate excel.
http://www.docjar.com/html/api/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.html
import java.io.*;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class BigGridDemo {
private static final String XML_ENCODING = "UTF-8";
public static void main(String[] args) throws Exception {
// Step 1. Create a template file. Setup sheets and workbook-level objects such as
// cell styles, number formats, etc.
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Big Grid");
Map<String, XSSFCellStyle> styles = createStyles(wb);
//name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
String sheetRef = sheet.getPackagePart().getPartName().getName();
//save the template
FileOutputStream os = new FileOutputStream("template.xlsx");
wb.write(os);
os.close();
//Step 2. Generate XML file.
File tmp = File.createTempFile("sheet", ".xml");
Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);
generate(fw, styles);
fw.close();
//Step 3. Substitute the template entry with the generated data
FileOutputStream out = new FileOutputStream("big-grid.xlsx");
substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
out.close();
}
/**
* Create a library of cell styles.
*/
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
XSSFDataFormat fmt = wb.createDataFormat();
XSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
style1.setDataFormat(fmt.getFormat("0.0%"));
styles.put("percent", style1);
XSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style2.setDataFormat(fmt.getFormat("0.0X"));
styles.put("coeff", style2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
style3.setDataFormat(fmt.getFormat("$#,##0.00"));
styles.put("currency", style3);
XSSFCellStyle style4 = wb.createCellStyle();
style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
style4.setDataFormat(fmt.getFormat("mmm dd"));
styles.put("date", style4);
XSSFCellStyle style5 = wb.createCellStyle();
XSSFFont headerFont = wb.createFont();
headerFont.setBold(true);
style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style5.setFont(headerFont);
styles.put("header", style5);
return styles;
}
private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {
Random rnd = new Random();
Calendar calendar = Calendar.getInstance();
SpreadsheetWriter sw = new SpreadsheetWriter(out);
sw.beginSheet();
//insert header row
sw.insertRow(0);
int styleIndex = styles.get("header").getIndex();
sw.createCell(0, "Title", styleIndex);
sw.createCell(1, "% Change", styleIndex);
sw.createCell(2, "Ratio", styleIndex);
sw.createCell(3, "Expenses", styleIndex);
sw.createCell(4, "Date", styleIndex);
sw.endRow();
//write data rows
for (int rownum = 1; rownum < 100000; rownum++) {
sw.insertRow(rownum);
sw.createCell(0, "Hello, " + rownum + "!");
sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
sw.createCell(4, calendar, styles.get("date").getIndex());
sw.endRow();
calendar.roll(Calendar.DAY_OF_YEAR, 1);
}
sw.endSheet();
}
/**
*
* @param zipfile the template file
* @param tmpfile the XML file with the sheet data
* @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
* @param out the stream to write the result to
*/
private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
ZipFile zip = new ZipFile(zipfile);
ZipOutputStream zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if(!ze.getName().equals(entry)){
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
}
}
zos.putNextEntry(new ZipEntry(entry));
InputStream is = new FileInputStream(tmpfile);
copyStream(is, zos);
is.close();
zos.close();
}
private static void copyStream(InputStream in, OutputStream out) throws IOException {
byte[] chunk = new byte[1024];
int count;
while ((count = in.read(chunk)) >=0 ) {
out.write(chunk,0,count);
}
}
/**
* Writes spreadsheet data in a Writer.
* (YK: in future it may evolve in a full-featured API for streaming data in Excel)
*/
public static class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
public SpreadsheetWriter(Writer out){
_out = out;
}
public void beginSheet() throws IOException {
_out.write("<?xml version=\"1.0\" encoding=\""+XML_ENCODING+"\"?>" +
"<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
_out.write("<sheetData>\n");
}
public void endSheet() throws IOException {
_out.write("</sheetData>");
_out.write("</worksheet>");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
public void insertRow(int rownum) throws IOException {
_out.write("<row r=\""+(rownum+1)+"\">\n");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow() throws IOException {
_out.write("</row>\n");
}
public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<is><t>"+value+"</t></is>");
_out.write("</c>");
}
public void createCell(int columnIndex, String value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"n\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<v>"+value+"</v>");
_out.write("</c>");
}
public void createCell(int columnIndex, double value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
}
}
}
How to add Cell Comments using above code?
I have ooxml format for cell comment :
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<authors><author/></authors>
<commentList>
<comment ref="E3" authorId="0">
<text>
<r>
<rPr>
<sz val="10"/>
<rFont val="Arial"/>
</rPr>
<t>Please select appropriate option</t>
</r>
</text>
</comment>
<comment ref="E4" authorId="0">
<text>
<r>
<rPr>
<sz val="10"/>
<rFont val="Arial"/>
</rPr>
<t>Please mark yes against your choice</t>
</r>
</text>
</comment>
</commentList>
</comments>
How to use it?
解决方案
To get something like this...
...use this code (full running example):
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XlsComments {
public static void main(String[] args) {
try {
new XlsComments().go();
} catch (IOException ex) {
Logger.getLogger(XlsComments.class.getName()).log(Level.SEVERE, null, ex);
}
}
private void go() throws IOException {
Workbook workbook = new XSSFWorkbook(); //create workbook
Sheet sheet = workbook.createSheet(); //create sheet
Cell cell = getOrCreateCell(sheet, 5, 2); //create cell
cell.setCellValue("Cell with comment"); //write text to cell
//add comment to cell C6 (row 5, column 2):
addComment(workbook, sheet, 5, 2, "the author", "content of comment");
//write to disc and close workbook:
workbook.write(new FileOutputStream(new File("c:/temp/comments.xlsx")));
workbook.close();
}
public Cell getOrCreateCell(Sheet sheet, int rowIdx, int colIdx) {
Row row = sheet.getRow(rowIdx);
if (row == null) {
row = sheet.createRow(rowIdx);
}
Cell cell = row.getCell(colIdx);
if (cell == null) {
cell = row.createCell(colIdx);
}
return cell;
}
public void addComment(Workbook workbook, Sheet sheet, int rowIdx, int colIdx, String author, String commentText) {
CreationHelper factory = workbook.getCreationHelper();
//get an existing cell or create it otherwise:
Cell cell = getOrCreateCell(sheet, rowIdx, colIdx);
ClientAnchor anchor = factory.createClientAnchor();
//i found it useful to show the comment box at the bottom right corner
anchor.setCol1(cell.getColumnIndex() + 1); //the box of the comment starts at this given column...
anchor.setCol2(cell.getColumnIndex() + 3); //...and ends at that given column
anchor.setRow1(rowIdx + 1); //one row below the cell...
anchor.setRow2(rowIdx + 5); //...and 4 rows high
Drawing drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(anchor);
//set the comment text and author
comment.setString(factory.createRichTextString(commentText));
comment.setAuthor(author);
cell.setCellComment(comment);
}
}
Please note the created file in c:/temp/comments.xlsx.
The author is displayed in the lower left corner of the excel application when the cell is selected.
这篇关于如何使用 POI 将单元格注释添加到 Excel 工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
