• Post Reply Bookmark Topic Watch Topic
  • New Topic

Excel file read  RSS feed

 
Alka ravi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
jatan bhavsar
Ranch Hand
Posts: 299
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Alka,

Where the problem exactly? Please paste the stack trace and also use the code tag.

Regards
Jatan
 
Alka ravi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
import java.io.*;
import java.sql.*;

import java.util.Vector;
import java.util.Iterator;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.regex.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Insert {
public static void main( String [] args ) {
String fileName="C:\\Downloads\\test.xls";
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();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
}catch (Exception e){e.printStackTrace(); }
return cellVectorHolder;
}
private static int saveToDatabase(Vector dataHolder)
{
int i = 0;

try
{
String q = null;
String sql = "";

Class.forName("org.postgresql.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test","postgres", "root");

PreparedStatement stmt = null;

String columnName = "";

Vector column = (Vector) dataHolder.elementAt(0);
for (int j=0; j < column.size(); j++)
{
HSSFCell myCell = (HSSFCell)column.elementAt(j);
columnName += myCell.toString();
if (j +1 < column.size())
columnName += ",";

}

sql = "Select " + columnName + " from login";

System.out.println(sql);
stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();

ResultSetMetaData metaData = rs.getMetaData();

sql = "insert into login (" + columnName + ") values (";

for (int j=0; j < column.size(); j++)
{
sql += "?";
if (j+1 < column.size())
sql += ",";
}

sql += ")";

System.out.println(sql);
stmt = conn.prepareStatement(sql);
boolean insertData = true;

DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy hh:mm:ss");
for (i=1; i < dataHolder.size(); i++)
{
Vector cellStoreVector=(Vector)dataHolder.elementAt(i);

for (int j=0; j < cellStoreVector.size();j++)
{
String value = null;

HSSFCell cell = (HSSFCell)cellStoreVector.elementAt(j);

switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = dateFormat.format(cell.getDateCellValue());
} else {
value = String.valueOf(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
value = cell.getStringCellValue();
break;
default:
}


if (value.equalsIgnoreCase("") || value == null) {
insertData = false;
break;
}

System.out.println(value);
System.out.println(" Columnn Type = " + metaData.getColumnType(cell.getColumnIndex() + 1));

switch (metaData.getColumnType(cell.getColumnIndex() + 1))
{
case 91:
case java.sql.Types.DATE:
java.util.Date date;
date = (java.util.Date)dateFormat.parse(value);
java.sql.Date sqlDate = new java.sql.Date(date.getTime());
stmt.setDate(cell.getColumnIndex()+1, sqlDate);
break;

case 93:
case java.sql.Types.TIMESTAMP:
date = (java.util.Date)dateFormat.parse(value);
java.sql.Timestamp timeStampDate = new Timestamp(date.getTime());
stmt.setTimestamp(cell.getColumnIndex()+1, timeStampDate);
break;

default:

System.out.println("Switch case failed for datatype " + metaData.getColumnType(cell.getColumnIndex() + 1));
}
}

if (insertData)
stmt.executeUpdate();
}

rs.close();
stmt.close();
conn.close();

return i;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
};
 
Alka ravi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
metaData.getColumnType(cell.getColumnIndex() + 1))

returns wrong value for some types when running with postgres. Why?
 
Paul Clapham
Sheriff
Posts: 22185
38
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alka ravi wrote:metaData.getColumnType(cell.getColumnIndex() + 1))

returns wrong value for some types when running with postgres. Why?


Which types? And what is wrong about their values?
 
Rishi Shah
Ranch Hand
Posts: 43
Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just a note, it's not good practice to call #newInstance() on a class. It's better to get the constructor, and get a new instance of that.

This is because the former propagates any exception thrown by the nullary constructor, including a checked exception. Use of this method effectively bypasses the compile-time exception checking that would otherwise be performed by the compiler. The Constructor.newInstance method avoids this problem by wrapping any exception thrown by the constructor in a (checked) InvocationTargetException.
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rishi Shah wrote:Just a note, it's not good practice to call #newInstance() on a class. It's better to get the constructor, and get a new instance of that.

More saliently, neither newInstance() nor the constructor should be called when loading the DB driver. Calling Class.forName is sufficient; everything else is unnecessary fluff.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!