public void TestGetPatriarch()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
Assert.IsNull(sh.DrawingPatriarch);
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
Assert.IsNotNull(patriarch);
patriarch.CreateSimpleShape(new HSSFClientAnchor());
patriarch.CreateSimpleShape(new HSSFClientAnchor());
Assert.AreSame(patriarch, sh.DrawingPatriarch);
EscherAggregate agg = patriarch.GetBoundAggregate();
EscherDgRecord dg = agg.GetEscherContainer().GetChildById(EscherDgRecord.RECORD_ID) as EscherDgRecord;
int lastId = dg.LastMSOSPID;
Assert.AreSame(patriarch, sh.CreateDrawingPatriarch());
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
dg = patriarch.GetBoundAggregate().GetEscherContainer().GetChildById(EscherDgRecord.RECORD_ID) as EscherDgRecord;
Assert.AreEqual(lastId, dg.LastMSOSPID);
}
public void TestAddShapesToGroup()
{
HSSFWorkbook wb = new HSSFWorkbook();
// create a sheet with a text box
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
int index = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG);
group.CreatePicture(new HSSFChildAnchor(), index);
HSSFPolygon polygon = group.CreatePolygon(new HSSFChildAnchor());
polygon.SetPoints(new int[] { 1, 100, 1 }, new int[] { 1, 50, 100 });
group.CreateTextbox(new HSSFChildAnchor());
group.CreateShape(new HSSFChildAnchor());
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, patriarch.Children.Count);
Assert.IsTrue(patriarch.Children[0] is HSSFShapeGroup);
group = (HSSFShapeGroup)patriarch.Children[0];
Assert.AreEqual(group.Children.Count, 4);
Assert.IsTrue(group.Children[0] is HSSFPicture);
Assert.IsTrue(group.Children[1] is HSSFPolygon);
Assert.IsTrue(group.Children[2] is HSSFTextbox);
Assert.IsTrue(group.Children[3] is HSSFSimpleShape);
HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor());
index = wb.AddPicture(new byte[] { 2, 2, 2 }, PictureType.JPEG);
group2.CreatePicture(new HSSFChildAnchor(), index);
polygon = group2.CreatePolygon(new HSSFChildAnchor());
polygon.SetPoints(new int[] { 1, 100, 1 }, new int[] { 1, 50, 100 });
group2.CreateTextbox(new HSSFChildAnchor());
group2.CreateShape(new HSSFChildAnchor());
group2.CreateShape(new HSSFChildAnchor());
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(2, patriarch.Children.Count);
group = (HSSFShapeGroup)patriarch.Children[1];
Assert.AreEqual(group.Children.Count, 5);
Assert.IsTrue(group.Children[0] is HSSFPicture);
Assert.IsTrue(group.Children[1] is HSSFPolygon);
Assert.IsTrue(group.Children[2] is HSSFTextbox);
Assert.IsTrue(group.Children[3] is HSSFSimpleShape);
Assert.IsTrue(group.Children[4] is HSSFSimpleShape);
int shapeid = group.ShapeId;
}
public void TestBSEPictureRef()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet("Pictures") as HSSFSheet;
HSSFPatriarch dr = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFClientAnchor anchor = new HSSFClientAnchor();
InternalSheet ish = HSSFTestHelper.GetSheetForTest(sh);
//register a picture
byte[] data1 = new byte[] { 1, 2, 3 };
int idx1 = wb.AddPicture(data1, PictureType.JPEG);
Assert.AreEqual(1, idx1);
HSSFPicture p1 = dr.CreatePicture(anchor, idx1) as HSSFPicture;
EscherBSERecord bse = wb.Workbook.GetBSERecord(idx1);
Assert.AreEqual(bse.Ref, 1);
dr.CreatePicture(new HSSFClientAnchor(), idx1);
Assert.AreEqual(bse.Ref, 2);
HSSFShapeGroup gr = dr.CreateGroup(new HSSFClientAnchor());
gr.CreatePicture(new HSSFChildAnchor(), idx1);
Assert.AreEqual(bse.Ref, 3);
}
public void TestCloneComment()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment c = p.CreateComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5));
c.Column = (1);
c.Row = (2);
c.String = (new HSSFRichTextString("qwertyuio"));
HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
HSSFPatriarch p2 = sh2.DrawingPatriarch as HSSFPatriarch;
HSSFComment c2 = (HSSFComment)p2.Children[0];
Assert.IsTrue(Arrays.Equals(c2.GetTextObjectRecord().Serialize(), c.GetTextObjectRecord().Serialize()));
Assert.IsTrue(Arrays.Equals(c2.GetObjRecord().Serialize(), c.GetObjRecord().Serialize()));
Assert.IsTrue(Arrays.Equals(c2.NoteRecord.Serialize(), c.NoteRecord.Serialize()));
//everything except spRecord.shapeId must be the same
Assert.IsFalse(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
EscherSpRecord sp = (EscherSpRecord)c2.GetEscherContainer().GetChild(0);
sp.ShapeId = (1025);
Assert.IsTrue(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
}
public void TestAddToExistingFile()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group1 = patriarch.CreateGroup(new HSSFClientAnchor());
HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor());
group1.SetCoordinates(1, 2, 3, 4);
group2.SetCoordinates(5, 6, 7, 8);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFShapeGroup group3 = patriarch.CreateGroup(new HSSFClientAnchor());
group3.SetCoordinates(9, 10, 11, 12);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 3);
}
public PictureToExcel()
{
InitializeWorkbook();
HSSFSheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");
TechStoreDocumentsDT = new DataTable();
using (SqlDataAdapter DA = new SqlDataAdapter(@"SELECT TOP (2000) TechStoreName, TechStoreDocumentID FROM TechStore
LEFT JOIN TechStoreDocuments ON TechStore.TechStoreID = TechStoreDocuments.TechID AND DocType = 0
WHERE TechStoreSubGroupID = 30 ORDER BY TechStoreName", ConnectionStrings.CatalogConnectionString))
{
using (DataTable DT = new DataTable())
{
DA.Fill(TechStoreDocumentsDT);
}
}
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
HSSFCell Cell1;
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
int RowIndex = 0;
for (int i = 0; i < TechStoreDocumentsDT.Rows.Count; i++)
{
string TechStoreName = TechStoreDocumentsDT.Rows[i]["TechStoreName"].ToString();
Cell1 = sheet1.CreateRow(RowIndex).CreateCell(0);
Cell1.SetCellValue(TechStoreName);
Cell1.CellStyle = ReportCS1;
if (TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"] != DBNull.Value)
{
int TechStoreDocumentID = Convert.ToInt32(TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"]);
anchor = new HSSFClientAnchor(0, 0, 0, 255, 2, RowIndex, 5, RowIndex + 7)
{
AnchorType = 2
};
HSSFPicture picture = patriarch.CreatePicture(anchor, GetTechStoreImage(TechStoreDocumentID, hssfworkbook));
//picture.Resize();
picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
}
RowIndex = RowIndex + 8;
}
WriteToFile();
}
public void TestSetGetCoordinates()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
Assert.AreEqual(group.X1, 0);
Assert.AreEqual(group.Y1, 0);
Assert.AreEqual(group.X2, 1023);
Assert.AreEqual(group.Y2, 255);
group.SetCoordinates(1, 2, 3, 4);
Assert.AreEqual(group.X1, 1);
Assert.AreEqual(group.Y1, 2);
Assert.AreEqual(group.X2, 3);
Assert.AreEqual(group.Y2, 4);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
group = (HSSFShapeGroup)patriarch.Children[(0)];
Assert.AreEqual(group.X1, 1);
Assert.AreEqual(group.Y1, 2);
Assert.AreEqual(group.X2, 3);
Assert.AreEqual(group.Y2, 4);
}
public void TestAddTextToExistingFile()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
textbox.String = (new HSSFRichTextString("just for Test"));
HSSFTextbox textbox2 = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
textbox2.String = (new HSSFRichTextString("just for Test2"));
Assert.AreEqual(patriarch.Children.Count, 2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFTextbox text3 = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
text3.String = (new HSSFRichTextString("text3"));
Assert.AreEqual(patriarch.Children.Count, 3);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 3);
Assert.AreEqual(((HSSFTextbox)patriarch.Children[0]).String.String, "just for Test");
Assert.AreEqual(((HSSFTextbox)patriarch.Children[1]).String.String, "just for Test2");
Assert.AreEqual(((HSSFTextbox)patriarch.Children[2]).String.String, "text3");
}
public void TestPolygonType()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPolygonDrawArea(102, 101);
polygon.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
HSSFPolygon polygon1 = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon1.SetPolygonDrawArea(102, 101);
polygon1.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
EscherSpRecord spRecord = polygon1.GetEscherContainer().GetChildById(EscherSpRecord.RECORD_ID) as EscherSpRecord;
spRecord.ShapeType = ((short)77 /**RANDOM**/);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
Assert.IsTrue(patriarch.Children[0] is HSSFPolygon);
Assert.IsTrue(patriarch.Children[1] is HSSFPolygon);
}
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);
}
/// <summary>
/// .xls后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSExcel(string excelPath, string imgPath)
{
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet(hssfworkbook.GetSheetName(0));
int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 5, 2, 10, 3);//(255, 125, 1023, 150, 5, 2, 10, 3);//(0, 0, 0, 0, 5, 2, 10, 3);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
FileStream file = new FileStream(excelPath, FileMode.Create);
hssfworkbook.Write(file);
hssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void TestRotation()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5));
rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
Assert.AreEqual(rectangle.RotationDegree, 0);
rectangle.RotationDegree = (short)45;
Assert.AreEqual(rectangle.RotationDegree, 45);
rectangle.IsFlipHorizontal = true;
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
rectangle = (HSSFSimpleShape)patriarch.Children[0];
Assert.AreEqual(rectangle.RotationDegree, 45);
rectangle.RotationDegree = (short)30;
Assert.AreEqual(rectangle.RotationDegree, 30);
patriarch.SetCoordinates(0, 0, 10, 10);
rectangle.String = new HSSFRichTextString("1234");
}
public void TestShapeContainerImplementsIterable()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
patriarch.CreateSimpleShape(new HSSFClientAnchor());
patriarch.CreateSimpleShape(new HSSFClientAnchor());
int i = 2;
foreach (HSSFShape shape in patriarch)
{
i--;
}
Assert.AreEqual(i, 0);
}
finally
{
//wb.Close();
}
}
public void TestCloneSheetWithEmptyDrawingAggregate()
{
HSSFWorkbook b = new HSSFWorkbook();
HSSFSheet s = b.CreateSheet("Test") as HSSFSheet;
HSSFPatriarch patriarch = s.CreateDrawingPatriarch() as HSSFPatriarch;
EscherAggregate agg1 = patriarch.GetBoundAggregate();
HSSFSheet s2 = s.CloneSheet(b) as HSSFSheet;
patriarch = s2.DrawingPatriarch as HSSFPatriarch;
EscherAggregate agg2 = patriarch.GetBoundAggregate();
EscherSpRecord sp1 = (EscherSpRecord)agg1.GetEscherContainer().GetChild(1).GetChild(0).GetChild(1);
EscherSpRecord sp2 = (EscherSpRecord)agg2.GetEscherContainer().GetChild(1).GetChild(0).GetChild(1);
Assert.AreEqual(sp1.ShapeId, 1024);
Assert.AreEqual(sp2.ShapeId, 2048);
EscherDgRecord dg = (EscherDgRecord)agg2.GetEscherContainer().GetChild(0);
Assert.AreEqual(dg.LastMSOSPID, 2048);
Assert.AreEqual(dg.Instance, 0x2);
//everything except id and DgRecord.lastMSOSPID and DgRecord.Instance must be the same
sp2.ShapeId = (1024);
dg.LastMSOSPID = (1024);
dg.Instance =((short)0x1);
Assert.AreEqual(agg1.Serialize().Length, agg2.Serialize().Length);
Assert.AreEqual(agg1.ToXml(""), agg2.ToXml(""));
Assert.IsTrue(Arrays.Equals(agg1.Serialize(), agg2.Serialize()));
}
public static void setPicture(HSSFWorkbook workbook, HSSFSheet worksheet, int row, int col, string fileName)
{
try
{
byte[] bytes = File.ReadAllBytes(fileName);
if (!string.IsNullOrEmpty(fileName))
{
int pictureIdx = 0;
if (fileName.EndsWith(".jpg"))
{
pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
}
else if (fileName.EndsWith(".png"))
{
pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
}
HSSFPatriarch patriarch = (HSSFPatriarch)worksheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为50，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void TestCorrectOrderInOptRecord()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
EscherOptRecord opt = HSSFTestHelper.GetOptRecord(textbox);
String opt1Str = opt.ToXml();
textbox.FillColor = textbox.FillColor;
EscherContainerRecord Container = HSSFTestHelper.GetEscherContainer(textbox);
EscherOptRecord optRecord = Container.GetChildById(EscherOptRecord.RECORD_ID) as EscherOptRecord;
Assert.AreEqual(opt1Str, optRecord.ToXml());
textbox.LineStyle = textbox.LineStyle;
Assert.AreEqual(opt1Str, optRecord.ToXml());
textbox.LineWidth = textbox.LineWidth;
Assert.AreEqual(opt1Str, optRecord.ToXml());
textbox.LineStyleColor = textbox.LineStyleColor;
Assert.AreEqual(opt1Str, optRecord.ToXml());
wb.Close();
}
public void TestPolygonPoints()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPolygonDrawArea(100, 100);
polygon.SetPoints(new int[] { 0, 90, 50, 90 }, new int[] { 5, 5, 44, 88 });
PolygonShape polygonShape = HSSFTestModelHelper.CreatePolygonShape(0, polygon);
EscherArrayProperty verticesProp1 = polygon.GetOptRecord().Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
EscherArrayProperty verticesProp2 = ((EscherOptRecord)polygonShape.SpContainer.GetChildById(EscherOptRecord.RECORD_ID))
.Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
Assert.AreEqual(verticesProp1.NumberOfElementsInArray, verticesProp2.NumberOfElementsInArray);
Assert.AreEqual(verticesProp1.ToXml(""), verticesProp2.ToXml(""));
polygon.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 1, 2, 3 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 4, 5, 6 }));
polygonShape = HSSFTestModelHelper.CreatePolygonShape(0, polygon);
verticesProp1 = polygon.GetOptRecord().Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
verticesProp2 = ((EscherOptRecord)polygonShape.SpContainer.GetChildById(EscherOptRecord.RECORD_ID))
.Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
Assert.AreEqual(verticesProp1.NumberOfElementsInArray, verticesProp2.NumberOfElementsInArray);
Assert.AreEqual(verticesProp1.ToXml(""), verticesProp2.ToXml(""));
}
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 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 TestClearShapes()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
group.CreateShape(new HSSFChildAnchor());
group.CreateShape(new HSSFChildAnchor());
EscherAggregate agg = HSSFTestHelper.GetEscherAggregate(patriarch);
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 5);
Assert.AreEqual(agg.TailRecords.Count, 0);
Assert.AreEqual(group.Children.Count, 2);
group.Clear();
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 1);
Assert.AreEqual(agg.TailRecords.Count, 0);
Assert.AreEqual(group.Children.Count, 0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
group = (HSSFShapeGroup)patriarch.Children[(0)];
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 1);
Assert.AreEqual(agg.TailRecords.Count, 0);
Assert.AreEqual(group.Children.Count, 0);
}
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();
}
public void TestAddToExistingFile()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sh = wb1.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPolygonDrawArea(102, 101);
polygon.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
HSSFPolygon polygon1 = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon1.SetPolygonDrawArea(103, 104);
polygon1.SetPoints(new int[] { 11, 12, 13 }, new int[] { 14, 15, 16 });
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sh = wb2.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFPolygon polygon2 = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon2.SetPolygonDrawArea(203, 204);
polygon2.SetPoints(new int[] { 21, 22, 23 }, new int[] { 24, 25, 26 });
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
wb2.Close();
sh = wb3.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 3);
polygon = (HSSFPolygon)patriarch.Children[0];
polygon1 = (HSSFPolygon)patriarch.Children[1];
polygon2 = (HSSFPolygon)patriarch.Children[2];
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 1, 2, 3 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 4, 5, 6 }));
Assert.AreEqual(polygon.DrawAreaHeight, 101);
Assert.AreEqual(polygon.DrawAreaWidth, 102);
Assert.IsTrue(Arrays.Equals(polygon1.XPoints, new int[] { 11, 12, 13 }));
Assert.IsTrue(Arrays.Equals(polygon1.YPoints, new int[] { 14, 15, 16 }));
Assert.AreEqual(polygon1.DrawAreaHeight, 104);
Assert.AreEqual(polygon1.DrawAreaWidth, 103);
Assert.IsTrue(Arrays.Equals(polygon2.XPoints, new int[] { 21, 22, 23 }));
Assert.IsTrue(Arrays.Equals(polygon2.YPoints, new int[] { 24, 25, 26 }));
Assert.AreEqual(polygon2.DrawAreaHeight, 204);
Assert.AreEqual(polygon2.DrawAreaWidth, 203);
wb3.Close();
}
public void SetComment(HSSFSheet sheet, HSSFCell cell, string reference, string commentText, string author)
{
HSSFPatriarch patr = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));
comment1.String = new HSSFRichTextString(commentText);
comment1.Author = author;
cell.CellComment = comment1;
}
public void TestDgRecordNumShapes()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
EscherAggregate aggregate = HSSFTestHelper.GetEscherAggregate(patriarch);
EscherDgRecord dgRecord = (EscherDgRecord)aggregate.GetEscherRecord(0).GetChild(0) as EscherDgRecord;
Assert.AreEqual(dgRecord.NumShapes, 1);
}
public void TestBug45312()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)1, 1, 0, 0, (short)1, 1, 512, 100);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (/*setter*/ HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
//setAnchor method is wrong??
a1.SetAnchor((short)1, 1, 512, 0, (short)1, 1, 1023, 100);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.FlipVertical = (true);
shape1.ShapeType = (/*setter*/ HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)2, 2, 0, 0, (short)2, 2, 512, 100);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (/*setter*/ HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)2, 2, 0, 100, (short)2, 2, 512, 200);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.FlipHorizontal = (/*setter*/ true);
shape1.ShapeType = (/*setter*/ HSSFSimpleShape.OBJECT_TYPE_LINE);
}
/*OutputStream stream = new FileOutputStream("/tmp/45312.xls");
* try {
* wb.Write(stream);
* } finally {
* stream.Close();
* }*/
CheckWorkbookBack(wb);
}
finally
{
//wb.Close();
}
}
public static void CopyImage(HSSFSheet sheet, HSSFPictureData picData, int row, int col)
{
var pictureIdx = sheet.Workbook.AddPicture(picData.Data, (NPOI.SS.UserModel.PictureType)picData.Format);
var drawing = sheet.CreateDrawingPatriarch();
ICreationHelper helper = sheet.Workbook.GetCreationHelper();
var anchor = helper.CreateClientAnchor();
anchor.Col1 = col;
anchor.Row1 = row;
var pict = drawing.CreatePicture(anchor, pictureIdx);
pict.Resize();
}
public void TestSpgrRecord()
{
HSSFWorkbook wb = new HSSFWorkbook();
// create a sheet with a text box
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
Assert.AreSame(((EscherContainerRecord)group.GetEscherContainer().GetChild(0)).GetChildById(EscherSpgrRecord.RECORD_ID),
GetSpgrRecord(group));
}
public void TestSetGetProperties()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sh = wb1.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPolygonDrawArea(102, 101);
polygon.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 1, 2, 3 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 4, 5, 6 }));
Assert.AreEqual(polygon.DrawAreaHeight, 101);
Assert.AreEqual(polygon.DrawAreaWidth, 102);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sh = wb2.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
polygon = (HSSFPolygon)patriarch.Children[0];
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 1, 2, 3 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 4, 5, 6 }));
Assert.AreEqual(polygon.DrawAreaHeight, 101);
Assert.AreEqual(polygon.DrawAreaWidth, 102);
polygon.SetPolygonDrawArea(1021, 1011);
polygon.SetPoints(new int[] { 11, 21, 31 }, new int[] { 41, 51, 61 });
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 11, 21, 31 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 41, 51, 61 }));
Assert.AreEqual(polygon.DrawAreaHeight, 1011);
Assert.AreEqual(polygon.DrawAreaWidth, 1021);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
wb2.Close();
sh = wb3.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
polygon = (HSSFPolygon)patriarch.Children[0];
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 11, 21, 31 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 41, 51, 61 }));
Assert.AreEqual(polygon.DrawAreaHeight, 1011);
Assert.AreEqual(polygon.DrawAreaWidth, 1021);
wb3.Close();
}
public void TestTextForSimpleShape()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sheet = wb1.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFSimpleShape shape = patriarch.CreateSimpleShape(new HSSFClientAnchor());
shape.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
EscherAggregate agg = HSSFTestHelper.GetEscherAggregate(patriarch);
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sheet = wb2.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
shape = (HSSFSimpleShape)patriarch.Children[0];
agg = HSSFTestHelper.GetEscherAggregate(patriarch);
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
shape.String = new HSSFRichTextString("string1");
Assert.AreEqual(shape.String.String, "string1");
Assert.IsNotNull(HSSFTestHelper.GetEscherContainer(shape).GetChildById(EscherTextboxRecord.RECORD_ID));
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
wb2.Close();
HSSFWorkbook wb4 = HSSFTestDataSamples.WriteOutAndReadBack(wb3);
wb3.Close();
sheet = wb4.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
shape = (HSSFSimpleShape)patriarch.Children[0];
Assert.IsNotNull(HSSFTestHelper.GetTextObjRecord(shape));
Assert.AreEqual(shape.String.String, "string1");
Assert.IsNotNull(HSSFTestHelper.GetEscherContainer(shape).GetChildById(EscherTextboxRecord.RECORD_ID));
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
wb4.Close();
}
public void TestResultEqualsToAbstractShape()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
TextboxShape textboxShape = HSSFTestModelHelper.CreateTextboxShape(1025, textbox);
Assert.AreEqual(textbox.GetEscherContainer().ChildRecords.Count, 5);
Assert.AreEqual(textboxShape.SpContainer.ChildRecords.Count, 5);
//sp record
byte[] expected = textboxShape.SpContainer.GetChild(0).Serialize();
byte[] actual = textbox.GetEscherContainer().GetChild(0).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = textboxShape.SpContainer.GetChild(2).Serialize();
actual = textbox.GetEscherContainer().GetChild(2).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = textboxShape.SpContainer.GetChild(3).Serialize();
actual = textbox.GetEscherContainer().GetChild(3).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = textboxShape.SpContainer.GetChild(4).Serialize();
actual = textbox.GetEscherContainer().GetChild(4).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
ObjRecord obj = textbox.GetObjRecord();
ObjRecord objShape = textboxShape.ObjRecord;
expected = obj.Serialize();
actual = objShape.Serialize();
TextObjectRecord tor = textbox.GetTextObjectRecord();
TextObjectRecord torShape = textboxShape.TextObjectRecord;
expected = tor.Serialize();
actual = torShape.Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
}