BIRT report with EXCEL formula

[darryljohn]

I have a BIRT report that I would like to export to EXCEL. I would like to include EXCEL formulas in the report. There is very little documentation online. Does anybody have experience with this?

[claesrosell]

Someone else needs to step in and correct me if I am wrong, but I do not think that this is currently supported.
I assume that you have read this stackoverflow article, but I think that the upvoted answer is wrong.

I took a look at the source code and the properties ExcelEmitter.ExtractMode and ExcelEmitter.ValueAsFormula does not seem to be used by the ExcelEmitter. The necessary calls to POI to set the cell type to formula is never called at all in the ExcelEmitter source code.

[hvbtup]

I think this is one of the things which Jim Talbut (alias yaytay) added to the "SpudSoft Excel emitter" after he contributed the emitter to the BIRT project.

Unfortunately, the emitter was hosted on bitbucket.org and the code is no longer publicly availabe AFAIK.

But fortunately I included that into my fork of BIRT (https://github.com/hvbtup/birt) before Jim's repo vanished

@darryljohn You may try to download a binary (designer-all-in-one for windows or POJO runtime) from by fork or build it yourself and see if it works for you.

I hesitate to make a PR for the whole thing because there were so many changes that I fear it could break functionality of existing reports, and I cannot provide the necessary support. I wish Jim would turn up again.

Please note that the Excel emitter in my fork is untested.
We are using an older version of the SpudSoft emitter in production reports and it worked, including formulas AFAIK.
I never used them in any report myself, but I think colleagues did use formulas.
That older version is still much newer than the Spudsoft emitter from eclipse/birt).

In my fork I am in the process of creating a new release. This included such a lot of diff for trivial changes like changing the license version, code reformatting etc that I could not carefully compare each change - it was hard enough work to make it compile...

[claesrosell]

@hvbtup Do this commit e095563 include the lion´s share of the enhancments to the emitter or are there more which I have not found yet?

[hvbtup]

Ah yes, you're right of course - I contributed most of Jim's changes.
I wish I had the time to compare the emitter sources in my fork with birt/eclipse to see if and how the functionality actually differs.

Base line is: Formulas should work. At least I can see the lines

public static final String VALUE_AS_FORMULA = "ExcelEmitter.ValueAsFormula";
public static final String FORMULA = "ExcelEmitter.Formula";

in the commit.

Unfortunately I don't have an example report.

[claesrosell]

It is a massive commit and the contribution information is gone. It may be other contributors then Jim to those changes. We can not include it into Birt without ECA from all the contributors. Hopefully Jim can help us

[wimjongman]

@Yaytay Jim, can you help us with this?

[hvbtup]

IIRC Jim was the only one contributing code actually.
Other contributed some ideas in form of issues, e.g. my ex-colleague Jan Zimmer and me.
But that's hard to tell for sure, since the bitbucket repository and its commit history seems lost.

I found links like
https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/commits/
in the search history. Maybe the repository still exists but is no longer publicly accessible?

I thought that - since the emitter was contributed from Jim to the BIRT project - his later changes (and those from others) would be under the ECA automatically. Am I wrong here?

The files had a header comment like this:

Copyright (c) 2011, 2012, 2013 James Talbut.
[email protected]

All rights reserved. This program and the accompanying materials 
are made available under the terms of the Eclipse Public License v1.0
which accompanies this distribution, and is available at
http://www.eclipse.org/legal/epl-v10.html

Contributors:
    James Talbut - Initial implementation.

I only looked at some of the files, not each and every file.

[Yaytay]

Well, I've found the repo (on the computer that I've since handed down to my son!).

It's been a very long time since I've worked on BIRT and I don't intend to change that, but you are very welcome to the repo (I can convert it to github, but probably not until Monday).

[claesrosell]

This is great news! We will take good care of it.
Thank you.

[TheLaserNinja]

I'm actually surprised to see this in there at all. I never messed with the Spudsoft Emitter. Actuate/OpenText had originally kept all the good functionality, like formulas, native charts, etc, to themselves for the commercial Excel emitter, so I remember thinking when Spudsoft ware released that it was nice to have something else that addressed some of the original Excel emitters shortcomings.

We appreciate it Jim.

[Yaytay]

Feel free to fork: https://github.com/Yaytay/spudsoft-birt-excel-emitters

[hvbtup]

All the commits in the repository came from Jim directly, so to the best of my knowledge he was the one and only contributer of the emitter.
I don't think that we have any trouble with missing ECAs.

[Yaytay]

That is correct, no one except ever committed anything to that repo.
Changes were made after it was copied into the BIRT repo.

[claesrosell]

I recently checked the repository which Jim uploaded recently. Unfortunately it does not contain the latest changes.
I took a closer look at the e095563 commit and even if it spans a lot of files it pretty easy to comprehend. Many of the changes seems to be from a refactoring of StyleConstants -> StylePropertyIndexes. From a quality perspective I would say that it is pretty safe to apply this patch. Without Jim´s commits someone else will need to be commiter of his changes, and he will need to be fine with that.

[hvbargen]

In my fork you can find a file engine\uk.co.spudsoft.birt.emitters.excel.tests\src\uk\co\spudsoft\birt\emitters\excel\tests\Issue85Formulae.rptdesign
which includes example usage of the ExcelEmitter.ValueAsFormula and ExcelEmitter.Formula user properties.
I'll try to run the example and see if it works.

Regarding the big commit hvbtup@e095563:
Obviously, this is the result of several commits to Jims original repository on bitbucket.
As you said, a lot of noise in this commit is just due to the refactoring. Probably the goal was to get rid of the CSS string constants.
The formula support was added in the same commit, but when I take a closer look at it, the formula support seems relatively easy.
It's more or less just a few lines in CellContentHandler.java methods emitContent and setCellContents (for details, look for "formular" in the diff).

Please note that - as I said - I hesitate to just create a PR with all the changes, because this would probably change the behavior regarding page-break / multiiple sheets. But as explained above, just adding formula support seems quite straightforward.

[hvbargen]

Tested the report Issue85Formulae.rptdesign with the all-in-one-designer built from hvbtup (tup_main branch). It seems to work, the cells B4 and D4 contain formulae.

@Yaytay BTW I wonder what the meaning of the files FixedExecutorManager.java, FixedRunTask etc. in engine\uk.co.spudsoft.birt.emitters.excel.tests\src\uk\co\spudsoft\birt\emitters\bugfix is? Do you still remember?
Does "Fixed" mean that you fixed issues in BIRT source code or is "Fixed" related to the layout?

[Yaytay]

If I remember correctly I think it's the former, workarounds for bugs/limitations in BIRT.
If that's the case it should be straightforward to compare them with the BIRT originals.
It's also possible, given the name of the dir, that a PR was submitted to BIRT and these were intended to be temporary.

[hvbtup]

Hmm, yes, that sounds reasonable. Seems like something about the page-breaking was missing in BIRT, so you created the minimum code to make it work :-)

[speckyspooky]

The change with the ValueAsFormula is implemented and will be part of BIRT 4.16