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