将Excel文件数据导入到数据库表中(Java版)

将Excel文件数据导入到数据库表中（Java版）


Java代码
1.<%@page language="java" contentType="text/html; charset=utf-8" %>
2.<%@page import="java.io.File"%>
3.<%@page import="java.io.InputStream"%>
4.<%@page import="java.io.FileInputStream"%>
5.<%@page import="java.util.Vector"%>
6.<%@page import="java.util.Iterator"%>
7.<%@page import="https://www.360docs.net/doc/019712769.html,ermodel.HSSFWorkbook"%>
8.<%@page import="https://www.360docs.net/doc/019712769.html,ermodel.HSSFSheet"%>
9.<%@page import="https://www.360docs.net/doc/019712769.html,ermodel.HSSFRow"%>
10.<%@page import="https://www.360docs.net/doc/019712769.html,ermodel.HSSFCell"%>
11.<%@page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
12.<%@page import="https://www.360docs.net/doc/019712769.html,mon.PubUtil"/>
13.<%@page import="com.fuyun.hp.hibernate.mapping.RadioType"%>
14.<%@page import="com.fuyun.hp.hibernate.mapping.RadioTypeDAO"%>
15.<%@page import="https://www.360docs.net/doc/019712769.html,mon.SpringContext"/>
16.<%@page import="org.springframework.orm.hibernate3.HibernateTransactionManager"/>
17.<%@page import="org.springframework.transaction.TransactionDefinition"/>
18.<%@page import="org.springframework.transaction.TransactionStatus"/>
19.<%@page import="org.springframework.transaction.support.DefaultTransactionDefinition"/>
20.<%
21. //出于考虑显示格式,实际运行时,请将代码中的全角空格转换为半角空格
22. //代码中的PubUtil.nvl(Object)方法,当Object为null时返回空串(也就是类似Oracle中的nvl方法)
23. request.setCharacterEncoding("utf-8");
24. File tempPath = new File(getServletContext().getRealPath("/")
25. + AdsUtil.TEMP_UPLOAD_PATH);
26. if (!tempPath.exists())
27. return;
28.
29. String fileName = "radioTree.xls";
30. fileName = tempPath.toString() + "\\" + fileName;
31. InputStream inp = new FileInputStream(fileName);
32. HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
33. HSSFSheet sheet = wb.getSheetAt(0);
34. RadioType rt;
35. RadioTypeDAO rtDao = RadioTypeDAO.getInstance();
36.
37. HibernateTransactionManager tManager = SpringContext.getTransactionManager();
38. TransactionDefinition td = new DefaultTransactionDefinition();
39. TransactionStatus ts = tManager.getTransaction(td);
40.
41. for (Iterator rit = (Iterator)sheet.rowIterator(); rit.hasNext(); ) {
42. HSSFRow row = rit.next();
43. boolean existFlag = true;
44. for(short index = 0;index < row.getPhysicalNumberOfCells();index ++){
45. HSSFCell cell = row.getCell(index);
46. if(index == row.getLastCellNum() && cell == null)
47. existFlag = false;
48. else if(index < row.getLastCellNum() && cell == null)
49.

　continue;
50. else
51. break;
52. }
53. if(existFlag == false){
54. break;
55. }
56. else{
57. Vector values = new Vector();
58. rt = null;
59. try{
60. if(row.getRowNum() == 0)//从第2行开始读取
61. continue;
62. for (int index = 0; index < 5; index ++) {
63. HSSFCell cell = row.getCell(new Short(index + ""));
64. if(cell == null)
65. values.add("");
66. else{
67. if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
68. values.add(String.format("%.0f", cell.getNumericCellValue()));
69. }
70. else
71. values.add(PubUtil.nvl(cell.getStringCellValue()));
72. }
73. }
74.
75. if("".equals(PubUtil.nvl(values.get(0)))){
76. rt = new RadioType();
77. }
78. else{
79. rt = rtDao.get(values.get(0));
80. }
81. rt.setId(values.get(0));//id
82. rt.setParentId(PubUtil.nvl(values.get(1)));//上级id
83. rt.setName(PubUtil.nvl(values.get(2)));//名称
84. rt.setType(PubUtil.nvl(values.get(3)));//类型
85. rt.setOrderIndex(Integer.parseInt(PubUtil.nvl(values.get(4))));//排序
86.
87. if("".equals(PubUtil.nvl(values.get(0)))){
88. rtDao.save(rt);
89. }
90. else{
91. rtDao.update(rt);
92. }
93. out.println(rt);
94. }
95. catch(Exception e){
96. tManager.rollback(ts);
97. e.printStackTrace();
98. out.print("{success: false, info:'导入失败！'}");
99. return;
100. }
101. }
102. }
103. https://www.360docs.net/doc/019712769.html,mit(ts);
104. out.print("{success: true}");
105. %>