Q: We've been using your "Excel from RPG" stuff, and love it. But I've been unable to figure out a way to lock cells in a spreadsheet with POI! We're generating a form in which users can fill in some fields, but we don't want them to change others. How can we lock the cells against changes?
A: Locking cells in HSSF is easier than you might think. You first have to protect the sheet by calling the protectSheet() method of the HSSFSheet class and assigning a password that will be needed to unlock the sheet from within Excel. Once you've done that, call the setLocked() method for each cell style used in the sheet. For the cells you want to be locked, you have to create separate cell styles from the ones for the cells you want unlocked.
Introduction
HSSF is a set of Java classes for reading, writing, and manipulating Excel spreadsheets. Starting with IBM i release 5.1, RPG has been able to call Java routines directly from ILE RPG code, and therefore you can use the HSSF tools to create spreadsheets in RPG.
From this point on, I assume that you are already familiar with creating spreadsheets from RPG with HSSF. If you are not, use these links to earlier articles that explain the process:
Locking the Sheet
If you've ever locked a spreadsheet that you've created by hand in Excel, you know that you have to protect a sheet in two places. You have to designate each cell as either locked or unlocked, and then you have to go into Excel's Protection menu and select Protect this sheet. This protect option is like a master switch and enables or disables all the locked cells in the whole sheet.
In HSSF, the same type of scenario applies. The HSSFSheet object has a protectSheet() that acts as the master switch. If you want your cells to be protected, call it immediately after you've created the sheet object. For example, you might run the following code:
D hssfSheet_protectSheet...
D PR ExtProc( *Java
D : 'org.apache.poi.hssf-
D .usermodel.HSSFSheet'
D : 'protectSheet' )
D password like(jString) const
D password s like(jString)
.
.
sheet = hssf_newSheet(book: 'Lock Test');
password = new_String('secret');
hssfSheet_protectSheet(sheet: password);
In this example, the new_String() call creates a new Java string containing the word "secret." This will be the password that the user will need to unprotect the sheet. The word "secret" is just an example; you should change it to a password of your choosing.
The call to hssfSheet_protectSheet() specifies the Java sheet object, as well as the string containing the password. This enables protection on the sheet. To disable protection on the sheet, pass the special value *NULL instead of a password.
Locking the Cells
When you add a cell to a sheet that has been protected, you use cell style objects to control whether the individual cell is locked or unlocked. These are the same cell style objects that you use to control how a cell is formatted, which font is used, which colors are used, and so forth. Whether a cell is locked or unlocked is just another attribute of the cell style.
Here's an example of creating cell styles with the locked attribute turned on and off:
D hssfCellStyle_setLocked...
D PR ExtProc( *java
D : 'org.apache.poi.hssf-
D .usermodel.HSSFCellStyle'
D : 'setLocked' )
D locked 1n value
D locked s like(HSSFCellStyle)
D unlocked s like(HSSFCellStyle)
.
.
locked = HSSFWorkbook_createCellStyle(book);
// set other attributes like fonts, colors, etc here.
hssfCellStyle_setLocked(locked: *ON);
unlocked = HSSFWorkbook_createCellStyle(book);
// set other attributes like fonts, colors, etc here.
hssfCellStyle_setLocked(unlocked: *OFF);
Now that you've created the cell styles, you need to apply them only to the cells that you want to be locked or unlocked. You do this the same way you apply any other cell style—as described in previous articles.
Putting It All Together
To help you get started, here's a complete (albeit, very simple) program that creates four cells. The two leftmost cells (in column A) are locked and the user cannot change them. The cells in column B, however, are unlocked, and the user can change them.
H DFTACTGRP(*NO) BNDDIR('HSSF') OPTION(*SRCSTMT)
/copy hssf_h
D hssfSheet_protectSheet...
D PR ExtProc( *Java
D : 'org.apache.poi.hssf-
D .usermodel.HSSFSheet'
D : 'protectSheet' )
D password like(jString) const
D hssfCellStyle_setLocked...
D PR ExtProc( *java
D : 'org.apache.poi.hssf-
D .usermodel.HSSFCellStyle'
D : 'setLocked' )
D locked 1n value
D book s like(HSSFWorkbook)
D sheet s like(HSSFSheet)
D locked s like(HSSFCellStyle)
D unlocked s like(HSSFCellStyle)
D row s like(HSSFRow)
D password s like(jString)
/free
hssf_begin_object_group(100);
book = new_HSSFWorkbook();
sheet = hssf_newSheet(book: 'Lock Test');
password = new_String('secret');
hssfSheet_protectSheet(sheet: password);
locked = HSSFWorkbook_createCellStyle(book);
hssfCellStyle_setLocked(locked: *ON);
unlocked = HSSFWorkbook_createCellStyle(book);
hssfCellStyle_setLocked(unlocked: *OFF);
row = hssfSheet_createRow(sheet: 0);
hssf_text( row: 0: 'Name:': locked);
hssf_text( row: 1: '(change me)': unlocked);
row = hssfSheet_createRow(sheet: 1);
hssf_text( row: 0: 'Address:': locked);
hssf_text( row: 1: '(change me)': unlocked);
hssf_save(book: '/tmp/lockdemo.xls');
hssf_end_object_group();
*inlr = *on;
/end-free