说明：
工作上需要导出excel带下拉框，查询poi导出模板时候发现下拉框的行数都只能写死一个限制数字，后来尝试发现先自己设置excel数据有效性就可以避免这个限制
1、导入依赖
<!--poi导入导出 begin--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.15</version></dependency><!--poi导入导出 end-->
2、新建表格
2.1步骤：
比如我们需要 校区这一列是下拉框 ，就新建一个excel 设置校区这一列的 数据 ——》有效性 ，选择序列然后填写excel函数 “=xqSite! A : A: A:A” ; 表示从"xqSite"这个工作表的A列取值, 新建一个工作表叫做“xqSite”,然后隐藏起来 ，之后用代码写入数据
3、关键代码
//手动创建一个专门用来存放校区信息的隐藏sheet页//得到我们创建"xqSite"工作表Sheet hideSheet = book.getSheet("xqSite");book.setSheetHidden(book.getSheetIndex(hideSheet), true);//隐藏sheet添加数据// 设置第一列，存校区名称的信息List<String> xqMcList = xqList.stream().map(x -> x.getXqMc()).collect(Collectors.toList());PoiExUtil.addDataToSheet(xqMcList, hideSheet);
/*** 往隐藏sheet里面添加数据** @param dataList* @param sheet*/public static void addDataToSheet(List<String> dataList, Sheet sheet){for (int i = 0; i < dataList.size(); i++){Row row = sheet.createRow(i);Cell cell = row.createCell(0);cell.setCellValue(dataList.get(i));}}
4、所有代码
/*** 下载导入模板*/public void downloadTemplate(HttpServletResponse response){//查到所有校区List<JjXqJbXxEntity> xqList = jjXqJbXxService.getXqJbXxList();if (CollectionUtils.isEmpty(xqList)){throw new CommonException("请先设置校区信息");}String templatePath = "/template/LdJbXxImTemplate.xlsx";//导出版本的org.springframework.core.io.Resource resource = new ClassPathResource(templatePath);ServletOutputStream os = null;InputStream is = null;try{response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("楼栋导入模板.xlsx", "UTF-8"));response.setContentType("application/octet-stream");is = resource.getInputStream();os = response.getOutputStream();Workbook book = new XSSFWorkbook(is);//手动创建一个专门用来存放校区信息的隐藏sheet页Sheet hideSheet = book.getSheet("xqSite");book.setSheetHidden(book.getSheetIndex(hideSheet), true);//隐藏sheet添加数据// 设置第一列，存校区的信息List<String> xqMcList = xqList.stream().map(x -> x.getXqMc()).collect(Collectors.toList());PoiExUtil.addDataToSheet(xqMcList, hideSheet);book.write(os);} catch (IOException e){log.info(e.getMessage());} finally{try{is.close();os.close();} catch (IOException e){log.info(e.getMessage());}}}
PoiExUtil:
package cj.jj.ggmk.excel;import lombok.extern.slf4j.Slf4j;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddressList;import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import java.io.IOException;import java.io.InputStream;import java.text.DateFormat;import java.util.Date;import java.util.List;@Slf4jpublic class PoiExUtil{/*** 设置某些列的值只能sheet中某列输入预制的数据,显示下拉框.** @param sheet模板sheet页（需要设置下拉框的sheet）* @param sheetName 隐藏的sheet页，用于存放下拉框的值 （下拉框值对应一列）* @param lastRow 存放下拉框值的最后一行* @param col 存放下拉框值的列名 "A"* @param firstRow 添加下拉框对应开始行* @param endRow 添加下拉框对应结束行* @param firstCol 添加下拉框对应开始列* @param endCol 添加下拉框对应结束列* @return HSSFSheet 设置好的sheet.*/public static XSSFSheet setXSSFValidation(XSSFSheet sheet, String sheetName, int lastRow, String col, int firstRow, int endRow, int firstCol, int endCol){//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);String cell = "\"" + sheetName + "!$" + col + "$1:$" + col + "$" + lastRow + "\"";log.info("下拉框列：" + cell);// 这句话是关键 引用ShtDictionary 的单元格XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);DataValidationConstraint constraint = dvHelper.createFormulaListConstraint("INDIRECT(" + cell + ")");DataValidation validation = dvHelper.createValidation(constraint, regions);sheet.addValidationData(validation);return sheet;}/*** 往隐藏sheet里面添加数据** @param dataList* @param sheet*/public static void addDataToSheet(List<String> dataList, Sheet sheet){for (int i = 0; i < dataList.size(); i++){Row row = sheet.createRow(i);Cell cell = row.createCell(0);cell.setCellValue(dataList.get(i));}}/*** 得到sheet页** @param file* @return* @throws IOException*/public static Sheet getSheet(MultipartFile file) throws IOException{/**** 判断文件版本*/String fileName = file.getOriginalFilename();String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);InputStream ins = file.getInputStream();Workbook wb = null;if (suffix.equals("xlsx") || suffix.equals("xlsm")){wb = new XSSFWorkbook(ins);} else{wb = new HSSFWorkbook(ins);}/*** 获取excel表单*/Sheet sheet = wb.getSheetAt(0);return sheet;}/*** 获取单元格的值** @param cell* @return*/public static String getCellValue(Cell cell){if (cell == null){return "";}return cell.toString();}/*** 解析POI导入Excel中日期格式数据** @param currentCell* @return currentCellValue*/public static String importDate(Cell currentCell, DateFormat forMater){String currentCellValue = "";String dataFormatString = currentCell.getCellStyle().getDataFormatString();// 判断单元格数据是否是日期if ("yyyy/mm;@".equals(dataFormatString) || "m/d/yy".equals(dataFormatString) || "yy/m/d".equals(dataFormatString) || "mm/dd/yy".equals(dataFormatString) || "dd-mmm-yy".equals(dataFormatString) || "yyyy/m/d".equals(dataFormatString) || "m/d/yy h:mm".equals(dataFormatString)){if (DateUtil.isCellDateFormatted(currentCell)){// 用于转化为日期格式Date d = currentCell.getDateCellValue();currentCellValue = forMater.format(d);}} else{// 不是日期原值返回currentCellValue = currentCell.toString();}return currentCellValue;}/*** 设置文列样式和边框** @param book* @param sheet*/public static void setColumnStyleAndBorder(Workbook book, Sheet sheet,Integer colNum){//设置列样式(文本)：CellStyle textStyle = book.createCellStyle();DataFormat format = book.createDataFormat();textStyle.setDataFormat(format.getFormat("@"));//边框textStyle.setBorderBottom(BorderStyle.THIN);textStyle.setBorderLeft(BorderStyle.THIN);textStyle.setBorderRight(BorderStyle.THIN);textStyle.setBorderTop(BorderStyle.THIN);sheet.setDefaultColumnStyle(colNum, textStyle);}}
