直接上代码(有注释)
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);}}