User Guide Tutorials ColdFusion tutorials Tutorial 06: How to protect cells and sheet elements in ColdFusion How to protect cells and sheet elements in ColdFusion
EasyXLS Excel library can be used to export Excel files in ColdFusion. The library works without MS Excel installed.
Step 1: Download EasyXLS Excel Library for Java To download the trial version of EasyXLS Excel Library, press the below button:
If you already own a license key, you may login and download EasyXLS from your account. Install the downloaded EasyXLS installer for v8.6 or earlier.
Step 2: Add EasyXLS library to CLASSPATH in ColdFusion Administator EasyXLS.jar must be included to class path for Java runtime environment. EasyXLS.jar can be found: - Inside the downloaded archive at Step 1 for EasyXLS v9.0 or later - Under installation path for EasyXLS v8.6 or earlier, in "Lib" folder. Another solution is to add EasyXLS.jar to "lib" folder from ColdFusion installation path.
Step 3: Run ColdFusion code that protects Excel sheet Execute the following ColdFusion code that exports data to Excel, protects Excel sheet and locks cells.
<cfobject type ="java" class ="EasyXLS.Constants.DataType" name ="DataType" action ="CREATE" >
<cfobject type ="java" class ="EasyXLS.Constants.Border" name ="Border" action ="CREATE" >
<cfobject type ="java" class ="EasyXLS.Constants.Alignment" name ="Alignment" action ="CREATE" >
<cfobject type ="java" class ="java.awt.Color" name ="Color" action ="CREATE" >
Tutorial 06<br>
----------<br>
<cfobject type ="java" class ="EasyXLS.ExcelDocument" name ="workbook" action ="CREATE" >
<cfset ret = workbook.easy_addWorksheet("First tab")>
<cfset ret = workbook.easy_addWorksheet("Second tab")>
<cfset workbook.easy_getSheetAt(0).setSheetProtected(true)>
<cfset xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()>
<cfobject type ="java" class ="EasyXLS.ExcelStyle" name ="xlsStyleHeader" action ="CREATE" >
<cfset xlsStyleHeader.setFont("Verdana")>
<cfset xlsStyleHeader.setFontSize(8)>
<cfset xlsStyleHeader.setItalic(true)>
<cfset xlsStyleHeader.setBold(true)>
<cfset xlsStyleHeader.setForeground(Color.yellow)>
<cfset xlsStyleHeader.setBackground(Color.black)>
<cfset xlsStyleHeader.setBorderColors(Color.gray, Color.gray, Color.gray, Color.gray)>
<cfset xlsStyleHeader.setBorderStyles(Border.BORDER_MEDIUM, Border.BORDER_MEDIUM,
Border.BORDER_MEDIUM, Border.BORDER_MEDIUM)>
<cfset xlsStyleHeader.setHorizontalAlignment(Alignment.ALIGNMENT_CENTER)>
<cfset xlsStyleHeader.setVerticalAlignment(Alignment.ALIGNMENT_BOTTOM)>
<cfset xlsStyleHeader.setWrap(true)>
<cfset xlsStyleHeader.setDataType(DataType.STRING)>
<cfloop from ="0" to ="4" index ="column" >
<cfset xlsFirstTable.easy_getCell(0, evaluate (column)).setValue("Column " & evaluate (column + 1))>
<cfset xlsFirstTable.easy_getCell(0, evaluate (column)).setStyle(xlsStyleHeader)>
</cfloop>
<cfset ret = xlsFirstTable.easy_getRowAt(0).setHeight(30)>
<cfobject type ="java" class ="EasyXLS.ExcelStyle" name ="xlsStyleData" action ="CREATE" >
<cfset xlsStyleData.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT)>
<cfset xlsStyleData.setForeground(Color.lightGray)>
<cfset xlsStyleData.setWrap(false)>
<cfset xlsStyleData.setLocked(true)>
<cfset xlsStyleData.setDataType(DataType.STRING)>
<cfloop from ="0" to ="99" index ="row" >
<cfloop from ="0" to ="4" index ="column" >
<cfset xlsFirstTable.easy_getCell(evaluate (row + 1), evaluate (column)).setValue(
"Data " & evaluate (row + 1) & ", " & evaluate (column + 1))>
<cfset xlsFirstTable.easy_getCell(evaluate (row + 1), evaluate (column)).setStyle(xlsStyleData)>
</cfloop>
</cfloop>
<cfset xlsFirstTable.setColumnWidth(0, 70)>
<cfset xlsFirstTable.setColumnWidth(1, 100)>
<cfset xlsFirstTable.setColumnWidth(2, 70)>
<cfset xlsFirstTable.setColumnWidth(3, 100)>
<cfset xlsFirstTable.setColumnWidth(4, 70)>
Writing file C:\Samples\Tutorial06 - protect Excel sheet.xlsx<br>
<cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Tutorial06 - protect Excel sheet.xlsx")>
<cfset sError = workbook.easy_getError()>
<cfif (sError is "")>
<cfoutput>
File successfully created.
</cfoutput>
<cfelse>
<cfoutput>
Error encountered: #sError#
</cfoutput>
</cfif>
<cfset workbook.Dispose()>