包
<!-- poi -->
<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>
实体类
package com.demo.pojo;
import java.lang.annotation.*;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExportEntityMap {
String EnName() default "实体属性名称";
String CnName() default "excel标题名称";
}
package com.demo.pojo;
import java.lang.annotation.*;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExportProperty {
Class<? extends Enum> Converter();
}
package com.demo.pojo;
import lombok.Data;
@Data
public class SubmitA {
@ExportEntityMap(CnName = "", EnName = "")//参照上方ExportEntityMap
private String ----;
}
工具类
package com.demo.util;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.demo.pojo.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.*;
public class ExportExcelUtils {
/**
* 导出Excel
*
* @param list 要导出的数据集合
* @param c 中英文字段对应Map,即要导出的excel表头
* @param response 使用response可以导出到浏览器
*/
public static void export(List<List> list,
Class<?> c,
//Class<?> d,
HttpServletResponse response) {
try {
//创建一个WorkBook,对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
//创建表格
XSSFSheet sheet1 = createNewSheet(wb, "A平台");
XSSFSheet sheet2 = createNewSheet(wb, "B平台");
XSSFSheet sheet3 = createNewSheet(wb, "C平台");
//创建单元格,并设置值表头 设置表头居中
XSSFCellStyle style = wb.createCellStyle();
//设置边框
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//自动换行
///style.setWrapText(true);
//创建一个居中格式
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体
XSSFFont font = wb.createFont();
font.setFontName("宋体");
style.setFont(font);
// 填充工作表
//获取需要转出的excel表头的map字段
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
//循环注解里面的值 填入Link集合
Field[] declaredFields = c.getDeclaredFields();
//获取属性对应的枚举类
LinkedHashMap<String, Class> converterMap = new LinkedHashMap<>();
for (Field declaredField : declaredFields) {
//获取注解对象
ExportEntityMap declaredAnnotation = declaredField.getDeclaredAnnotation(ExportEntityMap.class);
ExportProperty converterAnnotation = declaredField.getDeclaredAnnotation(ExportProperty.class);
if (declaredAnnotation != null) {
fieldMap.put(declaredAnnotation.EnName(), declaredAnnotation.CnName());
if (converterAnnotation != null) {
converterMap.put(declaredAnnotation.EnName(), converterAnnotation.Converter());
}
}
}
//填充数据
fillSheet(sheet1, list.get(0), fieldMap, style, converterMap);
fillSheet(sheet2, list.get(1), fieldMap, style, converterMap);
//需要创建的表格数据和别的表格不一样可以在方法上添加对应的实体类class
//然后重复上面填充工作表的操作,修改对应的实体类class
/*// 填充工作表
//获取需要转出的excel表头的map字段
LinkedHashMap<String, String> fieldMaps = new LinkedHashMap<>();
//循环注解里面的值 填入Link集合
Field[] declaredFieldss = d.getDeclaredFields();
//获取属性对应的枚举类
LinkedHashMap<String, Class> converterMaps = new LinkedHashMap<>();
for (Field declaredField : declaredFieldss) {
//获取注解对象
ExportEntityMap declaredAnnotation = declaredField.getDeclaredAnnotation(ExportEntityMap.class);
ExportProperty converterAnnotation = declaredField.getDeclaredAnnotation(ExportProperty.class);
if (declaredAnnotation != null) {
fieldMaps.put(declaredAnnotation.EnName(), declaredAnnotation.CnName());
if (converterAnnotation != null) {
converterMaps.put(declaredAnnotation.EnName(), converterAnnotation.Converter());
}
}
}*/
//fillSheet(sheet3, list.get(2), fieldMaps, style, converterMaps);
//导出到本地
/* String resultName="";
try{
String ctxPath= "D://upFiles";
//名字是前一天的时间
String fileName=LocalDate.now().minusDays(1) +".xlsx";
String bizPath= "files";
String nowday= new SimpleDateFormat("yyyyMMdd").format(new Date());
File file= new File(ctxPath + File.separator + bizPath + File.separator +nowday);
if (!file.exists()) {
file.mkdirs();//创建文件根目录
}
String savePath= file.getPath() + File.separator +fileName;
resultName= bizPath + File.separator + nowday+ File.separator +fileName;
if (resultName.contains("\\")) {
resultName= resultName.replace("\\", "/");
}
System.out.print(resultName);
System.out.print(savePath);//响应到客户端需要下面注释的代码//this.setResponseHeader(response, filename);//OutputStream os = response.getOutputStream();//响应到服务器
OutputStream os = new FileOutputStream(savePath); //保存到当前路径savePath
wb.write(os);
os.flush();
os.close();
// for (int i = 1; i < list.size(); i++) {
// createNewSheet(excelName,savePath,"sheet" + i,list.get(i), fieldMap, style, converterMap);
// }
}catch(Exception e) {
e.printStackTrace();
}*/
//导出到浏览器
String fileName = URLEncoder.encode(excel, "UTF-8");
OutputStream outstream;
OutputStream os;
//创建加密器
POIFSFileSystem fs = new POIFSFileSystem();
EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
Encryptor enc = info.getEncryptor();
enc.confirmPassword("jinrui1506");//打开excel 密码
os = enc.getDataStream(fs);
wb.write(os);
os.close();
// 返回给浏览器
outstream = response.getOutputStream();
// 设置response头信息
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
response.setContentType("application/x-download");
fs.writeFilesystem(outstream);
outstream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//创建工作表Sheet
public static XSSFSheet createNewSheet(XSSFWorkbook wb, String excelName) {
//添加一个新的工作表
XSSFSheet sheet = wb.createSheet(excelName);
//设置 边距、页眉、页脚
XSSFPrintSetup printSetup = sheet.getPrintSetup();
//打印方向,true:横向,false:纵向(默认)
printSetup.setLandscape(true);
printSetup.setHeaderMargin(0.2);
printSetup.setFooterMargin(0.2);
//设置打印缩放为88%
///printSetup.setScale((short) 55);
printSetup.setFitHeight((short) 0);
printSetup.setFitWidth((short) 1);
//列从左向右显示②
/// printSetup.setLeftToRight(true);
// 纸张
printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
// 页边距(下)
sheet.setMargin(XSSFSheet.BottomMargin, 0.8);
// 页边距(左)
sheet.setMargin(XSSFSheet.LeftMargin, 0);
// 页边距(右)
sheet.setMargin(XSSFSheet.RightMargin, 0);
// 页边距(上)
sheet.setMargin(XSSFSheet.TopMargin, 0.8);
//设置打印页面为水平居中
sheet.setHorizontallyCenter(true);
sheet.setVerticallyCenter(true);
sheet.setAutobreaks(false);
sheet.setFitToPage(false);
Footer footer = sheet.getFooter();
//设置页数
footer.setCenter("第" + HeaderFooter.page() + "页,共 " + HeaderFooter.numPages() + "页");
Header header = sheet.getHeader();
//自定义页眉,并设置页眉 左中右显示信息
//居中
///header.setCenter("Center Header");
//靠左
header.setLeft(HSSFHeader.font("宋体", "") +
HSSFHeader.fontSize((short) 16) + excelName + ".xlsx");
return sheet;
}
/**
* 根据字段名获取字段对象
*
* @param fieldName 字段名
* @param clazz 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段,则返回
for (Field field : selfFields) {
//如果本类中存在该字段,则返回
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则,查看父类中是否存在此字段,如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
//递归
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有,则返回空
return null;
}
/**
* 根据字段名获取字段值
*
* @param fieldName 字段名
* @param o 对象
* @return 字段值
* @throws Exception 异常
*/
public static String getFieldValueByName(String fieldName, Object o)
throws Exception {
String fieldValue = null;
//根据字段名得到字段对象
Field field = getFieldByName(fieldName, o.getClass());
//如果该字段存在,则取出该字段的值
if (field != null) {
//类中的成员变量为private,在类外边使用属性值,故必须进行此操作
field.setAccessible(true);
//获取当前对象中当前Field的value
fieldValue = field.get(o) == null ? "" : field.get(o).toString();
String fieldType = field.getGenericType().toString();
if ("class java.math.BigDecimal".equals(fieldType) || "class java.lang.Double".equals(fieldType)) {
fieldValue = new BigDecimal(fieldValue).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
} else if ("class java.time.LocalDateTime".equals(fieldType) || "class java.time.LocalDate".equals(fieldType)) {
fieldValue = fieldValue.replace("T", " ");
}
} else {
throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 "
+ fieldName);
}
return fieldValue;
}
/**
* 根据带路径或不带路径的属性名获取属性值,即接受简单属性名, 如userName等,又接受带路径的属性名,如student.department.name等
*
* @param fieldNameSequence 带路径的属性名或简单属性名
* @param o 对象
* @return 属性值
* @throws Exception 异常
*/
public static String getFieldValueByNameSequence(String fieldNameSequence,
Object o) throws Exception {
String value = null;
// 将fieldNameSequence进行拆分
String[] attributes = fieldNameSequence.split("\\.");
if (attributes.length == 1) {
value = getFieldValueByName(fieldNameSequence, o);
} else {
// 根据数组中第一个连接属性名获取连接属性对象,如student.department.name
Object fieldObj = getFieldValueByName(attributes[0], o);
//截取除第一个属性名之后的路径
String subFieldNameSequence = fieldNameSequence
.substring(fieldNameSequence.indexOf(".") + 1);
//递归得到最终的属性对象的值
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
* 向工作表中填充数据
*
* @param sheet excel的工作表名称
* @param list 数据源
* @param fieldMap 中英文字段对应关系的Map
* @param style 表格中的格式
* @param converterMap 属性对应转化器的Map
* @throws Exception 异常
*/
public static <T> void fillSheet(XSSFSheet sheet, List<T> list,
LinkedHashMap<String, String> fieldMap, XSSFCellStyle style, LinkedHashMap<String, Class> converterMap) throws Exception {
// 定义存放英文字段名和中文字段名的数组
String[] enFields = new String[fieldMap.size()];
String[] cnFields = new String[fieldMap.size()];
// 填充数组
int count = 0;
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
//存储最大列宽
Map<Integer, Integer> maxWidth = new HashMap<>(16);
XSSFRow row = sheet.createRow(0);
XSSFCell cell = null;
// 填充表头
for (int i = 0; i < cnFields.length; i++) {
cell = row.createCell(i);
cell.setCellValue(cnFields[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
//设置自适应宽高
maxWidth.put(i, cell.getStringCellValue().getBytes().length * 256 + 200);
}
// 填充内容
for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + 1);
// 获取单个对象
T item = list.get(index);
int j = 0;
for (int i = 0; i < enFields.length; i++) {
XSSFCell createCell = row.createCell(j);
String objValue = getFieldValueByNameSequence(enFields[i], item);
//获取属性对应的枚举类
Class<Enum> converterClazz = converterMap.get(enFields[i]);
objValue = convertFieldValue(objValue, converterClazz);
String fieldValue = objValue == null ? "" : objValue;
cell = row.createCell(i);
createCell.setCellValue(fieldValue);
int length = createCell.getStringCellValue().getBytes().length * 256 + 200;
//这里把宽度最大限制到15000
if (length > 15000) {
length = 15000;
}
maxWidth.put(j, Math.max(length, maxWidth.get(j)));
j++;
createCell.setCellStyle(style);
}
}
// 列宽自适应
for (int i = 0; i < cnFields.length; i++) {
sheet.setColumnWidth(i, maxWidth.get(i));
}
}
/**
* 转换属性值
*
* @param objValue :
* @param converterClazz :
* @return java.lang.String
**/
private static <T> String convertFieldValue(String objValue, Class<Enum> converterClazz) throws Exception {
if (ObjectUtils.isNotNull(converterClazz)) {
//获取所有枚举实例
Enum[] enumConstants = converterClazz.getEnumConstants();
//根据方法名获取方法
Method valueMethod = converterClazz.getMethod("getValue");
if (ObjectUtils.isNull(valueMethod)) {
throw new Exception(converterClazz.getSimpleName() + "类找不到getValue()方法");
}
Method descMethod = converterClazz.getMethod("getDescription");
if (ObjectUtils.isNull(descMethod)) {
throw new Exception(converterClazz.getSimpleName() + "类找不到getDescription()方法");
}
for (Enum enumFiled : enumConstants) {
//执行枚举方法获得枚举实例对应的值
Object value = valueMethod.invoke(enumFiled);
Object description = descMethod.invoke(enumFiled);
if (org.springframework.util.ObjectUtils.nullSafeEquals(objValue, value.toString())) {
objValue = description.toString();
}
}
}
return objValue;
}
}
引用的话
ExportExcelUtils.export(数据集合, 用到的实体类.class,不知道会不会用到的实体类.class,response);
应该就这么多东西,有问题再说