The daily grind

Yes, Suse “Hackweek” ( where you can scratch an itch on work time ) has just been and gone. This time I wanted to work on and tackle an aspect of usability ( to do with cell styles in Calc ) that really pisses me off, e.g. when you have some cell style and you want to see what that style will look like you more or less have to apply the style. Ok, if you are really sadistic you could open the Styles dialog ( F11 ), select the style and edit it ( and they you can access format dialog which describes ( but still doesn’t really show ) all the individual elements of the style ). Excel ( in later versions at least ) does a wonderful job with this by allowing you to preview the style in-place, in other words when selecting a style whatever cells are currently selected get that style temporarily applied. Excel also does the same thing for Font selection.
First I actually decided to try and implement a simple Font preview, it’s not quite what I wanted but it seemed maybe a simpler version of what I wanted to achieve and would provide the basic knowledge to be able to tackle the styles. After far more effort that I thought I managed to get a quite passable implementation of the Font preview working despite some unexpected challenges ( e.g. the listbox implementation doesn’t easily allow you to know about the currently highlighted element in the listbox which required some poking around in vcl ) Anyway in the end and after a couple of days a much smaller patch ( that the effort would have suggested ) appeared. It’s even in master right now with this commit
Next I started tackling the Styles preview, things initially seemed to go well, the cell previewed at least all of the font related information from the selected element in the Style dialog and after realising I was missing things like Rotation etc. I managed to get that stuff working too. Then of course I noticed things like cell background or border attributes weren’t being displayed, it took me quite some time to figure out in the drawing layer where this was happening and ( I wont say understand ) but find where to modify the code to do this. At this point I ran out of time

A quick screencast shows what works currently. I really wish I had more time to tackle the style dialog itself ( or perhaps create a slimmed down version ) that would additionally in the control itself show some sort of preview of the style ( at least in terms of the font, background etc ) that could be shown on the toolbar. Also the existing dialog doesn’t track the mouse moves and highlight the element you hover over ( it’s not immediately obvious but the Styles dialog uses a Tree like widget ( although most of the time it displays like a listbox ) ) Another annoyance ( quite obvious in the screencast ) is the fact that the selection area is highlighted when previewing which has the irritating effect of changing the background colour.

The full set of patches ( based of libreoffice-4-0 ) for the hackweek work can be found here A squashed version is available here
Again note the Font preview part has been extracted from the above patches and already applied to master. There are still some issues with the CellStyle Preview that I would like to iron out before applying it.

Last year for some reason that I can’t recall I only managed to take 1 day of hackweek, back then I started oletool.py. oletool.py is intended to be like a cheap & nasty (zip/unzip)-like command-line tool for OLE compound documents. I started this for 2 reasons
1) I often play with Libreoffice filters, many times I want to either extract some stream or other from a document to examine it or maybe quickly modify a stream re-inject it into the document to test some code or theory
2) Libreoffice has support for Python, I know nothing about Python and I wanted to learn a new scripting language ( I know I could have used libgsf for this tool - maybe something for some spare cycles )

So, has to be said I wasn’t starting completely from scratch, Kohei already created mso-dumper for dumping the content of Excel documents in python. Last year I was happy with my one day’s work, I managed using Kohei’s mso-dumper code to quickly cobble together a tool that allowed the contents of the document to be displayed ( ala gsf list type format ) and also extract one or more streams

Have to admit between the last Hackweek and this one I had completely forgotten whatever Python I had had learned. But, this time I did get around to reading the available documentation about the OLE compound document format and started trying to add support to be able to write OLE documents from oletool.py. I was aiming to be able to be able add support deletion ( stream or storage ), add ( stream or storage ) and update ( existing stream ) Things went a little ( ermmm much ) slower than I would have liked.
I took the approach of trying to surgically write various pieces of the already read document and new pieces to the output document after I discovered that the byte array used in mso-dumper was immutable ( the array of bytes is really a string ), attempts to convert this to use the mutable bytearray type started giving many many errors in the exiting code and I admit I got scared off burning time trying to convert that unfamiliar stuff. This worked well enough especially for delete actions and modifying the header and Directory entries but I ran into a brick wall with more complicated scenarios of adding/modifying streams especially when various internal tables needed to be expanded etc. So eventually I ended up converting Ole.py to use bytearray ( this actually turned out not so hard at all ), also had to rewrite my code to use the existing data model and try and be able to write the document top to bottom from the model alone ( yeah I know that makes more sense but I tried to cheat to get stuff working faster which turned to be completely counter productive ) Another problem was plagued with strange errors that looked like corruption, my progress was really stifled by that. I really missed not having access to a debugger, scripting languages are great but you can easily get lost without some nice debugging aids besides the old reliable ‘print’. In the end and after nearly 2 days of debugging I found a strange quirk with bytearray, something like

myoutputByteArray[ 4 : 24 ] = srcStringOrArray[ 0 : 20 ]

can give quite unexpected results if the srcStringOrArray’s size is unexpectedly short, in these cases it seems a slice corresponding to the missing bytes in the source array is removed. The position of where the slices is removed from depends somehow on the start/end positions so it isn’t just that some bytes are removed from the end. This simple ( and still to me strange ) behaviour caused complete havoc and even appeared to screw up some things in memory. I suppose my lack of Python knowledge is showing here and to those in the know this is all obvious.
After getting various bits working I managed in my final commits to break things ( now mostly fixed ) So, currently things are nearly working in terms of the most basic functionality, however the code is ugly in the extreme ( generally I like to get bits of functionality working before massaging it into some sort of shape ) there is plenty of cut’n'paste, little object-orientation and lots of debug print(s), additionally no testing or consideration of windows file paths etc. In essence still alot to do ( roll on the next hackweek )

the fruits of my labour(currently failure) can be see one this branch

note: itchy trigger finger deleted this post from June 15, 2010. So… here we go again

It was hackweek here again in Novell, I spent an enjoyable ( but also sometimes frustrating ) couple of days trying to extend support for Mono in Openoffice.org. Recently on IRC there was some interest in using C# to write extensions for Openoffice.org where it became clear that only being able to ‘drive’ Openoffice.org from C# is severely limiting. To provide decent custom functionality you need to be able to get called by Openoffice.org, integrate with the Menus and Toolbars etc. Clearly to write an extension you need to be able to be ‘plugged’ in.. There are many many C# developers ( and potential extension developers ) and
we are just ignoring them. I have to admit I always wanted to play with C#/DotNet/Mono and I even proposed a GSOC task to try and faciliate this
Unfortunately the project didn’t make the cut but fortunately there is Hackweek yay! So, I spent the last couple of days playing around with Mono and C#, first I wrote a new loader for Mono, this allowed me to use recomp to register some services, next I modified unopkg to accept a Mono component bundled in an extension.

The source code for the extension is available from here
Then I ported the CalcAddins example from the sdk to Mono, the result can be seen below

The source code for the Calc addin can be found here.

For more information see the details and patch

I didn’t have time to write some managed c++ code to load from DotNet, it should only require a small amount of code since the loader is only
a proxy for the managed code Loader implementation ( that was written for this mono component support and can be reused )

In a previous blog I mentioned I was trying to provide some more advanced containment control concepts to Openoffice.org. As such I have created a cws for it. My main focus is providing these controls to enhance the VBA Userform import experience, hence support in the general Openoffice.org usage case is limited to programmatic access for the moment ( probably a good thing whilst trying to mature this feature )
I do think though that the implementation ( with some minor cleaning up ) is good enough to commit, that way ,the Userform import will immediately benefit from this work and additionally developers can play with the new ‘MultiPage, Page & Frame’ controls. If you can’t wait for the cws to be integrated then you can of course try this feature in the ooo-build master branch where it is already available. The screenshot shows a MSO userform with some multiple levels of nested container controls, then the same useform imported in ‘vanilla’ openoffice and finally how it looks now in the cws. You should notice some other nice improvements that also are included in this cws e.g. the ’spinbutton’ is now imported ( unfortunately this cws does not make use of the spinbutton generally available for normal Openoffice.org Dialogs ) However this cws does enable controls in Openoffice Dialogs to now access embedded images ( note the image control from the MSO Userforms has an associated image, the filter has been modified to create embedded images on import and the Dialog controls now can handle embedded images ) Also some good news regarding the toolbar enhancements mentioned in the last blog these are now integrated in the DEV300 codebase since m70 ( many thanks to Carsten for helping make that happen )

Lately I have been on a bit of an upstream frenzy, cws npower12 has been created to capture a slew of api changes ( including lots of module structure changes ) that are already in ooo-build Additionally I eventually got some time to look at some of the problems I mentioned in a previous post regarding importing toolbars from MSO. I even made some progress closing some of those functionality gaps between MSO and Openoffice.org toolbars, specifically I have worked on extending the existing api to be able to:

1. specify a menu on a custom toolbar
2. allow the icon/text/icon+text settings to be applied per toolbar/menubar item ( in other words if specified these settings will override the toolbar/menubar setting )
3. support persisting those settings in the configuration
4. allow a tooltip to be specified for the toolbar item ( prior to these change the tooltip was generated from the ( label/command )

As such I have created this issue. Here are a couple of screen shots from the test document associated with the issue

Finally I had an interesting discussion recently about data-aware form controls recently in this thread I hope to open new cws(s) for the multipage and data-aware controls in dialogs soon. I will be giving a talk at OOoCon2009, don’t be suprised if there is some mention ( an maybe even demo ) of some of the above

Last week it was hackweek here in Novell again, I decided revisit the toolbar and menu import filter for word I created previously and and do the same for Excel too. Wow, Excel seems a little bizarre, any custom toolbars you create are set for the application ( Word behaves differently and gives you the option to create menus or toolbars for the application or the document you are editing ). You can attach your custom toolbars to a document though. But interestingly if you open a document in Excel that contains custom toolbars, those toolbars are applied to the application. I don’t think trying to replicate this strange behaviour is beneficial so just like the Word filter the imported toolbar settings are just applied to the imported document only, no settings are applied globally. What about menus? they seem a little odd too, you can of course add new menus to the ‘builtin’ Menus ( like Standard etc. ) but, I couldn’t figure out a way to attach or copy those modifications to the document, I guess you can’t do that. You can of course add new menus to a custom toolbar but adding menus to custom toolbars is not possible ( afaics ) in Openoffice.org. Another interesting discovery is that all application modifications regarding toolbars and menus are stored in a file with an ‘xlb’ extenion e.g. you will find the file in a location similar to Application Data/Microsoft/Excel/Excel10.xlb Unfortunately there is no information available about this format.
Anyway, I did manage to get basic import of toolbars working, additionally I tweaked Openoffice.org’s toolbar code to allow a tooltip to be specified ( unfortunately Openoffice.org doesn’t allow you to specify your own tooltip with the api, it creates a tooltip based on either the Label or Command ), this time I got around to processing the seperators in the filter, additionally I tweaked ( and this is an evil hack really ) the heuristics for detecting deleted custom toolbar data. Both Word and Excel have a nasty habit of carrying around redundant records in the binary format relating to changed or deleted custom toolbars. Even after poring over the spec a number of times I still don’t see the proper way to strip out this info, changes I made to the common code used by both Excel & Word filters had various sideaffects ( and some I only noticed and fixed while writing this ). My gut feeling is I am missing something obvious to do with popup menu handling, since we don’t really import menus ( see below ) but simply do a little evil import hack I must be missing some vital detail. However lets look at an example you’ll notice that of course we don’t actually import a menu on the toolbar, this is a pity and exposes a gap in Openoffice.org functionality. The best I can do at the moment is import the menu ( which in reality is just another toolbar ) as a separate toolbar. The basic toolbar, commands and associated images import well, builtin commands are not yet supported, unfortunately it would take quite some time to generate the appropriate translation between the Excel command-ids and the corrosponding ‘uno.xxxx’ commands. While I didn’t have time to do that I did spend some time debuging through the relevant code so I think if I can get some time I can close some of the obvious functionality gaps e.g.
a) can’t specifiy display of icon, text or text & icon at the toolbar control level
b) as above for Menu items
c) allow menus ( fully nested ) to be added to custom toolbar
d) additional controls for toolbar ( from the api, it seems this is already possibly to a certain extent using the configurations e.g. for Addons )

getting a,b,c & d to work for the filter would be a really nice first step, allowing the configuration to persist that new functionality would be a second one. Patch to extend the toolbar filter is already in ooo-build but the Excel part is currently disabled, you can view the patch here

Additionally I did implement a very seat of the pants hacky import for a HtmlSelect activex control. It probably is an idea to include the basic set of html controls to the standard Excel import filter too. I really would like to replace the home grown string parsing of the html stream associated with the control but I need to learn how to use whatever html parser openoffice has first ( help with that would be gratefully appreciated, especially since this must be done already, the data in the HtmlSelect activex control is nearly identical to that of a normal html listbox ) anyway patch for that is here

It’s been a long time since I blogged, I wanted just wanted to share some info about what I am up to at the moment. My colleague Fong blogged a while back about our tentative steps to support vba for word. Of course there are always side issues, one thing that was clear from a set of sample documents we were working on was the lack of support for importing toolbars and menus. So as part of the vba for word work I have written some filter code to import custom toolbars ( and the associated icons too ).

The example above shows the original word document with a custom toolbar at the top and underneath is writer’s import of the same toolbar using the new filter code. However implementing the filter is hampered by some missing functionality with Openoffice’s customization support. There are three toolbar controls added, the first is set to display image+text, the second image only and the third text only. As you can see in the imported document only images are displayed, it only seems possible to set the display setting for the whole toolbar. It would be much nicer if the each toolbar control could itself override the custom toolbar setting. You might also have noticed the size of the imported icons are a little smaller compared to the rest of the icons, the import should take care of scaling the image based on the configured default icon size, but it doesn’t do that yet.

The example above demonstrates my complete disregard for separators, currently I ignore ( but I do read ) the grouping information, it should be easy to implement.

I also added some basic support for importing custom menus to the main menubar ( although no nesting support yet ). Unfortunately imo Microsoft does a better job than us here, while Openoffice.org api differentiates between a MenuBar and a Custom toolbar, Microsoft just has toolbars, toolbars are custom or builtin, they just contain items, those items can be a button, a dropdown ( menu ) , a label etc. It would be great to pimp up some of this on the Openoffice.org side, would help with the filter and would benefit extension developers too.
As you can see, because of how Openoffice maps the images to commands you get an unexpected icon in the menu ( in the Word example the menu item does not have an icon associated with the menu macro command )
This work will be available in master in oo-build in the coming days for you to try out. If you would like to help hack on it please give me a shout.

Userform support is probably the missing feature that is most asked for in terms of VBA support. However implementing it is not easy, many missing pieces are required, not least
i) various import tweaks
ii) support in the basic language for UserForm modules, UserForm modules are a type of Document modules. Document modules are ’special’ module types in Excel & Word. In Excel for instance these modules are tied directly to the Workbook object and to each Sheet object. The module for the workbook is called ThisWorkbook, and each Sheet module has the same name as the sheet . These modules are special because:

* this is where the event handlers for the object must be placed, including user interface controls located on the worksheet
* Any ordinary functions or subroutines that are placed in a workbook or sheet module can not be called from other modules unless you fully qualify the function e.g. Sheet1.Foo
* methods or functions of the associated object can be called from within the module as if they were implemented by the module itself. Or, they can be called externally via a fully qualified reference e.g. Sheet1.Protect

The UserForm module allows access to the controls in the Userform by name, e.g.
UserForm1.Label1.Caption = "myCaption"
additionally it is where UserForm specific event handlers are placed ( UserForm_Initialise, UserForm_Activate etc.. )

At the recent GoOOCon 2008 I demoed some prelimenary work showing support for MSO Userforms

I uploaded a prototype build ( windows ) and some example Excel demo documents to show the progress. ( hopefully a Linux build will follow soon ) and a set of demo documents

Demo Document descriptions
==========================
1) PartLocDB.xls, this document is from here, the version presented here has just a single line[1] change to make it work with openoffice.

This Userform demonstrates a basic userform that interacts with spreadsheet.

[1] the change is actually not to the line content but rather combining a set of instructions written over 2 lines into 1 ( e.g. removal of the ‘_’ line concatenation operator )

2) UserformExample.xls from here

Again this document demonstrates fairly simple handling of simple events ( like click events ) Additionally the state of various controls is examined programatically and the results used to populate data in the spreadsheet

note: This userform hightlights some issues with the import
a) the missing image for the imagecontrol, openoffice cannot embed images for the image control, only a link can be specified ( see issue 38215 )
b) missing spinbuttons ( see issue 38900 )

of course there are other problems not hightlighed here like the fact in OpenOffice.org basic controls aren’t data-aware, no layout, Openoffice.org Dialogs only support a single level of containment, missing controls etc. etc. But… we can still do useful things and provide a framework to build upon.

3) UserformMouseandTooltip.xls

This document demonstrates some more advanced features like
a) use of Userform_Initialise event ( used to populate the combobox )
b) import of tooltips
c) support for MouseMove events which is used tp update control specific context info in the lable at the bottom of the userform
d) use of Userform_Terminate event which raises a message box whenever the Userform is unloaded from memory

4) ExcelExamples-demo.xls

The sheet named Example 7 contains another simple userform example, when you push the “Display State of UserForm” button the state of all of the control is written into the read-only textboxes. Note this document also shows some support for workbook and worksheet events.

5) progress-1.xls is an implementation of http://www.j-walk.com/ss/excel/tips/tip34.htm, see details of this very nice progress bar implementation. ( this also shows why the supporting the UserForm_Activate event is important )

6) progress-2.xls is also based on http://www.j-walk.com/ss/excel/tips/tip34.htm but uses OLE progress bar controls ( and there are 2 nested progress bars running simulataneously )

Note: progress-1 & progress-2 do not currently work well with Windows, seems to be due to some strange (VCL?) problem where we seem to actually get the activate event before the dialog is actually shown So, with this behaviour, while processing and updating the controls ( e.g. the progressbar ) we see nothing as the dialog isn’t yet displayed. Furthermore since the dialog is immedialely ‘Unloaded’ after processing you don’t even get to see the dialog.

Some test documents showing some api support for OLE controls on a spreadsheet.

aControlDemo.xls - simple test document. Use the combo box to select a control, use the spin buttons to adjust position and size. Notice the button below the combox whose caption is changed to indicate whether pressing the button will show or hide the control

aListBoxDemo.xls - another simple test document demostrating some programatic interaction, use the button to select whether the list box is single or multi-select, use the “>>>>” button to transfer the content of the list box.

Unfortunately Hackweek is over, however, I hope to get some time to continue on the macro export story in the near future. Anyway I promised I said I would publish a link to the macro export patches, please note they are still a bit ropey ( distinct lack of checking for error returns etc. ) There are some other parts of the code that I am not thrilled about either. for instance the determining of the Class Module type from basic, really we need to move the that information ( e.g. the ‘Option’ in the source that indicates the module type ) to be pushed into attributes in the xml ( currently there is a hack in the patches to pre-process the source to detect the ‘Option’ for ClassModule support ) In a similar vein there is a nasty hack to remove both the ‘Option ClassModule’ and/or ‘Option VBASupport …’ should they be detected on export. Additionally on export the ‘ThisWorkbook’, ‘Sheet..’ and ‘Userform’ modules types are currently detected using a heuristic approach. Internally in basic ( in compatibility mode ) these type of objects should be represented distinctly ( indeed from an interoperability point of view this will ultimately be necessary to support various VBA feature )

Anyway if you wish to experiment or have a look, see the patches for the
VBA project import/exporter and necessary misc integration bits

I also mentioned I would try to get export from a calc document working, e.g. open up a new or existing calc document, export the document to Excel and also export any macros. Well I tried but, the experiment was less than successful, e.g. you end up with a project like

( note the bogus Sheet[1..3] & ThisWorkbook1 objects ). This is quite a strange result as the project export did work ( the modules exported do exist and are shown in the VBA project editor), but clearly some associations between the exported objects in the project and the document were skewed. This led me to think about what would happen if you added a new sheet to an imported excel document and then resave that.

as you can see a similar result, this time though only the newly added Sheet object seems to be a problem. It seems that there is some missing information somewhere to identify the those objects as being part of the document so Excel autocreates them ( but Excel is still happy to import the objects we export also ) On further investigation I was able to indeed confirm that this is not in fact the fault of the macro export but missing information in the ‘Workbook’ stream itself. For instance if you replace the just the Workbook stream in the calcToExcel.xls with a Workbook stream from from an Excel document ( with the correct number of sheets with the same sheet names ) then the project editor will show the correct objects. Clearly we import/persist/export the correct information when modifying an Excel document but, it seems when exporting a Calc document we don’t export that information. When I get a chance I think a long look output of BIFF export debug output is necessary.

As some of you know this week has been HackWeek here in Novell. So, of course I chose to work on something macro/vba related. For a long time I’ve wanted to play with exporting Macros. Most of the hard lifting was already been done by Michael many moons ago. However, still a bit of work is required to actually integrate that work into openoffice, mostly getting rid of hardcoded bits, some tweaking for ‘Object’ ( for XL at least ThisWorkbook, Class, Form & Sheet ) Module types to be exported.
So far, export seems to work very nicely for Excel, but.. currently its only possible to export modified ( and newly created Modules ) from an Excel document that already has macros in it ( I hope to get a chance to look at rectifying that today )

So, what can exactly can you export? given a document like Userform1.xls that just has a userform, but no actual code. You could import that document and modify/create basic modules and macros. Starting with that document, I opened it with Openoffice and added some code. Note: all of the code now to be found in the re-saved ( exported ) document was created in open office. In detail what I did was
* Created new ‘Sheet’ modules, the macro export code even takes care to make sure the codenames are set up properly ( necessary to ensure the correct association between the sheet objects and the associated macro code ) I wrote sheet activate event routines that should be associated with the correct Sheets that will run when you navigate through the sheets.
* Created a new Class module ( please don’t depend on the CLASSMODULE option, its quite experimentatal undocumented and likely to change violently )
* Modified/Create a Form module associated with an already imported Userform. Since we don’t support export of Openoffice dialogs ( to Userforms ) any new Dialogs added won’t be exported. But if you delete an imported Userform from the document and resave it then of course it will be deleted.
* Created a new ‘ThisWorkbook’ module. I added here code for a Workbook_open event, you can see that event getting fired when opening the document with Excel

* Created normal code Modules, note: only modules in the ‘Standard’ library will be exported. I added a new function here and modified cell ‘A1′ to call it
Comments (0)