aspose file tools*
The moose likes JDBC and the fly likes Resulset to excel data not populating... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Resulset to excel data not populating..." Watch "Resulset to excel data not populating..." New topic
Author

Resulset to excel data not populating...

Sayantan Debnath
Greenhorn

Joined: Aug 30, 2013
Posts: 2
Resulset to excel data not populating...

below is the code :

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;



public class QA2Excel {

/**
* @param paramArrayOfString
* @throws Exception
*/
public static void main(String[] paramArrayOfString)
throws Exception
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String str1 = "jdbcracle:thin:@indlin076:1521:VELABP1";
String str2 = "VELDB2";
String str3 = "VELDB2";
Connection localConnection = DriverManager.getConnection(str1, str2, str3);
HSSFWorkbook localHSSFWorkbook = new HSSFWorkbook();
localConnection.setAutoCommit(false);
Statement localStatement = localConnection.createStatement();
String str4 = "VELDBO2";
String str5 = "VELDBO2";
String str6 = "@DB14";
String filename="C://Users/debankad/Desktop/empha.xls";

FileReader localFileReader = new FileReader("C://Users/debankad/Desktop/QueriesOfQa.txt");


BufferedReader localBufferedReader = new BufferedReader(localFileReader);
String str11;
String paramString= "";


//StringBuilder stringBuilder = new StringBuilder();

while ((str11 = localBufferedReader.readLine()) != null)
{
if (str11.startsWith("--EPC")){
paramString = str11.replace("--","").toUpperCase();
System.out.println("PARAMSTRING" + paramString);
}

if (!str11.endsWith(";")){
System.out.println(str11);
String str14 = str11;

if (str11 != null && !str11.isEmpty() && !str11.startsWith("--") && !str11.startsWith("//")){

paramString = paramString + 1;
ResultSet localResultSet = localStatement.executeQuery(str11);
int i = recordCountinResultSet(localResultSet);

HSSFSheet localHSSFSheet = localHSSFWorkbook.createSheet(paramString);
HSSFRow localHSSFRow = localHSSFSheet.createRow(0);
ResultSetMetaData localResultSetMetaData = localResultSet.getMetaData();
for (int k = 0; k < localResultSetMetaData.getColumnCount(); k++){
localHSSFRow.createCell((short)k).setCellValue(localResultSetMetaData.getColumnLabel(k + 1));
for (int k1 = 1; localResultSet.next(); k1++)
{
localHSSFRow = localHSSFSheet.createRow((short)k1);
for (int j = 0; j < localResultSetMetaData.getColumnCount(); j++)
localHSSFRow.createCell((short)j).setCellValue(localResultSet.getString(j + 1));
}
}
FileOutputStream fileOut = new FileOutputStream(filename);
localHSSFWorkbook.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated!");


if (i == 0)
{
System.out.println("QA Passed");

}
else if (i >= 1)
{
System.out.println("QA Failed");
}
else
{
System.out.println("No such Query");
}
}
}



//selectTableCreation(localStatement,localFileReader);

// String str8 = str2.substring(str2.lastIndexOf(" "), str2.length()).trim().toUpperCase();
// System.out.println(str8);


}} catch (Exception E){
E.printStackTrace();
}
}




@SuppressWarnings("rawtypes")
public static List selectTableCreation(Statement paramStatement, FileReader paramFileReader)
{
ArrayList<ResultSet> localArrayList = new ArrayList<ResultSet>();
try
{
BufferedReader localBufferedReader = new BufferedReader(paramFileReader);
String str1;

while ((str1 = localBufferedReader.readLine()) != null)
{
String str2 = str1;


try
{
ResultSet localResultSet = paramStatement.executeQuery(str2);
System.out.println("Result Set Obtained");
localArrayList.add(localResultSet);

}
catch (Exception localException2)
{
}
}
}
catch (Exception localException1)
{
System.out.println("Exception caught as::" + localException1.getMessage());
}
return localArrayList;
}

@SuppressWarnings("deprecation")
public static void writeToFile(HSSFWorkbook paramHSSFWorkbook, ResultSet paramResultSet, String paramString)
throws Exception
{
HSSFSheet localHSSFSheet = paramHSSFWorkbook.createSheet(paramString);
HSSFRow localHSSFRow = localHSSFSheet.createRow(0);
ResultSetMetaData localResultSetMetaData = paramResultSet.getMetaData();
for (int i = 0; i < localResultSetMetaData.getColumnCount(); i++)
localHSSFRow.createCell((short)i).setCellValue(localResultSetMetaData.getColumnLabel(i + 1));
for (int i = 1; paramResultSet.next(); i++)
{
localHSSFRow = localHSSFSheet.createRow((short)i);
for (int j = 0; j < localResultSetMetaData.getColumnCount(); j++)
localHSSFRow.createCell((short)j).setCellValue(paramResultSet.getString(j + 1));
}}

public static int recordCountinResultSet(ResultSet paramResultSet)
{
int i = 0;
try
{
while (paramResultSet.next())
i++;
}
catch (Exception localException)
{
System.out.println("Exception obtained as" + localException.getMessage());
}
return i;
}



}

please tell me what i am doing wrong..
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Welcome to the Ranch, Sayantan!

My advice is to refactor your current code and separate the part which reads data from the database from the part which writes the Excel file. The goal is to have a method that will read the data into and object or collection of objects and another method which will store these data into the Excel file (instead of using the ResultSet itself to pas the data between the two). You can then see whether the database code returned all expected data (by using a debugger or by printing out details about the data, for example by using System.out.println()) or not. Then you'll know whether it's the database code or the Excel code which has problems. You should then be able to post a shorter, more precise question (and, if the problem turns out not to be in the database code, in a more relevant forum).

See also HowToAskQuestionsOnJavaRanch (especially IsolateTheProblem) and SSCCE.
Sayantan Debnath
Greenhorn

Joined: Aug 30, 2013
Posts: 2
Martin Vajsar wrote:Welcome to the Ranch, Sayantan!

My advice is to refactor your current code and separate the part which reads data from the database from the part which writes the Excel file. The goal is to have a method that will read the data into and object or collection of objects and another method which will store these data into the Excel file (instead of using the ResultSet itself to pas the data between the two). You can then see whether the database code returned all expected data (by using a debugger or by printing out details about the data, for example by using System.out.println()) or not. Then you'll know whether it's the database code or the Excel code which has problems. You should then be able to post a shorter, more precise question (and, if the problem turns out not to be in the database code, in a more relevant forum).

See also HowToAskQuestionsOnJavaRanch (especially IsolateTheProblem) and SSCCE.


Thanks will try for sure.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Resulset to excel data not populating...