Below is the java code. Actually i am not so good in java or unix. The java code was initially created by someone else to generate a .xls file and then the shell script would place the file into the right directory(which was working perfectly fine).
Then i was supposed to make some changes in the java code to create a .csv file so i created the below code with someone's help .The code is creating the .csv file as desired but now the generated file is not being placed in the right directory.
the initial code was as below------
public class Int2TextUtility
{
public Int2TextUtility()
{
}
public static void main(String args[])
{
if(args.length < 2)
{
System.err.println("Usage: <java class name> <report path> <file name>");
System.exit(1);
}
SimpleDateFormat dateFormat = new SimpleDateFormat("MMddyyyy");
String date = dateFormat.format(new Date());
String reportPath = args[0];
String fileName = (new StringBuilder(String.valueOf(args[1]))).append(date).append(".xls").toString();
System.out.println((new StringBuilder("report file name ")).append(fileName).toString());
String query = prepareQuery();
System.out.println("Preparing connection..");
Connection conn = getConnection();
System.out.println((new StringBuilder("Got connection..")).append(conn).toString());
try
{
writeToExcel(conn, query, reportPath, fileName);
System.out.println("Report is generated..");
}
catch(Exception e)
{
e.printStackTrace();
System.err.println("There is some issue in report generation..");
}
System.out.println("Database connection is being closed..");
closeConnection(conn);
System.out.println("Database connection is closed..");
}
private static String prepareQuery()
{
StringBuilder builder = new StringBuilder();
builder.append("select NVL( TO_CHAR(s.name), ' ' ) name ,NVL( TO_CHAR(s.ql), ' ' ) ql, NVL( TO_C" +
"HAR(s.job_code), ' ' ) job_code,"
);
builder.append(" NVL( TO_CHAR(s.bu), ' ' ) bu ,NVL( TO_CHAR(s.resource_flag), ' ' ) resource_fla" +
"g,NVL( TO_CHAR(s.manager), ' ' ) manager ,"
);
builder.append(" NVL( TO_CHAR(s.manager_ql), ' ' ) manager_ql,NVL( TO_CHAR(s.date_job_code_enter" +
"ed), ' ' ) date_job_code_entered, "
);
builder.append(" NVL( TO_CHAR(s.country), ' ' ) country,NVL( TO_CHAR(pp.region), ' ' ) region,");
builder.append(" NVL( TO_CHAR(j.operational), ' ' ) operational,NVL( TO_CHAR(j.operational_speci" +
"al), ' ' ) operational_special,NVL( TO_CHAR(j.inv_supp), ' ' ) inv_supp,"
);
builder.append(" NVL( TO_CHAR(j.inv_not_supp), ' ' ) inv_not_supp,NVL( TO_CHAR(j.contigent_liabi" +
"lities), ' ' ) contigent_liabilities,NVL( TO_CHAR(j.financing_comm), ' ') financ" +
"ing_comm, "
);
builder.append(" NVL( TO_CHAR(s.Bus_Subunit_Code), ' ' ) Bus_Subunit_Code,NVL( TO_CHAR(s.Bus_Uni" +
"t_Name), ' ' ) Bus_Unit_Name"
);
builder.append(" from NCR_JOB_CODE_LEVELS j,NCR_SPP_SAR_ALL s,ncr_po_prefix pp");
builder.append(" where j.job_code = s.job_code and s.country = pp.code(+) ");
builder.append(" order by s.name");
System.out.println((new StringBuilder("Prepared Query :")).append(builder.toString()).toString());
return builder.toString();
}
private static void closeConnection(Connection conn)
{
try
{
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection conn = null;
try
{
ResourceBundle bundle = ResourceBundle.getBundle("database");
Class.forName(bundle.getString("DRIVER_NAME"));
conn = DriverManager.getConnection(bundle.getString("DB_URL"), bundle.getString("USER_NAME"), bundle.getString("PASS"));
}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
return conn;
}
private static void writeToExcel(Connection conn, String query, String reportPath, String fileName)
throws Exception
{
PreparedStatement ps = conn.prepareStatement(query);
ResultSet rs = ps.executeQuery();
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet();
int rowCount = 0;
writeHeader(sheet, rowCount);
rowCount++;
String BUS_UNIT_NAME;
HSSFCell cell_BUS_UNIT_NAME;
for(; rs.next(); cell_BUS_UNIT_NAME.setCellValue(BUS_UNIT_NAME))
{
HSSFRow row = sheet.createRow(rowCount++);
String NAME = rs.getString("NAME");
String QL = rs.getString("QL");
String JOB_CODE = rs.getString("JOB_CODE");
String BU = rs.getString("BU");
String RESOURCE_FLAG = rs.getString("RESOURCE_FLAG");
String MANAGER = rs.getString("MANAGER");
String MANAGER_QL = rs.getString("MANAGER_QL");
String DATE_JOB_CODE_ENTERED = rs.getString("DATE_JOB_CODE_ENTERED");
String COUNTRY = rs.getString("COUNTRY");
String REGION = rs.getString("REGION");
String OPERATIONAL = rs.getString("OPERATIONAL");
String OPERATIONAL_SPECIAL = rs.getString("OPERATIONAL_SPECIAL");
String INV_SUPP = rs.getString("INV_SUPP");
String INV_NOT_SUPP = rs.getString("INV_NOT_SUPP");
String CONTIGENT_LIABILITIES = rs.getString("CONTIGENT_LIABILITIES");
String FINANCING_COMM = rs.getString("FINANCING_COMM");
String BUS_SUBUNIT_CODE = rs.getString("BUS_SUBUNIT_CODE");
BUS_UNIT_NAME = rs.getString("BUS_UNIT_NAME");
HSSFCell cell_NAME = row.createCell(0);
cell_NAME.setCellValue(NAME);
HSSFCell cell_QL = row.createCell(1);
cell_QL.setCellValue(QL);
HSSFCell cell_JOB_CODE = row.createCell(2);
cell_JOB_CODE.setCellValue(JOB_CODE);
HSSFCell cell_BU = row.createCell(3);
cell_BU.setCellValue(BU);
HSSFCell cell_RESOURCE_FLAG = row.createCell(4);
cell_RESOURCE_FLAG.setCellValue(RESOURCE_FLAG);
HSSFCell cell_MANAGER = row.createCell(5);
cell_MANAGER.setCellValue(MANAGER);
HSSFCell cell_MANAGER_QL = row.createCell(6);
cell_MANAGER_QL.setCellValue(MANAGER_QL);
HSSFCell cell_DATE_JOB_CODE_ENTERED = row.createCell(7);
cell_DATE_JOB_CODE_ENTERED.setCellValue(DATE_JOB_CODE_ENTERED);
HSSFCell cell_COUNTRY = row.createCell(8);
cell_COUNTRY.setCellValue(COUNTRY);
HSSFCell cell_REGION = row.createCell(9);
cell_REGION.setCellValue(REGION);
HSSFCell cell_OPERATIONAL = row.createCell(10);
cell_OPERATIONAL.setCellValue(OPERATIONAL);
HSSFCell cell_OPERATIONAL_SPECIAL = row.createCell(11);
cell_OPERATIONAL_SPECIAL.setCellValue(OPERATIONAL_SPECIAL);
HSSFCell cell_INV_SUPP = row.createCell(12);
cell_INV_SUPP.setCellValue(INV_SUPP);
HSSFCell cell_INV_NOT_SUPP = row.createCell(13);
cell_INV_NOT_SUPP.setCellValue(INV_NOT_SUPP);
HSSFCell cell_CONTIGENT_LIABILITIES = row.createCell(14);
cell_CONTIGENT_LIABILITIES.setCellValue(CONTIGENT_LIABILITIES);
HSSFCell cell_FINANCING_COMM = row.createCell(15);
cell_FINANCING_COMM.setCellValue(FINANCING_COMM);
HSSFCell cell_BUS_SUBUNIT_CODE = row.createCell(16);
cell_BUS_SUBUNIT_CODE.setCellValue(BUS_SUBUNIT_CODE);
cell_BUS_UNIT_NAME = row.createCell(17);
}
String file = (new StringBuilder(String.valueOf(reportPath))).append("/").append(fileName).toString();
System.out.println((new StringBuilder("Report is going to be written in :")).append(file).toString());
book.write(new FileOutputStream(file));
}
private static void writeHeader(HSSFSheet sheet, int rowCount)
{
HSSFRow header = sheet.createRow(rowCount);
header.createCell(0).setCellValue("NAME");
header.createCell(1).setCellValue("QL");
header.createCell(2).setCellValue("JOB_CODE");
header.createCell(3).setCellValue("BU");
header.createCell(4).setCellValue("RESOURCE FLAG");
header.createCell(5).setCellValue("MANAGER");
header.createCell(6).setCellValue("MANAGER_QL");
header.createCell(7).setCellValue("DATE_JOB_CREATED");
header.createCell(8).setCellValue("COUNTRY");
header.createCell(9).setCellValue("REGION");
header.createCell(10).setCellValue("OPERATIONAL");
header.createCell(11).setCellValue("OPERATIONAL SPECIAL");
header.createCell(12).setCellValue("INVENTORY(SUPPORTED)");
header.createCell(13).setCellValue("INVENTORY(Not SUPPORTED)");
header.createCell(14).setCellValue("CONTINGENT LIABILITIES");
header.createCell(15).setCellValue("FINANCING COMMITMENTS");
header.createCell(16).setCellValue("COST CENTER");
header.createCell(17).setCellValue("SUB-ORG CODE");
}
}
I changed the code to below------
package org.dev.excel.util;
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.ResourceBundle;
public class Int2TextUtility
{
private static final String COMMA_DELIMITER = ",";
private static final String NEW_LINE_SEPARATOR = "\n";
private static final String FILE_HEADER = "id,firstName,lastName,gender,age";
public Int2TextUtility()
{
}
public static void main(String args[])
{
if(args.length < 2)
{
System.err.println("Usage: <java class name> <report path> <file name>");
System.exit(1);
}
SimpleDateFormat dateFormat = new SimpleDateFormat("MMddyyyy");
String date = dateFormat.format(new Date());
String reportPath = args[0];
String fileName = (new StringBuilder(String.valueOf(args[1]))).append(date).append(".csv").toString();
System.out.println((new StringBuilder("report file name ")).append(fileName).toString());
String query = prepareQuery();
System.out.println("Preparing connection..");
Connection conn = getConnection();
System.out.println((new StringBuilder("Got connection..")).append(conn).toString());
try
{
writeCsvFile(conn, query, reportPath, fileName);
System.out.println("Report is generated..");
}
catch(Exception e)
{
e.printStackTrace();
System.err.println("There is some issue in report generation..");
}
System.out.println("Database connection is being closed..");
closeConnection(conn);
System.out.println("Database connection is closed..");
}
private static String prepareQuery()
{
StringBuilder builder = new StringBuilder();
builder.append("select NVL( TO_CHAR(s.name), ' ' ) name ,NVL( TO_CHAR(s.ql), ' ' ) ql, NVL( TO_C" +
"HAR(s.job_code), ' ' ) job_code,"
);
builder.append(" NVL( TO_CHAR(s.bu), ' ' ) bu ,NVL( TO_CHAR(s.resource_flag), ' ' ) resource_fla" +
"g,NVL( TO_CHAR(s.manager), ' ' ) manager ,"
);
builder.append(" NVL( TO_CHAR(s.manager_ql), ' ' ) manager_ql,NVL( TO_CHAR(s.date_job_code_enter" +
"ed), ' ' ) date_job_code_entered, "
);
builder.append(" NVL( TO_CHAR(s.country), ' ' ) country,NVL( TO_CHAR(pp.region), ' ' ) region,");
builder.append(" NVL( TO_CHAR(j.operational), ' ' ) operational,NVL( TO_CHAR(j.operational_speci" +
"al), ' ' ) operational_special,NVL( TO_CHAR(j.inv_supp), ' ' ) inv_supp,"
);
builder.append(" NVL( TO_CHAR(j.inv_not_supp), ' ' ) inv_not_supp,NVL( TO_CHAR(j.contigent_liabi" +
"lities), ' ' ) contigent_liabilities,NVL( TO_CHAR(j.financing_comm), ' ') financ" +
"ing_comm, "
);
builder.append(" NVL( TO_CHAR(s.Bus_Subunit_Code), ' ' ) Bus_Subunit_Code,NVL( TO_CHAR(s.Bus_Uni" +
"t_Name), ' ' ) Bus_Unit_Name"
);
builder.append(" from NCR_JOB_CODE_LEVELS j,NCR_SPP_SAR_ALL s,ncr_po_prefix pp");
builder.append(" where j.job_code = s.job_code and s.country = pp.code(+) ");
builder.append(" order by s.name");
System.out.println((new StringBuilder("Prepared Query :")).append(builder.toString()).toString());
return builder.toString();
}
private static void closeConnection(Connection conn)
{
try
{
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection conn = null;
try
{
ResourceBundle bundle = ResourceBundle.getBundle("database");
Class.forName(bundle.getString("DRIVER_NAME"));
conn = DriverManager.getConnection(bundle.getString("DB_URL"), bundle.getString("USER_NAME"), bundle.getString("PASS"));
}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
return conn;
}
public static void writeCsvFile(Connection conn, String query, String reportPath, String fileName)
throws SQLException
{
ResultSet rs;
FileWriter fileWriter;
PreparedStatement ps = conn.prepareStatement(query);
rs = ps.executeQuery();
String file = (new StringBuilder(String.valueOf(reportPath))).append("/").append(fileName).toString();
System.out.println((new StringBuilder("Report is going to be written in :")).append(file).toString());
fileWriter = null;
try
{
fileWriter = new FileWriter(fileName);
fileWriter.append("id,firstName,lastName,gender,age".toString());
fileWriter.append("\n");
for(; rs.next(); fileWriter.append("\n"))
{
fileWriter.append(String.valueOf(rs.getString("NAME")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("QL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("JOB_CODE")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("BU")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("RESOURCE FLAG")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("MANAGER")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("MANAGER_QL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("DATE_JOB_CREATED")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("COUNTRY")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("REGION")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("OPERATIONAL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("OPERATIONAL SPECIAL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("INVENTORY(SUPPORTED")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("INVENTORY(Not SUPPORTED)")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("CONTINGENT LIABILITIES")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("FINANCING COMMITMENTS")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("COST CENTER")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("COST CENTER")));
fileWriter.append(",");
}
System.out.println("CSV file was created successfully !!!");
break MISSING_BLOCK_LABEL_684;
}
catch(Exception e)
{
System.out.println("Error in CsvFileWriter !!!");
e.printStackTrace();
}
try
{
fileWriter.flush();
fileWriter.close();
}
catch(IOException e)
{
System.out.println("Error while flushing/closing fileWriter !!!");
e.printStackTrace();
}
break MISSING_BLOCK_LABEL_713;
Exception exception;
exception;
try
{
fileWriter.flush();
fileWriter.close();
}
catch(IOException e)
{
System.out.println("Error while flushing/closing fileWriter !!!");
e.printStackTrace();
}
throw exception;
try
{
fileWriter.flush();
fileWriter.close();
}
catch(IOException e)
{
System.out.println("Error while flushing/closing fileWriter !!!");
e.printStackTrace();
}
}
}