java - 如何使用java POI从Excel表中读取数据插入数据库？

Question

我正在使用 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  ?
                    }

Answer 1

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