java poi 追加excel

由于工作需要本人写了个工具类来实现追加excel 的功能，用到了4个类文件 ，下面分别介绍有什么作用（poi 版本使用的3.9）

ChatExcel.java 实现追加功能的基础类，也是功能的父类。里面的功能有 获取路径文件 有则追加 没有创建等。

ChatMsgExcel.java 实现追加功能，具体的实现类。重写父类两个方法 append 与appendList 

ChatMsg.java  追加内容的实体类。

ChatField.java 实体中的属性 需要追加的 加上次注解

Reflections.java 反射的工具类，网上找的直接用。

为了方便 实体类中的 get set 方法没有贴出。

ChatExcel.java 文件

package com.chat.serve.modules.chatserve.excel;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.RandomAccessFile;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.nio.channels.FileChannel;import java.nio.channels.FileLock;import java.util.ArrayList;import java.util.Arrays;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.io.FileUtils;import org.apache.commons.lang3.StringUtils;import org.apache.http.client.utils.DateUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Comment;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;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.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.chat.serve.common.utils.Reflections;import com.google.common.collect.Lists;/** * 追加excel 父类 * @author liyangyang * * @param <T> */public abstract class ChatExcel<T> {private Class<?> clazz;public XSSFWorkbook wb;public Sheet sheet;private FileInputStream fileInput=null;private FileChannel fileChannel=null;private FileLock fileLock=null;/** * 路径是否是文件 */public boolean isFile=false;/** * 当前行号 */public int rownum;private List<String> headerList=new ArrayList<String>();private int headnum=0;/** * 样式列表 */private Map<String, CellStyle> styles;private String filePath;/** * 注解列表（Object[]{ ExcelField, Field/Method }） */List<Object[]> annotationList = Lists.newArrayList();public ChatExcel(String filePath) {this(filePath, "聊天记录",1);}//检测文件大小。private File checkFilePath(String filePath) {try {File file=new File(filePath);if(!file.exists()){return file;}else{long fileSize=file.length();if(fileSize > 1328427){//1.26MB左右，大概4-5万行左右   String fileName=DateUtils.formatDate(new Date(), "yyyyMMdd")+"-"+file.getName();   File path=new File(file.getParent());   FileUtils.copyFile(file, new File(path,fileName));   file.delete();}return file;}} catch (Exception e) {e.printStackTrace();}return null;}public ChatExcel(String filePath,String title,int headnum) {File file=checkFilePath(filePath);try {clazz=Reflections.getClassGenricType(getClass());if(file.exists() && file.isFile()){//文件存在//追加isFile=true;fileInput= new FileInputStream(file);//fileChannel=new RandomAccessFile(file, "rw").getChannel();wb=new XSSFWorkbook(fileInput);sheet=wb.getSheet("message");//fileLock=fileChannel.lock();}else{//文件不存在 // 新建isFile=false;wb=new XSSFWorkbook();sheet=wb.createSheet("message");}this.filePath=filePath;this.headnum=headnum;addHeader(title);} catch (Exception e) {e.printStackTrace();}}/** * 获取最后一个数据行号 * @return */public int getLastDataRowNum(){return this.sheet.getLastRowNum()+headnum;}public void setData(Row row,T data){int colunm = 0;Object val = null;for (Object[] os : annotationList){if(os[1] instanceof Field){val = Reflections.invokeGetter(data, ((Field)os[1]).getName());}else if (os[1] instanceof Method){val = Reflections.invokeMethod(data, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});}this.addCell(row, colunm++, val, 2, Class.class);}}public void setListData(int rownum,List<T> data){for(T m:data){   Row row= sheet.createRow(rownum++);   setData(row, m);}}public abstract void appenList(List<T> listdata);/** * 添加一个单元格 * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @param align 对齐方式（1：靠左；2：居中；3：靠右） * @return 单元格对象 */private Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){Cell cell = row.createCell(column);String cellFormatString = "@";try {if(val == null){cell.setCellValue("");}else if(fieldType != Class.class){cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));}else{if(val instanceof String) {cell.setCellValue((String) val);}else if(val instanceof Integer) {cell.setCellValue((Integer) val);cellFormatString = "0";}else if(val instanceof Long) {cell.setCellValue((Long) val);cellFormatString = "0";}else if(val instanceof Double) {cell.setCellValue((Double) val);cellFormatString = "0.00";}else if(val instanceof Float) {cell.setCellValue((Float) val);cellFormatString = "0.00";}else if(val instanceof Date) {cell.setCellValue((Date) val);cellFormatString = "yyyy-MM-dd HH:mm";}else {cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));}}if (val != null){CellStyle style = styles.get("data_column_"+column);if (style == null){style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));        style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));styles.put("data_column_" + column, style);}cell.setCellStyle(style);}} catch (Exception ex) {cell.setCellValue(val.toString());}return cell;}private void addHeader(String title){List<Field> listField= Arrays.asList(clazz.getDeclaredFields());for(Field field:listField){ChatField fieldClass=field.getAnnotation(ChatField.class);if(fieldClass!=null){annotationList.add(new Object[]{fieldClass,field});//headerList.add(f.getName());}}Collections.sort(annotationList,new Comparator<Object[]>() {@Overridepublic int compare(Object[] o1, Object[] o2) {int o1int= ((ChatField)o1[0]).sort();int o2int= ((ChatField)o2[0]).sort();return o1int-o2int;}});for(Object[] ob:annotationList){String fiName=((Field)ob[1]).getName();headerList.add(fiName);}this.styles = createStyles(wb);if(!isFile){//新建 headerRow titleRow = sheet.createRow(rownum++);titleRow.setHeightInPoints(30);Cell titleCell = titleRow.createCell(0);titleCell.setCellStyle(styles.get("title"));titleCell.setCellValue(title);sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));if (headerList == null){throw new RuntimeException("headerList not null!");}Row headerRow = sheet.createRow(rownum++);headerRow.setHeightInPoints(16);for (int i = 0; i < headerList.size(); i++) {Cell cell = headerRow.createCell(i);cell.setCellStyle(styles.get("header"));String[] ss = StringUtils.split(headerList.get(i), "**", 2);if (ss.length==2){cell.setCellValue(ss[0]);Comment comment = this.sheet.createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));comment.setString(new XSSFRichTextString(ss[1]));cell.setCellComment(comment);}else{cell.setCellValue(headerList.get(i));}sheet.autoSizeColumn(i);}for (int i = 0; i < headerList.size(); i++) {  int colWidth = sheet.getColumnWidth(i)*2;        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  }}}/** * 创建表格样式 * @param wb 工作薄对象 * @return 样式列表 */private Map<String, CellStyle> createStyles(Workbook wb) {Map<String, CellStyle> styles = new HashMap<String, CellStyle>();CellStyle style = wb.createCellStyle();style.setAlignment(CellStyle.ALIGN_CENTER);style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);Font titleFont = wb.createFont();titleFont.setFontName("Arial");titleFont.setFontHeightInPoints((short) 16);titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);style.setFont(titleFont);styles.put("title", style);style = wb.createCellStyle();style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style.setBorderRight(CellStyle.BORDER_THIN);style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderLeft(CellStyle.BORDER_THIN);style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderTop(CellStyle.BORDER_THIN);style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderBottom(CellStyle.BORDER_THIN);style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());Font dataFont = wb.createFont();dataFont.setFontName("Arial");dataFont.setFontHeightInPoints((short) 10);style.setFont(dataFont);styles.put("data", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));style.setAlignment(CellStyle.ALIGN_LEFT);styles.put("data1", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));style.setAlignment(CellStyle.ALIGN_CENTER);styles.put("data2", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));style.setAlignment(CellStyle.ALIGN_RIGHT);styles.put("data3", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));//style.setWrapText(true);style.setAlignment(CellStyle.ALIGN_CENTER);style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);Font headerFont = wb.createFont();headerFont.setFontName("Arial");headerFont.setFontHeightInPoints((short) 10);headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);headerFont.setColor(IndexedColors.WHITE.getIndex());style.setFont(headerFont);styles.put("header", style);return styles;}public void export(){try {if(fileInput!=null){fileInput.close();}if(fileLock!=null){fileLock.release();}if(fileChannel!=null){fileChannel.close();}} catch (Exception e) {e.printStackTrace();}write();}public void write(){try {FileOutputStream outputStream=new FileOutputStream(new File(filePath));wb.write(outputStream);outputStream.close();} catch (Exception e) {e.printStackTrace();}}public abstract void append(T data);}

ChatMsgExcel.java文件

package com.chat.serve.modules.chatserve.excel;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.ss.usermodel.Row;import com.chat.serve.modules.chatserve.entity.ChatMsg;/** * 追加excel 实现类 * @author 01054475 * */public class ChatMsgExcel extends ChatExcel<ChatMsg>{public ChatMsgExcel(String filePath) {super(filePath);}@Overridepublic void append(ChatMsg data) {if(isFile){   int listData=getLastDataRowNum();   Row row= sheet.createRow(listData++);   setData(row, data);}else{   Row row=sheet.createRow(rownum++);   setData(row, data);}export();}@Overridepublic void appenList(List<ChatMsg> listdata) {if(isFile){   int listData=getLastDataRowNum();   setListData(listData, listdata);}else{   setListData(rownum, listdata);}export();}public static void main(String[] args) {List<ChatMsg> list=new ArrayList<ChatMsg>();ChatMsg m=new ChatMsg();m.setFuid("fuid21");m.setTuid("tuid21");m.setAddTime(new Date());m.setContent("adsfadsfadsfasdfasdf");m.setIp("12.23.43.234");m.setUgroup("aaa");list.add(m);list.add(m);ChatMsgExcel e=new ChatMsgExcel("C:\\Users\\01054475\\Desktop\\chat\\msg1.xlsx");e.appenList(list);e.append(m);}}

ChatMsg.java 文件

package com.chat.serve.modules.chatserve.entity;import java.util.Date;import com.chat.serve.common.persistence.BaseEntity;import com.chat.serve.modules.chatserve.excel.ChatField;import com.google.gson.annotations.Expose;public class ChatMsg extends BaseEntity<ChatMsg>{private static final long serialVersionUID = 1L;@ChatField    private String fuid;@ChatField    private String tuid;@ChatField    private String content;@ChatFieldprivate String ugroup;@ChatField    private Date addTime;    private Boolean msgType;    private String isSend;    @ChatField    private String ip;    }

ChatField.java 文件

package com.chat.serve.modules.chatserve.excel;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** *   excel 注解。需要写入的属性 加上此注解。sort（） 排序 **/@Target({ElementType.FIELD, ElementType.TYPE})@Retention(RetentionPolicy.RUNTIME)public @interface ChatField {int sort() default 0;}

Reflections.java 文件

/** * Copyright (c) 2005-2012 springside.org.cn * * Licensed under the Apache License, Version 2.0 (the "License"); */package com.wondersgroup.ningbo.common.utils;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.lang.reflect.Modifier;import java.lang.reflect.ParameterizedType;import java.lang.reflect.Type;import org.apache.commons.lang3.StringUtils;import org.apache.commons.lang3.Validate;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.util.Assert;/** * 反射工具类. * 提供调用getter/setter方法, 访问私有变量, 调用私有方法, 获取泛型类型Class, 被AOP过的真实类等工具函数. * @author calvin * @version 2013-01-15 */@SuppressWarnings("rawtypes")public class Reflections {private static final String SETTER_PREFIX = "set";private static final String GETTER_PREFIX = "get";private static final String CGLIB_CLASS_SEPARATOR = "$$";private static Logger logger = LoggerFactory.getLogger(Reflections.class);/** * 调用Getter方法. * 支持多级，如：对象名.对象名.方法 */public static Object invokeGetter(Object obj, String propertyName) {Object object = obj;for (String name : StringUtils.split(propertyName, ".")){String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(name);object = invokeMethod(object, getterMethodName, new Class[] {}, new Object[] {});}return object;}/** * 调用Setter方法, 仅匹配方法名。 * 支持多级，如：对象名.对象名.方法 */public static void invokeSetter(Object obj, String propertyName, Object value) {Object object = obj;String[] names = StringUtils.split(propertyName, ".");for (int i=0; i<names.length; i++){if(i<names.length-1){String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(names[i]);object = invokeMethod(object, getterMethodName, new Class[] {}, new Object[] {});}else{String setterMethodName = SETTER_PREFIX + StringUtils.capitalize(names[i]);invokeMethodByName(object, setterMethodName, new Object[] { value });}}}/** * 直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数. */public static Object getFieldValue(final Object obj, final String fieldName) {Field field = getAccessibleField(obj, fieldName);if (field == null) {throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]");}Object result = null;try {result = field.get(obj);} catch (IllegalAccessException e) {logger.error("不可能抛出的异常{}", e.getMessage());}return result;}/** * 直接设置对象属性值, 无视private/protected修饰符, 不经过setter函数. */public static void setFieldValue(final Object obj, final String fieldName, final Object value) {Field field = getAccessibleField(obj, fieldName);if (field == null) {throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]");}try {field.set(obj, value);} catch (IllegalAccessException e) {logger.error("不可能抛出的异常:{}", e.getMessage());}}/** * 直接调用对象方法, 无视private/protected修饰符. * 用于一次性调用的情况，否则应使用getAccessibleMethod()函数获得Method后反复调用. * 同时匹配方法名+参数类型， */public static Object invokeMethod(final Object obj, final String methodName, final Class<?>[] parameterTypes,final Object[] args) {Method method = getAccessibleMethod(obj, methodName, parameterTypes);if (method == null) {throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");}try {return method.invoke(obj, args);} catch (Exception e) {throw convertReflectionExceptionToUnchecked(e);}}/** * 直接调用对象方法, 无视private/protected修饰符， * 用于一次性调用的情况，否则应使用getAccessibleMethodByName()函数获得Method后反复调用. * 只匹配函数名，如果有多个同名函数调用第一个。 */public static Object invokeMethodByName(final Object obj, final String methodName, final Object[] args) {Method method = getAccessibleMethodByName(obj, methodName);if (method == null) {throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");}try {return method.invoke(obj, args);} catch (Exception e) {throw convertReflectionExceptionToUnchecked(e);}}/** * 循环向上转型, 获取对象的DeclaredField, 并强制设置为可访问. *  * 如向上转型到Object仍无法找到, 返回null. */public static Field getAccessibleField(final Object obj, final String fieldName) {Validate.notNull(obj, "object can't be null");Validate.notBlank(fieldName, "fieldName can't be blank");for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {try {Field field = superClass.getDeclaredField(fieldName);makeAccessible(field);return field;} catch (NoSuchFieldException e) {//NOSONAR// Field不在当前类定义,继续向上转型continue;// new add}}return null;}/** * 循环向上转型, 获取对象的DeclaredMethod,并强制设置为可访问. * 如向上转型到Object仍无法找到, 返回null. * 匹配函数名+参数类型。 *  * 用于方法需要被多次调用的情况. 先使用本函数先取得Method,然后调用Method.invoke(Object obj, Object... args) */public static Method getAccessibleMethod(final Object obj, final String methodName,final Class<?>... parameterTypes) {Validate.notNull(obj, "object can't be null");Validate.notBlank(methodName, "methodName can't be blank");for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {try {Method method = searchType.getDeclaredMethod(methodName, parameterTypes);makeAccessible(method);return method;} catch (NoSuchMethodException e) {// Method不在当前类定义,继续向上转型continue;// new add}}return null;}/** * 循环向上转型, 获取对象的DeclaredMethod,并强制设置为可访问. * 如向上转型到Object仍无法找到, 返回null. * 只匹配函数名。 *  * 用于方法需要被多次调用的情况. 先使用本函数先取得Method,然后调用Method.invoke(Object obj, Object... args) */public static Method getAccessibleMethodByName(final Object obj, final String methodName) {Validate.notNull(obj, "object can't be null");Validate.notBlank(methodName, "methodName can't be blank");for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {Method[] methods = searchType.getDeclaredMethods();for (Method method : methods) {if (method.getName().equals(methodName)) {makeAccessible(method);return method;}}}return null;}/** * 改变private/protected的方法为public，尽量不调用实际改动的语句，避免JDK的SecurityManager抱怨。 */public static void makeAccessible(Method method) {if ((!Modifier.isPublic(method.getModifiers()) || !Modifier.isPublic(method.getDeclaringClass().getModifiers()))&& !method.isAccessible()) {method.setAccessible(true);}}/** * 改变private/protected的成员变量为public，尽量不调用实际改动的语句，避免JDK的SecurityManager抱怨。 */public static void makeAccessible(Field field) {if ((!Modifier.isPublic(field.getModifiers()) || !Modifier.isPublic(field.getDeclaringClass().getModifiers()) || Modifier.isFinal(field.getModifiers())) && !field.isAccessible()) {field.setAccessible(true);}}/** * 通过反射, 获得Class定义中声明的泛型参数的类型, 注意泛型必须定义在父类处 * 如无法找到, 返回Object.class. * eg. * public UserDao extends HibernateDao<User> * * @param clazz The class to introspect * @return the first generic declaration, or Object.class if cannot be determined */@SuppressWarnings("unchecked")public static <T> Class<T> getClassGenricType(final Class clazz) {return getClassGenricType(clazz, 0);}/** * 通过反射, 获得Class定义中声明的父类的泛型参数的类型. * 如无法找到, 返回Object.class. *  * 如public UserDao extends HibernateDao<User,Long> * * @param clazz clazz The class to introspect * @param index the Index of the generic ddeclaration,start from 0. * @return the index generic declaration, or Object.class if cannot be determined */public static Class getClassGenricType(final Class clazz, final int index) {Type genType = clazz.getGenericSuperclass();if (!(genType instanceof ParameterizedType)) {logger.warn(clazz.getSimpleName() + "'s superclass not ParameterizedType");return Object.class;}Type[] params = ((ParameterizedType) genType).getActualTypeArguments();if (index >= params.length || index < 0) {logger.warn("Index: " + index + ", Size of " + clazz.getSimpleName() + "'s Parameterized Type: "+ params.length);return Object.class;}if (!(params[index] instanceof Class)) {logger.warn(clazz.getSimpleName() + " not set the actual class on superclass generic parameter");return Object.class;}return (Class) params[index];}public static Class<?> getUserClass(Object instance) {Assert.notNull(instance, "Instance must not be null");Class clazz = instance.getClass();if (clazz != null && clazz.getName().contains(CGLIB_CLASS_SEPARATOR)) {Class<?> superClass = clazz.getSuperclass();if (superClass != null && !Object.class.equals(superClass)) {return superClass;}}return clazz;}/** * 将反射时的checked exception转换为unchecked exception. */public static RuntimeException convertReflectionExceptionToUnchecked(Exception e) {if (e instanceof IllegalAccessException || e instanceof IllegalArgumentException|| e instanceof NoSuchMethodException) {return new IllegalArgumentException(e);} else if (e instanceof InvocationTargetException) {return new RuntimeException(((InvocationTargetException) e).getTargetException());} else if (e instanceof RuntimeException) {return (RuntimeException) e;}return new RuntimeException("Unexpected Checked Exception.", e);}}