public class SimpleExcelReadExample {
static Connection con1 = null;
static Connection con3 = null;
static PreparedStatement preparedstatement = null;
static ResultSet resultset = null;
int j = 0;
public static void main(String[] args) {
String fileName = "D:/Excel/Report.xls";
Cleartables.table_daily_report();
Vector dataHolder = read(fileName);
saveToDatabase(dataHolder);
}
public static Vector read(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();
//System.out.println("read method"+myCell);
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
} catch (Exception e) {
e.printStackTrace();
}
return cellVectorHolder;
}
private static void saveToDatabase(Vector dataHolder)
{
for (int i=0;i<dataHolder.size(); i++)
{
Vector cellStoreVector=(Vector)dataHolder.get(i);
issueid = ((HSSFCell)cellStoreVector.get(0)).toString();
System.out.println(issueid);
}
So what happening is getting all the values from excel sheet including headerIf that's the case, then only the first row is a header, so why don't you simply ignore the first row?
private static void saveToDatabase(Vector dataHolder)
{
try
{
String field1= "";
String field2= "";
String field3= "";
String field4= "";
String field5= "";
String field6= "";
String field7= "";
String field8= "";
String field9= "";
String field10= "";
String field11= "";
String field12= "";
String field13= "";
String field14= "";
for (int i=1;i<dataHolder.size(); i++)
{
Vector cellStoreVector=(Vector)dataHolder.get(i);
field1= ((HSSFCell)cellStoreVector.get(0)).toString();
field2=((HSSFCell)cellStoreVector.get(1)).toString();
field3=((HSSFCell)cellStoreVector.get(2)).toString();
field4=((HSSFCell)cellStoreVector.get(3)).toString();
field5= ((HSSFCell)cellStoreVector.get(4)).toString();
field6= ((HSSFCell)cellStoreVector.get(5)).toString();
field7= ((HSSFCell)cellStoreVector.get(6)).toString();
field8= ((HSSFCell)cellStoreVector.get(7)).toString();
field9= ((HSSFCell)cellStoreVector.get(8)).toString();
field10= ((HSSFCell)cellStoreVector.get(9)).toString();
field11= ((HSSFCell)cellStoreVector.get(10)).toString();
field12= ((HSSFCell)cellStoreVector.get(12)).toString();
field13= ((HSSFCell)cellStoreVector.get(13)).toString();
field14= ((HSSFCell)cellStoreVector.get(14)).toString();
String sql2 = "INSERT INTO Daily_Report (field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
preparedstatement = con3.prepareStatement(sql2);
//Statement stmt = con3.createStatement();
System.out.println("1");
preparedstatement.setString(1, field1);
preparedstatement.setString(2, field2);
preparedstatement.setString(3, field3);
preparedstatement.setString(4, field4);
preparedstatement.setString(5, field5);
preparedstatement.setString(6, field6);
preparedstatement.setString(7, field7);
preparedstatement.setString(8, field8);
preparedstatement.setString(9, field9);
preparedstatement.setString(10, field10);
preparedstatement.setString(11, field11);
preparedstatement.setString(12, field12);
preparedstatement.setString(13, field13);
preparedstatement.setString(14, field14);
preparedstatement.executeUpdate();
}
}
catch(Exception e){}
}
private void printToConsole(List cellDataList) {
for (int i = 1; i < cellDataList.size(); i++) {
List cellTempList = (List) cellDataList.get(i);
System.out.println(cellTempList);
}
}
String sql2 = "INSERT INTO Daily_Report (field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
preparedstatement = con3.prepareStatement(sql2);
//Statement stmt = con3.createStatement();
System.out.println("1");
preparedstatement.setString(1, field1);
preparedstatement.setString(2, field2);
preparedstatement.setString(3, field3);
preparedstatement.setString(4, field4);
preparedstatement.setString(5, field5);
preparedstatement.setString(6, field6);
preparedstatement.setString(7, field7);
preparedstatement.setString(8, field8);
preparedstatement.setString(9, field9);
preparedstatement.setString(10, field10);
preparedstatement.setString(11, field11);
preparedstatement.setString(12, field12);
preparedstatement.setString(13, field13);
preparedstatement.setString(14, field14);
preparedstatement.executeUpdate();
See the below code for retrive the value from excel sheet one by one.I have column header and values like database.But here there is no option to retrive value as header.
So what happening is getting all the values from excel sheet including header.But already header in data table.
How to avoid header while reading the excel sheet ?
private static void saveToDatabase(Vector dataHolder)
{
for (int i=0;i<dataHolder.size(); i++)
{
Vector cellStoreVector=(Vector)da
issueid = ((HSSFCell)cellStoreVector
System.out.println(issueid
}