public void AttemptToSave2CommentsWithSameCoordinates()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
ICreationHelper factory = wb.GetCreationHelper();
IDrawing patriarch = sh.CreateDrawingPatriarch();
patriarch.CreateCellComment(factory.CreateClientAnchor());
try
{
patriarch.CreateCellComment(factory.CreateClientAnchor());
_testDataProvider.WriteOutAndReadBack(wb);
Assert.Fail("Expected InvalidOperationException(found multiple cell comments for cell $A$1");
}
catch (InvalidOperationException e)
{
// HSSFWorkbooks fail when writing out workbook
Assert.AreEqual(e.Message, "found multiple cell comments for cell A1");
}
catch (ArgumentException e)
{
// XSSFWorkbooks fail when creating and setting the cell address of the comment
Assert.AreEqual(e.Message, "Multiple cell comments in one cell are not allowed, cell: A1");
}
finally
{
wb.Close();
}
}
private IComment insertComment(IDrawing Drawing, ICell cell, String message)
{
ICreationHelper factory = cell.Sheet.Workbook.GetCreationHelper();
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (/*setter*/ cell.ColumnIndex);
anchor.Col2 = (/*setter*/ cell.ColumnIndex + 1);
anchor.Row1 = (/*setter*/ cell.RowIndex);
anchor.Row2 = (/*setter*/ cell.RowIndex + 1);
anchor.Dx1 = (/*setter*/ 100);
anchor.Dx2 = (/*setter*/ 100);
anchor.Dy1 = (/*setter*/ 100);
anchor.Dy2 = (/*setter*/ 100);
IComment comment = Drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString(message);
comment.String = (/*setter*/ str);
comment.Author = (/*setter*/ "fanfy");
cell.CellComment = (/*setter*/ comment);
return(comment);
}
private void SetRowRepeatedErrorStyle(ISheet sheet, IDrawing commentDrawing, List <List <RepeatRow> > rowGroups, string errorPrompt)
{
foreach (var rows in rowGroups)
{
foreach (var repeatRow in rows)
{
var row = sheet.GetRow(repeatRow.RowIndex);
var setedComment = false;
foreach (var columnIndex in repeatRow.ColumnIndexes)
{
var cell = row.GetCell(columnIndex);
cell.CellStyle = this._rowRepeatedErrorStyle;
if (setedComment)
{
continue;
}
if (cell.CellComment == null)
{
cell.CellComment = commentDrawing.CreateCellComment(this._commentAnchor);
}
cell.CellComment.String = this._commentFactory.CreateRichTextString($"{errorPrompt}.重复行：{string.Join(",", rows.Where(m => m.RowIndex != repeatRow.RowIndex).Select(m => m.RowIndex + 1))}");
setedComment = true;
}
}
}
}
/// <summary>
/// 设置批注
/// </summary>
/// <param name="cell"></param>
/// <param name="suffix"></param>
/// <param name="comment"></param>
/// <param name="col1"></param>
/// <param name="row1"></param>
/// <param name="col2"></param>
/// <param name="row2"></param>
public static void SetCellComment(this ICell cell, CommentEntity entitiy)
{
ISheet sheet = cell.Sheet;
IClientAnchor clientAnchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();
clientAnchor.AnchorType = AnchorType.MoveDontResize.GetType().ToInt();
clientAnchor.Dx1 = entitiy.Dx1;
clientAnchor.Dy1 = entitiy.Dy1;
clientAnchor.Dx2 = entitiy.Dx2;
clientAnchor.Dy2 = entitiy.Dy2;
clientAnchor.Col1 = cell.ColumnIndex;
clientAnchor.Row1 = cell.RowIndex;
clientAnchor.Col2 = cell.ColumnIndex + entitiy.Width;
clientAnchor.Row2 = cell.RowIndex + entitiy.Height;
IDrawing draw = sheet.CreateDrawingPatriarch();
IComment comment = draw.CreateCellComment(clientAnchor);
comment.Visible = false;
if (sheet.Workbook is HSSFWorkbook)
{
comment.String = new HSSFRichTextString(entitiy.Text);
}
else
{
comment.String = new XSSFRichTextString(entitiy.Text);
}
cell.CellComment = comment;
}
public void TestQuickGuide()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper factory = wb.GetCreationHelper();
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(3).CreateCell(5);
cell.SetCellValue("F4");
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = factory.CreateClientAnchor();
IComment comment = drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString("Hello, World!");
comment.String = (str);
comment.Author = ("Apache POI");
//assign the comment to the cell
cell.CellComment = (comment);
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0);
cell = sheet.GetRow(3).GetCell(5);
comment = cell.CellComment;
Assert.IsNotNull(comment);
Assert.AreEqual("Hello, World!", comment.String.String);
Assert.AreEqual("Apache POI", comment.Author);
Assert.AreEqual(3, comment.Row);
Assert.AreEqual(5, comment.Column);
}
public void GetCellComment()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
ICreationHelper factory = wb.GetCreationHelper();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(1);
// cell does not have a comment
Assert.IsNull(cell.CellComment);
// add a cell comment
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 1;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
IDrawing drawing = sheet.CreateDrawingPatriarch();
IComment comment = drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString("Hello, World!");
comment.String = str;
comment.Author = "Apache POI";
cell.CellComment = comment;
// ideally assertSame, but XSSFCell creates a new XSSFCellComment wrapping the same bean for every call to getCellComment.
Assert.AreEqual(comment, cell.CellComment);
wb.Close();
}
/// <summary>
/// 设置批注
/// </summary>
/// <param name="cell"></param>
/// <param name="errorMsg"></param>
/// <param name="excelGlobalDTO"></param>
public void SetCellComment(ICell cell, string errorMsg, ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
ICreationHelper facktory = cell.Row.Sheet.Workbook.GetCreationHelper();
if (cell.CellComment == null)
{
//创建批注区域
IDrawing patr = cell.Row.Sheet.CreateDrawingPatriarch();
var anchor = facktory.CreateClientAnchor();
//设置批注区间大小
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 2;
//设置列
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + 3;
cell.CellComment = patr.CreateCellComment(anchor);
}
if (excelGlobalDTO.ExcelVersionEnum == ExcelVersionEnum.V2003)
{
cell.CellComment.String = new HSSFRichTextString(errorMsg);//2003批注方式
}
else
{
cell.CellComment.String = new XSSFRichTextString(errorMsg);//2007批准方式
}
cell.CellComment.Author = "yank";
}
private static void PostilAdd(Tk5ListMetaData metaInfos, ImportError importError, IWorkbook workBook, ISheet sheet)
{
IDrawing part = sheet.CreateDrawingPatriarch();
Dictionary <string, int> indexOfName = new Dictionary <string, int>();
int i = 0;
foreach (var info in metaInfos.Table.TableList)
{
indexOfName.Add(info.DisplayName, i);
i++;
}
foreach (var err in importError)
{
IRow row = sheet.GetRow(err.IndexOfRow);
IComment comment = null;
ICell cell = row.GetCell(indexOfName[err.ColumnName]);
ICreationHelper factory = workBook.GetCreationHelper();
IClientAnchor anchor = null;
anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex + 2;
anchor.Col2 = cell.ColumnIndex + 4;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
comment = part.CreateCellComment(anchor);
comment.Author = "mitu";
comment.String = new HSSFRichTextString(err.ErrorMsg);
cell.CellComment = comment;
}
}
public void WriteRead()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithVariousData.xlsx");
XSSFSheet sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
XSSFSheet sheet2 = (XSSFSheet)workbook.GetSheetAt(1);
Assert.IsTrue(sheet1.HasComments);
Assert.IsFalse(sheet2.HasComments);
// Change on comment on sheet 1, and add another into
// sheet 2
IRow r5 = sheet1.GetRow(4);
IComment cc5 = r5.GetCell(2).CellComment;
cc5.Author = ("Apache POI");
cc5.String = (new XSSFRichTextString("Hello!"));
IRow r2s2 = sheet2.CreateRow(2);
ICell c1r2s2 = r2s2.CreateCell(1);
Assert.IsNull(c1r2s2.CellComment);
IDrawing dg = sheet2.CreateDrawingPatriarch();
IComment cc2 = dg.CreateCellComment(new XSSFClientAnchor());
cc2.Author = ("Also POI");
cc2.String = (new XSSFRichTextString("A new comment"));
c1r2s2.CellComment = (cc2);
// Save, and re-load the file
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
// Check we still have comments where we should do
sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
sheet2 = (XSSFSheet)workbook.GetSheetAt(1);
Assert.IsNotNull(sheet1.GetRow(4).GetCell(2).CellComment);
Assert.IsNotNull(sheet1.GetRow(6).GetCell(2).CellComment);
Assert.IsNotNull(sheet2.GetRow(2).GetCell(1).CellComment);
// And check they still have the contents they should do
Assert.AreEqual("Apache POI",
sheet1.GetRow(4).GetCell(2).CellComment.Author);
Assert.AreEqual("Nick Burch",
sheet1.GetRow(6).GetCell(2).CellComment.Author);
Assert.AreEqual("Also POI",
sheet2.GetRow(2).GetCell(1).CellComment.Author);
Assert.AreEqual("Hello!",
sheet1.GetRow(4).GetCell(2).CellComment.String.String);
}
public void GetAddress()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
ICreationHelper factory = wb.GetCreationHelper();
IDrawing patriarch = sh.CreateDrawingPatriarch();
IComment comment = patriarch.CreateCellComment(factory.CreateClientAnchor());
Assert.AreEqual(CellAddress.A1, comment.Address);
ICell C2 = sh.CreateRow(1).CreateCell(2);
C2.CellComment = comment;
Assert.AreEqual(new CellAddress("C2"), comment.Address);
}
public static IComment AddComment(IDrawing patr, ICell cell, IRow headerRow, string value)
{
var anchor = new XSSFClientAnchor();
anchor.Col1 = cell.ColumnIndex + 1;
anchor.Col2 = cell.ColumnIndex + 3;
anchor.Row1 = headerRow.RowNum + 1;
anchor.Row2 = headerRow.RowNum + 5;
IComment comment = patr.CreateCellComment(anchor);
comment.String = (new XSSFRichTextString(value));
return(comment);
}
/// <summary>
/// 增加备注
/// </summary>
/// <param name="Sheetindex"></param>
/// <param name="RowIndex"></param>
/// <param name="CellIndex"></param>
/// <param name="value"></param>
/// <param name="commentStr">备注信息</param>
public void SetCellCommentInXls(int Sheetindex, int RowIndex, int CellIndex, string value, string commentStr)
{
InitializeWorkbook();
ISheet sheet = hssfworkbook.GetSheetAt(Sheetindex);
IDrawing patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
ICell cell1 = sheet.CreateRow(RowIndex).CreateCell(CellIndex);
cell1.SetCellValue(new HSSFRichTextString(value));
//anchor defines size and position of the comment in worksheet
IComment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));
// set text in the comment
comment1.String = (new HSSFRichTextString(commentStr));
// The first way to assign comment to a cell is via HSSFCell.SetCellComment method
cell1.CellComment = (comment1);
#region old
////Create another cell in row 6
//ICell cell2 = sheet.CreateRow(6).CreateCell(1);
//cell2.SetCellValue(value);
//HSSFComment comment2 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));
////modify background color of the comment
//comment2.SetFillColor(204, 236, 255);
//HSSFRichTextString str = new HSSFRichTextString("Normal body temperature");
////apply custom font to the text in the comment
//IFont font = hssfworkbook.CreateFont();
//font.FontName = ("Arial");
//font.FontHeightInPoints = 10;
//font.Boldweight = (short)FontBoldWeight.BOLD;
//font.Color = HSSFColor.RED.index;
//str.ApplyFont(font);
//comment2.String = str;
//comment2.Visible = true; //by default comments are hidden. This one is always visible.
//comment2.Author = "Bill Gates";
///**
// * The second way to assign comment to a cell is to implicitly specify its row and column.
// * Note, it is possible to set row and column of a non-existing cell.
// * It works, the commnet is visible.
// */
//comment2.Row = 6;
//comment2.Column = 1;
#endregion
WriteToFile();
}
/// <summary>
/// 添加批注
/// </summary>
/// <param name="exCell">单元格</param>
/// <param name="commentAuthor">批注作者</param>
/// <param name="commentContent">批注内容</param>
public static void AddComment(ICell exCell, string commentAuthor, string commentContent)
{
IWorkbook book = exCell.Sheet.Workbook;
//IDrawing draw = exCell.Sheet.DrawingPatriarch ?? exCell.Sheet.CreateDrawingPatriarch();
IDrawing draw = exCell.Sheet.CreateDrawingPatriarch();
IComment comment = draw.CreateCellComment(draw.CreateAnchor(0, 0, 255, 255, exCell.ColumnIndex + 1, exCell.RowIndex + 1,
exCell.ColumnIndex + 3, exCell.RowIndex + 4));
comment.Author = commentAuthor;
comment.String = book.GetCreationHelper().CreateRichTextString(commentContent);
//用于解决XSSF批注不显示的问题
comment.String.ApplyFont(book.CreateFont());
//comment.Column = exCell.ColumnIndex;
//comment.Row = exCell.RowIndex;
exCell.CellComment = comment;
}
/// <summary>
/// 通过批注写入单元格的导入状态
/// </summary>
/// <param name="row">行</param>
/// <param name="col">列号</param>
/// <param name="message">状态消息</param>
/// <param name="isError">是否错误(否则显示为警告)</param>
protected void WriteCellState(IRow row, int col, string message, bool isError)
{
var cell = row.SafeGetCell(col);
if (_drawingPatriarch == null)
{
_drawingPatriarch = Sheet.CreateDrawingPatriarch();
}
var msg = $"{(isError ? "错误" : "警告")}\r\n{message}";
var comment = _drawingPatriarch.CreateCellComment(new XSSFClientAnchor(0, 0, 1, 1, col, row.RowNum, col + 3, row.RowNum + 3));
comment.String = new XSSFRichTextString(msg);
comment.Column = col;
comment.Row = row.RowNum;
cell.CellComment = comment;
//cell.CellStyle.FillForegroundColor = isError ? HSSFColor.Red.Index : HSSFColor.Yellow.Index;
}
/// <summary>
/// 创建批注
/// 创建者：戚鹏
/// 创建日期：2013.5.20
/// </summary>
/// <param name="sheet"></param>
/// <param name="Comment"></param>
/// <returns></returns>
private static IComment GetCellComment(ISheet sheet, string strComment)
{
if (sheet != null)
{
IDrawing patr = sheet.CreateDrawingPatriarch();
ICreationHelper helper = sheet.Workbook.GetCreationHelper();
var anchor = helper.CreateClientAnchor();
anchor.AnchorType = 2;
IComment comment = patr.CreateCellComment(anchor);
comment.String = helper.CreateRichTextString(strComment);
return(comment);
}
else
{
return(null);
}
}
public void testBug58175a()
{
IWorkbook wb = new SXSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(3);
cell.SetCellValue("F4");
IDrawing drawing = sheet.CreateDrawingPatriarch();
ICreationHelper factory = wb.GetCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex + 1);
anchor.Row1 = (row.RowNum);
anchor.Row2 = (row.RowNum + 3);
// Create the comment and set the text+author
IComment comment = drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString("Hello, World!");
comment.String = (str);
comment.Author = ("Apache POI");
/* fixed the problem as well
* comment.setColumn(cell.ColumnIndex);
* comment.setRow(cell.RowIndex);
*/
// Assign the comment to the cell
cell.CellComment = (comment);
FileStream out1 = new FileStream("C:\\temp\\58175.xlsx", FileMode.CreateNew, FileAccess.ReadWrite);
try
{
wb.Write(out1);
}
finally
{
out1.Close();
}
}
finally
{
wb.Close();
}
}
public void GetCellComment()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
IDrawing dg = sheet.CreateDrawingPatriarch();
IComment comment = dg.CreateCellComment(workbook.GetCreationHelper().CreateClientAnchor());
ICell cell = sheet.CreateRow(9).CreateCell(2);
comment.Author = (/*setter*/ "test C10 author");
cell.CellComment = (/*setter*/ comment);
Assert.IsNotNull(sheet.GetCellComment(9, 2));
Assert.AreEqual("test C10 author", sheet.GetCellComment(9, 2).Author);
Assert.IsNotNull(_testDataProvider.WriteOutAndReadBack(workbook));
workbook.Close();
}
private void SetSheetDataErrorStyle(ISheet sheet, IDrawing commentDrawing, IEnumerable <SheetRow> errorRows)
{
foreach (var item in errorRows)
{
foreach (var errorColumn in item.ColumnIndexError)
{
var cell = sheet.GetRow(item.RowIndex).GetCell(errorColumn.Key) ?? sheet.GetRow(item.RowIndex).CreateCell(errorColumn.Key);
SetCellErrorStyle(cell, _dataErrorStyle);
if (cell.CellComment == null)
{
cell.CellComment = commentDrawing.CreateCellComment(this._commentAnchor);
}
cell.CellComment.String = _commentFactory.CreateRichTextString(errorColumn.Value);
}
}
}
/// <summary>
/// 导出excel模板
/// </summary>
/// <param name="fieldList">Item1是字段名称 Item2是字段的备注</param>
/// <param name="isxls"></param>
/// <returns></returns>
public static byte[] ToExcel(List <Tuple <string, string> > fieldList, bool isxls = false)
{
IWorkbook workbook = CreateWorkbook(isxls);
ICellStyle headerCellStyle = GetCellStyle(workbook, true, isxls);
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < fieldList.Count; i++)
{
var item = fieldList[i];
ICell headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(item.Item1);
if (!string.IsNullOrEmpty(item.Item2))
{
IDrawing patriarch = sheet.CreateDrawingPatriarch();
IComment comment = isxls ? patriarch.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 4)) : patriarch.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 4));
comment.Author = "Admin";
if (isxls)
{
comment.String = new HSSFRichTextString(item.Item2);
}
else
{
comment.String = new XSSFRichTextString(item.Item2);
}
comment.Visible = false;
headerCell.CellComment = comment;
}
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
}
byte[] xlsInBytes;
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
xlsInBytes = ms.ToArray();
}
return(xlsInBytes);
}
public void SetAddress()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
ICreationHelper factory = wb.GetCreationHelper();
IDrawing patriarch = sh.CreateDrawingPatriarch();
IComment comment = patriarch.CreateCellComment(factory.CreateClientAnchor());
Assert.AreEqual(CellAddress.A1, comment.Address);
CellAddress C2 = new CellAddress("C2");
Assert.AreEqual("C2", C2.FormatAsString());
comment.Address = C2;
Assert.AreEqual(C2, comment.Address);
CellAddress E10 = new CellAddress(9, 4);
Assert.AreEqual("E10", E10.FormatAsString());
comment.SetAddress(9, 4);
Assert.AreEqual(E10, comment.Address);
}
// Set the comment on a sheet
//
private static void setComment(ISheet sheet, ICell cell, IDrawing drawing, String commentText, ICreationHelper helper, IClientAnchor anchor)
{
//System.out.println("Setting col: " + cell.getColumnIndex() + " and row " + cell.getRowIndex());
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex);
anchor.Row1 = (cell.RowIndex);
anchor.Row2 = (cell.RowIndex);
// get comment, or create if it does not exist
// NOTE - only occurs if getCellComment is called first
IComment comment = cell.CellComment;
//Comment comment = null;
if (comment == null)
{
comment = drawing.CreateCellComment(anchor);
}
comment.Author = ("Test");
// attach the comment to the cell
comment.String = (helper.CreateRichTextString(commentText));
cell.CellComment = (comment);
}
// Set the comment on a sheet
//
private static void setComment(ISheet sheet, ICell cell, IDrawing drawing, String commentText, ICreationHelper helper, IClientAnchor anchor)
{
//System.out.println("Setting col: " + cell.getColumnIndex() + " and row " + cell.getRowIndex());
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex);
anchor.Row1 = (cell.RowIndex);
anchor.Row2 = (cell.RowIndex);
// get comment, or create if it does not exist
// NOTE - only occurs if getCellComment is called first
IComment comment = cell.CellComment;
//Comment comment = null;
if (comment == null)
{
comment = drawing.CreateCellComment(anchor);
}
comment.Author = ("Test");
// attach the comment to the cell
comment.String = (helper.CreateRichTextString(commentText));
cell.CellComment = (comment);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("some comments");
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
IDrawing patr = sheet.CreateDrawingPatriarch();
//Create a cell in row 3
ICell cell1 = sheet.CreateRow(3).CreateCell(1);
cell1.SetCellValue(new XSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
IComment comment1 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));
// set text in the comment
comment1.String = (new XSSFRichTextString("We can set comments in POI"));
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.Author = ("Apache Software Foundation");
// The first way to assign comment to a cell is via HSSFCell.SetCellComment method
cell1.CellComment = (comment1);
//Create another cell in row 6
ICell cell2 = sheet.CreateRow(6).CreateCell(1);
cell2.SetCellValue(36.6);
IComment comment2 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));
//modify background color of the comment
//comment2.SetFillColor(204, 236, 255);
XSSFRichTextString str = new XSSFRichTextString("Normal body temperature");
//apply custom font to the text in the comment
IFont font = workbook.CreateFont();
font.FontName = ("Arial");
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.BOLD;
font.Color = HSSFColor.RED.index;
str.ApplyFont(font);
comment2.String = str;
comment2.Visible = true; //by default comments are hidden. This one is always visible.
comment2.Author = "Bill Gates";
/**
* The second way to assign comment to a cell is to implicitly specify its row and column.
* Note, it is possible to set row and column of a non-existing cell.
* It works, the commnet is visible.
*/
comment2.Row = 6;
comment2.Column = 1;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
private IComment insertComment(IDrawing Drawing, ICell cell, String message)
{
ICreationHelper factory = cell.Sheet.Workbook.GetCreationHelper();
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (/*setter*/cell.ColumnIndex);
anchor.Col2 = (/*setter*/cell.ColumnIndex + 1);
anchor.Row1 = (/*setter*/cell.RowIndex);
anchor.Row2 = (/*setter*/cell.RowIndex + 1);
anchor.Dx1 = (/*setter*/100);
anchor.Dx2 = (/*setter*/100);
anchor.Dy1 = (/*setter*/100);
anchor.Dy2 = (/*setter*/100);
IComment comment = Drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString(message);
comment.String = (/*setter*/str);
comment.Author = (/*setter*/"fanfy");
cell.CellComment = (/*setter*/comment);
return comment;
}
public void Run()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("some comments");
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
IDrawing patr = sheet.CreateDrawingPatriarch();
//Create a cell in row 3
ICell cell1 = sheet.CreateRow(3).CreateCell(1);
cell1.SetCellValue(new XSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
IComment comment1 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));
// set text in the comment
comment1.String = new XSSFRichTextString("We can set comments in POI");
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.Author = "Apache Software Foundation";
// The first way to assign comment to a cell is via HSSFCell.SetCellComment method
cell1.CellComment = comment1;
//Create another cell in row 6
ICell cell2 = sheet.CreateRow(6).CreateCell(1);
cell2.SetCellValue(36.6);
IComment comment2 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));
//modify background color of the comment
//comment2.SetFillColor(204, 236, 255);
XSSFRichTextString str = new XSSFRichTextString("Normal body temperature");
//apply custom font to the text in the comment
IFont font = workbook.CreateFont();
font.FontName = "Arial";
font.FontHeightInPoints = 10;
font.IsBold = true;
font.Color = HSSFColor.Red.Index;
str.ApplyFont(font);
comment2.String = str;
comment2.Visible = true; //by default comments are hidden. This one is always visible.
comment2.Author = "Bill Gates";
/**
* The second way to assign comment to a cell is to implicitly specify its row and column.
* Note, it is possible to set row and column of a non-existing cell.
* It works, the commnet is visible.
*/
comment2.Row = 6;
comment2.Column = 1;
sheet.CreateRow(0).CreateCell(1).SetCellValue(123);
sheet.Header.Left = HSSFHeader.Page; //Page is a static property of HSSFHeader and HSSFFooter
sheet.Header.Center = "This is a test sheet";
//set footer text
sheet.Footer.Left = "Copyright NPOI Team";
sheet.Footer.Right = "created by Tony Qu（瞿杰）";
using (var fs = File.Create(@"C:\00.Dev\temp\CreateCommentInXlsx.xlsx"))
{
workbook.Write(fs);
}
}
public void GetClientAnchor()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(10);
ICell cell = row.CreateCell(5);
ICreationHelper factory = wb.GetCreationHelper();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
double r_mul, c_mul;
if (sheet is HSSFSheet)
{
double rowheight = Units.ToEMU(row.HeightInPoints) / Units.EMU_PER_PIXEL;
r_mul = 256.0 / rowheight;
double colwidth = sheet.GetColumnWidthInPixels(2);
c_mul = 1024.0 / colwidth;
}
else
{
r_mul = c_mul = Units.EMU_PER_PIXEL;
}
int dx1 = (int)Math.Round(10 * c_mul);
int dy1 = (int)Math.Round(10 * r_mul);
int dx2 = (int)Math.Round(3 * c_mul);
int dy2 = (int)Math.Round(4 * r_mul);
int col1 = cell.ColumnIndex + 1;
int row1 = row.RowNum;
int col2 = cell.ColumnIndex + 2;
int row2 = row.RowNum + 1;
IClientAnchor anchor = Drawing.CreateAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
IComment comment = Drawing.CreateCellComment(anchor);
comment.Visible = (/*setter*/ true);
cell.CellComment = (/*setter*/ comment);
anchor = comment.ClientAnchor;
Assert.AreEqual(dx1, anchor.Dx1);
Assert.AreEqual(dy1, anchor.Dy1);
Assert.AreEqual(dx2, anchor.Dx2);
Assert.AreEqual(dy2, anchor.Dy2);
Assert.AreEqual(col1, anchor.Col1);
Assert.AreEqual(row1, anchor.Row1);
Assert.AreEqual(col2, anchor.Col2);
Assert.AreEqual(row2, anchor.Row2);
anchor = factory.CreateClientAnchor();
comment = Drawing.CreateCellComment(anchor);
cell.CellComment = (/*setter*/ comment);
anchor = comment.ClientAnchor;
if (sheet is HSSFSheet)
{
Assert.AreEqual(0, anchor.Col1);
Assert.AreEqual(0, anchor.Dx1);
Assert.AreEqual(0, anchor.Row1);
Assert.AreEqual(0, anchor.Dy1);
Assert.AreEqual(0, anchor.Col2);
Assert.AreEqual(0, anchor.Dx2);
Assert.AreEqual(0, anchor.Row2);
Assert.AreEqual(0, anchor.Dy2);
}
else
{
// when anchor is Initialized without parameters, the comment anchor attributes default to
// "1, 15, 0, 2, 3, 15, 3, 16" ... see XSSFVMLDrawing.NewCommentShape()
Assert.AreEqual(1, anchor.Col1);
Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx1);
Assert.AreEqual(0, anchor.Row1);
Assert.AreEqual(2 * Units.EMU_PER_PIXEL, anchor.Dy1);
Assert.AreEqual(3, anchor.Col2);
Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx2);
Assert.AreEqual(3, anchor.Row2);
Assert.AreEqual(16 * Units.EMU_PER_PIXEL, anchor.Dy2);
}
}
public static byte[] ExcelReport(string strFileName, Tk5ListMetaData metaInfos, ImportError importError)
{
MemoryStream ms = new MemoryStream();
ExcelExporter exporter =
new ExcelExporter(false, ExcelContentFormat.DefaultHeader, ExcelContentFormat.DefaultContent, metaInfos);
using (ms)
{
string sheetName = metaInfos.Table.TableDesc;
//HSSFWorkbook newWorkbook = exporter.CreateWorkbookTemplate();
//HSSFSheet newSheet = (HSSFSheet)newWorkbook.CreateSheet(sheetName);
IWorkbook workBook = null;
ISheet sheet = null;
Dictionary <string, int> indexOfName = new Dictionary <string, int>();
int i = 0;
foreach (var info in metaInfos.Table.TableList)
{
indexOfName.Add(info.DisplayName, i);
i++;
}
string fileExt = Path.GetExtension(strFileName);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
workBook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
workBook = new XSSFWorkbook(file);
}
}
sheet = workBook.GetSheet(sheetName);
IDrawing part = sheet.CreateDrawingPatriarch();
var res = (from err in importError
select err.IndexOfRow).Distinct();
int[] arr = new int[sheet.LastRowNum];
for (int index = sheet.FirstRowNum + 1, j = 0; index <= sheet.LastRowNum; index++)
{
arr[j] = index;
j++;
}
int[] arrExc = arr.Except(res).ToArray();
foreach (var err in importError)
{
IRow row = sheet.GetRow(err.IndexOfRow);
IComment comment = null;
ICell cell = row.GetCell(indexOfName[err.ColumnName]);
ICreationHelper factory = workBook.GetCreationHelper();
IClientAnchor anchor = null;
anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex + 2;
anchor.Col2 = cell.ColumnIndex + 4;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
comment = part.CreateCellComment(anchor);
comment.Author = "mitu";
comment.String = new HSSFRichTextString(err.ErrorMsg);
cell.CellComment = comment;
}
int counter = 0;
foreach (var rowNum in res)
{
sheet.ShiftRows(rowNum, rowNum, 1 - rowNum + counter);
counter++;
}
for (int rowNum = counter + 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow row = sheet.GetRow(rowNum);
sheet.RemoveRow(row);
}
workBook.Write(ms);
ms.Flush();
byte[] filedata = ms.ToArray();
using (FileStream fs = new FileStream(@"C:\Users\zll\Downloads\ImportReport.xls", FileMode.Create))
{
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(filedata);
bw.Close();
fs.Close();
}
return(filedata);
}
}
public static IWorkbook AddErrors(this IWorkbook workbook, string sheetName, IList <ExportExcelError> errors, Action <ICell, string> action = null)
{
if (errors == null || !errors.Any())
{
return(workbook);
}
var workSheet = workbook.GetSheet(sheetName);
if (workSheet == null)
{
throw new Exception($"{sheetName}不存在");
}
if (action == null)
{
action = (cell, msg) =>
{
var cellStyle = cell.Sheet.Workbook.CreateCellStyle();
cellStyle.FillPattern = FillPattern.FineDots;
cellStyle.FillBackgroundColor = IndexedColors.Red.Index;
#region 设置单元格字体样式
var font = cell.Sheet.Workbook.CreateFont();
font.IsBold = true; //字体为粗体
font.Color = IndexedColors.White.Index; //字体颜色
font.FontName = "微软雅黑"; //字体
font.FontHeight = 12; //字体大小
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
#endregion
if (cell is HSSFCell)
{
if (cell.CellComment == null)
{
// 创建绘图主控制器(用于包括单元格注释在内的所有形状的顶级容器)
IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
// 客户端锚定定义工作表中注释的大小和位置
//(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
//前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
IComment comment = patriarch.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 2, cell.RowIndex + 4));
comment.Author = "系统管理员";
cell.CellComment = comment;
}
cell.CellComment.String = new HSSFRichTextString(msg);
}
else
{
if (cell.CellComment == null)
{
// 创建绘图主控制器(用于包括单元格注释在内的所有形状的顶级容器)
IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
// 客户端锚定定义工作表中注释的大小和位置
//(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
//前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
IComment comment = patriarch.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 2, cell.RowIndex + 4));
comment.Author = "系统管理员";
cell.CellComment = comment;
}
cell.CellComment.String = new XSSFRichTextString(msg);
}
};
}
foreach (var item in errors)
{
var cell = workSheet.GetRow(item.Row).GetCell(item.Column);
action(cell, item.Message);
}
return(workbook);
}
/// <summary>
/// Creates an excel comment in each cell with an associated error
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="sheet"></param>
private void HighlightErrors(ISheet excelSheet, ICOBieSheet <COBieRow> sheet)
{
//sort by row then column
var errors = sheet.Errors.OrderBy(err => err.Row).ThenBy(err => err.Column);
// The patriarch is a container for comments on a sheet
IDrawing patr = excelSheet.CreateDrawingPatriarch();
int sheetCommnetCount = 0;
foreach (var error in errors)
{
if (error.Row > 0 && error.Column >= 0)
{
if ((error.Row + 3) > 65280)//UInt16.MaxValue some reason the CreateCellComment has 65280 as the max row number
{
// TODO: Warn overflow of XLS 2003 worksheet
break;
}
//limit comments to 1000 per sheet
if (sheetCommnetCount == 999)
{
break;
}
IRow excelRow = excelSheet.GetRow(error.Row);
if (excelRow != null)
{
ICell excelCell = excelRow.GetCell(error.Column);
if (excelCell != null)
{
string description = error.ErrorDescription;
if (hasErrorLevel)
{
if (error.ErrorLevel == COBieError.ErrorLevels.Warning)
{
description = "Warning: " + description;
}
else
{
description = "Error: " + description;
}
}
if (excelCell.CellComment == null)
{
try
{
// A client anchor is attached to an excel worksheet. It anchors against a top-left and bottom-right cell.
// Create a comment 3 columns wide and 3 rows height
IClientAnchor anchor = null;
if (IsXlsx)
{
anchor = new XSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
else
{
anchor = new HSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
IComment comment = patr.CreateCellComment(anchor);
IRichTextString str = null;
if (IsXlsx)
{
str = new XSSFRichTextString(description);
}
else
{
str = new HSSFRichTextString(description);
}
comment.String = str;
comment.Author = "XBim";
excelCell.CellComment = comment;
_commentCount++;
sheetCommnetCount++;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
else
{
if (IsXlsx)
{
((XSSFRichTextString)excelCell.CellComment.String).Append(" Also " + description);
}
else
{
description = excelCell.CellComment.String.ToString() + " Also " + description;
excelCell.CellComment.String = new HSSFRichTextString(description);
}
}
}
}
}
}
}
public VTComment(ref HSSFWorkbook workbook, string sheetName)
{
IDrawing patr = (HSSFPatriarch)workbook.GetSheet(sheetName).CreateDrawingPatriarch();
Comment = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor());
}
public void TestCreate()
{
String cellText = "Hello, World";
String commentText = "We can set comments in POI";
String commentAuthor = "Apache Software Foundation";
int cellRow = 3;
int cellColumn = 1;
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper factory = wb.GetCreationHelper();
ISheet sheet = wb.CreateSheet();
Assert.IsNull(sheet.GetCellComment(cellRow, cellColumn));
ICell cell = sheet.CreateRow(cellRow).CreateCell(cellColumn);
cell.SetCellValue(factory.CreateRichTextString(cellText));
Assert.IsNull(cell.CellComment);
Assert.IsNull(sheet.GetCellComment(cellRow, cellColumn));
IDrawing patr = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (2);
anchor.Col2 = (5);
anchor.Row1 = (1);
anchor.Row2 = (2);
IComment comment = patr.CreateCellComment(anchor);
Assert.IsFalse(comment.Visible);
comment.Visible = (true);
Assert.IsTrue(comment.Visible);
IRichTextString string1 = factory.CreateRichTextString(commentText);
comment.String = (string1);
comment.Author = (commentAuthor);
cell.CellComment = (comment);
Assert.IsNotNull(cell.CellComment);
Assert.IsNotNull(sheet.GetCellComment(cellRow, cellColumn));
//verify our Settings
Assert.AreEqual(commentAuthor, comment.Author);
Assert.AreEqual(commentText, comment.String.String);
Assert.AreEqual(cellRow, comment.Row);
Assert.AreEqual(cellColumn, comment.Column);
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0);
cell = sheet.GetRow(cellRow).GetCell(cellColumn);
comment = cell.CellComment;
Assert.IsNotNull(comment);
Assert.AreEqual(commentAuthor, comment.Author);
Assert.AreEqual(commentText, comment.String.String);
Assert.AreEqual(cellRow, comment.Row);
Assert.AreEqual(cellColumn, comment.Column);
Assert.IsTrue(comment.Visible);
// Change slightly, and re-test
comment.String = (factory.CreateRichTextString("New Comment Text"));
comment.Visible = (false);
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0);
cell = sheet.GetRow(cellRow).GetCell(cellColumn);
comment = cell.CellComment;
Assert.IsNotNull(comment);
Assert.AreEqual(commentAuthor, comment.Author);
Assert.AreEqual("New Comment Text", comment.String.String);
Assert.AreEqual(cellRow, comment.Row);
Assert.AreEqual(cellColumn, comment.Column);
Assert.IsFalse(comment.Visible);
}
