/**
* Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells.
*
* @return a new CT_TwoCellAnchor
*/
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor;
ctAnchor.from = (xssfanchor.GetFrom());
ctAnchor.to = (xssfanchor.GetTo());
ctAnchor.AddNewClientData();
xssfanchor.SetTo(ctAnchor.to);
xssfanchor.SetFrom(ctAnchor.from);
ST_EditAs aditAs;
switch (anchor.AnchorType)
{
case (int)AnchorType.DONT_MOVE_AND_RESIZE:
aditAs = ST_EditAs.absolute; break;
case (int)AnchorType.MOVE_AND_RESIZE:
aditAs = ST_EditAs.twoCell; break;
case (int)AnchorType.MOVE_DONT_RESIZE:
aditAs = ST_EditAs.oneCell; break;
default:
aditAs = ST_EditAs.oneCell;
break;
}
ctAnchor.editAs = aditAs;
ctAnchor.editAsSpecified = true;
return(ctAnchor);
}
public void TestCreate()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = (int)AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
public void TestReadAnchors()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4);
XSSFShape shape1 = Drawing.CreateTextbox(anchor1) as XSSFShape;
Assert.IsNotNull(shape1);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5);
XSSFShape shape2 = Drawing.CreateTextbox(anchor2) as XSSFShape;
Assert.IsNotNull(shape2);
int pictureIndex = wb.AddPicture(new byte[] { }, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6);
XSSFShape shape3 = Drawing.CreatePicture(anchor3, pictureIndex) as XSSFShape;
Assert.IsNotNull(shape3);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(3, shapes.Count);
Assert.AreEqual(shapes[0].GetAnchor(), anchor1);
Assert.AreEqual(shapes[1].GetAnchor(), anchor2);
Assert.AreEqual(shapes[2].GetAnchor(), anchor3);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor anchor)
{
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
XSSFSheet sheet = (XSSFSheet)GetParent();
//create comments and vmlDrawing parts if they don't exist
CommentsTable comments = sheet.GetCommentsTable(true);
XSSFVMLDrawing vml = sheet.GetVMLDrawing(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
// convert offsets from emus to pixels since we get a DrawingML-anchor
// but create a VML Drawing
int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL;
int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL;
int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL;
int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL;
String position =
ca.Col1 + ", " + dx1Pixels + ", " +
ca.Row1 + ", " + dy1Pixels + ", " +
ca.Col2 + ", " + dx2Pixels + ", " +
ca.Row2 + ", " + dy2Pixels;
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString();
if (comments.FindCellComment(ref1) != null)
{
throw new ArgumentException("Multiple cell comments in one cell are not allowed, cell: " + ref1);
}
return(new XSSFComment(comments, comments.NewComment(ref1), vmlShape));
}
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctTwoCellAnchor = this.drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor)anchor;
ctTwoCellAnchor.from = xssfClientAnchor.GetFrom();
ctTwoCellAnchor.to = xssfClientAnchor.GetTo();
ctTwoCellAnchor.AddNewClientData();
xssfClientAnchor.SetTo(ctTwoCellAnchor.to);
xssfClientAnchor.SetFrom(ctTwoCellAnchor.from);
NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs stEditAs;
switch (anchor.AnchorType)
{
case 0:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.twoCell;
break;
case 2:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.oneCell;
break;
case 3:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.absolute;
break;
default:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.oneCell;
break;
}
ctTwoCellAnchor.editAs = stEditAs;
ctTwoCellAnchor.editAsSpecified = true;
return(ctTwoCellAnchor);
}
public void TestBug56835CellComment()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
// first comment works
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4);
XSSFComment comment = Drawing.CreateCellComment(anchor) as XSSFComment;
Assert.IsNotNull(comment);
try
{
Drawing.CreateCellComment(anchor);
Assert.Fail("Should fail if we try to add the same comment for the same cell");
}
catch (ArgumentException e)
{
// expected
}
}
finally
{
wb.Close();
}
}
/**
* Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells.
*
* @return a new CT_TwoCellAnchor
*/
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor;
ctAnchor.from = (xssfanchor.From);
ctAnchor.to = (xssfanchor.To);
ctAnchor.AddNewClientData();
xssfanchor.To = ctAnchor.to;
xssfanchor.From = ctAnchor.from;
ST_EditAs aditAs;
switch (anchor.AnchorType)
{
case AnchorType.DontMoveAndResize:
aditAs = ST_EditAs.absolute; break;
case AnchorType.MoveAndResize:
aditAs = ST_EditAs.twoCell; break;
case AnchorType.MoveDontResize:
aditAs = ST_EditAs.oneCell; break;
default:
aditAs = ST_EditAs.oneCell;
break;
}
ctAnchor.editAs = aditAs;
ctAnchor.editAsSpecified = true;
return(ctAnchor);
}
public void TestCreate()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = (int)AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
//first picture
int imageId= LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
//second picture
int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);
picture.LineStyle = LineStyle.DashDotGel;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public XSSFShapeGroup CreateGroup(XSSFClientAnchor anchor)
{
CT_GroupShape ctGroup = this.CreateTwoCellAnchor((IClientAnchor)anchor).AddNewGrpSp();
ctGroup.Set(XSSFShapeGroup.Prototype());
XSSFShapeGroup xssfShapeGroup = new XSSFShapeGroup(this, ctGroup);
xssfShapeGroup.anchor = (XSSFAnchor)anchor;
return(xssfShapeGroup);
}
public XSSFConnector CreateConnector(XSSFClientAnchor anchor)
{
CT_Connector ctShape = this.CreateTwoCellAnchor((IClientAnchor)anchor).AddNewCxnSp();
ctShape.Set(XSSFConnector.Prototype());
XSSFConnector xssfConnector = new XSSFConnector(this, ctShape);
xssfConnector.anchor = (XSSFAnchor)anchor;
return(xssfConnector);
}
private XSSFGraphicFrame CreateGraphicFrame(XSSFClientAnchor anchor)
{
CT_GraphicalObjectFrame ctGraphicFrame = this.CreateTwoCellAnchor((IClientAnchor)anchor).AddNewGraphicFrame();
ctGraphicFrame.Set(XSSFGraphicFrame.Prototype());
long num = this.numOfGraphicFrames++;
return(new XSSFGraphicFrame(this, ctGraphicFrame)
{
Anchor = anchor, Id = num, Name = "Diagramm" + (object)num
});
}
public XSSFSimpleShape CreateSimpleShape(XSSFClientAnchor anchor)
{
long num = this.newShapeId();
NPOI.OpenXmlFormats.Dml.Spreadsheet.CT_Shape ctShape = this.CreateTwoCellAnchor((IClientAnchor)anchor).AddNewSp();
ctShape.Set(XSSFSimpleShape.Prototype());
ctShape.nvSpPr.cNvPr.id = (uint)num;
XSSFSimpleShape xssfSimpleShape = new XSSFSimpleShape(this, ctShape);
xssfSimpleShape.anchor = (XSSFAnchor)anchor;
return(xssfSimpleShape);
}
public override bool Equals(Object o)
{
if (o == null || !(o is XSSFClientAnchor))
{
return(false);
}
XSSFClientAnchor anchor = (XSSFClientAnchor)o;
return(cell1.ToString().Equals(anchor.GetFrom().ToString()) &&
cell2.ToString().Equals(anchor.GetTo().ToString()));
}
/**
* Creates a simple shape. This includes such shapes as lines, rectangles,
* and ovals.
*
* @param anchor the client anchor describes how this group is attached
* to the sheet.
* @return the newly Created shape.
*/
public XSSFShapeGroup CreateGroup(XSSFClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_GroupShape ctGroup = ctAnchor.AddNewGrpSp();
ctGroup.Set(XSSFShapeGroup.Prototype());
XSSFShapeGroup shape = new XSSFShapeGroup(this, ctGroup);
shape.anchor = anchor;
return(shape);
}
/**
* Creates a simple shape. This includes such shapes as lines, rectangles,
* and ovals.
*
* @param anchor the client anchor describes how this group is attached
* to the sheet.
* @return the newly Created shape.
*/
public XSSFConnector CreateConnector(XSSFClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_Connector ctShape = ctAnchor.AddNewCxnSp();
ctShape.Set(XSSFConnector.Prototype());
XSSFConnector shape = new XSSFConnector(this, ctShape);
shape.anchor = anchor;
return(shape);
}
/**
* Creates a simple shape. This includes such shapes as lines, rectangles,
* and ovals.
*
* @param anchor the client anchor describes how this group is attached
* to the sheet.
* @return the newly Created shape.
*/
public XSSFSimpleShape CreateSimpleShape(XSSFClientAnchor anchor)
{
long shapeId = newShapeId();
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_Shape ctShape = ctAnchor.AddNewSp();
ctShape.Set(XSSFSimpleShape.Prototype());
ctShape.nvSpPr.cNvPr.id = (uint)(shapeId);
XSSFSimpleShape shape = new XSSFSimpleShape(this, ctShape);
shape.anchor = anchor;
return(shape);
}
public XSSFPicture CreatePicture(XSSFClientAnchor anchor, int pictureIndex)
{
PackageRelationship rel = this.GetDrawing().AddPictureReference(pictureIndex);
CT_Picture ctPicture = this.ctGroup.AddNewPic();
ctPicture.Set(XSSFPicture.Prototype());
XSSFPicture xssfPicture = new XSSFPicture(this.GetDrawing(), ctPicture);
xssfPicture.parent = this;
xssfPicture.anchor = (XSSFAnchor)anchor;
xssfPicture.SetPictureReference(rel);
return(xssfPicture);
}
public IPicture CreatePicture(XSSFClientAnchor anchor, int pictureIndex)
{
PackageRelationship rel = this.AddPictureReference(pictureIndex);
long num = this.newShapeId();
CT_Picture ctPicture = this.CreateTwoCellAnchor((IClientAnchor)anchor).AddNewPic();
ctPicture.Set(XSSFPicture.Prototype());
ctPicture.nvPicPr.cNvPr.id = (uint)num;
XSSFPicture xssfPicture = new XSSFPicture(this, ctPicture);
xssfPicture.anchor = (XSSFAnchor)anchor;
xssfPicture.SetPictureReference(rel);
return((IPicture)xssfPicture);
}
public override bool Equals(object o)
{
if (o == null || !(o is XSSFClientAnchor))
{
return(false);
}
XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor)o;
if (this.Dx1 == xssfClientAnchor.Dx1 && this.Dx2 == xssfClientAnchor.Dx2 && (this.Dy1 == xssfClientAnchor.Dy1 && this.Dy2 == xssfClientAnchor.Dy2) && (this.Col1 == xssfClientAnchor.Col1 && this.Col2 == xssfClientAnchor.Col2 && this.Row1 == xssfClientAnchor.Row1))
{
return(this.Row2 == xssfClientAnchor.Row2);
}
return(false);
}
/**
* Creates a picture.
*
* @param anchor the client anchor describes how this picture is attached to the sheet.
* @param pictureIndex the index of the picture in the workbook collection of pictures,
* {@link XSSFWorkbook#getAllPictures()} .
* @return the newly Created picture shape.
*/
public XSSFPicture CreatePicture(XSSFClientAnchor anchor, int pictureIndex)
{
PackageRelationship rel = GetDrawing().AddPictureReference(pictureIndex);
CT_Picture ctShape = ctGroup.AddNewPic();
ctShape.Set(XSSFPicture.Prototype());
XSSFPicture shape = new XSSFPicture(GetDrawing(), ctShape);
shape.parent = this;
shape.anchor = anchor;
shape.SetPictureReference(rel);
return(shape);
}
/**
* Creates a new graphic frame.
*
* @param anchor the client anchor describes how this frame is attached
* to the sheet
* @return the newly Created graphic frame
*/
private XSSFGraphicFrame CreateGraphicFrame(XSSFClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_GraphicalObjectFrame ctGraphicFrame = ctAnchor.AddNewGraphicFrame();
ctGraphicFrame.Set(XSSFGraphicFrame.Prototype());
long frameId = numOfGraphicFrames++;
XSSFGraphicFrame graphicFrame = new XSSFGraphicFrame(this, ctGraphicFrame);
graphicFrame.Anchor = anchor;
graphicFrame.Id = frameId;
graphicFrame.Name = "Diagramm" + frameId;
return(graphicFrame);
}
public void TestAddChartsToNewWorkbook()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s1 = (XSSFSheet)wb.CreateSheet();
XSSFDrawing d1 = (XSSFDrawing)s1.CreateDrawingPatriarch();
XSSFClientAnchor a1 = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
XSSFChart c1 = (XSSFChart)d1.CreateChart(a1);
Assert.AreEqual(1, d1.GetCharts().Count);
Assert.IsNotNull(c1.GetGraphicFrame());
Assert.IsNotNull(c1.GetOrCreateLegend());
XSSFClientAnchor a2 = new XSSFClientAnchor(0, 0, 0, 0, 1, 11, 10, 60);
XSSFChart c2 = (XSSFChart)d1.CreateChart(a2);
Assert.AreEqual(2, d1.GetCharts().Count);
}
public IComment CreateCellComment(IClientAnchor anchor)
{
XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor)anchor;
XSSFSheet parent = (XSSFSheet)this.GetParent();
CommentsTable commentsTable = parent.GetCommentsTable(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = parent.GetVMLDrawing(true).newCommentShape();
if (xssfClientAnchor.IsSet())
{
string str = xssfClientAnchor.Col1.ToString() + ", 0, " + (object)xssfClientAnchor.Row1 + ", 0, " + (object)xssfClientAnchor.Col2 + ", 0, " + (object)xssfClientAnchor.Row2 + ", 0";
vmlShape.GetClientDataArray(0).SetAnchorArray(0, str);
}
return((IComment) new XSSFComment(commentsTable, commentsTable.CreateComment(), vmlShape)
{
Column = xssfClientAnchor.Col1, Row = xssfClientAnchor.Row1
});
}
private XSSFAnchor GetAnchorFromIEGAnchor(IEG_Anchor ctAnchor)
{
CT_Marker ctFrom = null, ctTo = null;
if (ctAnchor is CT_TwoCellAnchor)
{
ctFrom = ((CT_TwoCellAnchor)ctAnchor).from;
ctTo = ((CT_TwoCellAnchor)ctAnchor).to;
}
else if (ctAnchor is CT_OneCellAnchor)
{
ctFrom = ((CT_OneCellAnchor)ctAnchor).from;
}
XSSFAnchor anchor = new XSSFClientAnchor(ctFrom, ctTo);
return(anchor);
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(anchor);
c1.LineWidth = 3;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(anchor);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(anchor);
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(anchor);
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
}
/**
* Creates a picture.
*
* @param anchor the client anchor describes how this picture is attached to the sheet.
* @param pictureIndex the index of the picture in the workbook collection of pictures,
* {@link NPOI.xssf.usermodel.XSSFWorkbook#getAllPictures()} .
*
* @return the newly Created picture shape.
*/
public IPicture CreatePicture(XSSFClientAnchor anchor, int pictureIndex)
{
PackageRelationship rel = AddPictureReference(pictureIndex);
long shapeId = newShapeId();
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
NPOI.OpenXmlFormats.Dml.Spreadsheet.CT_Picture ctShape = ctAnchor.AddNewPic();
ctShape.Set(XSSFPicture.Prototype());
ctShape.nvPicPr.cNvPr.id = (uint)shapeId;
XSSFPicture shape = new XSSFPicture(this, ctShape);
shape.anchor = anchor;
shape.SetPictureReference(rel);
return(shape);
}
public override bool Equals(Object o)
{
if (o == null || !(o is XSSFClientAnchor))
{
return(false);
}
XSSFClientAnchor anchor = (XSSFClientAnchor)o;
return(Dx1 == anchor.Dx1 &&
Dx2 == anchor.Dx2 &&
Dy1 == anchor.Dy1 &&
Dy2 == anchor.Dy2 &&
Col1 == anchor.Col1 &&
Col2 == anchor.Col2 &&
Row1 == anchor.Row1 &&
Row2 == anchor.Row2);
}
public void TestAddChartsToNewWorkbook()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s1 = (XSSFSheet)wb.CreateSheet();
XSSFDrawing d1 = (XSSFDrawing)s1.CreateDrawingPatriarch();
XSSFClientAnchor a1 = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
XSSFChart c1 = (XSSFChart)d1.CreateChart(a1);
Assert.AreEqual(1, d1.GetCharts().Count);
Assert.IsNotNull(c1.GetGraphicFrame());
Assert.IsNotNull(c1.GetOrCreateLegend());
XSSFClientAnchor a2 = new XSSFClientAnchor(0, 0, 0, 0, 1, 11, 10, 60);
XSSFChart c2 = (XSSFChart)d1.CreateChart(a2);
Assert.AreEqual(2, d1.GetCharts().Count);
}
public void TestShapeId()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
byte[] jpegData = Encoding.UTF8.GetBytes("picture1");
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape1 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)1, shape1.GetCTPicture().nvPicPr.cNvPr.id);
jpegData = Encoding.UTF8.GetBytes("picture2");
jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape2 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)2, shape2.GetCTPicture().nvPicPr.cNvPr.id);
}
private XSSFAnchor GetAnchorFromParent(XmlNode obj)
{
XSSFAnchor anchor = null;
XmlNode parentNode = obj.ParentNode;
XmlNode fromNode = parentNode.SelectSingleNode("xdr:from", POIXMLDocumentPart.NamespaceManager);
if (fromNode == null)
{
throw new InvalidDataException("xdr:from node is missing");
}
CT_Marker ctFrom = CT_Marker.Parse(fromNode, POIXMLDocumentPart.NamespaceManager);
XmlNode toNode = parentNode.SelectSingleNode("xdr:to", POIXMLDocumentPart.NamespaceManager);
CT_Marker ctTo = null;
if (toNode != null)
{
ctTo = CT_Marker.Parse(toNode, POIXMLDocumentPart.NamespaceManager);
}
anchor = new XSSFClientAnchor(ctFrom, ctTo);
return(anchor);
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List<POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(anchor);
c1.LineWidth=3;
c1.LineStyle= SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(anchor);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(anchor);
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(anchor);
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill=(true);
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor anchor)
{
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
XSSFSheet sheet = (XSSFSheet)GetParent();
//create comments and vmlDrawing parts if they don't exist
CommentsTable comments = sheet.GetCommentsTable(true);
XSSFVMLDrawing vml = sheet.GetVMLDrawing(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
String position =
ca.Col1 + ", 0, " + ca.Row1 + ", 0, " +
ca.Col2 + ", 0, " + ca.Row2 + ", 0";
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString();
XSSFComment shape = new XSSFComment(comments, comments.NewComment(ref1), vmlShape);
return(shape);
}
private XSSFAnchor GetAnchorFromIEGAnchor(IEG_Anchor ctAnchor)
{
CT_Marker ctFrom=null, ctTo=null;
if (ctAnchor is CT_TwoCellAnchor)
{
ctFrom = ((CT_TwoCellAnchor)ctAnchor).from;
ctTo = ((CT_TwoCellAnchor)ctAnchor).to;
}
else if (ctAnchor is CT_OneCellAnchor)
{
ctFrom = ((CT_OneCellAnchor)ctAnchor).from;
}
XSSFAnchor anchor = new XSSFClientAnchor(ctFrom, ctTo);
return anchor;
}
/**
* Creates a new graphic frame.
*
* @param anchor the client anchor describes how this frame is attached
* to the sheet
* @return the newly Created graphic frame
*/
private XSSFGraphicFrame CreateGraphicFrame(XSSFClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_GraphicalObjectFrame ctGraphicFrame = ctAnchor.AddNewGraphicFrame();
ctGraphicFrame.Set(XSSFGraphicFrame.Prototype());
long frameId = numOfGraphicFrames++;
XSSFGraphicFrame graphicFrame = new XSSFGraphicFrame(this, ctGraphicFrame);
graphicFrame.Anchor = anchor;
graphicFrame.Id = frameId;
graphicFrame.Name = "Diagramm" + frameId;
return graphicFrame;
}
/**
* Creates a simple shape. This includes such shapes as lines, rectangles,
* and ovals.
*
* @param anchor the client anchor describes how this group is attached
* to the sheet.
* @return the newly Created shape.
*/
public XSSFShapeGroup CreateGroup(XSSFClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_GroupShape ctGroup = ctAnchor.AddNewGrpSp();
ctGroup.Set(XSSFShapeGroup.Prototype());
XSSFShapeGroup shape = new XSSFShapeGroup(this, ctGroup);
shape.anchor = anchor;
return shape;
}
/**
* Creates a simple shape. This includes such shapes as lines, rectangles,
* and ovals.
*
* @param anchor the client anchor describes how this group is attached
* to the sheet.
* @return the newly Created shape.
*/
public XSSFConnector CreateConnector(XSSFClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_Connector ctShape = ctAnchor.AddNewCxnSp();
ctShape.Set(XSSFConnector.Prototype());
XSSFConnector shape = new XSSFConnector(this, ctShape);
shape.anchor = anchor;
return shape;
}
/**
* Creates a simple shape. This includes such shapes as lines, rectangles,
* and ovals.
*
* @param anchor the client anchor describes how this group is attached
* to the sheet.
* @return the newly Created shape.
*/
public XSSFSimpleShape CreateSimpleShape(XSSFClientAnchor anchor)
{
long shapeId = newShapeId();
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_Shape ctShape = ctAnchor.AddNewSp();
ctShape.Set(XSSFSimpleShape.Prototype());
ctShape.nvSpPr.cNvPr.id=(uint)(shapeId);
XSSFSimpleShape shape = new XSSFSimpleShape(this, ctShape);
shape.anchor = anchor;
return shape;
}
/**
* Creates a picture.
*
* @param anchor the client anchor describes how this picture is attached to the sheet.
* @param pictureIndex the index of the picture in the workbook collection of pictures,
* {@link NPOI.xssf.usermodel.XSSFWorkbook#getAllPictures()} .
*
* @return the newly Created picture shape.
*/
public IPicture CreatePicture(XSSFClientAnchor anchor, int pictureIndex)
{
PackageRelationship rel = AddPictureReference(pictureIndex);
long shapeId = newShapeId();
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_Picture ctShape = ctAnchor.AddNewPic();
ctShape.Set(XSSFPicture.Prototype());
ctShape.nvPicPr.cNvPr.id = (uint)shapeId;
ctShape.nvPicPr.cNvPr.name = "Picture " + shapeId;
XSSFPicture shape = new XSSFPicture(this, ctShape);
shape.anchor = anchor;
shape.SetPictureReference(rel);
return shape;
}
public void TestBug56835CellComment()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
// first comment works
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4);
XSSFComment comment = Drawing.CreateCellComment(anchor) as XSSFComment;
Assert.IsNotNull(comment);
try
{
Drawing.CreateCellComment(anchor);
Assert.Fail("Should fail if we try to add the same comment for the same cell");
}
catch (ArgumentException e)
{
// expected
}
}
finally
{
wb.Close();
}
}
public void TestShapeId()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
byte[] jpegData = Encoding.UTF8.GetBytes("picture1");
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape1 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)1, shape1.GetCTPicture().nvPicPr.cNvPr.id);
jpegData = Encoding.UTF8.GetBytes("picture2");
jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape2 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)2, shape2.GetCTPicture().nvPicPr.cNvPr.id);
}
/**
* Creates a picture.
*
* @param anchor the client anchor describes how this picture is attached to the sheet.
* @param pictureIndex the index of the picture in the workbook collection of pictures,
* {@link XSSFWorkbook#getAllPictures()} .
* @return the newly Created picture shape.
*/
public XSSFPicture CreatePicture(XSSFClientAnchor anchor, int pictureIndex)
{
PackageRelationship rel = GetDrawing().AddPictureReference(pictureIndex);
CT_Picture ctShape = ctGroup.AddNewPic();
ctShape.Set(XSSFPicture.Prototype());
XSSFPicture shape = new XSSFPicture(GetDrawing(), ctShape);
shape.parent = this;
shape.anchor = anchor;
shape.SetPictureReference(rel);
return shape;
}
/**
* Attaches frame to an anchor.
*/
public void SetAnchor(XSSFClientAnchor anchor)
{
this.anchor = anchor;
}
public void TestReadAnchors()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4);
XSSFShape shape1 = Drawing.CreateTextbox(anchor1) as XSSFShape;
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5);
XSSFShape shape2 = Drawing.CreateTextbox(anchor2) as XSSFShape;
int pictureIndex = wb.AddPicture(new byte[] { }, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6);
XSSFShape shape3 = Drawing.CreatePicture(anchor3, pictureIndex) as XSSFShape;
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List<XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(3, shapes.Count);
Assert.AreEqual(shapes[0].GetAnchor(), anchor1);
Assert.AreEqual(shapes[1].GetAnchor(), anchor2);
Assert.AreEqual(shapes[2].GetAnchor(), anchor3);
}
private XSSFAnchor GetAnchorFromParent(XmlNode obj)
{
XSSFAnchor anchor = null;
XmlNode parentNode = obj.ParentNode;
XmlNode fromNode = parentNode.SelectSingleNode("xdr:from", POIXMLDocumentPart.NamespaceManager);
if(fromNode==null)
throw new InvalidDataException("xdr:from node is missing");
CT_Marker ctFrom = CT_Marker.Parse(fromNode, POIXMLDocumentPart.NamespaceManager);
XmlNode toNode = parentNode.SelectSingleNode("xdr:to", POIXMLDocumentPart.NamespaceManager);
CT_Marker ctTo=null;
if (toNode != null)
{
ctTo = CT_Marker.Parse(toNode, POIXMLDocumentPart.NamespaceManager);
}
anchor = new XSSFClientAnchor(ctFrom, ctTo);
return anchor;
}
public void TestBug58175()
{
IWorkbook wb = new SXSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(3);
cell.SetCellValue("F4");
ICreationHelper factory = wb.GetCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex + 1);
anchor.Row1 = (row.RowNum);
anchor.Row2 = (row.RowNum + 3);
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
// create comments and vmlDrawing parts if they don't exist
CommentsTable comments = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetCommentsTable(true);
XSSFVMLDrawing vml = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetVMLDrawing(true);
CT_Shape vmlShape1 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape1.GetClientDataArray(0).SetAnchorArray(0, position);
}
// create the comment in two different ways and verify that there is no difference
XSSFComment shape1 = new XSSFComment(comments, comments.NewComment(CellAddress.A1), vmlShape1);
shape1.Column = (ca.Col1);
shape1.Row = (ca.Row1);
CT_Shape vmlShape2 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape2.GetClientDataArray(0).SetAnchorArray(0, position);
}
CellAddress ref1 = new CellAddress(ca.Row1, ca.Col1);
XSSFComment shape2 = new XSSFComment(comments, comments.NewComment(ref1), vmlShape2);
Assert.AreEqual(shape1.Author, shape2.Author);
Assert.AreEqual(shape1.ClientAnchor, shape2.ClientAnchor);
Assert.AreEqual(shape1.Column, shape2.Column);
Assert.AreEqual(shape1.Row, shape2.Row);
Assert.AreEqual(shape1.GetCTComment().ToString(), shape2.GetCTComment().ToString());
Assert.AreEqual(shape1.GetCTComment().@ref, shape2.GetCTComment().@ref);
/*CommentsTable table1 = shape1.CommentsTable;
* CommentsTable table2 = shape2.CommentsTable;
* Assert.AreEqual(table1.CTComments.toString(), table2.CTComments.toString());
* Assert.AreEqual(table1.NumberOfComments, table2.NumberOfComments);
* Assert.AreEqual(table1.Relations, table2.Relations);*/
Assert.AreEqual(vmlShape1.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
vmlShape2.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
"The vmlShapes should have equal content afterwards");
}
finally
{
wb.Close();
}
}
/// <summary>
/// Creates an excel comment in each cell with an associated error
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="sheet"></param>
private void HighlightErrors(ISheet excelSheet, ICOBieSheet<COBieRow> sheet)
{
//sort by row then column
var errors = sheet.Errors.OrderBy(err => err.Row).ThenBy(err => err.Column);
// The patriarch is a container for comments on a sheet
IDrawing patr = excelSheet.CreateDrawingPatriarch();
int sheetCommnetCount = 0;
foreach (var error in errors)
{
if (error.Row > 0 && error.Column >= 0)
{
if ((error.Row + 3) > 65280)//UInt16.MaxValue some reason the CreateCellComment has 65280 as the max row number
{
// TODO: Warn overflow of XLS 2003 worksheet
break;
}
//limit comments to 1000 per sheet
if (sheetCommnetCount == 999)
break;
IRow excelRow = excelSheet.GetRow(error.Row);
if (excelRow != null)
{
ICell excelCell = excelRow.GetCell(error.Column);
if (excelCell != null)
{
string description = error.ErrorDescription;
if(hasErrorLevel)
{
if (error.ErrorLevel == COBieError.ErrorLevels.Warning)
description = "Warning: " + description;
else
description = "Error: " + description;
}
if (excelCell.CellComment == null)
{
try
{
// A client anchor is attached to an excel worksheet. It anchors against a top-left and bottom-right cell.
// Create a comment 3 columns wide and 3 rows height
IClientAnchor anchor = null;
if (IsXlsx)
{
anchor = new XSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
else
{
anchor = new HSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
IComment comment = patr.CreateCellComment(anchor);
IRichTextString str = null;
if (IsXlsx)
{
str = new XSSFRichTextString(description);
}
else
{
str = new HSSFRichTextString(description);
}
comment.String = str;
comment.Author = "XBim";
excelCell.CellComment = comment;
_commentCount++;
sheetCommnetCount++;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
else
{
if (IsXlsx)
{
((XSSFRichTextString)excelCell.CellComment.String).Append(" Also " + description);
}
else
{
description = excelCell.CellComment.String.ToString() + " Also " + description;
excelCell.CellComment.String = new HSSFRichTextString(description);
}
}
}
}
}
}
}