[Solved] Position Images in Calc Spreadsheet

Creating a macro - Writing a Script - Using the API

[Solved] Position Images in Calc Spreadsheet

Postby someware » Wed Feb 19, 2014 6:39 pm

Hello everyone,

my problem lies with inserting greater aumounts(around 1000 pics) of images into a Calc spreadsheet. The inserting alone i figured out already, but the positioning just doesnt go well.
The code to inserting is rather wide spread in methods and not easy to post, but it works, i have seen it. I recap what i have learned about inserting:

Images cannot be inserted directly into the cells via TextContext like this

Code: Select all   Expand viewCollapse view
var text = (XText)cell;
            var xTextCursor = text.createTextCursor();
            var xTextContent = (XTextContent)img;

            text.insertTextContent(xTextCursor, xTextContent, true);


so im inserting them into a DrawPage (Til now I only inserted one, do I need a new DrawPage for every Image or can i insert all in one?)

Since the image has no relation to any cell now i need to set the exact pixel Position of the image.
To get the Position of the upper left corner of the cell which needs to visualy hold the image, i wrote following method:

Code: Select all   Expand viewCollapse view

protected Point GetCellPosition(XCell cell)
        {
            var adress = ((XCellAddressable)cell).getCellAddress();
            var xRange = (XColumnRowRange)DocumentProperties.CurrentSheet.getCellRangeByPosition(0, 0, adress.Column, adress.Row);
            var columns = xRange.getColumns();
            var rows = xRange.getRows();
           
            var pos = new Point(0, 0);

            for (var i = 0; i < adress.Column; i++)
            {
                var column = columns.getByIndex(i);
                var xprop = (XPropertySet)column.Value;
                var bot = (BorderLine)xprop.getPropertyValue("BottomBorder").Value;
                pos.X += bot.LineDistance;
            }
            for (var i = 0; i < adress.Row; i++)
            {
                var row = rows.getByIndex(i);
                var xprop = (XPropertySet)row.Value;
                var left = (BorderLine)xprop.getPropertyValue("LeftBorder").Value;
                pos.Y += left.LineDistance;
            }
            return pos;
        }



The code probably will not give the exact position yet, but i can figure that out when my problem is solved, which is the BorderLine Property.
Obviously the Properties "LeftBorder" and "BottomBorder" exist in the C# Uno Api, but no values are returned.
Before I call the method with the distinct cell I fill the Spreadsheet an show it, where i can see that everything worked an the distinct cell even holds content.


My question is now, can anybody tell me if theres a better and faster way to postion the image or relate an image to a cell, or does anyone have the slightes idea why the BorderLine Values are allways 0.

Thx very much in advance, and best Regards.
Last edited by someware on Fri Feb 21, 2014 6:18 pm, edited 1 time in total.
LibreOffice, Version: 4.3.3.2, Win 8.1 proffessional 64 bit
someware
 
Posts: 19
Joined: Wed Feb 05, 2014 6:29 pm

Re: Position Images in Calc Spreadsheet

Postby someware » Thu Feb 20, 2014 12:14 pm

I usually dont magange to supply all the valuable Information, so if I missed something or more Information is needed, dont hesitate to demand more.

Thx in advance.
LibreOffice, Version: 4.3.3.2, Win 8.1 proffessional 64 bit
someware
 
Posts: 19
Joined: Wed Feb 05, 2014 6:29 pm

Re: Position Images in Calc Spreadsheet

Postby Charlie Young » Fri Feb 21, 2014 12:30 pm

I think you must be on the wrong track looking at the line properties. They won't necessarily return anything if the cell isn't formatted with borders. In particular, the LineDistance is just the spacing between the inner and outer of a double line.

Each cell does have a Position property, with X and Y coordinates of the upper left corner, which is probably what you want.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Position Images in Calc Spreadsheet

Postby someware » Fri Feb 21, 2014 6:18 pm

So simple and still miles away when you dont have an API. Am I also to stupid to find the simple answer here or is there just no detailed information about the C# Api.

Anyway, thank you very very much , this helped a lot.
LibreOffice, Version: 4.3.3.2, Win 8.1 proffessional 64 bit
someware
 
Posts: 19
Joined: Wed Feb 05, 2014 6:29 pm

Re: [Solved]Position Images in Calc Spreadsheet

Postby MTP » Fri Feb 21, 2014 8:57 pm

OpenOffice API documentation
There's a lot of good information in Andrew Pitonyak's book - he uses StarBasic, but the object names are going to be the same
And to see the properties for yourself the xray or mri tools are popular
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: [Solved] Position Images in Calc Spreadsheet

Postby someware » Mon Feb 24, 2014 12:11 pm

Thx for the information, i will work with the Extensions from now on.
LibreOffice, Version: 4.3.3.2, Win 8.1 proffessional 64 bit
someware
 
Posts: 19
Joined: Wed Feb 05, 2014 6:29 pm

Re:Position Images in Calc Spreadsheet

Postby someware » Mon Feb 24, 2014 1:46 pm

I still have a little problem, the images I inserted(about a 1000) are wandering. When i scroll down they are slowly drifting upwards, even though i get the position anew for every cell.

Image_Error_UNO.gif

The corresponding code is like this:

Code: Select all   Expand viewCollapse view
public void InsertImage(int posx, int posy, string url)
        {
            var name = Path.GetFileName(url);
            var image = new ImgOperations(name);
            image.SetSize(DocumentProperties.ImageSize, DocumentProperties.ImageSize);
            var cell = GetCell(posx, posy);
            image.SetPosition(cell);
            image.SetAnchorType( TextContentAnchorType.AS_CHARACTER);
            image.SetProperties( url);     
            image.InsertImage(cell);
            //image.FitImages(cell);
        }

In my Image Class:

Code: Select all   Expand viewCollapse view
        public void SetPosition(XCell cell)
        {
            var xprop = (XPropertySet)cell;
            var point = (Point)xprop.getPropertyValue("Position").Value;
            _image.setPosition(point);
        }

        public void SetProperties(string url)
        {           
            var psGraphic = (XPropertySet)_image;

            psGraphic.setPropertyValue("Name", new Any(_name));
            psGraphic.setPropertyValue("Title", new Any(_name));

            var internalURL = "file:///" + url.Replace('\\', '/');     

            if (_bitmapContainer.hasByName(_name))
                _bitmapContainer.replaceByName(_name, new Any(internalURL));
            else
                _bitmapContainer.insertByName(_name, new Any(internalURL));

            var newUrl = ((XNameAccess)_bitmapContainer).getByName(_name);

            psGraphic.setPropertyValue("GraphicURL", new Any((string)newUrl.Value));           
        }

        public void InsertImage(XCell cell)
        {
            var drawPageSupplier = (XDrawPagesSupplier)DocumentProperties.Document;
            var drawPages = drawPageSupplier.getDrawPages();
            var page = (XDrawPage)drawPages.getByIndex(0).Value;
            page.add(_image);
            _bitmapContainer.removeByName(_name);
        }

Again, the code is a little dispersed in classes so i copied it together, if some information is missing i will be happy to provide it.

Thx in advance and best regards.
LibreOffice, Version: 4.3.3.2, Win 8.1 proffessional 64 bit
someware
 
Posts: 19
Joined: Wed Feb 05, 2014 6:29 pm

Re: [Solved] Position Images in Calc Spreadsheet

Postby nkkonnar » Tue Feb 16, 2016 12:10 pm

Can you please share your full code how you inserted an image.
OpenOffice 4.1.2 on windows 8.1
nkkonnar
 
Posts: 1
Joined: Tue Feb 16, 2016 12:07 pm

Re: [Solved] Position Images in Calc Spreadsheet

Postby Albireo » Wed Jun 15, 2016 1:48 pm

Have something more occurred with this challenge / question?
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: Google [Bot] and 7 guests