Locking / protecting a cell or column in JasperSoft Studio

Posted on April 15, 2015 at 6:41am

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?

 

Jaspersoft® Studio
reach_Saugata's picture
reach_Saugata
60
Joined: Apr 10 2015 - 1:48pm
Last seen: 8 years 5 months ago

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>
 
morlandin - 8 years 5 months ago
add comment

1 Answer:

Posted on April 15, 2015 at 9:00pm

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

It seems that once we have set password at the sheet level, all the cells are by default locked (on second thought, this seems obviousblush). 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>
reach_Saugata's picture
reach_Saugata
60
Joined: Apr 10 2015 - 1:48pm
Last seen: 8 years 5 months ago
Feedback