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 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 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 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 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 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();
}
}
private static void DrawSheet3(ISheet sheet3)
{
// Create a row and size one of the cells reasonably large
IRow row = sheet3.CreateRow(2);
row.HeightInPoints = 140;
row.CreateCell(1);
sheet3.SetColumnWidth(2, 9000);
// Create the Drawing patriarch. This is the top level container for
// all shapes. This will clear out any existing shapes for that sheet.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet3.CreateDrawingPatriarch();
// Create a shape group.
HSSFShapeGroup group = patriarch.CreateGroup(
new HSSFClientAnchor(0, 0, 900, 200, (short)2, 2, (short)2, 2));
// Create a couple of lines in the group.
HSSFSimpleShape shape1 = group.CreateShape(new HSSFChildAnchor(3, 3, 500, 500));
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
((HSSFChildAnchor)shape1.Anchor).SetAnchor((short)3, 3, 500, 500);
HSSFSimpleShape shape2 = group.CreateShape(new HSSFChildAnchor((short)1, 200, 400, 600));
shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
private void ConvertUserModelToRecords()
{
if (patriarch != null)
{
shapeToObj.Clear();
tailRec.Clear();
ClearEscherRecords();
if (patriarch.Children.Count != 0)
{
ConvertPatriarch(patriarch);
EscherContainerRecord dgContainer = (EscherContainerRecord)GetEscherRecord(0);
EscherContainerRecord spgrContainer = null;
for (int i = 0; i < dgContainer.ChildRecords.Count; i++)
{
if (dgContainer.GetChild(i).RecordId == EscherContainerRecord.SPGR_CONTAINER)
{
spgrContainer = (EscherContainerRecord)dgContainer.GetChild(i);
}
}
ConvertShapes(patriarch, spgrContainer, shapeToObj);
patriarch = null;
}
}
}
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 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 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 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 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();
}
//向excel中提交图片
private void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
{
try
{
//由于File类只能读取本地资源，所以在配置文件中配置了物理路径的前半部分
string DiscPath = ConfigurationManager.AppSettings["PictureDiscPath"];
// string FileName = DiscPath.Replace("\\", "/") + fileurl.Replace("http://www.bolioptics.com/", "");
string FileName = fileurl;
FileInfo file = new FileInfo(FileName);
if (file.Exists == true)
{
byte[] bytes = System.IO.File.ReadAllBytes(FileName);
if (!string.IsNullOrEmpty(FileName))
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public void TestAllocateNewIds()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("empty.xls");
HSSFSheet sheet = wb.GetSheetAt(0) as HSSFSheet;
HSSFPatriarch patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
/**
* 2048 - main SpContainer id
* 2049 - existing shape id
*/
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 2050);
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 2051);
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 2052);
sheet = wb.GetSheetAt(1) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
/**
* 3072 - main SpContainer id
* 3073 - existing shape id
*/
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 3074);
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 3075);
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 3076);
sheet = wb.GetSheetAt(2) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 1026);
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 1027);
Assert.AreEqual(HSSFTestHelper.AllocateNewShapeId(patriarch), 1028);
}
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");
}
private static void DrawSheet4(ISheet sheet4, HSSFWorkbook wb)
{
// Create the Drawing patriarch. This is the top level container for
// all shapes. This will clear out any existing shapes for that sheet.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();
// Create a couple of textboxes
HSSFTextbox textbox1 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 0, 0, (short)1, 1, (short)2, 2));
textbox1.String = new HSSFRichTextString("This is a test");
HSSFTextbox textbox2 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 900, 100, (short)3, 3, (short)3, 4));
textbox2.String = new HSSFRichTextString("Woo");
textbox2.SetFillColor(200, 0, 0);
textbox2.LineStyle = LineStyle.DotGel;
// Create third one with some fancy font styling.
HSSFTextbox textbox3 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 900, 100, (short)4, 4, (short)5, 4 + 1));
IFont font = wb.CreateFont();
font.IsItalic = true;
font.Underline = FontUnderlineType.Double;
HSSFRichTextString str = new HSSFRichTextString("Woo!!!");
str.ApplyFont(2, 5, font);
textbox3.String = str;
textbox3.FillColor = 0x08000030;
textbox3.LineStyle = LineStyle.None; // no line around the textbox.
textbox3.IsNoFill = true; // make it transparent
}
private static void DrawLinesToCenter(HSSFPatriarch patriarch)
{
// Draw some lines from and to the corners
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)2, 2, 0, 0, (short)2, 2, 512, 128);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)2, 2, 512, 128, (short)2, 2, 1023, 0);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)1, 1, 0, 0, (short)1, 1, 512, 100);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.SetAnchor((short)1, 1, 512, 100, (short)1, 1, 1023, 0);
HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
}
}
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 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");
}
/// <summary>
/// 将图片导出到Excel
/// </summary>
/// <param name="sheet"></param>
/// <param name="xssfworkbook"></param>
/// <param name="imageName"></param>
private void ExportImgToExcel(ISheet sheet, HSSFWorkbook xssfworkbook, string imageName)
{
try
{
string picName = string.IsNullOrEmpty(imageName) ? @"Image\sjlogo.png" : imageName;
var picType = string.IsNullOrEmpty(imageName) ? PictureType.PNG : GetPicType(imageName);
Image imgOutput = Bitmap.FromFile(Path.Combine(Directory.GetCurrentDirectory(), picName));
//Image imgOutput = System.Drawing.Bitmap.FromStream()
Image img = imgOutput.GetThumbnailImage(160, 115, null, IntPtr.Zero);
//图片转换为文件流
MemoryStream ms = new MemoryStream();
img.Save(ms, ImageFormat.Bmp);
BinaryReader br = new BinaryReader(ms);
var picBytes = ms.ToArray();
ms.Close();
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
int pictureIdx = xssfworkbook.AddPicture(picBytes, picType); //添加图片
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(200, 90, 240, 200, 0, 0, 1, 0);// 90 200 图片高度110
// new HSSFClientAnchor(X1, Y1, X2, Y2, 列索引1,行索引1 , 列索引2, 行索引2); 行列索引从0开始 ,行列索引指的是 图片左上角所在单元格的行列和 图片右下角所在单元格的行列
//X: 0-1024 Y:0-256 ； X1\X2相对本单元格，距离Y轴的偏移量，最大值1023；Y1\Y2相对本单元格，距离X轴的偏移量，最大值255；
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//picture.Resize(); //使图像恢复到原始大小
picBytes = null;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
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();
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int row, int column, int rowEnd, int columnEnd)
{
try
{
if (_myExcel is HSSFWorkbook)
{
HSSFPatriarch patriarch = (HSSFPatriarch)_activeSheet.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
else if (_myExcel is XSSFWorkbook)
{
XSSFDrawing drawing = (XSSFDrawing)_activeSheet.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
}
catch (Exception exception)
{
//Log.Write.Error("InsertPictures to file fail", exception);
}
}
public void TableToExcel(DataTable dt, Image img)
{
if (Path.GetExtension(fileName) == ".xls")
{
workbook = new HSSFWorkbook();
}
else if (Path.GetExtension(fileName) == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else
{
return;
}
ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);
MemoryStream ms = new MemoryStream();
img.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 2, 2, 14, 34);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, workbook.AddPicture(ms.ToArray(), PictureType.PNG));
pict.Resize();
ms.Close();
//表头
IRow row = sheet.CreateRow(anchor.Row2 + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//行数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow ir = sheet.CreateRow(i + anchor.Row2 + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = ir.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
//添加批注
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 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 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 TestCreateDrawings()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
HSSFPatriarch p1 = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFPatriarch p2 = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
Assert.AreSame(p1, p2);
}
public void insertPicture(string sheetName, Models.RowData modelRowData, System.Windows.Forms.TextBox textBox)
{
//删除空白的实例
for (int i = modelRowData.instances.Count - 1; i >= 0; i--)
{
bool isDelete = true;
Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i];
for (int j = 0; j < model_Instance.pictures.Count; j++)
{
if (model_Instance.pictures[j] != null)
{
isDelete = false;
}
}
if (isDelete)
{
modelRowData.instances.RemoveAt(i);
}
}
//delete sheet if exist
ISheet sheet = null;
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.CreateSheet(sheetName);
}
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
for (int i = 0; i < modelRowData.instances.Count; i++)
{
Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i];
for (int j = 0; j < model_Instance.pictures.Count; j++)
{
if (model_Instance.pictures[j] != null)
{
string picturePath = model_Instance.pictures[j].ToString();
//读取图片
byte[] bytes = System.IO.File.ReadAllBytes(picturePath);
int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
//add a picture
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255, 4 + (j * 12), 2 + (i * 32), 14 + (j * 12), 10 + (i * 32));
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
textBox.AppendText(" " + picturePath.Substring(picturePath.LastIndexOf("\\") + 1) + "\r\n");
}
}
}
fs = new FileStream(fileName, FileMode.Open, FileAccess.Write);
workbook.Write(fs);
fs.Close();
textBox.AppendText("Insert pictures succeed\r\n");
}
public void TestAutoFilter()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = (HSSFSheet)wb.CreateSheet();
InternalWorkbook iwb = wb.Workbook;
InternalSheet ish = sh.Sheet;
Assert.IsNull(iwb.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1));
Assert.IsNull(ish.FindFirstRecordBySid(AutoFilterInfoRecord.sid));
CellRangeAddress range = CellRangeAddress.ValueOf("A1:B10");
sh.SetAutoFilter(range);
NameRecord name = iwb.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1);
Assert.IsNotNull(name);
// The built-in name for auto-filter must consist of a single Area3d Ptg.
Ptg[] ptg = name.NameDefinition;
Assert.AreEqual(1, ptg.Length, "The built-in name for auto-filter must consist of a single Area3d Ptg");
Assert.IsTrue(ptg[0] is Area3DPtg, "The built-in name for auto-filter must consist of a single Area3d Ptg");
Area3DPtg aref = (Area3DPtg)ptg[0];
Assert.AreEqual(range.FirstColumn, aref.FirstColumn);
Assert.AreEqual(range.FirstRow, aref.FirstRow);
Assert.AreEqual(range.LastColumn, aref.LastColumn);
Assert.AreEqual(range.LastRow, aref.LastRow);
// verify AutoFilterInfoRecord
AutoFilterInfoRecord afilter = (AutoFilterInfoRecord)ish.FindFirstRecordBySid(AutoFilterInfoRecord.sid);
Assert.IsNotNull(afilter);
Assert.AreEqual(2, afilter.NumEntries); //filter covers two columns
HSSFPatriarch dr = (HSSFPatriarch)sh.DrawingPatriarch;
Assert.IsNotNull(dr);
HSSFSimpleShape comboBoxShape = (HSSFSimpleShape)dr.Children[0];
Assert.AreEqual(comboBoxShape.ShapeType, HSSFSimpleShape.OBJECT_TYPE_COMBO_BOX);
Assert.IsNull(ish.FindFirstRecordBySid(ObjRecord.sid)); // ObjRecord will appear after serializetion
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = (HSSFSheet)wb.GetSheetAt(0);
ish = sh.Sheet;
ObjRecord objRecord = (ObjRecord)ish.FindFirstRecordBySid(ObjRecord.sid);
IList subRecords = objRecord.SubRecords;
Assert.AreEqual(3, subRecords.Count);
Assert.IsTrue(subRecords[0] is CommonObjectDataSubRecord);
Assert.IsTrue(subRecords[1] is FtCblsSubRecord); // must be present, see Bug 51481
Assert.IsTrue(subRecords[2] is LbsDataSubRecord);
}