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. }