基于poi生成excel模板并生成下拉选择框-Toy模板网

这篇具有很好参考价值的文章主要介绍了基于poi生成excel模板并生成下拉选择框。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

直接上代码（有注释）

public void downloadImportTemplate(HttpServletResponse response) {
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            //创建工作表
            XSSFWorkbook workbook = new XSSFWorkbook();
            //标题行的标题
            List<String> requireRowNameList = Collections.singletonList("订单编号");
            List<String> optionalRowNameList = Arrays.asList("用户编号");
            String fileName = "导入模板" + DateUtils.formatDateTime();
            XSSFSheet sheet = workbook.createSheet("导入模板");
            XSSFRow row = null;
            XSSFCell cell = null;

            //----------------标题样式---------------------
            XSSFCellStyle titleStyle = workbook.createCellStyle();//标题样式
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 22);
            titleFont.setFontName("微软雅黑");
            titleFont.setBold(true);
            titleStyle.setFont(titleFont);

            //----------------列样式2 必填字段显红色---------------------
            XSSFCellStyle cellStyle2 = workbook.createCellStyle();
            cellStyle2.setAlignment(HorizontalAlignment.CENTER);
            cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
            Font cellFont2 = workbook.createFont();
            cellFont2.setFontName("微软雅黑");
            cellFont2.setBold(true);
            cellFont2.setColor((short) 0xa); // 红色
            cellStyle2.setFont(cellFont2);

            //----------------列样式3 非必填字段显黑色---------------------
            XSSFCellStyle cellStyle3 = workbook.createCellStyle();
            cellStyle3.setAlignment(HorizontalAlignment.CENTER);
            cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
            Font cellFont3 = workbook.createFont();
            cellFont3.setFontName("微软雅黑");
            cellFont3.setBold(true);
            cellStyle3.setFont(cellFont3 );

            //------------------- 创建第一行（字段名） --------------------
            row = sheet.createRow(0);
            sheet.setDefaultColumnWidth(20);
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(1, true);
            int i = 0;
            for (; i < requireRowNameList.size(); i++) {
                cell = row.createCell(i);
                //必填列红色显示
                cell.setCellStyle(cellStyle2);
                cell.setCellValue(requireRowNameList.get(i));
            }
            int j = 0;
            for (; j < optionalRowNameList.size(); i++, j++) {
                cell = row.createCell(i);
                //非必填列黑色显示
                cell.setCellStyle(cellStyle3);
                cell.setCellValue(optionalRowNameList.get(j));
            }
            cell = row.createCell(i);
            //非必填列黑色显示
            cell.setCellStyle(cellStyle3);
            cell.setCellValue("是否通知客户");

            //--------------------设置下拉选择框的代码-----------------------------
            //选择框可选值
            String[] datas = new String[]{"是", "否"};
            //设置需要生成下拉列表的表格范围，1代表从第二行开始，30000代表一直到第三万零一行，第i+1列开始，到第i+1列；
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 30000, i, i);
            //生成下拉框内容
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(datas);
            DataValidation validation = dvHelper.createValidation(dvConstraint, cellRangeAddressList);
            //设置错误信息提示
            validation.setShowErrorBox(true);
            //对sheet页生效
            sheet.addValidationData(validation);
			//------------------------------------------------
			
            response.setContentType("application/binary;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            FileUtils.setAttachmentResponseHeader(response, fileName);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("获取导入模板失败e={}", e.getMessage(), e);
        }
    }

给生成的excel某系列设置时间格式

			Workbook workbook = new XSSFWorkbook();
			 Sheet sheet = workbook.createSheet("测试sheet");
            // 设置excel表第三第四列的时间格式
            CellStyle dateTimeStyle = workbook.createCellStyle();
            CreationHelper createHelper = workbook.getCreationHelper();
            dateTimeStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss"));
            sheet.setDefaultColumnStyle(2, dateTimeStyle);//给excel第三列设置时间格式
            sheet.setDefaultColumnStyle(3, dateTimeStyle);