CODEWITHSUNDEEP
javaexceljdbc
saifjunaid
saifjunaid

Reputation: 65

accessing excel as database with jdbc

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.

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.

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 

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.

Upvotes: 2

Views: 12479

Answers (1)

Michael Kazarian
Michael Kazarian

Reputation: 4462

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

  1. not database
  2. not network database Other words you must use other tools, other approach for your tasks.

Upvotes: 1

Related Questions