Hot to set the color of worksheet tab at generated Excel file?

Question

I have been using JasperReports version 6.4.3. I want to show the Excel tab color red.

Can I do this in above version?

Answer 1

You can use net.sf.jasperreports.export.xls.sheet.tab.color report's property to change color of tab at generated Excel file.

The working example - using static color

In this example the Collection based datasource passed via parameter is using.

<?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="Color tabs" pageWidth="290" pageHeight="60" whenNoDataType="AllSectionsNoDetail" columnWidth="290" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
    <property name="net.sf.jasperreports.export.xls.sheet.tab.color" value="#00FF00"/>
    <subDataset name="mainDataset">
        <field name="value" class="java.lang.String">
            <fieldDescription><![CDATA[_THIS]]></fieldDescription>
        </field>
    </subDataset>
    <parameter name="DATA" class="java.util.Collection" isForPrompting="false">
        <defaultValueExpression><![CDATA[Arrays.asList("Row 1", "Row 2",  "Row 3",  "Row 4",  "Row 5",  "Row 6",  "Row 7",  "Row 8",  "Row 9",  "Row 10")]]></defaultValueExpression>
    </parameter>
    <title>
        <band height="20">
            <componentElement>
                <reportElement x="0" y="0" width="290" height="20"/>
                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
                    <datasetRun subDataset="mainDataset">
                        <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource($P{DATA})]]></dataSourceExpression>
                    </datasetRun>
                    <jr:listContents height="20" width="290">
                        <textField>
                            <reportElement x="0" y="0" width="290" height="20"/>
                            <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </title>
</jasperReport>

The first property net.sf.jasperreports.export.xls.one.page.per.sheet is using to force JasperReports engine generates new tab for each new page. I set report's sizes to be able show 3 rows only per page. It means that the tab can show only 3 rows.

The second property net.sf.jasperreports.export.xls.sheet.tab.color is using to show all tabs in red color.

The generated result - using static color

The report generated at Jaspersoft Studio (JSS) looks like this:

The working example - using dynamic color

We can change the color dynamically - in this case we can use the expression for the same net.sf.jasperreports.export.xls.sheet.tab.color property.

<?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="Color tabs - Dynamic color" pageWidth="290" pageHeight="60" whenNoDataType="AllSectionsNoDetail" columnWidth="290" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
    <subDataset name="mainDataset">
        <field name="value" class="java.lang.String">
            <fieldDescription><![CDATA[_THIS]]></fieldDescription>
        </field>
    </subDataset>
    <parameter name="DATA" class="java.util.Collection" isForPrompting="false">
        <defaultValueExpression><![CDATA[Arrays.asList("Row 1", "Row 2",  "Row 3",  "Row 4",  "Row 5",  "Row 6",  "Row 7",  "Row 8",  "Row 9",  "Row 10")]]></defaultValueExpression>
    </parameter>
    <title>
        <band height="20">
            <componentElement>
                <reportElement x="0" y="0" width="290" height="20"/>
                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
                    <datasetRun subDataset="mainDataset">
                        <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource($P{DATA})]]></dataSourceExpression>
                    </datasetRun>
                    <jr:listContents height="20" width="290">
                        <textField>
                            <reportElement x="0" y="0" width="220">
                                <propertyExpression name="net.sf.jasperreports.export.xls.sheet.tab.color"><![CDATA[Arrays.asList("Row 3", "Row 6").contains($F{value})  ? "#E50000" : "#00FF00"]]></propertyExpression>
                            </reportElement>
                            <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </title>
</jasperReport>

It is harder to change property in this case - the value of last element generated on page is using to change color of tab. In this example I'm using this expression Arrays.asList("Row 3", "Row 6").contains($F{value}) ? "#E50000" : "#00FF00" to check third (last at page) record (field) at each page and set color with help of this last textFields.

The generated result - using dynamic color

The report generated at JSS looks like this:

More information

A lot of samples can be found at JasperReports - Advanced Excel Features samples