Processing Excel Report Created from BIRT

Hi All

I am planning to use the BIRT as the reporting tool, but the only constraint being that the BIRT dosen't give TXT format. So I am trying to acheive the same. For that I tried getting the EXCEL format and trying to parse the EXCEL and creating the TEXT file.
I have tried two options
1. Apache POI
2. JXL

The parsing code is working fine with an EXCEL file but when I run the code on the EXCEL generated from BIRT I am getting errors as follows

JXL API

jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:221)
at jxl.Workbook.getWorkbook(Workbook.java:198)
at JXMExcelToText.read(JXMExcelToText.java:16)
at BIRTReportBuilder.executeExcelReport(BIRTReportBuilder.java:178)
at BIRTReportBuilder.main(BIRTReportBuilder.java:396)

Using POI

java.io.IOException: Invalid header signature; read 7311066695147732796, expected -2226271756974174256
at org.apache.poi.poifs.storage.HeaderBlockReader.<init>(HeaderBlockReader.java:100)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:84)
at POIExcelToText.ExcelToText(POIExcelToText.java:24)
at BIRTReportBuilder.executeExcelReport(BIRTReportBuilder.java:171)
at BIRTReportBuilder.main(BIRTReportBuilder.java:389)

Kindly let me know how to proceed on the same. Also if there is any other way of gettting the TEXT file from BIRT.

BIRT VERSION:: 2.5
POI Version: poi.3.0.1FINAL
JXL Version: jxl2.6.10

BIRT Code for Excel Exporting::
EXCELRenderOption excelOptions = new EXCELRenderOption();
excelOptions.setOutputFormat("xls");
excelOptions.setOutputFileName("/QuantumFX/QFXREPORTS/BIRTSAMPLE/birt/output/Contract.xls");
task.setRenderOption(excelOptions);

Find more posts tagged with

Comments

thuston

I don't know why you would want a txt file, but here are some more options.

*You can simply use Excel to save as CSV.
*You can use the Actuate (BIRT) SpreadSheet API to read the XLS and plain Java to generate your text file (Part of the same Java program). The Free API should be sufficient for this need.
*BIRT itself has a CSV emitter you can dowload and use.

Gaganckc

Many thanks for the reply

We need the TEXT file because these are what is needed.
Please find the comments inline.

*You can simply use Excel to save as CSV.

<>

*You can use the Actuate (BIRT) SpreadSheet API to read the XLS and plain Java to generate your text file (Part of the same Java program). The Free API should be sufficient for this need.

<<Will try that option and update the same>>

*BIRT itself has a CSV emitter you can dowload and use.
<<The CSV Emitter is also an option but the MAIN task (Getting a TEXT format) would still remain the same. Will try this option as well and update>>

Gaganckc

An Update from My END.
I have just now checked with the CSV Emitter and its also giving me the same error.

Now I will try with the SpredSheet API and check.

Meanwhile If any one have any comments do let me know.

Gaganckc

Hi All
Using the SpreadSheet API I am getting the folowing Error
################################################################################
There is no license file.

If using the free API, setting Document.getBook().setAutoRecalc(false) is recommended to prevent inconsistent behavior since recalc is not supported.

Non-Excel file types are not supported in the free API
###############################################################################

The code which is used is as follows

BookModel bookModel = BookModel.Factory.create();
bookModel.getBook().setAutoRecalc(false);
bookModel.getBook().read(new FileInputStream("XLS Generated from BIRT"));
Document doc = bookModel.getBook().getDocument();
doc.getBook().setAutoRecalc(false);
File xlsFile = new File(inputPath);
doc.fileSaveAs(new java.io.File(New FIle Path), DocumentType.EXCEL_97_REPORT_VIEW, null);

Kindly help me how to proceed on the same as there is nothing I suppose using which I can read the XLS File generated from BIRT.

thuston

"Non-Excel file types are not supported in the free API" 
 
I'm a little worried about that for the TABBED_TEXT, but we'll start with the correct DocumentType. 
 
EXCEL_97_REPORT_VIEW tries to run the documnent like it was an SOD (Spreadsheet design) 
EXCEL_97_WORKBOOK will save it out still as a regular XLS (the same as you input). 
 
You can try TABBED_TEXT to get your delimited text output. 
If that is not sufficient, you will want to getEntry from all the cells and write your own text file. 
 
I have not tested the below, but it should be pretty close. 
<pre class='_prettyXprint _lang-auto _linenums:0'>Document doc = new Document(null, new FileInputStream("XLS Generated from BIRT"), null);
BookModel bm = BookModel.Factory.create(doc.getBook());
//make a regular java interface to a new txt file
int numSheets = bm.getNumSheets(); //probably 1
for ( int i = 0; i < numSheets; i++){
bm.setSheet(i);
for ( int row = 0; row < bm.getMaxRow; row++){
String textLine = "";
for ( int col = 0; col < bm.getMaxCol; col++){
String value = bm.getFormattedText(row,col);
// append value to textline
}
// append textline to your txt file
}
}</pre>
You can use additional BookModel methods like getColWidth to make your own calculations about adding whitespace between column values.