Unexpectedly, Java operation Excel is so simple!

preface

Before, many Java programmers liked to use the class library of POI to operate excel, but it was very inconvenient, not only the code was very bloated, but also to deal with the compatibility problem of various office versions. The most fear was that improper use would easily cause memory overflow, so today I recommend an open-source project easyexcel from Alibaba.

Project introduction

easyexcel is a fast and simple tool to avoid OOM

GitHub address: https://github.com/alibaba/easyexcel

Start:15.2k

After reading this, the project team was still improving the code two days ago. It can be seen that the project is still very active

Project integration

An easyexc demo project is simply created by using the idea development tool. The web module and easyexc Maven dependency are added. The dependency is as follows:

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.4</version> </dependency>

If we use version 2.2.4, we uploaded it in June of 20, which is the latest version.

OK, let's start writing functions.

1. Download the existing Excel template

Many systems have the scenario of data batch import, because the cost of adding data in batch on the page is too large, but generally, it has to be changed according to a certain format when importing, so the general good product will let the user download a document with format first, then upload and import after writing according to the format, let's realize this function!

Create template file

First, we create a template file with the content as shown in the figure

###Place the template file in the project

Then we put it under the configuration file of the project, as shown in the figure

Then download code is very simple, mainly divided into load resources - > read resources - > write response flow

@RestController @RequestMapping("/user") public class UserController { /** * Download template */ @GetMapping("/downloadTemplate") public void downloadTemplate(HttpServletResponse response) throws Exception { ClassPathResource classPathResource = new ClassPathResource("excelTemplate/easyexcel.xlsx"); InputStream inputStream = classPathResource.getInputStream(); Workbook workbook = new HSSFWorkbook(inputStream); response.setContentType("application/vnd.ms-excel"); response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel.xlsx", "utf-8")); response.setHeader("Access-Control-Expose-Headers", "content-Disposition"); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } }

Start the project, access it, as shown in the figure, and download it.

[failed to transfer the pictures in the external link. The source station may have anti-theft chain mechanism. It is recommended to save the pictures and upload them directly (img-eugwoqcg-1592558095012)( https://tva1.sinaimg.cn/large/007S8ZIlgy1gfojfgj9a9g30ng0e0e84.gif ]

2. Write data and generate files

Exporting data to documents is the most common scenario, so how to use easyExcel to quickly implement it? We also take the above template as an example

Define model mapping object UserExcelModel

@Data public class UserExcelModel extends BaseRowModel implements Serializable { @ExcelProperty(value = "user name", index = 0) private String name; @ExcelProperty(value = "Age", index = 1) private Integer age; @ExcelProperty(value = "cell-phone number", index = 2) private String mobile; @ExcelProperty(value = "Gender", index = 3) private String sex; }

There are two purposes to define this object: the write file in the current scene is used as the model object construction data and the next data to be read.

The brief code flow is as follows:

Define column header - > create sheet - > Customize font and style - > construct data - > write data - > write to browser response flow

/** * Export data */ @GetMapping("/exportData") public void exportData(HttpServletResponse response) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); String []columnNames = {"user name","Age","cell-phone number","Gender"}; Sheet sheet = workbook.createSheet(); Font titleFont = workbook.createFont(); titleFont.setFontName("simsun"); titleFont.setBold(true); titleFont.setColor(IndexedColors.BLACK.index); XSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index); titleStyle.setFont(titleFont); Row titleRow = sheet.createRow(0); for (int i = 0; i < columnNames.length; i++) { Cell cell = titleRow.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(titleStyle); } //Simulation construction data List<UserExcelModel> dataList = new ArrayList<>(); dataList.add(new UserExcelModel("Zhang San",12,"13867098765","male")); dataList.add(new UserExcelModel("Zhang san1",12,"13867098765","male")); dataList.add(new UserExcelModel("Zhang san2",12,"13867098765","male")); dataList.add(new UserExcelModel("Zhang SAN3",12,"13867098765","male")); //Create data row and write value for (int j = 0; j < dataList.size(); j++) { UserExcelModel userExcelModel = dataList.get(j); int lastRowNum = sheet.getLastRowNum(); Row dataRow = sheet.createRow(lastRowNum + 1); dataRow.createCell(0).setCellValue(userExcelModel.getName()); dataRow.createCell(1).setCellValue(userExcelModel.getAge()); dataRow.createCell(2).setCellValue(userExcelModel.getMobile()); dataRow.createCell(3).setCellValue(userExcelModel.getSex()); } response.setContentType("application/vnd.ms-excel"); response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel.xls", "utf-8")); response.setHeader("Access-Control-Expose-Headers", "content-Disposition"); OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); }

3. Read data

Let's go back to the Model object we defined, specify the index to correspond to the columns in the excel that can be read, and then define the data type to correspond to the specific value in the Excel to see how to achieve:

@PostMapping("/readExcel") public List<UserExcelModel> readExcel(@RequestParam("file") MultipartFile file){ List<UserExcelModel> list = new ArrayList<>(); try { list = EasyExcel.read(file.getInputStream(),UserExcelModel.class,new ModelExcelListener()).sheet().doReadSync(); } catch (IOException e) { e.printStackTrace(); } return list; }

After reading the code, is it a draft in my heart? After reading the code, I'll see if it's handled with the poi tool. It's quite simple. For the ModelExcelListener in the code, it is actually a user-defined read listening class. Paste the code:

public static class ModelExcelListener extends AnalysisEventListener { private List<Object> datas = new ArrayList<>(); /** * The current sheet, current row and other data can also be obtained through the AnalysisContext object */ @Override public void invoke(Object data, AnalysisContext context) { //The data is stored in the list for batch processing or subsequent business logic processing. log.info("Read data{}",data); datas.add(data); //Self processing according to business, write to database, etc } //So the data is parsed and called @Override public void doAfterAllAnalysed(AnalysisContext context) { log.info("All data analysis completed"); } }

This is a read data monitoring class. You can customize the implementation of this class for those with special business requirements, such as reading and writing to the library, data filtering and processing, etc. it is a sword if you use it well.

summary

Through this article, we demonstrate how to use easyexcel to perform some excel operations. In the actual project application, we can further encapsulate the above example code, so that no matter how to read, export and other operations can be done in several lines of code, which we have to play freely according to the situation.

Project code acquisition

1. Address: https://github.com/pengziliu/GitHub-code-practice/
2. Click on the bottom to read the original