Results 1 to 16 of 16
  1. #1
    lavanya82 is offline Member
    Join Date
    Feb 2009
    Posts
    15
    Rep Power
    0

    Smile Hi i need a code for excel sheet upload to database

    Hi ,

    i need a code sample for Excel sheet data upload to db2 .i.e master table in database.each excel sheet data has to be entied in single table.Can some one help me please.

  2. #2
    dswastik is offline Senior Member
    Join Date
    Dec 2008
    Location
    Kolkata
    Posts
    283
    Rep Power
    6

    Default

    You can use Apache POI apis to read data from excel and use jdbc to store data in database.

  3. #3
    lavanya82 is offline Member
    Join Date
    Feb 2009
    Posts
    15
    Rep Power
    0

    Default

    can you provide me a sample code and steps to proceed further with it.

  4. #4
    josyulasrk is offline Member
    Join Date
    Feb 2009
    Location
    HYDERABAD
    Posts
    3
    Rep Power
    0

    Default

    Create a reference with Apache POI (used for reading and writing data to the excel) and read / write the data from the excel and from that using jdbc you insert the data to the db.

  5. #5
    josyulasrk is offline Member
    Join Date
    Feb 2009
    Location
    HYDERABAD
    Posts
    3
    Rep Power
    0

    Default

    Use this link

    poi.apache.org/apidocs/index.html

    You willget more idea for your question.

    Srikanth.

  6. #6
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    use POI API that will read your excel data
    read them into some Vector or DataStructure
    and then you can add them to the database
    its very simple
    i have done this...
    if you need the code help then can ask
    thanks
    /\/

  7. #7
    lavanya82 is offline Member
    Join Date
    Feb 2009
    Posts
    15
    Rep Power
    0

    Default

    it would be nice if i get code bec i am new to this concept and how to use that code information, it will help me a lot .

  8. #8
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    in my code i am using a class to upload the file...
    right now i dont have that class...
    if you are able to upload the file the i can tell you how to read the excel file and insert to database.
    give me your mail id so that i can give u the sample

    thanks
    /\/

  9. #9
    lavanya82 is offline Member
    Join Date
    Feb 2009
    Posts
    15
    Rep Power
    0

    Default

    The first file has the following code:-
    import java.io.FileInputStream;
    import java.io.IOException;
    public class ExcelReader {
    public static ExcelReader getInstance(String sDocPath)
    throws IOException
    {
    return new ' ExcelReader(sDocPath);
    }

    private ExcelReader(String sDocPath) throws IOException
    {
    this.instance = new ExcelReader();
    this.docPath = sDocPath;
    this.document = new POIFSFileSystem(new FileInputStream(docPath));
    this.workbook = new HSSFWorkbook(document);

    }

    }

  10. #10
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    yor this code is uploading the sheet at yor server?

  11. #11
    dswastik is offline Senior Member
    Join Date
    Dec 2008
    Location
    Kolkata
    Posts
    283
    Rep Power
    6

    Default

    //this is how you read the contents of excel file using poi apis

    Java Code:
     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.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import java.util.*;
    
    import java.io.*;
    
    class ExcelReader{
    	public static void main(String args[]){
    		try{
    			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("c:\\Book1.xls"));		
    			HSSFWorkbook wb = new HSSFWorkbook(fs);
    			HSSFSheet sheet = wb.getSheetAt(0);
    			Iterator rows=sheet.rowIterator();
                            while(rows.hasNext()){
                                HSSFRow row=(HSSFRow)rows.next();
                                Iterator cells=row.cellIterator();
                                while(cells.hasNext()){
                                    HSSFCell cell=(HSSFCell)cells.next();
                                    int cellType=cell.getCellType();
                                    if(cellType==HSSFCell.CELL_TYPE_STRING){
                                        HSSFRichTextString strData=cell.getRichStringCellValue();
                                        System.out.println("String data="+strData.getString());
                                    }
                                    else if(cellType==HSSFCell.CELL_TYPE_NUMERIC){
                                        double data=cell.getNumericCellValue();
                                        System.out.println("Numeric data="+data);
                                    }
                                }
                            }
    		}
    		catch(Exception e){}
    	}
    }
    However to run this code few steps are needed to be performed, you first need to download the poi library from apache.

    Click the following link
    Facebook Mirror | Index of /poi/release/bin

    and from there download poi-bin-3.2-FINAL-20081019.zip

    now extract this file,after extracting you should get a file poi-3.2-FINAL-20081019.jar

    change your classpath settings so that it points to the above file. Now compile the code and execute.

  12. #12
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    having iterator will cause blank/null value rows into the database


    Java Code:
    ExcelFile = new POIFSFileSystem(new FileInputStream(sEXCEL_PATH));
    				workBook = new HSSFWorkbook (ExcelFile);
    				sheet    = workBook.getSheetAt (0);
    				HSSFRow hssfRow = sheet.getRow(iSTART_ROW);
    				HSSFCell cell = null;
    				
    				int cellType = 3; // defaul type is blank
    				String value = "";
    				
    				// SKIPING THREE ROWS FOR TOP HEADER
    				rows.next ();
    				rows.next ();
    				rows.next ();
    				int NoOfCols = 36;
    				String cellValue[] = new String[NoOfCols];
    				boolean isBlankRow = false;
    				int iCellType;
    				DecimalFormat dformat = new DecimalFormat("#####################");
    				while(rows.hasNext())
    				{
    					isBlankRow = false;
    					iCOLS =0;
    					HSSFRow row = rows.next ();
    					for(iCOLS=0;iCOLS<NoOfCols;iCOLS++)
    					{
    						try {
    						cell = row.getCell((short)iCOLS);
    						}
    						catch (NullPointerException e) {
          					}
    						isBlankRow = false;
    						value = "";
    						try {
    							cellType = cell.getCellType(); // 1 means char, 0 means number, 3 means blank
    						  } catch (NullPointerException e) {
    							cellValue[iCOLS] = value;
    							continue;
    						  }
    						if (cellType == cell.CELL_TYPE_STRING) {
    							value = cell.getStringCellValue();
    						} else if (cellType == cell.CELL_TYPE_NUMERIC) {
    							value = ""+cell.getNumericCellValue();
    							value=dformat.format(cell.getNumericCellValue());
    						} else if (cellType == cell.CELL_TYPE_BLANK) {
    							value = cell.getStringCellValue();
    						} else {
    							value = cell.getStringCellValue();
    						}
    						
    						if(iCOLS==0 && value.equals(""))
    						{
    							isBlankRow = true;
    							break;
    							
    						}
    						cellValue[iCOLS] = value;
    						//STORE VALUE FOR STRING ARRAY FOR EACH FOUND RECORD
    						switch (iCOLS)
    						{
    							case 0:
    								strFIRST_NAME[iROWS] = cellValue[iCOLS];
    								break;
    							case 1:
    								strLAST_NAME[iROWS] = cellValue[iCOLS];
    								break;
    							
    						}
    					}
    					if(!isBlankRow)
    						iROWS++;
    						
    				}

  13. #13
    dswastik is offline Senior Member
    Join Date
    Dec 2008
    Location
    Kolkata
    Posts
    283
    Rep Power
    6

    Default

    Agreed, but you can always apply a not null check.

  14. #14
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    that did not work dear
    and thats not a perfect sol...
    if we use data structure in case of array that will make it a perfect code.

  15. #15
    lavanya82 is offline Member
    Join Date
    Feb 2009
    Posts
    15
    Rep Power
    0

    Default

    Thks the code works .

  16. #16
    umeshsin is offline Member
    Join Date
    Jul 2009
    Posts
    1
    Rep Power
    0

    Exclamation help plZ

    hi lavanya... can you please send me the code that worked for you? I need both the codes ie., one for uploading and other for extracting data and putting into a database... thanks a lot in advance :) u.a.singh333 is my id on gmail...

Similar Threads

  1. Upload excel data to access database
    By ravikumar.achi in forum New To Java
    Replies: 20
    Last Post: 01-19-2012, 12:30 PM
  2. SQL Query resultset into Excel Sheet
    By chandpuri in forum JDBC
    Replies: 8
    Last Post: 01-24-2011, 07:53 PM
  3. Save data from Excel sheet to servlet
    By chandu.v in forum Java Servlet
    Replies: 2
    Last Post: 04-10-2008, 02:51 PM
  4. Replies: 0
    Last Post: 03-11-2008, 03:20 PM
  5. How to set the AutoFilter to Excel Sheet with Wither POI or JXL
    By lnarayana_boga in forum Advanced Java
    Replies: 0
    Last Post: 01-29-2008, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •