public void TestAddToExistingFile() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.PNG); HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.Column = (5); comment.String = new HSSFRichTextString("comment1"); comment = patriarch.CreateCellComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)10, 10)) as HSSFComment; comment.Row = (5); comment.String = new HSSFRichTextString("comment2"); comment.SetBackgroundImage(idx); Assert.AreEqual(comment.GetBackgroundImageId(), idx); Assert.AreEqual(patriarch.Children.Count, 2); HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wbBack.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[(1)]; Assert.AreEqual(comment.GetBackgroundImageId(), idx); comment.ResetBackgroundImage(); Assert.AreEqual(comment.GetBackgroundImageId(), 0); Assert.AreEqual(patriarch.Children.Count, 2); comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.String = new HSSFRichTextString("comment3"); Assert.AreEqual(patriarch.Children.Count, 3); HSSFWorkbook wbBack2 = HSSFTestDataSamples.WriteOutAndReadBack(wbBack); sh = wbBack2.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[1]; Assert.AreEqual(comment.GetBackgroundImageId(), 0); Assert.AreEqual(patriarch.Children.Count, 3); Assert.AreEqual(((HSSFComment)patriarch.Children[0]).String.String, "comment1"); Assert.AreEqual(((HSSFComment)patriarch.Children[1]).String.String, "comment2"); Assert.AreEqual(((HSSFComment)patriarch.Children[2]).String.String, "comment3"); wb.Close(); wbBack.Close(); wbBack2.Close(); }
public void TestFindComments() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; HSSFRow row = sh.CreateRow(5) as HSSFRow; HSSFCell cell = row.CreateCell(4) as HSSFCell; cell.CellComment = (comment); HSSFTestModelHelper.CreateCommentShape(0, comment); Assert.IsNotNull(sh.FindCellComment(5, 4)); Assert.IsNull(sh.FindCellComment(5, 5)); HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wbBack.GetSheetAt(0) as HSSFSheet; Assert.IsNotNull(sh.FindCellComment(5, 4)); Assert.IsNull(sh.FindCellComment(5, 5)); wb.Close(); wbBack.Close(); }
public void TestShapeId() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.ShapeId = 2024; /** * SpRecord.id == shapeId * ObjRecord.id == shapeId % 1024 * NoteRecord.id == ObjectRecord.id == shapeId % 1024 */ Assert.AreEqual(comment.ShapeId, 2024); CommonObjectDataSubRecord cod = (CommonObjectDataSubRecord)comment.GetObjRecord().SubRecords[0]; Assert.AreEqual(cod.ObjectId, 1000); EscherSpRecord spRecord = (EscherSpRecord)comment.GetEscherContainer().GetChild(0); Assert.AreEqual(spRecord.ShapeId, 2024); Assert.AreEqual(comment.ShapeId, 2024); Assert.AreEqual(comment.NoteRecord.ShapeId, 1000); }
public void TestClearShapesForPatriarch() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; patriarch.CreateSimpleShape(new HSSFClientAnchor()); patriarch.CreateSimpleShape(new HSSFClientAnchor()); patriarch.CreateCellComment(new HSSFClientAnchor()); EscherAggregate agg = HSSFTestHelper.GetEscherAggregate(patriarch); Assert.AreEqual(agg.GetShapeToObjMapping().Count, 6); Assert.AreEqual(agg.TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 3); patriarch.Clear(); Assert.AreEqual(agg.GetShapeToObjMapping().Count, 0); Assert.AreEqual(agg.TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 0); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(agg.GetShapeToObjMapping().Count, 0); Assert.AreEqual(agg.TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 0); }
//添加批注 public IComment addPiZhu(HSSFPatriarch patr, string commond, string Author) { IComment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 3, 6, 5)); comment1.String = (new HSSFRichTextString(commond)); comment1.Author = (Author); return(comment1); }
public void TestSetGetProperties() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.String = new HSSFRichTextString("comment1"); Assert.AreEqual(comment.String.String, "comment1"); comment.Author = ("poi"); Assert.AreEqual(comment.Author, "poi"); comment.Column = (3); Assert.AreEqual(comment.Column, 3); comment.Row = (4); Assert.AreEqual(comment.Row, 4); comment.Visible = (false); Assert.AreEqual(comment.Visible, false); HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wbBack.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[0]; Assert.AreEqual(comment.String.String, "comment1"); Assert.AreEqual("poi", comment.Author); Assert.AreEqual(comment.Column, 3); Assert.AreEqual(comment.Row, 4); Assert.AreEqual(comment.Visible, false); comment.String = new HSSFRichTextString("comment12"); comment.Author = ("poi2"); comment.Column = (32); comment.Row = (42); comment.Visible = (true); HSSFWorkbook wbBack2 = HSSFTestDataSamples.WriteOutAndReadBack(wbBack); sh = wbBack2.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[0]; Assert.AreEqual(comment.String.String, "comment12"); Assert.AreEqual("poi2", comment.Author); Assert.AreEqual(comment.Column, 32); Assert.AreEqual(comment.Row, 42); Assert.AreEqual(comment.Visible, true); wb.Close(); wbBack.Close(); wbBack2.Close(); }
}//解压结束 /// <summary> /// 初始化导出表格样式 /// </summary> /// LZ Add 2016-08-04 /// <param name="book">Excel文件</param> /// <param name="param"></param> /// <param name="paramComment">批注字典 注意:字典的key要与表头key一致</param> /// <returns></returns> public static ISheet CreateSheet(HSSFWorkbook book, Dictionary <string, int> param, Dictionary <string, string> paramComment, string sheetName = "Sheet1") { //添加一个sheet ISheet sheet1 = book.CreateSheet(sheetName); IRow row = sheet1.CreateRow(0); //初始化样式 ICellStyle mStyle = book.CreateCellStyle(); mStyle.Alignment = HorizontalAlignment.Center; mStyle.VerticalAlignment = VerticalAlignment.Center; IFont mfont = book.CreateFont(); mfont.FontHeight = 10 * 20; mStyle.SetFont(mfont); HSSFPatriarch patr = sheet1.CreateDrawingPatriarch() as HSSFPatriarch; NPOI.SS.UserModel.ICreationHelper facktory = book.GetCreationHelper(); HSSFComment comment = null; NPOI.SS.UserModel.IClientAnchor anchor = facktory.CreateClientAnchor(); int i = 0; foreach (var item in param) { //设置列宽 sheet1.SetColumnWidth(i, item.Value * 256); sheet1.SetDefaultColumnStyle(i, mStyle); row.CreateCell(i).SetCellValue(item.Key.ToString()); if (paramComment.ContainsKey(item.Key.ToString())) { //设置批注 anchor = facktory.CreateClientAnchor(); anchor.Col1 = row.GetCell(i).ColumnIndex; anchor.Col2 = row.GetCell(i).ColumnIndex + 1; anchor.Row1 = row.RowNum; anchor.Row2 = row.RowNum + 3; comment = patr.CreateCellComment(anchor) as HSSFComment; comment.String = new HSSFRichTextString(paramComment[item.Key.ToString()].ToString()); comment.Author = ("CySoft"); row.GetCell(i).CellComment = (comment); } i++; } i = 0; sheet1.GetRow(0).Height = 28 * 20; return(sheet1); }
public void TestAttemptToSave2CommentsWithSameCoordinates() { Object err = null; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; patriarch.CreateCellComment(new HSSFClientAnchor()); patriarch.CreateCellComment(new HSSFClientAnchor()); try { HSSFTestDataSamples.WriteOutAndReadBack(wb); } catch (InvalidOperationException e) { err = 1; Assert.AreEqual(e.Message, "found multiple cell comments for cell A1"); } Assert.IsNotNull(err); }
/// <summary> /// 设置批注 /// </summary> /// <param name="errMsg"></param> /// <param name="row"></param> /// <param name="cell"></param> /// <param name="facktory"></param> /// <param name="patr"></param> public static void SetComment(string errMsg, IRow row, ICell cell, ICreationHelper facktory, HSSFPatriarch patr) { var anchor = facktory.CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + 3; anchor.Row1 = row.RowNum; anchor.Row2 = row.RowNum + 5; var comment = patr.CreateCellComment(anchor); comment.String = new HSSFRichTextString(errMsg); comment.Author = ("mysoft"); cell.CellComment = (comment); }
public void TestInitState() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; EscherAggregate agg = HSSFTestHelper.GetEscherAggregate(patriarch); Assert.AreEqual(agg.TailRecords.Count, 0); HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; Assert.AreEqual(agg.TailRecords.Count, 1); HSSFSimpleShape shape = patriarch.CreateSimpleShape(new HSSFClientAnchor()); Assert.AreEqual(comment.GetOptRecord().EscherProperties.Count, 10); }
/// <summary> /// 导出报价成功率Excel /// </summary> /// <param name="dataList"></param> public static void GenScussedExcel(List <Dictionary <string, string> > dataList) { HSSFWorkbook hssfworkbook; hssfworkbook = new HSSFWorkbook(); ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); IRow row = sheet1.CreateRow(0); for (int i = 0; i < dataList.Count; i++) { ICell curCell = row.CreateCell(i, CellType.String); curCell.SetCellValue(dataList[i]["CityName"]); } row = sheet1.CreateRow(1); for (int i = 0; i < dataList.Count; i++) { ICell curCell = row.CreateCell(i, CellType.String); curCell.SetCellValue(dataList[i]["CityPercent"]); HSSFPatriarch patr1 = sheet1.CreateDrawingPatriarch() as HSSFPatriarch; HSSFComment comment1 = patr1.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 3, 3, 7)) as HSSFComment; comment1.String = new HSSFRichTextString(dataList[i]["ErrorInfo"]); curCell.CellComment = comment1; } if (!Directory.Exists(Exportpath)) { Directory.CreateDirectory(Exportpath); } using (FileStream fs = File.OpenWrite(string.Format(@"{0}\{1}报价_成功率.xls", Exportpath, DateTime.Now.ToString("yyyy-MM-dd HH-mm")))) { hssfworkbook.Write(fs); } //MessageBox.Show("导出完成!"); System.Diagnostics.Process.Start("explorer.exe", Exportpath); }
static void Main(string[] args) { hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("Cell comments in POI HSSF"); // Create the drawing patriarch. This is the top level container for all shapes including cell comments. HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //Create a cell in row 3 ICell cell1 = sheet.CreateRow(3).CreateCell(1); cell1.SetCellValue(new HSSFRichTextString("Hello, World")); //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("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); //Write the stream data of workbook to the root directory FileStream file = new FileStream(@"OutputAddedCommentInCell.xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); //hssfworkbook = new HSSFWorkbook(); //Sheet sheet = hssfworkbook.CreateSheet("Cell comments in POI HSSF"); //// Create the drawing patriarch. This is the top level container for all shapes including cell comments. //HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); ////Create a cell in row 3 //Cell cell1 = sheet.CreateRow(3).CreateCell(1); //cell1.SetCellValue(new HSSFRichTextString("Hello, World")); ////anchor defines size and position of the comment in worksheet //Comment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5)); //// set text in the comment //comment1.String = (new HSSFRichTextString("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); ////Write the stream data of workbook to the root directory //FileStream file = new FileStream(@"OutputAddedCommentInCell.xls", FileMode.Create); //hssfworkbook.Write(file); //file.Close(); }
/// <summary> /// 生成导入模版Excel信息 /// </summary> /// <param name="templateModels">模板定义信息</param> /// <param name="title">Sheet名称</param> /// <returns></returns> public static HSSFWorkbook ExportTemplate(List <ExcelTemplate> templateModels, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); workbook.SetSheetName(0, title); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = Company; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = Author; si.ApplicationName = ApplicationName; si.Title = title; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; //取得列宽 int[] arrColWidth = new int[templateModels.Count]; int columnIndex = 0; foreach (var templateModel in templateModels) { arrColWidth[columnIndex] = templateModel.CellLength > 0 ? templateModel.CellLength * 2 : Encoding.UTF8.GetBytes(templateModel.Name.ToString()).Length; columnIndex++; } var headerRow = sheet.CreateRow(0); columnIndex = 0; foreach (var templateModel in templateModels) { var cell = headerRow.CreateCell(columnIndex); if (!string.IsNullOrEmpty(templateModel.ExportComments)) { HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFComment comment = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 16)); comment.String = new HSSFRichTextString(templateModel.ExportComments); comment.Author = ApplicationName; cell.CellComment = comment; } if (templateModel.DictionaryItems != null && templateModel.DictionaryItems.Count > 0) { DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(templateModel.DictionaryItems.ToArray()); CellRangeAddressList regions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); IDataValidation validation = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(validation); } cell.SetCellValue(templateModel.Name); if (templateModel.IsRequred) { var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; var font = workbook.CreateFont(); font.Color = HSSFColor.Red.Index; font.FontHeightInPoints = 10; font.IsBold = true; headStyle.SetFont(font); cell.CellStyle = headStyle; } else { var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; var font = workbook.CreateFont(); font.Color = HSSFColor.Black.Index; font.FontHeightInPoints = 10; font.IsBold = true; headStyle.SetFont(font); cell.CellStyle = headStyle; } //设置列宽 sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256); columnIndex++; } return(workbook); }
public void ResultEqualsToNonExistingAbstractShape() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; HSSFRow row = sh.CreateRow(0) as HSSFRow; HSSFCell cell = row.CreateCell(0) as HSSFCell; cell.CellComment = (comment); Assert.AreEqual(comment.GetEscherContainer().ChildRecords.Count, 5); //sp record byte[] expected = TestDrawingAggregate.decompress("H4sIAAAAAAAAAFvEw/WBg4GBgZEFSHAxMAAA9gX7nhAAAAA="); byte[] actual = comment.GetEscherContainer().GetChild(0).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); expected = TestDrawingAggregate.decompress("H4sIAAAAAAAAAGNgEPggxIANAABK4+laGgAAAA=="); actual = comment.GetEscherContainer().GetChild(2).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); expected = TestDrawingAggregate.decompress("H4sIAAAAAAAAAGNgEPzAAAQACl6c5QgAAAA="); actual = comment.GetEscherContainer().GetChild(3).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); expected = TestDrawingAggregate.decompress("H4sIAAAAAAAAAGNg4P3AAAQA6pyIkQgAAAA="); actual = comment.GetEscherContainer().GetChild(4).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); ObjRecord obj = comment.GetObjRecord(); expected = TestDrawingAggregate.decompress("H4sIAAAAAAAAAItlMGEQZRBikGRgZBF0YEACvAxiDLgBAJZsuoU4AAAA"); actual = obj.Serialize(); Assert.AreEqual(expected.Length, actual.Length); //assertArrayEquals(expected, actual); TextObjectRecord tor = comment.GetTextObjectRecord(); expected = TestDrawingAggregate.decompress("H4sIAAAAAAAAANvGKMQgxMSABgBGi8T+FgAAAA=="); actual = tor.Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); NoteRecord note = comment.NoteRecord; expected = TestDrawingAggregate.decompress("H4sIAAAAAAAAAJNh4GGAAEYWEAkAS0KXuRAAAAA="); actual = note.Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); wb.Close(); }
public void TestResultEqualsToAbstractShape() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; HSSFRow row = sh.CreateRow(0) as HSSFRow; HSSFCell cell = row.CreateCell(0) as HSSFCell; cell.CellComment = (comment); CommentShape commentShape = HSSFTestModelHelper.CreateCommentShape(1025, comment); Assert.AreEqual(comment.GetEscherContainer().ChildRecords.Count, 5); Assert.AreEqual(commentShape.SpContainer.ChildRecords.Count, 5); //sp record byte[] expected = commentShape.SpContainer.GetChild(0).Serialize(); byte[] actual = comment.GetEscherContainer().GetChild(0).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); expected = commentShape.SpContainer.GetChild(2).Serialize(); actual = comment.GetEscherContainer().GetChild(2).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); expected = commentShape.SpContainer.GetChild(3).Serialize(); actual = comment.GetEscherContainer().GetChild(3).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); expected = commentShape.SpContainer.GetChild(4).Serialize(); actual = comment.GetEscherContainer().GetChild(4).Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); ObjRecord obj = comment.GetObjRecord(); ObjRecord objShape = commentShape.ObjRecord; expected = obj.Serialize(); actual = objShape.Serialize(); Assert.AreEqual(expected.Length, actual.Length); //assertArrayEquals(expected, actual); TextObjectRecord tor = comment.GetTextObjectRecord(); TextObjectRecord torShape = commentShape.TextObjectRecord; expected = tor.Serialize(); actual = torShape.Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); NoteRecord note = comment.NoteRecord; NoteRecord noteShape = commentShape.NoteRecord; expected = note.Serialize(); actual = noteShape.Serialize(); Assert.AreEqual(expected.Length, actual.Length); Assert.IsTrue(Arrays.Equals(expected, actual)); wb.Close(); }
public void TestRemoveShapes() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor()); rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE; int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG); patriarch.CreatePicture(new HSSFClientAnchor(), idx); patriarch.CreateCellComment(new HSSFClientAnchor()); HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor()); polygon.SetPoints(new int[] { 1, 2 }, new int[] { 2, 3 }); patriarch.CreateTextbox(new HSSFClientAnchor()); HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor()); group.CreateTextbox(new HSSFChildAnchor()); group.CreatePicture(new HSSFChildAnchor(), idx); Assert.AreEqual(patriarch.Children.Count, 6); Assert.AreEqual(group.Children.Count, 2); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 6); group = (HSSFShapeGroup)patriarch.Children[5]; group.RemoveShape(group.Children[0]); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); group = (HSSFShapeGroup)patriarch.Children[(5)]; patriarch.RemoveShape(group); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 5); HSSFShape shape = patriarch.Children[0]; patriarch.RemoveShape(shape); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 4); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 4); HSSFPicture picture = (HSSFPicture)patriarch.Children[0]; patriarch.RemoveShape(picture); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 3); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1); Assert.AreEqual(patriarch.Children.Count, 3); HSSFComment comment = (HSSFComment)patriarch.Children[0]; patriarch.RemoveShape(comment); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 2); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 2); polygon = (HSSFPolygon)patriarch.Children[0]; patriarch.RemoveShape(polygon); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 1); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 1); HSSFTextbox textbox = (HSSFTextbox)patriarch.Children[0]; patriarch.RemoveShape(textbox); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 0); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0); Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0); Assert.AreEqual(patriarch.Children.Count, 0); }
/// <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 HSSFPatriarch patr = (HSSFPatriarch)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 IComment comment = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3)); comment.String = new HSSFRichTextString(description); comment.Author = "XBim"; excelCell.CellComment = comment; _commentCount++; sheetCommnetCount++; } catch (Exception ex) { Console.WriteLine(ex.Message); } } else { excelCell.CellComment.String = new HSSFRichTextString(excelCell.CellComment.String + " Also " + description); } } } } } }
static void Main(string[] args) { InitializeWorkbook(); Sheet sheet = hssfworkbook.CreateSheet("Cell comments in POI HSSF"); // Create the drawing patriarch. This is the top level container for all shapes including cell comments. HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //Create a cell in row 3 Cell cell1 = sheet.CreateRow(3).CreateCell(1); cell1.SetCellValue(new HSSFRichTextString("Hello, World")); //anchor defines size and position of the comment in worksheet Comment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5)); // set text in the comment comment1.String = (new HSSFRichTextString("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 Cell cell2 = sheet.CreateRow(6).CreateCell(1); cell2.SetCellValue(36.6); 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 Font 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; WriteToFile(); }