java操作Excel之POI（6）使用POI实现使用模板批量添加数据

Z时代
2024-01-10
分类：综合

java

action是用struts2写的；前端界面easyUI写的，

前端：

 1 <!DOCTYPE html>
 2 <html>
 3 <head>
 4     <meta charset="UTF-8">
 5     <title>Basic DataGrid - jQuery EasyUI Demo</title>
 6     <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css">
 7     <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/icon.css">
 8     <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.min.js"></script>
 9     <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.easyui.min.js"></script>
10     <script type="text/javascript" src="jquery-easyui-1.3.3/locale/easyui-lang-zh_CN.js"></script>
11     <script>
12         //批量导入数据，打开dialog
13         function openUploadFileDialog(){
14             $("#dlg2").dialog('open').dialog('setTitle','批量导入数据');
15         }
16         
17         //下载批量导入模板
18         //userExporTemplate.xls位于WebContent/template/下面
19         //一个纯的js来下载模板
20         function downloadTemplate(){
21             window.open('template/userExporTemplate.xls');
22         }
23         
24         //上传Excel文件，交给后台解析
25         //其中uploadForm的action=user!upload
26         function uploadFile(){
27             $("#uploadForm").form("submit",{
28                 success:function(result){
29                     var result=eval('('+result+')');
30                     if(result.errorMsg){
31                         $.messager.alert("系统提示",result.errorMsg);
32                     }else{
33                         $.messager.alert("系统提示","上传成功");
34                         $("#dlg2").dialog("close");
35                         $("#dg").datagrid("reload");
36                     }
37                 }
38             });
39         }
40     </script>
41 </head>
42 <body>
43     <!-- 一排操作按钮 -->
44     <div id="toolbar">
45         <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-import" plain="true" onclick="openUploadFileDialog()">用模版批量导入数据</a>
46     </div>
47     
48     <!-- 批量导入数据模板Excel 的dialog -->
49     <div id="dlg2" class="easyui-dialog" style="width:400px;height:180px;padding:10px 20px"
50             closed="true" buttons="#dlg-buttons2">
51         <form id="uploadForm" action="user!upload" method="post" enctype="multipart/form-data">
52             <table>
53                 <tr>
54                     <td>下载模版：</td>
55                     <td><a href="javascript:void(0)" class="easyui-linkbutton"  onclick="downloadTemplate()">导入模版</a></td>
56                 </tr>
57                 <tr>
58                     <td>上传文件：</td>
59                     <td><input type="file" name="userUploadFile"></td>
60                 </tr>
61             </table>
62         </form>
63     </div>
64     
65     <div id="dlg-buttons2">
66         <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="uploadFile()">上传</a>
67         <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg2').dialog('close')">关闭</a>
68     </div>
69 </body>
70 </html>

View Code

后端：

UserAction中upload方法：

 1 public class UserAction extends ActionSupport{
 2 
 3     private File userUploadFile;    //用来接收上传的文件
 4 
 5     public File getUserUploadFile() {
 6         return userUploadFile;
 7     }
 8     public void setUserUploadFile(File userUploadFile) {
 9         this.userUploadFile = userUploadFile;
10     }
11 
12     /**
13     *    上传文件，解析Excel
14     */
15     public String upload() throws Exception{
16         POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(userUploadFile));
17         HSSFWorkbook wb = new HSSFWorkbook(fs);
18         HSSFSheet hssfSheet =  wb.getSheetAt(0);     //获取第一个sheet页
19         if(hssfSheet != null){
20             for(int rowNum=1; rowNum<=hssfSheet.getLastRowNum(); rowNum++){
21                 HSSFRow hssfRow = hssfSheet.getRow(rowNum);
22                 if(hssfRow == null){
23                     continue;
24                 }
25                 User user = new User();
26                 user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
27                 user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
28                 user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
29                 user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
30                 Connection conn = null;
31                 try{
32                     con=dbUtil.getCon();
33                     userDao.userAdd(con, user);
34                 }catch(Exception e){
35                     e.printStackTrace();
36                 }finally{
37                     dbUtil.closeCon(con);
38                 }
39             }
40         }
41         JSONObject result=new JSONObject();
42         result.put("success", "true");
43         ResponseUtil.write(ServletActionContext.getResponse(), result);
44         return null;
45     }
46 
47 }

对单元格进行分类处理（String、boolean、number）的ExcelUtil：

 1 /**
 2 *    处理Excel的util
 3 */
 4 public class ExcelUtil{
 5 
 6     /**
 7     *    传入Cell，根据Cell的类型转化，返回的都是String
 8     */
 9     public static String formatCell(HSSFCell hssfCell){
10         if(hssfCell == null){
11             return "";
12         }else{
13             if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){    
14                 return String.valueOf(hssfCell.getBooleanCellValue());
15             }else if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
16                 return String.valueOf(hssfCell.getNumericCellValue());
17             }else{
18                 return String.valueOf(hssfCell.getStringCellValue());
19             }
20         }
21     } 
22 
23 }