Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Excel file read

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Ranch Hand
Posts: 300
Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
metaData.getColumnType(cell.getColumnIndex() + 1))

returns wrong value for some types when running with postgres. Why?
 
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 43
Mac Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
World domination requires a hollowed out volcano with good submarine access. Tiny ads are optional.
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic