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));
}
public void TestGetCharts()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithThreeCharts.xlsx");
XSSFSheet s1 = (XSSFSheet)wb.GetSheetAt(0);
XSSFSheet s2 = (XSSFSheet)wb.GetSheetAt(1);
XSSFSheet s3 = (XSSFSheet)wb.GetSheetAt(2);
Assert.AreEqual(0, (s1.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
Assert.AreEqual(2, (s2.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
Assert.AreEqual(1, (s3.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
// Check the titles
XSSFChart chart = (s2.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[(0)];
Assert.AreEqual(null, chart.Title);
chart = (s2.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[(1)];
Assert.AreEqual("Pie Chart Title Thingy", chart.Title.String);
chart = (s3.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[(0)];
Assert.AreEqual("Sheet 3 Chart with Title", chart.Title.String);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void multiRelationShips()
{
XSSFWorkbook wb = new XSSFWorkbook();
byte[] pic1Data = Encoding.UTF8.GetBytes("test jpeg data");
byte[] pic2Data = Encoding.UTF8.GetBytes("test png data");
List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(0, pictures.Count);
int pic1 = wb.AddPicture(pic1Data, XSSFWorkbook.PICTURE_TYPE_JPEG);
int pic2 = wb.AddPicture(pic2Data, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFSheet sheet1 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape1 = drawing1.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
XSSFPicture shape2 = drawing1.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFSheet sheet2 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape3 = drawing2.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFPicture shape4 = drawing2.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
Assert.AreEqual(2, pictures.Count);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(2, pictures.Count);
sheet1 = wb.GetSheetAt(0) as XSSFSheet;
drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape11 = (XSSFPicture)drawing1.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape1.PictureData.Data, shape11.PictureData.Data));
XSSFPicture shape22 = (XSSFPicture)drawing1.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape2.PictureData.Data, shape22.PictureData.Data));
sheet2 = wb.GetSheetAt(1) as XSSFSheet;
drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape33 = (XSSFPicture)drawing2.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape3.PictureData.Data, shape33.PictureData.Data));
XSSFPicture shape44 = (XSSFPicture)drawing2.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape4.PictureData.Data, shape44.PictureData.Data));
}
public void TestRichText()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromRgb(0, 128, 128)));
font.IsItalic = (true);
font.IsBold = (true);
font.Underline = FontUnderlineType.Single;
rt.ApplyFont(font);
shape.SetText(rt);
CT_TextParagraph pr = shape.GetCTShape().txBody.p[0];
Assert.AreEqual(1, pr.SizeOfRArray());
CT_TextCharacterProperties rPr = pr.r[0].rPr;
Assert.AreEqual(true, rPr.b);
Assert.AreEqual(true, rPr.i);
Assert.AreEqual(ST_TextUnderlineType.sng, rPr.u);
Assert.IsTrue(Arrays.Equals(
new byte[] { 0, (byte)128, (byte)128 },
rPr.solidFill.srgbClr.val));
checkRewrite(wb);
wb.Close();
}
public void TestRichTextFontAndColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromRgb(0, 128, 128)));
font.FontName = ("Arial");
rt.ApplyFont(font);
shape.SetText(rt);
CT_TextParagraph pr = shape.GetCTShape().txBody.GetPArray(0);
Assert.AreEqual(1, pr.SizeOfRArray());
CT_TextCharacterProperties rPr = pr.GetRArray(0).rPr;
Assert.AreEqual("Arial", rPr.latin.typeface);
Assert.IsTrue(Arrays.Equals(
new byte[] { 0, (byte)128, (byte)128 },
rPr.solidFill.srgbClr.val));
checkRewrite(wb);
wb.Close();
}
public void TestAddMultipleParagraphs()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
XSSFTextParagraph para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 1");
para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 2");
para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 3");
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(4, paras.Count); // this should be 4 as XSSFSimpleShape Creates a default paragraph (no text), and then we Added 3 paragraphs
Assert.AreEqual("Line 1\nLine 2\nLine 3", shape.Text);
checkRewrite(wb);
wb.Close();
}
public void TestSetAddMultipleParagraphs()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
shape.SetText("Line 1");
XSSFTextParagraph para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 2");
para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 3");
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(3, paras.Count); // this should be 3 as we overwrote the default paragraph with SetText, then Added 2 new paragraphs
Assert.AreEqual("Line 1\nLine 2\nLine 3", shape.Text);
checkRewrite(wb);
wb.Close();
}
public void TestReadTextBox()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
//the sheet has one relationship and it is XSSFDrawing
List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
POIXMLDocumentPart.RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(6, shapes.Count);
Assert.IsTrue(shapes[4] is XSSFSimpleShape);
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[4];
Assert.AreEqual("Sheet with various pictures\n(jpeg, png, wmf, emf and pict)", textbox.Text);
checkRewrite(wb);
wb.Close();
}
public void Create()
{
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 = 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 TestReadTextBox2()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox2.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = drawing.GetShapes();
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0];
String extracted = textbox.Text;
StringBuilder sb = new StringBuilder();
sb.Append("1. content1A\n");
sb.Append("\t1. content1B\n");
sb.Append("\t2. content2B\n");
sb.Append("\t3. content3B\n");
sb.Append("2. content2A\n");
sb.Append("\t3. content2BStartAt3\n");
sb.Append("\t\n\t\n\t");
sb.Append("4. content2BStartAt3Incremented\n");
sb.Append("\t\n\t\n\t\n\t");
Assert.AreEqual(sb.ToString(), extracted);
checkRewrite(wb);
wb.Close();
}
public void SetString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet();
XSSFComment comment = (XSSFComment)sh.CreateDrawingPatriarch().CreateCellComment(new XSSFClientAnchor());
//passing HSSFRichTextString is incorrect
try
{
comment.String = (new HSSFRichTextString(TEST_RICHTEXTSTRING));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Only XSSFRichTextString argument is supported", e.Message);
}
//simple string argument
comment.SetString(TEST_RICHTEXTSTRING);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//if the text is already Set, it should be overridden, not Added twice!
comment.SetString(TEST_RICHTEXTSTRING);
CT_Comment ctComment = comment.GetCTComment();
// Assert.Fail("TODO test case incomplete!?");
//XmlObject[] obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//sequential call of comment.String should return the same XSSFRichTextString object
Assert.AreSame(comment.String, comment.String);
XSSFRichTextString richText = new XSSFRichTextString(TEST_RICHTEXTSTRING);
XSSFFont font1 = (XSSFFont)wb.CreateFont();
font1.FontName = ("Tahoma");
font1.FontHeight = 8.5;
font1.IsItalic = true;
font1.Color = IndexedColors.BlueGrey.Index;
richText.ApplyFont(0, 5, font1);
//check the low-level stuff
comment.String = richText;
//obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreSame(comment.String, richText);
//check that the rich text is Set in the comment
CT_RPrElt rPr = richText.GetCTRst().GetRArray(0).rPr;
Assert.AreEqual(true, rPr.GetIArray(0).val);
Assert.AreEqual(8.5, rPr.GetSzArray(0).val);
Assert.AreEqual(IndexedColors.BlueGrey.Index, (short)rPr.GetColorArray(0).indexed);
Assert.AreEqual("Tahoma", rPr.GetRFontArray(0).val);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
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)
{
// expected
}
}
finally
{
wb.Close();
}
}
public void Test53568()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53568.xlsx");
List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.IsNotNull(pictures);
Assert.AreEqual(4, pictures.Count);
XSSFSheet sheet1 = wb.GetSheetAt(0) as XSSFSheet;
List <XSSFShape> shapes1 = (sheet1.CreateDrawingPatriarch() as XSSFDrawing).GetShapes();
Assert.IsNotNull(shapes1);
Assert.AreEqual(5, shapes1.Count);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
XSSFSheet sheet = wb.GetSheetAt(i) as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
foreach (XSSFShape shape in Drawing.GetShapes())
{
if (shape is XSSFPicture)
{
XSSFPicture pic = (XSSFPicture)shape;
XSSFPictureData picData = pic.PictureData as XSSFPictureData;
Assert.IsNotNull(picData);
}
}
}
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing Drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
byte[] wmfData = Encoding.UTF8.GetBytes("test wmf data");
byte[] pngData = Encoding.UTF8.GetBytes("test png 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));
int wmfIdx = wb.AddPicture(wmfData, PictureType.WMF);
Assert.AreEqual(2, pictures.Count);
Assert.AreEqual("wmf", ((XSSFPictureData)pictures[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures[wmfIdx]).Data));
int pngIdx = wb.AddPicture(pngData, PictureType.PNG);
Assert.AreEqual(3, pictures.Count);
Assert.AreEqual("png", ((XSSFPictureData)pictures[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures[pngIdx]).Data));
//TODO finish usermodel API for XSSFPicture
XSSFPicture p1 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), jpegIdx);
Assert.IsNotNull(p1);
XSSFPicture p2 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), wmfIdx);
Assert.IsNotNull(p1);
XSSFPicture p3 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), pngIdx);
Assert.IsNotNull(p1);
//check that the Added pictures are accessible After write
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
IList pictures2 = wb.GetAllPictures();
Assert.AreEqual(3, pictures2.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures2[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures2[jpegIdx]).Data));
Assert.AreEqual("wmf", ((XSSFPictureData)pictures2[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures2[wmfIdx]).Data));
Assert.AreEqual("png", ((XSSFPictureData)pictures2[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures2[pngIdx]).Data));
}
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);
}
public void TestXSSFSimpleShapeCausesNPE56514()
{
XSSFWorkbook wb1 = XSSFTestDataSamples.OpenSampleWorkbook("56514.xlsx");
XSSFSheet sheet = wb1.GetSheetAt(0) as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
wb2.Close();
}
public void TestMultipleDrawings()
{
XSSFWorkbook wb = new XSSFWorkbook();
for (int i = 0; i < 3; i++)
{
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
}
OPCPackage pkg = wb.Package;
Assert.AreEqual(3, pkg.GetPartsByContentType(XSSFRelation.DRAWINGS.ContentType).Count);
}
public void WriteRead()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithVariousData.xlsx");
XSSFSheet sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
XSSFSheet sheet2 = (XSSFSheet)workbook.GetSheetAt(1);
Assert.IsTrue(sheet1.HasComments);
Assert.IsFalse(sheet2.HasComments);
// Change on comment on sheet 1, and add another into
// sheet 2
IRow r5 = sheet1.GetRow(4);
IComment cc5 = r5.GetCell(2).CellComment;
cc5.Author = ("Apache POI");
cc5.String = (new XSSFRichTextString("Hello!"));
IRow r2s2 = sheet2.CreateRow(2);
ICell c1r2s2 = r2s2.CreateCell(1);
Assert.IsNull(c1r2s2.CellComment);
IDrawing dg = sheet2.CreateDrawingPatriarch();
IComment cc2 = dg.CreateCellComment(new XSSFClientAnchor());
cc2.Author = ("Also POI");
cc2.String = (new XSSFRichTextString("A new comment"));
c1r2s2.CellComment = (cc2);
// Save, and re-load the file
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
// Check we still have comments where we should do
sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
sheet2 = (XSSFSheet)workbook.GetSheetAt(1);
Assert.IsNotNull(sheet1.GetRow(4).GetCell(2).CellComment);
Assert.IsNotNull(sheet1.GetRow(6).GetCell(2).CellComment);
Assert.IsNotNull(sheet2.GetRow(2).GetCell(1).CellComment);
// And check they still have the contents they should do
Assert.AreEqual("Apache POI",
sheet1.GetRow(4).GetCell(2).CellComment.Author);
Assert.AreEqual("Nick Burch",
sheet1.GetRow(6).GetCell(2).CellComment.Author);
Assert.AreEqual("Also POI",
sheet2.GetRow(2).GetCell(1).CellComment.Author);
Assert.AreEqual("Hello!",
sheet1.GetRow(4).GetCell(2).CellComment.String.String);
}
public void TestAccessMethods()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30) as XSSFClientAnchor;
XSSFChart chart = Drawing.CreateChart(anchor) as XSSFChart;
XSSFCategoryAxis axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom) as XSSFCategoryAxis;
axis.Crosses = (AxisCrosses.AutoZero);
Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void TestGetCharts()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("chart_sheet.xlsx");
XSSFSheet ns = (XSSFSheet)wb.GetSheetAt(0);
XSSFChartSheet cs = (XSSFChartSheet)wb.GetSheetAt(2);
Assert.AreEqual(0, (ns.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
Assert.AreEqual(1, (cs.CreateDrawingPatriarch() as XSSFDrawing).GetCharts().Count);
XSSFChart chart = (cs.CreateDrawingPatriarch() as XSSFDrawing).GetCharts()[0];
Assert.AreEqual(null, chart.Title);
}
public void Bug51158()
{
// create a workbook
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(3) as XSSFCell;
cell.SetCellValue("test1");
//XSSFCreationHelper helper = workbook.GetCreationHelper();
//cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));
XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;
Assert.IsNotNull(comment);
comment.SetString("some comment");
// ICellStyle cs = workbook.CreateCellStyle();
// cs.ShrinkToFit=(/*setter*/false);
// row.CreateCell(0).CellStyle=(/*setter*/cs);
// write the first excel file
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsNotNull(wb2);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.IsNull(row.GetCell(4));
// add a new cell to the sheet
cell = row.CreateCell(4) as XSSFCell;
cell.SetCellValue("test2");
// write the second excel file
XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;
Assert.IsNotNull(wb3);
sheet = wb3.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.AreEqual("test2", row.GetCell(4).StringCellValue);
wb3.Close();
wb2.Close();
wb1.Close();
}
/// <summary>
/// .xlsx后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSXExcel(string excelPath, string imgPath)
{
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
{
//创建工作簿
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
//获取第一个工作表（下标从0起）
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
//获取指定图片的字节流
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.JPEG);
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第五列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 2, 10, 3);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(excelPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
xssfworkbook.Write(file);
//关闭工作簿
xssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void TestGetChartAxisBug57362()
{
//Load existing excel with some chart on it having primary and secondary axis.
IWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("57362.xlsx");
ISheet sh = workbook.GetSheetAt(0);
XSSFSheet xsh = (XSSFSheet)sh;
XSSFDrawing Drawing = xsh.CreateDrawingPatriarch() as XSSFDrawing;
XSSFChart chart = Drawing.GetCharts()[(0)];
List <IChartAxis> axisList = chart.GetAxis();
Assert.AreEqual(4, axisList.Count);
Assert.IsNotNull(axisList[(0)]);
Assert.IsNotNull(axisList[(1)]);
Assert.IsNotNull(axisList[(2)]);
Assert.IsNotNull(axisList[(3)]);
}
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);
}
/**
* Gets the first chart from the named sheet in the workbook.
*/
private XSSFChart GetChartFromWorkbook(IWorkbook wb, String sheetName)
{
ISheet sheet = wb.GetSheet(sheetName);
if (sheet is XSSFSheet)
{
XSSFSheet xsheet = (XSSFSheet)sheet;
XSSFDrawing drawing = xsheet.CreateDrawingPatriarch() as XSSFDrawing;
if (drawing != null)
{
List <XSSFChart> charts = drawing.GetCharts();
if (charts != null && charts.Count > 0)
{
return(charts[0]);
}
}
}
return(null);
}
public void TestRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
//the sheet has one relationship and it is XSSFDrawing
List <RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(6, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFPicture);
Assert.IsTrue(shapes[(1)] is XSSFPicture);
Assert.IsTrue(shapes[(2)] is XSSFPicture);
Assert.IsTrue(shapes[(3)] is XSSFPicture);
Assert.IsTrue(shapes[(4)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(5)] is XSSFPicture);
foreach (XSSFShape sh in shapes)
{
Assert.IsNotNull(sh.GetAnchor());
}
checkRewrite(wb);
wb.Close();
}
public void TestRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
//the sheet has one relationship and it is XSSFDrawing
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
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);
}
public void IncrementShapeId()
{
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);
}
public void TestXSSFSimpleShapeCausesNPE56514()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56514.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
shapes = Drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
/* OutputStream stream = new FileOutputStream(new File("C:\\temp\\56514.xlsx"));
* try {
* wb.Write(stream);
* } finally {
* stream.Close();
* }*/
}
public void Bug47668()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("47668.xlsx");
IList allPictures = workbook.GetAllPictures();
Assert.AreEqual(1, allPictures.Count);
PackagePartName imagePartName = PackagingUriHelper
.CreatePartName("/xl/media/image1.jpeg");
PackagePart imagePart = workbook.Package.GetPart(imagePartName);
Assert.IsNotNull(imagePart);
foreach (XSSFPictureData pictureData in allPictures)
{
PackagePart picturePart = pictureData.GetPackagePart();
Assert.AreSame(imagePart, picturePart);
}
XSSFSheet sheet0 = (XSSFSheet)workbook.GetSheetAt(0);
XSSFDrawing Drawing0 = (XSSFDrawing)sheet0.CreateDrawingPatriarch();
XSSFPictureData pictureData0 = (XSSFPictureData)Drawing0.GetRelations()[0];
byte[] data0 = pictureData0.Data;
CRC32 crc0 = new CRC32();
crc0.Update(data0);
XSSFSheet sheet1 = workbook.GetSheetAt(1) as XSSFSheet;
XSSFDrawing Drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPictureData pictureData1 = (XSSFPictureData)Drawing1.GetRelations()[0];
byte[] data1 = pictureData1.Data;
CRC32 crc1 = new CRC32();
crc1.Update(data1);
Assert.AreEqual(crc0.Value, crc1.Value);
workbook.Close();
}
