• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to import data from Excel sheet to database using java??

 
Greenhorn
Posts: 25
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


please help me out with this. how to import data from excel sheet to database in java i tried different codes but it is not working . please help me friends



thanks in advance
 
Ranch Hand
Posts: 296
Eclipse IDE Firefox Browser Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What have you tried? and what the error you are getting?
Please post the details.
Thanks
 
N. Ganapathy
Greenhorn
Posts: 25
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I fetched the data from excel sheet while i trying to export to database it is displayin as "error in appending"
 
Sumit Patil
Ranch Hand
Posts: 296
Eclipse IDE Firefox Browser Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post the code along with the exception message?
 
N. Ganapathy
Greenhorn
Posts: 25
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
/*
* 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
 
N. Ganapathy
Greenhorn
Posts: 25
Netbeans IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Session ID of this connection:1272716213
Date
Error in appending null
kumar
Error in appending null
appdata
Error in appending null
raja
Error in appending null
approver V1
Error in appending null
naveen
Error in appending null
APS V1
Error in appending null
as
Error in appending null
ce
Error in appending null
ce V1
Error in appending null
ce V1(2012_3_26_16_58_50_920)
Error in appending null
CEASCANOC20JOB
Error in appending null
Classificator
Error in appending null
Custom Reports
Error in appending null

Cabinet DisConnected Successfully and session id is:1272716213



This is my output.
 
Saloon Keeper
Posts: 7585
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Print the full exception stack trace instead of just the error message. That way you'll know in which line of code the error occurred.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic