导出数据集合到Excel里,我这里做了一个通用的处理,但还不是很精确的通用,不过应对当前使用是没什么问题。
先定义一个接口,让需要调用导出方法的类去继承这个接口,重写一下方法(目的就是指定下导出时,表头的顺序):
public interface ResultList {
/**
* 获取标题数组方法 映射类中字段顺序的先后顺序即重写的数组字段顺序,两者顺序需要一致
* @return
*/
String[] getTitles();
}
这是一种方法,还有一种是提前把表头通过数组的形式存到enum类里面,key对应导出的类名,value对应保存表头顺序的数组,在导出方法里面通过getClass()获取类名,然后去获取相应的表头数组即可。
/**
* 导出excel表
*
* @param request 请求
* @param response 响应
* @param fileName 输出文件名
* @param sheetName 表名
* @param dataList 任意类型的list集合
*/
public static void exportExcel(HttpServletRequest request, HttpServletResponse response,
String fileName, String sheetName,
List<Object> dataList) {
if (CollectionUtils.isEmpty(dataList)) {
return;
}
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Access-Control-Allow-Credentials", "true");
response.setHeader("Access-Control-Allow-Origin", request.getHeader("Origin"));
response.setHeader("Access-Control-Allow-Headers", request.getHeader("Access-Control-Request-Headers"));
response.setHeader("Access-Control-Allow-Methods", request.getHeader("Access-Control-Request-Method"));
response.setHeader("Access-Control-Max-Age", "1800");
response.setHeader("Vary", "Origin, Access-Control-Request-Method, Access-Control-Request-Headers");
try {
OutputStream os = response.getOutputStream();
// response.reset();
// 设定输出文件头
response.setHeader(
"Content-disposition",
"attachment; filename="
+ new String((fileName)
.getBytes("GB2312"), "ISO8859-1"));
//获取对应标题头
ResultList classname = (ResultList) dataList.get(0);
String[] titles = classname.getTitles();
// 创建工作簿
WritableWorkbook workbook = jxl.Workbook.createWorkbook(os);
// 创建工作表
WritableSheet sheet = workbook.createSheet(sheetName, 0);
// 设置单元格宽度
for (int i = 0; i < titles.length; i++) {
sheet.setColumnView(i, 15);
}
// 设置纵横打印(默认为纵打)、打印纸
jxl.SheetSettings sheetset = sheet.getSettings();
sheetset.setProtected(false);
// 设置单元格字体
WritableFont normalFont = new WritableFont(WritableFont.ARIAL, 11);
WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.NO_BOLD);
WritableFont headFont = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD);
// 报表头部
WritableCellFormat wcfHead = new WritableCellFormat(headFont);
wcfHead.setBorder(Border.ALL, BorderLineStyle.THIN);
wcfHead.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfHead.setAlignment(Alignment.CENTRE);
wcfHead.setWrap(false);
// 用于标头居中
WritableCellFormat wcfCenter = new WritableCellFormat(boldFont);
wcfCenter.setBorder(Border.ALL, BorderLineStyle.THIN);
wcfCenter.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfCenter.setAlignment(Alignment.CENTRE);
wcfCenter.setWrap(false);
// 用于正文居中
WritableCellFormat wcfLeft = new WritableCellFormat(normalFont);
// 添加边框
wcfLeft.setBorder(Border.ALL, BorderLineStyle.THIN);
wcfLeft.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// 设置对齐方式
wcfLeft.setAlignment(Alignment.CENTRE);
wcfLeft.setWrap(true);
// EXCEL第一行列标题
AtomicInteger headersAi = new AtomicInteger();
for (String title : titles) {
int andIncrement = headersAi.getAndIncrement();
sheet.addCell(new Label(andIncrement, 0, title, wcfHead));
}
Cell cell = null;
AtomicInteger dataAi = new AtomicInteger(1);
for (Object o : dataList) {
Field[] declaredFields = o.getClass().getDeclaredFields();
int rowIndex = dataAi.getAndIncrement();
int i = 0;
for (Field declaredField : declaredFields) {
declaredField.setAccessible(true);
try {
if (ObjectUtil.isEmpty(declaredField.get(o))) {
sheet.addCell(new Label(i, rowIndex, "", wcfCenter));
} else {
sheet.addCell(new Label(i, rowIndex, declaredField.get(o).toString(), wcfCenter));
}
i++;
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
workbook.write();
workbook.close();
} catch (Exception e) {
log.error(fileName + "文件导出异常");
}
}