导入依赖
<!-- 阿里开源EXCEL 数据表导出-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>
</dependency>
<!--swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.8.7</version>
</dependency>
controller
@ApiOperation(value = "导出Excel分页根据条件筛选")
@GetMapping("/outExcel")
public void outExcel(
@ApiParam(name = "distIdList",value = "地区")@RequestParam(value = "distIdList",required = false) List<String> distIdList,
@ApiParam(name = "proj",value = "工程中的筛选条件")ZgProj proj,
@ApiParam(name = "district",value = "区域id")@RequestParam(value = "district",required = false)String district,
HttpServletResponse response)throws IOException {
projService.excelNoPage(response,distIdList, proj, district);
}
实体类
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.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;
@Data
@TableName("xj_zg_proj") //MP表名
@ApiModel("直管工程") //swagger注释
@HeadRowHeight(23)//表头行高
public class ZgProj{
@ExcelIgnore
@ApiModelProperty("工程id")
private String uuid;
@ExcelProperty(value = {"直管工程台账","工程名称"}, index = 2)
@ColumnWidth(35)
@ApiModelProperty("工程名称")
private String gcmc;
@ExcelIgnore
@ApiModelProperty("地区id")
private String distId;
@ExcelProperty(value = {"直管工程台账","地址"}, index = 3)
@ApiModelProperty("地址")
@ColumnWidth(35)
private String address;
@ExcelIgnore
@ApiModelProperty("用户id")
private String userId;
@TableField(exist = false)
@ExcelIgnore
@ApiModelProperty("用户名字")
private String realName;
@TableField(exist = false)
@ExcelProperty(value = {"直管工程台账","所属市/区"}, index = 1)
@ColumnWidth(15)
private String distname;
@ExcelProperty(value = {"直管工程台账","创建时间"}, index = 4)
@ColumnWidth(22)
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime; // 单位id
@TableField(exist = false)
@ExcelProperty(value = {"直管工程台账","序号"}, index = 0)
private Integer number; // 单位id
}
service
// web导出
void excelNoPage(HttpServletResponse response,List<String> distIdList,ZgProj proj,String district)throws IOException;
实现类impl
// web导出
@Override
public void excelNoPage(HttpServletResponse response,List<String> distIdList,ZgProj proj,String district)throws IOException{
List<ZgProj> byConNopage = projMapper.findByConNopage(distIdList, proj, district);
for (int i = 0; i < byConNopage.size(); i++) {
byConNopage.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(), ZgProj.class)
.registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1")
.doWrite(byConNopage);
} catch (Exception e) {
e.printStackTrace();
}
}