神刀安全网

Excel导入导出

  在后台管理系统中,常遇到Excel导入导出的需求,整理了如下两个工具类。

一、导入

导入工具类

package com.demo.util;  import java.io.IOException; import java.io.InputStream; import java.io.PushbackInputStream; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.Map;  import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.LoggerFactory; import ch.qos.logback.classic.Logger;  public class ExcelToEntityList {      private Logger log = (Logger) LoggerFactory.getLogger(this.getClass());     private BeanStorage storage = new BeanStorage();     private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");     private StringBuffer error = new StringBuffer(0);      /**      * 包含headMapping信息已经前端传过来的扩展信息      * @param entity          一行excel内容要转换为何种对象      * @param excel              excel输入流      * @param titleToAttr    key为excel的中文title,value为该中文title对于的entity属性名      * @return      * @throws IOException      * @throws ClassNotFoundException      * @throws InstantiationException      * @throws IllegalAccessException      * @throws InvalidFormatException      */     public  <T> ArrayList<T> transform(Class<?> entity,InputStream excel,Map<String,String> titleToAttr) throws IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, InvalidFormatException{         ArrayList<T> result = new ArrayList<T>();          Workbook book = create(excel);         Sheet sheet = book.getSheetAt(0);         int rowCount = sheet.getLastRowNum();         if(rowCount < 1){             return result;         }         //加载标题栏数据,以此和headMapping对应         Map<Integer, String> headTitle = loadHeadTitle(sheet);         for(int i=1;i<=rowCount;i++){             Row row = sheet.getRow(i);             //空行跳过             if(row==null){                 continue;             }             int cellCount = row.getLastCellNum();             @SuppressWarnings("unchecked")             T instance = (T) entity.newInstance();             int col = 0;             try {                 for(;col<cellCount;col++){                     String cellValue = getCellValue(row.getCell(col));                     if(null!=cellValue){                         this.setEntity(entity, instance,titleToAttr.get(headTitle.get(col)), cellValue);                     }                 }                 result.add(instance);             } catch (Exception e) {                 //excel.close();                 e.printStackTrace();                 this.error.append("第"+ (i+1) +"行,"+ headTitle.get(col)+"字段,数据错误,跳过!").append("<br>");                 log.error("第"+ (i+1) +"行,"+ headTitle.get(col)+"字段,数据错误,跳过!");             }         }         excel.close();         return result;     }      /**      * 加载Excel的标题栏      * @param sheet      * @return 返回列序号和对于的标题名称Map      */     private Map<Integer,String> loadHeadTitle(Sheet sheet){         Map<Integer,String> map = new HashMap<Integer, String>();         Row row = sheet.getRow(0);         int cellCount= row.getLastCellNum();         for(int i = 0; i < cellCount; i++){             String value = row.getCell(i).getStringCellValue();             if(null == value){                 throw new RuntimeException("Excel导入:标题栏不能为空!");             }             map.put(i, value);         }         return map;     }      /**      * 获取表格列的值      * @param cell      * @return      */     private String getCellValue(Cell cell){         if(null==cell){return "";}         String value = null;         switch (cell.getCellType()){         case XSSFCell.CELL_TYPE_BOOLEAN:             value = String.valueOf(cell.getBooleanCellValue());             break;         case XSSFCell.CELL_TYPE_NUMERIC:             // 判断当前的cell是否为Date             if (DateUtil.isCellDateFormatted(cell)){                 value = dateFormat.format(cell.getDateCellValue());             }else{                 value = String.valueOf((long) cell.getNumericCellValue());             }             break;         case XSSFCell.CELL_TYPE_STRING:             value = cell.getStringCellValue();             break;         case XSSFCell.CELL_TYPE_FORMULA:             log.debug("不支持函数!");             break;         }          return value;     }      private <T> void setEntity(Class<?> clazz, T instance, String pro, String value) throws SecurityException, NoSuchMethodException, Exception{         String innerPro = null;         String outterPro = null;         if (pro.contains(".")){             String[] pros = pro.split("//.");             outterPro = pros[0];             innerPro = pros[1];             // 将成员变量的类型存储到仓库中             storage.storeClass(instance.hashCode() + outterPro, clazz.getDeclaredMethod(this.initGetMethod(outterPro), null).getReturnType());         }         String getMethod = this.initGetMethod(outterPro!=null?outterPro:pro);         Class<?> type = clazz.getDeclaredMethod(getMethod, null).getReturnType();         Method method = clazz.getMethod(this.initSetMethod(outterPro!=null?outterPro:pro), type);         if (type == String.class){             method.invoke(instance, value);         }else if (type == int.class || type == Integer.class){             method.invoke(instance, Integer.parseInt("".equals(value) ? "0" : value));         }else if (type == long.class || type == Long.class){             method.invoke(instance, Long.parseLong("".equals(value) ? "0" : value));         }else if (type == float.class || type == Float.class){             method.invoke(instance, Float.parseFloat("".equals(value) ? "0" : value));         }else if (type == double.class || type == Double.class){             method.invoke(instance, Double.parseDouble("".equals(value) ? "0" : value));         }else if (type == Date.class){             method.invoke(instance, dateFormat.parse(value));         }else if (type == boolean.class|| type == Boolean.class){             method.invoke(instance, Boolean.parseBoolean("".equals(value) ? "false" : value));         }else if (type == byte.class|| type == Byte.class){             method.invoke(instance, Byte.parseByte(value));         }else{             // 引用类型数据             Object ins = storage.getInstance(instance.hashCode() + outterPro);             this.setEntity(ins.getClass(), ins, innerPro, value);             method.invoke(instance, ins);         }     }      public String initSetMethod(String field)     {         return "set" + field.substring(0, 1).toUpperCase() + field.substring(1);     }      public String initGetMethod(String field)     {         return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);     }      /**      * @return true 存在错误,false 不存在错误      */     public boolean hasError()     {         return error.capacity() > 0;     }      public StringBuffer getError()     {         return error;     }     /**      * 存储bean中的bean成员变量      */     private class BeanStorage     {         private Map<String, Object> instances = new HashMap<String, Object>();         public void storeClass(String key, Class<?> clazz) throws Exception{             if (!instances.containsKey(key)){                 instances.put(key, clazz.newInstance());             }         }         public Object getInstance(String key){             return instances.get(key);         }     }      //2003、2007兼容处理     public  Workbook create(InputStream inp) throws IOException,InvalidFormatException {         if (!inp.markSupported()) {             inp = new PushbackInputStream(inp, 8);         }         if (POIFSFileSystem.hasPOIFSHeader(inp)) {             return new HSSFWorkbook(inp);         }         if (POIXMLDocument.hasOOXMLHeader(inp)) {             return new XSSFWorkbook(OPCPackage.open(inp));         }         throw new IllegalArgumentException("你的excel版本目前poi解析不了");     } }

使用方式

  //前端部分提交excel文件与该对应关系到后端   var mapping = {       "EntityClassName":"com.demo.entity.User",       "用户编号":"userCode","用户名称":"userName",       "电话":"phone","地址":"address"   }
  /*后端部分在servlet中接收excel与excel头与entity属性的对应关系*/   //接收对应关系参数   HashMap<String, String> param = JSON.parseObject(URLDecoder.decode(request.getParameter("mapping"), "utf-8"), HashMap.class);   ExcelToEntityList excel = new ExcelToEntityList();   //把excel文件内容转换为List对象   ArrayList<?> list = excel.transform(Class.forName(param.get("EntityClassName")), request.getPart("file").getInputStream(), param);

二、导出

导入工具类

package com.demo.util;  import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern;  import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFComment; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor;  public class EntityListToExcel {     private StringBuffer error = new StringBuffer(0);      /**      * 将实体类列表entityList转换成excel      * @param param 包含headMapping信息,key为属性名,value为列名<br>      * @param entityList      * @param excel      * @return      * @throws NoSuchMethodException      * @throws SecurityException      * @throws IllegalAccessException      * @throws IllegalArgumentException      * @throws InvocationTargetException      * @throws IOException      */     public <T> boolean transform(Map<String, String> param, List<T> entityList,OutputStream excel) throws NoSuchMethodException,             SecurityException, IllegalAccessException,             IllegalArgumentException, InvocationTargetException, IOException {         // 声明一个工作薄         HSSFWorkbook workbook = new HSSFWorkbook();         // 生成一个表格         HSSFSheet sheet = workbook.createSheet();         // 设置表格默认列宽度为15个字节         sheet.setDefaultColumnWidth(15);         // 声明一个画图的顶级管理器         HSSFPatriarch patriarch = sheet.createDrawingPatriarch();         // 定义注释的大小和位置,详见文档         HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,                 0, 0, 0, (short) 4, 2, (short) 6, 5));         // 设置注释内容         comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));         // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.         comment.setAuthor("admin");          // 产生表格标题行         HSSFRow row = sheet.createRow(0);         int i = 0;         List<String> proList = new ArrayList<String>();         HSSFFont blueFont = workbook.createFont();         blueFont.setColor(HSSFColor.BLUE.index);         for (Map.Entry<String, String> entry : param.entrySet()) {             HSSFCell cell = row.createCell(i);             HSSFRichTextString text = new HSSFRichTextString(entry.getValue());             text.applyFont(blueFont);             cell.setCellValue(text);             proList.add(entry.getKey());             i++;         }         // 遍历集合数据,产生数据行         Iterator<T> it = entityList.iterator();         int index = 0;         while (it.hasNext()) {             index++;             row = sheet.createRow(index);             T t = (T) it.next();             // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值             for (i = 0; i < proList.size(); i++) {                 HSSFCell cell = row.createCell(i);                 String propertyName = proList.get(i);                 String textValue = null;                 try {                     textValue = this.getPropertyValue(t, propertyName);                 } catch (Exception e) {                     e.printStackTrace();                     this.error.append("第").append(index+1).append("行,列名:").append(param.get(propertyName)).append(",字段:").append(propertyName).append(",数据错误,跳过!").append("<br>");                 }                 // 利用正则表达式判断textValue是否全部由数字组成                 if (textValue != null) {                     Pattern p = Pattern.compile("^//d+(//.//d+)?$");                     Matcher matcher = p.matcher(textValue);                     if (matcher.matches()) {                         // 是数字当作double处理                         cell.setCellValue(Double.parseDouble(textValue));                     } else {                         HSSFRichTextString richString = new HSSFRichTextString(                                 textValue);                         cell.setCellValue(richString);                     }                 }             }          }         workbook.write(excel);         workbook.close();         return true;     }      /**      * 获取实体instance的propertyName属性的值      * @param instance      * @param propertyName      * @return      * @throws NoSuchMethodException      * @throws SecurityException      * @throws IllegalAccessException      * @throws IllegalArgumentException      * @throws InvocationTargetException      */     private <T> String getPropertyValue(T instance, String propertyName)             throws NoSuchMethodException, SecurityException,             IllegalAccessException, IllegalArgumentException,             InvocationTargetException {          String getMethodName = this.initGetMethod(propertyName);         Class<?> tCls = instance.getClass();         Method getMethod = null;         Object value = null;          getMethod = tCls.getMethod(getMethodName, new Class[] {});         value = getMethod.invoke(instance, new Object[] {});          String returnType = getMethod.getReturnType().getName();          // 判断值的类型后进行强制类型转换         SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");         String textValue = null;         if ("java.util.Date".equals(returnType)) {             textValue = dateFormat.format(value);         }else{             textValue = value.toString();         }         return textValue;     }     /**      * 返回fiel属性的getXXX方法字符串      * @param field      * @return      */     private String initGetMethod(String field) {         return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);     }      /**      * @return true 存在错误,false 不存在错误      */     public boolean hasError() {         return error.capacity() > 0;     }      /**      * 获得错误信息      * @return      */     public StringBuffer getError() {         return error;     } }

使用方式

//Servlet中设置返回类型 response.setContentType("application/vnd.ms-excel;charset=UTF-8"); //这里也可以通过前端传过来 LinkedHashMap<String, String> header = new LinkedHashMap<String, String>(); header.put("userCode", "用户编码"); header.put("userName", "用户名"); header.put("phone", "电话"); header.put("address", "地址");  //数据导出为excel EntityListToExcel excel =  = new EntityListToExcel(); //这里的entityList为要转换为excel行的数据列表 excel.transform(header, entityList, response.getOutputStream());

Blog:http://muchstudy.com

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Excel导入导出

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址