www.webdeveloper.com
Results 1 to 4 of 4

Thread: import data from Excel to mysql db using Java

Hybrid View

  1. #1
    Join Date
    Sep 2011
    Posts
    15

    import data from Excel to mysql db using Java

    The attached image is the example of .xls file that I want to import those data to mysql database using Java. Any help I get will be very much appreciated.

  2. #2
    Join Date
    Oct 2005
    Posts
    843
    You'll need a couple of things to do this and I'm going to suggest you approach this in multiple steps.

    First you need to read in your excel file with java, for that there is the Apache POI Project which allows you to do so.

    Once you have it read in you'll then need to transfer it into the database. Depending how you read it in there are multiple ways to do so. The most basic is a simple JDBC connection. A basic Java JDBC tutorial can be found here

    There's also no attached image showing the excel table you wish to read in. (Least from what I could see).

    This kind of outlines the technologies you can use to accomplish this, Google is your friend in finding tutorials for each.

    If you need more information just let me know and I can try and help you out. Same goes if you're having trouble with the code, post what you got and explain the difficulties you are having and I can try and provide some guidance.

  3. #3
    Join Date
    Sep 2011
    Posts
    15
    The following is the code that I used to read the data from the excel but when I run the code I got this 'H11*0.07' instead of getting the value. I attached the xls image again.

    And I also want to insert these values to mysql database. Could you please kindly guide me how can I assign the values to a variable and insert these all data into the database. Really thanks a lot. I'm still very new to Java. Thanks for your kindly reply.

    Code:
    import java.io.FileInputStream;
    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 {
    
        public static void main(String[] args) {
    
            String fileName = "C:\\sample.xls";
            Vector dataHolder = ReadCSV(fileName);
            printCellDataToConsole(dataHolder);
        }
    
        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;
        }
    
        private static void printCellDataToConsole(Vector dataHolder) {
            //LinkedList<String> row = new LinkedList<>;
            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");
                }
                System.out.println();
            }
        }
    }
    Attached Images Attached Images

  4. #4
    Join Date
    Oct 2005
    Posts
    843
    All be it delayed, you can read here for Formula Evaluation so that instead of "H11*0.07" you would get 53.86.

    There are many different ways to store stuff in databases, try something and if you can't get it working post what you have.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles