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