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

问题描述

我是jasper的新手。我的项目使用jasper创建一个excel模板，该模板只有列名（例如：Name，Age，Department，Location），它使用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 .

对于位置字段的示例，我可以设置诸如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. 使用一个小程序来计算调用之前的次数（详情） / li>
   
3. 使用POI查找最后一行。


4. 在Excel中使用最大值（2003）为65536

希望这有助于

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