<el-button type="primary" @click="exportMember()">导出</el-button>
exportMember() {
var baseUrl = process.env.VUE_APP_BASE_API; //项目路径
request.exportMember(baseUrl, this.user);//this.user 获取表格的数据
},
export function exportMember(baseUrl, data) {
exportRequest(baseUrl + '接口地址', data, "文件名");
}
function exportRequest(url, data, filename) {
axios({
url: url,
method: 'get',
params: data,
responseType: 'blob'
}).then((res) => {
let url = window.URL.createObjectURL(new Blob([res.data]));
// 生成一个a标签
let link = document.createElement("a");
link.style.display = "none";
link.href = url;
link.download = filename + ".xlsx";
document.body.appendChild(link);
link.click();
});
}
后台
@GetMapping("export")
public void export(User user) {
List<User> list = selfMemberService.list(user); //查询需要导出的数据
NewExcelUtil.exportExcel(SelfShopMemberExcel.class, list);
}
工具类
public class NewExcelUtil {
private static int dataRow = 2;
// private static Cache dictCache = EhCacheUtil.getDictCache();
/**
* 导出Excel数据
*
* @param entity 实体类Class
* @param list 导出的数据列表
*/
public static <T> void exportExcel(Class<?> entity, List<T> list) {
Excel excel = entity.getAnnotation(Excel.class);
if (excel != null) {
exportExcel(entity, list, excel.value());
} else {
exportExcel(entity, list, entity.getSimpleName());
}
}
/**
* 导出Excel数据
*
* @param entity 实体类Class
* @param list 导出的数据列表
* @param sheetTitle 工作组标题(文件名称)
*/
public static <T> void exportExcel(Class<?> entity, List<T> list, String sheetTitle) {
List<Field> fields = getExcelList(entity, ExcelType.EXPORT);
List<String> fns = getFieldName(fields);
SXSSFWorkbook sxssfWorkbook = getCommon(sheetTitle, fields);
SXSSFSheet sheet = sxssfWorkbook.getSheet(sheetTitle);
// 时间样式
CellStyle dateStyle = sxssfWorkbook.createCellStyle();
dateStyle.cloneStyleFrom(dateStyle);
DataFormat format = sxssfWorkbook.createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(i + dataRow);
T item = list.get(i);
// 通过反射机制获取实体对象的状态
try {
final BeanInfo bi = Introspector.getBeanInfo(item.getClass());
for (final PropertyDescriptor pd : bi.getPropertyDescriptors()) {
if (fns.contains(pd.getName())) {
Object value = pd.getReadMethod().invoke(item, (Object[]) null);
int index = fns.indexOf(pd.getName());
Cell cell = row.createCell(index);
if (value != null) {
Excel excel = fields.get(index).getAnnotation(Excel.class);
// 字典值转换--此项目弃用
// String dict = excel.dict();
// if (!dict.isEmpty()) {
// //查询值
// Element dictEle = dictCache.get(dict);
// if (dictEle != null) {
// @SuppressWarnings("unchecked") Map<String, String> dictValue = (Map<String, String>) dictEle.getObjectValue();
// value = dictValue.get(String.valueOf(value));
// }
// }
// 手动字典值转换
String diyDict = excel.diyDict();
if (!diyDict.isEmpty()){
value =getMsg(diyDict,value.toString());
}
// 获取关联对象指定的值
String joinField = excel.joinField();
if (!joinField.isEmpty()) {
PropertyDescriptor sourcePd = BeanUtils.getPropertyDescriptor(value.getClass(), joinField);
value = sourcePd.getReadMethod().invoke(value, (Object[]) null);
}
// 给单元格赋值
if (value instanceof Number) {
cell.setCellValue((Double.valueOf(String.valueOf(value))));
} else if (value instanceof Date) {
cell.setCellValue(simpleDateFormat.format(value));
// cell.setCellStyle(dateStyle);
sheet.setColumnWidth(index,256*20);
} else {
cell.setCellValue(String.valueOf(value));
}
}
}
}
} catch (InvocationTargetException | IntrospectionException | IllegalAccessException e) {
String message = "导入失败:字段名称匹配失败!";
throw new IllegalArgumentException(message, e);
}
}
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
download(sxssfWorkbook, sheetTitle + dateFormat.format(new Date()));
}
/**
* 获取实体类带有@Excel的属性
*/
private static List<Field> getExcelList(Class<?> entity, ExcelType type) {
List<Field> list = new ArrayList<>();
Field[] fields = entity.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(Excel.class)) {
ExcelType fieldType = field.getAnnotation(Excel.class).type();
if (fieldType.equals(ExcelType.ALL) || fieldType.equals(type)) {
list.add(field);
}
}
}
return list;
}
/**
* 获取实体类带有@Excel字段名
*/
private static List<String> getFieldName(List<Field> fields) {
List<String> list = new ArrayList<>();
for (Field field : fields) {
list.add(field.getName());
}
return list;
}
/**
* 功能模板(标题及表头)
*/
private static SXSSFWorkbook getCommon(String sheetTitle, List<Field> fields) {
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet sheet = workbook.createSheet(sheetTitle);
// 设置列宽度
for (int i = 0; i < fields.size(); i++) {
sheet.setColumnWidth(i, 16 * 256);
}
// 通用样式
CellStyle cellStyle = getCellStyle(workbook);
// 标题样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.cloneStyleFrom(cellStyle);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontName(cellStyle.getDataFormatString());
titleFont.setBold(true);
titleStyle.setFont(titleFont);
titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 表头样式
CellStyle thStyle = workbook.createCellStyle();
thStyle.cloneStyleFrom(titleStyle);
thStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
thStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font thFont = workbook.createFont();
thFont.setFontName(cellStyle.getDataFormatString());
thFont.setBold(titleFont.getBold());
thFont.setColor(IndexedColors.WHITE.getIndex());
thStyle.setFont(thFont);
// 创建标题样式、表格表头
SXSSFRow titleRow = sheet.createRow(0);
SXSSFRow thsRow = sheet.createRow(1);
for (int i = 0; i < fields.size(); i++) {
Excel excel = fields.get(i).getAnnotation(Excel.class);
Cell th = thsRow.createCell(i);
th.setCellValue(excel.value());
th.setCellStyle(thStyle);
}
// 绘制标题
titleRow.setHeight((short) (26 * 20));
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(sheetTitle);
titleCell.setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fields.size() - 1));
return workbook;
}
/**
* 获取通用样式
*/
private static CellStyle getCellStyle(SXSSFWorkbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
Font font = workbook.createFont();
font.setFontName("Microsoft YaHei UI");
cellStyle.setFont(font);
return cellStyle;
}
/**
* 下载操作
*/
private static void download(SXSSFWorkbook workbook, String fileName) {
try {
fileName = URLEncoder.encode(fileName + ".xlsx", "utf-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = servletRequestAttributes.getResponse();
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
OutputStream ros = null;
try {
ros = response.getOutputStream();
workbook.write(ros);
ros.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (ros != null) {
try {
ros.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 根据不同的值拿到说明
*/
public static String getMsg(String dictValue,String key){
String[] outerSplit = dictValue.split(",");
for (String osp : outerSplit) {
String[] split = osp.split("[.]");
if(split.length > 1){
if (split[0].equals(key)){
return split[1];
}
}
}
return null;
}
}```