Technology selection process from EasyExcel to Apache POI

The front-end has a function to export the data Kanban to excel, and the whole data Kanban is divided into 5 modules.

Phase 1 Requirement: Import the data presentation of these 5 modules together into excel.

Phase II requirements: the data of these 5 modules will be imported into the excel document on demand, that is to say, there are buttons on the front-end to check the 5 modules, which modules need to be imported into the excel data of which modules are dynamic.

 Realization of Phase I requirements

Since EasyExcel already exists in the project, it is directly considered to use EasyExcel to complete the overall export of the data of the five modules.

 The way I implemented it was intuitive:

1. 
   Using the excel document provided with the product, fill in your own defined placeholders.
2. 
   Upload this excel template document with placeholders to your company’s file server to get a link to a web resource for the file.
3. 
   Get an input stream in the program by linking through this excel template file web resource.
4. 
   The resulting 5 module data is then populated into this input stream. (This is done using EasyExcel.) The brief code is as follows:

	public static void writeOpsExcel(HttpServletResponse response, String filePath, String fileName, String sheetName, OverviewStatisticsVO overviewStatisticsVO, List<OpsScreenVO> vos) throws IOException {
           InputStream is = IoUtils.getIs(filePath);
          ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(is).build();
		try {
			WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
 			excelWriter.fill(overviewStatisticsVO, writeSheet);
			FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).build();
			excelWriter.fill(vos, fillConfig, writeSheet);
		} finally {
			if (excelWriter != null) {
                            excelWriter.finish();
			}
		}
	}

This excel template is extremely complex, let’s take a look at it: (there’s more on the right, it’s not finished yet)

According to my four steps above can be perfectly realized this document export requirements, and then send test delivery on schedule.

Well, on the day of sending the test, the product suddenly said that the requirement has been changed to export module data to excel on-demand.

 Realization of phase II requirements

The point of change between Phase 1 and Phase 2 is intuitively that the number of modules has changed from a fixed 5 to a dynamically selectable one.

 But the technical solutions on the back end are pretty much going to have to be pushed back.

 Envisioning technical solutions

 The technical solution I envisioned for realizing the needs of Phase II based on Phase I:

1.  The front-end passes in each module identifier, e.g. 1-5 corresponds to 5 modules.
2. 
   I get the input stream based on the excel template file web resource link.
3. 
   This input stream is passed to EasyExcel, which then deletes the cell areas corresponding to module identifiers not passed in by the front-end on a per-module basis.
4. 
   Since the module data cells are linked together, this is what happens when they are deleted: module data – deleted blank cells – module data, so you need to move the ones that have data together.

 Okay, we’ve got a good direction for our technical solution, go look at feasibility:

1. 
   First of all, I briefly looked at the api related to ExcelWriter in EasyExcel, and found that there is no api for cell deletion.
2.  Then went back and asked Bunshin Ichiyo, and her answer is below:

Well, it doesn’t seem to be supported. 3. 3. go to the official website to confirm: About Easyexcel | Easy Excel (alibaba.com) indeed the whole content does not mention the word deleted. 4. even add the official exchange group to ask in the group, no reply, haha. 4. even added an official communication group in the group asked, no one replied, haha.

By this point I knew the odds were that a straight EasyExcel solution wouldn’t work.

Apache POI

Apache POI debut, this is the underlying dependency of EasyExcel, the ability to operate excel document granularity is even smaller and more flexible.

On the downside, EasyExcel’s website says that importing and exporting large files can be very memory intensive.

Continuing with the scenario described above, Apache POI does provide an API implementation for deleting cells in a given area and moving cells left in a certain area. However, because my template file has a complex style operation such as merging cells, resulting in the wrong formatting after the move.

 Replacement program

 An idea for another replacement came to mind:

1.  The front-end passes in each module identifier, e.g. 1-5 corresponds to 5 modules.
2. 
   I get the input stream based on the excel template file web resource link.
3.  Pass this input stream to the POI for manipulation.
4.  We can create another empty sheet.
5. 
   Copy the sheet template cell corresponding to the template logo passed in by the front-end to our newly created sheet.
6. 
   After completing the corresponding template cell copying operation, the original sheet will be deleted.

In short, the core idea is to use an empty sheet to load the corresponding template cells, and so loaded to delete all the original template cells of the sheet, a kind of “civet for a prince” approach.

 Problems encountered

OK, said dry this way is finally possible, but in practice still encountered a variety of problems, say a few fresh in my mind:

1.  It’s still cell merging, but the problem is clearer and easier to solve.
2.  The previous template had frozen rows and columns, this needs to be taken care of.
3. 
   The most troublesome is the module cell area filled with “tightly”, that is, there can not be module data – empty – module data in this form, the logic is not difficult, but pay attention to the total number of modules cumulative number of columns, do an offset.

To here, the first and second phase of the function is to realize the end, but also a little bit of EasyExcel and Apache POI have an understanding. Next, compare the two technology programs.

Summary: EasyExcel vs. Apache POI

1. 
   EasyExcel makes it easier to import and export according to a fixed template, and the official website also says it is more memory-efficient.
2. 
   But EasyExcel I think the application of the scenario is not too flexible, such as my needs of this second phase, the need to do operations on the specified cell does not seem to support.
3. 
   Apache POI Well, flexible and easy to use, powerful API support, after all, EasyExcel bottom with it, the shortcomings: EasyExcel official website said it consumes memory, there is a risk of memory overflow.