js跳转至controller
function exportInfo(){
window.open("../bondExport");
}
controller伪代码
@RequestMapping(value = "/bondExport")
@ResponseBody
public void bondExport(HttpServletRequest request, HttpServletResponse response) throws IOException, IllegalAccessException {
//后台查询返回的原始数据
List<RzPoolBondCollection> bondList = rzBorrowRefactorService.queryBondListPage();
//用于导出excel的数据类
List<RzPoolBondCollectionExportVO> bondVOList = new ArrayList<>();
//将bondList和bondVOList含有的相同属性复制bondList-->bondVOList
for(int i = 0; i < bondList.size() ; i++) {
RzPoolBondCollectionExportVO bondVO = new RzPoolBondCollectionExportVO();
BeanUtils.copyProperties(bondList.get(i), bondVO);
if(bondVO.getStatus() == 0 ) {
bondVO.setStatus2("未收款");
}
if(bondVO.getStatus() == 1 ) {
bondVO.setStatus2("已收款");
}
bondVOList.add(bondVO);
}
ExcelExportUtil<RzPoolBondCollectionExportVO> excelUtil = new ExcelExportUtil<>(RzPoolBondCollectionExportVO.class);
try {
excelUtil.export("债权转让列表", bondVOList, 2 , response);
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
XxxExportVO类
public class RzPoolBondCollectionExportVO {
@ExceVo(name = "投资用户ID",sort = 1)
private Integer userId;
@ExceVo(name = "大标名称",sort = 2)
private String name;
@ExceVo(name = "大标编号",sort = 3)
private Integer borrowId;
@ExceVo(name = "已转出本金",sort = 4)
private BigDecimal bondCapital;
//getter和setter不能省略
}
ExcelVO类
/**
* @des Excel 注解
* @author mapp 2018/2/10.
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExceVo {
/** 对应的列名称 */
String name() default "";
/** 列序号 */
int sort();
/** 字段类型对应的格式 */
String format() default "";
/** 是否需要校验 */
boolean isCheck() default false;
/** 校验字段长度 */
int fieldLength() default 50;
/** 校验是否可以为空 */
boolean isEmpty() default true;
}
ExcelExportUtil类
public class ExcelExportUtil<T> {
private Class claze;
public ExcelExportUtil(Class claze) {
this.claze = claze;
}
/**
* 基于模板导出下载
* @param fileName 模板名称
* @param objs 导出实体集合
* @param rowIndex excel第几行开始导出
*/
public void export(String fileName, List<T> objs, int rowIndex, HttpServletResponse response) throws IllegalAccessException {
Workbook workbook = new XSSFWorkbook();
// 带注解并排序好的字段
Map<String,List> fieldmap= getFieldList();
List<Field> fieldList = fieldmap.get("value");
List<String> namelist = fieldmap.get("name");
//设置样式
CellStyle style = setCellStyle(workbook);
Sheet sheet = workbook.createSheet("First Sheet");
Row row = sheet.createRow(0);
//填充标题
for (int i = 0; i < namelist.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(namelist.get(i));
}
//填充内容
for (int i = 0; i < objs.size(); i++) {
row = sheet.createRow(i + rowIndex - 1);
for (int j = 0; j < fieldList.size(); j++) {
Cell cell = row.createCell(j);
cell.setCellStyle(style);
Field field = fieldList.get(j);
String fieldValue = covertAttrType(field, objs.get(i));
cell.setCellValue(fieldValue);
}
}
//输出文件下载流
try {
response.reset();
//设置生成的文件类型
response.setContentType("application/msexcel");
//设置文件头编码方式和文件名
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + new String((fileName+".xlsx").getBytes("utf-8"), "ISO8859-1"));
OutputStream os=response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置样式
* @param workbook
*/
@SuppressWarnings("deprecation")
private CellStyle setCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//设置字体
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 12);
style.setFont(font);
//设置居中
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
return style;
}
/**
* 获取带注解的字段 并且排序
* @return
*/
private Map<String,List> getFieldList() {
Field[] fields = this.claze.getDeclaredFields();
// 无序
List<Field> fieldList = new ArrayList<Field>();
// 排序后的字段
List<Field> fieldSortList = new LinkedList<Field>();
List<String> namelist = new LinkedList<String>();
int length = fields.length;
int sort = 0;
Field field = null;
// 获取带注解的字段
for (int i = 0; i < length; i++) {
field = fields[i];
if (field.isAnnotationPresent(ExceVo.class)) {
fieldList.add(field);
}
}
//判断是否为空 空则抛出异常 中断程序
Assert.assertNotNull("未获取到需要导出的字段", fieldList);
length = fieldList.size();
Map<String , List >map=new HashMap<>();
//获取排好序的字段及标题
for (int i = 1; i <= length; i++) {
for (int j = 0; j < length; j++) {
field = fieldList.get(j);
ExceVo exceVo = field.getAnnotation(ExceVo.class);
//对所有属性设置访问权限 当类中的成员变量为private时 必须设置此项
field.setAccessible(true);
sort = exceVo.sort();
String name = exceVo.name();
if (sort == i) {
fieldSortList.add(field);
namelist.add(name);
continue;
}
}
}
map.put("value", fieldSortList);
map.put("name", namelist);
return map;
}
/**
* 类型转换 转为String
*/
private String covertAttrType(Field field, T obj) throws IllegalAccessException {
if (field.get(obj) == null) {
return "";
}
ExceVo exceVo = field.getAnnotation(ExceVo.class);
String type = field.getType().getSimpleName();
String format = exceVo.format();
if ("Date".equals(type)) {
return DateFormatUtils.format((Date)field.get(obj), "yyyy-MM-dd");
}else {
return field.get(obj).toString();
}
}
}