aspose file tools*
The moose likes Struts and the fly likes how to read data from excel i store it in database table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Struts
Bookmark "how to read data from excel i store it in database table" Watch "how to read data from excel i store it in database table" New topic
Author

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

vishal gaiky
Greenhorn

Joined: Jun 06, 2012
Posts: 19
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

Joined: Mar 28, 2012
Posts: 859

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

Joined: Jun 06, 2012
Posts: 19
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

Joined: Jun 06, 2012
Posts: 19
FileInputStream myInput = new FileInputStream(filename);

i have used this in above code
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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

Joined: Jan 10, 2002
Posts: 61648
    
  67

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.


    [Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
    vishal gaiky
    Greenhorn

    Joined: Jun 06, 2012
    Posts: 19
    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
    Rancher

    Joined: Sep 21, 2011
    Posts: 2408
    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

    Joined: Jun 06, 2012
    Posts: 19
    hey friends please help me..i m stuck in this problem from last two days..please help me
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61648
        
      67

    My bad, carry on!
    Karn Kumar
    Ranch Hand

    Joined: Aug 06, 2009
    Posts: 146

    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
    Rancher

    Joined: Sep 21, 2011
    Posts: 2408
    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

    Joined: Jun 06, 2012
    Posts: 19
    thank you chetan..i will try your advice and will let you know..thank you once again..
    Karn Kumar
    Ranch Hand

    Joined: Aug 06, 2009
    Posts: 146



    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 .








     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: how to read data from excel i store it in database table