 |
|
Microsoft Excel allows users to password protect spreadsheets in a workbook. Protected spreadsheets can only be modified by entering the password (selected by the person who protected the spreadsheet).
Sometimes these passwords are lost, and we are left without the ability to modify said spreadsheets. There are utilities that can be used to either unlock or recover the password, but sometimes these tools are not available. Luckily, with a little bit of programming expertise and the help of a Java library called JExcelApi, we can fairly easily unlock an Excel spreadsheet.
JExcelApi is an open source Java library to read, write and manipulate Microsoft Excel spreadsheets. JExcelApi is distributed under the GNU Lesser General Public License (LGPL). With the help of JExcelApi, we can load a password protected Microsoft Excel spreadsheet into memory, unprotect any protected sheets, and write back an unprotected copy back to disk. The following code example illustrates how to do this.
package net.ensode.jexcelapitest;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class JExcelApiTest
{
public static void main(String[] args)
{
try
{
Workbook workbook = Workbook.
getWorkbook(new File("/path/to/protected.xls"));
WritableWorkbook copy = Workbook.
createWorkbook(new File("/path/to/unprotected.xls"), workbook);
WritableSheet[] sheets = copy.getSheets();
for (WritableSheet sheet : sheets)
{
sheet.getSettings().setProtected(false);
}
copy.write();
copy.close();
}
catch (BiffException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
catch (WriteException e)
{
e.printStackTrace();
}
}
}
As can be seen in the above example, we can read an existing
Excel spreadsheet into memory by calling the Workbook.getWorkbook().
method. This method takes an instance of java.io.File as
its only parameter, and returns an instance of the jxl.Workbook
class, this object contains an in-memory representation of the
spreadsheet.
Once we have an in-memory representation of the Excel
spreadsheet, we can create an in-memory copy of it by calling the Workbook.createWorkbook()
method, passing an instance of java.io.File containing the
location where we want to write the unlocked spreadsheet, and the
instance of jxl.Workbook we obtained earlier as parameters.
This method call will return an instance of jxl.write.WritableWorkbook,
wich is basically a modifiable in-memory representation of the Excel
spreadsheet.
To unlock or unprotect any password protected sheets in the
workbook, we call the WriteableWorkbook.getSheets() method.
This method will return an array of jxl.write.WritableSheet
objects. These objects represent sheets in the workbook. To unprotect
any protected sheets, we traverse the array (note: the example code
above uses the enhanced for loop introduced in JDK 1.5, it will not
compile under earlier versions of the JDK) and call the getSettings.getProtected(false)
on each element of the array. This will effectively unprotect any
protected sheets in the workbook. Finally, we write the unprotected
workbook to disk by calling the WriteableWorkbook.write()
method, and close the workbook by invoking its close()
method.
As can be seen in this article, unprotecting password protected APIs is an almost trivial exercise with the help of JExcelApi.
