• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Resulset to excel data not populating...

 
Sayantan Debnath
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic