情景:导出时会遇到某个字段是字典表中数据,为了防止用户乱填,需要返回下拉,用户只需要选择下拉即可
实现方法如下:
第一步:需要导入poi包
<!-- excel -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.2.0</version>
</dependency>
第二步:easypoi中未封装进去下拉,需要创建一个生成下拉的方法
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
public final class ExcelUtil {
/**
* firstRow 开始行号(下标0开始)
* lastRow 结束行号,最大65535
* firstCol 区域中第一个单元格的列号 (下标0开始)
* lastCol 区域中最后一个单元格的列号
* dataArray 下拉内容
* sheetHidden 影藏的sheet编号(例如1,2,3),多个下拉数据不能使用同一个
* */
public static void selectList(Workbook workbook,int firstCol,int lastCol,String[] strings ){
Sheet sheet = workbook.getSheetAt(0);
// 生成下拉列表
// 只对(x,x)单元格有效
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
// 生成下拉框内容
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
// 对sheet页生效
sheet.addValidationData(dataValidation);
}
}
第三步:把查询字典列表封装一个方法中:
private void commonChange(Workbook workbook) {
//String[] list1 = new String[]{"南山区", "福田区", "宝安区", "罗湖区", "龙岗区"};
//String[] list2 = new String[]{"商场", "医院", "住宅", "学校", "港口", "酒店","其他"};
SysDictMaintenance sysDictMaintenance = new SysDictMaintenance();
//设备使用区域列表
List<String> sbsyqyList = new ArrayList<>();
List<SysArea> list3 = sysAreaService.getAreaList();
for (SysArea dict : list3) {
sbsyqyList.add(dict.getName());
}
//查询设备所属场所列表
List<String> sbcsList = new ArrayList<>();
sysDictMaintenance.setType("use_place");
List<SysDictMaintenance> list4 = sysDictMaintenanceService.queryList(sysDictMaintenance);
for (SysDictMaintenance dict : list4) {
sbcsList.add(dict.getLabel());
}
//设置Excel下拉列表
ExcelUtil.selectList(workbook, 33, 33, sbsyqyList.toArray(new String[sbsyqyList.size()]));
ExcelUtil.selectList(workbook, 34, 34, sbcsList.toArray(new String[sbcsList.size()]));
}
注意: ExcelUtil.selectList(workbook, 33, 33, sbsyqyList.toArray(new String[sbsyqyList.size()])); 下标要与实体对象中相关字段,一一对应,不然显示对不上的。还有要把数据格式数组数组对象转换成数组
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class WbDeviceParameters {
private static final long serialVersionUID = 1L;
/**
* 特种设备编号
*/
@TableId
private String id;
/**
* 设备注册代码
*/
@Excel(name = "*设备注册代码", orderNum = "1", width = 20)
private String sbzcdm;
.
.
.
/**
* 设备使用区域
*/
@Excel(name = "*设备使用区域", orderNum = "34", width = 20)
private String deviceUseArea;
/**
* 设备所属场所
*/
@Excel(name = "*设备所属场所", orderNum = "35", width = 20)
private String devicePlace;
}
第四步:调用封装相关字典查询列表方法:
/**
* 下载导入数据模板
*/
@RequestMapping(value = "downLoad")
public void downLoad(HttpServletResponse response) throws IOException {
ServletOutputStream sos = null;
try {
String title = "设备信息";//表格标题
String fileName = "设备信息导入模板.xls";
DeviceParameters deviceParameters = new DeviceParameters();
deviceParameters.setSbzcdm("3110441xxx2012060002");
List<DeviceParameters> list = deviceParametersService.findList(deviceParameters);//查询设备列表
commonChange(workbook);//生成下拉调用
ExportParams exportParams = new ExportParams(title, title);
exportParams.setDictHandler(new ExcelDictHandlerImpl());
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, DeviceParameters.class, list);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName));
sos = response.getOutputStream();
workbook.write(sos);
} catch (Exception e) {
LoggerUtils.error(logger, e);
}
}
效果图如下:
OK ^ _ ^这次就到这里啦