import data from Excel to mysql db using Java

This site is best viewed in a modern browser with JavaScript enabled.

import data from Excel to mysql db using Java

pote86

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.

welsh

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.

pote86

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.

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();
    }
}
}

welsh

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.