如何使用 jrxml (jasper api) 在 excel 中显示下拉列表? [英] How to show dropdown in excel using jrxml (jasper api)?

问题描述

我是 jasper 的新手.我的项目使用 jasper 创建一个只有列名(例如: Name 、 Age 、 Department 、 Location )的 excel 模板，它使用 jrxml 进行字体和对齐.[基本上我们使用 来显示列名]

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 .

例如，对于 'Location' 字段，我可以设置诸如 'Texas'、'California'、'FortWorth' 等值.我不是从 DB 查询这些值，我只是想在 .jrxml 中对这些值进行硬编码.我必须再创建一行，其中 Location 列应该有下拉值，用户可以从中选择一个并上传到我的应用程序

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

在下载的 excel 中，我想要一个包含上述值的下拉列表，以便用户可以选择而不是自己输入.

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

有什么办法可以把它放在 .jrxml 中.如果这是不可能的，那么在 excel 的下拉列表中给出可以呈现这些的代码.

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.

我的一个字段的示例 .jrxml 是

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

推荐答案

我不认为使用当前的 jasper api 6.0 这是可能的，但是使用 POI(已经在您的类路径中)它导出后添加它非常容易.

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.

首先我们导出或JasperPrint print，到excel(包括代码，因为它最好删除空行，我们稍后会用poi处理，Exception代码>处理超出了这个例子)

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();

现在报告已导出到 excel，我们使用 POI 重新打开它并添加我们的数据验证.

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();

rowCount 可以通过以下方式实现:

The rowCount can be achived by:

1. 再次查询数据库
2. 使用一个脚本来计算 beforeDetailEval() 被调用的次数
3. 使用 POI 找到最后一行.
4. 在 excel (2003) 中使用最大值为 65536

希望能帮到你

这篇关于如何使用 jrxml (jasper api) 在 excel 中显示下拉列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！