一、导包
compile 'cn.hutool:hutool-all:4.5.15' compile 'org.apache.poi:poi-ooxml:3.17'
导出示例
OutputStream out = new FileOutputStream("D:\\aa.xlsx");
List<String[]> titleAliasList = new ArrayList<>();
titleAliasList.add(new String[]{"startDate", "开始时间"});
titleAliasList.add(new String[]{"endDate", "结束时间"});
titleAliasList.add(new String[]{"page", "分页索引"});
titleAliasList.add(new String[]{"size", "页元素大小"});
List<DateQuery> rows = new ArrayList<>();
rows.add(new DateQuery("ha1", "he5", 1, 5));
rows.add(new DateQuery("ha2", "he4", 2, 4));
rows.add(new DateQuery("ha3", "he3", 3, 3));
rows.add(new DateQuery("ha4", "he2", 4, 2));
rows.add(new DateQuery("ha5", "he1", 5, 1));
String title = "myTitle";
// 生成xlsx
ExcelWriter writer = ExcelUtil.getWriter(true);
writer.renameSheet("mySheet");
titleAliasList.forEach(arr -> writer.addHeaderAlias(arr[0], arr[1]));
//合并单元格后的标题行,使用默认标题样式
writer.merge(titleAliasList.size() - 1, title);
// 设置标题样式
StyleSet style = writer.getStyleSet();
CellStyle headCellStyle = style.getHeadCellStyle();
//水平居中
headCellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置内容字体
Font font = writer.createFont();
//加粗
font.setBold(true);
//设置标题字体大小
font.setFontHeightInPoints((short)12);
headCellStyle.setFont(font);
// 一次性写出内容,使用默认样式,强制输出标题
writer.write(rows, true);
//out为OutputStream,需要写出到的目标流
writer.flush(out);
// 关闭writer,释放内存
writer.close();
返回给前端下载
public static void excelExport(String fileName, String sheetName, String title, List<HeaderTitle> titleAliasList, List<?> rows, HttpServletResponse response) throws IOException {
// 生成xlsx
ExcelWriter writer = ExcelUtil.getWriter(true);
writer.renameSheet(sheetName);
titleAliasList.forEach(h -> writer.addHeaderAlias(h.getKey(), h.getName()));
//合并单元格后的标题行,使用默认标题样式
writer.merge(titleAliasList.size() - 1, title);
// 设置标题样式
StyleSet style = writer.getStyleSet();
CellStyle headCellStyle = style.getHeadCellStyle();
//水平居中
headCellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置内容字体
Font font = writer.createFont();
//加粗
font.setBold(true);
//设置标题字体大小
font.setFontHeightInPoints((short)12);
headCellStyle.setFont(font);
// 一次性写出内容,使用默认样式,强制输出标题
writer.write(rows, true);
//导出数据
OutputStream out = null;
try {
//设置Http响应头告诉浏览器下载这个附件
response.setHeader("Content-Disposition", "attachment;Filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
response.setHeader("Content-Transfer-Encoding", "binary");
out = response.getOutputStream();
writer.flush(out, true);
// 关闭writer,释放内存
writer.close();
} catch (Exception ex) {
log.info("导出Excel异常,异常信息");
throw new IOException("导出Excel异常,异常信息:" + ex.getMessage());
} finally {
//清理资源
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
log.info("清理资源错误");
}
}
}