• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to read data from excel i store it in database table

 
vishal gaiky
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello friends... i want to take excel file from user and store its data into database table..how i will achieve it..kindly help me..thank you in advance
 
William P O'Sullivan
Ranch Hand
Posts: 859
Chrome IBM DB2 Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If it's in csv format, not a problem.

If it's pure Excel (.xls, .xlsx) etc. then you will need a processing framework
to extract the row data and insert into a db.

WP
 
vishal gaiky
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this is my action class but i am getting error at FileInputStream myInput = new FileInputStream(filename); in following code as FILENOTFOUNDEXception..help me




public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception
{
String target="";
FileForm myForm=(FileForm)form;
FormFile myFile = myForm.getTheFile();
StoreExcel storeExcel=new StoreExcel();
File file = new File(myFile.getFileName());


// String fileNamee="C:\\Book1.xls";
System.out.println("file name ----"+fileNamee);
String fileName=myFile.getFileName();
/

Vector dataHolder=read(fileName);
saveToDatabase(dataHolder);
return mapping.findForward("success");

}
public Vector read(String fileName)
{
Vector cellVectorHolder = new Vector();
try{


FileInputStream myInput = new FileInputStream(filename);// giving error at this line...filenotfound exception can anybody help me


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;
}
private void saveToDatabase(Vector dataHolder) {
String username="";
String password="";
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 st = myCell.toString();
username=st.substring(0,1);
password=st.substring(0);
}
try{
BaseDao dao=new BaseDao();
Connection conn=(Connection) dao.getConnection();
Statement stat=(Statement) conn.createStatement();
int k=stat.executeUpdate("insert into USER_TABLE(username,password) value('"+username+"','"+password+"')");
System.out.println("Data is inserted");
stat.close();
conn.close();
}
catch(Exception e){}
}
}





}

 
vishal gaiky
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
FileInputStream myInput = new FileInputStream(filename);

i have used this in above code
 
Tim Moores
Bartender
Posts: 2856
46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're probably using relative paths somewhere along the line. Those don't work in web apps, you need to use absolute paths.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64992
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Moores wrote:You're probably using relative paths somewhere along the line. Those don't work in web apps, you need to use absolute paths.

I'm going to have to disagree. But it's likely just a terminology issue...

There are a number of type of relative paths:

  • page-relative path: is relative to the current URL, very bad in web apps. This is likely what Tim was referring to.
  • server-relative path: starts with the context path. This is what you want to use. They are not fragile and do not depend upon the current URL.

  • An absolute path is one that starts with the protocol (http:// or https://) and you do not want to use these for resources in the same web app -- just for resources external to the web app.
     
    vishal gaiky
    Greenhorn
    Posts: 19
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    i tried with absolute path also but it didnt work..actully i want to take excel file from user in jsp page using upload file option
     
    Tim Moores
    Bartender
    Posts: 2856
    46
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Actually, I wasn't referring to either sort of path, both of which are about URLs. This problem is about file systems paths, where there's only one kind of relative path (relative to the current directory - a concept which doesn't make sense in web apps, and is somewhat unpredictable in its result if used anyway).
     
    vishal gaiky
    Greenhorn
    Posts: 19
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    hey friends please help me..i m stuck in this problem from last two days..please help me
     
    Bear Bibeault
    Author and ninkuma
    Marshal
    Pie
    Posts: 64992
    86
    IntelliJ IDE Java jQuery Mac Mac OS X
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    My bad, carry on!
     
    Karn Kumar
    Ranch Hand
    Posts: 153
    Eclipse IDE Java Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Vishal,

    There is API provide by apache to read excel file .

    You can use that one , name of the file is poi-3.5-FINAL-20090928.jar . I used it recently , even you will get program on google to read the excel and once you will get that excel row values , you can create statement and execute it so that it will be

    stored in the database.

    I recently used it , but mine was Java Class where in main method i am hardcoding xls file name from where it is reading data.

    In case of JSP you can use FILE tag and form should have enctype="multipart/form-data" .

    You can refer http://www.tutorialspoint.com/jsp/jsp_file_uploading.htm . I uploaded JSP/HTML files on server same as what you are require in case of reading excel in past , but right now i dont have code .

    Do some google if will not get i will send some Excel read code i have.

    -Chetan
     
    Tim Moores
    Bartender
    Posts: 2856
    46
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Chetan Dorle wrote:name of the file is poi-3.5-FINAL-20090928.jar

    Why are you advocating using a version that has been outdated for more than 2 years? POI is at version 3.8 now, and LOTS of bugs have been fixed and LOTS of improvements made since version 3.5.

    you will get program on google to read the excel

    No need to do that, the POI web site has lots of examples.
     
    vishal gaiky
    Greenhorn
    Posts: 19
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    thank you chetan..i will try your advice and will let you know..thank you once again..
     
    Karn Kumar
    Ranch Hand
    Posts: 153
    Eclipse IDE Java Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator


    Thanks Tim for correcting , actually i was also not having idea that new version is released and i am using old version , now I will also use new version .








     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic