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); }