Locking / protecting a cell or column in Jasper iReport

Question

I'm trying to lock a column/cell in an excel sheet that is created from Jasper iReport, because our client doesn't want the user to be able to update the formula. I haven't been able to find much on this.

I added the locked property that I found in the config reference (http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.cell.locked), although it does not prevent the cells in the column from being edited on the exported sheet.

<jr:column width="100">
    <jr:detailCell style="table" height="20" rowSpan="1">
        <textField>
            <reportElement style="table" x="0" y="0" width="100" height="20">
                <property name="net.sf.jasperreports.export.xls.formula" value="INDIRECT(ADDRESS(ROW(),4))"/>
                <property name="net.sf.jasperreports.export.xls.cell.locked" value="true"/>
            </reportElement>
        </textField>
    </jr:detailCell>
</jr:column>

The config reference also says the setting "only has effect if the enclosing sheet is protected". I added a password to the sheet with the password property, but this causes the entire sheet to be protected.

<property name="net.sf.jasperreports.export.xls.password" value="password" />

With the whole sheet protected, I tried adding the locked property set to false for the fields that need to be updatable. But I still couldn't update them since the entire sheet was protected.

Any ideas on how to protect some cells or columns and not others?

Answer 1

I was facing the same issue yesterday in jaspersoft studio. May be I am too late, and you already have solved this on your own. In that case, just to help others like us, here is the way to do it.

It seems that once we have set password at the sheet level, all the cells are by default locked (on second thought, this seems obvious). So, if we want to unlock a cell we have to mention explicitly.

<property name="net.sf.jasperreports.export.xls.cell.locked" value="false"/>

So, the below code will produce an excel sheet where "Static Text" cell will be locked and "Static Text22" cell will be unlocked. Hope this example will help others and they will not have to go round and round at it like me.

<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="Blank_A4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="3bff4add-3bc7-49b8-acd4-0ec822191576">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
<property name="net.sf.jasperreports.export.xls.password" value="test_password"/>
<queryString>
<![CDATA[]]>
</queryString>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="79" splitType="Stretch">
<staticText>
<reportElement x="123" y="2" width="100" height="30" uuid="c9f7abf8-2ed4-44c3-abf5-84fda5d190f4"/>
<text><![CDATA[Static Text]]></text>
</staticText>
<staticText>
<reportElement x="223" y="2" width="100" height="30" uuid="d95b84d7-5d1b-4919-b4f0-82a30c9effd6">
<property name="net.sf.jasperreports.export.xls.cell.locked" value="false"/>
</reportElement>
<text><![CDATA[Static Text22]]></text>
</staticText>
</band>
</title>
</jasperReport>