- 导入依赖
- controller 层
- Service层
- ServiceImpl层
- ExcelUtils
- 前端
- 效果
首先将EasyExcel依赖导入项目中
com.alibaba easyexcel 2.2.6
然后复制一下代码。有些地方根据自己需求进行改动
controller 层@GetMapping("/export") public void export(HttpServletResponse resp) throws IOException { orderHandleService.export(resp); }Service层
void export(HttpServletResponse resp) throws IOException;ServiceImpl层
@Override public void export(HttpServletResponse resp) { try { // LambdaQueryWrapperExcelUtilsqueryWrapper = queryConditions(orderHandleDTO); // List orderEntities = order.selectList(queryWrapper); //第三个参数是导出后文件名称,后面的 class 是根据你查出来的数据类型 ExcelUtils.writeExcel(resp, orderEntities, "物流信息记录", "物流信息", OrderEntity.class); } catch (Exception e) { e.printStackTrace(); } }
package com.boailian.excelhander.util; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.springframework.stereotype.Component; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.HashSet; import java.util.List; import java.util.Set; @Component public class ExcelUtils { public static void writeExcel(HttpServletResponse response, List extends Object> data, String fileName,String sheetName, Class model) throws Exception { // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置表头居中对齐 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 颜色 headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 10); // 字体 headWriteCellStyle.setWriteFont(headWriteFont); headWriteCellStyle.setWrapped(true); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); Set前端excludeColumnFiledNames = ExcelIgnore(); //设置内容靠中对齐 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭 EasyExcel.write(getOutputStream(fileName, response), model).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy) //最大长度自适应 目前没有对应算法优化 建议注释掉不用 会出bug .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .excludeColumnFiledNames(excludeColumnFiledNames) .doWrite(data); } private static Set ExcelIgnore() { // 去掉不需要的字段 Set excludeColumnFiledNames = new HashSet (); excludeColumnFiledNames.add("isnotify"); excludeColumnFiledNames.add("sendState"); excludeColumnFiledNames.add("logisticsState"); excludeColumnFiledNames.add("cellStyleMap"); return excludeColumnFiledNames; } private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception { try { fileName = URLEncoder.encode(fileName, "UTF-8"); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setCharacterEncoding("utf8"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls"); response.setHeader("Pragma", "public"); response.setHeader("Cache-Control", "no-store"); response.addHeader("Cache-Control", "max-age=0"); return response.getOutputStream(); } catch (IOException e) { throw new Exception("导出excel表格失败!", e); } } }
// 直接这一行就能下载 多条件查询就自己拼接数据 window.location.href = 'http://localhost:8088/order/export';效果