I have an excel spreadsheet that is password-protected. I need to open this spreadsheet and read the data from it. I've been attempting to use the POI API to no avail. A Java solution would be preferred but any ideas would be helpful.
Edit: Yes, I have the password. The file is password protected in excel; a password must be entered to view the spreadsheet.
Edit2: I am unable to open it with POI with the password, I am looking for an alternate solution.
POI should be able to open both protected xls files (using org.apache.poi.hssf.record.crypt) and protected xlsx files (using org.apache.poi.poifs.crypt). Have you tried these?
If you're using HSSF (for a xls file), you need to set the password before opening the file. You do this with a call to:
org.apache.poi.hssf.record.crypto.Biff8EncryptionKey.setCurrentUserPassword(password);
After that, HSSF should be able to open your file.
For XSSF, you want something like:
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("protect.xlsx"));
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor d = Decryptor.getInstance(info);
d.verifyPassword(Decryptor.DEFAULT_PASSWORD);
XSSFWorkbook wb = new XSSFWorkbook(d.getDataStream(fs));
Full details are given on the POI Encryption documentation page
I tried to set password for excel file from java script, this script will only work on IE and Excel get should installed in the client system.
<script>
function setPasswordToExcel(password,excelFileName,newFileName)
{
var Excel;
Excel = new ActiveXObject("Excel.Application");
Excel.Visible = false;
var obj = Excel.Workbooks.Open(excelFileName);
obj.Password =password;
obj.SaveAs(newFileName);
obj.Close();
Excel.Close();
return 1;
}
setPasswordToExcel("stephen","C:/test1.xls","C:\\test2.xls");
</script>
addthe excel file in ODBC Sources (from control panel->Administrative Tools) and then execute the code:
// program to extract data from excel file
import java.sql.Connection ;
import java.sql.Statement ;
import java.sql.ResultSet ;
import java.sql.ResultSetMetaData ;
import java.sql.DriverManager ;
import java.sql.SQLException ;
public class ExtractExcelData {
public static void main (String[] args) {
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL,userName,password);
}
catch (ClassNotFoundException cnfe) {
System.err.println("unable to load excel driver");
return ;
}
catch (SQLException se) {
System.err.println("cannot connect to excel file");
return ;
}
try {
statement = connection.createStatement();
String select = "SELECT * FROM [Sheet1$]";
resultSet = statement.executeQuery(select);
metaData = resultSet.getMetaData();
int count = metaData.getColumnCount();
while ( resultSet.next() ) {
String col1 = resultSet.getString(1) ;
String col2 = resultSet.getString(2) ;
String col3 = resultSet.getString(3) ;
System.out.println( col1 ) ;
System.out.println( col2 ) ;
System.out.println( col3 ) ;
System.out.println();
}
}
catch (SQLException se) {
System.err.println("cannot execute query");
return ;
}
try {
statement.close();
resultSet.close();
}
catch (SQLException se ) {
System.err.println("unable to close excel file");
return ;
}
}
private static final String userName = "" ;
private static final String password = "" ;
private static final String URL = "jdbc:odbc:testexcel" ;
private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver" ;
private static Connection connection ;
private static Statement statement ;
private static ResultSet resultSet ;
private static ResultSetMetaData metaData ;
}
You can use JExcelApi.
It has been a while since I have done this, so I may not be telling you how to do it correctly, but there is definitely a way to do this using JExcelApi. Try the source below:
Workbook workbook = Workbook.getWorkbook(new File("/path/to/protected.xls"));
workbook.setProtected(false);
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();
Of course, you will need to import necessary classes and catch necessary exceptions.
