POI & EasyExcel
POI
Excel写操作
导入依赖
<!-- xls(03) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- xlsx(07) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- joda-time -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.6</version>
</dependency>
<!-- junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
</dependency>
写入测试
public class ExcelWriteTest {
String PATH = "/Users/work/idea_work/poi_easy-excel/";
@Test
public void testWrite03() throws Exception {
// 创建一个工作簿, 03版本的为new HSSFWorkbook(), 07版本的为new XSSFWorkbook()
Workbook workbook = new XSSFWorkbook();
// 创建一个sheet页
Sheet sheet = workbook.createSheet("学生信息表1");
// 创建一行
Row row1 = sheet.createRow(0);
// 创建一个单元格(1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("学生姓名");
// (1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("年龄");
// (1,3)
Cell cell31 = row1.createCell(2);
cell31.setCellValue("创建时间");
// 第二行
Row row2 = sheet.createRow(1);
// (2,1)
Cell cell21 = row2.createCell(0);
cell21.setCellValue("李志鹏");
// (2,2)
Cell cell22 = row2.createCell(1);
cell22.setCellValue("23");
// (2,3)
Cell cell32 = row2.createCell(2);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell32.setCellValue(time);
// 创建一个文件输出流,03版本的后缀为.xls, 07版本的后缀为.xlsx
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "学生信息表.xlsx");
workbook.write(fileOutputStream);
// 关流
fileOutputStream.close();
System.out.println("学生表创建成功~");
}
}
大数据的写入
HSSF (03)
优点:
在内存中操作,最后一次写入磁盘,速度快
缺点:
最大只能一次插入65536行数据,否则会报错
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
@Test
public void testWriteBigData() throws IOException {
long start = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 65537; rowNum++) { // 最大行数为65536行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream outputStream = new FileOutputStream(PATH + "大数据写入.xls");
workbook.write(outputStream);
outputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - start)/1000);
}
XSSF (07)
优点:
可以写入更大的数据量
缺点:
写数据速度慢,耗内存
@Test
public void testWriteBigData2() throws IOException {
long start = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream outputStream = new FileOutputStream(PATH + "大数据写入2.xlsx");
workbook.write(outputStream);
outputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - start)/1000);
}
SXSSF 大数据操作
优点:
可以写入非常大的数据量,速度更快,占用内存少
注意:
过程中会产生临时文件,需要清理临时文件
@Test
public void testWriteBigData3() throws IOException {
long start = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream outputStream = new FileOutputStream(PATH + "大数据写入3.xlsx");
workbook.write(outputStream);
outputStream.close();
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double) (end - start)/1000);
}
Excel读取操作
HSSF (3)
@Test
public void ExcelRead03() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "学生信息表.xls");
// 根据输入流创建工作簿
Workbook workbook = new HSSFWorkbook(inputStream);
// 获取表
Sheet sheet = workbook.getSheet("学生信息表1");
// 获取行
Row row = sheet.getRow(0);
// 获取列
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
// 关闭输入流
inputStream.close();
}
XSSF
@Test
public void ExcelRead07() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "学生信息表.xlsx");
// 根据输入流创建工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
// 获取表
Sheet sheet = workbook.getSheet("学生信息表1");
// 获取行
Row row = sheet.getRow(1);
// 获取列
Cell cell = row.getCell(1);
System.out.println(cell.getStringCellValue());
// 关闭输入流
inputStream.close();
}
不同数据类型数据读取
@Test
public void testCellType() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "学生信息表.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet("学生信息表1");
// 获取标题行
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
int cellCount = rowTitle.getPhysicalNumberOfCells(); // 获取该行总共列数
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
System.out.println();
}
// 获取表内容
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 0; rowNum < rowCount; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row != null) {
int cellCount = row.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK: // 空数据
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串数据
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔类型数据
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC: // 数字类型,包含日期类型
if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期类型
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 数字类型转字符串类型,防止数字过长
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR: // 错误数据类型
break;
}
System.out.print(cellValue + " | ");
}
}
}
System.out.println();
}
inputStream.close();
}
公式类型数据读取
@Test
public void testFormula() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "公式.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(3);
Cell cell = row.getCell(0);
// 获取计算公式
FormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
// 输出单元格内容
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_FORMULA: // 公式数据
String formula = cell.getCellFormula();
System.out.println(formula);
// 计算公式
CellValue evaluate = evaluator.evaluate(cell);
String cellValue = evaluate.formatAsString(); // 公式转字符串
System.out.println(cellValue);
break;
}
inputStream.close();
}
EasyExcel
导入依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
</dependency>
实体类:
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
写入操作
简单写入
public class EasyExcelTest {
String PATH = "/Users/work/idea_work/poi_easy-excel/";
// 通用数据
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;
}
// 写入操作
@Test
public void simpleWrite() {
String fileName = PATH + "EasyExcelTest.xls"; // 文件写入位置和文件名
// write(文件名称,对应实体类); sheet(表名); doWrite(写入的数据); 文件流会自动关闭
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}
}
读取操作
简单读取
监听器:
public class DemoDataListener extends AnalysisEventListener<DemoData> {
private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
// 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
private static final int BATCH_COUNT = 5;
List<DemoData> list = new ArrayList<DemoData>();
// 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
private DemoDAO demoDAO;
public DemoDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}
// 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
// 这个每一条数据解析都会来调用
@Override
public void invoke(DemoData data, AnalysisContext context) {
//LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
System.out.println("解析到一条数据:" + JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
// 所有数据解析完成了 都会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
// LOGGER.info("所有数据解析完成!");
System.out.println("所有数据解析完成!");
}
// 加上存储数据库
private void saveData() {
// LOGGER.info("{}条数据,开始存储数据库!", list.size());
System.out.println(list.size() + "条数据,开始存储数据库!");
demoDAO.save(list);
// LOGGER.info("存储数据库成功!");
System.out.println("存储数据库成功!");
}
}
简单读取:
String PATH = "/Users/work/idea_work/poi_easy-excel/";
@Test
public void simpleRead() {
String fileName = PATH + "EasyExcelTest.xls"; // 文件位置
// read(文件名称,对应实体类,监听器).sheet().deRead(); 读取第一个sheet,文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
其他API
指定参数不写入
// 指定参数不写入
@Test
public void excludeOrIncludeWrite() {
String fileName = PATH + "EasyExcelTest.xls";
Set<String> excludeColumnFiledNames = new HashSet<String>();
excludeColumnFiledNames.add("date"); // date不写入excel
EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板").doWrite(data());
}
指定写入的列
@Data
public class IndexData {
@ExcelProperty(value = "字符串标题", index = 0)
private String string;
@ExcelProperty(value = "日期标题", index = 1)
private Date date;
@ExcelProperty(value = "数字标题", index = 3) // 这里设置3 会导致第二列空的
private Double doubleData;
}
// 指定写入的列
@Test
public void indexWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, IndexData.class).sheet("模板").doWrite(data());
}
复杂头写入
@Data
public class ComplexHeadData {
@ExcelProperty({"主标题", "字符串标题"})
private String string;
@ExcelProperty({"主标题", "日期标题"})
private Date date;
@ExcelProperty({"主标题", "数字标题"})
private Double doubleData;
}
// 复杂头写入
@Test
public void complexHeadWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data());
}
重复多次写入
单个对象写入一个sheet
// 重复多次写入(单个对象写入一个sheet)
@Test
public void repeatedWrite() {
String fileName = PATH + "EasyExcelTest.xls";
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
// 如果写入同一个sheet只需要创建一次
WriteSheet sheet = EasyExcel.writerSheet("模板").build();
for (int i = 0; i < 2; i++) {
List<DemoData> data = data();
excelWriter.write(data, sheet);
}
} finally {
// 关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}
同一对象写入不同sheet
// 同一对象写入不同sheet
@Test
public void repeatedWrite2() {
String fileName = PATH + "EasyExcelTest.xls";
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
for (int i = 0; i < 5; i++) {
WriteSheet sheet = EasyExcel.writerSheet("模板" + i).build();
List<DemoData> data = data();
excelWriter.write(data, sheet);
}
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
不同对象写入不同sheet
// 不同对象写入不同sheet
@Test
public void repeatedWrite3() {
String fileName = PATH + "EasyExcelTest.xls";
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName).build();
for (int i = 1; i <= 3; i++) {
switch (i) {
case 1:
WriteSheet sheet1 = EasyExcel.writerSheet(i, "模板1").head(DemoData.class).build();
List<DemoData> data = data();
excelWriter.write(data, sheet1);
break;
case 2:
WriteSheet sheet2 = EasyExcel.writerSheet(i, "模板2").head(IndexData.class).build();
List<IndexData> indexData = indexData();
excelWriter.write(indexData, sheet2);
break;
case 3:
WriteSheet sheet3 = EasyExcel.writerSheet(i, "模板3").head(ComplexHeadData.class).build();
List<ComplexHeadData> complexHeadData = complexHeadData();
excelWriter.write(complexHeadData, sheet3);
break;
}
}
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
模板1:
模板2:
模板3:
日期、数字自定义格式转换
@Data
public class ConverterData {
/**
* 我想所有的 字符串起前面加上"自定义:"三个字
*/
@ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class)
private String string;
/**
* 我想写到excel 用年月日的格式
*/
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty("日期标题")
private Date date;
/**
* 我想写到excel 用百分比表示
*/
@NumberFormat("#.##%")
@ExcelProperty(value = "数字标题")
private Double doubleData;
}
public class CustomStringStringConverter implements Converter<String> {
@Override
public Class supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
/**
* 这里是读的时候会调用 不用管
*
* @param cellData NotNull
* @param contentProperty Nullable
* @param globalConfiguration NotNull
* @return
*/
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return cellData.getStringValue();
}
/**
* 这里是写的时候会调用 不用管
*
* @param value NotNull
* @param contentProperty Nullable
* @param globalConfiguration NotNull
* @return
*/
@Override
public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData("自定义:" + value);
}
}
// 日期、数字自定义格式转换
@Test
public void converterWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, ConverterData.class).sheet("模板").doWrite(data());
}
图片写入
/**
* 图片导出类
*/
@Data
@ContentRowHeight(100)
@ColumnWidth(100/8)
public class ImageData {
private File file;
private InputStream inputStream;
@ExcelProperty(converter = StringImageConverter.class)
private String string;
private byte[] byteArray;
// 根据url导出
private URL url;
}
public class TestFileUtil {
public static InputStream getResourcesFileInputStream(String fileName) {
return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
}
public static String getPath() {
return TestFileUtil.class.getResource("/").getPath();
}
public static File createNewFile(String pathName) {
File file = new File(getPath() + pathName);
if (file.exists()) {
file.delete();
} else {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
}
return file;
}
public static File readFile(String pathName) {
return new File(getPath() + pathName);
}
public static File readUserHomeFile(String pathName) {
return new File(System.getProperty("user.home") + File.separator + pathName);
}
}
// 图片写入
@Test
public void imageWrite() throws Exception {
String fileName = PATH + "EasyExcelTest.xls";
FileInputStream inputStream = null;
try {
ArrayList<ImageData> list = new ArrayList<ImageData>();
ImageData imageData = new ImageData();
list.add(imageData);
String imagePath = TestFileUtil.getPath() + "图片1.png";
// 五种方式写入图片
imageData.setFile(new File(imagePath));
imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
imageData.setString(imagePath);
inputStream = FileUtils.openInputStream(new File(imagePath));
imageData.setInputStream(inputStream);
imageData.setUrl(new URL("https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));
EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list);
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
根据模板写入
// 根据模板写入
@Test
public void templateWrite() {
String templateFileName = PATH + "demo.xls";
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, DemoData.class).withTemplate(templateFileName).sheet().doWrite(data());
}
写入时设置列宽行高
@Data
@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class WidthAndHeightData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
/**
* 宽度为50
*/
@ColumnWidth(50)
@ExcelProperty("数字标题")
private Double doubleData;
}
// 写入时设置列宽和行高
@Test
public void widthAndHeightWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, WidthAndHeightData.class).sheet().doWrite(data());
}
写入时配置自定义样式(注解形式)
/**
* 样式的数据类
*
* @author Jiaju Zhuang
**/
@Data
// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 20)
public class DemoStyleData {
// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
// 字符串的头字体设置成20
@HeadFontStyle(fontHeightInPoints = 30)
// 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
// 字符串的内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 30)
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
}
// 写入时配置自定义样式
@Test
public void annotationStyleWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, DemoStyleData.class).sheet().doWrite(data());
}
写入时自定义样式
// 写入时自定义样式
@Test
public void styleWrite() {
String fileName = PATH + "EasyExcelTest.xls";
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)20);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)20);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, DemoData.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("模板")
.doWrite(data());
}
合并单元格
// 合并单元格(注解方式)
@Test
public void mergeWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, DemoMergeData.class).sheet().doWrite(data());
}
// 合并单元格
@Test
public void mergeWrite2() {
String fileName = PATH + "EasyExcelTest.xls";
LoopMergeStrategy strategy = new LoopMergeStrategy(2, 0); // 每2个合并,第0列元素
EasyExcel.write(fileName, DemoData.class).registerWriteHandler(strategy).sheet().doWrite(data());
}
使用table去写入
// 使用table去写入
@Test
public void tableWrite() {
String fileName = PATH + "EasyExcelTest.xls";
// 这里直接写多个table的案例了,如果只有一个 也可以直一行代码搞定,参照其他案例
// 这里 需要指定写用哪个class去写
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
// 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();
// 这里必须指定需要头,table 会继承sheet的配置,sheet配置了不需要,table 默认也是不需要
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
// 第一次写入会创建头
excelWriter.write(data(), writeSheet, writeTable0);
// 第二次写如也会创建头,然后在第一次的后面写入数据
excelWriter.write(data(), writeSheet, writeTable1);
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}
动态头
private List<List<String>> head() {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add("字符串" + System.currentTimeMillis());
List<String> head1 = new ArrayList<String>();
head1.add("数字" + System.currentTimeMillis());
List<String> head2 = new ArrayList<String>();
head2.add("日期" + System.currentTimeMillis());
list.add(head0);
list.add(head1);
list.add(head2);
return list;
}
// 动态头,实时生成头写入
@Test
public void dynamicHeadWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName)
// 这里放入动态头
.head(head()).sheet("模板")
// 当然这里数据也可以用 List<List<String>> 去传入
.doWrite(data());
}
自动列宽(不太精确)
@Data
public class LongestMatchColumnWidthData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题很长日期标题很长日期标题很长很长")
private Date date;
@ExcelProperty("数字")
private Double doubleData;
}
// 自动列宽
@Test
public void longestMatchColumnWidthWrite() {
String fileName = PATH + "EasyExcelTest.xls";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, LongestMatchColumnWidthData.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong());
}
private List<LongestMatchColumnWidthData> dataLong() {
List<LongestMatchColumnWidthData> list = new ArrayList<LongestMatchColumnWidthData>();
for (int i = 0; i < 10; i++) {
LongestMatchColumnWidthData data = new LongestMatchColumnWidthData();
data.setString("测试很长的字符串测试很长的字符串测试很长的字符串" + i);
data.setDate(new Date());
data.setDoubleData(1000000000000.0);
list.add(data);
}
return list;
}
自定义拦截器
对第一行第一列的头超链接到:https://github.com/alibaba/easyexcel
/**
* 自定义拦截器。对第一行第一列的头超链接到:https://github.com/alibaba/easyexcel
*
* @author Jiaju Zhuang
*/
public class CustomCellWriteHandler implements CellWriteHandler {
private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);
@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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 这里可以对cell进行任何操作
LOGGER.info("第{}行,第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
if (isHead && cell.getColumnIndex() == 0) {
CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("https://github.com/alibaba/easyexcel");
cell.setHyperlink(hyperlink);
}
}
}
对第一列第一行和第二行的数据新增下拉框,显示 测试1 测试2
/**
* 自定义拦截器.对第一列第一行和第二行的数据新增下拉框,显示 测试1 测试2
*
* @author Jiaju Zhuang
*/
public class CustomSheetWriteHandler implements SheetWriteHandler {
private static final Logger LOGGER = LoggerFactory.getLogger(CustomSheetWriteHandler.class);
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
// 区间设置 第一列第一行和第二行的数据。由于第一行是头,所以第一、二行的数据实际上是第二三行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
}
// 自定义拦截器
@Test
public void customHandlerWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, DemoData.class).registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(new CustomSheetWriteHandler()).sheet().doWrite(data());
}
插入批注
/**
* 自定义拦截器.新增注释,第一行头加批注
*
* @author Jiaju Zhuang
*/
public class CommentWriteHandler extends AbstractRowWriteHandler {
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
Sheet sheet = writeSheetHolder.getSheet();
Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
// 在第一行 第二列创建一个批注
Comment comment = drawingPatriarch.createCellComment(
new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 0, (short) 2, 1));
// 输入批注信息
comment.setString(new HSSFRichTextString("创建批注!"));
// 将批注添加到单元格对象中
sheet.getRow(0).getCell(1).setCellComment(comment);
}
}
}
// 插入批注
@Test
public void commentWrite() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.write(fileName, DemoData.class).inMemory(Boolean.TRUE).registerWriteHandler(new CommentWriteHandler()).sheet().doWrite(data());
}
读取指定列名或下标
@Data
public class IndexOrNameData {
/**
* 强制读取第三个 这里不建议 index 和 name 同时用,要么一个对象只用index,要么一个对象只用name去匹配
*/
@ExcelProperty(index = 2)
private Double doubleData;
/**
* 用名字去匹配,这里需要注意,如果名字重复,会导致只有一个字段读取到数据
*/
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
}
/**
* 模板的读取类
*
* @author Jiaju Zhuang
*/
public class IndexOrNameDataListener extends AnalysisEventListener<IndexOrNameData> {
private static final Logger LOGGER = LoggerFactory.getLogger(IndexOrNameDataListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<IndexOrNameData> list = new ArrayList<IndexOrNameData>();
@Override
public void invoke(IndexOrNameData data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
System.out.println("解析到一条数据:" + JSON.toJSONString(data));
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
LOGGER.info("存储数据库成功!");
}
}
// 读取指定列名或下标
@Test
public void indexOrNameRead() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.read(fileName, IndexOrNameData.class, new IndexOrNameDataListener()).sheet().doRead();
}
读取多个sheet
读取全部sheet
// 读取全部sheet
@Test
public void repeatedRead() {
String fileName = PATH + "EasyExcelTest.xls";
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();
}
读取部分sheet
// 读取部分sheet
@Test
public void repeatedRead2() {
String fileName = PATH + "EasyExcelTest.xls";
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(fileName).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去,不然有个问题就是03版的excel 会读取多次,浪费性能
excelReader.read(readSheet1, readSheet2);
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
}
}