Spring Boot Excel 文件导出
目标：
实现Excel文件的直接导出下载，后续开发不需要开发很多代码，直接继承已经写好的代码，增加一个Xml配置就可以直接导出。
实现：
1、抽象类 BaseExcelView 继承 webmvc 的 AbstractXlsxStreamingView 抽象类， AbstractXlsxStreamingView 是webmvc继承了最顶层View接口，是可以直接大量数据导出的不会造成内存泄漏问题，即 SXSSFWorkbook 解决了内存问题， 导出只支持xlsx类型文件。
抽象类代码 BaseExcelView ：
public abstract class BaseExcelView extends AbstractXlsxStreamingView {
private static final Logger logger = LoggerFactory.getLogger(BaseExcelView.class);
/**
* 获取导出文件名
*
* @return
*/
abstract protected String getFileName();
/**
* 获取表单名称
*
* @return
*/
abstract protected String getSheetName();
/**
* 获取标题栏名称
*
* @return
*/
abstract protected String[] getTitles();
/**
* 获取列宽
*
* @return
*/
abstract protected short[] getColumnWidths();
/**
* 构造内容单元格
*
* @param sheet
*/
abstract protected void buildContentCells(Sheet sheet);
@Override
protected void buildExcelDocument(
Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 构造标题单元格 SXSSFWorkbook
Sheet sheet = buildTitleCells(workbook);
// 构造内容单元格
buildContentCells(sheet);
// 设置响应头
setResponseHead(request, response);
}
/**
* 设置响应头
*
* @param response
* @throws IOException
*/
protected void setResponseHead(HttpServletRequest request,
HttpServletResponse response) throws IOException {
// 文件名
String fileName = getFileName();
String userAgent = request.getHeader("user-agent").toLowerCase();
logger.info("客户端请求头内容:");
logger.info("user-agent\t值: {}", userAgent);
if (userAgent != null) {
if (userAgent.contains("firefox")) {
// firefox有默认的备用字符集是西欧字符集
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
} else if (userAgent.contains("webkit") && (userAgent.contains("chrome") || userAgent.contains("safari"))) {
// webkit核心的浏览器,主流的有chrome,safari,360
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
} else {
// 新老版本的IE都可直接用URL编码工具编码后输出正确的名称,无乱码
fileName = URLEncoder.encode(fileName, "UTF-8");
}
}
//响应头信息
response.setCharacterEncoding("UTF-8");
response.setContentType("application/ms-excel; charset=UTF-8");
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
}
/**
* 构造标题单元格
*
* @param
* @return
*/
protected Sheet buildTitleCells(Workbook workbook) {
// 表单名称
String sheetName = getSheetName();
// 标题名称
String[] titles = getTitles();
// 列宽
short[] colWidths = getColumnWidths();
// 创建表格
Sheet sheet = workbook.createSheet(sheetName);
// 标题单元格样式
CellStyle titleStyle = getHeadStyle(workbook);
// 默认内容单元格样式
CellStyle contentStyle = getBodyStyle(workbook);
// 标题行
Row titleRow = sheet.createRow(0);
// 创建标题行单元格
for (int i = 0; i < titles.length; i++) {
// 标题单元格
Cell cell = titleRow.createCell((short) i);
cell.setCellType(CellType.STRING);
cell.setCellValue(new XSSFRichTextString(titles[i]));
cell.setCellStyle(titleStyle);
// 设置列宽
sheet.setColumnWidth((short) i, (short) (colWidths[i] * 256));
// 设置列默认样式
sheet.setDefaultColumnStyle((short) i, contentStyle);
}
return sheet;
}
/**
* 设置表头的单元格样式
*/
public CellStyle getHeadStyle(Workbook workbook) {
// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
// 设置填充字体的样式
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
Font font = workbook.createFont();
// 字号
font.setFontHeightInPoints((short) 12);
// 加粗
font.setBold(true);
// 将字体填充到表格中去
cellStyle.setFont(font);
// 设置单元格边框为细线条（上下左右）
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
return cellStyle;
}
/**
* 设置表体的单元格样式
*/
public CellStyle getBodyStyle(Workbook workbook) {
// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置单元格居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 创建单元格内容不显示自动换行
cellStyle.setWrapText(true);
//设置单元格字体样式字体
Font font = workbook.createFont();
// 字号
font.setFontHeightInPoints((short) 10);
// 将字体添加到表格中去
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
return cellStyle;
}
}
Excel导出实现 1: 可以直接继承 BaseExcelView 实现定义的方法 eg:
public class CheckExcelView extends BaseExcelView {
private List<T> vo;
public CheckExcelView(List<T> vo) {
this.vo= vo;
}
@Override
protected String getFileName() {
String time = DateUtils.getLocalFullDateTime14();
return "导出文件" + time;
}
@Override
protected String getSheetName() {
return "报表";
}
@Override
protected String[] getTitles() {
return new String[] { "申请时间"};
}
@Override
protected short[] getColumnWidths() {
return new short[] { 20};
}
@Override
protected void buildContentCells(Sheet sheet) {
DecimalFormat df = new DecimalFormat("0.00");
int rowNum = 1;
for (T o : vO) {
Row crow = sheet.createRow(rowNum++);
crow.createCell(0).setCellValue(o.getApplicationDate()));
}
}
}
导出实现 2： XML配置导出
1、需要定义XML的配置 export-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<table id="demo" name="测试">
<columns>
<column id="name" name="名称" width="40"></column>
</columns>
</table>
</configuration>
2、XMl解析配置
@Root
public class Export {
@ElementList(entry = "table", inline = true)
private List<Table> table;
public List<Table> getTable() {
return table;
}
public void setTable(List<Table> table) {
this.table = table;
}
public static class Table {
@Attribute
private String id;
@Attribute
private String name;
@ElementList(entry = "column")
private List<Column> columns;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Column> getColumns() {
return columns;
}
public void setColumns(List<Column> columns) {
this.columns = columns;
}
}
public static class Column {
@Attribute
private String id;
@Attribute
private String name;
@Attribute
private short width;
@Attribute(required = false)
private String mapping;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMapping() {
return mapping;
}
public void setMapping(String mapping) {
this.mapping = mapping;
}
public short getWidth() {
return width;
}
public void setWidth(short width) {
this.width = width;
}
}
}
3、解析XMl方法配置
@Service
public class IExportService {
private Export tables;
private Map<String, Export.Table> tableMap;
@SuppressWarnings("rawtypes")
@PostConstruct
public void init() throws Exception {
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("export-config.xml");
Serializer serializer = new Persister();
tables = serializer.read(Export.class, inputStream);
tableMap = new HashMap<>();
for (Export.Table table : tables.getTable()) {
tableMap.put(table.getId(), table);
}
}
public Export.Table getTable(String key) {
return tableMap.get(key);
}
}
4、导出基础 ExcelExportView 代码实现
public class ExcelExportView extends BaseExcelView {
private String[] titles;
private short[] columnWidths;
List<Map<String, Object>> results;
private Export.Table table;
private IExportService iExportService;
@Override
protected String getFileName() {
return table.getName();
}
@Override
protected String getSheetName() {
return table.getName();
}
@Override
protected String[] getTitles() {
return this.titles;
}
@Override
protected short[] getColumnWidths() {
return this.columnWidths;
}
public ExcelExportView() {
this.iExportService = ApplicationContextProvider.getBean(IExportService.class);
}
@Override
protected void buildContentCells(Sheet sheet) {
int dataIndex = 1;
if(CollectionUtils.isEmpty(results)){
return;
}
for (Map<String, Object> data : results) {
Row row = sheet.createRow(dataIndex++);
for (int i = 0; i < table.getColumns().size(); i++) {
Export.Column column = table.getColumns().get(i);
Cell cell = row.createCell(i);
Object value = data.get(column.getId());
if (value == null) {
value = "";
}
cell.setCellValue(new XSSFRichTextString(value.toString()));
}
}
}
public void exportExcel(String key, List<Map<String, Object>> results) {
this.table = iExportService.getTable(key);
if (null == table) {
return;
}
this.results = results;
this.titles = new String[table.getColumns().size()];
this.columnWidths = new short[table.getColumns().size()];
for (int i = 0; i < table.getColumns().size(); i++) {
Export.Column column = table.getColumns().get(i);
titles[i] = column.getName();
columnWidths[i] = column.getWidth();
}
}
}
最后：导出Controller代码实现
@RequestMapping(path = "/export", method = RequestMethod.GET, produces = "application/octet-stream;charset=UTF-8")
public @ResponseBody
ModelAndView export(){
Long loginComId = loginContext.getCompany().getId();
List<T> list = new ArrayList<>();
ExcelExportView exportView = new ExcelExportView();
exportView.exportExcel("XMl中表的ID", BeanUtils.objectToMapList(list));
return new ModelAndView(exportView);
<em id="__mceDel"><em id="__mceDel">}</em></em>
总结
以上所述是小编给大家介绍的Spring Boot Excel文件导出下载实现代码，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！
