CODEWITHSUNDEEP
jasper-reportsexport-to-excelxlsx
quento
quento

Reputation: 1114

Why is style ignored in XLSX when using markup?

I am using bold style for part of the text, so it looks like:

"<style isBold = 'true'>" + $P{REPORT_RESOURCE_BUNDLE}.getString("report.label.foo") +": "+"</style>"+$F{foo}

in jrxml this textField looks like:

<textField>
<reportElement style="moduleBorderColumnStyle" mode="Opaque" x="0" y="0" width="555" height="20" uuid="6adbbfa7-e549-4378-903c-04095c2f34c4"/>
<textElement markup="styled"/>
<textFieldExpression><![CDATA["<style isBold = 'true'>" +
$P{REPORT_RESOURCE_BUNDLE}
.getString("report.label.foo")
+": "+"</style>"+$F{foo}]]></textFieldExpression>
</textField>

TextField Markup property - styled

It works perfect for PDF and HTML. However, I have problems with using same for XLSX.

Unfortunately, even after setting directly font size to 14 (I've tried to set it from style before) I am getting font 11 callibri (It's default font) for whole label, which is using tag <style isBold='true'>.

I've tried the same with <b> text </b> and markup = HTML - result did not changed.

Conclusion: Any styled text in XLSX is insensitive for fonts (sets it to default), how can this be resolved?

EDIT:

I found that the problem is in style which I am applying for this textField before, however still problem is just in excel. The <style> tag just overwrites it to default font and font size.

Upvotes: 3

Views: 1953

Answers (1)

Petter Friberg
Petter Friberg

Reputation: 21710

I can confirm same bug when exporting to xlsx, style is ignored, it seems related to creating the RichTextString for the cell in the XSSFSheet. (incorrect/no font is set to RichTextString?)

EDIT: I have created a bug issue, that is marked as solved for next release (current release was v6.3.0)

Simple example to reproduce bug

jrxml (SimpleTest.jrxml)

<?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 http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="SimpleTest" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="e4188d8a-c7f9-4f7d-8f0f-ada07b89d42f">
    <style name="test" mode="Transparent" forecolor="#CC0000" fontSize="14"/>
    <detail>
        <band height="20">
            <textField isStretchWithOverflow="true">
                <reportElement style="test" positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="200" height="20" uuid="6d5644bf-480e-4ed2-831b-3ed043f38f70"/>
                <textElement verticalAlignment="Middle" markup="html">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA["<b>TEST</b> TEXT"]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement style="test" positionType="Float" stretchType="RelativeToTallestObject" x="200" y="0" width="200" height="20" uuid="f876d0a3-136b-468c-b3bd-bd9cd5475ca9"/>
                <textElement verticalAlignment="Middle" markup="html">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA["TEXT2"]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

java code to export to xls and xlsx

JasperReport report = JasperCompileManager.compileReport("SimpleTest.jrxml");
JasperPrint jasperPrint = JasperFillManager.fillReport(report,new HashMap<String, Object>(), new JREmptyDataSource(1));

//Export to excel xls
JRXlsExporter exporterXls = new JRXlsExporter();
File outputFile = new File("excelTest.xls");
exporterXls.setExporterInput(new SimpleExporterInput(jasperPrint));
exporterXls.setExporterOutput(new SimpleOutputStreamExporterOutput(outputFile));
SimpleXlsReportConfiguration configXls = new SimpleXlsReportConfiguration();
configXls.setDetectCellType(true);
configXls.setRemoveEmptySpaceBetweenColumns(true);
configXls.setRemoveEmptySpaceBetweenRows(true);
configXls.setCollapseRowSpan(true);
configXls.setWhitePageBackground(false);
exporterXls.setConfiguration(configXls);
exporterXls.exportReport();

//Export to excel xlsx
JRXlsxExporter exporterXlsx = new JRXlsxExporter();
File output = new File("excelTest.xlsx");
exporterXlsx.setExporterInput(new SimpleExporterInput(jasperPrint));
exporterXlsx.setExporterOutput(new SimpleOutputStreamExporterOutput(output));
SimpleXlsxReportConfiguration configXlsx = new SimpleXlsxReportConfiguration();
configXlsx.setDetectCellType(true);
configXlsx.setRemoveEmptySpaceBetweenColumns(true);
configXlsx.setRemoveEmptySpaceBetweenRows(true);
configXlsx.setCollapseRowSpan(true);
configXlsx.setWhitePageBackground(false);
exporterXlsx.setConfiguration(configXlsx);
exporterXlsx.exportReport();

Output xls (left), xlsx (right)

result

cell A1 in xlsx the style is not applied

Work around

Do not use style on the textField, hence apply the style directly to the textField.

In example we add the forecolor="#CC0000" and fontSize="14" to textField and remove the style attribute

<textField isStretchWithOverflow="true">
    <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="200" height="20" forecolor="#CC0000" uuid="6d5644bf-480e-4ed2-831b-3ed043f38f70"/>
    <textElement verticalAlignment="Middle" markup="html">
        <font size="14"/>
        <paragraph lineSpacing="Single"/>
    </textElement>
    <textFieldExpression><![CDATA["<b>TEST</b> TEXT"]]></textFieldExpression>
</textField>

Upvotes: 1

Related Questions