JAVA解析Excel工具easyexcel
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。
github地址：https://github.com/alibaba/easyexcel
依赖jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.2</version>
</dependency>读取Excel
从2.0.x开始类似如下方法已是过时方法，可以使用推荐的方法去实现。
1.无模板
@Controller
public class EasyExcelController {
@RequestMapping("/readNoEntity")
@ResponseBody
public Map<String,Object> noEntity(MultipartFile excelFile) throws IOException {
Map<String,Object> result = new HashMap<>();
List<Object> list = EasyExcel.read(excelFile.getInputStream()).sheet(0).doReadSync();
result.put("list", list);
return result;
}
}2.有模板
@Controller
public class EasyExcelController {
@RequestMapping("/readToEntity")
@ResponseBody
public Map<String,Object> toEntity(MultipartFile excelFile) throws IOException {
Map<String,Object> result = new HashMap<>();
List<Object> list = EasyExcel.read(excelFile.getInputStream(),User.class,null).sheet(0).doReadSync();
result.put("list", list);
return result;
}
}模板Model同样从2.0.x开始就不需要再继承BaseRowModel了
@Data
public class User {
private String name;
private String account;
private String address;
}3.也可以根据需要自定义监听，只需要继承AnalysisEventListener
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ruihao.zzx.client.demo.entity.User;
public class ExcelListener extends AnalysisEventListener<User> {
private final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
private List<User> datas = new ArrayList<>();
//会读取每一行的数据
@Override
public void invoke(User object, AnalysisContext context) {
System.out.println(object);
datas.add(object);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
LOGGER.info("{}条数据，开始存储数据库！", datas.size());
LOGGER.info("所有数据解析完成！");
}
public List<User> getDatas() {
return datas;
}
public void setDatas(List<User> datas) {
this.datas = datas;
}
}invoke方法会一行一行的读取excel表格中的数据，使用自定义监听如下
@Controller
public class EasyExcelController {
@RequestMapping("/readToEntity")
@ResponseBody
public Map<String,Object> toEntity(MultipartFile excelFile) throws IOException {
Map<String,Object> result = new HashMap<>();
ExcelListener listener = new ExcelListener();
EasyExcel.read(excelFile.getInputStream(),User.class,listener).sheet(0).doRead();
result.put("list", listener.getDatas());
return result;
}
}这里使用的是ExcelReaderSheetBuilder类的doRead()方法。doRead()和doReadSync()区别在于doReadSync()里面配了一个自定义的监听，并且返回读取到excel数据的List集合
/**
* Sax read
*/
public void doRead() {
if (excelReader == null) {
throw new ExcelGenerateException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
}
excelReader.read(build());
excelReader.finish();
}
/**
* Synchronous reads return results
*
* @return
*/
public List<Object> doReadSync() {
if (excelReader == null) {
throw new ExcelAnalysisException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
}
SyncReadListener syncReadListener = new SyncReadListener();
registerReadListener(syncReadListener);
excelReader.read(build());
excelReader.finish();
return syncReadListener.getList();
}4.多个sheet的读取方法
@Controller
public class EasyExcelController {
private final Logger LOGGER = LoggerFactory.getLogger(EasyExcelController.class);
@RequestMapping("/readToEntity")
@ResponseBody
public Map<String,Object> toEntity(MultipartFile excelFile) throws IOException {
Map<String,Object> result = new HashMap<>();
ExcelListener listener = new ExcelListener();
ExcelReaderBuilder builder = EasyExcel.read(excelFile.getInputStream(),User.class,listener);
ExcelReader reader = builder.build();
//sheet集合
List<ReadSheet> sheets = reader.excelExecutor().sheetList();
for(ReadSheet sheet:sheets) {
listener.getDatas().clear();
LOGGER.info("sheet name:{}",sheet.getSheetName());
//读取每一个sheet的内容
reader.read(sheet);
List<User> current = listener.getDatas();
LOGGER.info("content:{}",JSONObject.toJSONString(current));
}
reader.finish();
result.put("list", listener.getDatas());
return result;
}
}
导出Excel
1.无模板
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.alibaba.excel.EasyExcel;
@Controller
public class DownloadController {
@RequestMapping("/download")
public void download(HttpServletResponse response) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
EasyExcel.write(response.getOutputStream()).sheet("第一个sheet").doWrite(data());;
}
protected List<?> data(){
List<List<String>> rowList = new ArrayList<>();
List<String> cell = new ArrayList<>();
cell.add("good1");
cell.add("good2");
cell.add("good3");
rowList.add(cell);
return rowList;
}
}2.有模板
模板实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class Cell {
@ExcelProperty(value= {"主标题","第一列"})
private String cell1;
@ExcelProperty(value= {"主标题","第二列"})
private String cell2;
@ExcelProperty(value="第三列")
private String cell3;
@ExcelProperty(value="第四列")
private String cell4;
@ExcelProperty(value="第五列")
private String cell5;
@ExcelProperty(value="第六列")
private String cell6;
@ExcelProperty(value="第七列")
private String cell7;
@ExcelProperty(value="第八列")
private String cell8;
@ExcelProperty(value="第九列")
private String cell9;
@ExcelProperty(value="第十列")
private String cell10;
}导出方法
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.alibaba.excel.EasyExcel;
@Controller
public class DownloadController {
@RequestMapping("/download")
public void download(HttpServletResponse response) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
EasyExcel.write(response.getOutputStream(),Cell.class).sheet("第一个sheet").doWrite(data());;
}
protected List<?> data(){
List<Cell> rowList = new ArrayList<>();
Cell cell = new Cell();
cell.setCell1("大师傅1");
cell.setCell2("大师傅2");
cell.setCell3("大师傅3");
cell.setCell4("大师傅4");
cell.setCell5("大师傅5");
cell.setCell6("大师傅6");
cell.setCell7("大师傅7");
cell.setCell8("大师傅8");
cell.setCell9("大师傅9");
cell.setCell10("大师傅20");
rowList.add(cell);
return rowList;
}
}3.多个sheet导出
@Controller
public class DownloadController {
@RequestMapping("/download")
public void download(HttpServletResponse response) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
ExcelWriterBuilder builder = EasyExcel.write(response.getOutputStream(),Cell.class);
//注册单元格拦截器
builder.registerWriteHandler(new CustomCellWriteHandler());
//注册sheet拦截器
builder.registerWriteHandler(new CustomSheetWriteHandler());
ExcelWriter excelWriter = builder.build();
WriteSheet writeSheet =null;
writeSheet = EasyExcel.writerSheet(0, "TSBD").build();
excelWriter.write(data(), writeSheet);
writeSheet = EasyExcel.writerSheet(1, "BSBD").build();
excelWriter.write(data2(), writeSheet);
excelWriter.finish();
}
protected List<?> data(){
List<Cell> rowList = new ArrayList<>();
Cell cell = new Cell();
cell.setCell1("大师傅1");
cell.setCell2("大师傅2");
cell.setCell3("大师傅3");
cell.setCell4("大师傅4");
cell.setCell5("大师傅5");
cell.setCell6("大师傅6");
cell.setCell7("大师傅7");
cell.setCell8("大师傅8");
cell.setCell9("大师傅9");
cell.setCell10("大师傅20");
rowList.add(cell);
return rowList;
}
protected List<?> data2(){
List<Cell> rowList = new ArrayList<>();
Cell cell = new Cell();
cell.setCell1("大师傅11");
cell.setCell2("大师傅21");
cell.setCell3("大师傅31");
cell.setCell4("大师傅41");
cell.setCell5("大师傅51");
cell.setCell6("大师傅61");
cell.setCell7("大师傅71");
cell.setCell8("大师傅81");
cell.setCell9("大师傅91");
cell.setCell10("大师傅10");
rowList.add(cell);
return rowList;
}4.配置单元格和sheet拦截器
单元格拦截器
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;
/**
* 自定义单元格拦截器
* @author
* 2019年10月12日
*/
public class CustomCellWriteHandler implements CellWriteHandler {
private final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, int relativeRowIndex, boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData,
Cell cell, Head head, int 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);
}
}
}sheet拦截器
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
/**
* 自定义sheet拦截器
* @author
* 2019年10月12日
*/
public class CustomSheetWriteHandler implements SheetWriteHandler {
private 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(2, 2, 0, 1);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"康师傅", "汤达人"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
}配置
@RequestMapping("/download")
public void download(HttpServletResponse response) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
ExcelWriterBuilder builder = EasyExcel.write(response.getOutputStream(),Cell.class);
//注册单元格拦截器
builder.registerWriteHandler(new CustomCellWriteHandler());
//注册sheet拦截器
builder.registerWriteHandler(new CustomSheetWriteHandler());
ExcelWriter excelWriter = builder.build();
WriteSheet writeSheet =null;
writeSheet = EasyExcel.writerSheet(0, "TSBD").build();
excelWriter.write(data(), writeSheet);
writeSheet = EasyExcel.writerSheet(1, "BSBD").build();
excelWriter.write(data2(), writeSheet);
excelWriter.finish();
}
扫一扫
2996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
