1.导入依赖
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
</dependencies>
2.处理代码（写的有点拉跨）
package com.sjyf.gym.utils;
import com.sjyf.gym.annotation.Excel;
import com.sjyf.gym.ex.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.*;
/**
* @author 何杰
* @date 2020年5月20日
*/
public class ExcelUtil {
/**
* @Title: createEcxel
* @Description: 通过属性组创建Excel，序号自动生成
* @param title
* 标题
* @param property
* 对象的属性名
* @param coll
* 需要生成表格的对象
* @param suffix
* 后缀名 xls || xlsx
* @throws SecurityException
* @throws NoSuchFieldException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws IOException
*/
public static SXSSFWorkbook createEcxelByGroups(String[] title, String[] property,
List<?> coll, PoiType suffix) {
if (suffix == null) {
throw new RuntimeException("请选择文件后缀名");
}
SXSSFWorkbook wb = null;
if (suffix == PoiType.XLS) {
wb = new SXSSFWorkbook();
} else {
wb = new SXSSFWorkbook();
}
Sheet st = wb.createSheet();
// 创建第一行
Row rowone = st.createRow(0);
// 设置标题
for (int i = 0; i <= title.length; i++) {
Cell cell = rowone.createCell(i);
if (i == 0) {
cell.setCellValue("序号");
continue;
}
cell.setCellValue(title[i - 1]);
}
// 填充值
try {
for (int i = 0; i < coll.size(); i++) {
Class<? extends Object> clazz = coll.get(i).getClass();
Row row = st.createRow(i + 1);
for (int j = 0; j <= property.length; j++) {
// 创建j++列
Cell cell = row.createCell(j);
if (j == 0) {
cell.setCellValue(i + 1);
continue;
}
// 判断要获取的属性名是否包含.
if(property[j - 1].indexOf(".") == -1){
// 不包含
Field field = null;
try {
field = coll.get(i).getClass()
.getDeclaredField(property[j - 1]);
} catch (Exception e) {
field = clazz.getSuperclass().getDeclaredField(property[j - 1]);
}
Method method = clazz.getMethod("get" + toUpperCase(field.getName()));
Object invoke = method.invoke(coll.get(i));
// 设置对象的访问权限，保证对private的属性的访问
String classType = field.getType().toString();
if (classType.equals("class java.util.Date")){
String format = dateFormat(invoke);
cell.setCellValue(format == null ? "" : format);
continue;
}
if(classType.equals("class java.time.LocalDateTime")){
Date date = localDateTimeToDate((LocalDateTime) invoke);
String format = dateFormat(date);
cell.setCellValue(format == null ? "" : format);
continue;
}
Excel excel = field.getAnnotation(Excel.class);
if(excel != null){
String value = getValue(excel.value(), invoke + "");
cell.setCellValue(value);
}else{
cell.setCellValue(invoke == null ? "" : invoke + "");
}
}else{
// 包含
Field field = coll.get(i).getClass()
.getDeclaredField(property[j - 1].substring(0, property[j - 1].lastIndexOf(".")));
// 设置对象的访问权限，保证对private的属性的访问
field.setAccessible(true);
Object o = field.get(coll.get(i));
// 判断是否属于基本数据类型，或是否包含.
if(!typeDict(o) && property[j - 1].indexOf(".") != -1){
String value = getFieldValue(o, property[j - 1]);
cell.setCellValue(value == null ? "" : value + "" );
continue;
}
cell.setCellValue(o == null ? "" : o + "");
}
}
}
} catch (Exception e) {
e.printStackTrace();
throw new BusinessException("导出错误，请联系管理员");
}
return wb;
}
/**
*
* @Description: 获取字段值
* @param @param obj 对象
* @param @param field 获取的字段名
* @param @return
* @return String
* @throws
*/
public static String getFieldValue(Object obj, String field){
// 截取字段名，只支持一级，obj -> obj，
try {
String fname = field.substring(field.lastIndexOf(".") + 1,field.length());
// 创建j++列
Field d = obj.getClass().getDeclaredField(fname);
// 设置对象的访问权限，保证对private的属性的访问
d.setAccessible(true);
Object o = d.get(obj);
return o + "";
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 判断是否是常用基本数据类型
*/
public static boolean typeDict(Object obj){
Class<?>[] clazzs = {int.class,short.class,double.class,
float.class,byte.class,long.class,char.class,boolean.class,
String.class,Double.class,Float.class,Integer.class,Long.class,
Short.class,Boolean.class,Character.class,Byte.class,BigDecimal.class};
for (Class<?> c : clazzs) {
if(obj.getClass().isAssignableFrom(c)){
return true;
}
}
return false;
}
public static String toUpperCase(String name){
return name.substring(0,1).toUpperCase() + name.substring(1,name.length());
}
public static void main(String[] args) {
toUpperCase(null);
}
public enum PoiType {
XLS, XLSX;
}
public static Date localDateTimeToDate(LocalDateTime localDateTime){
ZoneId zoneId = ZoneId.systemDefault();
ZonedDateTime zdt = localDateTime.atZone(zoneId);
return Date.from(zdt.toInstant());
}
/**
* 导出
*/
public static void exportExcel(SXSSFWorkbook wb, String fileName){
OutputStream out = null;
try {
HttpServletResponse response = HttpContextUtils.getHttpServletResponse();
response.reset();
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),
"ISO-8859-1") + ".xlsx");
out = response.getOutputStream();
wb.write(out);
} catch (Exception e) {
throw new BusinessException("导出Excel失败，请联系管理员！");
} finally {
if (wb != null) {
try {
wb.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
if (out != null) {
try {
out.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
}
public static String dateFormat(Object obj){
if(Objects.isNull(obj)){
return "";
}
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(obj);
}
/**
* 获取字段的字典值
*/
public static Map<String, String> getDict(String dictValues) {
// String dictValues = "1=待学员确认,2=待教练确认,3=待销课,4=已销课待评价,5=已销课已评价,6=已退还,7=已取消";
String[] keyAndValues = dictValues.split(",");
Map<String, String> values = new HashMap<>(keyAndValues.length);
for (String value : keyAndValues) {
String[] key = value.split("=");
values.put(key[0], key[1]);
}
return values;
}
public static String getValue(String dictValues, String key){
Map<String, String> dict = getDict(dictValues);
if (dict == null || dict.size() == 0){
return "";
}
return dict.get(key);
}
public static XSSFSheet getSheet() {
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream("C:\\Users\\qian\\Desktop\\私教模板(1)(2).xlsx");
XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = sheets.getSheetAt(0);
return sheet;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
3.所使用的注解类
package com.sjyf.gym.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
String value() default "";
}
4.注解类的使用方法
首先说明为什么要使用注解，因为有些类是int值，当然你可以在数据库用case when去处理，使用格式（1=正常,2=过期,3=停用）
程序会按照相应的int值匹配，去设置其相应的注释值。
@Excel("1=正常,2=过期,3=停用")
private Integer status;
5.使用（就是这么简单，当然还能更简单，可以使用注解，然后反射得到注解的值和key，自行发挥吧！）
List<ContractPersonalExportVo> personals = contractPersonalMapper.exportExcel();
String[] titles = {"门店", "虚拟会员卡号", "会员姓名", "手机号码", "性别", "身份证号", "课程名称", "总课时",
"剩余课时", "总金额", "销售员", "状态", "单价", "合同编号", "创建时间"};
String[] propertys = {"storeName", "virtualCardNum", "userName", "phone", "sex", "idCard", "courseName",
"totalNum", "surplusNum", "totalAmt", "nickName", "status", "avgPrice", "code", "createTime"};
SXSSFWorkbook workbook = ExcelUtil.createEcxelByGroups(titles, propertys, personals, ExcelUtil.PoiType.XLSX);
ExcelUtil.exportExcel(workbook, "业务合同");