老规矩还是先将展示图放在这里,在上期说到List<Map>多表头导出,EasyExcel实现List<Map>多表头excel导出因此这期就这说代码不同的地方。
现在要求每课的成绩靠右对齐,而姓名班级则靠左对齐。合计两个字进行加粗
和上期的格式对比,明显我们增加了背景色,一些格式的设计,所以需要重新加个拦截器 。
因此在ExcelUtil中将原来的拦截器删掉,加上新的拦截器。
在部分在上期内容中,就不贴出来了。重点是拦截器中的内容。
创建ExcedlStyleTool 并写你自己需要的格式,拿当前举例,在头中我们需要一个白色背景的样式和一个灰色背景的样式。而内容方面则需要成绩的格式向右对齐,姓名班级向左对齐,合计字体加粗并且居中显示,序号不加粗但是剧中。因此需要大概六个样式。
/**
* 设置自定义单元格策略
* @return
*/
public static CustomCellStyleStrategy getCellStyleStrategy(int size){
//背景色白色表头样式
WriteCellStyle headWhite = new WriteCellStyle();
setStyle(headWhite, true,"center");
setBorder(headWhite);
WriteFont headWriteFontBlue = getHeadFont(IndexedColors.BLACK.getIndex());
headWhite.setWriteFont(headWriteFontBlue);
//背景色黄色色表头样式
WriteCellStyle headYellow = new WriteCellStyle();
setStyle(headYellow, false,"center");
setBorder(headYellow);
WriteFont headWriteFontRed = getHeadFont(IndexedColors.BLACK.getIndex());
headYellow.setWriteFont(headWriteFontRed);
//内容文字
//左对齐内容样式
WriteCellStyle contentStyleLeft = new WriteCellStyle();
setStyle(contentStyleLeft, true,"left");
setBorder(contentStyleLeft);
contentStyleLeft.setWriteFont(getContentFont(false));
//右对齐内容样式
WriteCellStyle contentStyleCRight = new WriteCellStyle();
setStyle(contentStyleCRight, true,"right");
setBorder(contentStyleCRight);
contentStyleCRight.setWriteFont(getContentFont(false));
//剧中对齐内容样式
WriteCellStyle contentStyleCenter = new WriteCellStyle();
setStyle(contentStyleCenter, true,"center");
setBorder(contentStyleCenter);
contentStyleCenter.setWriteFont(getContentFont(false));
//剧中对齐内容样式
WriteCellStyle contentStyleCenterHj = new WriteCellStyle();
setStyle(contentStyleCenterHj, true,"center");
setBorder(contentStyleCenterHj);
contentStyleCenterHj.setWriteFont(getContentFont(true));
return new CustomCellStyleStrategy(
headWhite,headYellow,
contentStyleLeft,contentStyleCRight,contentStyleCenter,contentStyleCenterHj,size);
}
/**
* 获取表头字体
* @param color
* @return
*/
private static WriteFont getHeadFont(Short color){
//表头字体样式
WriteFont headWriteFont = new WriteFont();
// 头字号
headWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
headWriteFont.setFontName("宋体");
// 字体加粗
headWriteFont.setBold(true);
return headWriteFont;
}
/**
* 获取内容字体
* @return
*/
private static WriteFont getContentFont(boolean flag){
//内容字体
WriteFont contentWriteFont = new WriteFont();
// 内容字号
contentWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
contentWriteFont.setFontName("宋体");
if (flag){
// 字体加粗
contentWriteFont.setBold(true);
}
return contentWriteFont;
}
/**
*
* @param cellStyle
* @param WhiteFlag 背景色标识标识
*/
private static void setStyle(WriteCellStyle cellStyle, boolean WhiteFlag,String horizontalAlignmentFlag){
if (WhiteFlag){
// 头背景 白色
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
}else{
// 头背景 黄色
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}
// 自动换行
cellStyle.setWrapped(true);
if ("left".equals(horizontalAlignmentFlag)){
// 左对齐方式
cellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
}else if ("right".equals(horizontalAlignmentFlag)){
// 右对齐方式
cellStyle.setHorizontalAlignment(HorizontalAlignment.RIGHT);
}else if ("center".equals(horizontalAlignmentFlag)){
// 水平对齐方式
cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
}
// 垂直对齐方式
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
}
/**
* 设置边框
* @param cellStyle
*/
private static void setBorder(WriteCellStyle cellStyle){
// 设置细边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
// 设置边框颜色
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
}
返回这个CustomCellStyleStrategy是我们自定义的拦截器,上面只是对于样式的控制。
通过不同的列编号给他们不同的样式。
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import java.util.ArrayList;
import java.util.List;
/**
* 自定义单元格样式
*/
public class CustomCellStyleStrategy extends AbstractCellStyleStrategy {
private WriteCellStyle headWhite;
private WriteCellStyle headYellow;
private List<WriteCellStyle> contentWriteCellStyleListLeft;
private List<WriteCellStyle> contentWriteCellStyleListRight;
private List<WriteCellStyle> contentWriteCellStyleListCenter;
private List<WriteCellStyle> contentWriteCellStyleListCenterHj;
private CellStyle headCellStyleWhite;
private CellStyle headCellStyleYellow;
private List<CellStyle> contentCellStyleListLeft;
private List<CellStyle> contentCellStyleListRight;
private List<CellStyle> contentCellStyleListCenter;
private List<CellStyle> contentCellStyleListCenterHj;
private int excelSize;
//斑马纹构造方法
public CustomCellStyleStrategy(WriteCellStyle headWhite, WriteCellStyle headYellow,
List<WriteCellStyle> contentWriteCellStyleListLeft,List<WriteCellStyle> contentWriteCellStyleListRight,List<WriteCellStyle> contentWriteCellStyleListCenter,List<WriteCellStyle> contentWriteCellStyleListCenterHj) {
this.headWhite = headWhite;
this.headYellow = headYellow;
this.contentWriteCellStyleListLeft = contentWriteCellStyleListLeft;
this.contentWriteCellStyleListRight = contentWriteCellStyleListRight;
this.contentWriteCellStyleListCenter = contentWriteCellStyleListCenter;
this.contentWriteCellStyleListCenterHj = contentWriteCellStyleListCenterHj;
}
//统一样式的构造方法
public CustomCellStyleStrategy(WriteCellStyle headWhite, WriteCellStyle headYellow,
WriteCellStyle contentWriteCellStyleLeft, WriteCellStyle contentStyleCRight, WriteCellStyle contentWriteCellStyleCenter, WriteCellStyle contentStyleCenterHj, int size) {
this.headWhite = headWhite;
this.headYellow = headYellow;
this.excelSize = size;
contentWriteCellStyleListLeft = new ArrayList<>();
contentWriteCellStyleListLeft.add(contentWriteCellStyleLeft);
contentWriteCellStyleListRight = new ArrayList<>();
contentWriteCellStyleListRight.add(contentStyleCRight);
contentWriteCellStyleListCenter = new ArrayList<>();
contentWriteCellStyleListCenter.add(contentWriteCellStyleCenter);
contentWriteCellStyleListCenterHj = new ArrayList<>();
contentWriteCellStyleListCenterHj.add(contentStyleCenterHj);
}
//实例化后进行 easyexcel -> poi 样式的转换
@Override
protected void initCellStyle(Workbook workbook) {
if (headWhite != null) {
headCellStyleWhite = StyleUtil.buildHeadCellStyle(workbook, headWhite);
}
if (headYellow != null) {
headCellStyleYellow = StyleUtil.buildHeadCellStyle(workbook, headYellow);
}
if (contentWriteCellStyleListLeft != null && !contentWriteCellStyleListLeft.isEmpty()) {
contentCellStyleListLeft = new ArrayList<CellStyle>();
for (WriteCellStyle writeCellStyle : contentWriteCellStyleListLeft) {
contentCellStyleListLeft.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
}
}
if (contentWriteCellStyleListRight != null && !contentWriteCellStyleListRight.isEmpty()) {
contentCellStyleListRight = new ArrayList<CellStyle>();
for (WriteCellStyle writeCellStyle : contentWriteCellStyleListRight) {
contentCellStyleListRight.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
}
}
if (contentWriteCellStyleListCenter != null && !contentWriteCellStyleListCenter.isEmpty()) {
contentCellStyleListCenter = new ArrayList<CellStyle>();
for (WriteCellStyle writeCellStyle : contentWriteCellStyleListCenter) {
contentCellStyleListCenter.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
}
}
if (contentWriteCellStyleListCenterHj != null && !contentWriteCellStyleListCenterHj.isEmpty()) {
contentCellStyleListCenterHj = new ArrayList<CellStyle>();
for (WriteCellStyle writeCellStyle : contentWriteCellStyleListCenterHj) {
contentCellStyleListCenterHj.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
}
}
}
//设置表头样式
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
int rowIndex = cell.getRowIndex();
//此处可以根据不同的需求设置不同列使用哪种策略
if(rowIndex == 1){
if (headCellStyleYellow == null) {
return;
}
cell.setCellStyle(headCellStyleYellow);
} else {
if (headCellStyleWhite == null) {
return;
}
cell.setCellStyle(headCellStyleWhite);
}
}
//设置内容样式
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
int columnIndex= cell.getColumnIndex();
int rowIndex = cell.getRowIndex();
//同样,根据不同的列编号选择使用不同的内容样式
if(columnIndex == 0){
if (contentCellStyleListCenter == null || contentCellStyleListCenter.isEmpty()) {
return;
}
cell.setCellStyle(contentCellStyleListCenter.get(relativeRowIndex % contentCellStyleListCenter.size()));
} else if (columnIndex == 1 || columnIndex == 2){
if (contentCellStyleListLeft == null || contentCellStyleListLeft.isEmpty()) {
return;
}
if (rowIndex == excelSize){
cell.setCellStyle(contentCellStyleListLeft.get(relativeRowIndex % contentCellStyleListLeft.size()));
}else{
cell.setCellStyle(contentCellStyleListCenterHj.get(relativeRowIndex % contentCellStyleListCenterHj.size()));
}
}else{
if (contentCellStyleListRight == null || contentCellStyleListRight.isEmpty()) {
return;
}
cell.setCellStyle(contentCellStyleListRight.get(relativeRowIndex % contentCellStyleListRight.size()));
}
}
}