EasyExcel设置首行自增需要(带样式)_xiaoxiang090的博客-CSDN博客_easyexcel 序号
EasyExcel设置首行自增需要(带样式)
EasyExcel优点不过多累述，本文主要分享实践代码，在使用EasyExcel之前建议先将项目从github克隆下来，然后根据其中的测试类跟踪一下源码，大致了解其思路，这样能是大家更好的运用EasyExcel。
最终效果图
代码
- 单元格样式的工具类CellStyleUtil
package com.jesse.commons;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
/**
* excel 样式工具类
* @author jesse
* @date 2020/03/08 19:08
*/
public class CellStyleUtil {
/**
* excel首列序号列样式
* @param workbook
* @return
*/
public static CellStyle firstCellStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
//设置边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
//文字
Font font = workbook.createFont();
font.setBold(Boolean.TRUE);
cellStyle.setFont(font);
return cellStyle;
}
/**
* 用于设置excel导出时的样式
* easyexcel 导出样式
* @return
*/
public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//边框
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//文字
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
注意： 普通cell设置填充颜色时一定要设置填充类型，即cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);不然颜色是填充不上的，我就在这遇到坑的。
- 非常重要的一点，定制拦截器，即实现WriteHandler 类，由于需求是在每行前加序号，所以需要实现其子类RowWriteHandler，这个也是在EasyExcel官方给的测试类中没有给出。另外还有SheetWriteHandler、WorkbookWriteHandler等
package com.alibaba.excel.write.handler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Row;
public interface RowWriteHandler extends WriteHandler {
void beforeRowCreate(WriteSheetHolder var1, WriteTableHolder var2, Integer var3, Integer var4, Boolean var5);
void afterRowCreate(WriteSheetHolder var1, WriteTableHolder var2, Row var3, Integer var4, Boolean var5);
void afterRowDispose(WriteSheetHolder var1, WriteTableHolder var2, Row var3, Integer var4, Boolean var5);
}
以上可看出该类有三个方法，即在创建行之前执行、之后执行、处理后执行，所以我们只需要重写创建行之后执行的方法即可。
package com.jesse.configuration;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.xdf.bling.cloud.tutor.commons.CellStyleUtil;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 自定义行拦截器
* @author jesse
* @date 2020/03/08 12:57
*/
public class CustomRowWriteHandler implements RowWriteHandler {
private static final Logger LOGGER = LoggerFactory.getLogger(CustomRowWriteHandler.class);
/**
* 一定将样式设置成全局变量
* 首行只需要创建一次样式就可以 不然每行都创建一次 数据量大的话会保错
* 异常信息：The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
*/
private CellStyle firstCellStyle;
/**
* 列号
*/
private int count = 0;
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("第{}行创建完毕！", row.getRowNum());
}
Cell cell = row.createCell(0);
if (firstCellStyle == null) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
firstCellStyle = ExportUtil.firstCellStyle(workbook);
LOGGER.info("设置首列样式成功");
}
cell.setCellStyle(firstCellStyle);
//设置列宽 0列 10个字符宽度
writeSheetHolder.getSheet().setColumnWidth(0, 10 * 256);
if (row.getRowNum() == 0) {
cell.setCellValue("序号");
return;
}
cell.setCellValue(++count);
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
}
}
- 实体类
package com.jesse.pojo;
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.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import java.util.Date;
/**
* 基础数据类
*
* @author Jiaju Zhuang
**/
@ContentRowHeight(16)
@HeadRowHeight(16)
@ColumnWidth(16)
public class DemoData {
@ExcelProperty(value = "字符串标题", index = 1)
private String string;
@ExcelProperty(value = "日期标题", index = 2)
private Date date;
@ExcelProperty(value = "数字标题", index = 3)
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
public String getString() {
return string;
}
public void setString(String string) {
this.string = string;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Double getDoubleData() {
return doubleData;
}
public void setDoubleData(Double doubleData) {
this.doubleData = doubleData;
}
public String getIgnore() {
return ignore;
}
public void setIgnore(String ignore) {
this.ignore = ignore;
}
@Override
public String toString() {
return ToStringBuilder.reflectionToString(this, ToStringStyle.JSON_STYLE);
}
}
- 测试类
package com.jesse.service;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.xdf.bling.cloud.service.tutor.pojo.DemoData;
import com.xdf.bling.cloud.service.tutor.pojo.WidthAndHeightData;
import com.xdf.bling.cloud.tutor.commons.CellStyleUtil;
import com.xdf.bling.cloud.tutor.configuration.CustomRowWriteHandler;
import org.apache.poi.ss.usermodel.*;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author wpx
* @date 2020/03/07 23:29
*/
public class EasyExcelTest {
@Test
public void customHandlerWrite() {
String fileName = "C:\\Users\\Administrator\\Desktop\\customHandlerWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, DemoData.class)
.registerWriteHandler(CellStyleUtil.getHorizontalCellStyleStrategy())
.registerWriteHandler(new CustomRowWriteHandler())
.sheet("模板").doWrite(data());
}
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
}
注意： 一定要将拦截器注册registerWriteHandler(new CustomRowWriteHandler()) 。
真正用好EasyExcel还需要用心去研究其源码，小小分享希望对大家能有所帮助。
原网址: 访问
创建于: 2021-05-25 17:23:38
目录: default
标签: 无
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论