如何在C#中使用NPOI Excel添加单元格注释？

Question

我希望使用C#中的NPOI Excel创建单元格注释。我没有找到任何明确的文档。我自己写了一些代码，如下所示：

NPOI.HSSF.Record.NoteRecord nr = new NPOI.HSSF.Record.NoteRecord();
nr.Author = "Some Author";
NPOI.HSSF.Record.TextObjectRecord tor = new NPOI.HSSF.Record.TextObjectRecord();
tor.Str = new HSSFRichTextString("something");

HSSFComment cm = new HSSFComment(nr, tor);
cm.Visible = true;

sheet.GetRow(i).Cells[k + 8].CellComment = cm;   

那段代码没有正常工作。在生成的Excel文件中，我看不到任何单元格上的注释。有人知道如何在特定单元格中添加注释吗？

- aanilapaydin

Answer 1

你需要使用绘画保护者来创建单元格注释。然后你可以定义作者和文本。你也可以应用一些字体自定义。

请尝试这段代码，我已经对不同的步骤进行了注释:

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue("Cell1");

// Create the drawing patriarch (top level container for all shapes including cell comments)
IDrawing patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

// Client anchor defines size and position of the comment in the worksheet
IComment comment = patriarch.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 4));

// Set comment author
comment.Author = "Author";

// Set text in the comment
comment.String = new HSSFRichTextString($"{comment.Author}:{Environment.NewLine}A comment");

// If you want the author displayed in bold on top like in Excel
// The author will be displayed in the status bar when on mouse over the commented cell
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
comment.String.ApplyFont(0, comment.Author.Length, font);

// Set comment visible
comment.Visible = true;

// Assign comment to a cell
cell.CellComment = comment;

using (MemoryStream exportData = new MemoryStream())
{
    workbook.Write(exportData);
    Response.ContentEncoding = Encoding.UTF8;
    Response.Charset = Encoding.UTF8.EncodingName;
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("content-disposition", $"attachment; filename=test.xls");
    Response.Clear();
    Response.BinaryWrite(exportData.GetBuffer());
    Response.End();
}

参考资料：

• NPOI HSSF示例 - 单元格注释

- krlzlx

---

谢谢krlzlx。我已经用patriarch处理了它。 - aanilapaydin

Answer 2

由于某些原因，在我的情况下，这行代码

comment.String.ApplyFont(0, comment.Author.Length, font);

没有按预期工作 - 如果我只想要加粗评论的作者，它会将整个评论字符串都加粗。

这是我的解决方案：

IWorkbook workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("sheet name");
var patriarch = sheet.CreateDrawingPatriarch();
var commentAuthor = "ABC:";
comment = patriarch.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 2, 4, 13, 26));

IFont boldFont = (XSSFFont)workbook.CreateFont();
boldFont.IsBold = true;

IFont regularFont = (XSSFFont)workbook.CreateFont();

var str = new XSSFRichTextString();
str.Append(commentAuthor, boldFont);
str.Append("some text", regularFont);
comment.String = str;
row.GetCell(k).CellComment = comment;

- Vladimir Krasko