导入实例
List 转化为数组
方式1
List<Map<String, Object>> deviceTypeList = getDeviceTypeList(companyId);
if (deviceTypeList != null && !deviceTypeList.isEmpty() && deviceTypeList.get(0) != null) {
String[] deviceTypeArray = deviceTypeList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
ExcelUtil.addValidationToSheet(workbook,sheet, deviceTypeArray,'B',1,1000);
}
方式2 遍历
List<Map<String, Object>> deviceTypeList = getDeviceTypeList(companyId);
if (deviceTypeList != null && !deviceTypeList.isEmpty() && deviceTypeList.get(0) != null) {
String[] array = new String[deviceTypeList.size()];
for (int i = 0; i < deviceTypeList.size(); i++) {
array[i] = deviceTypeList.get(i).get("name").toString();
}
ExcelUtil.addValidationToSheet(workbook,sheet, array,'B',1,1000);
}
List T 转化为List String
// List<T> 转化为List<String>
List<String> collect = deviceList.stream().map(DeviceInfoVo::getDeviceType).collect(Collectors.toList());
//过滤
List<String> list = deviceList.stream().filter(e -> e.getPlateNo().equals("1")).map(DeviceInfoVo::getDeviceType).collect(Collectors.toList());
//或
List<DeviceInfoVo> collect1 = deviceList.stream().filter(e -> e.getPlateNo().equals("1")).collect(Collectors.toList());
下载模板
大概
@GetMapping("/downDeviceTemplate")
public ResponseEntity<byte[]> downDeviceTemplate(HttpServletRequest request) {
String[] a = {"设备编号","设备类型","制造商","条码"};
String name = "设备信息";
HSSFWorkbook workbook = ExcelUtil.createExcel(a, name);
HSSFSheet sheet = workbook.getSheet(name);
String[] b = {"aa","bb"}; //下拉框
ExcelUtil.addValidationToSheet(workbook,sheet,b,'B',1,1000);
Map<String,List<String>> map = new HashMap<>();
map.put("1", Arrays.asList("cc","dd")); //联动
map.put("2", Arrays.asList("ff","ee"));
ExcelUtil.addValidationToSheet(workbook,sheet,map,'C','D',1,1000);
return ExcelUtil.outputExcel(workbook,request,"设备.xls");
}
主要方法：
ExcelUtil
public static HSSFWorkbook createExcel(String[] titleArray, String sheetName) {
//创建Excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
//字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 11);
font.setFontName("宋体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置背景颜色
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
// solid 填充 foreground 前景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 表头
HSSFRow excelRoot = sheet.createRow(0);
// 此处设置数据格式
for (int i = 0; i < titleArray.length; i++) {
HSSFCell rootCell = excelRoot.createCell(i);
rootCell.setCellValue(titleArray[i]);
sheet.setColumnWidth(i, 5000);
rootCell.setCellStyle(style);
}
return workbook;
}
/**
* 给sheet页，添加下拉列表
*
* @param workbook excel文件
* @param targetSheet 需要操作的sheet页
* @param options 下拉列表数据
* @param column 下拉列表所在列 从'A'开始
* @param fromRow 下拉限制开始行
* @param endRow 下拉限制结束行
*/
public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
if (options != null && options.length > 0) {
String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
String nameName = column + "_parent";
int rowIndex = 0;
for (Object option : options) {
int columnIndex = 0;
Row row = optionsSheet.createRow(rowIndex++);
Cell cell = row.createCell(columnIndex++);
cell.setCellValue(option.toString());
}
createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);
DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
// 隐藏sheet页
int sheetIndex = workbook.getSheetIndex(optionsSheet);
workbook.setSheetHidden(sheetIndex, true);
}
}
/**
* 给sheet页 添加级联下拉列表
*
* @param workbook excel
* @param targetSheet 需要操作的sheet页
* @param options 要添加的下拉列表内容
* @param keyColumn 下拉列表1位置
* @param valueColumn 级联下拉列表位置
* @param fromRow 级联限制开始行
* @param endRow 级联限制结束行
*/
public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
if (options != null && !options.isEmpty()) {
String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
List<String> firstLevelItems = new ArrayList<>();
int rowIndex = 0;
for (Map.Entry<String, List<String>> entry : options.entrySet()) {
String parent = formatNameName(entry.getKey());
firstLevelItems.add(parent);
List<String> children = entry.getValue();
int columnIndex = 0;
Row row = hiddenSheet.createRow(rowIndex++);
Cell cell = null;
for (String child : children) {
cell = row.createCell(columnIndex++);
cell.setCellValue(child);
}
char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));
DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
}
addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);
// 隐藏sheet页
int sheetIndex = workbook.getSheetIndex(hiddenSheet);
workbook.setSheetHidden(sheetIndex, true);
}
}
/**
* 输出创建的Excel文件
*/
public static ResponseEntity<byte[]> outputExcel(Workbook workbook, HttpServletRequest request, String fileName) {
//设置头信息
HttpHeaders headers = new HttpHeaders();
//设置响应的文件名
String downloadFileName = DownloadUtil.getEncodedFilename(request, fileName);
headers.setContentDispositionFormData("attachment", downloadFileName);
headers.add("Access-Control-Expose-Headers", "filename");
headers.add("filename", downloadFileName);
//application/octet-stream二进制流数据的形式下载
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
try {
workbook.write(byteArrayOutputStream);
byte[] bytes = byteArrayOutputStream.toByteArray();
return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
workbook.close();
byteArrayOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
实例
下载模板
controller
/**
* 下载车载设备信息模板
* @param request
* @return org.springframework.http.ResponseEntity<byte[]>
* @author guozh
* @date 2021/12/23 16:21
*/
@GetMapping("/downDeviceTemplate")
public ResponseEntity<byte[]> downDeviceTemplate(HttpServletRequest request) {
Integer companyId = Integer.valueOf(HttpUtil.getCompanyId(request));
return deviceService.downDeviceTemplate(companyId,request);
}
serviceImpl
@Override
public ResponseEntity<byte[]> downDeviceTemplate(Integer companyId, HttpServletRequest request) {
String fileName = "车载设备信息模板.xls";
String[] titleArray = {"设备编号(必填)","设备类型","制造商","条码","设备状态","安装时间","车牌号","SIM卡号(必填)","营运商","账户状态(0：已开户，1：已销户)",
"摄像头数量","是否视频(0：否，1：是)","协议类型(必填)","套餐流量","SMSI卡号","开卡时间","结算方式","结算日期","备注"};
String sheetName = "车载设备信息";
HSSFWorkbook workbook = ExcelUtil.createExcel(titleArray, sheetName);
HSSFSheet sheet = workbook.getSheet(sheetName);
//设备类型下拉框
List<Map<String, Object>> deviceTypeList = getDeviceTypeList(companyId);
if (deviceTypeList != null && !deviceTypeList.isEmpty() && deviceTypeList.get(0) != null) {
String[] deviceTypeArray = deviceTypeList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
ExcelUtil.addValidationToSheet(workbook,sheet,deviceTypeArray,'B',1,1000);
}
//设备状态下拉框
List<Map<String, Object>> deviceStateList = getDeviceStateList(companyId);
if (deviceStateList != null && !deviceStateList.isEmpty() && deviceStateList.get(0) != null){
String[] deviceStateArray = deviceStateList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
ExcelUtil.addValidationToSheet(workbook,sheet,deviceStateArray,'E',1,1000);
}
//协议类型下拉框
List<Map<String, Object>> protocolTypeList = getProtocolTypeList(companyId);
if (protocolTypeList != null && !protocolTypeList.isEmpty() && protocolTypeList.get(0) != null){
String[] protocolTypeArray = protocolTypeList.stream().map(e -> e.get("name").toString()).toArray(String[]::new);
ExcelUtil.addValidationToSheet(workbook,sheet,protocolTypeArray,'M',1,1000);
}
//账户状态选择
String[] accountState = {"已开户","已销户"};
ExcelUtil.addValidationToSheet(workbook,sheet,accountState,'J',1,1000);
//是否视频选择
String[] canVideo = {"是","否"};
ExcelUtil.addValidationToSheet(workbook,sheet,canVideo,'L',1,1000);
return ExcelUtil.outputExcel(workbook,request,"车载设备信息.xls");
}
效果