Facing problem when using formulas in BIRT report for excel report

I am working on BIRT report,in this i can successfully export excelformula when export to excel with the use of spudsoft emitter that comes default with birt. The problem is when i reference a sheet from an another sheet it throws error. Like in BIRT report i have two table first table name is table1 and second table name is table2(so that this name is used as sheet name)both first table has pagebreak after always, so two sheets will be created. When i export it to excel, the excel report with two sheets 'table1' and 'table2' created successfuly(without a formula that refer another sheet). 
 
When a column of table1(sheet1) is refered from table2(sheet2) it refers and exported report works successfuly.
 
example-1
 a column in table2 refer a value of table1 column( B,the code for that table2 dataelement onRender event is
<pre class="_prettyXprint _lang-js">
if((reportContext.getRenderOption().outputFormat == "xlsx" )||(reportContext.getRenderOption().outputFormat == "XLSX"))
{
p = row.__rownum;
this.text="table1!B"+prow;
}
</pre>
it exports and refer the table1 value correctly.
 
But when refering a column of table2 from table1, this time it throws error.Sheet index (-1) is out of range (0..0)
 
example-2
a column in table1 refer a value of table2 column(C),the code for that table1 dataelement onRender event is
<pre class="_prettyXprint _lang-">
if((reportContext.getRenderOption().outputFormat == "xlsx" )||(reportContext.getRenderOption().outputFormat == "XLSX"))
{
p = row.__rownum;
this.text="table2!C"+prow;
}
</pre>
My assumption is when table1 is generated that time table2 is not available.If this is the correct assumption, then my solution is to changing the order of element while rendering(i dont have idea for the possiblity).
 
Here I have attached working and notworking versions rptdesign with exported excel report for both rptdesigns. Please find it.
 
The rptdesign in which referring a upcoming sheet, that means in Sheet1 a column refers a column of sheet2.(Not working version)

Find more posts tagged with

Comments

JFreeman

It looks like you are using the spudsoft emitter, is that correct?
Have you made any changes to the emitter?
 
With your sample report, in my testing it does not take the values you are providing as formulas by default. I had to add:
<pre class="_prettyXprint _lang-js">
ExcelEmitter.ValueAsFormula = true; </pre>
After that, it takes the values as formulas and I do not get any errors.
Take a look at the attached modified report and it's output.

Mani Kandan

JFreeman,
 
I am using spudsoft emitter , actually when we download birt runtime/eclipse spudsoft emitter comes default with this.. But this default emitter does not export excel formulas. Because The recent version of birt i am using from feb 2015(4.4.1) does not have this feature.
 
The problem is, eventhough the birt has updates regularly the spudsoft emitter does not comes with formula export feature. See here: <a data-ipb='nomediaparse' href='https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/commits/1fca2f0'>https://bitbucket.org/yaytay/spudsoft-birt-excel-emitters/commits/1fca2f0</a>
 
This is the bugfix to export formula error in recent versions of birt spudsoft emitter has. But i am still wondering that this bug has been fixed on 2013-05-21 . I dont know why the eclipse team has not updated yet to their recent versions.
 
So I made these changes(bug fix) to my current using version birt runtime jar ( exitst in tomcat and eclipse (spudsoft emitter)).
 
after this bugfix only, spudsoft emitter exports formula.
 
please check the xlsx file that you have posted.It does not have any formula.
 
and you are telling that i have not added excelEmitter.ValueAsFormula userproperty to TRUE?( have you checked the xmlsource?).
<pre class="_prettyXprint _lang-xml">
<cell id="175">
<text-data id="251">
<list-property name="userProperties">
<structure>
<property name="name">ExcelEmitter.ValueAsFormula</property>
<property name="type">boolean</property>
</structure>
</list-property>
<property name="ExcelEmitter.ValueAsFormula">true</property>
<method name="onRender"><![CDATA[if((reportContext.getRenderOption().outputFormat == "xlsx" )||(reportContext.getRenderOption().outputFormat == "XLSX"))
{
ExcelEmitter.ValueAsFormula = true;
this.text="Customer_Address!B"+(row.__rownum+3);
}]]></method>
<expression name="valueExpr">row["CITY"]</expression>
<property name="contentType">html</property>
</text-data>
</cell>
</pre>
<pre class="_prettyXprint _lang-js">
if((reportContext.getRenderOption().outputFormat == "xlsx" )||(reportContext.getRenderOption().outputFormat == "XLSX"))
{
this.text="Customer_Address!B"+(row.__rownum+3);
}
</pre>
to my cell for which i need to show formula on excel? no, i did. check my rptdesign.
<div> </div>
The problem i am facing is,I need to refer the city column of the second sheet in a sheet1 column, this is what throws error when i try like below,
 
<div>
<pre class="_prettyXprint">
if((reportContext.getRenderOption().outputFormat == "xlsx" )||(reportContext.getRenderOption().outputFormat == "XLSX"))
{
this.text="Customer_Address!B"+(row.__rownum+3);
}</pre>
</div>
 
Sheet1 Name - Customer_Credit Sheet2 Name - Customer_Address
 
in a Sheet1 for a column referring the "B" column of Sheet2"Customer_Address". This makes the problem but referring sheet1 from sheet2 works. Check my <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_id=12338' title="Download attachment">ReferSheet1fromSheet2_works.xlsx</a> sheet, it says that the sheet2 can refer a column in sheet1. Check this value in excel "Customer_Address" E column 
<pre class="_prettyXprint _lang-js">
=$Customer_Address.C3
</pre>
its works, but reverse referring City column of Customer_Address in Sheet1 makes error.

JFreeman

Interesting, I've not seen anybody apply the excel emitter options as user properties on an element and not a scripting event.
 
The referenced addition of formulas that includes the setting "ExcelEmitter.ValueAsFormula" has been implemented and that setting can be used out of the box in version 4.4.1. However, setting as the user property the way you have does not seem to work out of the box and with that configuration the values of the text you are setting on onRender are being displayed exactly as text in the output. See the attach excel output for an example.
 
After adding the "ExcelEmitter.ValueAsFormula" to the onRender scripting event with the rest of your code, this no longer happens and the text you have set is interpreted as a forumla which is why in the previous output I attached the city names appear on the first page being read from the second page.
 
This shows it is treating the text as a formula and calculating the correct value from page 2 on page 1. I'm guessing the error you are having is due to the modifications you have made to the emitter.
 
Are you wanting the cell to still show the forumla instead of just the value calculated from the formula during render?
If so, what is your use case?

Mani Kandan

@JFreeman 
 
I want to have both formula and value. 
 
like i have in my attached excel file :- <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_id=12338'>ReferSheet1fromSheet2_works.xlsx</a> 
 In this sheet, i can refer a column of sheet1 in sheet2..as below image

JFreeman

Okay, in that case I do believe this is going to be a factor of how the code that was added to emitter handles the formulas. I believe you are correct that the issue that when the emitter is rendering the second sheet has not yet been created yet which means the formula reference is point to a sheet that doesn't exist.
 
In a situation of needing page 1 to reference page 2 and page 2 to reference page 1, I'm not sure of a way to get around this behavior as you would have to swap the pages during the emitter rendering cycle which I believe would mean adding code into the emitter and not something you could do inside the report design itself.
 
You could reach out to the creator of the spudsoft emitter directly and report this as a bug. He may be able to provide more insight into the emitter itself.
 
Otherwise, you will have to live with the behavior of the default emitter setup of getting values and not full formulas.

Mani Kandan

@Freeman this is what my thought too. Thanks for your valueable time shared with me and for the confirmation.

JFreeman

You're welcome.
Let us know if you have additional questions.

deepti2623

HI Can you please tell how can we apply live formulas in excel ? How can formulas be exported to excel ?