ext+struts2上传excel并将excel表格显示在extgrid控件上的解决方案

ext2.3

struts2

poi

 

ext上传，使用了extfileUploadField,需引入 <link rel="stylesheet" type="text/css" href="js/extjs/file-upload.css" />
 <script type="text/javascript" src="js/extjs/FileUploadField.js"></script>：<link rel="stylesheet" type="text/css" href="js/extjs/file-upload.css" mce_href="js/extjs/file-upload.css" /> <mce:script type="text/javascript" src="js/extjs/FileUploadField.js" mce_src="js/extjs/FileUploadField.js"></mce:script> <mce:script type="text/javascript"><!-- Ext.onReady(function(){ //上传excel表格 var fp = new Ext.FormPanel({ renderTo: Ext.getBody(), //这一行是必须的 fileUpload: true, width: 500, frame: true, //title: '上传Excel', autoHeight: true, bodyStyle: 'padding: 10px 10px 0 10px;', labelWidth: 50, defaults: { anchor: '95%', allowBlank: false, msgTarget: 'side' }, items: [{ xtype: 'textfield', fieldLabel: 'Name', name:"txtname" }, new Ext.form.FileUploadField({ name:'upload', width:500 })], buttons: [{ text: '上传', handler: function() { if (fp.getForm().isValid()) { fp.getForm().submit({ url: 'qs/excelSettingUpload_excel2settingData',//后台处理的页面 //waitMsg: 'Uploading your photo...', success: function(form, action) { //返回的json数据是action.result alert(Ext.decode(Ext.encode(action.result.data))); },failure:function(f,a){ alert(); } }); } } }, { text: '重置', handler: function() { fp.getForm().reset(); } }] }); //***********excel上传窗口************** var excelWindow =new Ext.Window({ renderTo:Ext.getBody(), form:fp, closeAction:"hide", plain:true, width:540, title:"上传excel", //modal:true, items:fp }); excelWindow.show(); }); // --></mce:script> 

struts2接收上传文件并保存到服务器指定文件夹，然后将excel转化为json字符串后返回页面：

需注意的地方：1返回的json字符串中必须有success属性

private File upload; /** * 文件类型 */ private String uploadContentType; /** * 文件名 */ private String uploadFileName; public String getUploadContentType() { return uploadContentType; } public void setUploadContentType(String uploadContentType) { this.uploadContentType = uploadContentType; } public String getUploadFileName() { return uploadFileName; } public void setUploadFileName(String uploadFileName) { this.uploadFileName = uploadFileName; } public File getUpload() { return upload; } public void setUpload(File upload) { this.upload = upload; } private IQuotationSettingManage quotationSettingManage; public void setQuotationSettingManage( IQuotationSettingManage quotationSettingManage) { this.quotationSettingManage = quotationSettingManage; } public void excel2settingData() throws IOException { String realPath = ServletActionContext.getServletContext().getRealPath("/excel"); if(upload!=null){ String orderNo = "MA20100101"; Date d = new Date(); String saveFileContentType =uploadFileName.substring(uploadFileName.indexOf(".")); String saveFileName = orderNo+d.getTime()+saveFileContentType; File saveFile = new File(new File(realPath),saveFileName); if(!saveFile.getParentFile().exists()){ saveFile.getParentFile().mkdirs(); } try { FileUtil.copyFile(upload, saveFile); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(saveFile.exists()){ //将excel表格拼成json数组 String jsonData = excel2JsonArray(saveFile.getAbsolutePath(),true); //getResponse().setContentType("text/html; charset=utf-8"); //这样写就会下载返回的字符串了getResponse().setContentType("application/json;charset=utf-8"); //PrintWriter out = getResponse().getWriter(); //out.write("{success:true,data:"+jsonData+"}"); //输出如上 outJsonString("{success:true,data:"+jsonData+"}"); } } }

 

sturts2中调用的，poi操作excel返回json字符串的方法excel2JsonArray(saveFile.getAbsolutePath(),true)：

同时兼容2003和2007

private String excel2JsonArray(String excelFilePath,boolean flag) throws IOException{ Workbook xwb =null; if(flag){ xwb = new XSSFWorkbook(excelFilePath); }else{ File f = new File(excelFilePath); FileInputStream is = new FileInputStream(f); POIFSFileSystem fs = new POIFSFileSystem(is); xwb = new HSSFWorkbook(fs); } return read(xwb); } private String read(Workbook xwb){ List dataList = new ArrayList(); List rowList = null; // 9月20日五金电器招标采购竞价单.xlsx Sheet xSheet = xwb.getSheetAt(0); // 循环行Row //从第3行开始 for (int rowNum = 2; rowNum <= xSheet.getLastRowNum(); rowNum++) { Row xRow = xSheet.getRow(rowNum); if (xRow == null) { continue; } rowList = new ArrayList(); // 循环列Cell for (int cellNum = 0; cellNum <= xRow.getLastCellNum(); cellNum++) { Cell xCell = xRow.getCell(cellNum); if (xCell == null) { continue; } if (xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { rowList.add(xCell.getBooleanCellValue()); } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { rowList.add(xCell.getNumericCellValue()); } else { rowList.add(xCell.getStringCellValue()); } } dataList.add(rowList); } JSONArray jsonData = JSONArray.fromObject(dataList); return jsonData.toString(); }

 

未完，待续