java 编程excel文件导入数据库

html] view plain copy

1. // 以下为单文件上传，即excel  
2.     private File uploadExcel; // 文件  
3.     private String uploadExcelFileName; // 文件名  
4.     private static String[] allowFileType = { "xls", "XLS", "xlsx", "XLSX" }; // 控制文件类型  
5.   
6.     /**  
7.      * excel批量导入  
8.      * */  
9.     public String excelUpload() {  
10.         try {  
11.             if ((uploadExcelFileName == null)  
12.                     || (uploadExcelFileName.equals(""))) {  
13.                 finalMsg = "文件名不能为空！";  
14.             } else {  
15.                 uploadStu(uploadExcel); // 只传入一个excel文件  
16.             }  
17.         } catch (Exception e) {  
18.             e.printStackTrace();  
19.         }  
20.         return SUCCESS;  
21.     }  
22.   
23.     private Sheet sheet;  
24.     private String[][] excelValue;  
25.     private int successRow;  
26.     private StringBuilder msg = new StringBuilder();  
27.     private String finalMsg = "";  
28.   
29.     /**  
30.      * excel导入的总方法  
31.      */  
32.     public void uploadStu(File upload) {  
33.         initExcel(upload); // 初始化  
34.         boolean flag = readExcel(); // 读取  
35.         if (flag) {  
36.             insertIntoDB(); // 插入  
37.         }  
38.     }  
39.   
40.     /**  
41.      * 读取excel文件中数据，保存到sheet对象中  
42.      *   
43.      * @param upload  
44.      */  
45.     private void initExcel(File upload) {  
46.         Workbook rwb = null;  
47.         try {  
48.             InputStream is = new FileInputStream(upload);  
49.             rwb = Workbook.getWorkbook(is);  
50.             sheet = rwb.getSheet(0);  
51.         } catch (Exception e) {  
52.             e.printStackTrace();  
53.         }  
54.     }  
55.   
56.     /**  
57.      * 读取excel中数据进入excelValue数组中  
58.      */  
59.     private boolean readExcel() {  
60.         excelValue = new String[sheet.getRows()][sheet.getColumns()];  
61.         for (int i = 0; i < sheet.getRows(); i++) {  
62.             for (int j = 0; j < sheet.getColumns(); j++) {  
63.                 if (i >= sheet.getRows()) {     //修改    
64.                     return false;  
65.                 }  
66.                   
67.                 Cell cell = sheet.getCell(j, i);  
68.                 if ("".equals(cell.getContents().toString().trim())) {  
69.                     excelValue[i][j] = "";  
70.                 }  
71.                 if (cell.getType() == CellType.LABEL) {  
72.                     LabelCell labelcell = (LabelCell) cell;  
73.                     excelValue[i][j] = labelcell.getString().trim();  
74.                 } else if (cell.getType() == CellType.NUMBER) {  
75.                     excelValue[i][j] = cell.getContents();  
76.                 } else if (cell.getType() == CellType.DATE) {  
77.                     DateCell datcell = (DateCell) cell;  
78.                     excelValue[i][j] = datcell.getDate().toString();  
79.                 } else {  
80.                     excelValue[i][j] = cell.getContents().toString().trim();  
81.                 }  
82.             }  
83.         }  
84.         return true;  
85.     }  
86.   
87.     /**  
88.      * 3.保存进入数据库  
89.      *   
90.      * @param course  
91.      */  
92.     private void insertIntoDB() {  
93.         int excelRows = excelValue.length;  
94.         // 将消息清空  
95.         msg.delete(0, msg.length());  
96.         successRow = 0;  
97.         if (excelValue.length > 1) {  
98.             for (int i = 1; i < excelRows; i++) { // 从第二排开始，第一排为文字说明  
99.                 String[] DBValue = excelValue[i]; // 取一行数据  
100.                 successRow += 1;  
101.                 finalInsert(DBValue);  
102.             }  
103.             finalMsg = "录入结束：成功录入数：" + successRow + msg.toString() + "条";  
104.         } else {  
105.             finalMsg = "excel中无任何数据！";  
106.             System.out.println("excel中没有任何数据");  
107.         }  
108.     }  
109.   
110.     /** 数据插入数据库 **/  
111.     private void finalInsert(String[] DBValue) {  
112.         Building building = new Building();  
113.         building.setBuildingName(DBValue[0]);  
114.         building.setNamePinyin(DBValue[1]);  
115.         building.setBuildingAddress(DBValue[2]);  
116.         building.setSearchPinYin(DBValue[3]);  
117.         building.setLat(Double.valueOf(DBValue[4]));  
118.         building.setLng(Double.valueOf(DBValue[5]));  
119.         building.setBusinessId(Integer.parseInt(DBValue[6]));  
120.         building.setCityId(Integer.parseInt(DBValue[7]));  
121.         buildingService.addBuilding(building);  
122.     }  

[html] view plain copy

1. <form action="/company/building/excelUpload.action" enctype="multipart/form-data" method="post" id="uploadForm"  style="margin-left: 30px;margin-top: 10px">  
2.             <input type="file" name="uploadExcel" id="buildingId"/>  
3.             <input type="button" value="导入" onclick="checkFile()" class="button button-small border-blue"/>  
4.             <span id="tip" style="color:red;margin-left:10px;" ></span>  
5.         </form>  

[html] view plain copy

1. /** excel核对导入的格式 **/  
2.         function checkFile(){  
3.             var fileName = $("#buildingId").val();  
4.             var ext =/\.[^\.]+/.exec(fileName);  
5.             if(ext!='.xls'){  
6.                 $("#tip").html("请导入.xls格式的文件！");      
7.             }else{  
8.                 $("#uploadForm").submit();  
9.             }  
10.         }  
11.         $(function(){  
12.             var result = "<s:property value='finalMsg'/>";  
13.             $("#tip").html(result);   
14.         });