工作中由于有用到excel导出的功能，在设置单元格样式的时候呢也是网上各种找资料，既然问题已经解决了呢! 自然要做一个记录，便于日后翻阅。也供各位同学做参考啦！
首先我们添加easyExcel依赖，我们这里选用的版本是2.1.6
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
废话不多说，直接上代码吧
package com.xchaset.excelexample.excel.service;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.xchaset.excelexample.excel.model.SimpleCustomerModel;
import java.util.List;
public class ExcelWriteService {
public static void main(String[] args) {
new ExcelWriteService().writeExcel();
}
public void writeExcel(){
// 读取的excel路径，此处主要是为获取数据源。
String readPathName = "C:\\Users\\customer.XLS";
List rows = new ExcelReadService().readExcel(readPathName);
// 写入的文件路径
String writePathName = "C:\\Users\\write.xlsx";
ExcelWriterBuilder writerBuilder = EasyExcel.write(writePathName, SimpleCustomerModel.class);
writerBuilder.excelType(ExcelTypeEnum.XLSX);
// 注册写入处理器
writerBuilder.registerWriteHandler(new CellCommentWriteHandler());
writerBuilder.registerWriteHandler(new CellStyleWriteHandler());
ExcelWriter writer = writerBuilder.build();
WriteSheet writeSheet = new WriteSheet();
writeSheet.setSheetName("客户信息");
writer.write(rows, writeSheet);
//可以写入多个sheet页
WriteSheet writeSheet2 = new WriteSheet();
writeSheet2.setSheetName("客户信息2");
writer.write(rows,writeSheet2);
// finish才会写入到文件中
writer.finish();
}
/**
* 多个sheet页写入
* @param filePath 写出文件路径
* @param dataMap sheetName:数据集 的Map 集合
* @param writeHandlers 写入处理器
*/
public static void writeToExcel(String filePath, Map<String, List> dataMap, List<WriteHandler> writeHandlers){
if (!FileUtil.exist(filePath)) {
FileUtil.mkParentDirs(filePath);
}
ExcelWriterBuilder writerBuilder = EasyExcel.write(filePath);
writerBuilder.excelType(ExcelTypeEnum.XLSX);
for (WriteHandler writeHandler : writeHandlers) {
writerBuilder.registerWriteHandler(writeHandler);
}
ExcelWriter writer = writerBuilder.build();
for(Map.Entry<String,List> entry :dataMap.entrySet()){
String sheetName = entry.getKey();
List rows = entry.getValue();
WriteSheet writeSheet = new WriteSheet();
writeSheet.setNeedHead(true);
writeSheet.setClazz(rows.get(0).getClass());
writeSheet.setSheetName(sheetName);
writer.write(rows,writeSheet);
}
writer.finish();
}
上面代码有提到我们的注册写入处理器
package com.xchaset.excelexample.excel.service;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
/**
* 单元格样式写入处理器
*/
public class CellStyleWriteHandler extends AbstractCellStyleStrategy {
@Override
protected void initCellStyle(Workbook workbook) {
}
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.CORAL.getIndex());
cell.setCellStyle(cellStyle);
}
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
if ("*".equals(cell.getStringCellValue())) {
//隐藏行
cell.getRow().setZeroHeight(true);
}
cell.setCellStyle(cellStyle);
}
}
注意上面的代码CellStyleWriteHandler类是继承自AbstractCellStyleStrategy 类，而不是直接实现CellWriteHandler。通过实现AbstractCellStyleStrategy 的setHeadCellStyle可以对头部的样式进行设置，实现setContentCellStyle方法可以对内容进行样式设置。我这里只是简单对单元格的颜色做了设置。
另外还有一点需要注意的就是由于AbstractCellStyleStrategy 实现了NotRepeatExecutor接口，这个接口只有一个方法uniqueValue()，就是返回一个唯一的值，AbstractCellStyleStrategy 本身已经实现了这个方法。
所以如果同时有多个类实现了AbstractCellStyleStrategy ，而没有重写uniqueValue()方法，那只会有其中一个会生效。
我们可以设置多个单元格的处理器。针对需要设置单元格批注的，我们添加了一个CellCommentWriteHandler。这次我们直接实现CellWriteHandler接口。
package com.xchaset.excelexample.excel.service;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.xchaset.excelexample.excel.constant.CellCommentEnum;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.util.List;
/**
* 批注写入处理器
*/
public class CellCommentWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
addCellComment(cell);
}
/**
* 添加批注信息
* @param cell
*/
private void addCellComment(Cell cell) {
for (CellCommentEnum commentEnum : CellCommentEnum.values()) {
int rowIndex = commentEnum.getRowIndex();
int columnIndex = commentEnum.getColumnIndex();
if (rowIndex == cell.getRowIndex() && columnIndex == cell.getColumnIndex()){
Drawing<?> drawing = cell.getSheet().createDrawingPatriarch();
Comment cellComment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, columnIndex, 1, columnIndex + 2, 6));
cellComment.setString(new XSSFRichTextString(commentEnum.getCellCommentStr()));
cell.setCellComment(cellComment);
}
}
}
}
针对需要添加批注的单元格。新建了一个枚举类,这样我们可以把头部的一些需要添加批注的信息维护在枚举类中。
package com.xchaset.excelexample.excel.constant;
import lombok.AllArgsConstructor;
import lombok.Getter;
@Getter
@AllArgsConstructor
public enum CellCommentEnum {
CUSTOMER_CODE_COMMENT(1,0,"客户代码"),
CUSTOMER_NAME_COMMENT(1,1,"客户名称"),
ADDRESS_COMMENT(1,5,"工作单位地址"),
LIVING_ADDRESS_COMMENT(1,16,"住宅地址");
private int rowIndex;
private int columnIndex;
private String cellCommentStr;
}
下面是model类，我们写入excel的头部信息可以通过注解进行设置。在之前的easyExcel中是需要继承com.alibaba.excel.metadata.BaseRowModel的，不过这个类已经废弃了。所以新版本的话就不在需要了。
package com.xchaset.excelexample.excel.model;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class SimpleCustomerModel {
@ExcelProperty(value = {"客户信息","客户代码"})
private String customerCode;
@ExcelProperty(value = {"客户信息","客户名称"})
private String name;
@ExcelProperty(value = {"客户信息","性别"})
private String gender;
@ExcelProperty(value = {"出生信息","年龄"})
private String age;
@ExcelProperty(value = {"出生信息","出生日期"})
private String birthday;
@ExcelProperty(value = {"地址信息","客户地址"})
private String address;
@ExcelProperty(value = {"联系方式","电话"})
private String tel;
@ExcelProperty(value = {"联系方式","移动电话"})
private String mobile;
@ExcelProperty(value = {"联系方式","邮编"})
private String postcode;
@ExcelProperty(value = {"联系方式","邮箱"})
private String email;
@ExcelProperty(value = {"工作信息","职业"})
private String occupation;
@ExcelProperty(value = {"工作信息","职务"})
private String job;
@ExcelProperty(value = {"地址信息","省区代码"})
private String provinceCode;
@ExcelProperty(value = {"地址信息","省区名称"})
private String provinceName;
@ExcelProperty(value = {"地址信息","市县代码"})
private String cityCode;
@ExcelProperty(value = {"地址信息","市县名称"})
private String cityName;
@ExcelProperty(value = {"地址信息","住宅区"})
private String livingAddress;
}
就是这样了。具体的例子可以参考github上的源码。https://github.com/xchaset/example
