Locking / protecting a cell or column in JasperSoft Studio

[reach_Saugata]

I'm trying to lock a column/cell in an excel sheet that is created from Jasper Studio, 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.

<reportElement stretchType="RelativeToTallestObject" x="100" y="0" width="100" height="30" uuid="c356dd4b-5227-4917-b4ff-d2d4c72bafd0">

<property name="local_mesure_unitx" value="pixel"/>

<property name="com.jaspersoft.studio.unit.x" value="px"/>

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

</reportElement>

 

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?

 

[morlandin]

You must set the password property at report level and the locked property at element level. I've tested something like this and it works:

 

<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">

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

</reportElement>

<text><![CDATA[static Text]]></text>

</staticText>

</band>

</title>

</jasperReport>

 

[reach_Saugata]

Thanks @morlandin. You have started me thinking at the right direction.

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>