Java 使用 jdbc 访问 excel 作为数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21478459/
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
accessing excel as database with jdbc
提问by saifjunaid
I have to design an application for accessing excel application as database. My problem is I have to create multiple connection for each transaction and if I miss any of closing it, the excel is not being update.
我必须设计一个应用程序来访问 excel 应用程序作为数据库。我的问题是我必须为每个事务创建多个连接，如果我错过了关闭它的任何一个，excel 不会被更新。
I want to design pattern where i am able to access the excel. Any one help me in designing a common pattern through which i wont be having problem. I want something like this, but we are not able to use it to access excel.
我想设计一种可以访问 excel 的模式。任何人都可以帮助我设计一种通用模式，通过该模式我不会遇到问题。我想是这样，但我们不能用它来访问Excel。
Thanks in advance!
提前致谢！
i have this method in utility class
我在实用程序类中有这个方法
static ResultSet getExcelData(String filePath,String sqlQuery){
ResultSet rs=null;
try{
conn = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="+filePath+";READONLY=false");
stmt= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs=stmt.executeQuery( sqlQuery );
}catch (Exception e) {
e.printStackTrace();
return null;
// TODO: handle exception
}finally{
}
return rs;
}
and i am calling it this way
我这样称呼它
ResultSet rs=JdbcUtil.getExcelData("D:\AB_demo\AB_demo\test.xls", "Select max(int(ID)) from [MAIN$] where HEADER_IND is not Null AND int(ID)<"+excelId);
int databaseId = 0;
if(rs.next())
{
databaseId=rs.getInt(1);
}
ResultSet rs1=JdbcUtil.getExcelData("D:\AB_demo\AB_demo\test.xls", "SELECT * from [MAIN$] where id= '"+databaseId+"'or id='"+excelId+"'");
i am calling this method twice after which im updating the excel file by using
我两次调用此方法，然后我使用更新 excel 文件
stmt.executeUpdate(sql);
its returning the integer 1 but its not reflecting in excel.when i use process explorer the file is still in use.i need a design pattern or come code to overcome his kind of problem.
它返回整数 1 但它没有反映在 excel 中。当我使用进程资源管理器时，文件仍在使用中。我需要一个设计模式或代码来克服他的那种问题。
回答by Michael Kazarian
I think more right way is generate Excel file from database. Otherwise you must create server side for ensure transactions and connections control. Main problem of your task - Excel is
我认为更正确的方法是从数据库生成 Excel 文件。否则，您必须创建服务器端以确保事务和连接控制。您的任务的主要问题 - Excel 是
- not database
- not network database Other words you must use other tools, other approach for your tasks.
- 不是数据库
- 不是网络数据库 换句话说，您必须使用其他工具，其他方法来完成您的任务。
