序号
- 前言
- 需求
- 不通过excel,实现省市区级联
- 实战
- pom.xml配置
- controller配置
- service类
- 业务处理类
- 测试
前言
首先，我们先来了解一下java实现模板下载的几种方式
- 1、使用poi实现
- 2、使用阿里的easyexcel实现
今天社长就给大家说一下easyexcel的实现模板下载的之旅。在这里社长祝福各位小朋友大朋友，儿童节快乐
需求
接到一个需求，实现模板的下载，并且要支持省市区三级级联，点击所属省份的时候，所属市能动态显示对应的市，选择市这一列时，所属区下拉框显示对应的区。
下图是对应模板的信息，下方第二个图是stationTemplate.xlsx文件的内容。各位社友可以作为参考，主要是理解实现的逻辑。
不通过excel,实现省市区级联
实现的逻辑:
- 创建一个sheet，第一列是父节点，从第二列开始是子节点对应的值
- 创建名称管理器，建立key value关系，方便做单元格变动时，动态显示子节点的列表
- 给省或者市设置验证规则
点击公式里面的名称管理器，实际上就是跟我们创建的sheet建立练习，点击佛山市时，显示顺德区和南海区。达到级联的效果。
实战
pom.xml配置
<!-- easypoi-spring-boot-starter --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.1.3</version></dependency>
controller配置
package com.zyee.iopace.web.controller;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zyee.iopace.web.config.annotation.Log;
import com.zyee.iopace.web.dto.DataCondition;
import com.zyee.iopace.web.dto.dept.DeptSearchDto;
import com.zyee.iopace.web.dto.station.DataListConditionDto;
import com.zyee.iopace.web.dto.station.PrimitiveReportDto;
import com.zyee.iopace.web.dto.station.StationPhotoDto;
import com.zyee.iopace.web.entity.Cern;
import com.zyee.iopace.web.entity.Dept;
import com.zyee.iopace.web.entity.Station;
import com.zyee.iopace.web.entity.StationPicture;
import com.zyee.iopace.web.enums.BusinessType;
import com.zyee.iopace.web.enums.EcologyType;
import com.zyee.iopace.web.response.ResponseResult;
import com.zyee.iopace.web.service.CernService;
import com.zyee.iopace.web.service.StationPictureService;
import com.zyee.iopace.web.service.StationService;
import com.zyee.iopace.web.utils.BeanCopyUtils;
import com.zyee.iopace.web.utils.PageUtils;
import com.zyee.iopace.web.vo.station.PrimitiveReportVo;
import com.zyee.iopace.web.vo.station.StationCommVo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import javax.servlet.http.HttpServletResponse;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;/*** 站点模块* @author wu* @version 1.0* @date 2023/5/16 14:55*/
@Slf4j
@Api(tags = "站点管理")
@RestController
@RequestMapping("/station")
public class StationController {@Autowiredprivate StationService stationService;@ApiOperation(value = "站点模板下载", produces = "application/octet-stream")@Log(title = "站点模板下载", businessType = BusinessType.DOWNLOAD)@GetMapping("/downloadStationTemplate")public void downloadStationTemplate(HttpServletResponse response) {stationService.downloadStationTemplate(response);}
}
- application/octet-stream 是解决swagger下载时乱码问题
service类
package com.zyee.iopace.web.service.impl;import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zyee.iopace.web.config.exception.BusinessException;
import com.zyee.iopace.web.entity.*;
import com.zyee.iopace.web.dto.StationDto;
import com.zyee.iopace.web.dao.StationMapper;
import com.zyee.iopace.web.service.ModuleService;
import com.zyee.iopace.web.service.StationService;
import com.zyee.iopace.web.service.UserService;
import com.zyee.iopace.web.service.UserStationService;
import com.zyee.iopace.web.service.impl.excel.ElementSheetWriteHandler;
import com.zyee.iopace.web.service.impl.excel.StationSheetWriteHandler;
import com.zyee.iopace.web.utils.UserUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;import static com.baomidou.mybatisplus.core.toolkit.IdWorker.getId;@Service
public class StationServiceImpl extends ServiceImpl<StationMapper, Station> implements StationService {@Value("${station.template-path}")private String templatePath;@Value("${downloadTemplate.stationPath}")private String stationPath;@Overridepublic void downloadStationTemplate(HttpServletResponse response) {try {String path = templatePath + File.separator + stationPath;String fileName = URLEncoder.encode("站点模版下载.xls", "utf-8");response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition","attachment;filename=" + fileName);// 写出数据EasyExcel.write(response.getOutputStream()).withTemplate(path).registerWriteHandler(new StationSheetWriteHandler(moduleService)).sheet().doWrite((Collection<?>) null);} catch (Exception e) {e.printStackTrace();throw new BusinessException("下载模板失败");}}
}- path对应的是下载模板存放在服务器的路径，一般为网络地址(第三方)
- 注意：用swagger模拟下载时，一定要加上produces属性的设置，不然会显示乱码
业务处理类
package com.zyee.iopace.web.service.impl.excel;import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.zyee.iopace.web.entity.ElementRange;
import com.zyee.iopace.web.entity.Module;
import com.zyee.iopace.web.enums.CharStatusEnum;
import com.zyee.iopace.web.enums.EcologyType;
import com.zyee.iopace.web.enums.RangeType;
import com.zyee.iopace.web.service.ElementRangeService;
import com.zyee.iopace.web.service.ModuleService;
import com.zyee.iopace.web.utils.ExcelUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;import java.util.*;
import java.util.stream.Collectors;/*** 站点模块* @author wude* @date 2023/05/10*/
public class StationSheetWriteHandler implements SheetWriteHandler {private ModuleService moduleService;public StationSheetWriteHandler(ModuleService moduleServices){this.moduleService = moduleServices;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//所有一级List<String> mapOneList = new ArrayList<String>();mapOneList.add("广东省");mapOneList.add("湖北省");//关系mapMap<String, List<String>> map = new HashMap<String, List<String>>();map.put("广东省", Arrays.asList("广州市", "佛山市"));map.put("湖北省", Arrays.asList("武汉市", "荆州市"));map.put("广州市", Arrays.asList("白云区", "越秀区"));map.put("佛山市", Arrays.asList("顺德区", "南海区"));Workbook workbook = writeWorkbookHolder.getWorkbook();//三级联动 sheetSheet mapSheet = writeWorkbookHolder.getWorkbook().createSheet("SHEET_MAP");// true:隐藏/false:显示//省市区关系sheet//workbook.setSheetHidden(workbook.getSheetIndex(mapSheet), true);// 设置sheet是否隐藏writeData(workbook, mapSheet, mapOneList, map);//将省份放入下拉列表中String[] provinceArr = mapOneList.toArray(new String[mapOneList.size()]);///开始设置（大类小类）下拉框DataValidationHelper dvHelper = writeSheetHolder.getSheet().getDataValidationHelper();// 大类规则DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(provinceArr);CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 999, 8, 8);setValidation(writeSheetHolder.getSheet(), dvHelper, expConstraint, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");// 小类规则(各单元格按个设置)// "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市// 为了让每个单元格的公式能动态适应，使用循环挨个给公式。// 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联for (int i = 2; i < 1000; i++) {CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 9, 9);DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($I$" + i + ")");setValidation(writeSheetHolder.getSheet(), dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");}for (int i = 2; i < 1000; i++) {CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 10, 10);DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($J$" + i + ")");setValidation(writeSheetHolder.getSheet(), dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");}int number = writeWorkbookHolder.getWorkbook().getNumberOfSheets();//将新建立的sheet页隐藏掉for (int i = 1; i < number;i++){writeWorkbookHolder.getWorkbook().setSheetHidden(i,true);}}/*** 设置验证规则* @param sheet sheet对象* @param helper 验证助手* @param constraint createExplicitListConstraint* @param addressList 验证位置对象* @param msgHead 错误提示头* @param msgContext 错误提示内容*/private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {DataValidation dataValidation = helper.createValidation(constraint, addressList);dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);dataValidation.setShowErrorBox(true);dataValidation.setSuppressDropDownArrow(true);dataValidation.createErrorBox(msgHead, msgContext);sheet.addValidationData(dataValidation);}private static void setDataValid(Workbook workbook, Sheet mainSheet, List<String> provinceList, Map<String, List<String>> siteMap, CharStatusEnum charEnum) {//设置省份下拉DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) mainSheet);String[] dataArray = provinceList.toArray(new String[0]);Sheet hidden = workbook.createSheet("hidden");Cell cell = null;for (int i = 0, length = dataArray.length; i < length; i++) {String name = dataArray[i];Row row = hidden.createRow(i);cell = row.createCell(0);cell.setCellValue(name);}Name namedCell = workbook.createName();namedCell.setNameName("hidden");namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);//加载数据,将名称为hidden的XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, "hidden");// 四个参数分别是：起始行、终止行、起始列、终止列.// 1 (省份下拉框代表从excel第1+1行开始) 10(省份下拉框代表从excel第1+10行结束) 1(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)// CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 10, 1, 1);CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 255, charEnum.getValue() - 1, charEnum.getValue() - 1);DataValidation provinceDataValidation = dvHelper.createValidation(constraint, provinceRangeAddressList);provinceDataValidation.createErrorBox("error", "请选择正确");provinceDataValidation.setShowErrorBox(true);// provinceDataValidation.setSuppressDropDownArrow(true);//将第二个sheet设置为隐藏// true:隐藏/false:显示workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);// 设置sheet是否隐藏mainSheet.addValidationData(provinceDataValidation);// 设置市、区下拉// i <= 10 ,10代表市、区下拉框到10+1行结束for (int i = 0; i <= 10; i++) {// setDataValidation('B', mainSheet, i + 1, 2);// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3setDataValidation(charEnum.getCharacter(), mainSheet, i + 1, charEnum.getValue());// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3}}private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, DataValidationHelper dvHelper) {DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);DataValidation data_validation_list = dvHelper.createValidation(dvConstraint, regions);data_validation_list.setEmptyCellAllowed(false);if (data_validation_list instanceof XSSFDataValidationHelper) {// data_validation_list.setSuppressDropDownArrow(true);data_validation_list.setShowErrorBox(true);} else {// data_validation_list.setSuppressDropDownArrow(false);}// 设置输入信息提示信息data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");return data_validation_list;}/*** 设置有效性** @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动* @param sheet* @param rowNum 行数* @param colNum 列数*/private static void setDataValidation(char offset, Sheet sheet, int rowNum, int colNum) {DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);DataValidation dataValidationList1;DataValidation dataValidationList2;dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum - 1, colNum, dvHelper);dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 1) + (rowNum) + ")", rowNum - 1, colNum + 1, dvHelper);sheet.addValidationData(dataValidationList1);sheet.addValidationData(dataValidationList2);}private static void writeData(Workbook hssfWorkBook, Sheet mapSheet, List<String> provinceList, Map<String, List<String>> siteMap) {//循环将父数据写入siteSheet的第1行中int siteRowId = 0;Row provinceRow = mapSheet.createRow(siteRowId++);provinceRow.createCell(0).setCellValue("父列表");for (int i = 0; i < provinceList.size(); i++) {//有多少个省，创建多少个下拉框provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));}// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。Iterator<String> keyIterator = siteMap.keySet().iterator();while (keyIterator.hasNext()) {String key = keyIterator.next();List<String> son = siteMap.get(key);Row siteRow = mapSheet.createRow(siteRowId++);siteRow.createCell(0).setCellValue(key);for (int i = 0; i < son.size(); i++) {siteRow.createCell(i + 1).setCellValue(son.get(i));}// 添加名称管理器String range = ExcelUtils.getRange(1, siteRowId, son.size());Name name = hssfWorkBook.createName();name.setNameName(key);String formula = mapSheet.getSheetName() + "!" + range;name.setRefersToFormula(formula);}}}- mapOneList为省份模拟数据，需要从数据库取出，需要省市区对应字典表的sql，可以在下方@我
- map 是省份对应的市，以及市对应的区
- 第一个CellRangeAddressList的参数 1 999 8 8，表示1到999行都新增下拉框，8和8表示那一列
- dvHelper.createFormulaListConstraint(“INDIRECT( J J J” + i + “)”) 这里的“ J J J”，J对应的就是区，I对应的就是市
- writeWorkbookHolder.getWorkbook().setSheetHidden(i,true); 隐藏新增的sheet
测试
点击发送->下载文件
