Java 受密码保护的 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2609301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Password Protected Excel File
提问by dhorn
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.
我有一个受密码保护的 Excel 电子表格。我需要打开这个电子表格并从中读取数据。我一直在尝试使用 POI API 无济于事。Java 解决方案将是首选，但任何想法都会有所帮助。
Edit: Yes, I have the password. The file is password protected in excel; a password must be entered to view the spreadsheet.
编辑：是的，我有密码。该文件在excel中受密码保护；必须输入密码才能查看电子表格。
Edit2: I am unable to open it with POI with the password, I am looking for an alternate solution.
Edit2：我无法使用密码打开它，我正在寻找替代解决方案。
采纳答案by Kevin Crowell
You can use JExcelApi.
您可以使用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:
我已经有一段时间没有这样做了，所以我可能不会告诉你如何正确地做到这一点，但肯定有一种方法可以使用 JExcelApi 做到这一点。试试下面的来源：
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.
当然，您需要导入必要的类并捕获必要的异常。
回答by Wajdy Essam
addthe excel file in ODBC Sources (from control panel->Administrative Tools) and then execute the code:
在 ODBC 源中添加 excel 文件（从控制面板-> 管理工具），然后执行代码：
// 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 ;
}
回答by Gagravarr
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?
POI 应该能够打开受保护的 xls 文件（使用org.apache.poi.hssf.record.crypt）和受保护的 xlsx 文件（使用org.apache.poi.poifs.crypt）。你试过这些吗？
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:
如果您使用 HSSF（用于 xls 文件），则需要在打开文件之前设置密码。您可以通过以下方式调用：
org.apache.poi.hssf.record.crypto.Biff8EncryptionKey.setCurrentUserPassword(password);
After that, HSSF should be able to open your file.
之后，HSSF 应该能够打开您的文件。
For XSSF, you want something like:
对于 XSSF，您需要以下内容：
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
POI 加密文档页面上提供了完整的详细信息
回答by stephen4amu
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.
我试图从 java 脚本设置 excel 文件的密码，这个脚本只能在 IE 上工作，Excel get 应该安装在客户端系统中。
<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>
