Problem with Exporting Numbers to Excel

Hello,

I have a BIRT report that I use to extract 3 computed columns with type "Decimal". They display in the report without any problems but I need to elaborate on them in Excel. OK, so I export to Excel and I open the file. The number values are there, but they are not recognised as numbers in Excel. They are listed as numbers stored as text. In order to use them, I have to select the cells and click on "Convert to Number".

Why is this happening? The computed column in the dataset has type "Decimal" (I have tried Float and Integer as well) and in the editor presentation the cells are formatted as "Fixed" with 0 decimal places.

What can I do?

Thank you!

Andrew

Find more posts tagged with

Comments

kclark

What version of BIRT are you running? I just created a report with the sample database with computed columns to excel and everything came over fine, I was able to autosum and other functions without the need to convert. Could you post your report and some sample data?

spriteburn

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="110286" data-time="1349792840" data-date="09 October 2012 - 07:27 AM">
What version of BIRT are you running? I just created a report with the sample database with computed columns to excel and everything came over fine, I was able to autosum and other functions without the need to convert. Could you post your report and some sample data? </blockquote>
 
Hello, thank you for your reply. 
 
I am using the BIRT 2.5.2 Framework. I cannot provide sample data because I am generating reports on an IBM Rational Team Concert application and running the reports there. Do you know of any limitations with this BIRT version? 
 
I should also mention the file is exported with an .xls format that when opened with Excel produces the following warning: 
 
The file you are trying to open , 'filename.xls', is in a different format than specified by the extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? 
 
I appreciate your input. 
 
Best regards, 
 
Andrew

Yaytay

Hi Andrew, 
 
That warning is normal with the built-in emitter, because it doesn't output files in either XLS or XLSX format. 
It actually outputs them in a Microsoft specified XML format, but there is no file extension specified for that format - so the only way for BIRT to get Excel to open the files is by using the wrong extension. 
 
There are third party emitters that don't have this problem, including <a class='bbc_url' href='http://www.spudsoft.co.uk/2011/10/the-spudsoft-birt-excel-emitters/'>SpudSoft</a>(mine); <a class='bbc_url' href='http://code.google.com/a/eclipselabs.org/p/native-excel-emitter-birt-plugin/'>Native</a> or <a class='bbc_url' href='http://www.arctorus.com/'>Arctorus</a>. 
I'm not sure how much support you'll get for BIRT 2.5.2, however (for SpudSoft I know it works, but I don't actively test on it). 
 
Your data type issue may be fixed by using a different emitter, but it may not. 
One thing to check is that the data type is correct at all points from source to cell - especially if you've ever changed the type of the data. 
BIRT needs the type specifying at (from memory) the actual result set, the declaration of the result set, the binding and the data control. 
If you ever do change one of these the others won't be automatically changed for you (if you do change the data type of a source column it's often simplest to delete the binding and data control and recreate them). 
 
If you could post your report, even if it couldn't be run, it may help us to diagnose (but then again, it may not :rolleyes: ). 
 
Jim

spriteburn

Hello Jim, 
 
Thank you for your response. 
 
The decimal values are actually Strings that I am converting to Decimals in the computed column. I have a generic key-value pair JavaScript array that stores String values which are then declared as a Number in the computed column. 
 
So when I initialize the report I create my array 
 
stringAttributes = [];
 
After the array is filled I create my computed column like this: 
 
if (stringAttributes[row["ID"]+"effort"] == null) 
0; 
else 
new Number(stringAttributes[row["ID"]+"effort"]);
 
Where row["ID"] is an Integer pulled from DB (which actually exports correctly to Excel) and my computed column has data type "Decimal" and in the layout they are formatted as a fixed number. 
 
Regards, 
 
Andrew

Yaytay

Hi Andrew,

Please can you post your report, or even better a self-contained report based on a scripted data set that shows the problem?
It's a lot easier for me, or anyone else, to fix a problem I can see, rather than trying to reproduce it from scratch.

Thanks.

Jim

spriteburn

Hello Jim,

I am unable to provide any information and don't know how to create a scripted data set. You have given me an idea of where the problem might lie, so I will take a look at that.

Thank you for your support.

Andrew

spriteburn

Hello Jim,

Just a note to say that I've managed to solve the problem. I cast the string attributes to numerical data types (Floats and Integers) and the added them to the presentation table.

The reason that the export was not working properly was that I assumed the current table in the presentation would automatically reflect the changes made to the dataset. When I completely removed and re-added the table and exported that, everything worked without problems! I'll be sure to add this little detail to my list of BIRT reporting troubleshooting.

Thank you for your availability!

Regards,

Andrew

Phaneendra Balla

Hello, I have a column which has both string values and Integer values. The Integer values in that column are showing a green color icon warning in excel sheet. Is there a way to hide that warning in Excel sheet?