Introduction: EasyExcel is a Java-based simple, memory-saving open source project for reading and writing Excel. In the case of saving memory as much as possible, it supports reading and writing Excel of hundreds of M.
Official document: EasyExcel official document – Java-based Excel processing tool | Easy Excel
Prerequisites
Depends
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
Excel import
Easiest reading steps:
- Create the entity object corresponding to excel
- Since excel is read line by line by default, it is necessary to create a callback listener (StringExcelListener) for excel line by line
- Just read it directly
Test Entity Class
@Data
@EqualsAndHashCode
public class DemoData {
private String string;
private Date date;
private Double doubleData;
}
StringExcelListener listening class
There is a very important point, StringExcelListener cannot be managed by spring, it must be new every time it reads excel, and then spring can be used in it to construct methods to pass in.
@Slf4j
public class StringExcelListener extends AnalysisEventListener
{
/**
* Customized for temporary storage of data
* This value can be obtained through the instance
*/
private List<List<String>> datas = ListUtils. newArrayList();
/**
* Every parsed line will call back the invoke() method
* @param o
* @param analysisContext
*/
@Override
public void invoke(Object o, AnalysisContext analysisContext)
{
@SuppressWarnings("unchecked") Map<String, String> stringMap = (HashMap<String, String>) o;
//Data is stored in the list for batch processing, or subsequent business logic processing.
datas.add(new ArrayList<>(stringMap.values()));
//Process according to your own business
}
/**
* All data analysis is completed, it will be called
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext)
{
log.info("All data analysis completed!");
}
/**
* return data
*
* @return Returns the read data set
**/
public List<List<String>> getDatas() {
return data;
}
/**
* Set the data set to read
*
* @param datas set the data set to read
**/
public void setDatas(List<List<String>> datas) {
this.datas = datas;
}
}
Encapsulate ExcelUtils tool class
@Component
public class ExcelUtils
{
/**
* Read excel data
*
* @param file
* @return result
* @author hecc
* @date 2022/9/27 17:43
**/
public List<List<String>> writeWithoutHead(MultipartFile file) {
List<List<String>> result = Lists. newArrayList();
try {
result = writeWithoutHead(file. getInputStream());
} catch (IOException e) {
throw new RuntimeException("APP_ADD_ERROR, file parsing exception");
}
if (CollectionUtils. isEmpty(result)) {
throw new RuntimeException("APP_ADD_ERROR, the file is empty");
}
return result;
}
public List<List<String>> writeWithoutHead(InputStream inputStream) {
StringExcelListener listener = new StringExcelListener();
ExcelReader excelReader = EasyExcelFactory.read(inputStream, null, listener).headRowNumber(1).build();
excelReader. read();
List<List<String>> datas = listener. getDatas();
excelReader. finish();
return data;
}
}
controller layer
@RestController
@RequiredArgsConstructor
@FieldDefaults(level = AccessLevel. PRIVATE, makeFinal = true)
@RequestMapping("/excel")
public class ExcelController
{
ExcelUtils excelUtils;
@PostMapping("/read")
public void read(@RequestParam("file") MultipartFile file) {
if(file.isEmpty()){
throw new RuntimeException("Please upload the file!");
}
List<List<String>> lists = excelUtils.writeWithoutHead(file);
//Process, store in object
List<DemoData> demoDataList = ListUtils. newArrayList();
lists.forEach(item->{
DemoData demoData = new DemoData();
demoData.setString(item.get(0));
try
{
demoData.setDate(DateUtils.parseDate(item.get(1)));
}
catch (ParseException e)
{
throw new RuntimeException(e);
}
demoData.setDoubleData(Double.valueOf(item.get(2)));
demoDataList.add(demoData);
});
demoDataList.forEach(System.out::println);
}
}
Run results
Modified version
Test Entity Class
@Data
@EqualsAndHashCode
public class DemoData {
@ExcelProperty("string title")
private String string;
@ExcelProperty("date title")
/**
* Here use string to pick up the date to format. Others may also require String
*/
@DateTimeFormat("yyyy MM dd day")
private String date;
@ExcelProperty(index = 2)
private Double doubleData;
}
StringExcelListener listening class
There is a very important point, StringExcelListener cannot be managed by spring, it must be new every time it reads excel, and then spring can be used in it to construct methods to pass in.
@Slf4j
public class StringExcelListener<T> extends AnalysisEventListener<T>
{
/**
* Customized for temporary storage of data
* This value can be obtained through the instance
*/
private List<T> datas = ListUtils. newArrayList();
/**
* Every parsed line will call back the invoke() method
* @param o
* @param analysisContext
*/
@Override
public void invoke(T o, AnalysisContext analysisContext)
{
//@SuppressWarnings("unchecked") Map<String, String> stringMap = (HashMap<String, String>) o;
//Data is stored in the list for batch processing, or subsequent business logic processing.
datas. add(o);
//Process according to your own business
}
/**
* All data analysis is completed, it will be called
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext)
{
log.info("All data analysis completed!");
}
/**
* return data
*
* @return Returns the read data set
**/
public List<T> getDatas() {
return data;
}
/**
* Set the data set to read
*
* @param datas set the data set to read
**/
public void setDatas(List<T> datas) {
this.datas = datas;
}
}
Encapsulate ExcelUtils tool class
@Component
public class ExcelUtils
{
/**
* Read excel data
*
* @param file
* @return result
* @date 2022/9/27 17:43
**/
public <T> List<T> writeWithoutHead(MultipartFile file, Class<T> clz ) {
List<T> result = Lists. newArrayList();
try {
result = writeWithoutHead(file. getInputStream(),clz);
} catch (IOException e) {
throw new RuntimeException("APP_ADD_ERROR, file parsing exception");
}
if (CollectionUtils. isEmpty(result)) {
throw new RuntimeException("APP_ADD_ERROR, the file is empty");
}
return result;
}
public <T> List<T> writeWithoutHead(InputStream inputStream, Class<T> clz) {
StringExcelListener listener = new StringExcelListener();
ExcelReader excelReader = EasyExcelFactory.read(inputStream, clz, listener).headRowNumber(1).build();
excelReader. read();
List<T> datas = listener. getDatas();
excelReader. finish();
return data;
}
}
controller layer
@RestController
@RequiredArgsConstructor
@FieldDefaults(level = AccessLevel. PRIVATE, makeFinal = true)
@RequestMapping("/excel")
public class ExcelController
{
ExcelUtils excelUtils;
@PostMapping("/read")
public void read(@RequestParam("file") MultipartFile file) {
if(file.isEmpty()){
throw new RuntimeException("Please upload the file!");
}
List<DemoData> demoDataList = excelUtils.writeWithoutHead(file,DemoData.class);
demoDataList.forEach(System.out::println);
}
}
Run results
Excel export
1. Simple export
Test Entity Class
@Data
@EqualsAndHashCode
public class DemoData {
@ExcelProperty("string title")
private String string;
@ExcelProperty("date title")
@DateTimeFormat("yyyy MM dd day")
private Date date;
@ExcelProperty("Number Title")
private Double doubleData;
/**
* ignore this field
*/
@ExcelIgnore
private String ignore;
}
controller layer
@PostMapping("/write")
public void write() {
//Writing 1
String fileName ="simpleWrite" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName, DemoData.class)
.sheet("template")
.doWrite(() -> {
// Paging query data
return data();
});
// Writing 2
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// Here you need to specify which class to use for writing, then write to the first sheet, the name is template, and the file stream will be automatically closed
// If you want to use 03 here, just pass in the excelType parameter
EasyExcel.write(fileName, DemoData.class).sheet("template").doWrite(data());
// writing method 3
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// Here you need to specify which class to use for writing
try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("template").build();
excelWriter.write(data(), writeSheet);
}
}
//Test Data
private List<DemoData> data() {
List<DemoData> list = ListUtils. newArrayList();
for (int i = 0; i < 10; i ++ ) {
DemoData data = new DemoData();
data.setString("string" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list. add(data);
}
return list;
}
Run result:
Under the project directory:
2. web export
Entity class ditto
controller layer
/**
* File download (if it fails, it will return an Excel with some data)
* <p>
* 1. Create the entity object corresponding to excel. Refer to {@link DemoData}
* <p>
* 2. Set the returned parameter
* <p>
* 3. Write directly. Note here that the OutputStream will be automatically closed when it is finished. Of course, it is not a big problem for you to close the stream outside
*/
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException
{
// Note here that some students have reported that using swagger will cause various problems, please use the browser or postman directly
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// Here URLEncoder.encode can prevent Chinese garbled characters, of course it has nothing to do with easyexcel
String fileName = URLEncoder.encode("Test", "UTF-8").replaceAll("\ + ", " ");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), DemoData.class).sheet("template").doWrite(data());
}
//Test Data
private List<DemoData> data() {
List<DemoData> list = ListUtils. newArrayList();
for (int i = 0; i < 10; i ++ ) {
DemoData data = new DemoData();
data.setString("string" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list. add(data);
}
return list;
}
Reference: Ali’s Easyexcel reads Excel files (latest version)
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeHomepageOverview 118663 people are studying systematically