1、在maven加引入插件依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
2、创建对应的映射实体
import java.time.LocalDate;
import org.springframework.format.annotation.DateTimeFormat;
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.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@NoArgsConstructor
@Accessors(chain = true)
@ContentRowHeight(40) // 设置内容高度
@HeadRowHeight(50)// 设置表头高度
@Data
public class ExportExaminationVO {
@ExcelProperty(value = "序号",index = 0) // 设置表头字段名
@ColumnWidth(8)// 设置行宽
private Integer idx;
@ExcelProperty(value = "姓名",index = 1)
@ColumnWidth(20)
private String name;
@ExcelProperty(value="性别",index = 2)
@ColumnWidth(8)
private String genderName;
@ExcelProperty(value="J号",index = 3)
@ColumnWidth(15)
private String policeNo;
@ExcelProperty(value="出生年月",index = 4)
@JsonFormat(pattern = "yyyy-MM", timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM")
@ColumnWidth(20)
private LocalDate birthday;
@ExcelProperty(value = "单位名称",index = 5)
@ColumnWidth(50)
private String bzDwCodeName;
@ExcelProperty(value = "人员类别",index = 6)
@ColumnWidth(30)
private String assessTypeName;
}
3、在controller层写请求接口
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.szpsbnpt.hrs.annualappraisal.facade.AssessExaminationApprovalFacade;
import com.szpsbnpt.hrs.annualappraisal.util.MonthSheetWriteHandler;
import com.szpsbnpt.hrs.annualappraisal.web.vo.ExportExaminationVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import jade.core.dto.TableTagBean;
import jade.core.util.BeanHelper;
@Api(tags = "审定")
@RestController
@RequestMapping("/test/assessExaminationApproval")
public class TestController {
@Autowired
private AssessExaminationApprovalFacade assessExaminationApprovalFacade;
@ApiOperation(value = "列表导出")
@ApiImplicitParams({
@ApiImplicitParam(paramType = "query", name = "query", value = "姓名/J号", required = false, dataType = "String")
})
@GetMapping(value = "/testList")
public void exportExaminationTest(HttpServletRequest request, HttpServletResponse response) throws Exception {
TableTagBean ttb = TableTagBean.getFromExt(request);
StringBuffer bigTitle = new StringBuffer("审定名单");
List<ExportExaminationVO> list = BeanHelper.copyProperties(assessExaminationApprovalFacade.findExaminationApproval(ttb), ExportExaminationVO.class);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(bigTitle.toString(), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 默认设置为水平居中
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
EasyExcel.write(response.getOutputStream(), ExportExaminationVO.class)
//设置输出excel版本,不设置默认为xlsx
// .excelType(ExcelTypeEnum.XLS).head(ExportExaminationVO.class)
//设置拦截器或自定义样式
.registerWriteHandler(new MonthSheetWriteHandler())
.registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle))
.sheet(bigTitle.toString())
//设置默认样式及写入头信息开始的行数
.useDefaultStyle(true).relativeHeadRowIndex(3)
.doWrite(list);
}
}
4、在拦截器中对表头的样式进行自定义的覆盖
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import jade.core.util.DateUtil;
/**
* 表头设置拦截器
*
*/
public class MonthSheetWriteHandler implements SheetWriteHandler {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
//设置标题,设置首行表头样式
Row row1 = sheet.createRow(0);
// 设置行高
row1.setHeight((short) 900);
Cell cell1 = row1.createCell(0);
cell1.setCellValue("审定名单");
// 设置水平、垂直居中
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置字体样式,bold加粗、fontHeight设置字体大小
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 500);
cellStyle.setFont(font);
cell1.setCellStyle(cellStyle);
// CellRangeAddress(1, 1, 0, 17) 用于合并表格,其中四个参数含义起始行号、终止行号、起始列号、终止列号
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 10));
//设置填表日期
Row row2 = sheet.createRow(1);
row2.setHeight((short) 500);
// 在当前行的第10+1列处展示(时间:2021-05-31)
row2.createCell(10).setCellValue("时间:" + DateUtil.format(new Date(), DateUtil.DATE_PATTERN_DEFAULT));
}
}
最终生成的表格如下