注意：编码前先导入poi相关jar包
1 /** 2 * 读excel 到list 3 * 4 * @param file excel file 5 * @param fields 字段数组 6 * @return 7 * example OfficeHandle.readExcel("d:/test/test.xls", 8 * new String[]{"id","num","name"}) 9 */ 10 public static JSONArray readExcel(String file,String[] fields){ 11 if(null == file || null == fields) 12 return null; 13 14 JSONArray jarr = new JSONArray(); 15 FileInputStream fis = null; 16 int cols = 0; 17 try {
/************************读取本地文件（如d:/test/test.xls）********************************************/ 18 fis = new FileInputStream(new File(file));//读取本地文件（如d:/test/test.xls）
HSSFWorkbook workbook = new HSSFWorkbook(fis);
/**********************读取服务器文件(file="http://你的地址")******************************************/
19 URL url = new URL(file); //file="http://你的地址" 20 URLConnection connection = url.openConnection(); 21 InputStream is = connection.getInputStream(); 22 HSSFWorkbook workbook = new HSSFWorkbook(is);
/**************************************************************************************************/ 23 HSSFSheet sheet = workbook.getSheetAt(0); 24 if(sheet != null){ 25 HSSFRow row = sheet.getRow(0); 26 if(row != null) 27 cols = row.getLastCellNum(); 28 29 for(int i=1,len=sheet.getLastRowNum();i<=len;i++){ 30 row = sheet.getRow(i); 31 if(row != null){ 32 JSONObject jo = new JSONObject(); 33 for(int j=0;j<cols;j++){ 34 HSSFCell cell = row.getCell(j); 35 if(cell != null){ 36 Object v=null; 37 HSSFCellStyle type = cell.getCellStyle(); 38 switch (cell.getCellType()) { 39 case HSSFCell.CELL_TYPE_NUMERIC: 40 v = cell.getNumericCellValue(); 41 break; 42 case HSSFCell.CELL_TYPE_STRING: 43 v = cell.getStringCellValue(); 44 break; 45 case HSSFCell.CELL_TYPE_BOOLEAN: 46 v = cell.getBooleanCellValue(); 47 break; 48 case HSSFCell.CELL_TYPE_FORMULA: 49 v = cell.getCellFormula(); 50 break; 51 default: 52 System.out.println("unsuported sell type"); 53 break; 54 } 55 jo.put(fields[j], v); 56 57 } 58 } 59 jarr.add(jo); 60 } 61 } 62 } 63 } catch (FileNotFoundException e ) { 64 65 }catch(IOException e){ 66 67 }finally{ 68 try { 69 fis.close(); 70 } catch (IOException e) { 71 72 } 73 } 74 return jarr; 75 } 76 77 /** 78 * 从list生成excel 79 * 80 * @param lstData json array data 81 * @param fieldEn 字段英文名 82 * @param fieldZh 生成字段名 83 * @return 84 * example OfficeHandle.exportExcel(lstdata, 85 * new String[]{"schoolId","schoolno","schoolName","address","remarks","linkMobile","linkMan"}, 86 * new String[]{"学校编号","","","","","",""}, 87 * "d:/test/exel1.xls"); 88 */ 89 public static String exportExcel(JSONArray lstData,String[] fieldEn,String[] fieldZh,String fname){ 90 if(null == lstData || null == fieldEn) 91 return null; 92 93 int fieldLen = fieldEn.length; 94 HSSFWorkbook workbook = new HSSFWorkbook(); 95 HSSFSheet sheet = workbook.createSheet(); 96 HSSFRow row = sheet.createRow(0); 97 for(int i=0;i<fieldLen;i++){ 98 String fn = fieldEn[i]; 99 if(null != fieldZh && !StringUtils.isEmpty(fieldZh[i])){ 100 fn = fieldZh[i]; 101 } 102 HSSFCell cell = row.createCell(i); 103 cell.setCellValue(fn); 104 } 105 for(int i=0,len=lstData.size();i<len;i++){ 106 row = sheet.createRow(i+1); 107 for(int j=0;j<fieldLen;j++){ 108 JSONObject jo = lstData.getJSONObject(i); 109 if(jo != null){ 110 HSSFCell cell = row.createCell(j); 111 if(jo.containsKey(fieldEn[j])){ 112 cell.setCellValue(jo.getString(fieldEn[j])); 113 } 114 } 115 } 116 } 117 FileOutputStream fos = null; 118 try { 119 fos = new FileOutputStream(fname); 120 workbook.write(fos); 121 122 } catch (FileNotFoundException e) { 123 124 e.printStackTrace(); 125 }catch (IOException e) { 126 127 }finally{ 128 try { 129 fos.close(); 130 } catch (IOException e) { 131 132 } 133 } 134 return fname; 135 }
案例展示
1 /** 2 * 导入excel数据 3 */ 4 public void importExcel(){ 5 String x = null; 6 JSONArray jar = new JSONArray(); 7 School sc = new School(); 8 String[] fields = new String[]{"schoolId","schoolno","schoolName","address","remarks","linkMobile","linkMan"}; 9 10 try{ 11 12 jar = officHandle.readExcel(filePath, fields,true); 13 for(int i=0,len=jar.size();i<len;i++){ 14 JSONObject ob = JSONObject.fromObject(jar.get(i)); 15 String schoolno = ob.getString("schoolno"); 16 if(!school.isExist(schoolno)){//根据学校编号判断，若不存在就添加否则更新 17 sc = (School)school.addRecord((School)JSONObject.toBean(ob,School.class)); 18 }else{ 19 School sch = (School)school.findByProperty("School", new String[]{"schoolno"}, new Object[]{schoolno}).get(0);//获取存在的记录id 20 ob.put("schoolId", sch.getSchoolId()); 21 sc = (School)school.editRecord((School)JSONObject.toBean(ob,School.class)); 22 } 23 } 24 x = sc.getSchoolId().toString(); 25 }catch(Exception e){ 26 x = errorHandle.handleErr(e); 27 } 28 servletHandle.writeToClient1(ServletActionContext.getResponse(), x); 29 }
1 /** 2 * 导出数据到Excel 3 */ 4 public void exportData(){ 5 String x = null; 6 String[] idArr = model.getIds().split(","); 7 List<School> schoolList = new ArrayList<School>(); 8 JSONArray jar = new JSONArray();//数据list 9 String[] fieldEn = new String[]{"schoolno","schoolName","address","linkMobile","linkMan","remarks"}; 10 String[] fieldCn = new String[]{"学校编号","学校名称","学校地址","联系电话","联系人","备注"}; 11 try{ 12 if(StringUtils.isEmpty(model.getIds())){//全部导出 13 x = school.findByProperty("School", "*", 14 "json", true,null, null, null, null, 0, 0); 15 schoolList = (List<School>) JSONObject.fromObject(x).get("rows"); 16 if(schoolList.size() > 0){ 17 for(int i=0,len=schoolList.size();i<len;i++){ 18 jar.add(schoolList.get(i)); 19 } 20 } 21 22 }else{ 23 for(int i=0,len=idArr.length;i<len;i++){//导出选择记录 24 schoolList = school.findByProperty("School", new String[]{"schoolId"}, new Object[]{Long.parseLong(idArr[i])}); 25 if(schoolList.size() > 0) 26 jar.add(schoolList.get(0)); 27 } 28 } 29 String basePath = ServletActionContext.getServletContext().getRealPath("/");//获取服务器文件存放地址 30 String path = "/assets/export/" + UUID.randomUUID().toString().replaceAll("-", "") + ".xls";//拼接随机生成文件名，用于写入excel数据流 31 String fn = basePath + path; 32 officHandle.exportExcel(jar, fieldEn, fieldCn, fn);//传入数据list，字段名及保存文件名 33 x = CommonConfig.domainName + CommonConfig.contextPath + path;//获取文件路径返回，location.href = x（浏览器自动下载文件） 34 }catch(Exception e){ 35 x = errorHandle.handleErr(e); 36 } 37 servletHandle.writeToClient1(ServletActionContext.getResponse(), x); 38 }