该样例实现了：
1、EasyExcel和原生poi实现了添加序号列
2、单Sheet和多Sheet页导出
3、设置好的打印样式，导出即符合打印版式。
1、引入EasyExcel Maven依赖
<!-- excel导入导出 --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.10</version></dependency>
2、给实体类添加导出注解，以及配置导出样式注解
import java.io.Serializable;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;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.ContentFontStyle;import com.alibaba.excel.annotation.write.style.ContentStyle;import com.alibaba.excel.annotation.write.style.HeadFontStyle;import com.alibaba.excel.annotation.write.style.HeadRowHeight;import com.alibaba.excel.annotation.write.style.HeadStyle;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 lombok.Data;@Data//设置Excel head高度@HeadRowHeight(30)//设置Excel head样式@HeadStyle(wrapped = true,fillPatternType = FillPatternType.NO_FILL,borderTop = BorderStyle.THIN,borderBottom = BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN,topBorderColor = 8,bottomBorderColor = 8,leftBorderColor = 8,rightBorderColor = 8,verticalAlignment = VerticalAlignment.CENTER,horizontalAlignment = HorizontalAlignment.CENTER)//设置Excel head字体@HeadFontStyle(fontName = "宋体",fontHeightInPoints = 12,bold = true)//设置Excel默认列宽@ColumnWidth(14)//设置Excel 正文内容样式@ContentStyle(wrapped = true,borderTop = BorderStyle.THIN,borderBottom = BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN,topBorderColor = 8,bottomBorderColor = 8,leftBorderColor = 8,rightBorderColor = 8,verticalAlignment = VerticalAlignment.CENTER,horizontalAlignment = HorizontalAlignment.CENTER)//设置Excel 正文字体样式@ContentFontStyle(fontName = "宋体",fontHeightInPoints = 12)@TableName(value = "t_test")public class Test implements Serializable {private static final long serialVersionUID = 1L;@ExcelIgnore@TableId(value = "id",type = IdType.AUTO)private Integer id;//这里再次设置列宽会覆盖上面设置的默认列宽@ColumnWidth(20)@ExcelProperty(value = {"测试1标题"},index=1)@TableField(value = "column1")private String column1;@ExcelProperty(value = {"测试2标题"},index=2)@TableField(value = "column2")private String column2;@ExcelProperty(value = {"测试3标题"},index=3)@TableField(value = "column3")private String column3;@ExcelProperty(value = {"测试4标题"},index=4)@TableField(value = "column4")private String column4;@ExcelProperty(value = {"测试5标题"},index = 5)@TableField(value = "column5")private String column5;@ExcelProperty(value = {"测试6标题"},index = 6)@TableField(value = "column6")private String column6;@ExcelProperty(value = {"测试7标题"},index = 7)@TableField(value = "column7")private String column7;@ExcelProperty(value = {"测试8标题"},index = 8)@TableField(value = "column8")private String column8;}
3.设置序号列样式
import org.apache.poi.ss.usermodel.BorderStyle;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.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;/*** 列样式工具* 1.该工具实现设置了表格头的样式和表格内容的样式* 2.该工具类设置的样式建议和Test实体类中的样式相同* 3.该工具类设置的样式就是为了给自定义序号列使用**/public class CellStyleUtil {public static CellStyle getHeaderStyle(Workbook book) {CellStyle cellStyle = book.createCellStyle();//设置水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置内容超出自动换行cellStyle.setWrapText(true);//设置边框为细边框，并且颜色为黑色cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setTopBorderColor((short)8);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBottomBorderColor((short)8);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setLeftBorderColor((short)8);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setRightBorderColor((short)8);//设置字体为宋体，字体加粗，字体大小为12Font font = book.createFont();font.setBold(true);font.setFontName("宋体");font.setFontHeightInPoints((short)12);cellStyle.setFont(font);return cellStyle;}public static CellStyle getContentStyle(Workbook book) {CellStyle cellStyle = book.createCellStyle();//设置水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置内容超出自动换行cellStyle.setWrapText(true);//设置边框为细边框，并且颜色为黑色cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setTopBorderColor((short)8);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBottomBorderColor((short)8);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setLeftBorderColor((short)8);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setRightBorderColor((short)8);//设置字体为宋体，字体不加粗，字体大小为12Font font = book.createFont();font.setBold(false);font.setFontName("宋体");font.setFontHeightInPoints((short)12);cellStyle.setFont(font);return cellStyle;}}
4.自定义添加导出“序号”列，需自定义一个类并实现EasyExcel的RowWriteHandler接口
import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Workbook;import com.alibaba.excel.write.handler.RowWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;/*** 自定义EasyExcel Row**/public class CustomRowWriteHandler implements RowWriteHandler{private CellStyle headerStyle;private CellStyle contentStyle;@Overridepublic void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,Integer relativeRowIndex, Boolean isHead) {}/*** 行创建后执行此方法*/@Overridepublic void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Integer relativeRowIndex, Boolean isHead) {//创建一个单元格Cell cell = row.createCell(0);//给表格头样式赋值if (headerStyle == null) {Workbook workbook = writeSheetHolder.getSheet().getWorkbook();headerStyle = CellStyleUtil.getHeaderStyle(workbook);}//给表格内容样式赋值if (contentStyle == null) {Workbook workbook = writeSheetHolder.getSheet().getWorkbook();contentStyle = CellStyleUtil.getContentStyle(workbook);}//设置列宽0列8个字符宽度(poi的列宽是通过字符个数来确定的，一个列宽为一个字符的1/256)writeSheetHolder.getSheet().setColumnWidth(0, 8 * 256);if (row.getRowNum() == 0) {cell.setCellValue("序号");cell.setCellStyle(headerStyle);}else {cell.setCellValue(relativeRowIndex+1);cell.setCellStyle(contentStyle);}}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Integer relativeRowIndex, Boolean isHead) {}}
5、给导出Excel添加打印样式
import org.apache.poi.ss.usermodel.PrintSetup;import org.apache.poi.ss.usermodel.Sheet;import com.alibaba.excel.write.handler.SheetWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;/*** 自定义EasyExcel Sheet**/public class CustomSheetWriteHandler implements SheetWriteHandler{@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//得到SheetSheet sheet = writeSheetHolder.getSheet();//设置将所有列调整为一页sheet.setFitToPage(true);//设置打印内容水平居中显示sheet.setHorizontallyCenter(true);//设置打印页面边距sheet.setMargin(Sheet.TopMargin, 0.3);sheet.setMargin(Sheet.BottomMargin, 0.5);sheet.setMargin(Sheet.LeftMargin, 0.3);sheet.setMargin(Sheet.RightMargin, 0.3);//打印设置对象PrintSetup print = sheet.getPrintSetup();//并缩减打印输出只有一页宽print.setFitHeight((short)0);//设置竖屏打印（false），横屏打印（true）print.setLandscape(false);//设置A4纸打印print.setPaperSize(PrintSetup.A4_PAPERSIZE);}}
6、导出Controller代码
import java.io.IOException;import .URLEncoder;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.support.ExcelTypeEnum;import com.alibaba.excel.write.metadata.WriteSheet;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import mon.easyExcel.CustomRowWriteHandler;import mon.easyExcel.CustomSheetWriteHandler;import com.frame.system.entity.Test;import com.frame.system.service.ITestService;import io.swagger.annotations.Api;@Api(tags = {"测试操作接口：TestController"})@RestController@RequestMapping("/test")public class TestController {@Autowiredprivate ITestService testService;/*** 单Sheet页导出* @param test* @param req* @param res*/@PostMapping("excel")public void export(Test test,HttpServletRequest req,HttpServletResponse res) {try {String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");List<Test> list = this.testService.list();res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");res.setCharacterEncoding("utf-8");res.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//这里需要注册自己定义的处理器CustomSheetWriteHandler，CustomRowWriteHandler，否则不起作用EasyExcel.write(res.getOutputStream(), Test.class).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(new CustomSheetWriteHandler()).registerWriteHandler(new CustomRowWriteHandler()).sheet(fileName).doWrite(list);} catch (IOException e) {e.printStackTrace();}}/*** 多Sheet页导出* @param test* @param req* @param res*/@PostMapping("excelSheets")public void exportSheets(Test test,HttpServletRequest req,HttpServletResponse res) {ExcelWriter excelWriter = null;try {String fileName = URLEncoder.encode("测试1", "UTF-8");res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");res.setCharacterEncoding("utf-8");res.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//这里需要注册自己定义的处理器CustomSheetWriteHandler，CustomRowWriteHandler，否则不起作用excelWriter = EasyExcel.write(res.getOutputStream(), Test.class).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(new CustomSheetWriteHandler()).registerWriteHandler(new CustomRowWriteHandler()).build();for(int i=1;i<5;i++) {IPage<Test> page = new Page<>(i,2000);List<Test> list = this.testService.page(page).getRecords();WriteSheet writeSheet = EasyExcel.writerSheet(i, "测试"+i+"sheet").build();excelWriter.write(list, writeSheet);}} catch (IOException e) {e.printStackTrace();}finally {if(null != excelWriter) {excelWriter.finish();}}}}
提示：
EasyExcel 中提供了几个接口，分别是：
1、SheetWriteHandler 可实现自己的Sheet页逻辑
2、RowWriteHandler 可实现自己的Row逻辑
3、CellWriteHandler 可实现自己的Cell逻辑
其它方面可多看看EasyExcel：源码，EasyExcel本质上也是引用的POI 工具，如果还不满足需求，可直接导入POI Maven依赖，进行原生编写。
