【使用easyexcel导出excel

2024-02-19 06:51| 来源: 网络整理| 查看: 265

添加maven依赖

com.alibaba easyexcel 2.2.0-beta2 com.alibaba fastjson 1.2.58

实体类

package com.ph.rfwg.entity; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import io.swagger.annotations.ApiModel; import lombok.Data; import java.io.Serializable; @Data @TableName("xj_proj") //mapper表名 @ContentRowHeight(15) //内容行高 @HeadRowHeight(23)//表头行高 public class Proj implements Serializable { private static final long serialVersionUID = 312449932801745800L; @ExcelIgnore @TableId(type = IdType.ASSIGN_UUID) private String uuid; @ExcelProperty(value = {"工程台账","工程名称"}, index = 1) @ColumnWidth(35) private String gcmc; @ExcelProperty(value = {"工程台账","区域"}, index = 2) private String district; @ExcelIgnore private String address; @ExcelIgnore private String symc; @ExcelIgnore private String jgsj; @ExcelIgnore private String jzmj; @ExcelIgnore private String symj; @ExcelIgnore private String ypj; @ExcelIgnore private String qsqk; @ExcelIgnore private String whfl; @ExcelProperty(value = {"工程台账","最近巡查时间"}, index = 5) @ColumnWidth(21) private String zjxcsj; @ExcelProperty(value = {"工程台账","车位应配建"}, index = 3) @ColumnWidth(13) private Integer cwypj; @ExcelProperty(value = {"工程台账","车位其它"}, index = 4) @ColumnWidth(13) private Integer cwqt; @ExcelIgnore private String sfsy; @ExcelIgnore private String syr; @ExcelIgnore private String ffsyz; @ExcelIgnore private String syzqx; @ExcelIgnore private String syfsq; @ExcelIgnore private String bz; @ExcelProperty(value = {"工程台账","序号"}, index = 0) @ColumnWidth(8) private Integer number; }

@ExcelProperty(value = {“工程台账”,“序号”}, index = 0) value第一个是标题，第二个是列的字段名称，index是显示的顺序 @ExcelIgnore 不导出该字段 @ColumnWidth(8) 该字段列的宽度

service层

package com.ph.rfwg.service; import javax.servlet.http.HttpServletResponse; import java.io.IOException; public interface ExcelDownloadService { /** * 导出项目excel */ void proExcelDown(HttpServletResponse response) throws IOException; }

要返回void不然会报错

实现层

package com.ph.rfwg.service.impl; import com.alibaba.excel.EasyExcel; 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.fastjson.JSON; import com.ph.rfwg.entity.Proj; import com.ph.rfwg.mapper.CheckMapper; import com.ph.rfwg.mapper.ProjMapper; import com.ph.rfwg.service.ExcelDownloadService; import com.ph.rfwg.vo.CheckExcelVo; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.HashMap; import java.util.List; import java.util.Map; @Service public class ExcelDownloadServiceImpl implements ExcelDownloadService { @Autowired private CheckMapper checkMapper; @Autowired private ProjMapper projMapper; // 导出项目excel @Override public void proExcelDown(HttpServletResponse response) throws IOException { List projs = projMapper.selectList(null); for (int i = 0; i < projs.size(); i++) { projs.get(i).setNumber(i + 1); } try { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置背景颜色 headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); //设置头字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)13); headWriteFont.setBold(true); headWriteCellStyle.setWriteFont(headWriteFont); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //内容策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); //设置水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系 String fileName = URLEncoder.encode("工程台账", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), Proj.class) .registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1") .doWrite(projs); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map map = new HashMap(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } } }

controller层

package com.ph.rfwg.cmcontroller; import com.ph.rfwg.service.ExcelDownloadService; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.io.IOException; @RestController @RequestMapping("/excel") public class ExcelDownloadController { @Autowired private ExcelDownloadService excel; @RequestMapping(value = "/excelDownByProj",method = {RequestMethod.GET,RequestMethod.POST}) @ApiOperation(value = "项目台账excel导出") public void excelDownByProj(HttpServletResponse response) throws IOException { excel.proExcelDown(response); } }

要返回void不然会报错

最终效果

在这里插入图片描述

【本文地址】

公司简介

联系我们