java实现excel与mysql的导入导出

  注意：编码前先导入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     }

相关阅读:
第六章函数、谓词、CASE表达式 6-3 CASE表达式
 第六章函数、谓词、CASE表达式 6-2 谓词
 第六章函数、谓词、CASE表达式 6-1 各种各样的函数
 第五章复杂查询 5-3 关联子查询
 第五章复杂查询 5-2 子查询
 第五章复杂查询 5-1 视图
 第四章数据更新 4-3 事务
 第四章数据库和SQL 4-3 数据的更新（UPDATE语句的使用方法）
面向对象进阶
 多态
原文地址：https://www.cnblogs.com/weilantiankong/p/4647372.html