效果预览
设置表头的行高有0.1的误差，按理说我设置的应该是17.7，但是实际出来excel中是17.8
准备工作
-
项目结构
-
导入easyExcel的依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency>具体代码实现
- controller层
为了方便，我把需要导的包也放进来了
packagecom.easyexcel.test.controller;importcom.easyexcel.test.service.TestService;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.RestController;importjavax.servlet.http.HttpServletResponse;@RestControllerpublicclassTestController{@AutowiredprivateTestService testService;@GetMapping("/download")publicvoidwindowsClientDownload(HttpServletResponse response,Long procedureId){
testService.doDownload(response);}}- service层
ipackage com.easyexcel.test.service;importjavax.servlet.http.HttpServletResponse;publicinterfaceTestService{voiddoDownload(HttpServletResponse response);}- service实现类
packagecom.easyexcel.test.service.impl;importcom.alibaba.excel.EasyExcel;importcom.alibaba.excel.ExcelWriter;importcom.alibaba.excel.util.ListUtils;importcom.alibaba.excel.write.metadata.WriteSheet;importcom.alibaba.excel.write.metadata.style.WriteCellStyle;importcom.easyexcel.test.service.TestService;importcom.easyexcel.test.strategy.CellRowHeightStyleStrategy;importcom.easyexcel.test.strategy.CellStyleStrategy;importorg.springframework.core.io.ClassPathResource;importorg.springframework.core.io.Resource;importorg.springframework.stereotype.Service;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.io.UnsupportedEncodingException;importjava.net.URLEncoder;importjava.util.ArrayList;importjava.util.Arrays;importjava.util.Date;importjava.util.List;importjava.util.concurrent.atomic.AtomicInteger;@ServicepublicclassTestServiceImplimplementsTestService{@OverridepublicvoiddoDownload(HttpServletResponse response){//设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");//中文文件名编码String fileName=null;try{//中文文件名编码要用URLEncoder.encode编码
fileName=URLEncoder.encode("模板","UTF-8").replaceAll("\\+","%20");}catch(UnsupportedEncodingException e){
e.printStackTrace();}
response.setHeader("Content-Disposition","attachment;fileName="+ fileName+".xlsx");try{//主标题和副标题在excel中分别是是第0和第1行List<Integer> columnIndexes=Arrays.asList(0,1);//自定义标题和内容策略(具体定义在下文)CellStyleStrategy cellStyleStrategy=newCellStyleStrategy(columnIndexes,newWriteCellStyle(),newWriteCellStyle());ExcelWriter excelWriter=EasyExcel.write(response.getOutputStream()).registerWriteHandler(newCellRowHeightStyleStrategy())//设置行高的策略.registerWriteHandler(cellStyleStrategy)//设置表头和内容的策略.build();//填入数据writeData(excelWriter);// 千万别忘记关闭流
excelWriter.finish();}catch(IOException e){
e.printStackTrace();}}/**
* 写入数据
* @param excelWriter excelWriter
*/privatevoidwriteData(ExcelWriter excelWriter){WriteSheet writeSheet=newWriteSheet();//设置写到第几个sheet
writeSheet.setSheetNo(0);
writeSheet.setSheetName("测试");//造数据List<List<Object>> list=ListUtils.newArrayList();for(int i=0; i<10; i++){List<Object> data=ListUtils.newArrayList();
data.add("字符串"+ i);
data.add(newDate());
data.add(0.56);
list.add(data);}//设置表头List<List<String>> headList=newArrayList<>();String name="********表";
headList.add(Arrays.asList(name,"序号"));
headList.add(Arrays.asList(name,"名称"));for(int i=1; i<3; i++){
headList.add(Arrays.asList(name,"单位"+i));}
writeSheet.setHead(headList);//（设置数据）//第一列序号从1开始增加AtomicInteger orderNumber=newAtomicInteger(1);ArrayList<List<Object>> dataList=newArrayList<>();for(int i=0; i<10; i++){List<Object> data=ListUtils.newArrayList();
data.add(String.valueOf(orderNumber.getAndIncrement()));
data.add("名称"+ i);
data.add("单元"+i);
data.add(0.56);
dataList.add(data);}
excelWriter.write(dataList, writeSheet);}}- 设置表头和填充内容的样式
packagecom.easyexcel.test.strategy;importcom.alibaba.excel.metadata.data.WriteCellData;importcom.alibaba.excel.write.handler.context.CellWriteHandlerContext;importcom.alibaba.excel.write.metadata.style.WriteCellStyle;importcom.alibaba.excel.write.metadata.style.WriteFont;importcom.alibaba.excel.write.style.HorizontalCellStyleStrategy;importorg.apache.poi.ss.usermodel.BorderStyle;importorg.apache.poi.ss.usermodel.IndexedColors;importjava.util.List;/**
- 设置表头和填充内容的样式
*/publicclassCellStyleStrategyextendsHorizontalCellStyleStrategy{privatefinalWriteCellStyle headWriteCellStyle;privatefinalWriteCellStyle contentWriteCellStyle;/**
* 操作列
*/privatefinalList<Integer> columnIndexes;publicCellStyleStrategy(List<Integer> columnIndexes,WriteCellStyle headWriteCellStyle,WriteCellStyle contentWriteCellStyle){this.columnIndexes= columnIndexes;this.headWriteCellStyle= headWriteCellStyle;this.contentWriteCellStyle= contentWriteCellStyle;}//设置头样式@OverrideprotectedvoidsetHeadCellStyle(CellWriteHandlerContext context){// 获取字体实例WriteFont headWriteFont=newWriteFont();
headWriteFont.setFontName("宋体");if(columnIndexes.get(0).equals(context.getRowIndex())){
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteFont.setFontHeightInPoints((short)14);
headWriteFont.setBold(true);}else{
headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
headWriteFont.setFontHeightInPoints((short)11);
headWriteFont.setBold(false);}
headWriteCellStyle.setWriteFont(headWriteFont);if(stopProcessing(context)){return;}WriteCellData<?> cellData= context.getFirstCellData();WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());}//设置填充数据样式@OverrideprotectedvoidsetContentCellStyle(CellWriteHandlerContext context){WriteFont contentWriteFont=newWriteFont();
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short)11);//设置数据填充后的实线边框
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);WriteCellData<?> cellData= context.getFirstCellData();WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());}}- 设置表头行高策略
如果excel需要显示行高为15，那这里就要设置为15*20=300
packagecom.easyexcel.test.strategy;importcom.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;importorg.apache.poi.ss.usermodel.Row;/**
* 设置表头的自动调整行高策略
*/publicclassCellRowHeightStyleStrategyextendsAbstractRowHeightStyleStrategy{@OverrideprotectedvoidsetHeadColumnHeight(Row row,int relativeRowIndex){//设置主标题行高为17.7if(relativeRowIndex==0){//如果excel需要显示行高为15，那这里就要设置为15*20=300
row.setHeight((short)(354));}}@OverrideprotectedvoidsetContentColumnHeight(Row row,int relativeRowIndex){}}访问
启动项目后访问 http://localhost:8411/download 开始下载文件 （8411是我自己在application.yml中设置的项目启动端口）
写在最后
如果有什么不对的可以评论交流
