How to show dropdown in excel using jrxml (jasper

Question

I am new to jasper . My project used jasper to create an excel template that has only column names (eg : Name , Age , Department , Location) which uses jrxml for fonts and alignment. [basically we used the for showing the column names]

User can download the template and they can enter the values they want.

Now in order to avoid user enter the details manually by entering values , I would like to give dropdowns in the template with some hard coded values .

For example for the field 'Location' , I can set values like 'Texas' , 'California', 'FortWorth' etc . I am not querying from DB for these values , I just want to hard code these in .jrxml . I have to create one more row where the Location column alone should have drop down values from which user can pick one and upload to my application

In the downloaded excel , I want a dropdown with the above values so that user can select instead of typing themselves.

Is there any way to put this in .jrxml . If that is not possible then give the code that can render these in a dropdown in excel.

My sample .jrxml for one field is

<staticText>
    <reportElement mode="Opaque" x="684" y="0" width="114" height="20" backcolor="#808080">
    </reportElement>
    <box leftPadding="10">
        <pen lineColor="#000000" />
        <topPen lineWidth="0.5" />
        <leftPen lineWidth="0.5" />
        <bottomPen lineWidth="0.5" />
        <rightPen lineWidth="0.5" />
    </box>
    <textElement>
        <font size="10" isBold="true" />
    </textElement>
    <text><![CDATA[Location]]></text>
</staticText>

Please let me know if more details are required

Answer 1

I don't think that with current jasper api 6.0 this is possibile but with POI (that is already in your classpath) it is farily easy to add this after export.

First we export or JasperPrint print, to excel (include code since its best to remove empty rows, we will process with poi later, Exception handling is beyond this example)

JRXlsExporter exporter = new JRXlsExporter();
File outputFile = new File("excelTest.xls");
exporter.setExporterInput(new SimpleExporterInput(print));
exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outputFile));
SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
configuration.setOnePagePerSheet(false);
configuration.setDetectCellType(true);
configuration.setCollapseRowSpan(false);
configuration.setWhitePageBackground(false);
configuration.setRemoveEmptySpaceBetweenColumns(true);
configuration.setRemoveEmptySpaceBetweenRows(true);
exporter.setConfiguration(configuration);
exporter.exportReport();

Now that the report is exported to excel, we re-open it with POI and add our data validation.

int rowCount = 50; //This will be discussed at end.
int cellWithDV = 1;

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(outputFile));
HSSFSheet sheet = workbook.getSheetAt(0);
CellRangeAddressList addressList = new CellRangeAddressList(1,rowCount, cellWithDV, cellWithDV);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                    new String[]{ "Texas" , "California", "FortWorth"});
DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);
workbook.write(new FileOutputStream(outputFile));
workbook.close();

The rowCount can be achived by:

1. Querying the database again
2. Use a scriptlet that counts how many times beforeDetailEval() is called
3. Use POI to find the the last row.
4. Use maximum value in excel (2003) is 65536

Hope this helps