Menu
I'm generating a report using JasperReports and suppose to export it into several formats. But currently im only working with excel reports.
Here is my controller code.
InputStream in = reportTemplate.getTemplate(reportInquery.getTemplateFile());
JasperPrint print = JasperFillManager.fillReport(in, null,
new JRResultSetDataSource(reportDao.getReportData(reportInquery)));
resp = HttpConfig.setHeaders(reportInquery, resp);
Exporter exporter = reportOption.getRenderOptions(reportInquery.getFormat(), resp.getOutputStream(), print);
exporter.exportReport();
In my configuration factory, excel report configures as follows, after calling getRenderOptions method.
public Exporter exporterOptions(OutputStream outputStream, JasperPrint print) {
JRXlsExporter exporter = new JRXlsExporter();
exporter.setExporterInput(new SimpleExporterInput(print));
OutputStreamExporterOutput outputStreamExporterOutput = new SimpleOutputStreamExporterOutput(outputStream);
exporter.setExporterOutput(outputStreamExporterOutput);
SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
configuration.setOnePagePerSheet(true);
configuration.setDetectCellType(true);
configuration.setMaxRowsPerSheet(100);
configuration.setRemoveEmptySpaceBetweenColumns(true);
configuration.setRemoveEmptySpaceBetweenRows(true);
exporter.setConfiguration(configuration);
return exporter;
}
To create JRResultSetDataSource I have used oracle OracleCachedRowSet in above getReportData method.
public RowSet getReportData(ReportInqueryDTO reportInquery) {
try {
String query = getQueryById(reportInquery.getTemplateId());
Map<String, String> params = new HashMap<>();
params.put("fromDate", reportInquery.getFromDate());
params.put("toDate", reportInquery.getToDate());
params.put("appName", this.applicationName);
RowSet rowSet = namedParameterJdbcTemplate.query(query, params, new ResultSetExtractor<RowSet>() {
@Override
public RowSet extractData(ResultSet resultSet) throws SQLException, DataAccessException {
OracleCachedRowSet rs = new OracleCachedRowSet();
rs.populate(resultSet);
return rs;
}
});
rowSet.beforeFirst();
return rowSet;
} catch (Exception exception) {
log.error("getReportData failed.");
}
return null;
}
My problem is, excel always comes with first record missing from, where the cursor of the resultSet is pointed. (Ex: Here rowSet.beforeFirst() means before 1st index. I'm missing the first record in this scenario.)
I'm stuck with this issue couple of days. I want to know if this is a jasper issue how can I resolve it. If it can not be resolved, I want to know how to add a cached rowset dynamically before the first record?
I have couple of jrxml files and, one of it is added below.Every file follows the same format.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0 -->
<!-- 2014-12-30T15:20:10 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="TTT_Call_Transfers_Report" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="b61676f8-0366-4125-996c-7564d0f77eb4">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="NewDataAdapter"/>
<style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<subDataset name="Dataset1" uuid="54db10cf-a696-4f7d-b642-96871feb617d">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="NewDataAdapter"/>
<queryString language="SQL">
<![CDATA[]]>
</queryString>
<field name="DATE_OF_TRANSFER" class="java.sql.Timestamp"/>
<field name="AGENT_ID" class="java.lang.String"/>
<field name="AGENT_NAME" class="java.lang.String"/>
<field name="CTALK_CONTACT_ID" class="java.lang.String"/>
<field name="ASSOCIATED_QUEUE_ID" class="java.lang.String"/>
</subDataset>
<queryString language="SQL">
<![CDATA[]]>
</queryString>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="63" splitType="Stretch">
<staticText>
<reportElement x="160" y="16" width="222" height="30" uuid="d89e2553-d3fb-4360-b797-4f69b60938a5"/>
<text><![CDATA[TTT Call Transfers Report]]></text>
</staticText>
</band>
</title>
<detail>
<band height="253" splitType="Stretch">
<componentElement>
<reportElement x="0" y="0" width="555" height="253" uuid="c229d6c3-18b7-4de2-a669-01e11d25c642"/>
<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="Dataset1" uuid="e0262612-dc81-4d84-abf8-9b52609ba792">
<datasetParameter name="REPORT_DATA_SOURCE">
<datasetParameterExpression><![CDATA[$P{REPORT_DATA_SOURCE}]]></datasetParameterExpression>
</datasetParameter>
</datasetRun>
<jr:column width="40" uuid="a91061c3-7760-43d2-bdc1-091f0e712aaa">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="6afcdf24-958c-49f4-9ef3-b757ffd1e8de"/>
<text><![CDATA[DATE_OF_TRANSFER]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="40" height="30" uuid="84a5e4b7-30e0-409c-b2ab-e13c1dd02950"/>
<textFieldExpression><![CDATA[$F{DATE_OF_TRANSFER}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="c7b5a8eb-04b6-4cd6-9328-e1e8ce11a2fc">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="d7cc290a-6918-4d47-8f55-b43761bee59b"/>
<text><![CDATA[AGENT_ID]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="40" height="30" uuid="b5f2b8bf-9132-4447-9209-f1fcb3b873c4"/>
<textFieldExpression><![CDATA[$F{AGENT_ID}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="d2427379-2c72-4e33-9b46-ade8727dbed5">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="bdcb01c5-5073-4a10-b498-e2d1a2cf7c78"/>
<text><![CDATA[AGENT_NAME]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="40" height="30" uuid="e259ef09-f446-491c-88dd-f7694197e060"/>
<textFieldExpression><![CDATA[$F{AGENT_NAME}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="81fe9b33-57aa-48b2-914b-e3e9b45cf6a1">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="ef5e379b-65de-4ea0-a5cb-13368efa16f0"/>
<text><![CDATA[CTALK_CONTACT_ID]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="40" height="30" uuid="5f49f1a9-379a-4d02-9e55-fc6a865aa828"/>
<textFieldExpression><![CDATA[$F{CTALK_CONTACT_ID}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="f6e8b2a6-925b-41b5-9043-1ffa215871f1">
<jr:columnHeader style="Table_CH" height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="b55c7599-a237-43f1-aff5-464717e3c917"/>
<text><![CDATA[ASSOCIATED_QUEUE_ID]]></text>
</staticText>
</jr:columnHeader>
<jr:columnFooter style="Table_CH" height="30"/>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement x="0" y="0" width="40" height="30" uuid="0d2d53d0-1cb2-4d64-a09d-b0eeacca694d"/>
<textFieldExpression><![CDATA[$F{ASSOCIATED_QUEUE_ID}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
</jr:table>
</componentElement>
</band>
</detail>
Thanks in advance.
489
The most common situation where we face this problem is when the master report does not need to iterate on anything, but rather give the data source to one of its subreports.
If you use iReport, you can select the static text area. In the right column you will find the properties of that object. Under "Static text properties -> Text" you will find your text. Click onto the button [...] so that a window opens where you can enter your text including line breaks.
Please post your jrxml file.
The problem might not be in your code.
In Jasper, the record pointer in the data source is incremented by every element that receives it (so, for exemple, if you in the report you have a table and you set the datasource of the table as being the datasource of the report it will skip the first record. If this is the case, you have to pass the datasource from the report as a parameter to the table).
UPDATE:
1.Send your datasource from the server as a parameter, and fill the report with a different one (can be empty).
2.Decalre a new parameter in the report of same type as your bean collection, let's name it 'DS1'.
3.Set TableDatasource to use the $P{DS1} parameter.
See my response to How to show JRBeanCollectionDataSource data with help of Table component? for an example.
66
As Laura mentioned in this answer the record pointer in the data source is incremented by every element that receives it. Since I'm passing the result set, I thought no point in passing result set again as a parameter. Finally I decided to add an empty record at the beginning of the result set.
In getReportData method in above question, you can do it by, following below method.
RowSet rowSet = namedParameterJdbcTemplate.query(query, params, new ResultSetExtractor<RowSet>() {
@Override
public RowSet extractData(ResultSet resultSet) throws SQLException, DataAccessException {
OracleCachedRowSet rs = new OracleCachedRowSet();
rs.populate(resultSet);
// Have to add a empty row, because jasper is not displaying
// the first row of report.
rs.setReadOnly(false);
rs.beforeFirst();
rs.moveToInsertRow();
int numCol = rs.getMetaData().getColumnCount();
for (int i = 1; i < numCol + 1; i++) {
// Add null inserted row to each column
rs.updateNull(i);
}
rs.insertRow();
rs.beforeFirst();
return rs;
}
});
As they already said, the problem might not be in your code.
The record pointer in the data source is incremented by one when you pass your dataSet into a ResultSet. I solved this problem creating a ResultSet with the properties: ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE. In the approach, you can only execute your query in your database and do rs.beforeFirst.
Connection conn = ...;
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rset = stmt.executeQuery(sql);
if(rset != null){
rset.beforeFirst();
return rset;
} else return null;
} catch (SQLException e) {
e.printStackTrace();
}
The method beforeFirst() will set the record pointer before the first row.
So, you can do:
rs.beforeFirst();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
jp = JasperFillManager.fillReport(jasperFile, parametersJasper,jrRS);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
