/// <summary>
/// Cell comment Finder.
/// Returns cell comment for the specified sheet, row and column.
/// </summary>
/// <param name="sheet">The sheet.</param>
/// <param name="row">The row.</param>
/// <param name="column">The column.</param>
/// <returns>cell comment or
/// <c>null</c>
/// if not found</returns>
public static HSSFComment FindCellComment(Sheet sheet, int row, int column)
{
HSSFComment comment = null;
Dictionary<short, TextObjectRecord> noteTxo = new Dictionary<short, TextObjectRecord>(); //map shapeId and TextObjectRecord
int i=0;
for (IEnumerator it = sheet.Records.GetEnumerator(); it.MoveNext(); )
{
RecordBase rec = (RecordBase)it.Current;
if (rec is NoteRecord)
{
NoteRecord note = (NoteRecord)rec;
if (note.Row == row && note.Column == column)
{
if (i < noteTxo.Count)
{
TextObjectRecord txo = (TextObjectRecord)noteTxo[note.ShapeId];
comment = new HSSFComment(note, txo);
comment.Row = note.Row;
comment.Column = note.Column;
comment.Author = (note.Author);
comment.Visible = (note.Flags == NoteRecord.NOTE_VISIBLE);
comment.String = txo.Str;
break;
}
}
}
else if (rec is ObjRecord)
{
ObjRecord obj = (ObjRecord)rec;
SubRecord sub = (SubRecord)obj.GetSubRecords()[0];
if (sub is CommonObjectDataSubRecord)
{
CommonObjectDataSubRecord cmo = (CommonObjectDataSubRecord)sub;
if (cmo.ObjectType == CommonObjectType.COMMENT)
{
//Find the nearest TextObjectRecord which holds comment's text and map it to its shapeId
while (it.MoveNext())
{
rec = (Record)it.Current;
if (rec is TextObjectRecord)
{
noteTxo.Add(cmo.ObjectId, (TextObjectRecord)rec);
break;
}
}
}
}
}
}
return comment;
}
/// <summary>
/// Removes the comment for this cell, if
/// there is one.
/// </summary>
/// <remarks>WARNING - some versions of excel will loose
/// all comments after performing this action!</remarks>
public void RemoveCellComment()
{
HSSFComment comment = FindCellComment(sheet.Sheet, record.Row, record.Column);
this.comment = null;
if (comment == null)
{
// Nothing to do
return;
}
// Zap the underlying NoteRecord
IList sheetRecords = sheet.Sheet.Records;
sheetRecords.Remove(comment.NoteRecord);
// If we have a TextObjectRecord, is should
// be proceeed by:
// MSODRAWING with container
// OBJ
// MSODRAWING with EscherTextboxRecord
if (comment.TextObjectRecord != null)
{
TextObjectRecord txo = comment.TextObjectRecord;
int txoAt = sheetRecords.IndexOf(txo);
if (sheetRecords[txoAt - 3] is DrawingRecord &&
sheetRecords[txoAt - 2] is ObjRecord &&
sheetRecords[txoAt - 1] is DrawingRecord)
{
// Zap these, in reverse order
sheetRecords.RemoveAt(txoAt - 1);
sheetRecords.RemoveAt(txoAt - 2);
sheetRecords.RemoveAt(txoAt - 3);
}
else
{
throw new InvalidOperationException("Found the wrong records before the TextObjectRecord, can't Remove comment");
}
// Now Remove the text record
sheetRecords.Remove(txo);
}
}