目录
1、引用maven依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
<!-- java 造数据用 -->
<dependency>
<groupId>com.github.javafaker</groupId>
<artifactId>javafaker</artifactId>
<version>0.17.2</version>
</dependency>
2、模板文件template1.xlsx
3、导出效果
4、导入效果
5、导出用EasyWriteHandler
import com.alibaba.excel.util.WorkBookUtil; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.handler.RowWriteHandler; import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.handler.WorkbookWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; public class EasyWriteHandler implements WorkbookWriteHandler, SheetWriteHandler, RowWriteHandler, CellWriteHandler { private Integer startRowIndex; private Integer lastColumnIndex; @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { if(startRowIndex == null){ startRowIndex = row.getRowNum(); lastColumnIndex = Integer.valueOf(row.getLastCellNum()); } } public EasyWriteHandler(){} public Cell cell(Sheet sheet ,int rowIndex,int cellIndex){ Row row = sheet.getRow(rowIndex); if (row == null) { row = WorkBookUtil.createRow(sheet, rowIndex); } Cell cell = row.getCell(cellIndex); if (cell == null) { cell = WorkBookUtil.createCell(row, cellIndex); } return cell; } public Integer getStartRowIndex() { return startRowIndex; } public Integer getLastColumnIndex() { return lastColumnIndex; } }
6、测试工具类 ExcelTest
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.handler.context.WorkbookWriteHandlerContext;
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.alibaba.fastjson.JSON;
import com.github.javafaker.Faker;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.junit.Test;
import xiao.utils.FakerUtil;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;
public class ExcelTest {
@Test
public void testExport() throws Exception {
String fileName = "D:/export.xlsx";
String templateFile = "D:/template1.xlsx";
List<List<String>> headList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<String> head = new ArrayList<>();
head.add("code"+i);
head.add("标题"+i);
headList.add(head);
}
Faker faker = FakerUtil.FAKER;
List<List<String>> dataList = Stream.generate(() -> {
List<String> data=new ArrayList<>();
for (int i = 0; i < 10; i++) {
data.add(faker.name().fullName());
}
return data;
}).limit(50).collect(Collectors.toList());
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)14);
headWriteCellStyle.setWriteFont(headWriteFont);
//内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short)10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
WriteCellStyle headBlueWriteCellStyle=new WriteCellStyle();
headBlueWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
//添加自定义样式和修改固定单元格内容时需要加上 .inMemory(true)
EasyExcel.write(fileName)
.withTemplate(templateFile).head(headList).inMemory(true)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(new EasyWriteHandler() {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
if (context.getHead()) {
Cell cell = context.getCell();
if(cell.getColumnIndex()%2 == 0){
WriteCellStyle.merge(headBlueWriteCellStyle,
context.getFirstCellData().getOrCreateStyle());
}
}
}
@Override
public void afterWorkbookDispose(WorkbookWriteHandlerContext context) {
Sheet sheet = context.getWriteContext().writeSheetHolder().getSheet();
for (int columnIndex = 0; columnIndex < getLastColumnIndex() ; columnIndex++) {
//设置自适应列宽
sheet.autoSizeColumn(columnIndex);
}
//设置部门名称
cell(sheet,1,1).setCellValue("开发部门");
}
}).sheet().doWrite(dataList);
}
@Test
public void testUpload() throws Exception {
String fileName = "D:/export.xlsx";
List<Map<Integer, String>> resultList = EasyExcel.read(fileName).sheet().headRowNumber(2).doReadSync();
System.out.println(JSON.toJSON(resultList));
}
}