我正在使用Java POI从excel工作表中读取值，需要插入到数据库中。我有来自excel工作表的字符串，数字和日期值以及第一个字段是列标题。
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.elementAt(i);
for (int j=0; j < cellStoreVector.size();j++)
{
System.out.println("show.....");
HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
Please help me here....How to get the each column values ?
}
最佳答案
try {
FileInputStream file = new FileInputStream(new File("E://Imp/Details.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next();
while(rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while(cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//This will change all Cell Types to String
cell.setCellType(Cell.CELL_TYPE_STRING);
switch(cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
System.out.println("boolean===>>>"+cell.getBooleanCellValue() + "\t");
break;
case Cell.CELL_TYPE_NUMERIC:
break;
case Cell.CELL_TYPE_STRING:
list.add(cell.getStringCellValue());
break;
}
}
name=row.getCell(0).getStringCellValue();
empid=row.getCell(1).getStringCellValue();
add=row.getCell(2).getStringCellValue();
mobile=row.getCell(3).getStringCellValue();
System.out.println(name+empid+add+mobile);
ex.InsertRowInDB(name,empid,add,mobile);
System.out.println("");
}
file.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public void InsertRowInDB(String name,String empid,String add,String mobile) throws SQLException{
Statement stmt=db.con.createStatement();
PreparedStatement ps=null;
String sql="Insert into Employee(Name,EmployeeId,Address,ContactInfo) values(?,?,?,?)";
ps=db.con.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, empid);
ps.setString(3, add);
ps.setString(4, mobile);
ps.executeUpdate();
System.out.println("Values Inserted Successfully");
}
}