Tuesday, July 11, 2017


--- Graphics ---

2016

One of my more frustrating programming episodes occurred with one of OpenOffices updates. I tried to install the new version and it wouldn't install. The suggestion on the user forum was to uninstall the older version and then try to install the new version. The new install still didn't work so I had no working version of OpenOffice and a lot of work on DANSYS that I had not saved. Dumb of me - irritating of OpenOffice. That was when I decided to junk OpenOffice and switch to LibreOffice.

The biggest loss was a set of programs I had put a lot of work into that expanded the ability of OpenOffice Calc to produce statistical graphs. They constituted a graph building suite. Instead of creating a set of predesigned graphs (as with the charts currently produced by Calc), I could build graphs from elements such as axes, points, lines, and such. I decided to ditch the idea. I just didn't have the heart to redo all that work, and I had other programs that would produce all the statistical graphs I would ever need.

But then I started thinking (often a big mistake, but one that often leads to adventure) that it would be nice to have DANSYSX be able to generate some simple graphics. Points and lines and such are useful for a lot more than making statistical graphs.

One limitation of spreadsheet functions is that they can only output data into cells, so I couldn't use functions to generate geometrical objects on a spreadsheet. I would have to use subroutines, and thus commands, to do the job, and that meant creating a menu of graphical commands.

I also wanted to be able to translate the positions of the geometrical objects into a specified area - a frame. I had done that with the OpenOffice commands, so I should be able to do it again for LibreOffice.

This is a different kind of programming than creating a function to output a value or matrix of values. I had to actually place objects on a spreadsheet. That meant that I had to have the program determine where everything had to go and place it there. LibreOffice Basic can do that and I'll show you the frame routine. You can find the others in the Graphics folder of the DANSYSX macros.

One thing I remember doing over and over in the older programs was translating data points from one range (the range of data values) to another (the range of positions on a frame on a spreadsheet). That got old, so, this time I created a very simple function to do that. It looks like this:

Function Rescale(c,a,b,y,z)
'Rescales a value, c, between a and b to be proportionally
'between y and z)
Rescale=(c-a)*(z-y)/(b-a)+y

End Function

That was easy and, if you went through the article, DANSYSX: Complex cubic equations, you know what that all means.

The way I had the older macros work is that the user would create a specification form on a spreadsheet for a specific object, fill it out, process the specifications, and then display the object on the spreadsheet.  I've trimmed this four step process down in DANSYSX except for the frame. You still have to process the specifications for the frame. A frame has to be generated (whether it's displayed or not) so LibreOffice knows where to place the graphics object.

The Specification routine is tedious (there's a lot of information required to specify the frame) but straightforward. A frame is just a rectangle. Draw a rectangle and you have a frame that you can situate everything else on.

The only complication is that I need different specifications according to the kind of background to have on the frame, so I had to work up a dialog to select the kind of background before printing the specification form.

Here is the specification routine:

SUB FrameSpec()
'Prints out a frame specification form onto a spreadsheet
'beginning at the current cell.
DIM I AS INTEGER, J AS INTEGER
DIM RP, CP, DLG AS OBJECT, STL AS INTEGER, LBX AS OBJECT
DIM oDoc AS OBJECT, oSheet AS OBJECT, oSel AS OBJECT
DIM addr AS OBJECT, M AS INTEGER, N AS INTEGER
DIM oCell AS OBJECT, oView AS OBJECT



'Display dialog Frame

DialogLibraries.LoadLibrary("Standard")
DLG=CreateUnoDialog(DialogLibraries.Standard.Frame)
DLG.Execute()


LBX=DLG.getControl("ListBox1")
STL=LBX.SelectedItemPos

IF DLG.Execute()=0 THEN EXIT SUB


'Print form
 oDoc=ThisComponent
 oView=ThisComponent.getCurrentController()
 oSheet=oView.getActiveSheet()
 oSel=oDoc.getCurrentSelection
 addr=oSel.getRangeAddress()

 m=addr.StartRow
 n=addr.StartColumn

oCell=oSheet.getCellByPosition(n,m)
oCell.String="Frame"
oCell=oSheet.getCellByPosition(n,m+1)
oCell.String="Position 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+2)
oCell.String="X"
oCell=oSheet.getCellByPosition(n+1,m+3)
oCell.String="Y"
oCell=oSheet.getCellByPosition(n,m+4)
oCell.String="Size 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+5)
oCell.String="Width"
oCell=oSheet.getCellByPosition(n+1,m+6)
oCell.String="Height"
oCell=oSheet.getCellByPosition(n,m+7)
oCell.String="Margins: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+8)
oCell.String="Top"
oCell=oSheet.getCellByPosition(n+1,m+9)
oCell.String="Bottom"
oCell=oSheet.getCellByPosition(n+1,m+10)
oCell.String="Left"
oCell=oSheet.getCellByPosition(n+1,m+11)
oCell.String="Right"
oCell=oSheet.getCellByPosition(n,m+12)
oCell.String="Caption"
oCell=oSheet.getCellByPosition(n+1,m+13)
oCell.String="Top"
oCell=oSheet.getCellByPosition(n+1,m+14)
oCell.String="Bottom"
oCell=oSheet.getCellByPosition(n+1,m+15)
oCell.String="Left"
oCell=oSheet.getCellByPosition(n+1,m+16)
oCell.String="Right"
oCell=oSheet.getCellByPosition(n,m+17)
oCell.String="Transparency: percent"
oCell=oSheet.getCellByPosition(n,m+18)
oCell.String="Border"
oCell=oSheet.getCellByPosition(n+1,m+19)
oCell.String="Line Style: NONE, SOLID, DASH"
oCell=oSheet.getCellByPosition(n+1,m+20)
oCell.String="Line Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+21)
oCell.String="Line Transparency :percent"
oCell=oSheet.getCellByPosition(n+1,m+22)
oCell.String="Line Width: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+23)
oCell.String="Line Joint: NONE, MIDDLE, BEVEL, MITER, ROUND"
SELECT CASE STL
CASE=0
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Background: NONE"
CASE=1
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Background: SOLID"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Red"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Green"
oCell=oSheet.getCellByPosition(n+1,m+27)
oCell.String="Blue"
CASE=2
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Background: HATCH"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Style: SINGLE, DOUBLE, TRIPLE"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+27)
oCell.String="Distance: 1/100 mm"
oCell=oSheet.getCellByPosition(n+1,m+28)
oCell.String="Angle: 1/10 degree"
CASE=3
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Bitmap - Named"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Name"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Style: REPEAT, STRETCH, NO_REPEAT"
CASE=4
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Bitmap - URL"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="URL"
CASE=5
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Gradient - Named"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Name"
CASE=6
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Gradient - Custom"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Style: LINEAR, AXIAL, RADIAL, ELLIPTICAL, SQUARE, RECT"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Start Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+27)
oCell.String="End Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+28)
oCell.String="Angle: 1/10 degree"
oCell=oSheet.getCellByPosition(n+1,m+29)
oCell.String="X Offset: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+30)
oCell.String="Y Offset: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+31)
oCell.String="Start Intensity: percent"
oCell=oSheet.getCellByPosition(n+1,m+32)
oCell.String="End Intensity: percent"
oCell=oSheet.getCellByPosition(n+1,m+33)
oCell.String="Step Count: number of color graduations"
END SELECT

It's long but fairly simple. It does illustrate how you can get things from a program onto a spreadsheet, though. Let's look at the sections.

Variables can be set to hold things like documents, sheets, cells, ranges, shapes, and such but they have to be declared as objects. This program uses several object variables.

DialogLibraries.LoadLibrary("Standard")
DLG=CreateUnoDialog(DialogLibraries.Standard.Frame)
DLG.Execute()


LBX=DLG.getControl("ListBox1")
STL=LBX.SelectedItemPos

IF DLG.Execute()=0 THEN EXIT SUB

This section displays the dialog that has the list box of styles for backgrounds. I constructed the dialog in the dialog editor (Select Macro>Organizer>Dialogs tab) with a listbox (I specified the items in the listbox in the properties settings for the listbox.), a Okay button, and a Cancel button. Here's what the dialog editor looked like:



"DialogLibraries.LoadLibrary("Standard")" loads the library that contains the Frame dialog.
"DLG=CreateUnoDialog(DialogLibraries.Standard.Frame)" sets the DLG object variable to contain the Frame dialog.
"DLG.Execute()" displays the dialog. As long as the dialog is displayed and working, nothing else runs in Calc.
"LBX=DLG.getControl("ListBox1")" loads the listbox1 control into object variable LBX and "STL=LBX.SelectedItemPos" gets the selected text string from the listbox1.
"IF DLG.Execute()=0 THEN EXIT SUB" specifies that, if the Cancel button is clicked (which generates a 0 from the DLG object), that the subroutine is closed without doing anything.

 oDoc=ThisComponent
 oView=ThisComponent.getCurrentController()
 oSheet=oView.getActiveSheet()
 oSel=oDoc.getCurrentSelection
 addr=oSel.getRangeAddress()

 m=addr.StartRow
 n=addr.StartColumn

The oDoc variable is set to contain the document that is currently open (DANSYSX)
The oView variable is set to contain the current documents view.
The oSheet variable is set to contain the currently active sheet in the view of the document.
The oSel variable is set to contain the currently active range (in this case, just one cell, the top left cell intended to start the Frame form.)
The addr variable contains the address structure of oSel. It contains the starting and ending cell row and columns as a data structure.

Data structures are addressed by the name of the data structure (in this case addr) followed by a period and the name of the specific value to be accessed. For instance, addr.StartRow returns the value of the StartRow value in addr. The integer variable m is set to the StartRow value and the integer variable n is set to the StartColumn value.

The rest of the subroutine is just a long list of statement of the form:

oCell=oSheet.getCellByPosition(n,m)
oCell.String="Frame"

Here, the oCell variable is set to the cell at column n and row m on the currently active sheet. Notice that cell positions are opposite to matrix positions. Matrix positions are addressed by (row,column); cell positions are addresses by (column, row) order. This is in keeping with the way cells are addressed on spreadsheets. A cell at position C5 is at column C and row 5. It's also not obvious here, but the first row on a spreadsheet is row 0, and the first column is column 0.

I use a Select Case structure to decide the labels to output onto the spreadsheet according to which background style is chosen. The Select Case structure works like this:

SELECT CASE (statement)
CASE (value)
CASE (value)
...
CASE (value)
END SELECT

The statement in the first line specifies what the following case values refer to. In this example, the case is the value of STL, the numerical position of the selected style in the dropdown list of the dialog. The first CASE statement is CASE 0, which is the value of the first item, NONE, in the list. If STL=0, the block of statements following CASE 0 is executed and, then, the other case statements are ignored. END SELECT marks the end of the Select Case structure. Notice that the Case values can be numerical or string values, inequalities like >5 or <=3, or statements like "between 2 and 5".

When this routine is run from the Graphics menu>FrameSpec, a form is printed onto the active spreadsheet beginning at the selected cell. This form can be filled in to specify a frame for other graphic objects.

Saving all this information so that it doesn't go away between sessions requires that I construct a hidden spreadsheet to save it on. You can see that by unhiding the Graphics sheet in DANSYSX. Right click on any sheet tab and select the Show Sheet... command. It will open a list of hidden sheet. Double click Graphics. Be careful and don't change anything. You'll the information generated by the FrameSpec command in the top part of the sheet. Below that, though, are several calculated value. These are values necessary to scale values to the frame, taking margin specifications into account.

The FrameGen command transfers information from the frame table to the FrameSpec sheet. It uses much of the same Basic and Uno commands but there are two sheets to deal with, the sheet the table is on and the FrameSpec sheet. You will recognize most of the code if you look at it.

This pattern is repeated a lot.

oCell=oSheet.getCellByPosition(n1+1,m1+0)
aCell=aSheet.getCellByPosition(1,0)
aCell.Value=oCell.Value

That, of course, transfers the information from the first column to the right and top row of the frame table to the first column and top row of the Frame Spec sheet. These lines are quite simple but tedious to write. Luckily, there is a lot of cut, paste, and modification going on.

There is an interesting little subroutine tagged onto the end that parses out a string of color specifications. Here, color is specified by three numbers from 0 to 225 specifying how much red, green, or blue is used to create the color desired. The subroutine is called using the statement:

GOSUB FG10

The cell containing the string is first specified as aCell. The subroutine has to come after the last line of the main subroutine:

EXIT SUB

and it must begin with the calling label. Here is the subroutine:

FG10: 'RGB subroutine. Sets R, G, and B.
RGBSTR=aCell.String
Clr=""
S=1
FOR I=1 TO LEN(RGBStr)
Clr=Clr & MID(RGBStr,I,1)
IF MID(RGBStr,I,1)="," OR I=LEN(RGBStr) THEN
IF S=1 THEN
R=Val(Clr)
ELSEIF S=2 THEN
G=Val(Clr)
ELSE
B=Val(Clr)
END IF
S=S+1
Clr=""
END IF
NEXT I

RETURN


 END SUB

The string in aCell is transferred into the RGBStr variable and the following loop structure takes the string apart, converting the parts between the commas into numerical values stored in the R, G, and B variables. The last line of the subroutine is RETURN, which switches processing back to the line immediately following the last GOSUB statement. Finally, the whole macro is closed out using the END SUB statement.

More than one subroutine with different line labels can be placed at the end of a macro, but they all have to come before the END SUB or END FUNCTION statement.

The FrameShow command acually places the graphics frame on the spreadsheet. It is just a drawn rectangle and the LibreOffice Basic Programmer's Guide very adequately explains how to code for various drawn shapes and I will direct you to that since there are a lot of possibilities, but I will point out a few things that it doesn't make terribly clear.

First, every spreadsheet has it's own drawing page. In Draw and Impress, this is pretty explicit, but not for Calc. The way the drawing page for the active sheet is specified is:

oSheet = aDoc.getcurrentcontroller.activesheet
DPAGE=oSheet.DrawPage

The first line defines the active sheet and loads it into an object variable (oSheet). The second loads the drawing page of oSheet into another object variable called DPAGE. Later, the frame, which is a rectangle stored in the object variable oRect, is placed on the drawing page by the following line:

DPAGE.add(oRect)

The frame is built up by sequentially adding the frame and all the specified captions (which are rectangles with text) with similar commands.

Other commands to create points, lines, ellipses, and boxes will be very similar. They will all be in the Graphics folder of the DANSYSX macro library.


No comments: