File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Struts and the fly likes How to import data from Excel sheet to database using java?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Struts
Bookmark "How to import data from Excel sheet to database using java??" Watch "How to import data from Excel sheet to database using java??" New topic
Author

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

N. Ganapathy
Greenhorn

Joined: Apr 16, 2012
Posts: 22



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
Sumit Patil
Ranch Hand

Joined: May 25, 2009
Posts: 296

What have you tried? and what the error you are getting?
Please post the details.
Thanks


Thanks & Regards, Sumeet
SCJP 1.4, SCWCD 5, LinkedIn Profile
N. Ganapathy
Greenhorn

Joined: Apr 16, 2012
Posts: 22

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

Joined: May 25, 2009
Posts: 296

Can you post the code along with the exception message?
N. Ganapathy
Greenhorn

Joined: Apr 16, 2012
Posts: 22

/*
* 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

Joined: Apr 16, 2012
Posts: 22

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.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to import data from Excel sheet to database using java??
 
Similar Threads
read data from excel sheet and put it into the postgresql database
how to store values(data) from excel into mysql
regarding excel sheet
accessing excel file from java
importing data from database to excel sheet through JExcel Api