accessing excel as database with jdbc

Question

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.

Why not use access as a database as excel isn't specifically meant to be used as a database — Liam Sorsby
– Liam Sorsby, Commented Jan 31, 2014 at 11:08
i am basically using it with testng where i have to read the class and the data in dictionary and run it through testng java code.and update the data if the test have passed or failed based on the result.i have code.google.com/p/excel-testng but my requirement is accessing excel as a database. — saifjunaid
– saifjunaid, Commented Jan 31, 2014 at 11:09
Firstly you need to update your post with what you have tried however, as another post has specified, excel is the second worst option for a database. raw csv being the first. — Liam Sorsby
– Liam Sorsby, Commented Jan 31, 2014 at 11:16
to access the file try. Control panel, administrative tools, System DSN and then setup the access file in there. Once setup in your java instead of using the path to the database just use DSN name that you setup in your dsn. — Liam Sorsby
– Liam Sorsby, Commented Jan 31, 2014 at 11:18

Michael Kazarian · Accepted Answer · 2014-01-31 15:03:14Z

1

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

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

answered Jan 31, 2014 at 15:03

Michael Kazarian

4,4921 gold badge23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

accessing excel as database with jdbc

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related