/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
/**
*
* @author Administrator
*/
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.newgen.dmsapi.DMSXmlList;
import com.newgen.dmsapi.DMSXmlResponse;
import com.newgen.omni.wf.util.app.*;
import java.io.FileInputStream;
import java.sql.*;
import java.util.Iterator;
import java.util.Vector;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ReadExcelFile {
private
String inputFile;
NGEjbClient ejbObj;
StringBuffer strBuffer = null;
String strInputXml="";
String strOutputXml = "";
String strSessionId = "";
String strLogPath = "D://netbe//samp";
String strReportLogPath = "";
//Date dtCurDate = new Date();
String strCurTime = "";
String strCurDateTime = "";
String strJbossIp = "192.168.12.87";
String strJbossPort = "1099";
String strCabinetName = "tr_spt";
String strUserName = "supervisor";
String strPassword = "supervisor351";
String sFolderName="";
String strInputFilepathAS="outfile.csv";
String strVendornameA="";
String strVendorname1A="";
Connection conn=null;
public void connect()throws Exception{
ejbObj=NGEjbClient.getSharedInstance();
ejbObj.initialize("192.168.12.87","1099","Jboss");
strBuffer=new StringBuffer();
//String strInputXml = "";
DMSXmlResponse xmlResponse = null;
DMSXmlList xmlList = null;
strInputXml ="<?xml version=1.0?>"
+"<NGOConnectCabinet_Input>"
+ "<Option>NGOConnectCabinet</Option>"
+"<CabinetName>"+strCabinetName+"</CabinetName>"
+"<UserName>"+strUserName+"</UserName>"
+"<UserPassword>"+strPassword+"</UserPassword>"
+"<UserExist>N</UserExist>"
+"<UserType>S</UserType>"
+"<NGOConnectCabinet_Input>";
strOutputXml=ejbObj.makeCall(strInputXml.toString());
xmlResponse=new DMSXmlResponse(strOutputXml);
System.out.println("Cabinet Connected Successfully\n");
System.out.println("Fetched List While Connect:\n\n"+strOutputXml);
xmlList = xmlResponse.createList("Instruments", "Instrument");
if(xmlResponse.getVal("Status").equals("0")){
strSessionId=xmlResponse.getVal("UserDBId");
System.out.println("Session ID of this connection:"+strSessionId);
}else{
System.out.println("Error in Connecting Application Server");
return;
}
}
public void DisconnectCabinet() throws Exception{
ejbObj=NGEjbClient.getSharedInstance();
ejbObj.initialize("192.168.12.87","1099","Jboss");
DMSXmlResponse xmlResponse = null;
strInputXml ="<?xml version='1.0'?>"
+"<NGODisconnectCabinet_Input>"
+"<Option>NGODisconnectCabinet</Option>"
+"<CabinetName>"+strCabinetName+"</CabinetName>"
+"<UserDBId>"+strSessionId+"</UserDBId>"
+"</NGODisconnectCabinet_Input>";
strOutputXml=ejbObj.makeCall(strInputXml.toString());
xmlResponse=new DMSXmlResponse(strOutputXml);
if(xmlResponse.getVal("Status").equals("0")){
// strSessionId=xmlResponse.getVal("UserDBId");
System.out.println("\n Cabinet DisConnected Successfully and session id is:"+strSessionId);
// System.out.println("fetched list:"+strOutputXml);
}else{
System.out.println("Error in DisConnecting Application Server");
return;
}
}
/*
public static Vector ReadCSV(String fileName) {
Vector cellVectorHolder = new Vector();
try {
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while (rowIter.hasNext()) {
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector = new Vector();
while (cellIter.hasNext()) {
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
} catch (Exception e) {
e.printStackTrace();
}
return cellVectorHolder;
}
public static void printCellDataToConsole(Vector dataHolder) {
//LinkedList<String> row = new LinkedList<>;
Connection conn = null;
Vector row = new Vector();
for (int i = 0; i < dataHolder.size(); i++) {
Vector cellStoreVector = (Vector) dataHolder.elementAt(i);
for (int j = 0; j < cellStoreVector.size(); j++) {
HSSFCell myCell = (HSSFCell) cellStoreVector.elementAt(j);
String stringCellValue = myCell.toString();
System.out.print(stringCellValue + "\t");
try{
Statement sta = conn.createStatement();
String table = "INSERT INTO ashok (username) VALUES('" +stringCellValue+ "')";
System.out.println(table);
sta.executeUpdate(table);
sta.close();
} catch (Exception ex) {
System.out.println("Error in appending " + ex.getMessage());
}
}
}
}
@SuppressWarnings("static-access")
public static void main(String[] args) {
String fileName = "test.xls";
Vector dataHolder = ReadCSV(fileName);
printCellDataToConsole(dataHolder);
try
{
ReadExcelFile objConn=new ReadExcelFile();
objConn.connect();
objConn.ReadCSV(fileName);
objConn.printCellDataToConsole(dataHolder);
objConn.DisconnectCabinet();
}
catch(Exception e)
{
System.out.println("Error in Main" + e.getMessage());
}
}
}*/
public void readExcelFile(String fileName)
{
/**
* Create a new instance for cellDataList
*/
List cellDataList = new ArrayList();
try
{
/**
* Create a new instance for FileInputStream class
*/
FileInputStream fileInputStream = new FileInputStream(fileName);
/**
* Create a new instance for POIFSFileSystem class
*/
POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
/*
* Create a new instance for HSSFWorkBook Class
*/
HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
HSSFSheet hssfSheet = workBook.getSheetAt(0);
/**
* Iterate the rows and cells of the spreadsheet
* to get all the datas.
*/
Iterator rowIterator = hssfSheet.rowIterator();
while (rowIterator.hasNext())
{
HSSFRow hssfRow = (HSSFRow) rowIterator.next();
Iterator iterator = hssfRow.cellIterator();
List cellTempList = new ArrayList();
while (iterator.hasNext())
{
HSSFCell hssfCell = (HSSFCell) iterator.next();
cellTempList.add(hssfCell);
}
cellDataList.add(cellTempList);
}
}
catch (Exception e)
{
e.printStackTrace();
}
/**
* Call the printToConsole method to print the cell data in the
* console.
*/
printToConsole(cellDataList);
}
/**
* This method is used to print the cell data to the console.
* @param cellDataList - List of the data's in the spreadsheet.
*/
public void printToConsole(List cellDataList)
{
for (int i = 0; i < cellDataList.size(); i++)
{
List cellTempList = (List) cellDataList.get(i);
for (int j = 0; j < cellTempList.size(); j++)
{
HSSFCell hssfCell = (HSSFCell) cellTempList.get(j);
String stringCellValue = hssfCell.toString();
System.out.print(stringCellValue + "\t");
System.out.println();
try{
Statement sta = conn.createStatement();
String table = "INSERT INTO ashok (username) VALUES('" +stringCellValue+ "')";
System.out.println(table);
sta.executeUpdate(table);
sta.close();
} catch (Exception ex) {
System.out.println("Error in appending " + ex.getMessage());
}
} }
}
public static void main(String[] args)
{
String fileName ="test.xls";
new ReadExcelFile().readExcelFile(fileName);
try
{
ReadExcelFile objConn=new ReadExcelFile();
objConn.connect();
objConn.readExcelFile(fileName);
// objConn.printToConsole(null);
objConn.DisconnectCabinet();
}
catch(Exception e)
{
System.out.println("Error in Main" + e.getMessage());
}
}
}
this is my coding
