java反射构建excel导出工具类
一、定义注解，用于vo类中标记使用到的属性
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author fc
* @date 2018-09-18
*/
@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Allow {
String title() default "undefined";
}
二、反射工具类
package com.osa.promote.common.util.excel;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;
/**
* @author fc
* @date 2018-09-18
* @msg 反射工具类
* @tips 字段必须使用@Allow注解 title属性定义标题
*/
public class ExcelCreateUtil {
/**
* @param o 集合类
* @param suf 返回的excel文件后缀
* @return
* @throws Exception
*/
public static Workbook createWorkBook(List o, ReturnSuf suf) throws Exception {
return createWorkBook(o, suf, false);
}
/**
* @param o 集合类
* @param suf 返回的excel文件后缀
* @param isAllowTitle 是否展示title
* @return
* @throws Exception
*/
public static Workbook createWorkBook(List o, ReturnSuf suf, Boolean isAllowTitle) throws Exception {
if (CollectionUtils.isEmpty(o)) {
throw new Exception("param invalid.");
}
isAllowTitle = null == isAllowTitle ? false : isAllowTitle;
//定义一个新的工作簿
Workbook wb = suf.getValue().equals(ReturnSuf.XLSX.getValue()) ? new XSSFWorkbook() : new HSSFWorkbook();
//创建sheet
Sheet sheet = wb.createSheet(String.valueOf(System.currentTimeMillis()));
//如果是map类型
if(Map.class.isAssignableFrom(o.get(0).getClass())){
throw new Exception("不支持Map类型");
}
createVoListSheet(wb, sheet, o, suf, isAllowTitle);
return wb;
}
private static void createVoListSheet(Workbook wb,Sheet sheet, List o, ReturnSuf suf, Boolean isAllowTitle)throws Exception {
Class<?> aClass = o.get(0).getClass();
Field[] fields1 = aClass.getDeclaredFields();
//要表头
if (isAllowTitle) {
//创建行
Row row = sheet.createRow(0);
//第一行以注解@Allow title属性命名
int titleCellIndex = 0;
for (int j = 0; j < fields1.length; j++) {
Allow annotation = fields1[j].getAnnotation(Allow.class);
if (annotation != null) {
fields1[j].setAccessible(true);
row.createCell(titleCellIndex).setCellValue(annotation.title());
titleCellIndex++;
}
}
}
int contentRowIndex = isAllowTitle ? 1 : 0;
for (int i = 0; i < o.size(); i++) {
//获取当前对象
Object o1 = o.get(i);
//获取当前类类型
Class<?> c = o.get(i).getClass();
//获取当前类类型的所有属性
Field[] fields = c.getDeclaredFields();
//创建行-从第二行起
Row row1 = sheet.createRow(contentRowIndex);
contentRowIndex++;
int contentCellIndex = 0;
for (int j = 0; j < fields.length; j++) {
Allow annotation = fields1[j].getAnnotation(Allow.class);
if (annotation != null) {
fields[j].setAccessible(true);
//得到属性名
String name = fields[j].getName();
//获取属性对应的get方法
Method method = c.getDeclaredMethod(toGet(name));
if (null == method) {
throw new Exception("找不到【" + toGet(name) + "】");
}
Object invoke = method.invoke(o1);
String value = invoke == null ? "" : invoke.toString();
//赋值
row1.createCell(contentCellIndex).setCellValue(value);
contentCellIndex++;
}
}
}
}
//获取属性的set方法
private static String toSet(String s) {
final String SET = "set";
if (Character.isUpperCase(s.charAt(0))) {
return SET + s;
} else {
return SET + (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
}
}
//获取属性的get方法
private static String toGet(String s) {
final String GET = "get";
if (Character.isUpperCase(s.charAt(0))) {
return GET + s;
} else {
return GET + (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
}
}
public enum ReturnSuf {
XLS(".xls后缀", ".xls"),
/**
* 大数据量建议使用.xlsx
*/
XLSX(".xlsx后缀", ".xlsx"),
;
private String key;
private String value;
ReturnSuf(String key, String value) {
this.key = key;
this.value = value;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
}
三、构造实体类-任意创建一个实体类给属性加上注解，注解标识的是该属性是否被加入列,添加@Allow注解的属性会被列入文件列，注解属性title是表头
package com.osa.promote.common.util.excel;
public class LngAndLat {
public LngAndLat() {
}
public LngAndLat(String province, String city, String area, String street, String name) {
this.province = province;
this.city = city;
this.area = area;
this.street = street;
this.name = name;
}
@Allow(title = "省份")
private String province;
@Allow(title = "城市")
private String city;
@Allow(title = "大区")
private String area;
@Allow(title = "街道")
private String street;
@Allow(title = "店名")
private String name;
@Allow(title = "经度")
private String lng;
@Allow(title = "纬度")
private String lat;
@Allow(title = "参考")
private String remark;
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getArea() {
return area;
}
public void setArea(String area) {
this.area = area;
}
public String getStreet() {
return street;
}
public void setStreet(String street) {
this.street = street;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLng() {
return lng;
}
public void setLng(String lng) {
this.lng = lng;
}
public String getLat() {
return lat;
}
public void setLat(String lat) {
this.lat = lat;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
}
四、使用
//模拟五万条数据
List<LngAndLat> lst=new ArrayList<>();
for(int i=0;i<50000;i++){
lst.add(new LngAndLat("1","2","3","4","5"));
}
//一行代码即可生成WorkBook
Workbook workBook = ExcelCreateUtil.createWorkBook(lst, ExcelCreateUtil.ReturnSuf.XLSX);
workBook.write(new FileOutputStream(new File("C://Users/Administrator/Desktop/test.xlsx")));
workBook.close();
