Printable View
Dear all,
I am very new to java and am writing a program to put the resultset from an SQL query into an excel sheet. I already have downloaded the library jexcelapi library.
Please let me know how could I proceed further on this. It would be great if someone could give me a sample code to see.
Thanks a ton.
Cheers
Chandpuri
Hi guys,
I dont believe that no one has any clue on writing recordsets into excel from SQL using Java.
I thought there were good knowledgable people.
Cmon guys
Best Regards,
G
If you have downloaded JExcelApi then there are whole set of demos included, try working your way through the write one - it is comprehensive. Be aware that JExcelApi does not support xslx files i.e. the latest version of excel. If you simply want to get data from a result set into excel then create a .csv file, make the first row your column headers and and each other row the data; excel will open it drectly and convert it on the fly. For example a result with two rows, each row has three columns - age, town, country . Create a csv file that looks something like the following from the data:
Code:age, town, country
42, london, uk
19, glasgow, scotland
Did u made any progress ?
I am working on same thing and thought if you can guide !
There is a tutorial page for jexcelapi linked from their sourceforge page.Quote:
Originally Posted by chandpuri
I'm not familiar with jexcelapi, but the basics of writing a ResultSet to any other tabular medium are pretty straightforward. To access the ResultSet, use boolean ResultSet.next(), and the appropriate getter method for the column information. To write to the worksheet, use whatever setter is most appropriate to the data type. Really it's just a couple of nested for loops.
To determine the data types of the ResultSet's columns, use ResultSet.getMetaData().getColumnType(int) (the return value is an int whose interpretation is defined in java.sql.Types)
Recently I wrote some code to write Excel 2007 (OpenXML) workbooks from a database, and after researching the available software I decided to write it myself from scratch. There are a couple of good powerpoint presentations on Microsoft's website explaining how to write a workbook file in a form that is acceptable to Excel 2007. The format is very simple and also comforms to a published international standard, so unless you need to write Excel files that can be interpreted by earlier versions of Excel I recommend this approach.
After writing the peripheral code to produce the workbook the strategy I chose was to write an interface for a worksheet and implement it in the most basic manner, in an abstract class BasicWorksheet, that knows how to write the XML using the "nested for loops" logic, from any implementation of the interface.
The upshot was that I was then able to write a subclass, ResultSetWorksheet, that implemented the interface using the methods provided by ResultSet and ResultSetMetaData. Immediately it was able to write a worksheet from a ResultSet.
One thing I was careful about in implementing the abstract class was to avoid relying on the code that writes the worksheet file knowing the number of rows in the worksheet. One facet of a ResultSet is that one does not always know how many rows it contains until after one has scanned every row of data. By writing carefully avoiding references to the number of rows, I was able to produce worksheet files from a single SQL select statement. This kind of "single pass" design can be important for performance. The logic tests to see if the number of rows is known, and if not it omits the optional "dimension" element (which must precede the sheetData element if it is present) from the XML. An alternative approach would be to assemble the worksheet as a two-dimensional array in memory prior to writing it out.
Still another approach would be to model the spreadsheet as an updateable ResultSet, and use a jdbc driver for Excel files. One example of this, which I haven't investigated, is apparently xlsql, also hosted on sourceforge.
Since writing the Excel code, I've been investigating a more general solution for OpenXML in Java, called OpenXML4J. It's available under either the Apache v2 license or a BSD license, and it will handle all details of packaging and unpackaging OpenXML (Office 2007) files. In my opinion it's a bit on the heavy side for the kind of one-off project I was asked to do, but if you need to do a lot of work in OpenXML then this would almost certainly provide a solid basis for interoperability. It's definitely worth investigating.
You could use the same basic algorithm I described in the earlier posting to translate data from a ResultSet to an OpenXML document; indeed it would be a trivial matter to write OpenXML4J implementations of the Workbook and Worksheet interfaces.
Hi,
U can very well use POI for excel data manipulation.The required jar files include:
poi-3.0-alpha3-20061212
poi-contrib-3.0-alpha3-20061212
poi-scratchpad-3.0-alpha3-20061212
The below code is for reading SQL Files located in a Folder.
All SQL files in this folder will be read one by one and will be executed.
You can place the Result set out put in different blocks using JExcelApi, POI or any other. This is the main code which you will use.
let me know if anyone needs full working code.
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
public class JDBCGeneral
{
private java.sql.Connection con = null;
private final String selectMethod = "cursor";
// Constructor
public JDBCGeneral()
{
}
public static void main(String[] argv) throws Exception
{
String folderpath = "";
String folderpatharg = argv[0];
final String serverName = argv[1];
final String portNumber = argv[2];
final String databaseName = argv[3];
final String userName = argv[4];
final String password = argv[5];
final String SQLServerType = argv[6];
StringBuffer sb = new StringBuffer();
sb.append(folderpatharg);
sb.append("\\");
sb.append("updates");
sb.append("\\");
final String folderpathbase = sb.toString();
if (SQLServerType.equals("SQLServer"))
{
final String url = "jdbc:microsoft:sqlserver://";
folderpath = folderpathbase.concat("sqlsms");
String classforname = "com.microsoft.sqlserver.jdbc.SQLServerDriver" ;
JDBCGeneral myDbTest = new JDBCGeneral();
// myDbTest.displayDbPropertiesMS(classforname,url,se rverName,portNumber,databaseName,userName,
// password);
myDbTest.getfilenamesMS(classforname, url, folderpath, serverName, portNumber, databaseName,
userName, password);
}
else if (SQLServerType.equals("Oracle"))
{
URL urls[] = {};
JarFileLoader jarfileloader = new JarFileLoader(urls);
jarfileloader.addFile("/ojdbc14.jar");
final String url = "jdbc:oracle:thin:@";
folderpath = folderpathbase.concat("sqlsoracle");
// test if there's no need for SQL Execution,
// check if null value is returned from file selection on selected
// path
String classforname = "oracle.jdbc.driver.OracleDriver";
JDBCGeneral myDbTest = new JDBCGeneral();
// myDbTest.displayDbPropertiesOracle(classforname, url, serverName, portNumber, databaseName,
// userName, password);
myDbTest.getfilenamesOracle(classforname, url, folderpath, serverName, portNumber,
databaseName, userName, password);
}
}
public void displayDbPropertiesOracle(String classforname, String url, String serverName,
String portNumber, String databaseName, String userName, String password)
{
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try
{
con = this.getConnectionOracle(classforname, url, serverName, portNumber, databaseName,
userName, password);
if (con != null)
{
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: " + dm.getDriverName());
System.out.println("\tDriver Version: " + dm.getDriverVersion());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: " + dm.getDatabaseProductName());
System.out.println("\tDatabase Version: " + dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while (rs.next())
{
System.out.println("\tcatalog: " + rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}
else
System.out.println("Error: No active Connection");
}
catch (Exception e)
{
e.printStackTrace();
}
dm = null;
}
public void displayDbPropertiesMS(String classforname, String url, String serverName,
String portNumber, String databaseName, String userName, String password)
{
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try
{
con = this.getConnectionMS(classforname, url, serverName, portNumber, databaseName, userName,
password);
if (con != null)
{
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: " + dm.getDriverName());
System.out.println("\tDriver Version: " + dm.getDriverVersion());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: " + dm.getDatabaseProductName());
System.out.println("\tDatabase Version: " + dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while (rs.next())
{
System.out.println("\tcatalog: " + rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}
else
System.out.println("Error: No active Connection");
}
catch (Exception e)
{
e.printStackTrace();
}
dm = null;
}
private java.sql.Connection getConnectionMS(String classforname, String url, String serverName,
String portNumber, String databaseName, String userName, String password)
{
try
{
Class.forName(classforname);
con = java.sql.DriverManager.getConnection(
getConnectionUrlMS(url, serverName, portNumber, databaseName), userName, password);
if (con != null) System.out.println("Connection Successful!");
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
private String getConnectionUrlMS(String url, String serverName, String portNumber,
String databaseName)
{
return url + serverName + ":" + portNumber + ";databaseName=" + databaseName + ";selectMethod="
+ selectMethod + ";";
}
private java.sql.Connection getConnectionOracle(String classforname, String url,
String serverName, String portNumber, String databaseName, String userName, String password)
{
try
{
// boolean printed = false;
Class.forName(classforname);
con = java.sql.DriverManager.getConnection(
getConnectionUrlOracle(url, serverName, portNumber, databaseName), userName, password);
/*
* if (con != null) { if (printed == false)
* System.out.println("Oracle Connection Successful!"); printed = true; }
*/
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("Error Trace ingetConnectionOracle() : " + e.getMessage());
}
return con;
}
private String getConnectionUrlOracle(String url, String serverName, String portNumber,
String databaseName)
{
return url + serverName + ":" + portNumber + ":" + databaseName;
}
private String[] getfilenamesOracle(String classforname, String url, String folderpath,
String serverName, String portNumber, String databaseName, String userName, String password)
throws FileNotFoundException, IOException
{
String newfolderpath = folderpath + "\\";
File toto = new File(newfolderpath);
String[] thelist = toto.list();
for (int j = 0; j < thelist.length; ++j)
{
String[] StatementsSQL = SQLFileInput(newfolderpath + thelist[j]);
System.out.println("****************************") ;
System.out.println("Script File : " + thelist[j]);
System.out.println("Total SQL Statements : " + StatementsSQL.length);
System.out.println("****************************") ;
RunSQLOracle(StatementsSQL, classforname, url, serverName, portNumber, databaseName,
userName, password);
}
return thelist;
}
private String[] getfilenamesMS(String classforname, String url, String folderpath,
String serverName, String portNumber, String databaseName, String userName, String password)
throws FileNotFoundException, IOException
{
String newfolderpath = folderpath + "\\";
File toto = new File(newfolderpath);
String[] thelist = toto.list();
for (int j = 0; j < thelist.length; ++j)
{
String[] StatementsSQL = SQLFileInput(newfolderpath + thelist[j]);
RunSQLMS(StatementsSQL, classforname, url, serverName, portNumber, databaseName, userName,
password);
}
return thelist;
}
/*
* public String[] addToArray(String[] array, String s) { String[] ans = new
* String[array.length+1]; System.arraycopy(array, 0, ans, 0, array.length); ans[ans.length - 1] =
* s; return ans; }
*/
public String[] SQLFileInput(String sqlinput) throws FileNotFoundException, IOException
{
String str = new String();
StringBuffer sb = new StringBuffer();
FileReader filereader = new FileReader(sqlinput);
BufferedReader bufferreader = new BufferedReader(filereader);
while ((str = bufferreader.readLine()) != null)
{
sb.append(str);
}
bufferreader.close();
String[] SQLStatements = sb.toString().split(";");
return SQLStatements;
}
public void RunSQLOracle(String[] StatementsSQL, String classforname, String url,
String serverName, String portNumber, String databaseName, String userName, String password)
{
int i = 0;
HashMap badRecords = new HashMap();
HashMap goodRecords = new HashMap();
int count = 0;
while (i < StatementsSQL.length)
{
try
{
con = this.getConnectionOracle(classforname, url, serverName, portNumber, databaseName,
userName, password);
Statement stAddUser = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,
ResultSet.CONCUR_UPDATABLE);
// we ensure that there is no spaces before or after the request
// string
// in order to not execute empty statements
if (StatementsSQL[i] != null && !StatementsSQL[i].trim().equals(""))
{
if (StatementsSQL[i].toLowerCase().startsWith("select count(*)"))
{
System.out.println("CASE A");
int columnCount = 0;
ResultSet rs = stAddUser.executeQuery(StatementsSQL[i]);
ResultSetMetaData rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
String COLUMN_NAME = null;
while (rs.next())
{
for (int colLen = 1; colLen <= columnCount;)
{
COLUMN_NAME = "" + rsmd.getColumnName(colLen);
colLen++;
}
for (int colLen = 1; colLen <= columnCount;)
{
count = rs.getInt(colLen);
colLen++;
}
}
System.out.println("SQL No. " + i + "\n" + COLUMN_NAME + "\n" + count);
// goodRecords.put("SQL No. " + i, "" + COLUMN_NAME + "\nNumber of Count : " + count);
columnCount = 0;
break;
}
else if (StatementsSQL[i].toLowerCase().startsWith("select"))
{
System.out.println("CASE B");
int columnCount = 0;
ResultSet rs = stAddUser.executeQuery(StatementsSQL[i]);
ResultSetMetaData rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
System.out.println("SQL Query No. " + i);
columnCount = rsmd.getColumnCount();
boolean printed = false;
while (rs.next())
{
if (!printed)
{
for (int colLen = 1; colLen <= columnCount;)
{
System.out.print("||" + rsmd.getColumnName(colLen) + "\t");
colLen++;
}
}
printed = true;
System.out.println();
for (int colLen = 1; colLen <= columnCount;)
{
System.out.print("||" + rs.getObject(colLen) + "\t");
colLen++;
}
}
rs.last();
System.out.println("");
System.out.println("Total : " + rs.getRow());
// goodRecords.put(StatementsSQL[i], "\nTotal : " + rs.getRow() + " Rows.");
columnCount = 0;
break;
}
else
{
System.out.println("CASE C");
stAddUser.executeUpdate(StatementsSQL[i]);
goodRecords.put(StatementsSQL[i], "Rows");
}
}
closeConnection();
}
catch (SQLException sqlException)
{
badRecords.put(StatementsSQL[i] + "\n", sqlException.getMessage().toUpperCase());
// System.out.println("\n Error while executing SQL in Oracle : " +
// sqlException.getMessage());
SQLException nextException = sqlException.getNextException();
while (nextException != null)
{
System.out.println(nextException.getMessage());
nextException = nextException.getNextException();
}
// sqlException.printStackTrace();
}
if (i == StatementsSQL.length)
{
break;
}
else
{
++i;
}
}
/*
* System.out.println("********** Successfull Results **********"); Set goodSet =
* goodRecords.entrySet(); Iterator goodIterator = goodSet.iterator(); while
* (goodIterator.hasNext()) { Map.Entry good = (Map.Entry) goodIterator.next();
* System.out.println("SQL executed"); System.out.println(good.getKey() + " : ");
* System.out.println(good.getValue()); }
* System.out.println("********** Failed Results **********"); Set badSet =
* badRecords.entrySet(); Iterator badIterator = badSet.iterator(); while
* (badIterator.hasNext()) { Map.Entry bad = (Map.Entry) badIterator.next();
* System.out.println("SQL Statement failed"); System.out.println(bad.getKey());
* System.out.println("Reason : " + bad.getValue()); }
* System.out.println("*****************************" );
*/
}
public void RunSQLMS(String[] StatementsSQL, String classforname, String url, String serverName,
String portNumber, String databaseName, String userName, String password)
{
try
{
con = this.getConnectionMS(classforname, url, serverName, portNumber, databaseName, userName,
password);
Statement stAddUser = con.createStatement();
for (int i = 0; i < StatementsSQL.length; ++i)
{
if (StatementsSQL[i] != null)
{
System.out.print(StatementsSQL[i] + "...");
int rowsAffected = stAddUser.executeUpdate(StatementsSQL[i]);
if (rowsAffected == 1) System.out.println("OK");
}
}
closeConnection();
}
catch (SQLException sqlException)
{
System.out.println("\n Error while executing SQL in MS SQL Server:"
+ sqlException.getMessage());
SQLException nextException = sqlException.getNextException();
while (nextException != null)
{
System.out.println(nextException.getMessage());
nextException = nextException.getNextException();
}
// sqlException.printStackTrace();
}
}
private void closeConnection()
{
try
{
if (con != null) con.close();
con = null;
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
The Full Working Code Using JExcel API.
Enjoy :) :D :D :eek:
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.Locale;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class JDBCGeneral
{
private java.sql.Connection con = null;
private final String selectMethod = "cursor";
// Constructor
public JDBCGeneral()
{
}
public static void main(String[] argv) throws Exception
{
String folderpath = "";
String folderpatharg = argv[0];
final String serverName = argv[1];
final String portNumber = argv[2];
final String databaseName = argv[3];
final String userName = argv[4];
final String password = argv[5];
final String SQLServerType = argv[6];
StringBuffer sb = new StringBuffer();
sb.append(folderpatharg);
sb.append("\\");
sb.append("updates");
sb.append("\\");
final String folderpathbase = sb.toString();
if (SQLServerType.equals("SQLServer"))
{
final String url = "jdbc:microsoft:sqlserver://";
folderpath = folderpathbase.concat("sqlsms");
String classforname = "com.microsoft.sqlserver.jdbc.SQLServerDriver" ;
JDBCGeneral myDbTest = new JDBCGeneral();
myDbTest.getfilenamesMS(classforname, url, folderpath, serverName, portNumber, databaseName, userName, password);
}
else if (SQLServerType.equals("Oracle"))
{
URL urls[] = {};
JarFileLoader jarfileloader = new JarFileLoader(urls);
jarfileloader.addFile("/ojdbc14.jar");
final String url = "jdbc:oracle:thin:@";
folderpath = folderpathbase.concat("sqlsoracle");
String classforname = "oracle.jdbc.driver.OracleDriver";
JDBCGeneral myDbTest = new JDBCGeneral();
myDbTest.getfilenamesOracle(classforname, url, folderpath, serverName, portNumber, databaseName, userName, password);
}
}
private java.sql.Connection getConnectionMS(String classforname, String url, String serverName, String portNumber, String databaseName, String userName, String password)
{
try
{
Class.forName(classforname);
con = java.sql.DriverManager.getConnection(getConnection UrlMS(url, serverName, portNumber, databaseName), userName, password);
if (con != null) System.out.println("Connection Successful!");
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
private String getConnectionUrlMS(String url, String serverName, String portNumber, String databaseName)
{
return url + serverName + ":" + portNumber + ";databaseName=" + databaseName + ";selectMethod=" + selectMethod + ";";
}
private java.sql.Connection getConnectionOracle(String classforname, String url, String serverName, String portNumber, String databaseName, String userName, String password)
{
try
{
Class.forName(classforname);
con = java.sql.DriverManager.getConnection(getConnection UrlOracle(url, serverName, portNumber, databaseName), userName, password);
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("Error Trace ingetConnectionOracle() : " + e.getMessage());
}
return con;
}
private String getConnectionUrlOracle(String url, String serverName, String portNumber, String databaseName)
{
return url + serverName + ":" + portNumber + ":" + databaseName;
}
private String[] getfilenamesOracle(String classforname, String url, String folderpath, String serverName, String portNumber, String databaseName, String userName, String password)
throws FileNotFoundException, IOException
{
String newfolderpath = folderpath + "\\";
File toto = new File(newfolderpath);
String[] thelist = toto.list();
for (int j = 0; j < thelist.length; ++j)
{
String[] StatementsSQL = SQLFileInput(newfolderpath + thelist[j]);
System.out.println("****************************") ;
System.out.println("Script File : " + thelist[j]);
System.out.println("Total SQL Statements : " + StatementsSQL.length);
System.out.println("****************************") ;
File excelFile = new File(thelist[j] + ".xls");
String FileName = excelFile.toString();
if (!excelFile.exists())
{
excelFile.createNewFile();
System.out.println("Excel file " + excelFile + " has been created.");
}
try {
RunSQLOracle(StatementsSQL, classforname, url, serverName, portNumber, databaseName, userName, password, FileName, j);
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return thelist;
}
private String[] getfilenamesMS(String classforname, String url, String folderpath, String serverName, String portNumber, String databaseName, String userName, String password)
throws FileNotFoundException, IOException
{
String newfolderpath = folderpath + "\\";
File toto = new File(newfolderpath);
String[] thelist = toto.list();
for (int j = 0; j < thelist.length; ++j)
{
String[] StatementsSQL = SQLFileInput(newfolderpath + thelist[j]);
RunSQLMS(StatementsSQL, classforname, url, serverName, portNumber, databaseName, userName, password);
}
return thelist;
}
/*
* public String[] addToArray(String[] array, String s) { String[] ans = new String[array.length+1]; System.arraycopy(array, 0, ans, 0, array.length); ans[ans.length - 1] = s; return ans; }
*/
public String[] SQLFileInput(String sqlinput) throws FileNotFoundException, IOException
{
String str = new String();
StringBuffer sb = new StringBuffer();
FileReader filereader = new FileReader(sqlinput);
BufferedReader bufferreader = new BufferedReader(filereader);
while ((str = bufferreader.readLine()) != null)
{
sb.append(str);
}
bufferreader.close();
String[] SQLStatements = sb.toString().split(";");
return SQLStatements;
}
public void RunSQLOracle(String[] StatementsSQL, String classforname, String url, String serverName, String portNumber, String databaseName, String userName, String password, String excelFile, int SqlNo) throws WriteException
{
int i = 0;
HashMap badRecords = new HashMap();
HashMap goodRecords = new HashMap();
int count = 0;
while (i < StatementsSQL.length)
{
try
{
String filename = excelFile;
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en"));
WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws);
workbook.createSheet("SQL No." + i, i);
System.out.println("Sheet Created : SQL No." + i);
WritableSheet excelSheet = workbook.getSheet("SQL No." + i);
con = this.getConnectionOracle(classforname, url, serverName, portNumber, databaseName, userName, password);
Statement stAddUser = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E, ResultSet.CONCUR_UPDATABLE);
if (StatementsSQL[i] != null && !StatementsSQL[i].trim().equals("")){
try
{
if (StatementsSQL[i].toLowerCase().startsWith("select count(*)")){
System.out.println("CASE A");
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat cf = new WritableCellFormat(wf);
cf.setWrap(true);
int columnCount = 0;
ResultSet rs = stAddUser.executeQuery(StatementsSQL[i]);
ResultSetMetaData rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
String COLUMN_NAME = null;
while (rs.next()){
for (int colLen = 1; colLen <= columnCount;){
COLUMN_NAME = "" + rsmd.getColumnName(colLen);
cf.setWrap(false);
cf.setShrinkToFit(false);
cf.setBackground(Colour.BLACK);
Label l = new Label(columnCount - 1, colLen - 1, "Sr. No", cf);
excelSheet.addCell(l);
Number n = new Number(columnCount - 1, colLen, colLen);
excelSheet.addCell(n);
l = new Label(columnCount, colLen - 1, COLUMN_NAME, cf);
excelSheet.addCell(l);
count = rs.getInt(colLen);
n = new Number(columnCount, colLen, count);
excelSheet.addCell(n);
colLen++;
}
}
System.out.println("SQL No. " + i + "\n" + COLUMN_NAME + "\n" + count);
// goodRecords.put("SQL No. " + i, "" + COLUMN_NAME + "\nNumber of Count : " + count);
columnCount = 0;
}
else if (StatementsSQL[i].toLowerCase().startsWith("select")){
System.out.println("CASE B");
/* Format the Font */
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat cf = new WritableCellFormat(wf);
cf.setWrap(true);
int columnCount = 0;
ResultSet rs = stAddUser.executeQuery(StatementsSQL[i]);
ResultSetMetaData rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
columnCount = rsmd.getColumnCount();
boolean printed = false;
String COLUMN_NAME[] = new String[300];
while (rs.next())
{
Label l = new Label(0, 0, "Sr. No", cf);
excelSheet.addCell(l);
if (!printed){
for (int colLen = 1; colLen <= columnCount;){
COLUMN_NAME[colLen] = "" + rsmd.getColumnName(colLen);
l = new Label(colLen, 0, COLUMN_NAME[colLen], cf);
excelSheet.addCell(l);
colLen++;
}
}
printed = true;
for (int colLen = 1; colLen <= columnCount;){
int currentRow = rs.getRow();
Number n = new Number(0, colLen, colLen);
excelSheet.addCell(n);
l = new Label(colLen, currentRow, ""+rs.getObject(colLen), cf);
excelSheet.addCell(l);
colLen++;
}
}
rs.last();
// goodRecords.put(StatementsSQL[i], "\nTotal : " + rs.getRow() + " Rows.");
columnCount = 0;
}
else
{
System.out.println("CASE C");
stAddUser.executeUpdate(StatementsSQL[i]);
goodRecords.put(StatementsSQL[i], "Rows");
}
}// Closing the TRY
catch (WriteException e){
e.printStackTrace();
}
}
workbook.write();
workbook.close();
closeConnection();
}catch(IOException ioe){
}catch (SQLException sqlException){
badRecords.put(StatementsSQL[i] + "\n", sqlException.getMessage().toUpperCase());
SQLException nextException = sqlException.getNextException();
while (nextException != null){
System.out.println(nextException.getMessage());
nextException = nextException.getNextException();
}
}
if (i == StatementsSQL.length){
System.out.println("i " + i + "StatementsSQL.length "+StatementsSQL.length);
break;
}
else{
i++;
}
}
/*
* System.out.println("********** Successfull Results **********"); Set goodSet = goodRecords.entrySet(); Iterator goodIterator = goodSet.iterator(); while (goodIterator.hasNext()) { Map.Entry
* good = (Map.Entry) goodIterator.next(); System.out.println("SQL executed"); System.out.println(good.getKey() + " : "); System.out.println(good.getValue()); }
* System.out.println("********** Failed Results **********"); Set badSet = badRecords.entrySet(); Iterator badIterator = badSet.iterator(); while (badIterator.hasNext()) { Map.Entry bad =
* (Map.Entry) badIterator.next(); System.out.println("SQL Statement failed"); System.out.println(bad.getKey()); System.out.println("Reason : " + bad.getValue()); }
* System.out.println("*****************************" );
*/
}
static void writeDataSheet(WritableSheet s) throws WriteException
{
/* Format the Font */
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat cf = new WritableCellFormat(wf);
cf.setWrap(true);
/* Creates Label and writes date to one cell of sheet */
Label l = new Label(0, 0, "Date", cf);
s.addCell(l);
WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT9);
DateTime dt = new DateTime(0, 1, new Date(), cf1, DateTime.GMT);
s.addCell(dt);
/* Creates Label and writes float number to one cell of sheet */
l = new Label(2, 0, "Float", cf);
s.addCell(l);
WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
Number n = new Number(2, 1, 3.1415926535, cf2);
s.addCell(n);
n = new Number(2, 2, -3.1415926535, cf2);
s.addCell(n);
// Creates Label and writes float number upto 3 decimal to one cell of sheet
l = new Label(3, 0, "3dps", cf);
s.addCell(l);
NumberFormat dp3 = new NumberFormat("#.###");
WritableCellFormat dp3cell = new WritableCellFormat(dp3);
n = new Number(3, 1, 3.1415926535, dp3cell);
s.addCell(n);
/* Creates Label and adds 2 cells of sheet */
l = new Label(4, 0, "Add 2 cells", cf);
s.addCell(l);
n = new Number(4, 1, 10);
s.addCell(n);
n = new Number(4, 2, 16);
s.addCell(n);
Formula f = new Formula(4, 3, "E1+E2");
s.addCell(f);
/* Creates Label and multipies value of one cell of sheet by 2 */
l = new Label(5, 0, "Multipy by 2", cf);
s.addCell(l);
n = new Number(5, 1, 10);
s.addCell(n);
f = new Formula(5, 2, "F1 * 3");
s.addCell(f);
/* Creates Label and divide value of one cell of sheet by 2.5 */
l = new Label(6, 0, "Divide", cf);
s.addCell(l);
n = new Number(6, 1, 12);
s.addCell(n);
f = new Formula(6, 2, "F1/2.5");
s.addCell(f);
}
public void RunSQLMS(String[] StatementsSQL, String classforname, String url, String serverName, String portNumber, String databaseName, String userName, String password)
{
try
{
con = this.getConnectionMS(classforname, url, serverName, portNumber, databaseName, userName, password);
Statement stAddUser = con.createStatement();
for (int i = 0; i < StatementsSQL.length; ++i)
{
if (StatementsSQL[i] != null)
{
System.out.print(StatementsSQL[i] + "...");
int rowsAffected = stAddUser.executeUpdate(StatementsSQL[i]);
if (rowsAffected == 1) System.out.println("OK");
}
}
closeConnection();
}
catch (SQLException sqlException)
{
System.out.println("\n Error while executing SQL in MS SQL Server:" + sqlException.getMessage());
SQLException nextException = sqlException.getNextException();
while (nextException != null)
{
System.out.println(nextException.getMessage());
nextException = nextException.getNextException();
}
// sqlException.printStackTrace();
}
}
private void closeConnection()
{
try
{
if (con != null) con.close();
con = null;
}
catch (Exception e)
{
e.printStackTrace();
}
}
}