Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Export Access Database To Excel File
- Thread starter JDOne
- Start date
You can read Access Databases using JDBC via the JDBC to ODBC bridge driver. Then you can build Excel files using the Apache POI project classes.
JDBC tutorials can be found at java.sun.com
The POI home is here:
-pete
JDBC tutorials can be found at java.sun.com
The POI home is here:
-pete
.... but if you dont fancy using POI u could try it totally javaside with this lovely set of classes and a JDBC bridge:
Hope that helps :0)
matt
Hope that helps :0)
matt
![[bugeyed]](/data/assets/smilies/bugeyed.gif "[bugeyed] [bugeyed]")
- Thread starter
- #6
Here is an example using the POI API ( to take the data from the result set and put them into a excel sheet.
The code below uses the following table:
CompanyInfomration
CompanyID
CompanyName
CompanyAddress
To use the sample code below...download the POI API, add the jar file to your classpath. This is just a sample of what you can do. If you prefer a easier way, create a csv file from the results and give the ".csv" ext to the file. Excel will open the file and create the columns itself.
***** Code *****
The code below uses the following table:
CompanyInfomration
CompanyID
CompanyName
CompanyAddress
To use the sample code below...download the POI API, add the jar file to your classpath. This is just a sample of what you can do. If you prefer a easier way, create a csv file from the results and give the ".csv" ext to the file. Excel will open the file and create the columns itself.
***** Code *****
Code:
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.util.HSSFColor;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.io.*;
public class AccessDbToExcelFile
{
public static void main(String[] args)
{
HSSFCellStyle style = null;
HSSFWorkbook wb = null;
FileOutputStream fileOut = null;
HSSFSheet sheet1 = null;
HSSFRow row = null;
HSSFCell cell = null;
ResultSet rs = null;
Statement stmt = null;
String excelFileName = "C:\\SampleAccessToExcel.xls";
try
{
//Creating a new Excel Workbook
wb = new HSSFWorkbook();
fileOut = new FileOutputStream(excelFileName);
System.out.println ("Created Excel Workbook: "+ excelFileName);
//Creating a new sheet1 inside the workbook
wb = new HSSFWorkbook();
sheet1 = wb.createSheet("Data From AccessDB");
System.out.println("Finshed creating a sheet1 within the workbook");
}
catch (FileNotFoundException fnfe)
{
System.out.println ("FileNotFoundException " + fnfe);
}
//Creating a connection object
Connection aConnection = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//make sure that this is a space between "MicroSoft Access Driver" and "(*.mdb)"
aConnection = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C://TestDB.mdb","","");
stmt = aConnection.createStatement();
String query = "SELECT CompanyInformation.CompanyID, CompanyInformation.CompanyName," +
"CompanyInformation.StreetAddress" +
" FROM CompanyInformation;";
rs = stmt.executeQuery (query);
//Creating a Row for columns headings - POI starts with row 0
row = sheet1.createRow((short)0);
//Get column headings and print them in Excel - on the first row
ResultSetMetaData meta = rs.getMetaData();
int columnNumber = 0;
for (int i = 1; i <= meta.getColumnCount(); i++)
{
String columnName = meta.getColumnName(i);
//Places the column name in the cell - cell are base 0
cell = row.createCell((short)columnNumber);
cell.setCellValue(columnName);
System.out.println(columnName);
columnNumber++;
//Adding some color to the cell
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.TAN.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);
}
//Taking the resultset and printing it to excel.
int rowNumber = 1;
int stringLenght = 1;
while (rs.next())
{
//Used to keep track of the column numbers
int columnCount = 0;
//Creating a new row
row = sheet1.createRow((short)rowNumber);
int companyID = rs.getInt("CompanyID");
//Creating a new cell
cell = row.createCell((short)columnCount);
cell.setCellValue(companyID);
columnCount++;
String CompanyName = rs.getString("CompanyName");
cell = row.createCell((short)columnCount);
columnCount++;
String StreetAddress = rs.getString("StreetAddress");
cell = row.createCell((short)columnCount);
rowNumber++;
}
System.out.println("Added " + rowNumber + " new row to Excel Workbook: " + excelFileName);
}
catch (ClassNotFoundException cnfe)
{
System.out.println("ClassNotFoundException " + cnfe);
}
catch (Exception ex)
{
System.out.println ("**General Exception**");
ex.printStackTrace();
}
finally
{
try
{
rs.close();
aConnection.close();
stmt.close();
wb.write(fileOut);
fileOut.close();
}
catch (IOException ioe)
{
System.out.println("IOException: " + ioe);
}
catch(SQLException sqlE)
{
System.out.println("SQLException " + sqlE);
}
}
}
}- Thread starter
- #9
Thanks - that's all very helpful.
Pete, what I meant was isn't there a more simpler way of writing the results of a SQL query to an xls file without including the POI API and having to build a workbook - like just writing the data into a file, in this case, an xls file, using file io? Was planning on trying this method first - soon as I catch up on my workload - but was just wondering if you or anyone has ever done it that way.
-J
Pete, what I meant was isn't there a more simpler way of writing the results of a SQL query to an xls file without including the POI API and having to build a workbook - like just writing the data into a file, in this case, an xls file, using file io? Was planning on trying this method first - soon as I catch up on my workload - but was just wondering if you or anyone has ever done it that way.
-J
The problem is an xls file is not just a simple file. Each excel file is in a binary file format, which is why the API is used (to take care of the low level details). Of course of can write our own java code to produce this a excel file...however you need to know the file format of the xls files...your can read more about that at I have not yet found another (free) way to do this yet.
There are other options... In your requirements...do you just have to produce a simple xls file (mean no color..or formating?)... If your user are using office 2000 you can always create a HTML doc...and have them open it in excel...
There are other options... In your requirements...do you just have to produce a simple xls file (mean no color..or formating?)... If your user are using office 2000 you can always create a HTML doc...and have them open it in excel...
- Thread starter
- #11
Ok, well I just though of a simple way to do what you want to do. Create a JDBC-ODBC connection to the spreadsheet and insert the rows from the
that way. I provided sample code below, if needed. I would create a master spreadsheet (Template) and copy that file for daily use. That way go can formatting (and make it pretty for the users 
)....Also here are some link to websites that will provide some more insight into JDBC-ODBC Excel connections:
<<Sorry about the code sloppy...was in a rush>>
Excel ODBC Driver Limitations - (Microsoft)
Artical about how to use this with Java - JavaWorld.com
Let me know if this does not help.
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
/**
* This class does the following:
*
* 1.Creates a copy of the template file for daily use. If the file has been already been
* created, then the same copy will be use and the new data will simply be added to
* the spreadsheet. If the file does not exist a new copy will be made and saved
* in a directory and the data from the result set will be copied there.
*
* 2.It take the data from the database and will create a excel file.
*
*/
public class CreateExcelFileWithJdbcOdbc
{
//Get the line line character for the OS
static String newLine = System.getProperty("line.separator"
static ResultSet accessResultset = null;
/**
* Create a copy of a file
*/
private static String copyFile()
{
File outputFile = null;
File inputFile = null;
try
{
inputFile = new File("C:\\SampleTemplateTest.xls"
outputFile = new File("C:\\DailyReport\\SomeReport_" + getTodaysDate()+".xls"
if (outputFile.exists())
{
System.out.println(newLine + "File " + outputFile.getName() +
" already exists, found file " +
"in directory " + outputFile.getParent() +
". Returning this file."+
newLine + "NEW FILE NOT CREATED; Process: copyFile() halted"
+newLine);
}
else
{
FileInputStream in = new FileInputStream(inputFile);
FileOutputStream out = new FileOutputStream(outputFile);
int c;
while ((c = in.read()) != -1)
out.write(c);
in.close();
out.close();
System.out.println(newLine + "Created Excel file: " + outputFile.getName() +
" in Directory " + outputFile.getParent() + newLine);
}
}
//Done to save space
catch (Exception e)
{
System.out.println(e);
System.exit(2);
}
return outputFile.toString();
}
/**
* Get todays day in Month Day Year format, for example: 05282003
*/
public static String getTodaysDate()
{
Date today = new Date();
DateFormat formater = new SimpleDateFormat("MMddyyyy"
//only accept exact values
formater.setLenient(true);
String todayDate = formater.format(today);
return todayDate;
}
/**
* Gets Data from an Access Database, and populates the excel sheet.
*/
public static void getDataFromAccessDB(String file)
{
Statement stmt;
Statement aStmt;
String excelFile = file;
ResultSet accessResultset = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"
Connection accessConnection = null;
//creating a DSN dynamically: <-- This is process will slow down the app (slightly)
accessConnection = DriverManager.getConnection("jdbcdbcriver={MicroSoft Access "+
"Driver (*.mdb)};DBQ=C://TestDB.mdb","",""
stmt = accessConnection.createStatement();
String query = "SELECT CompanyID, CompanyName," +
"StreetAddress" +
" FROM CompanyInformation;";
accessResultset = stmt.executeQuery (query);
Connection excelConnection = null;
PreparedStatement putStmt = null;
//creating a DSN dynamically:<-- This is process will slow down the app (slightly)
excelConnection = DriverManager.getConnection("jdbcdbcriver={Microsoft Excel" +
" Driver (*.xls)};DBQ="+ excelFile +
";DriverID=22;READONLY=false","",""
String insertSql = ("INSERT INTO [Sheet1$] values (?, ?, ?)"
aStmt = excelConnection.createStatement();
putStmt = excelConnection.prepareStatement(insertSql);
int rowsUpdated = 0;
while (accessResultset.next())
{
putStmt.clearParameters();
putStmt.setInt(1,accessResultset.getInt("CompanyID");
putStmt.setString(2,accessResultset.getString("CompanyName");
putStmt.setString(3,accessResultset.getString("StreetAddress");
rowsUpdated = rowsUpdated + putStmt.executeUpdate();
}
System.out.println ("Updated " + rowsUpdated + "rows"
accessResultset.close();
accessConnection.close();
excelConnection.close();
aStmt.close();
stmt.close();
}
catch (ClassNotFoundException cnfe)
{
System.out.println("Error: ClassNotFoundException - " + cnfe);
}
catch(SQLException sqlE)
{
System.err.println("Error: SQLException - " + sqlE);
}
}
public static void main(String args[])
{
String excelFileName = copyFile();
getDataFromAccessDB(excelFileName);
}
}
Code:
ResultSet<<Sorry about the code sloppy...was in a rush>>
Excel ODBC Driver Limitations - (Microsoft)
Artical about how to use this with Java - JavaWorld.com
Let me know if this does not help.
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
/**
* This class does the following:
*
* 1.Creates a copy of the template file for daily use. If the file has been already been
* created, then the same copy will be use and the new data will simply be added to
* the spreadsheet. If the file does not exist a new copy will be made and saved
* in a directory and the data from the result set will be copied there.
*
* 2.It take the data from the database and will create a excel file.
*
*/
public class CreateExcelFileWithJdbcOdbc
{
//Get the line line character for the OS
static String newLine = System.getProperty("line.separator"
;static ResultSet accessResultset = null;
/**
* Create a copy of a file
*/
private static String copyFile()
{
File outputFile = null;
File inputFile = null;
try
{
inputFile = new File("C:\\SampleTemplateTest.xls"
;outputFile = new File("C:\\DailyReport\\SomeReport_" + getTodaysDate()+".xls"
;if (outputFile.exists())
{
System.out.println(newLine + "File " + outputFile.getName() +
" already exists, found file " +
"in directory " + outputFile.getParent() +
". Returning this file."+
newLine + "NEW FILE NOT CREATED; Process: copyFile() halted"
+newLine);
}
else
{
FileInputStream in = new FileInputStream(inputFile);
FileOutputStream out = new FileOutputStream(outputFile);
int c;
while ((c = in.read()) != -1)
out.write(c);
in.close();
out.close();
System.out.println(newLine + "Created Excel file: " + outputFile.getName() +
" in Directory " + outputFile.getParent() + newLine);
}
}
//Done to save space
catch (Exception e)
{
System.out.println(e);
System.exit(2);
}
return outputFile.toString();
}
/**
* Get todays day in Month Day Year format, for example: 05282003
*/
public static String getTodaysDate()
{
Date today = new Date();
DateFormat formater = new SimpleDateFormat("MMddyyyy"
;//only accept exact values
formater.setLenient(true);
String todayDate = formater.format(today);
return todayDate;
}
/**
* Gets Data from an Access Database, and populates the excel sheet.
*/
public static void getDataFromAccessDB(String file)
{
Statement stmt;
Statement aStmt;
String excelFile = file;
ResultSet accessResultset = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"
;Connection accessConnection = null;
//creating a DSN dynamically: <-- This is process will slow down the app (slightly)
accessConnection = DriverManager.getConnection("jdbc
dbcriver={MicroSoft Access "+"Driver (*.mdb)};DBQ=C://TestDB.mdb","",""
;stmt = accessConnection.createStatement();
String query = "SELECT CompanyID, CompanyName," +
"StreetAddress" +
" FROM CompanyInformation;";
accessResultset = stmt.executeQuery (query);
Connection excelConnection = null;
PreparedStatement putStmt = null;
//creating a DSN dynamically:<-- This is process will slow down the app (slightly)
excelConnection = DriverManager.getConnection("jdbc
dbcriver={Microsoft Excel" +" Driver (*.xls)};DBQ="+ excelFile +
";DriverID=22;READONLY=false","",""
;String insertSql = ("INSERT INTO [Sheet1$] values (?, ?, ?)"
;aStmt = excelConnection.createStatement();
putStmt = excelConnection.prepareStatement(insertSql);
int rowsUpdated = 0;
while (accessResultset.next())
{
putStmt.clearParameters();
putStmt.setInt(1,accessResultset.getInt("CompanyID"
);putStmt.setString(2,accessResultset.getString("CompanyName"
);putStmt.setString(3,accessResultset.getString("StreetAddress"
);rowsUpdated = rowsUpdated + putStmt.executeUpdate();
}
System.out.println ("Updated " + rowsUpdated + "rows"
;accessResultset.close();
accessConnection.close();
excelConnection.close();
aStmt.close();
stmt.close();
}
catch (ClassNotFoundException cnfe)
{
System.out.println("Error: ClassNotFoundException - " + cnfe);
}
catch(SQLException sqlE)
{
System.err.println("Error: SQLException - " + sqlE);
}
}
public static void main(String args[])
{
String excelFileName = copyFile();
getDataFromAccessDB(excelFileName);
}
}
- Thread starter
- #13
- Thread starter
- #14
As is par for the course in the development world, this particular project was put on hold and guess what - yup, its off hold now. Anyway, I used the code provided above to do the export DB content to an excel file and am getting the following message:
Error: SQLException - java.sql.SQLException: No suitable driver
How can it be that the specified driver is not found?
Thanks for your help!
JDone
Error: SQLException - java.sql.SQLException: No suitable driver
How can it be that the specified driver is not found?
Thanks for your help!
JDone
- Thread starter
- #15
Hi -
Well, I straigthened out the "no suitable driver issue" but now have a different problem. I get the following message when running the program:
Error: SQLException - java.sql.SQLException: [Microsoft][ODBC Excel Driver]Invalid precision value
java.sql.SQLException: [Microsoft][ODBC Excel Driver]Invalid precision value
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4089)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246)
at sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterString(JdbcOdbc.java:482)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(JdbcOdbcPreparedStatement.java:1222)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:575)
at CreateExcelFileWithJdbcOdbc.getDataFromAccessDB(CreateExcelFileWithJd bcOdbc.java, Compiled Code)
at CreateExcelFileWithJdbcOdbc.main(CreateExcelFileWithJdbcOdbc.java:163
=====================================================
Anyone have any insight on a solution?
Thanks.
J
Well, I straigthened out the "no suitable driver issue" but now have a different problem. I get the following message when running the program:
Error: SQLException - java.sql.SQLException: [Microsoft][ODBC Excel Driver]Invalid precision value
java.sql.SQLException: [Microsoft][ODBC Excel Driver]Invalid precision value
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4089)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246)
at sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterString(JdbcOdbc.java:482)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(JdbcOdbcPreparedStatement.java:1222)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:575)
at CreateExcelFileWithJdbcOdbc.getDataFromAccessDB(CreateExcelFileWithJd bcOdbc.java, Compiled Code)
at CreateExcelFileWithJdbcOdbc.main(CreateExcelFileWithJdbcOdbc.java:163
=====================================================
Anyone have any insight on a solution?
Thanks.
J
- Thread starter
- #16
Hi,
OK, got through the that hurdle. But now have another one. This is a real PIA!!! But OK, here's the next error message:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name too long
What could possibly be wrong with this dynamically created DSN?
excelConnection = DriverManager.getConnection("jdbcdbcriver={Microsoft Excel Driver(*.xls)};DBQ=c:\\DailyReport\\SomeReport.xls;DriverID=790;READONLY=false");
Any insights from anybody?
Thanks.
J
OK, got through the that hurdle. But now have another one. This is a real PIA!!! But OK, here's the next error message:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name too long
What could possibly be wrong with this dynamically created DSN?
excelConnection = DriverManager.getConnection("jdbc
dbcriver={Microsoft Excel Driver(*.xls)};DBQ=c:\\DailyReport\\SomeReport.xls;DriverID=790;READONLY=false");Any insights from anybody?
Thanks.
J
- Thread starter
- #18
- Thread starter
- #19
- Thread starter
- #20
Well, I managed to do a workaround that's fairly good. Since I couldn't get anywhere with the Microsoft Excel Driver, I decided to just write the results of the query to a .csv file (as was much earlier indicated). Was wondering though, is there any way to manipulate the appearance of the printed results on the Excel/csv page? IOW, I have on line 1 four header columns denoting the fields of the query. Is there any way to manipulate the background color of those header cells to make it say, gray to show a contrast between the headers and the details?
Thanks again.
J
Thanks again.
J
- Status
Similar threads
- Locked
- Question