• 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 read data from excel i store it in database table

 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 859
IBM DB2 Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
FileInputStream myInput = new FileInputStream(filename);

i have used this in above code
 
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
You're probably using relative paths somewhere along the line. Those don't work in web apps, you need to use absolute paths.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    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
    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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    My bad, carry on!
     
    Ranch Hand
    Posts: 153
    Eclipse IDE Tomcat Server Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
    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

    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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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 Tomcat Server Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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 .








     
    Our first order of business must be this tiny ad:
    a bit of art, as a gift, that will fit in a stocking
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic