java把excel資料大量匯入到資料庫

標籤：excel   資料庫   excel匯入資料庫   

java把excel資料大量匯入到資料庫中，java匯入excel資料代碼如下

1.    public List<Choice> GetFromXls(String xlsname){

2.        

3.        List<Choice> choices = new ArrayList<Choice>();   

4.        Choice choice=null;

5.        try {   

6.            java.io.File file=new java.io.File(xlsname);

7.            Workbook book = Workbook.getWorkbook(file);  

8.            

9.            // 獲得第一個sheet,預設有三個   

10.           Sheet sheet = book.getSheet(0);   

11.           // 一共有多少行多少列資料   

12.           int rows = sheet.getRows();   

13.           int columns = sheet.getColumns();  

14.           for (int i = 1; i <rows; i++) {   

15.               boolean hasText = false;   

16.               // 過濾掉沒有常值內容的行   

17.               for (int j = 0; j < columns; j++)   

18.                   if (sheet.getCell(j, i).getContents().length()!=0) {   

19.                       hasText = true;   

20.                       break;   

21.                   } 

22.                   if (hasText) {  

23.                       try {

24.                           

25.                           choice = new Choice();   

26.                           choice.setTypeid(Integer.parseInt(sheet.getCell(0, i).getContents()));

27.                           choice.setCataid(sheet.getCell(1, i).getContents());

28.                           choice.setTitle(sheet.getCell(2, i).getContents());

29.                           choice.setA(sheet.getCell(3, i).getContents());   

30.                           choice.setB(sheet.getCell(4, i).getContents());   

31.                           choice.setC(sheet.getCell(5, i).getContents());   

32.                           choice.setD(sheet.getCell(6, i).getContents());   

33.                           choice.setAnswer(sheet.getCell(7, i).getContents());   

34.                           choices.add(choice); 

35.                       } catch (Exception e) {

36.                           // TODO: handle exception

37.                           e.printStackTrace();

38.                       }

39.                       

40.                   }   

41.           }   

42.           book.close();   

43.       } catch (Exception e) {   

44.           e.printStackTrace();   

45.       }   

46.       return choices;  

47.   }

48.   public int batchInsert(final List<Choice> q,String xlsname) {   

49.       // TODO Auto-generated method stub   

50.       final List<Choice> choices = GetFromXls(xlsname);   

51.       final int size = choices.size(); 

52.       

53.       int result=0;

54.       //此處應當擷取題型進行插入資料庫

55.       String sql = "insert into choice(title,typeid,cataid,A,B,C,D,Answer) "  

56.           + "values(?,?,?,?,?,?,?,?)";  

57.       

58.       conn=jdbconn.getConn();   

59.       try{ 

60.           ps=conn.prepareStatement(sql);

61.           for(int i=0;i<size;i++){

62.               Choice choice = choices.get(i);   

63.               ps.setString(1, choice.getTitle());

64.               ps.setInt(2,choice.getTypeid());

65.               ps.setString(3, choice.getCataid()); 

66.               ps.setString(4, choice.getA());   

67.               ps.setString(5, choice.getB());   

68.               ps.setString(6, choice.getC());   

69.               ps.setString(7, choice.getD());   

70.               ps.setString(8, choice.getAnswer());

71.               ps.executeUpdate();

72.               result++;

73.           } 

74.       } catch (SQLException e) {

75.           e.printStackTrace();

76.       } finally {

77.           jdbconn.closeDB(conn);

78.       }

79.       return result;

80.   } 

java把excel資料大量匯入到資料庫