该样例实现了:
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);
//设置字体为宋体,字体加粗,字体大小为12
Font 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);
//设置字体为宋体,字体不加粗,字体大小为12
Font 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;
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,
Integer relativeRowIndex, Boolean isHead) {
}
/**
* 行创建后执行此方法
*/
@Override
public 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);
}
}
@Override
public 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{
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//得到Sheet
Sheet 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 java.net.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 com.frame.common.easyExcel.CustomRowWriteHandler;
import com.frame.common.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 {
@Autowired
private 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依赖,进行原生编写。