preface

Recently, I took over a requirement. I need to add a menu column in the existing project, which has the function of exporting excel, but the format is relatively complex. There are single group data display and multiple groups of data display. I checked many articles and didn't understand them very well. However, this article is almost very similar to my requirement (the link is placed at the end of the article), According to this article, I then record my implementation process in case I use it later

This is the excel format I need to export
The beginning is the display of a single piece of data
Then there is the display of multiple pieces of data

1. To export complex excel styles, you need to use a custom excel template ---- > which means that we create a new EXCEL according to the excel format we need, and then draw the complete format first, fill in the fixed title content, cell size, background color, etc
2. The data to be changed shall be enclosed in {} curly brackets
Single data directly {attribute name corresponding to entity} for example: user name {userName} password {password}
Multiple pieces of data {. Attribute name corresponding to entity} for example: time {. date} number {. id} plus dot. Means to display multiple pieces of data
3. Note:
If multiple lists need to be displayed in an excel file, they need to be identified before. It is used to distinguish which data is whose. For example, address {list1.address} number {list2.id}

This is the excel template I wrote

Because I only need a list to display multiple pieces of data, I didn't mark it before

Import dependency

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

front end

import { getParamString } from '/@/api/commn/common';
import { getAppEnvConfig } from '/@/utils/env';

enum Api {
  DOWNLOAD_OPERATIONAL = '/ldmp/report/download/operational',
}

const { VITE_GLOB_API_URL } = getAppEnvConfig();

export function exportOperationalListApi(params: any) {
  console.log('Operation data export==' + getParamString(params));
  if (import.meta.env.DEV) {
    window.location.href = Api.DOWNLOAD_OPERATIONAL + getParamString(params);
  } else {
    window.location.href = VITE_GLOB_API_URL + Api.DOWNLOAD_OPERATIONAL + getParamString(params);
  }
}

back-end

ExcelUtils (self encapsulated easyExcel tool class)

public class ExcelUtils {
	/**
	 * Export fixed header Excel
	 * Author wang.lch
	 * @param response
	 * @param list
	 * @param name
	 * @param sheetName
	 * @param model
	 * @throws Exception
	 */
	public static void exportExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
								   String name, String sheetName, BaseRowModel model) throws Exception{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
		String date = sdf.format(new Date());
		String fileName = new String(name.getBytes(), CommonConstants.UTF8) + date + ".xlsx";
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
		EasyExcel.write(response.getOutputStream(), model.getClass()).sheet(sheetName).doWrite(list);
	}

	/**
	 * When exporting Excel with complex header, fill in a single group of data first, and then multiple groups of data
	 * @param response
	 * @param list Multi group data List
	 * @param map Single group data Map
	 * @param outFileName Exported Excel name
	 * @param templateFileName Excel Path name of the template
	 * @throws Exception
	 */
	public static void exportExcel(HttpServletResponse response, List<? extends BaseRowModel> list, Map<String,Object> map,
								   String outFileName, String templateFileName ) throws Exception{
		//Tell response to download the excel file
		response.setContentType("application/vnd.ms-excel");
		//Tell the response to use utf-8 encoding format
		response.setCharacterEncoding("utf-8");
		//. withTemplate(templateFileName) is to read the template
		//. write(ExcelUtil.getOutputStream(outFileName, response)) writes data to a file and gives it to the response
		ExcelWriter excelWriter = EasyExcel.write(ExcelUtils.getOutputStream(outFileName, response)).withTemplate(templateFileName).build();
		//Create Sheet
		//Set the excel Sheet as the page number and set the name
		//The previous parameter in. writerSheet(0, "first") is sheetNo, which is the first sheet
		//The second parameter is sheet name
		//No writing is the default
		WriteSheet writeSheet  = EasyExcel.writerSheet().build();
		// Note here that forceNewRow is used in the input parameter to represent that when writing the list, a row will be created regardless of whether there is an empty row under the list, and then the following data will move back. The default is false. The next line will be used directly. If not, it will be created.
		// If forceNewRow is set to true, one disadvantage is that it will put all data into memory, so use it with caution
		// Simply put, if your template has a list, and the list is not the last line, and there is data to fill in, you must set forceNewRow=true, but this will put all the data into memory, which will consume a lot of memory
		//. direction(WriteDirectionEnum.VERTICAL) this is to set the list to be filled vertically
		FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.FALSE).build();
		//Here is to fill the list into excel.
		//It will find the corresponding data on the template to fill in. For example, {list.getGoodsName} in the template is the data corresponding to the field named goodsName in the following List collection
		//The parameter in front of new FillWrapper("list", selectOrderDTO.getSelectOrderGoodsDTOS()) is to set a filled list name
		//The following parameters are the obtained list, which contains the data to be filled in
		//. fill() is mainly used to fill data into excel
		excelWriter.fill(new FillWrapper(list), fillConfig, writeSheet);
		//Here is to put some ordinary data into the map for easy filling. You can see getStringObjectMap().
		//The String of the map is the corresponding name, and the Object is the data.
		//Fill in data
		excelWriter.fill(map, writeSheet);
		//close
		excelWriter.finish();
	}


	/**
	 * This is ExcelUtil.getOutputStream
	 * Here is the file download to the browser
	 * @return
	 */
	public static OutputStream getOutputStream(String Name, HttpServletResponse response) throws Exception {
		//Here is the renaming of the file
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
		String date = sdf.format(new Date());
		String fileName = new String(Name.getBytes(), CommonConstants.UTF8) + date + ".xlsx";
		// If the file name here involves Chinese, you must use URL coding, otherwise it will be garbled
		response.setContentType("application/force-download");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
		return response.getOutputStream();
	}
}

controller layer

Call the written tool class's exportExcel method parameters: the first response, the second list (list data of multiple data to be displayed in Excel), the third map (map of single data to be displayed in Excel), the fourth outFileName (file name of exported Excel) (file name + time), and the fifth templateFileName (path of self built excel template)

The single piece of data to be displayed is placed in the map
Multiple pieces of data to be displayed are placed in the list

/**
	 * Operation data information export
	 *
	 * @return ApiResult
	 */
	@SysLog("Operation data information export")
	@ApiOperation(value = "Operation data information export")
	@GetMapping("/download/operational")
	public void downloadOperational(HttpServletResponse response, OperationalDataAnalyticalDto operationalDataAnalyticalDto) {
		try {
			List<OperationalDataAnalyticalModel> OperationalList = operationalDataAnalyticalService.operationalListModel(operationalDataAnalyticalDto);
			OperationalDataAnalyticalDto count = operationalDataAnalyticalService.getNoiseInfoCount(operationalDataAnalyticalDto);
			//After creating a map, the queried data will be mapped to the attribute names in the excel template one by one in the map
			HashMap<String, Object> map = new HashMap<>();
			map.put("thisTimeInstallCount", count.getThisTimeInstallCount());
			map.put("addInstallCount", count.getAddInstallCount());
			map.put("nowLeaveCount", count.getNowLeaveCount());
			map.put("installDateStar", count.getInstallDateStar());
			map.put("installDateEnd", count.getInstallDateEnd());
			map.put("district", count.getDistrict());
			map.put("isLeakCount", count.getIsLeakCount());
			map.put("isDoubtCount", count.getIsDoubtCount());
			map.put("isNormalCount", count.getIsNormalCount());
			map.put("isOfflineCount", count.getIsOfflineCount());
			//excel template path
			String templateFileName = "/opt/services/file/OperationalTemplate.xlsx";
			System.out.println(templateFileName);
			ExcelUtils.exportExcel(response, OperationalList, map, FileName.OPERATIONAL_INFO, templateFileName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

serviceImpl layer

If you want to process the queried data step by step, you can write it in serviceImpl

	/**
	 * Export noise equipment disassembly history information list
	 *
	 * @return List<OperationalDataAnalyticalModel>
	 */
	@Override
	public List<OperationalDataAnalyticalModel> operationalListModel(OperationalDataAnalyticalDto operationalDataAnalyticalDto){
		return baseMapper.getOperationalExcel(operationalDataAnalyticalDto);
		//Complex processing of queried data
	}

Result display:

Reference article: https://blog.csdn.net/weixin_44459007/article/details/115555795?