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

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

前端：

 <!DOCTYPE html>

 <html>

 <head>

     <meta charset="UTF-8">

     <title>Basic DataGrid - jQuery EasyUI Demo</title>

     <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css">

     <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/icon.css">

     <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.min.js"></script>

     <script type="text/javascript" src="jquery-easyui-1.3.3/jquery.easyui.min.js"></script>

     <script type="text/javascript" src="jquery-easyui-1.3.3/locale/easyui-lang-zh_CN.js"></script>

     <script>

         //批量导入数据，打开dialog

         function openUploadFileDialog(){

             $("#dlg2").dialog('open').dialog('setTitle','批量导入数据');

         }

         //下载批量导入模板

         //userExporTemplate.xls位于WebContent/template/下面

         //一个纯的js来下载模板

         function downloadTemplate(){

             window.open('template/userExporTemplate.xls');

         }

         //上传Excel文件，交给后台解析

         //其中uploadForm的action=user!upload

         function uploadFile(){

             $("#uploadForm").form("submit",{

                 success:function(result){

                     var result=eval('('+result+')');

                     if(result.errorMsg){

                         $.messager.alert("系统提示",result.errorMsg);

                     }else{

                         $.messager.alert("系统提示","上传成功");

                         $("#dlg2").dialog("close");

                         $("#dg").datagrid("reload");

                     }

                 }

             });

         }

     </script>

 </head>

 <body>

     <!-- 一排操作按钮 -->

     <div id="toolbar">

         <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-import" plain="true" onclick="openUploadFileDialog()">用模版批量导入数据</a>

     </div>

     <!-- 批量导入数据模板Excel 的dialog -->

     <div id="dlg2" class="easyui-dialog" style="width:400px;height:180px;padding:10px 20px"

             closed="true" buttons="#dlg-buttons2">

         <form id="uploadForm" action="user!upload" method="post" enctype="multipart/form-data">

             <table>

                 <tr>

                     <td>下载模版：</td>

                     <td><a href="javascript:void(0)" class="easyui-linkbutton"  onclick="downloadTemplate()">导入模版</a></td>

                 </tr>

                 <tr>

                     <td>上传文件：</td>

                     <td><input type="file" name="userUploadFile"></td>

                 </tr>

             </table>

         </form>

     </div>

     <div id="dlg-buttons2">

         <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="uploadFile()">上传</a>

         <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg2').dialog('close')">关闭</a>

     </div>

 </body>

 </html>

后端：

UserAction中upload方法：

 public class UserAction extends ActionSupport{

     private File userUploadFile;    //用来接收上传的文件

     public File getUserUploadFile() {

         return userUploadFile;

     }

     public void setUserUploadFile(File userUploadFile) {

         this.userUploadFile = userUploadFile;

     }

     /**

     *    上传文件，解析Excel

     */

     public String upload() throws Exception{

         POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(userUploadFile));

         HSSFWorkbook wb = new HSSFWorkbook(fs);

         HSSFSheet hssfSheet =  wb.getSheetAt(0);     //获取第一个sheet页

         if(hssfSheet != null){

             for(int rowNum=1; rowNum<=hssfSheet.getLastRowNum(); rowNum++){

                 HSSFRow hssfRow = hssfSheet.getRow(rowNum);

                 if(hssfRow == null){

                     continue;

                 }

                 User user = new User();

                 user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));

                 user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));

                 user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));

                 user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));

                 Connection conn = null;

                 try{

                     con=dbUtil.getCon();

                     userDao.userAdd(con, user);

                 }catch(Exception e){

                     e.printStackTrace();

                 }finally{

                     dbUtil.closeCon(con);

                 }

             }

         }

         JSONObject result=new JSONObject();

         result.put("success", "true");

         ResponseUtil.write(ServletActionContext.getResponse(), result);

         return null;

     }

 }

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

 /**

 *    处理Excel的util

 */

 public class ExcelUtil{

     /**

     *    传入Cell，根据Cell的类型转化，返回的都是String

     */

     public static String formatCell(HSSFCell hssfCell){

         if(hssfCell == null){

             return "";

         }else{

             if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){

                 return String.valueOf(hssfCell.getBooleanCellValue());

             }else if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){

                 return String.valueOf(hssfCell.getNumericCellValue());

             }else{

                 return String.valueOf(hssfCell.getStringCellValue());

             }

         }

     } 

 }

图：

巴特西

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

最新文章

热门文章