Hi,
I have just started using Jasper report and exporting data in HTML, PDF, Excel and CSV formats.
All the formats are working fine except for the CSV one. The CSV format is having an extra , in the middle of the csv data. This fake column is creating problems as the csv data is input for database.
Can somebody help?
10 Answers:
Posted on December 13, 2011 at 3:39am
Hi,
To export only columns you need, try to use the JRCsvMetadataExporter instead, and the related properties in the JRCsvMetadataExporterParameter.
To see an example, take a look into the demo/samples/jasper sample and run it with the
>ant clean javac compile fill csvMetadata
command.
Hope this helps,
sanda
Posted on December 13, 2011 at 4:34am
Hi Sanda,
Thanks for your reply. But I guess I was not clear with my question. The problem is with data also. Here is the sample output from the report
ID,FirstName,,LastName,Age
1,John,,Mclaine,25
2,Michael,,Smith,26
I want to remove that additional comma between FirstName and LastName columns.
Thanks for your reply. But I guess I was not clear with my question. The problem is with data also. Here is the sample output from the report
ID,FirstName,,LastName,Age
1,John,,Mclaine,25
2,Michael,,Smith,26
I want to remove that additional comma between FirstName and LastName columns.
Posted on December 13, 2011 at 5:00am
Well, I ran some tests on a local sample. All fields containing commas were double quoted before being exported and there were no duplicate commas or other errors.
If your input data come with duplicate commas, you can fix them with the replaceAll(...) method.
But if your input data are well formed and the extra comma is added during the export time, then it could be a problem here. In order to find the cause please post your jrxml file here (or better, a self consistent sample).
Thank you,
sanda
Posted on December 13, 2011 at 6:00am
Here is my JRXML file.
| Code: |
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports <a target="_blank" href="http://jasperreports.sourceforge.net/xsd/jasperreport.xsd""><a href="http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"" target="_blank">http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"</a></a> name="user_report" pageWidth="949" pageHeight="595" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="909" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" whenResourceMissingType="Empty">
<property name="ireport.zoom" value="1.5"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<style name="Arial_Normal" isDefault="true" fontName="Arial" fontSize="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<style name="Arial_Bold" fontName="Arial" fontSize="12" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<style name="Arial_Italic" fontName="Arial" fontSize="12" isBold="false" isItalic="true" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<queryString language="xPath">
<![CDATA[/users/user]]>
</queryString>
<field name="ID" class="java.lang.String">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
<field name="FIRST_NAME" class="java.lang.String">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
<field name="LAST_NAME" class="java.lang.String">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
<field name="AGE" class="java.lang.String">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="42" splitType="Stretch">
<staticText>
<reportElement style="Arial_Italic" mode="Transparent" x="16" y="0" width="373" height="39" forecolor="#009999" backcolor="#009999"/>
<textElement textAlignment="Center">
<font size="22" isBold="true" isItalic="true" isUnderline="false"/>
</textElement>
<text><![CDATA[User Report]]></text>
</staticText>
</band>
</title>
<pageHeader>
<band height="1" splitType="Stretch"/>
</pageHeader>
<columnHeader>
<band height="33" splitType="Stretch">
<staticText>
<reportElement style="Arial_Bold" mode="Opaque" x="78" y="0" width="111" height="32" forecolor="#FFFFFF" backcolor="#009999"/>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
<font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<text><![CDATA[LastName]]></text>
</staticText>
<staticText>
<reportElement style="Arial_Bold" mode="Opaque" x="189" y="0" width="86" height="32" forecolor="#FFFFFF" backcolor="#009999"/>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
<font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<text><![CDATA[FirstName]]></text>
</staticText>
<staticText>
<reportElement style="Arial_Bold" mode="Opaque" x="275" y="0" width="114" height="32" forecolor="#FFFFFF" backcolor="#009999"/>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
<font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<text><![CDATA[Age]]></text>
</staticText>
<staticText>
<reportElement style="Arial_Bold" mode="Opaque" x="0" y="0" width="78" height="32" forecolor="#FFFFFF" backcolor="#009999"/>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
<font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<text><![CDATA[Id]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="24" splitType="Prevent">
<textField pattern="" isBlankWhenNull="true">
<reportElement positionType="Float" stretchType="RelativeToTallestObject" mode="Transparent" x="0" y="0" width="78" height="24" forecolor="#000000" backcolor="#FFFFFF"/>
<box>
<pen lineWidth="0.5" lineColor="#006666"/>
<topPen lineWidth="0.5" lineColor="#006666"/>
<leftPen lineWidth="0.5" lineColor="#006666"/>
<bottomPen lineWidth="0.5" lineColor="#006666"/>
<rightPen lineWidth="0.5" lineColor="#006666"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" markup="none">
<font fontName="Arial" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">
<reportElement positionType="Float" stretchType="RelativeToTallestObject" mode="Transparent" x="189" y="0" width="86" height="24" forecolor="#000000" backcolor="#FFFFFF"/>
<box>
<pen lineWidth="0.5" lineColor="#006666"/>
<topPen lineWidth="0.5" lineColor="#006666"/>
<leftPen lineWidth="0.5" lineColor="#006666"/>
<bottomPen lineWidth="0.5" lineColor="#006666"/>
<rightPen lineWidth="0.5" lineColor="#006666"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Top" rotation="None" markup="none">
<font fontName="Arial" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{FIRST_NAME}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="true">
<reportElement positionType="Float" stretchType="RelativeToTallestObject" mode="Transparent" x="78" y="0" width="111" height="24" isPrintWhenDetailOverflows="true" forecolor="#000000" backcolor="#FFFFFF"/>
<box>
<pen lineWidth="0.5" lineColor="#006666"/>
<topPen lineWidth="0.5" lineColor="#006666"/>
<leftPen lineWidth="0.5" lineColor="#006666"/>
<bottomPen lineWidth="0.5" lineColor="#006666"/>
<rightPen lineWidth="0.5" lineColor="#006666"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Top" rotation="None" markup="none">
<font fontName="Arial" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{LAST_NAME}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">
<reportElement positionType="Float" stretchType="RelativeToTallestObject" mode="Transparent" x="275" y="0" width="114" height="24" isPrintWhenDetailOverflows="true" forecolor="#000000" backcolor="#FFFFFF"/>
<box>
<pen lineWidth="0.5" lineColor="#006666"/>
<topPen lineWidth="0.5" lineColor="#006666"/>
<leftPen lineWidth="0.5" lineColor="#006666"/>
<bottomPen lineWidth="0.5" lineColor="#006666"/>
<rightPen lineWidth="0.5" lineColor="#006666"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" markup="none">
<font fontName="Arial" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{AGE}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter>
<band splitType="Stretch"/>
</columnFooter>
<pageFooter>
<band splitType="Stretch"/>
</pageFooter>
<summary>
<band splitType="Stretch"/>
</summary>
</jasperReport> |
Post Edited by divinedragon at 12/13/2011 14:01
Posted on December 13, 2011 at 6:13am
Also here is the code snippet that I am using to generate the CSV report.
| Code: |
/* Report Type - CSV */ exporter = new JRCsvExporter(); /* Set Parameters for CSV */ exporter.setParameter(JRExporterParameter.JASPER_PRINT, print); exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, new FileOutputStream(file)); exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE); exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.TRUE); exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE); exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE); exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN, Boolean.TRUE); exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_CELL_BORDER, Boolean.FALSE); exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS, Boolean.TRUE); exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE); exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE); exporter.setParameter(JRXlsExporterParameter.MAXIMUM_ROWS_PER_SHEET, MAX_ROWS_PER_SHEET); /* Export the Report */ exporter.exportReport();</td></tr></tbody></table> |
Posted on December 13, 2011 at 6:31am
At a glance I think the problem might be caused by the white space to the left of the report title element 'User Report'. It results in an extra column added to the report.
Try to set a proper alignment for this element, and see if things are changed:
Hope this helps,
sanda
| Code: |
<staticText>
<reportElement style="Arial_Italic" mode="Transparent" x="0" y="0" width="389" height="39" forecolor="#009999" backcolor="#009999"/>
<textElement textAlignment="Center">
<font size="22" isBold="true" isItalic="true" isUnderline="false"/>
</textElement>
<text><![CDATA[User Report]]></text>
</staticText>
</td></tr></tbody></table> |
Posted on December 20, 2011 at 11:36pm
Thanks Shertage for that response.
Your solution works fine with the CSV report. The additional comma is now removed.
However, the same format is used for excel report as well where the heading comes at the center. But changing the header to start from x=0 shifts the header to absolute left. Is there anything to handle that as well?
Posted on December 21, 2011 at 2:05am
Hi,
To be sure I understood the problem: you are complaining about the text in the header, or about the header cell itself?
I ran the report sample with Excel output, and the text in the header was completely centered, although the header cell was starting from x=0. You only need to preserve the textAlignment="Center" setting in the <textElement/>.
But, if you really need to see 2 separate cells in the header when exporting to Excel, then I think that the JRCsvMetadataExporter is the solution for the CSV export in this case. Try to see how specific CSV export properties are used to export only the needed columns by running the jasper sample with the
>ant clean javac compile fill csvMetadata command.
All necessary properties are starting with the net.sf.jasperreports.export.csv prefix and you'll find them in the FirstJasper.jrxml sample.
Hope this helps,
sanda
Post Edited by shertage at 12/21/2011 10:07
Posted on September 14, 2015 at 6:28am
<staticText> <reportElement style="Arial_Italic" mode="Transparent" x="0" y="0" width="389" height="39" forecolor="#009999" backcolor="#009999"/> <textElement textAlignment="Center"> <font size="22" isBold="true" isItalic="true" isUnderline="false"/> </textElement> <text><![CDATA[User Report]]></text> </staticText> </td></tr></tbody></table>
where is the whitespace here?
