Connect to Excel worksheet using jdbc without specifying DSN string in Excel

Question

I want to connect to an excel sheet using jdbc or some other method, but I do not want to specify a DSN for this using the administration tool. Can they do this with code? If so, how?

Thanks in advance

Answer 1

It is also possible to connect to a spreadsheet without using a DSN, which provides a more flexible way to specify JDBC in the Excel file of interest without accessing the client registry to determine the required DSN inside the code. Without a DSN, a db connection is created as follows, please do not distinguish the constructed JDBC URL:

java.sql.DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls"); 

Here DBQ defines the path to the target spreadsheet file (qa.xls). Both the backslash and the slash work well.

Source: Available Source

Answer 2

What you are avoiding is a connection string not related to DSN. See http://support.microsoft.com/kb/165866 for details. However, I would choose the Apache POI, as Jayan 14 mentioned.

Answer 3

try changing the driver name from Microsoft Excel Driver(*.xls) to do Microsoft Excel(*.xls) driver Microsoft Excel(*.xls)

java.sql.DriverManager.getConnection("jdbc:odbc:Driver={Driver do Microsoft Excel(*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls");

and if you want to update the excel file, use the following connection string:

java.sql.DriverManager.getConnection("jdbc:odbc:Driver={Driver do Microsoft Excel(*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls;ReadOnly=0");