demo
/**
* 多个 sheet 的写
*/
@Test
public void multiSheetWrite() {
String fileName = "E:/test" + File.separator + "multiSheetWrite" + System.currentTimeMillis() + ".xlsx";
try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
for (int i = 0; i < 3; i++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i, "sheet-" + i).head(DemoData.class).build();
excelWriter.write(data, writeSheet);
}
}
}
/**
* 指定要忽略的字段(对应的也可以指定只包含的字段: includeColumnFiledNames)
*/
@Test
public void excludeOrIncludeWrite() {
String fileName = "E:/test" + File.separator + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
// 指定要忽略的字段
Set<String> excludeColumnFiledNames = new HashSet<>();
excludeColumnFiledNames.add("name");
EasyExcel.write(fileName).head(DemoData.class).excludeColumnFieldNames(excludeColumnFiledNames).sheet("sheet-1").doWrite(data);
}
/**
* 复杂的写
*/
@Test
public void complexWrite() {
// 指定范围合并
//OnceAbsoluteMergeStrategy secondHeadMaleMergeStrategy = new OnceAbsoluteMergeStrategy(1, 1, 1, 2);
// 设置表头样式
WriteCellStyle headCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setBold(true);
headCellStyle.setWriteFont(headWriteFont);
headCellStyle.setFillForegroundColor(GREY_25_PERCENT.index);
headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置内容样式
WriteCellStyle contentCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setColor(RED.getIndex());
contentCellStyle.setWriteFont(contentWriteFont);
contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle);
String fileName = "E:/test" + File.separator + "complexWrite" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName).head(getHeads()).sheet("sheet-1")
.registerWriteHandler(horizontalCellStyleStrategy)
.doWrite(getRows());
}
private List<List<String>> getHeads() {
List<List<String>> headOfColumns = ListUtils.newArrayList();
/// 第 0 列表头(值相同时会自动合并)
List<String> headOfColumn0 = ListUtils.newArrayList();
// 第 0 列,第 0 行
headOfColumn0.add("姓名");
// 第 0 列,第 1 行
headOfColumn0.add("姓名");
List<String> headOfColumn1 = ListUtils.newArrayList();
headOfColumn1.add("性别");
headOfColumn1.add("性别");
/// 第 2,3,4 列,在第 1 行的值相同,都是[成绩],自动合并
List<String> headOfColumn2 = ListUtils.newArrayList();
headOfColumn2.add("成绩");
headOfColumn2.add("语文");
List<String> headOfColumn3 = ListUtils.newArrayList();
headOfColumn3.add("成绩");
headOfColumn3.add("数学");
List<String> headOfColumn4 = ListUtils.newArrayList();
headOfColumn4.add("成绩");
headOfColumn4.add("英语");
headOfColumns.add(headOfColumn0);
headOfColumns.add(headOfColumn1);
headOfColumns.add(headOfColumn2);
headOfColumns.add(headOfColumn3);
headOfColumns.add(headOfColumn4);
return headOfColumns;
}
private List<List<String>> getRows() {
List<List<String>> rows = ListUtils.newArrayList();
// 第 0 行数据
List<String> row0 = ListUtils.newArrayList();
row0.add("andy");
row0.add("male");
row0.add("80");
row0.add("90");
row0.add("100");
// 第 1 行数据
List<String> row1 = ListUtils.newArrayList();
row1.add("sara");
row1.add("female");
row1.add("100");
row1.add(null);
row1.add("100");
rows.add(row0);
rows.add(row1);
return rows;
}
``
@Test
public void noModelMultiSheetRead() {
String fileName = FILE_DIR + File.separator + "demo1.xlsx";
try (ExcelReader excelReader = EasyExcel.read(fileName).build()) {
ReadSheet readSheet1 = EasyExcel.readSheet(0).registerReadListener(new NoModelDataListener()).build();
// 不同 sheet 一般使用不同的 listener,这里为方便,使用同一个
ReadSheet readSheet2 = EasyExcel.readSheet(1).registerReadListener(new NoModelDataListener()).build();
excelReader.read(readSheet1, readSheet2);
}
}
import lombok.Getter;
import java.util.*;
public class CollectionUtils {
// ElementComparator 参考了 Comparator 中的 compare 方法
//
// @FunctionalInterface
// public interface Comparator<T> {
// int compare(T o1, T o2);
// }
//
// compare 方法的返回值:
// o1 < o2 --> a negative integer
// o1 = o2 --> zero
// o1 > o2 --> a positive integer
//
// 而比较是否是同一个元素时,只关心是否相等,并不关心谁大谁小,因此自定义了一个比较器接口,与直接使用 Comparator 相比,更优雅一点
@FunctionalInterface
public interface ElementComparator<T> {
boolean isEqual(T o1, T o2);
}
/**
* 比较新旧集合中的元素差异
* 使用场景: 批量更新
* 方式-1: 先全部删除,再新增.【实现简单】
* ------- 很多时候,删除操作不是物理删除,而是软删除,在此场景下,先全部删除,会产量大量被标识为已删除的数据
* ------- 若 id 是关联字段,还需要重新处理关联关系
* ------- 不会记录更新人,只有创建人
* 方式-2: 比较新数据和旧数据的差异,分别处理.【实现复杂】
* ------- 软删除场景不会产生大量被标识为已删除的数据
*
* @param newElements 新集合(一般来源于前端)
* @param oldElements 旧集合(一般来源于数据库)
* @param comparator 定义集合元素比较规则
* @return
*/
public static <T> CompareResult<T> compare(List<T> newElements, List<T> oldElements, ElementComparator<T> comparator) {
List<T> duplicatedElementsFromNew = new ArrayList<>();
List<T> duplicatedElementsFromOld = new ArrayList<>();
Iterator<T> newIterator = newElements.iterator();
while (newIterator.hasNext()) {
T newObj = newIterator.next();
Iterator<T> oldIterator = oldElements.iterator();
while (oldIterator.hasNext()) {
T oldObj = oldIterator.next();
if (comparator.isEqual(newObj, oldObj)) {
duplicatedElementsFromNew.add(newObj);
duplicatedElementsFromOld.add(oldObj);
try {
oldIterator.remove();
newIterator.remove();
} catch (UnsupportedOperationException e) {
throw new RuntimeException("请使用可变集合");
}
}
}
}
return new CompareResult<>(newElements, oldElements, duplicatedElementsFromNew, duplicatedElementsFromOld);
}
@Getter
public static class CompareResult<T> {
/**
* 新集合中独有的元素,该集合一般用于【新增】操作
*/
private final List<T> uniqueElementsFromNew;
/**
* 旧集合中独有的元素,该集合一般用于【删除】操作
*/
private final List<T> uniqueElementsFromOld;
/**
* 新旧集合中都存在的元素(元素来自新集合),该集合一般用于【更新】操作
* Comparator 一般根据 id 等字段判断是否为相同的元素,对于新旧集合中的相同元素,id 虽然没变,但是其他字段可能改变了,因此可用该集合做更新操作
*/
private final List<T> duplicatedElementsFromNew;
/**
* 新旧集合中都存在的元素(元素来自旧集合)
*/
private final List<T> duplicatedElementsFromOld;
public CompareResult(List<T> uniqueElementsFromNew, List<T> uniqueElementsFromOld, List<T> duplicatedElementsFromNew, List<T> duplicatedElementsFromOld) {
this.uniqueElementsFromNew = uniqueElementsFromNew;
this.uniqueElementsFromOld = uniqueElementsFromOld;
this.duplicatedElementsFromNew = duplicatedElementsFromNew;
this.duplicatedElementsFromOld = duplicatedElementsFromOld;
}
}
public static void main(String[] args) {
User user1 = new User(1L, 10, "Allen");
User user2 = new User(2L, 10, "Brian");
User user3_1 = new User(3L, 10, "Caroline");
User user3_2 = new User(3L, 20, "Caroline New");
User user4 = new User(4L, 10, "David");
User user5 = new User(5L, 10, "Elisa");
// Arrays.asList 返回的列表是不可变集合,因此需要转为可变集合(允许动态地添加、删除或修改其元素的集合)
List<User> newElements = new ArrayList<>(Arrays.asList(user4, user5, user3_2));
List<User> oldElements = new ArrayList<>(Arrays.asList(user1, user2, user3_1));
System.out.println("newElements: " + newElements);
System.out.println("oldElements: " + oldElements);
CompareResult<User> compareResult = compare(newElements, oldElements, (o1, o2) -> o1.getId().equals(o2.getId()));
System.err.println("uniqueElementsFromNew: " + compareResult.getUniqueElementsFromNew());
System.err.println("uniqueElementsFromOld: " + compareResult.getUniqueElementsFromOld());
System.err.println("duplicatedElementsFromNew: " + compareResult.getDuplicatedElementsFromNew());
System.err.println("duplicatedElementsFromOld: " + compareResult.getDuplicatedElementsFromOld());
}
}
@Test
public void complexWrite() {
// 指定范围合并
//OnceAbsoluteMergeStrategy secondHeadMaleMergeStrategy = new OnceAbsoluteMergeStrategy(1, 1, 1, 2);
//// 设置表头样式
WriteCellStyle headCellStyle = new WriteCellStyle();
/// 字体样式
WriteFont headWriteFont = new WriteFont();
// 粗体
headWriteFont.setBold(true);
// 字体高度
headWriteFont.setFontHeightInPoints(Short.valueOf("20"));
headCellStyle.setWriteFont(headWriteFont);
// 单元格填充颜色
headCellStyle.setFillForegroundColor(GREY_25_PERCENT.index);
// 水平居中
headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//// 设置内容样式
WriteCellStyle contentCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setColor(RED.getIndex());
contentCellStyle.setWriteFont(contentWriteFont);
contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
String firstSheetName = "sheet-1";
/// 自定义样式
CellWriteHandler cellWriteHandler = new CellWriteHandler() {
/**
* 在单元格创建之后执行的操作: 遍历所有 sheet 的所有 cell
*/
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
if (sheet.getSheetName().equals(firstSheetName)) {
int columnIndex = cell.getColumnIndex();
// 设置第 0 列的宽度为 20 个字符
if (columnIndex == 0) {
//Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
//CellStyle cellStyle = workbook.createCellStyle();
//cellStyle.setAlignment(HorizontalAlignment.LEFT);
//cell.setCellStyle(cellStyle);
sheet.setColumnWidth(columnIndex, 20 * 256);
}
}
}
};
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle);
String fileName = "test.xlsx";
EasyExcel.write(fileName).head(getHeads()).sheet(firstSheetName)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(cellWriteHandler)
.doWrite(getRows());
}